Referential Integrity Spot-Checks Without Foreign Key Constraints

Every major cloud warehouse records foreign key relationships without checking them. This single fact forces a redesign of how referential integrity gets protected, moving the work from the engine to the query layer, the pipeline, and the team that owns the data.

Foreign key constraints as metadata in cloud warehouses

Snowflake, BigQuery, Redshift, and Databricks all accept FOREIGN KEY syntax in their DDL and record it in the catalog, but none of them check it when a row gets written for standard tables. Snowflake's Hybrid Tables are the exception, enforcing foreign key constraints where standard Snowflake tables do not. The lakehouse formats built on top of these systems follow the same logic: Iceberg, Delta, and Hudi treat primary key and foreign key constraints as informational metadata only, while Delta Lake does validate NOT NULL and CHECK constraints at write time. So the gap is specific: it applies to relationships between tables, not to every kind of constraint a table can carry.

Two forces locked this pattern into place. The first is performance. Enforcing a foreign key means looking up the parent row before every insert, update, or delete, and that cost runs against the grain of a warehouse built to load data in bulk rather than one row at a time. The second is architectural. Modern systems split data across services and databases, and a foreign key constraint cannot reach across that boundary by definition, so the job of keeping references intact moves up to the application writing the data. Put those two forces together: the FOREIGN KEY clause in a warehouse's DDL tells the query optimizer how tables relate, so it can plan joins more efficiently. It does nothing to stop a fact row from pointing at a dimension key that was never loaded. The warehouse will take that row without complaint.

That leaves one decision for anyone building on these systems: if the engine will not check the reference, something else has to.

The limits of application-layer and pipeline-layer enforcement

Moving integrity checks into application code or pipeline logic recovers most of what a database constraint used to guarantee, but one gap cannot be closed at that level no matter how careful the code is. An application can check that a parent key exists right before it writes a child row, but it has no way to see what every other transaction in the system is doing at that same instant. A parent row that passes the check can be deleted a moment later by a separate process before the child row finishes committing. Or a new child row can be inserted pointing at a parent that's in the middle of being deleted, while the delete logic is still confirming it's safe to proceed. A database engine running the check itself can see every concurrent transaction and settle these races correctly. An application sitting outside the database cannot, and that's the one thing engine-level enforcement does that nothing else fully replicates.

Pipeline-layer checks run into a different problem: timing. A scheduled test runs after data has already landed, so a broken reference that lands in a staging table has time to spread into downstream models before anyone looks for it. Application-side checks also tend to fragment across a warehouse's ingestion paths. Bulk loads, streaming inserts, manual uploads, and third-party connectors each write into the same tables through different code, so a guard built into one path leaves the others unprotected. Full table reloads show this clearly: a pipeline may finish loading a child table while the parent table sits empty mid-reload, which makes write-time enforcement impractical at that moment even when the technology to do it exists.

Hootsuite's move to microservices is a useful real case here. The engineering team never adopted foreign keys in the first place, an early decision made for maintenance and scalability reasons, and that choice is what made the later migration manageable. But removing constraints did not remove the need for integrity. It shifted that need onto compensating logic written into the application, which is the trade every team makes when it steps away from engine-enforced constraints.

None of this means pipeline-layer checks are inadequate. It means they have to be deliberate and thorough to do the job a constraint used to do for free. The rest of this piece maps out what deliberate and thorough looks like in practice.

What silent referential violations cost when they go undetected

A broken reference that goes unnoticed does not stay put. It spreads into every downstream model, every report, and every workflow built on the assumption that the relationship held, and by the time someone notices, the error has multiplied across everything built on top of it.

CRM and RevOps data carries a disproportionate share of this risk, because sales pipelines, account records, and customer hierarchies depend on foreign key relationships that rarely get engine-level protection. A large share of companies report annual revenue losses exceeding ten percent tied specifically to CRM data decay, a scale of damage that makes referential integrity in these systems a financial issue well before it becomes a technical one.

Machine learning raises the stakes further. A model trained on data where referential integrity has already broken down does not just underperform. It learns the broken relationships as if they were real and repeats that error in every prediction it makes afterward. Gartner's own forecast, that a significant share of generative AI projects will be abandoned after proof of concept because of poor data quality, weak risk controls, rising costs, or unclear business value, points straight at this failure path. A real-time 3D content platform lived out a version of this at scale: a data quality problem that began with bad data ingested from a large customer worked its way into the training sets behind its advertising models, and the resulting failure cost the company $110 million and drove a large drop in its stock price.

The cost curve on these failures only gets steeper with time. A violation caught where it enters the pipeline is cheap to fix. The same violation caught after it has worked its way through several transformation layers and into a dashboard or a model costs far more, and most of that cost is just the work of tracing the error back to where it started before anyone can begin fixing it.

The LEFT JOIN NULL pattern: the workhorse query for finding broken references

The standard substitute for a foreign key constraint is a query, not a piece of software. A LEFT JOIN between the child and parent table, filtered down to the rows where the parent side comes back empty, surfaces every broken reference in a table, and any engineer who knows SQL can write and schedule it today.

The structure is simple:

SELECT child.*
FROM child_table AS child
LEFT JOIN parent_table AS parent
  ON child.foreign_key_id = parent.primary_key_id
WHERE parent.primary_key_id IS NULL;

The logic is simple: join child to parent on the key, filter for the rows where the parent side returned nothing. Orders carrying customer IDs that don't exist in the customer table are the textbook case, and this is the exact pattern most data observability checks use to find them. Every row the query returns is a reference the warehouse would have accepted without checking, even though it would have been rejected under enforced foreign keys.

A few variations matter depending on the situation. A NOT EXISTS subquery does the same job and often reads more clearly when the check spans multiple schemas or databases, and it tends to perform better when the parent table is large enough that a full join gets expensive. A NOT IN version is quicker to write but carries a trap: if the subquery returns even one NULL value, NOT IN returns no rows at all, silently hiding every violation in the table. NOT EXISTS or the LEFT JOIN form avoid that failure mode entirely, so either is the safer default. Soft-deleted or archived parent rows need their own handling too: a child key can point at a parent row that technically still exists but has been marked inactive, so the check needs a second condition to catch these quieter orphans.

Scale changes the economics of running these checks. A full LEFT JOIN across a billion-row fact table on every pipeline run gets expensive fast, and sampling the child table with TABLESAMPLE or a random draw can surface a violation rate using a fraction of the compute, at the cost of completeness. That trade-off is often worth making for monitoring purposes, even when a full check is still warranted before a release or a major report.

Where to place spot-checks in the pipeline

Where a check sits in the pipeline decides whether it catches a violation before it does damage or long after. Four positions are available, and each trades something for something else.

Checking at ingestion catches a violation as early as possible, before or right after raw data lands in staging. But this position can block an entire load over a single bad record, which makes it the wrong default for bulk ingestion, where dimension rows often arrive later than the facts that reference them. Checking post-staging and pre-transformation is the practical sweet spot for most pipelines: it surfaces violations before they reach fact tables or aggregated models, without holding up the raw load itself. Checking post-transformation catches a different class of problem, violations introduced by the transformation logic itself, like a wrong join key or a broken surrogate key mapping, and works as a complement to the pre-transformation check rather than a replacement for it. Scheduled audit queries sit outside the pipeline entirely, running on their own cadence, which makes them useful for tables that don't rebuild often and for catching violations that built up before any check existed.

The more consequential decision is what happens when a check fails. Failing the entire load over one bad row is often the wrong default for analytical pipelines, where a few late-arriving records are normal rather than exceptional. The quarantine pattern handles this better: route the offending rows into a separate quarantine table along with the reason they failed, let the main pipeline keep running, and resolve the quarantine on its own schedule. A common version of this uses an unknown-member surrogate key, a placeholder dimension row, to absorb late-arriving or unresolvable foreign keys so the morning's reporting isn't held hostage by one broken record while the violation is still fully visible to whoever needs to fix it.

A check that fails silently accomplishes nothing. Violations need to route to whoever owns the data, not a general monitoring inbox, and the alert needs enough detail, which rows, which keys, which pipeline run, that someone can act on it without having to reconstruct the context first.

Check constraints and triggers as write-time guards where the engine permits them

On engines that still support real write-time enforcement, SQL Server among them, check constraints and triggers can approximate what a foreign key constraint does, though with limits worth knowing before relying on them.

The standard SQL Server approach wraps a lookup against the parent table inside a user-defined function, then binds that function to the child table through a CHECK constraint. This stops an insert of a child row whose key doesn't exist in the parent table, which is the core behavior a foreign key provides at insert time. But the approach has a real gap: CHECK constraints never run during a DELETE, so a parent row can be removed even while child rows still point at it, and there's no way to get cascade behavior out of this setup. A second limitation appears during maintenance: if the function is tied to an active constraint, it can't be changed in place. The constraint has to be dropped first and recreated once the function is updated.

An INSTEAD OF DELETE or AFTER DELETE trigger on the parent table can reject a delete outright or cascade it down to the child rows when references still exist. Triggers give more flexibility than check constraints, but they cost more to maintain, and trigger logic has a habit of becoming a hidden dependency that breaks quietly the next time someone changes the schema.

One older design isn't something most teams can deploy today, but it's worth knowing. An IBM patent describes a constraint mode where rows that violate a rule aren't rejected outright but stored and marked hidden, kept out of normal query results until they're resolved through a separate utility operation, all without stopping the load, a direct precedent for the quarantine pattern described earlier and proof that the idea of separating "detected" from "rejected" has deep roots in database design.

Composing dbt tests, data observability tools, and scheduled SQL queries into a spot-check regime

A working spot-check regime layers several techniques together, each catching a different class of violation at a different point in the pipeline, because no single technique covers every table in a warehouse.

dbt tests work well as the checkpoint at build time. Referential integrity tests written into a dbt project run as part of the build, surface violations before a model gets promoted, and fold naturally into the same CI/CD process that already governs the rest of the codebase. That turns an integrity check into part of the deployment gate rather than a separate system someone has to remember to run.

Not every table in a warehouse goes through dbt, though. Operational tables, dimensions loaded from outside systems, and CRM-synced objects all sit outside a typical dbt graph, and these need their own scheduled queries running the LEFT JOIN NULL pattern on a fixed cadence. CRM and RevOps tables deserve particular attention here, given how much financial exposure sits behind broken account and pipeline relationships, and layering dbt tests, scheduled audits, and observability tooling together gives these tables coverage at build time, on a schedule, and in between.

Put together, these three layers do the job a foreign key constraint used to do automatically: dbt tests catch what the transformation layer introduces, scheduled SQL audits catch what enters outside that layer, and observability tooling watches for drift across both. None of it runs by accident the way engine enforcement once did. It has to be built, scheduled, and owned, but done properly, it closes the gap the warehouse left open.