Skip to lesson
Exit
Data, SQL & Measurement1 / 2

2 min lesson

Dirty data is the systems problem wearing a SQL hat

Recall the main items in "Dirty data is the systems problem wearing a SQL hat", then connect each one to the work.

Step 1 of 2

Dirty data is the systems problem wearing a SQL hat

Dedup, nulls and inconsistent enums show up in queries the same way they show up in the pipeline you built. An interviewer who drops a duplicate lead_id or a null segment into the dataset is checking whether you notice before you report. Handle it explicitly rather than letting the database silently decide.

  • Dedup at the entity grain with row_number() keyed on the latest updated_at, not a blind distinct that hides which row won.
  • Coalesce nulls into an explicit bucket (coalesce(segment, 'unknown')) so they appear in the report instead of vanishing from a group by.
  • Normalize enums (lower(trim(stage))) when the source mixes Qualified, qualified and QUALIFIED.
  • Guard every division with nullif(denominator, 0) so an empty segment returns null, not a query error.
Sanity-check before you state the number

Run a row-count and a distinct-key count on every joined CTE. If they diverge when you expect them equal, you have a fan-out. Cross-check one segment total against a known figure the interviewer mentioned earlier. State the time window and the filter you applied as part of the answer, not as an afterthought.

Interview move

Narrate the grain before you write the join: "leads is one row per person, usage is one row per event, so I'll collapse usage to per-lead in a CTE first." That sentence alone signals more seniority than a syntactically perfect query written silently.

Watch out

Don't over-claim what a number means. "3,200 qualified leads" is a count; "3,200 qualified leads, defined as first-time hitting score 75, in Q1, deduped on lead_id" is an answer. The qualifier is where truth-seeking shows up under pressure.

Learn more

Optional practice

Practice: Dirty data is the systems problem wearing a SQL hat

QYou join leads to usage_events and report 9,400 "active leads," but the raw lead table only has 3,100 rows. What almost certainly happened and how do you fix it?