# Snowflake does not enforce foreign keys; the pipeline has to

Canonical URL: <https://datadriven.io/problems/snowflake-does-not-enforce-foreign-keys-the-pipeline-has-to-b39472e1>

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.

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/snowflake-does-not-enforce-foreign-keys-the-pipeline-has-to-b39472e1)
- [System Design Interview Questions](https://datadriven.io/data-engineering-system-design)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.