Snowflake does not enforce foreign keys; the pipeline has to
A medium Pipeline Design mock interview question on DataDriven. Practice with AI-powered feedback, real code execution, and a hire/no-hire decision.
- Domain
- Pipeline Design
- Difficulty
- medium
Interview Prompt
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.
How This Interview Works
- Read the vague prompt (just like a real interview)
- Ask clarifying questions to the AI interviewer
- Write your pipeline design solution with real code execution
- Get instant feedback and a hire/no-hire decision