dbt Constraints v1.0.10

A bug fix release. Two fixes change what the package does at runtime, so read the upgrade notes below.

No constraints were created at all on 1.0.8

Two clauses in create_constraints_by_type read meta.materialized with a literal
default of "other". Almost no node sets that property, so both clauses read "other",
which is in neither tuple they test, and both were therefore always true.

One of them set verify_permissions for every model, so the ownership pre-check ran
before every constraint. That check does not reliably report OWNERSHIP for a table that
the same run just replaced, so the package skipped the constraint. If you upgraded to
1.0.8 and your constraints stopped appearing, this is the cause.

The other clause excludes views, ephemeral models and dynamic tables. It sits in an or
inside an inclusive filter, so an always-true operand made the whole exclusion dead and
those nodes passed.

meta.materialized is an override, not an independent property. A custom materialization
uses it to declare the kind of object it builds. The package now reads it first and falls
back to config.materialized, through a new effective_materialization macro that both
call sites share. Fixes #131.

Tests that set the where config produced invalid SQL

dbt replaces the model with (select * from x where ...) dbt_subquery when a test sets
the where config, and that text already carries an alias. The test SQL read
from {{model}} pk_test, so the query held two aliases and the database rejected it with
a syntax error on the package's own alias. Primary key, unique key and foreign key tests
were all affected.

The test SQL now selects from the model through a subselect, so the test supplies the only
alias at its own level. The parent relation in the foreign key test is unchanged, because
the where config never rewrites it. Fixes #100, a regression from 1.0.6, and #119.

Constraints on sources

dbt_constraints_sources_enabled never worked. The package resolved test dependencies
against graph.nodes, but dbt keeps sources in graph.sources, so every source
dependency resolved to nothing and no DDL was issued. Dependencies now resolve from
graph.nodes and fall back to graph.sources. A test on a source has no
attached_node, so the package accepts a test that depends on exactly one node and
reads the table name from identifier.

If you already set dbt_constraints_sources_enabled: true, the package starts
creating constraints on your source tables after you upgrade. Confirm the dbt role
should alter those tables. Set the variable to false to turn source constraints off.

Deadlocks on databases that enforce constraints

Postgres and Oracle deadlocked under a parallel run. Dropping a constraint with CASCADE
also locks every table whose foreign key depends on it, and drop_relation did that on
each worker thread, so two threads could take the same locks in opposite order. The
previous mitigation was itself one of the deadlock participants. Ordering cannot fix it,
because a foreign key does not imply a ref(), so dbt has no ordering guarantee between
the two models a foreign key joins.

The fix is timing. A new on-run-start hook calls release_constraints_for_rebuild(),
which drops the constraints on the relations the run will drop or replace, from a single
session, before the worker threads start. Same DDL against the same constraints. Seeds
are always included, because a seed without a full refresh is TRUNCATEd, which needs the
constraints gone just as much as a drop does.

This adds an on-run-start hook to the package. Adapters opt in by implementing
release_constraints_for_rebuild; the default does nothing, so Snowflake, Redshift,
BigQuery and Vertica are unaffected. Set dbt_constraints_release_before_build: false
to drop constraints during the build as before.

Verified at 64 threads: postgres and oracle both clean, no ORA-00060.

Faster constraint generation on large projects

test_selected ran once per test, and each call looped over every node in the graph to
find the unique and primary keys behind a selected model's foreign keys. That is
O(n squared) and it was slow on large projects. The foreign key model list is now built
once per create_constraints_by_type call and reused, so test_selected does a dict
lookup. Contributed by @RichardGoerlerKS in #128.

Other bug fixes

• #85 Foreign key creation raised UndefinedError when the parent table was missing.
  The guard's else-branch read fk_table_relation.is_table on a None relation and
  pk_model.is_table on a manifest node that has no such attribute.
• #115 Constraints were silently skipped on versioned models. The identifier was built
  from node.alias or node.name, giving my_model where the database has my_model_v1,
  so adapter.get_relation() returned nothing. The package now prefers
  node.relation_name when node.version is set.
• #117 lookup_table_privileges had no dispatch wrapper, which broke projects that
  call dbt_constraints.create_constraints() from their own on-run-end hook, a pattern
  from before 0.6.3.
