AdvancedData Modeling · 25 min

Grain and Fan Traps: Advanced

Grain is the single most important concept in data modeling interviews. It is also the one candidates fumble first. Every FAANG data modeling question starts here, whether the word 'grain' appears in the prompt or not. When the interviewer says 'design a fact table for X,' they are testing whether you will define the grain before you write a single column name. Candidates who jump to columns get a 'no hire.' Candidates who open with 'the grain of this table is one row per...' are already ahead of 90% of the pool.

What you will be able to do

Identify grain as the hidden first question in every modeling prompt

Detect fan traps and chasm traps before they corrupt your output

Articulate grain decisions like a senior modeler, not a textbook

"What Is the Grain of This Table?"

Daily Life

Interviews

You are being tested on grain when you hear:

▸"Design a fact table for..."
▸"How would you model this data?"
▸"What would this table look like?"
▸"The numbers don't add up when we join these tables"
▸"We need to track X at the Y level"
▸Any question where the interviewer describes a business process

Grain is not a modeling step. It is THE modeling step. Everything else follows from it: which columns belong in the table, which joins are valid, which aggregations produce correct numbers. Getting grain wrong does not produce an error message. It produces wrong numbers that look right. That is why interviewers test it.

What They're Really Testing

The interviewer is not checking whether you know the word 'grain.' They are checking whether you instinctively open with it before doing anything else. The hidden rubric item is: does this candidate define the unit of analysis before designing the schema? If you skip this step and jump to column names, you have signaled that you build tables by feel rather than by discipline.

The 60-Second Framework

State the grain in plain English: 'One row represents one order line item on one day.'

Name the columns that enforce it: the composite of columns that is unique per row.

Verify with the interviewer: 'Does one order ever have multiple shipments on the same day? That would change the grain.'

Only then start listing dimensions and measures.

Step 3 is the strong-hire signal. Asking the interviewer a clarifying question about grain shows you have been burned by wrong assumptions in production. Junior candidates never ask. Senior candidates always do.

Grain Statements: Good vs Bad

•Weak (No Hire)

"The table has order_id, customer_id, product_id..."
"Each row is an order"
"It stores order data"
"We'd put all the order info in one table"

✓Strong (Strong Hire)

"The grain is one row per order line item"
"Unique on (order_id, line_item_seq)"
"Before I add columns, is this order-level or item-level?"
"If a line item can be partially shipped, the grain might need to include shipment_id"

Why Companies Care

Cite these in your answer: At Meta, wrong-grain caused ad revenue to be double-counted across ad groups for a quarter. At Amazon, order-level grain instead of item-level meant return rates were computed against order counts, masking a product quality issue. These stories prove you have seen grain bugs. They only surface when a VP asks 'why do our numbers not match finance?'

TIP

When the interviewer asks you to 'design a table,' your first sentence should always be a grain statement. Always. Not sometimes. Not after you think about columns. First.

Defining Grain: One Row Represents What?

Daily Life

Interviews

Your grain statement answer: 'The grain is a contract. Every row represents exactly one instance of X. If I can write a GROUP BY on the grain columns and get COUNT(*) > 1 for any group, the grain is violated.' Say this, then immediately write the validation query. The interviewer is checking whether you can enforce grain, not just define it.

How Interviewers Grade Your Grain Statement

Watch the progression from vague to interview-grade:

Precision Level	Grain Statement	Verdict
Vague	"It's an orders table"	No hire. What about orders?
Better	"One row per order"	Hire if followed up. But what's an order?
Good	"One row per order line item"	Hire. Shows item-level thinking.
Strong Hire	"One row per order line item per fulfillment event"	Handles partial shipments.
Senior+	"...unique on (order_id, line_seq, fulfillment_id), with a NOT NULL constraint on the composite"	Production-grade design.

The jump from 'good' to 'strong hire' is acknowledging that a line item can have multiple lifecycle events. The jump from 'strong hire' to 'senior+' is naming the enforcement mechanism, not just the concept.

The Validation Move That Signals Production Experience

