Why does a warehouse dimension carry an 'Unknown' member row instead of leaving the fact's key NULL?
answer
- what does an inner join do with NULL?
- totals shrink and nobody is told
- reserve a fixed key such as −1
- give the dimension a row to point at
basics
~20 sA NULL dimension key drops the fact row from every inner join to that dimension, silently changing totals. A reserved Unknown member row keeps the fact joinable, so measures still add up and the missing value shows as its own visible bucket.
solid answer
~50 sFacts should never carry a NULL dimension reference. If they do, every inner join to that dimension quietly discards those rows, so revenue reported by customer segment is lower than revenue reported overall and nobody can tell why. The fix is to pre-load the dimension with reserved rows at fixed key values — conventionally −1 for *Unknown*, −2 for *Not applicable*, −3 for *Pending* or *Late arriving* — and have the load resolve every business key, sending anything that does not match to the reserved key. Now the fact row survives the join, the total reconciles with the source, and the report shows an explicit `Unknown` line that an analyst will actually notice and ask about. It also gives you a monitoring signal: the share of facts on the unknown member is a data-quality metric you alert on when it jumps.
go deeper
Remember that an inner join silently discards rows whose join key is NULL, so a fact with a missing dimension reference disappears from grouped reports while still counting in the grand total.
Explain the reserved-key convention, why the row must exist before any fact load, and how the load resolves an unmatched business key with a left join plus a fallback rather than dropping it.
Show the operational layer: separating unknown from not-applicable, alerting on the share per load, and recognising when the unknown member is quietly masking a failed dimension build.
Own it as a warehouse-wide convention — fixed sentinel values across every dimension, a NOT NULL rule on fact keys, and a data-quality metric definition the business agrees to act on.
## The failure the unknown member prevents Suppose a fact table has 1,000,000 order rows and 3% of them have no resolvable customer. Store NULL in the customer key, and this happens: ```text SELECT SUM(amount) FROM fct_order; -> 10,000,000 SELECT c.segment, SUM(o.amount) FROM fct_order o JOIN dim_customer c USING (customer_sk) GROUP BY c.segment; -> totals 9,700,000 ``` The two numbers disagree and nothing in the output says why. An inner join drops rows with a NULL key, and a NULL never equals anything, so even an equality predicate on the dimension excludes them. The report is not wrong in a way anyone can see — it is just quietly 3% short, and the discrepancy surfaces weeks later when finance reconciles. With an unknown member the same query returns 10,000,000 with a visible `Unknown` segment holding 300,000. The error becomes a row in the output instead of a missing row. ## Reserved keys Seed each dimension, at creation time, with a small set of rows at fixed, negative (or otherwise obviously artificial) key values, and keep the values identical across every dimension so the convention is memorable: ```sql INSERT INTO dim_customer (customer_sk, customer_id, customer_name, segment) VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown'), (-2, 'N/A', 'Not applicable', 'Not applicable'); ``` Every descriptive attribute gets a filled-in label rather than NULL, so a BI tool shows the word `Unknown` on the axis instead of a blank. The load then does the resolution: ```sql SELECT s.order_id, COALESCE(c.customer_sk, -1) AS customer_sk, s.amount FROM stg_order s LEFT JOIN dim_customer c ON c.customer_id = s.customer_id; ``` Note the `LEFT JOIN` plus `COALESCE`: an inner join here would drop the fact at load time, which is the same bug moved earlier. ## Unknown is not the only reason a key is missing Distinguishing the reasons is what separates a careful model from a sloppy one, because they mean different things to a business reader: - **Unknown** — the source provided a value but it does not match any dimension member, or provided nothing where a value was expected. This is a data-quality problem. - **Not applicable** — the dimension legitimately does not apply to this fact. A refund has no shipping carrier; that is not an error and should not be counted as one. - **Pending / late arriving** — the entity exists but its dimension row has not loaded yet and is expected shortly. Collapsing all three into one member means you cannot tell a broken feed from normal business. Splitting them means a data-quality dashboard can alert on the first and ignore the second. (The related pattern where a placeholder row is created for a specific business key and later filled in with real attributes belongs to the late-arriving-dimension discussion; the reserved unknown member is a single shared row, not one row per missing entity.) ## Rules - **The reserved rows must exist before any fact loads.** Create them in the same migration that creates the dimension, not in the load job. - **Fix the key values and never reuse them.** −1 must mean the same thing in every dimension in the warehouse, otherwise the convention costs more than it saves. - **Make the surrogate key column NOT NULL on facts.** With the unknown member in place there is no legitimate reason for NULL, and the declaration turns a modelling rule into something a test can check. - **Fill every attribute on the reserved rows** with a readable label, including dates and numeric attributes, so no downstream `GROUP BY` produces a blank. - **Monitor the share.** Track the percentage of facts on the unknown member per load. A jump usually means the source changed a key format or the dimension load failed. ## When people get it wrong Two misuses recur. The first is using the unknown member to hide a broken load: the number goes up, the reports still balance, and nobody investigates because nothing errored — which is why the alert on the share matters as much as the member itself. The second is treating *not applicable* as *unknown*, so a data-quality metric is permanently non-zero for structural reasons and everyone learns to ignore it. There is also a legitimate opposing case worth mentioning in an interview: if a dimension is genuinely optional for a fact and consumers only ever outer-join to it, a NULL is honest and an unknown member adds ceremony. That case is rarer than it looks, because you rarely control how everyone downstream writes their joins — which is the real argument for the unknown member. It makes the model robust to the joins other people write.
- Why is Not applicable usually a different reserved member from Unknown?Because they mean opposite things to a reader. Unknown is a data-quality defect worth alerting on; not applicable is normal business — a refund has no shipping carrier and never will. Collapsing them makes your unknown-share metric permanently non-zero for structural reasons, so a real breakage no longer stands out against the baseline.
- Why should the reserved rows have real text in every attribute rather than NULLs?Because report axes, filters and GROUP BY output render those attributes directly. A reserved row full of NULLs produces blank labels and empty filter entries, which readers interpret as a broken report rather than as missing source data. Fill every column — including dates and numeric attributes — with an explicit sentinel label.
- How would you detect that the unknown member is masking a broken load?Track the share of fact rows on the unknown key per dimension per load and alert on change, not on presence. A stable 0.3% is business as usual; the same metric hitting 15% overnight means a source key format changed or the dimension load failed, and without that alert the reports still balance so nobody notices.
It is the difference between a survey response left blank and one that ticks a box marked 'prefer not to say'. Both are missing data, but only one of them still counts as a response.
saying these in an interview costs you the question
- Says a NULL dimension key is fine because reports use outer joins
- Adds the unknown row inside the load job instead of at creation
- Uses one reserved member for unknown, not-applicable and pending alike
- Leaves the reserved row's attributes NULL, producing blank report labels
- Treats a rising unknown share as normal because totals still reconcile