I85PREP
⌘ K
Alteryx Designer Expert study materials
Alteryx Designer Expert study resources

Parsing a Semi-Structured Log Without Losing Rows

Alteryx Designer Expert · Put your knowledge to work in context.

All case studies

Parsing a Semi-Structured Log Without Losing Rows

A 1.8M-row application log has a single message field with inconsistent shapes, e.g. 2024-03-11T14:02:11Z INFO user=4471 action=login status=200 and 2024-03-11T14:02:19Z WARN user=882 action=timeout retry=3. Some lines are truncated and lack status or retry. The analyst built a RegEx tool with (\w+)=(\w+) and a Text to Columns on space, and after parsing the row count dropped from 1.8M to 1.62M. They need timestamp (as DateTime), level, user, action, and a nullable numeric retry. They also need to keep every original row, flagging parse failures rather than dropping them.

1. Explain why the row count dropped and why Text to Columns on space is the wrong primary tool here.

Read the worked answer

The drop from 1.8M to 1.62M is the classic symptom of a parsing tool that only emits matched rows. A RegEx tool in Parse mode emits one output row per match; rows with no match (truncated lines) are silently dropped unless you use the U (unmatched) output anchor. Text to Columns on space is also wrong as the primary method because the number of tokens varies per line (some have status, some retry, some neither), so fixed column counts misalign and truncated lines produce ragged output.

Text to Columns is a positional tool; it assumes a stable delimiter count. A key=value log is not positional. The correct primary tool is RegEx in Parse mode with the U anchor wired to a flag, or better, RegEx with named capture groups plus a fallback. Any row landing on U should be tagged parse_failed=1 and unioned back so the 1.8M count is preserved.

2. Give a robust parsing design that preserves all rows, handles missing fields, and produces a typed nullable retry.

Read the worked answer

Design for "keep everything, type what you can":

  1. RegEx (Parse) with a pattern anchored to the known prefix, using named groups, e.g. ^(?<ts>\S+)\s+(?<level>\w+)\s+user=(?<user>\d+)\s+action=(?<action>\w+)(?:\s+status=(?<status>\d+))?(?:\s+retry=(?<retry>\d+))?. Optional groups make missing fields non-fatal.
  2. Take the matched anchor as parsed rows.
  3. Take the U anchor, add parse_failed=1 and nulls for the missing fields, then Union it back with the parsed stream so total rows = 1.8M. Assert the count.
  4. DateTime tool on ts (ISO 8601 format) to get a true DateTime.
  5. Select/Formula to cast retry to Int32; empty strings become null via ToNumber() or a null-check formula, not zero. Use IF IsEmpty([retry]) THEN Null() ELSE ToNumber([retry]) ENDIF.

Validate by checking: total rows unchanged, count of parse_failed is small and explainable, and retry nulls equal the count of lines lacking retry.