Using constraints in models without name and column parameters

[universe-designer]

I'm using datavault4dbt. Models look a little different than the standard. In my model CUST_HUB_CUSTOMER_CUST.sql I call the post-hook

{% set CUST_HUB_CUSTOMER_CUST = api.Relation.create(schema='dwhstage', identifier='CUST_HUB_CUSTOMER_CUST', type='table') %}
{{
    config({
	  "post-hook": [

		"{{ dbt_constraints.create_primary_key(table_model='CUST_HUB_CUSTOMER_CUST', column_names=['CUSTKEY_HK'], verify_permissions=false, quote_columns=false, constraint_name=PK_CUST, lookup_cache=none)}}"
	  ]
    })
}}


{%- set yaml_metadata -%}
source_models:
  stg_customer:
    bk_columns: 'C_CUSTKEY'
    rsrc_static: 'Customer'
  stg_customer_taxlocation:
    bk_columns: 'C_CUSTKEY'
    rsrc_static: 'Daniel'
  stg_telephone:
    bk_columns: 'C_CUSTKEY'
    rsrc_static: 'Daniel'
hashkey: CUSTKEY_HK
business_keys:
  - 'C_CUSTKEY'

{%- endset -%}

{% set metadata_dict = fromyaml(yaml_metadata) %}

{{ datavault4dbt.hub(hashkey=metadata_dict.get("hashkey"),
        business_keys=metadata_dict.get("business_keys"),
        source_models=metadata_dict.get("source_models")) }}

... and receive this error

15:48:00 Encountered an error: Compilation Error in model CUST_HUB_CUSTOMER_CUST (models\raw_vault\CUST_HUB_CUSTOMER_CUST.sql) 'None' has no attribute 'table'

in macro unique_constraint_exists (macros\create_constraints.sql) called by macro oracle__create_primary_key (macros\oracle__create_constraints.sql) called by macro create_primary_key (macros\create_constraints.sql) called by model CUST_HUB_CUSTOMER_CUST (models\raw_vault\CUST_HUB_CUSTOMER_CUST.sql)

I tried creating a table_relation but the error stayed the same.

{% set CUST_HUB_CUSTOMER_CUST = api.Relation.create(schema='dwhstage', identifier='CUST_HUB_CUSTOMER_CUST', type='table') %}
{{
    config({
	  "post-hook": [

		"{{ dbt_constraints.create_primary_key(table_model='CUST_HUB_CUSTOMER_CUST', column_names=['CUSTKEY_HK'], verify_permissions=false, quote_columns=false, constraint_name=PK_CUST, lookup_cache=none)}}"
	  ]
    })
}}I

the package is installed properly I see the drop constraints statements in the log. I added name and columns parameters to this model, but it didn't help.

Any idea how I can create a relation object in order to run this?

[sfc-gh-dflippo]

First, the package is designed to be run only at the end, after all the models and tests have completed. Nevertheless, the reason you are getting the error is that you have to pass a specific object as the lookup cache. This is what that object looks like when I create it in my macro:

        {%- set lookup_cache = {
            "table_columns": { },
            "table_privileges": { },
            "unique_keys": { },
            "not_null_col": { },
            "foreign_keys": { } } -%}

I would not recommend using your post-hook on Snowflake because you are not confirming whether there is a test to ensure there are no violations and you are not skipping the constraint if a test fails with a warning. That can cause Snowflake to return incorrect query results due to Join Elimination. Instead, I recommend adding unique, relationship, and not_null tests on columns and to allow the macros to automatically create the constraints only if the tests pass. Most of the complexity of these macros comes from ensuring that tests passed, that the constraints do not already exist, and that you have the necessary permissions to create the constraints. Alternately, I would recommend you check out dbt Core's model contracts feature. That feature could create your constraints and you wouldn't have to worry about join elimination because model contracts' constraints don't take advantage of the RELY / Table Elimination feature.

[universe-designer]

Thank you for your response! In the meantime I created a workaround but I agree with you, that running at end is the better option. I read the documentation but I still don't understand how and where to set the lookup_cache. Do I hard code the table parameters in dbt_packages\dbt_constraints\macros\create_constraints.sql Line 130?

{%- macro create_constraints( [...] {%- set lookup_cache = { "table_columns": {["column_a", "column_b" ]}, "table_privileges": { }, "unique_keys": { "uq_constraint_1", "uq_constraint_2"}, "not_null_col": { }, "foreign_keys": { } } -%} [...]

Can you provide an example?