A grain statement without a validation strategy is just a comment. In production, grain is enforced by uniqueness constraints or dbt tests. In an interview, mentioning either one unprompted is a strong signal.

	/* Grain validation: should return zero rows */
	SELECT
	order_id,
	line_item_seq,
	COUNT(*)
	FROM fact_order_items
	GROUP BY order_id, line_item_seq
	HAVING COUNT(*) > 1

If this returns rows, your grain is violated. In dbt, this is a unique test on the composite key. Mentioning this in an interview shows you have operated models in production, not just designed them on whiteboards.

The Follow-Up Trap

Once you state a grain, the interviewer will probe it. They will introduce a complication that challenges your grain statement. This is not them disagreeing with you. This is the next phase of the test.

Follow-Up #1Follow-Up #2Follow-Up #3

Follow-Up #1

"What if an order is amended after placement?"

They're testing whether your grain handles mutations. Strong answer: 'The grain stays the same, but we need an effective_date or version column to track amendments. The composite key becomes (order_id, line_seq, version).'

Follow-Up #2

"What about returns?"

They're testing whether returns are a new fact table or rows in the existing one. Strong answer: 'Returns have a different grain: one row per return request per line item. They join to the order fact but live in their own table.'

Follow-Up #3

"Now aggregate this to daily revenue."

They're testing whether you understand the relationship between transaction grain and aggregate grain. Strong answer: 'We'd build a periodic snapshot at the (date, product_id) grain that sums from the transaction fact.'

TIP

Never change your grain statement without explicitly saying so. 'That changes the grain. Now one row represents...' shows the interviewer you are tracking the impact of each design decision.

The Fan Trap: Joins That Inflate Metrics

Daily Life

Interviews

Your fan trap answer: 'A fan trap happens when I join two facts through a shared dimension and one side has multiple rows per key. The join fans out, duplicating the other side. My SUM now counts revenue 3x because each order was duplicated once per shipment. No error message, no warning, just wrong numbers that look plausible.' Say 'no error message' explicitly. That is what makes fan traps dangerous and why interviewers test them.

The Tell: Words That Signal This Pattern

Suspect a fan trap when:

▸You're joining two tables that both have multiple rows per join key
▸Aggregated numbers are higher than expected but not obviously wrong
▸A SUM works fine for one table alone but inflates when you join
▸The interviewer says "the revenue numbers look too high"
▸You're joining a fact to a fact through a shared dimension

The Scenario the Interviewer Will Draw on the Whiteboard

The interviewer gives you two tables with different row counts per join key and asks you to combine them. They are not testing your JOIN syntax. They are watching whether you see the fan trap before you write the query. Candidates who join directly and only notice the inflated numbers when asked 'does that total look right?' get a no-hire. Candidates who say 'these are different grains, I need to pre-aggregate first' get a strong hire.

customer_id	order_id	order_amount
alice	O1	$100
alice	O2	$200

customer_id	shipment_id	ship_cost
alice	S1	$10
alice	S2	$15
alice	S3	$12

Now join them on customer_id:

customer_id	order_id	order_amount	shipment_id	ship_cost
alice	O1	$100	S1	$10
alice	O1	$100	S2	$15
alice	O1	$100	S3	$12
alice	O2	$200	S1	$10
alice	O2	$200	S2	$15
alice	O2	$200	S3	$12

Walk through the math: '2 orders x 3 shipments = 6 rows. SUM(order_amount) is now $900 instead of $300. Revenue tripled.' Point at the numbers. The interviewer is checking whether you can spot inflated aggregates. If you can do this arithmetic on a whiteboard in 10 seconds, you pass.

The Answer: Pre-Aggregate Before Joining

	/* Fix: aggregate each fact to the join grain FIRST */
	WITH customer_orders AS (
	SELECT
	customer_id,
	SUM(order_amount) AS total_orders
	FROM orders
	GROUP BY customer_id
	),
	customer_shipments AS (
	SELECT
	customer_id,
	SUM(ship_cost) AS total_shipping
	FROM shipments
	GROUP BY customer_id
	)

	SELECT
	c.customer_id,
	o.total_orders,
	s.total_shipping
	FROM customers AS c
	LEFT JOIN customer_orders AS o USING (customer_id)
	LEFT JOIN customer_shipments AS s USING (customer_id)

