Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Null foreign keys in fact tables

Data modeling · Fact Table Design

Null foreign keys in fact tables

Easydata-modeling-41
foreign-keyssentinel-valuesdata-qualitydimensional-modeling

Question

What do you put in a fact table's foreign key when the dimension value is unknown?

Solution

Never leave a foreign key column as NULL in a fact table. Instead, point unknown or missing references to standard sentinel records in the corresponding dimension table, such as -1 for Unknown, -2 for Not Applicable, or -3 for Late Arriving records.

Why NULL foreign keys hurt queries

In standard SQL, inner joins automatically discard rows where the foreign key is NULL. If an order row has a NULL customer key, a query joining fact_orders to dim_customer silently drops that order from the result set:

-- Orders with NULL customer_key vanish from this calculation:
SELECT c.region, SUM(f.amount)
FROM fact_orders f
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY c.region;

A short explanation of join behavior:

  • Dropping rows leads to understated financial totals on dashboards.
  • Using outer joins everywhere creates performance overhead and complicates BI tool data models.

Sentinel records in dimensions

Every dimension table should include pre-seeded default rows with negative primary keys:

dim_customer
customer_key | customer_name   | customer_segment
-1           | Unknown         | Missing or unmapped source data
-2           | Not Applicable  | Guest checkout or non-registered buyer
-3           | Late Arriving   | Transaction arrived before customer profile

A review of each sentinel purpose:

  • -1 handles records where the source system provided corrupted or missing values.
  • -2 represents valid business cases where the dimension does not apply, such as corporate accounts without an individual customer profile.
  • -3 flags transactions that arrived before the upstream system published the master dimension record.

Late-arriving dimension workflow

When a transaction references a customer that does not yet exist in dim_customer, the ingestion pipeline inserts the fact row with customer_key = -3. When the customer record eventually arrives in the master data feed, a reconciliation job creates the dimension row and updates the historical fact record with the newly assigned surrogate key.

PreviousNext