Add constraints, data type, and check enforcement on SQL Tables

[sungchun12]

trafficstars

Describe the feature

Add constraints to Databricks SQL tables based on documentation here:

Reference PR for dbt-core and dbt-postgres: https://github.com/dbt-labs/dbt-core/pull/6271

Databricks constraints: https://docs.databricks.com/tables/constraints.html

Describe alternatives you've considered

Custom macros that one wants to nor should maintain on their own

Additional context

Example of how sql file + schema config = expected DDL

{{
  config(
    materialized = "table",
    constraints_enabled = true
  )
}}

select 
  1 as id, 
  'blue' as color, 
  cast('2019-01-01' as date) as date_day


version: 2

models:
  - name: constraints_example
    docs:
      node_color: black
    config:
      constraints_enabled: true
    columns:
      - name: id
        data_type: integer
        description: hello
        constraints: ['not null','primary key']
        constraints_check: (id > 0)
        tests:
          - unique
      - name: color
        data_type: text
      - name: date_day
        data_type: date

# DDL generated and run against postgresql example
# you'll notice this is for a temp table but this replaces the actual table name after this succeeds


    create  table "sungwonchung3/constraints"."dbt_sung"."constraints_example__dbt_tmp"
    
  (
    
      
      
      
      id integer  not null  primary key  check (id > 0) ,
    
      
      
      
      color text   ,
    
      
      
      
      date_day date   
    
  )
 ;
    insert into "sungwonchung3/constraints"."dbt_sung"."constraints_example__dbt_tmp" 
  (
    
      
      id ,
    
      
      color ,
    
      
      date_day 
    
  )

     (
      

select 
  1 as id, 
  'blue' as color, 
  cast('2019-01-01' as date) as date_day
    );

Who will this benefit?

All dbt users who want to guarantee data shape and prevent accidental null inserts into a table. Removes the need for not null dbt tests :)

Are you interested in contributing this feature?

I can help coach on the macro implementations, but I don't have bandwidth to own this hands-on.

[sungchun12]

Contribute to open source tutorial: https://www.loom.com/share/b6836df041ac499691d0491dd25bd78a

[bilalaslamseattle]

Super cool idea. @andrefurlan-db FYI

[ueshin]

What's the difference from Delta constraints support? cc @allisonwang-db

[bilalaslamseattle]

Actually ahem we already support that ... but we don't have docs. I need to add docs 😬

[sungchun12]

Hey Everyone!

That's so splendid that you already support constraints through the meta tag. You're probably in no rush to implement this then. I recommend eventually making a PR for this issue because the config below will be the standard for dbt-core and all adapters.

The main benefits you get are error handling for errors like the below.

These configs also become standard within manifest.json for artifact based runs.

If you want more details, I recommend watching the loom video above!

version: 2

models:
  - name: constraints_example
    docs:
      node_color: black
    config:
      constraints_enabled: true
    columns:
      - name: id
        data_type: integer
        description: hello
        constraints: ['not null','primary key']
        constraints_check: (id > 0)
        tests:
          - unique
      - name: color
        data_type: text
      - name: date_day
        data_type: date

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue.