dbt Constraints on Snowflake: Enforcing Primary Keys in a Database That Won't
- SaaS & Tech
TL;DR
Snowflake accepts PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints but does not enforce them — they are metadata only, and NOT NULL is the single exception it actually checks at write time. So on Snowflake, dbt tests (unique, not_null, relationships) are your real enforcement layer: they run after the build and fail the pipeline when a key is duplicated or an orphan appears. Declare the constraints anyway — via dbt's native model contracts or the dbt_constraints package — because the optimizer and your BI tools read that metadata, and use the tests to make sure the metadata is telling the truth.
Key takeaway — Snowflake stores primary key, foreign key, and unique constraints but never enforces them; NOT NULL is the only one it checks. That makes dbt tests the actual enforcement layer and constraints the metadata veneer. This post covers what each layer really does — tests, the
dbt_constraintspackage, and dbt’s native model contracts — and how a mid-market team should pick between them.
Somebody adds a PRIMARY KEY to a Snowflake table, ships it, and assumes duplicates are now impossible. Six weeks later a revenue number is 12% high because a fact table has two rows where it should have one, and the primary key on that table has been sitting there the whole time, declared, documented, and completely inert.
This is the most common misunderstanding I see on Snowflake projects, and it is not a knowledge gap — it is a reasonable expectation carried over from every transactional database the team has ever used. Postgres enforces primary keys. SQL Server enforces primary keys. Snowflake accepts the declaration and moves on.
Here is what actually enforces anything, and where dbt fits.
Does Snowflake enforce primary key constraints?
No. Snowflake supports the syntax for PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints, stores them in the information schema, and shows them in the UI — but it does not enforce them. You can insert two rows with the same primary key value into a table that declares one, and every row lands.
NOT NULL is the exception. It is the one constraint type Snowflake enforces at write time: a null in a NOT NULL column fails the statement.
This is not an oversight. Enforcing uniqueness on every write means checking every incoming row against everything already in the table, which is the opposite of what a columnar analytical warehouse is built to do — bulk loads of millions of rows, micro-partitions written independently, no row-level index to probe. Snowflake made the trade deliberately: declare what you want the optimizer and your tools to know, and validate it yourself.
The practical consequence is uncomfortable but clear. On Snowflake, a declared constraint is an assertion about your data, not a guarantee of it. If nothing else is checking, the assertion can be wrong for months.
If Snowflake doesn’t enforce them, why declare constraints at all?
Because three separate things read that metadata, and all three get better when it exists.
Join elimination. When the optimizer knows a join key is unique on one side and no columns from that side survive into the output, it can prove the join changes nothing and skip it. This matters most with the SQL your BI tool generates, which routinely joins in dimensions nobody selected a column from. On Snowflake this behavior depends on the constraint carrying the RELY property — you are telling the optimizer “trust this, I have validated it elsewhere,” which is exactly the promise your dbt tests are keeping.
BI and semantic-layer tools. Most modern BI tools inspect the warehouse’s key metadata to infer relationships between tables when you add them to a model. With primary and foreign keys declared, the tool proposes the right joins. Without them, someone hand-wires every relationship in the tool’s UI, where it is invisible to version control and diverges from the dbt project the first time a model changes.
Documentation that lives where people look. An analyst inspecting a table in Snowflake sees the declared keys. That is a better place for grain information than a wiki page that stopped being true two quarters ago. It pairs naturally with +persist_docs, which writes your dbt model and column descriptions into Snowflake’s own metadata — a pattern worth adopting whether or not you run dbt through Snowflake’s native runner.
So declare them. Just do not confuse declaring with enforcing.
What is the dbt_constraints package, and what does it actually do?
dbt_constraints is a dbt package from Snowflake Labs that closes the gap between “I wrote tests” and “the database knows about my keys.” Its premise is elegant: you already declared the truth about your keys in your tests, so it should not take a second declaration to get the metadata.
The package reads your existing generic tests and issues the matching DDL after the run:
uniqueplusnot_nullon the same column →PRIMARY KEYuniquealone →UNIQUEkeyrelationships→FOREIGN KEYback to the referenced model
So this schema file, which is what you would write anyway on any serious project:
models:
- name: dim_customers
columns:
- name: customer_id
tests:
- unique
- not_null
- name: fct_revenue
columns:
- name: revenue_id
tests:
- unique
- not_null
- name: customer_id
tests:
- not_null
- relationships:
to: ref('dim_customers')
field: customer_id…produces a primary key on dim_customers.customer_id, a primary key on fct_revenue.revenue_id, and a foreign key from fct_revenue.customer_id to the customer dimension — with no constraint configuration written anywhere.
Two things about it are worth knowing before you adopt it.
It hooks into the end of the run. The package’s constraint creation is wired in as an on-run-end hook it registers in its own dbt_project.yml — you install it in packages.yml, run dbt deps, and it fires automatically; there is nothing to configure in your project. Constraints appear after models are built and tests have run — not as part of each model’s DDL. And test results decide what the optimizer gets to trust: tests that pass with zero failures produce constraints with the RELY property, while tests with any failures produce NORELY constraints — the constraint still exists as metadata, but the optimizer is told not to lean on it for join elimination. A failing test can’t quietly hand the optimizer a promise your data isn’t keeping.
It only helps where the platform can hold a constraint. Constraints attach to tables. Models materialized in ways that cannot carry one are skipped, and a foreign key needs the referenced column to already have a key on it — a relationships test pointing at a column with no unique test will not produce a foreign key, because there is nothing to point at. If you adopt the package and half your expected constraints do not appear, that asymmetry is the first thing to check.
The limit to be honest about: dbt_constraints changes nothing about enforcement. It generates metadata from tests. The tests are still what caught the problem. What you gain is that the metadata can no longer drift from the tests, because it is derived from them.
What are dbt model contracts, and how are they different from tests?
Model contracts are the other half of this, and they solve a genuinely different problem.
Recent dbt versions let you declare a contract on a model: the exact columns it produces, their data types, and optional constraints. Turn it on and dbt checks the model’s output against the declaration as part of building it.
models:
- name: fct_revenue
config:
contract:
enforced: true
columns:
- name: revenue_id
data_type: varchar
constraints:
- type: not_null
- type: primary_key
- name: customer_id
data_type: varchar
constraints:
- type: not_null
- name: revenue_amount
data_type: number(38,2)Note what is required here that tests never asked for: every column needs an explicit data_type. That is the point. A contract is a promise about shape, and shape includes types.
The distinction that matters:
| What it checks | When it fires | |
|---|---|---|
| Tests | The data — duplicates, nulls, orphans | After the model is built |
| Contracts | The structure — columns, types, order | While the model is built |
| Constraints | Nothing, on Snowflake, except not_null | — |
A contract catches the failure mode tests are blind to: someone edits a model, a CAST disappears, revenue_amount starts arriving as a float instead of a fixed-precision number, every test still passes, and the rounding behavior of a downstream calculation quietly changes. Tests assert things about rows. Contracts assert things about the table.
And the part people get wrong: on Snowflake, a contract’s primary_key constraint is still not enforced. dbt renders it into the DDL, Snowflake stores it, and Snowflake never checks it. not_null is enforced, because Snowflake enforces not_null. Setting enforced: true on the contract enforces the contract — the columns and types — not every constraint listed inside it. (Constraints are only rendered at all when the contract is enforced, so you cannot get the metadata without the shape check.)
If your mental model is “contracts make Snowflake behave like Postgres,” reset it. Contracts make dbt refuse to ship a model whose shape changed.
Should I use dbt contracts or dbt tests?
Both, for different models, in this order.
Tests on every key, always. unique and not_null on every primary key, relationships on every foreign key. This is the floor, not the ceiling, and it is non-negotiable on financial data — a duplicated grain row is the bug that produces a plausible-looking wrong number, which is far more expensive than an obvious one. The four categories of dbt tests go well past this, but nothing in that catalog matters if the key tests are missing.
For compound keys, unique on a single column is not enough. Use dbt_utils.unique_combination_of_columns — the grain of a fact table is usually two or three columns, and a per-column uniqueness test on a compound grain asserts something that is simply false.
dbt_constraints when tools or the optimizer will read the metadata. If your BI tool infers relationships from warehouse metadata, or you have joins the optimizer could eliminate, the package pays for itself immediately — it is close to free, since you already wrote the tests. If nothing downstream reads the information schema, it is a nice-to-have.
Contracts on the models other people depend on. Not all of them. The marts other teams query, the tables a reverse-ETL job or a downstream application reads, the models that feed an executive dashboard. These are the models where a silent type change is expensive and where the declaration cost — a data_type on every column, kept current — is worth paying. Putting contracts on every staging model in a hundred-model project buys you very little and adds real maintenance drag every time a source column changes.
That layering falls out of the same principle as collapsing 377 legacy BI objects into 51 dbt models: the strictness belongs where the blast radius is, not spread evenly across everything.
What does enforcement actually look like in production?
The honest answer is that it looks like dbt build and a CI job, not like a constraint.
dbt build interleaves models and tests in dependency order — each model is created, then its tests run, and a failure stops the models downstream of it. That is the enforcement mechanism. A duplicate primary key does not silently propagate into six marts; it fails at the model that introduced it and the marts do not get rebuilt.
Two things make this real rather than theoretical:
Key tests are error severity, not warn. A warning in a CI log that nobody reads is not enforcement. If a primary key test can fail without stopping a deploy, you do not have a primary key — you have a preference.
Use --store-failures on the key tests. When a uniqueness test fails, the useful question is immediately “which rows?” Persisting failures to a table turns a red CI check into a query you can run in fifteen seconds, and that gap is most of what determines whether the fix happens today or next sprint.
On the migration behind our analytics platform modernization work — 377 legacy BI objects reduced to 51 dbt models in 45 days — the reconciliation was validated to 0.002% variance. None of that came from a constraint Snowflake was checking. It came from tests running on every build, plus reconciliation against an independent reference. The constraints described the model. The tests defended it.
Where teams get this wrong
Declaring keys and skipping the tests. The worst of both worlds: the metadata asserts a guarantee, the optimizer may act on it, BI tools build relationships on it, and nothing is verifying it. An unvalidated RELY constraint is not a documentation gap — it is a promise to the optimizer that you have not kept, and the failure mode is wrong results rather than an error.
Adding contracts everywhere at once. Contracts require a data_type on every declared column. Retrofit them across a large project in one pass and you have committed to maintaining a type declaration for every column in the warehouse. Start with the models that have consumers outside your team.
Treating a passing test suite as proof the keys are right. A unique test on order_id passes cheerfully when the real grain is order_id + line_item_id and you have exactly one line per order today. It fails the first day a customer orders two things. Test the grain you actually have, not the grain that happens to hold in the current data.
The short version
Snowflake will accept any primary key you declare and enforce none of them. That is not a problem to route around — it is the design of the platform, and it is fine, provided you know which layer is doing the work.
Tests are the enforcement layer. dbt_constraints turns those tests into metadata the optimizer and your BI tools can use, without a second source of truth. Contracts protect the shape of the models other people depend on. Constraints, on their own, protect nothing.
Write the tests first. Everything else is a decision about how loudly to publish what the tests already know.
None of this changes with your dbt tier — the license question is priced out separately in dbt Cloud vs dbt Core: What Mid-Market Teams Actually Pay.
Migrating to dbt on Snowflake and unsure which layer of enforcement you actually need? We’ve done this on production financial data. Book a quick consultation.
Frequently asked questions
How do I enforce primary keys in Snowflake with dbt?
You enforce them with dbt tests, not with constraints. Add a unique test and a not_null test to the key column in your schema YAML, and run dbt build so the tests execute against the model right after it is created. If the key is duplicated or null, the build fails and downstream models do not run. The PRIMARY KEY constraint itself — whether you declare it through a dbt model contract or generate it with the dbt_constraints package — is metadata that Snowflake stores but never checks, so it cannot be your enforcement mechanism.
Does Snowflake enforce primary key constraints?
No. Snowflake lets you declare PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints, and it stores them in the information schema, but it does not enforce them — you can insert duplicate keys into a table with a declared primary key and Snowflake will accept every row. NOT NULL is the one constraint type Snowflake actually enforces at write time. This is a deliberate design choice for an analytical warehouse: enforcing uniqueness on every write would require a check that does not scale the way columnar bulk loads need to.
What is the dbt_constraints package?
dbt_constraints is a dbt package from Snowflake Labs that turns your existing dbt tests into database constraints. It reads the tests you already declared — unique plus not_null on a column becomes a PRIMARY KEY, unique alone becomes a UNIQUE key, and a relationships test becomes a FOREIGN KEY — and issues the corresponding ALTER TABLE statements after your run finishes. The appeal is that you write no new configuration: the tests you were going to write anyway also produce the metadata that BI tools and the query optimizer read.
Should I use dbt contracts or dbt tests?
Use both, for different jobs. Tests validate data — is this key unique, does every foreign key resolve, is this column ever null — and they run after the model is built. Contracts validate structure — does this model still produce the columns and data types it promised — and they fail the build before bad shape reaches production. A contract will not catch a duplicate primary key on Snowflake, and a test will not catch a column that silently changed from a string to a number. Mid-market teams should start with tests on every key, and add contracts to the handful of models other teams or systems consume.
Do dbt model contracts enforce constraints on Snowflake?
Partially. When you set contract enforced to true and declare constraints on a model, dbt renders that constraint into the DDL — but Snowflake only enforces the not_null constraint. primary_key, foreign_key, and unique constraints are created as metadata and never checked. What the contract does enforce, on every platform, is the column list and the data types: if your model stops producing a column the contract declares, or produces it with a different type, the build fails before the model is replaced.
Why declare primary keys in Snowflake if they are not enforced?
Three reasons. The query optimizer can use reliable key metadata to eliminate joins it can prove are unnecessary, which removes work from queries your BI tool generates. BI and semantic-layer tools read primary and foreign key metadata to infer table relationships, so declared keys mean less manual relationship modeling in the tool. And declared keys are documentation that lives in the database rather than a wiki, visible to every analyst who inspects the schema.