dim_customer

customer_idPKBIGINT

fact_orders

order_idPKBIGINT

customer_idFKBIGINT

amountNUMERIC

fact_calls

call_idPKBIGINT

customer_idFKBIGINT

durationINT

The fan trap: joining one customer to TWO facts at different grains (orders and calls) multiplies the rows, inflating SUM(amount). Fix: aggregate each fact to the join grain BEFORE joining.

The principle: never join two fact tables at different grains. Pre-aggregate each to the shared dimension grain first, then join the aggregates. This is the single most important query pattern for avoiding fan traps.

✓Do

Pre-aggregate each fact to the join grain before combining
Use CTEs to make the grain of each subquery explicit
Verify row counts before and after the join

✗Don't

Join two fact tables directly on a shared dimension key
Assume SUM will be correct without checking grain compatibility
Ignore row count differences between expected and actual

What the Interviewer Writes on the Scorecard

•No Hire

Joins the two fact tables directly without noticing the grain mismatch
"We'd just join orders and shipments on customer_id"
Cannot explain why the numbers are inflated when asked

✓Strong Hire

Identifies the fan trap before writing any SQL
"These are different grains, so I'll aggregate each to customer level first"
Mentions that this is a 2xN cartesian product problem

TIP

If an interviewer gives you two tables with different grains and asks you to combine them, the question IS the fan trap. They are watching whether you see it before you write the JOIN.

The Chasm Trap: Missing Relationships

Daily Life

Interviews

Your chasm trap answer: 'A chasm trap is the inverse. Instead of too many rows, I get too few. An INNER JOIN through a sparse dimension silently drops entities that exist on only one side. Bob has orders but no shipments. The INNER JOIN drops Bob entirely. My total orders metric just lost 70% of its value.' Pair this with the fan trap to show you check for both.

The Tell: Words That Signal This Pattern

Suspect a chasm trap when:

▸Row counts are lower than expected after a join
▸"Some customers are missing from the report"
▸You're joining Fact A to Dimension to Fact B, and the dimension is sparse
▸The interviewer asks about customers with orders but no shipments (or vice versa)
▸An INNER JOIN path drops entities that exist in one fact but not the other

The Scenario the Interviewer Will Draw on the Whiteboard

Set up the scenario for the interviewer: 'Orders has alice, bob, carol. Shipments has alice, dave. An INNER JOIN returns only alice. Bob and carol are silently dropped. Dave is silently dropped. Your report shows 1 customer instead of 4.' Walk through it on the whiteboard. The visual makes the problem undeniable.

customer_id	order_amount
alice	$100
bob	$200
carol	$150

customer_id	ship_cost
alice	$10
dave	$25

Narrate the impact: 'INNER JOIN returns only alice. Bob and carol had orders but no shipments, so they vanished. Dave had shipments but no orders, so he vanished. Your total orders metric is now 70% too low. Your total shipping metric is 75% too low. Neither error is obvious without checking the row count before and after the join.'

customer_id	order_amount	ship_cost	status
alice	$100	$10	Returned by INNER JOIN
bob	$200	NULL	DROPPED
carol	$150	NULL	DROPPED
dave	NULL	$25	DROPPED

The Answer: FULL OUTER JOIN or Separate Queries

Two approaches, and knowing which one to reach for and why is the interview signal:

Aggregate each fact to the dimension grain, then FULL OUTER JOIN the results. This preserves all entities from both sides. Best when you need a single combined view.
Run independent queries against each fact table. Combine in the presentation layer. Best when the two facts have fundamentally different semantics and joining them is misleading.
Use COALESCE(orders.customer_id, shipments.customer_id) after a FULL OUTER JOIN to produce a clean key column with no NULLs.

	/* Fix: FULL OUTER JOIN preserves all entities */
	WITH order_totals AS (
	SELECT
	customer_id,
	SUM(order_amount) AS total_orders
	FROM orders
	GROUP BY customer_id
	),
	ship_totals AS (
	SELECT
	customer_id,
	SUM(ship_cost) AS total_shipping
	FROM shipments
	GROUP BY customer_id
	)

	SELECT
	COALESCE(
	o.customer_id,
	s.customer_id
	) AS customer_id,
	COALESCE(o.total_orders, 0) AS total_orders,
	COALESCE(s.total_shipping, 0) AS total_shipping
	FROM order_totals AS o
	FULL OUTER JOIN ship_totals AS s
	ON o.customer_id = s.customer_id

