[CT-1613] Update redshift adapter to use constraints and checks

[sungchun12]

Use the new constraints configs from this PR: dbt-labs/dbt-core#6271

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']
        check: id > 0
        tests:
          - unique
      - name: color
        data_type: string
      - name: date_day
        data_type: date

Reference dbt-snowflake PR doing something similar: dbt-labs/dbt-snowflake#341

Considerations

• You may simply need to inject the global macro: {{ get_columns_spec_ddl() }} within the existing table materialization macro in redshift's adapter to get it working
• Verify what redshift can and can't do and if redshift can't do it, make the macro spit out a warning, see the snowflake PR above as an example: https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html
• Make sure to use the dbt-constraints branch from the PR to ensure this version of dbt-core works well with your PR branch

[sungchun12]

Postgres example:

{% macro postgres__create_table_as(temporary, relation, sql) -%}
  {%- set unlogged = config.get('unlogged', default=false) -%}
  {%- set sql_header = config.get('sql_header', none) -%}

  {{ sql_header if sql_header is not none }}

  {# Verify global macro is dispatched and can be overridden elegantly in any adapter(I can do this by creating a custom macro in the dbt-postgres equivalent adapter) #}
  create {% if temporary -%}
    temporary
  {%- elif unlogged -%}
    unlogged
  {%- endif %} table {{ relation }}
  {{ get_columns_spec_ddl() }}
  as (
    {{ sql }}
  );
{%- endmacro %}

[dave-connors-3]

howdy @sungchun12 !

did a bit of spelunking and came up with the following findings (from least to most pertinent):

1. As with snowflake, checks are not supported, and would need to be flagged in a similar fashion. I added a redshift__get_columns_spec_ddl() to achieve this, and called it from within redshift__create_table_as
2. like snowflake, primary key, foreign_key, and uniqueness contstraints are metadata only. not null constraints are enforced.
3. Now for the big one -- it seems like column costraints are only supported by create table {{ ddl }} statements, and not by create table as {{ sql }} statements

The docs for create table examples show constraints as a part of the DDL, and behave as expected. The docs for create table as {{ sql }} indicate that while you can specify column names, you cannot specify any other attributes.

this works:

create  table
    "ci"."dbt_dconnors"."test_table__dbt_tmp"
    
  (
    
      id , 
      
      color , 
      
      date_day  
      
  )

    
    
    
  as (
    select 1 as id, 'red' as color, cast('2022-05-05' as date) as date_day
union all 
select 2 as id, 'blue' as color, cast('2022-05-05' as date) as date_day
union all 
select 3 as id, 'green' as color, cast('2022-05-05' as date) as date_day
  );

but this does not

create  table
    "ci"."dbt_dconnors"."test_table__dbt_tmp"
    
  (
    
      id integer , 
      
      color string , 
      
      date_day date  
      
  )

    
    
    
  as (
    select 1 as id, 'red' as color, cast('2022-05-05' as date) as date_day
union all 
select 2 as id, 'blue' as color, cast('2022-05-05' as date) as date_day
union all 
select 3 as id, 'green' as color, cast('2022-05-05' as date) as date_day
  );

[sungchun12]

@dave-connors-3 Thanks for the detailed progress update! I may have a simple solution for you.

I encountered the same problems in the postgres adapter. I got the below DDL work with the following macro.

create  table constraints

    

  (

    

      

      

      

      id integer  not null  primary key  check (id > 0) ,

    

      

      

      

      color text   ,

    

      

      

      

      date_day date   

    

  )

 ;


    insert into constraints

  (

    

      

      id ,

    

      

      color ,

    

      

      date_day 

    

  )


     (

      


select 

  1 as id, 

  'blue' as color, 

  cast('2019-01-01' as date) as date_day

    );

{% macro postgres__create_table_as(temporary, relation, sql) -%}
  {%- set unlogged = config.get('unlogged', default=false) -%}
  {%- set sql_header = config.get('sql_header', none) -%}

  {{ sql_header if sql_header is not none }}

  {% if config.get('constraints_enabled', False) %}
    create {% if temporary -%}
      temporary
    {%- elif unlogged -%}
      unlogged
    {%- endif %} table {{ relation }}
    {{ get_columns_spec_ddl() }} ;
    insert into {{ relation }} {{ get_column_names() }}
     (
      {{ sql }}
    );
  {% else %}
    create {% if temporary -%}
      temporary
    {%- elif unlogged -%}
      unlogged
    {%- endif %} table {{ relation }}
    as (
      {{ sql }}
    );
  {% endif %}
{%- endmacro %}

[sungchun12]

Can you link your PR branch to this issue?