Grain Rehearsal: Say the Sentence, Then Prove It in SQL

The review screen (you already learned this)

Level 3's lesson "Facts, Dimensions, and Grain" taught the whole idea, so this is a pointer, not a reteach:

• The grain is the sentence that says what one row of a fact table means. "One row per completed trip." "One row per order line." "One row per driver per day."
• Declare it before you draw a single table. Every later decision, which measures are allowed, which dimensions can attach, which duplicates are bugs, falls out of that sentence.
• Facts stay narrow (keys plus measures). Dimensions get wide (descriptive attributes).
• Saying the grain out loud, first, is a named strong-candidate signal in modeling interviews.

That is the part you already have. Here is the part this lesson adds.

The follow-up that separates rehearsed candidates

You say "one row per completed trip." A good interviewer does not say "great." They say: prove the data respects it.

The proof is a query, and it is short. Group the raw extract by the key your grain sentence names, count the rows in each group, and keep only the groups holding more than one. If that query returns nothing, the extract honors the declared grain. If it returns rows, you found duplicates before they inflated a single number.

Order of evaluation

1. Staging extractraw rows, grain unproven
2. Declare grainone row per completed trip
3. Grain auditGROUP BY key HAVING COUNT(*) > 1
4. Dedupkeep the latest row per key
5. Clean factgrain now guaranteed

The scenario

A ride-share company hands you staging_trips, a raw extract from the trips service. It is wide: it carries the driver's name and home city on every trip row, which is normal for an extract and wrong for a fact table. It also re-emits corrected rows: when a fare is adjusted, the service sends the whole trip again with a later completed_at instead of updating in place.

So the extract has more rows than trips. Run the audit and you can say which trips, and how badly, in one query.

Repairing the grain

Once the audit finds duplicates, the repair is the windowed-dedup pattern you already know: number the rows inside each key, newest first, and keep number one.

WITH latest AS (
  SELECT trip_id, fare_usd,
         ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY completed_at DESC) AS rn
  FROM staging_trips
)
SELECT ROUND(SUM(fare_usd), 2) AS clean_revenue
FROM latest
WHERE rn = 1;

PARTITION BY trip_id restarts the numbering inside each trip. ORDER BY completed_at DESC decides which version wins. WHERE rn = 1 keeps exactly one row per trip, which is the declared grain, restored.

Common mistake: deduplicating with SELECT DISTINCT on the whole row. The corrected rows differ in fare_usd and completed_at, so DISTINCT keeps every one of them and reports success. Dedup by the grain key, then pick a winner by a rule you can defend.

Interview nuance: in a take-home, running the grain audit before you build anything is a 60-second move that reviewers notice. It is the difference between "I assumed the extract was clean" and "I checked, and two trips were re-emitted, so I kept the latest version of each." Narrate the rule you chose for picking the winner. That rule is the actual judgment call.

On a real platform this differs. Here the audit is an ad-hoc query you run by hand. In a warehouse it becomes a scheduled test: dbt's unique test on the grain key, or Great Expectations' expect_compound_columns_to_be_unique, both of which fail the build instead of the dashboard. The SQL underneath is the same GROUP BY key HAVING COUNT(*) > 1 you are about to write.