Fan Trap vs Chasm Trap

•Fan Trap

Too MANY rows after join
Aggregates are inflated
Caused by many-to-many through a dimension
Fix: pre-aggregate before joining
Symptom: numbers are too HIGH

•Chasm Trap

Too FEW rows after join
Entities are silently dropped
Caused by INNER JOIN through sparse dimension
Fix: FULL OUTER JOIN or separate queries
Symptom: numbers are too LOW

The strongest interview move: when you see a join between two fact tables, say 'I need to check for both fan traps and chasm traps here.' Naming both unprompted signals deep modeling experience.

Grain as a Communication Tool

Daily Life

Interviews

Everything in this lesson so far has been about grain as a technical concept. This section is about grain as a communication strategy. In an interview, the way you talk about grain determines your level. Junior candidates treat grain as a checkbox. Senior candidates use it as the anchor for every subsequent design decision.

The Bridge Move

Grain is your lever for expanding any modeling question into a system design conversation. Every time you state a grain, you create a natural bridge to discuss: partitioning strategy, storage cost, query performance, and downstream consumers. This is how you get leveled up in the interview loop.

Bridge to PartitioningBridge to CostBridge to ConsumersBridge to SLAs

Bridge to Partitioning

"Given this grain, I'd partition by..."

The grain dictates the natural partition key. Order line items partition by order_date. Event streams partition by event_hour. Saying this unprompted shows you think about physical layout, not just logical design.

Bridge to Cost

"At this grain, we're looking at ~X rows/day"

Estimating row volume from grain shows you think about operational reality. 'One row per click per user per day at 50M DAU is 500M rows/day, so we need columnar storage and partition pruning.' This is senior+ thinking.

Bridge to Consumers

"Who consumes this table?"

Dashboard consumers need aggregate grain. ML feature stores need event grain. Asking who the consumer is before finalizing grain shows you design for use cases, not for schemas.

Bridge to SLAs

"What freshness does this grain support?"

Event-level grain supports near-real-time. Daily snapshot grain means T+1 at best. Connecting grain to freshness SLAs shows you understand operational constraints.

Red Flag Phrases to Avoid

"I'd put everything in one table" - Ignores grain entirely. Signals no dimensional modeling experience.

"We can always aggregate later" - True but misses the point. The question is what the base grain should be.

"The grain is whatever the source system gives us" - Source system grain and analytical grain are often different. Parroting source grain signals you copy data, not model it.

"I'd denormalize everything for performance" - Denormalization is a grain-dependent decision. Saying it before defining grain is backwards.

Vocabulary That Signals Seniority

Junior Phrasing	Senior Phrasing
"Each row is an order"	"The grain is one row per order line item, unique on (order_id, line_seq)"
"We'd join these tables"	"These are at different grains, so I'd pre-aggregate before joining"
"The numbers are wrong"	"This looks like a fan trap from joining at mismatched grains"
"Some rows are missing"	"The INNER JOIN is creating a chasm trap; entities without matches are dropped"
"I'd add all the columns"	"Which attributes are at this grain vs a different grain?"

The Closing Move

At the end of any modeling question, circle back to grain. 'So to summarize: the grain is one row per X, enforced by a unique constraint on (A, B). We pre-aggregate to customer grain before joining to avoid fan traps, and use a FULL OUTER JOIN to prevent chasm traps.' This three-sentence summary hits every rubric item most interviewers are scoring against.

The 3-sentence interview closer for grain:

▸"The grain is one row per X, enforced by a unique constraint on (A, B)."
▸"We pre-aggregate to the join grain before combining facts to avoid fan traps."
▸"We use FULL OUTER JOIN to prevent chasm traps from silently dropping entities."

