What is the grain of this table, and how would you verify it?
Whether the candidate can state a grain precisely, verify it rather than assume it, and recognise the specific way a mixed grain produces a confidently wrong number.
The situation behind the question
Interviewers ask this because it happened to them.
A candidate is shown fct_orders and asked what one row represents. The table has an order_id, a line_item_id, an amount, and a shipment_id, and an analyst has been summing amount grouped by customer.
A strong answer
Flags
Green flags
- Defines grain as a uniqueness claim and verifies it with a query rather than trusting the name.
- Spots that an additive measure at the wrong grain produces a wrong answer with no error anywhere.
- Wants the grain declared and tested as part of the model, not documented separately.
- Distinguishes measures that are additive at this grain from ones that are not.
Red flags
- Reads the grain off the table name and moves on.
- Sums a measure without asking whether it is additive at the grain being grouped.
- Believes
SELECT DISTINCTis a fix for a fan-out rather than a symptom of one. - Cannot say what a fan-out join does to a
COUNT(*).
Follow-ups
Where the conversation goes if the first answer holds up.
- Write the assertion that would fail if the grain were violated tomorrow.
- You need both order-level and line-level measures. How do you model that?
- A dimension has overlapping validity ranges. What does joining to it do to your grain?