Snowflake does not enforce foreign keys; the pipeline has to

A medium Pipeline Design interview practice problem on DataDriven. Write and execute real pipeline design code with instant grading.

Domain

Pipeline Design

Difficulty

medium

Problem

Snowflake does not enforce foreign keys; the pipeline has to. fct_orders references customer_id values that sometimes do not exist in dim_customer (CDC race condition, soft-deleted dim rows, mid-rebuild windows). The cost is invisible: INNER JOINs silently drop orphan rows; LEFT JOINs preserve rows but produce NULLs in dimension columns. The same orphan produces different wrong answers in two different consumer queries depending on the join shape. The section's pattern is a LEFT JOIN NULL filter to detect orphans, plus a strategy choice (block, quarantine, placeholder, or defer). Find the orphans by adding a referential-integrity check between fct_orders and dim_customer and a quarantine destination for orphan rows.

Practice This Problem

Solve this Pipeline Design problem with real code execution. DataDriven runs your solution and grades it automatically.