❯❯❯PUTTING IT ALL TOGETHER

> You are in a Meta data engineering interview. The interviewer asks you to design a fact table for ad impressions and clicks.

You open with: 'The impression fact grain is one row per ad impression event, unique on (impression_id). The click fact grain is one row per click event, unique on (click_id). These are separate facts at different grains.'

When asked to compute click-through rate, you say: 'I'd pre-aggregate impressions and clicks to the (ad_id, date) grain before joining, to avoid a fan trap inflating impression counts.'

When asked about ads with impressions but no clicks, you say: 'That's a chasm trap scenario. I'd use a LEFT JOIN from impressions to clicks so zero-click ads appear with NULL click counts, not get dropped.'

KEY TAKEAWAYS

Grain first, always: state 'one row represents...' before writing any column names

Fan trap: joining two facts through a shared dimension produces a cartesian product that inflates aggregates

Chasm trap: INNER JOIN through a sparse dimension silently drops entities that exist on only one side

Fix fan traps: pre-aggregate each fact to the join grain before joining

Fix chasm traps: use FULL OUTER JOIN with COALESCE, or query facts independently

The bridge: use grain to naturally expand into partitioning, cost estimation, consumers, and SLA discussions

Wrong grain = wrong numbers; fan traps multiply your metrics silently

Category: Data Modeling
Difficulty: advanced
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: "What Is the Grain of This Table?", Defining Grain: One Row Represents What?, The Fan Trap: Joins That Inflate Metrics, The Chasm Trap: Missing Relationships, Grain as a Communication Tool

Lesson Sections

"What Is the Grain of This Table?" (concepts: dmGrainDefinition)
Grain is not a modeling step. It is THE modeling step. Everything else follows from it: which columns belong in the table, which joins are valid, which aggregations produce correct numbers. Getting grain wrong does not produce an error message. It produces wrong numbers that look right. That is why interviewers test it. What They're Really Testing The 60-Second Framework Step 3 is the strong-hire signal. Asking the interviewer a clarifying question about grain shows you have been burned by wrong
Defining Grain: One Row Represents What? (concepts: dmGrainDefinition)
Your grain statement answer: 'The grain is a contract. Every row represents exactly one instance of X. If I can write a GROUP BY on the grain columns and get COUNT(*) > 1 for any group, the grain is violated.' Say this, then immediately write the validation query. The interviewer is checking whether you can enforce grain, not just define it. How Interviewers Grade Your Grain Statement Watch the progression from vague to interview-grade: The jump from 'good' to 'strong hire' is acknowledging that
The Fan Trap: Joins That Inflate Metrics (concepts: dmGrainDefinition)
Your fan trap answer: 'A fan trap happens when I join two facts through a shared dimension and one side has multiple rows per key. The join fans out, duplicating the other side. My SUM now counts revenue 3x because each order was duplicated once per shipment. No error message, no warning, just wrong numbers that look plausible.' Say 'no error message' explicitly. That is what makes fan traps dangerous and why interviewers test them. The Tell: Words That Signal This Pattern The Scenario the Inter
The Chasm Trap: Missing Relationships (concepts: dmManyToMany)
Your chasm trap answer: 'A chasm trap is the inverse. Instead of too many rows, I get too few. An INNER JOIN through a sparse dimension silently drops entities that exist on only one side. Bob has orders but no shipments. The INNER JOIN drops Bob entirely. My total orders metric just lost 70% of its value.' Pair this with the fan trap to show you check for both. The Tell: Words That Signal This Pattern The Scenario the Interviewer Will Draw on the Whiteboard Set up the scenario for the interview
Grain as a Communication Tool (concepts: dmGrainDefinition)
Everything in this lesson so far has been about grain as a technical concept. This section is about grain as a communication strategy. In an interview, the way you talk about grain determines your level. Junior candidates treat grain as a checkbox. Senior candidates use it as the anchor for every subsequent design decision. The Bridge Move Red Flag Phrases to Avoid Vocabulary That Signals Seniority The Closing Move At the end of any modeling question, circle back to grain. 'So to summarize: the