dbt Constraints v1.0.4

What's Changed

• Added additional logic to detect when a table exists before creating constraints. The code was previously trying to create UK and FK on parent tables when the child is selected and the parent has not yet been created. @sfc-gh-dflippo in #81
• Also identified where the macro could fail when a model fails. Now if there is a SQL issue with a model, the macro will correctly skip constraints for that model. In the process also hardened the code by checking whether more attributes exist for tests in the results and metadata.

Full Changelog: 1.0.3...1.0.4

dbt Constraints v1.0.3

What's Changed

• Updated to avoid including FK constraints when the parent table is selected but not the child

Full Changelog: 1.0.2...1.0.3

dbt Constraints v1.0.2

Disabling constraints for dynamic table materializations.

Full Changelog: 1.0.1...1.0.2

dbt Constraints v1.0.1

Minor emergency patch to fix macro namespace conflicts.

Full Changelog: 1.0.0...1.0.1

1.0.0

Version 1.0.0

This release is a significant overhaul of dbt Constraints to support some key requested features.

What's Changed

• The package now supports both RELY and NORELY constraints on Snowflake. This has opened up a lot more possibilities that were previously blocked because previously we only created RELY constraints for join elimination. Now that the code can create constraints that Snowflake will not try to use for join elimination, we can safely create constraints when tests have not be executed.
• The previous logic looped over dbt's results object and created constraints for passed tests. Now the logic loops over all the tests in dbt's graph object that contains all the metadata for the project, and then it has a series of checks to determine if each test should be created as a RELY or NORELY constraint. The README.md describes all the conditions but essentially we determine if a test or its model was "selected" and whether the test passed, failed, or was skipped.
• The always_create_constraint feature has taken on a more significant role. Previously it didn't do very much because it only skipped a few of the criteria used to pick constraints to generate. Now, this is the parameter that will trigger constraints to be generated even if a test was not executed. For example, you can run dbt run and all the tests associated with your models will be used to create NORELY constraints. Later if you run dbt test, all of those constraints will be turned into RELY constraints if the test passes.
• Another previous issue was that a constraint could be created with the RELY property and then subsequently, the test could fail and the RELY property was not changed. Now every execution of a test could also cause the constraint to be altered to RELY or NORELY.

Full Changelog: 0.6.3...1.0.0

dbt Constraints v0.6.3

What's Changed

• Credit for this release goes to @sfc-gh-tbraunschober who identified an issue and provided the fix for where the Python and SQL would produce an error for users not yet using database roles on Snowflake. #67

Full Changelog: 0.6.2...0.6.3

dbt Constraints v0.6.2

What's Changed

• Reverted a change that added MATCH SIMPLE to the DDL for foreign keys. The change was causing FK DDL to fail for other users.
• Fixed an issue where dbt Constraints would skip constraints when the privileges to change the tables is granted through a database role on Snowflake. The fix uses the new is_database_role_in_session function.

Full Changelog: 0.6.1...0.6.2

dbt Constraints v0.6.1

What's Changed

• Add support for Vertica database by @jaceksan in #50
• Added match simple to Snowflake DDL to avoid errors on FK from nullable columns by @sfc-gh-dflippo in #54
• Added always_create_constraint config option by @sfc-gh-dflippo in #56

The always_create_constraint feature should address several open feature requests. Until now there was no way to force a constraint to be generated when there was a where configuration or if the constraint has a threshold. Now a always_create_constraint configuration setting will override those exclusions.

PLEASE NOTE:

• You will get an error if you try to force constraints that are enforced by your database. On Snowflake that is only a not_null constraint but on databases like Oracle, all the generated constraints are enforced.
• This feature can all cause unexpected query results on Snowflake due to join elimination. Some users specifically requested that they could have the benefits of join elimination where there was a missing parent 0 or -1 record. Personally, I would force the parent to have a 0 or -1 record but I understand there are scenarios for data vault that would benefit from this feature.

This is an example from the integration tests using the feature:

  - name: dim_duplicate_orders
    description: "Test that we do not try to create PK/UK on failed tests"
    columns:
      - name: o_orderkey
        description: "The primary key for this table"
      - name: o_orderkey_seq
        description: "duplicate seq column to test UK"
    tests:
      # This constraint should be skipped because it has failures
      - dbt_constraints.primary_key:
          column_name: o_orderkey
          config:
            severity: warn
      # This constraint should be still generated because always_create_constraint=true
      - dbt_constraints.unique_key:
          column_name: o_orderkey
          config:
            warn_if: ">= 5000"
            error_if: ">= 10000"
            always_create_constraint: true
      # This constraint should be still generated because always_create_constraint=true
      - dbt_constraints.unique_key:
          column_name: o_orderkey_seq
          config:
            severity: warn
            always_create_constraint: true

New Contributors

• @jaceksan made their first contribution in #50

Full Changelog: 0.6.0...0.6.1

dbt Constraints v0.6.0

What's Changed

• Added caching for metadata lookups which significantly improves the performance on Snowflake. There are metadata lookups for the columns on tables, the user's effective permissions, and existing constraints. Now, the code utilizes an in-memory cache to avoid duplicate lookups. Also, as constraints are added, this cache includes those new constraints so that later constraints can be already aware without looking them up in Snowflake. For other supported DB, the column lookup is now cached but not other lookups. by @sfc-gh-dflippo

Full Changelog: 0.5.3...0.6.0

dbt Constraints v0.5.3

What's Changed

• Added support for seeds by @sfc-gh-dflippo in #39

Full Changelog: 0.5.2...0.5.3