• #107 Generated constraint names only stripped double quotes, so a column containing
  parentheses, spaces, dots or dashes produced invalid DDL such as
  ADD CONSTRAINT TBL_OBJECTID(SMTH)_PK. A new sanitize_constraint_name() helper
  uppercases, strips quotes, and replaces every character outside [A-Z0-9_$] with an
  underscore. It runs before the postgres and oracle length truncation, so the truncated
  identifier is always valid.
• #110 Restored the RELY/NORELY flip on constraints that already exist on Snowflake.
• #120 Corrected macro argument types in macros.yml for dbt Fusion compatibility.
• #130 dbt 1.12 could not parse the package. macros.yml used the argument type
  boolean, which is not in the accepted grammar, and five macros documented fewer
  arguments than they declare and in a different order. dbt 1.12 validates all of that,
  and a project with warn_error_options set to error: all failed at parse time. Types
  are now bool, and verify_permissions, lookup_cache and rely_clause are
  documented in declaration order.

Documentation

The README listed the supported databases in two places and both were wrong. BigQuery was
missing from the compatibility line, and the adapter-porting section named only Snowflake,
PostgreSQL and Oracle while claiming seven required macros where there are eight. Both
lists now name all six supported adapters, and the optional
release_constraints_for_rebuild macro is documented.

The test examples now use the arguments: property that dbt-core 1.10.5 and later, dbt
2.x and dbt Fusion expect. One nested example was invalid YAML because arguments had no
colon. A new section shows the older form and names the versions that need it, because the
package still accepts both. The eight adapter macro signatures were also wrong: they used
parameter names the macros do not declare, omitted rely_clause, and create_not_null
listed the foreign key parameters of a different macro.

Testing

The integration suite now runs dbt inside Snowflake through dbt Projects on Snowflake, as
two new matrix cells against a deployed DBT PROJECT object: dbt Core 1.11.11 and Fusion
2.0.0-preview.186, the versions SYSTEM$SUPPORTED_DBT_VERSIONS() returns. Host cells keep
snowflake 1.5.12 as the backward-compatibility floor, plus Fusion 2.0.0rc212 and Core
2.0.0b2. A shared baseline build cut each cell from roughly 50 dbt invocations to 8.

SQL Server is out of the test matrix. The package has no sqlserver__create_constraints
and no default__, so a SQL Server run dies at macro dispatch and the cell could never
have passed.

The two test projects are now chosen by dbt version rather than by engine.
integration_tests/dbt-core becomes dbt-legacy-syntax and dbt-fusion becomes
dbt-current-syntax, because the split follows the schema.yml syntax and not the
engine. dbt-core added the arguments: property in 1.10.5, so every cell at that version
or later now runs the current-syntax project, and recent dbt-core 1.x runs the same tests
as dbt 2.x and Fusion. Only snowflake 1.5.12 still runs the legacy project, which keeps
the older syntax covered.

Every cell was re-run on the final commit: 238 passed, 63 skipped, 0 failed across all
seven.

Cell	Engine	Result
snowflake	dbt-core 1.5.12	37 passed, 6 skipped
postgres	dbt-core 1.11.14	27 passed, 16 skipped
oracle	dbt-core 1.12.3	25 passed, 18 skipped
fusion	dbt Fusion 2.0.0rc212	39 passed, 4 skipped
core2	dbt-core 2.0.0b2	36 passed, 7 skipped
dpos_core	dbt Core in Snowflake 1.11.11	37 passed, 6 skipped
dpos_fusion	dbt Fusion in Snowflake preview.186	37 passed, 6 skipped

The snowflake 1.5.12 cell earned its place. dbt 1.5 decides whether a macro is a
materialization by testing whether its name holds the text "materialization", and it then
demands a supported_languages argument. A helper macro added for the #131 fix tripped that
test and stopped dbt 1.5 from parsing the package at all. dbt 1.11 and later test the block
type instead, so every newer cell accepted the name. The macro is now called
effective_materialized.

Contributors

• Source constraints, the deadlock fix, the bug fixes listed above, and the test bench by
  @sfc-gh-dflippo in #121, #122, #123, #124, #125, #126 and #133
• PK/UK lookup optimization for foreign keys by @RichardGoerlerKS in #128
• Fusion macro argument types by @b-per in #120

New Contributors

• @RichardGoerlerKS made their first contribution in #128

Full Changelog: 1.0.8...1.0.9

What's Changed

• feat: constraints on sources, deadlock prevention, and a faster FK lookup by @sfc-gh-dflippo in #133

Full Changelog: 1.0.9...1.0.10