Invalid test sql (dbt-synapse==1.8.3)

[jeremyyeo]

Project setup:

# ~/.dbt/profiles.yml
sy:
  target: dev
  outputs:
    dev:
      type: synapse
      driver: "ODBC Driver 18 for SQL Server" # (The ODBC Driver installed on your system)
      server: ....azuresynapse.net # (Dedicated SQL endpoint of your workspace here)
      port: 1433
      database: ...
      schema: dbt_jyeo
      authentication: ServicePrincipal
      tenant_id: ...
      client_id: ...
      client_secret: ...

# dbt_project.yml
name: analytics
profile: sy
version: "1.0.0"

models:
  analytics:
    +materialized: table
    +schema: marketing

# models/schema.yml
models:
  - name: foo
    columns:
      - name: c
        tests:
          - unique

-- models/foo.sql
select 1 c

-- macros/gsn.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- else -%}
        {{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

dbt-synapse==1.8.2

$ dbt --version 
Core:
  - installed: 1.8.9 
  - latest:    1.10.9 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - fabric:  1.8.9 - Update available!
  - synapse: 1.8.2 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

$ pip freeze | grep dbt
dbt-adapters==1.16.3
dbt-common==1.27.1
dbt-core==1.8.9
dbt-extractor==0.6.0
dbt-fabric==1.8.9
dbt-protos==1.0.348
dbt-semantic-interfaces==0.5.1
dbt-synapse==1.8.2

$ dbt --debug test

03:04:43  1 of 1 START test unique_foo_c ................................................. [RUN]
03:04:43  Re-using an available connection from the pool (formerly list_dbtmsft.azsyn.cipool_marketing, now test.analytics.unique_foo_c.ddb367a659)
03:04:43  Began compiling node test.analytics.unique_foo_c.ddb367a659
03:04:43  Writing injected SQL for node "test.analytics.unique_foo_c.ddb367a659"
03:04:43  Began executing node test.analytics.unique_foo_c.ddb367a659
03:04:43  local_md5(model.name): 4c98547d3998b1cc4f26da372a168899
03:04:43  local_md5(invocation_id): 4c99e09ed4519038f136b3bcac92e324
03:04:43  Writing runtime sql for node "test.analytics.unique_foo_c.ddb367a659"
03:04:43  Using synapse connection "test.analytics.unique_foo_c.ddb367a659"
03:04:43  On test.analytics.unique_foo_c.ddb367a659: /* {"app": "dbt", "dbt_version": "1.8.9", "profile_name": "all", "target_name": "sy", "node_id": "test.analytics.unique_foo_c.ddb367a659"} */
  
  EXEC('create view 

    [dbt_test__audit.testview_469d7ba42554af0f4b0d9c789a8f6ddd]
   as 

select
    c as unique_field,
    count(*) as n_records

from "marketing"."foo"
where c is not null
group by c
having count(*) > 1


;')
  select
    count(*) as failures,
    case when count(*) != 0
      then 'true' else 'false' end as should_warn,
    case when count(*) != 0
      then 'true' else 'false' end as should_error
  from (
    select  * from 

    [dbt_test__audit.testview_469d7ba42554af0f4b0d9c789a8f6ddd]
  
  ) dbt_internal_test;

  
  EXEC('drop view 

    [dbt_test__audit.testview_469d7ba42554af0f4b0d9c789a8f6ddd]
  ;')
03:04:43  Opening a new connection, currently in state closed
03:04:43  fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=dbtlabssynapseci.sql.azuresynapse.net;Database=dbtmsft.azsyn.cipool;Authentication=ActiveDirectoryServicePrincipal;UID={1e74e997-4c1f-41c5-8520-15b1f11a9b35};PWD=***;encrypt=Yes;TrustServerCertificate=No;APP=dbt-synapse/1.8.9;ConnectRetryCount=3
03:04:43  fabric adapter: Connected to db: dbtmsft.azsyn.cipool
03:04:44  SQL status: OK in 1.000 seconds
03:04:44  On test.analytics.unique_foo_c.ddb367a659: ROLLBACK
03:04:44  On test.analytics.unique_foo_c.ddb367a659: Close
03:04:44  1 of 1 PASS unique_foo_c ....................................................... [PASS in 0.65s]

^ So far so good.

dbt-synapse==1.8.3

Upgrade to 1.8.3

$ pip install dbt-synapse==1.8.3
...
Installing collected packages: dbt-synapse
  Attempting uninstall: dbt-synapse
    Found existing installation: dbt-synapse 1.8.2
    Uninstalling dbt-synapse-1.8.2:
      Successfully uninstalled dbt-synapse-1.8.2
Successfully installed dbt-synapse-1.8.3

$ pip freeze | grep dbt
dbt-adapters==1.16.3
dbt-common==1.27.1
dbt-core==1.8.9
dbt-extractor==0.6.0
dbt-fabric==1.8.9
dbt-protos==1.0.348
dbt-semantic-interfaces==0.5.1
dbt-synapse==1.8.3

$ dbt --version
Core:
  - installed: 1.8.9 
  - latest:    1.10.9 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - fabric:  1.8.9 - Update available!
  - synapse: 1.8.3 - Up to date!

Retest:

$ dbt --debug test

03:07:00  1 of 1 START test unique_foo_c ................................................. [RUN]
03:07:00  Re-using an available connection from the pool (formerly list_dbtmsft.azsyn.cipool_marketing, now test.analytics.unique_foo_c.ddb367a659)
03:07:00  Began compiling node test.analytics.unique_foo_c.ddb367a659
03:07:00  Writing injected SQL for node "test.analytics.unique_foo_c.ddb367a659"
03:07:00  Began executing node test.analytics.unique_foo_c.ddb367a659
03:07:00  Writing runtime sql for node "test.analytics.unique_foo_c.ddb367a659"
03:07:00  Using synapse connection "test.analytics.unique_foo_c.ddb367a659"
03:07:00  On test.analytics.unique_foo_c.ddb367a659: /* {"app": "dbt", "dbt_version": "1.8.9", "profile_name": "all", "target_name": "sy", "node_id": "test.analytics.unique_foo_c.ddb367a659"} */


  -- Create target schema in synapse db if it does not
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '')
  BEGIN
    EXEC('CREATE SCHEMA []')
  END

  select
      
      count(*) as failures,
      case when count(*) != 0
        then 'true' else 'false' end as should_warn,
      case when count(*) != 0
        then 'true' else 'false' end as should_error
    from (
      
    
    

select
    c as unique_field,
    count(*) as n_records

from "marketing"."foo"
where c is not null
group by c
having count(*) > 1



    ) dbt_internal_test
03:07:00  Opening a new connection, currently in state closed
03:07:00  fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=dbtlabssynapseci.sql.azuresynapse.net;Database=dbtmsft.azsyn.cipool;Authentication=ActiveDirectoryServicePrincipal;UID={1e74e997-4c1f-41c5-8520-15b1f11a9b35};PWD=***;encrypt=Yes;TrustServerCertificate=No;APP=dbt-synapse/1.8.9;ConnectRetryCount=3
03:07:00  fabric adapter: Connected to db: dbtmsft.azsyn.cipool
03:07:01  fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Parse error at line: 1, column: 16: Incorrect syntax near ']'. (103010) (SQLExecDirectW)")
03:07:01  On test.analytics.unique_foo_c.ddb367a659: ROLLBACK
03:07:01  On test.analytics.unique_foo_c.ddb367a659: Close
03:07:01  Database Error in test unique_foo_c (models/sch.yml)
  ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Parse error at line: 1, column: 16: Incorrect syntax near ']'. (103010) (SQLExecDirectW)")
  compiled code at target/run/analytics/models/sch.yml/unique_foo_c.sql
03:07:01  1 of 1 ERROR unique_foo_c ...................................................... [ERROR in 1.13s]

Note:

  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '')
  BEGIN
    EXEC('CREATE SCHEMA []')
  END

---

Current workarounds

(Option 1) Set the var:

# dbt_project.yml
...
vars:
  synapse_test_schema: "{{ target.schema }}"

OR:

(Option 2) Undo the change in #260 by overriding and going back to how it was before:

-- macros/get_test_sql.sql
{% macro synapse__get_test_sql(main_sql, fail_calc, warn_if, error_if, limit) -%}

  -- Create target schema in synapse db if it does not
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '{{ target.schema }}')
  BEGIN
    EXEC('CREATE SCHEMA [{{ target.schema }}]')
  END

  {% if main_sql.strip().lower().startswith('with') %}
    {% set testview %}
      {{ target.schema }}.testview_{{ range(1300, 19000) | random }}
    {% endset %}

    {% set sql = main_sql.replace("'", "''")%}
    EXEC('create view {{testview}} as {{ sql }};')
    select
      {{ "top (" ~ limit ~ ')' if limit != none }}
      {{ fail_calc }} as failures,
      case when {{ fail_calc }} {{ warn_if }}
        then 'true' else 'false' end as should_warn,
      case when {{ fail_calc }} {{ error_if }}
        then 'true' else 'false' end as should_error
    from (
      select * from {{testview}}
    ) dbt_internal_test;

    EXEC('drop view {{testview}};')

  {% else -%}
    select
      {{ "top (" ~ limit ~ ')' if limit != none }}
      {{ fail_calc }} as failures,
      case when {{ fail_calc }} {{ warn_if }}
        then 'true' else 'false' end as should_warn,
      case when {{ fail_calc }} {{ error_if }}
        then 'true' else 'false' end as should_error
    from (
      {{ main_sql }}
    ) dbt_internal_test
  {%- endif -%}
{%- endmacro %}

$ dbt --debug test
...
03:26:06  1 of 1 START test unique_foo_c ................................................. [RUN]
03:26:06  Re-using an available connection from the pool (formerly list_dbtmsft.azsyn.cipool_marketing, now test.analytics.unique_foo_c.ddb367a659)
03:26:06  Began compiling node test.analytics.unique_foo_c.ddb367a659
03:26:06  Writing injected SQL for node "test.analytics.unique_foo_c.ddb367a659"
03:26:06  Began executing node test.analytics.unique_foo_c.ddb367a659
03:26:06  Writing runtime sql for node "test.analytics.unique_foo_c.ddb367a659"
03:26:06  Using synapse connection "test.analytics.unique_foo_c.ddb367a659"
03:26:06  On test.analytics.unique_foo_c.ddb367a659: /* {"app": "dbt", "dbt_version": "1.8.9", "profile_name": "all", "target_name": "sy", "node_id": "test.analytics.unique_foo_c.ddb367a659"} */
  -- Create target schema in synapse db if it does not
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'dbt_jyeo')
  BEGIN
    EXEC('CREATE SCHEMA [dbt_jyeo]')
  END
  select

      count(*) as failures,
      case when count(*) != 0
        then 'true' else 'false' end as should_warn,
      case when count(*) != 0
        then 'true' else 'false' end as should_error
    from (


select
    c as unique_field,
    count(*) as n_records
from "marketing"."foo"
where c is not null
group by c
having count(*) > 1

    ) dbt_internal_test
03:26:06  Opening a new connection, currently in state closed
03:26:06  fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=dbtlabssynapseci.sql.azuresynapse.net;Database=dbtmsft.azsyn.cipool;Authentication=ActiveDirectoryServicePrincipal;UID={1e74e997-4c1f-41c5-8520-15b1f11a9b35};PWD=***;encrypt=Yes;TrustServerCertificate=No;APP=dbt-synapse/1.8.9;ConnectRetryCount=3
03:26:06  fabric adapter: Connected to db: dbtmsft.azsyn.cipool
03:26:06  SQL status: OK in 1.000 seconds
03:26:06  On test.analytics.unique_foo_c.ddb367a659: ROLLBACK
03:26:06  On test.analytics.unique_foo_c.ddb367a659: Close
03:26:06  1 of 1 PASS unique_foo_c ....................................................... [PASS in 0.76s]

---

The latest pypi release is 1.8.3:
https://pypi.org/project/dbt-synapse/1.8.3/

Though in this repo itself it shows 1.8.2:
https://github.com/microsoft/dbt-synapse/releases/tag/v1.8.2

[jeremyyeo]

Did some more testing... I believe the intention of #260 was when access to target.schema wasn't not allowed to the principal and therefore folks we're setting the schema of the test via:

# dbt_project.yml
name: analytics

...

tests:
  analytics:
    +schema: marketing

What would happen prior to #260, is the get_test_sql macro utilized target.schema which would always evaluate to the schema set on the profile and totally ignore the "marketing" schema for the test:

{% macro synapse__get_test_sql(main_sql, fail_calc, warn_if, error_if, limit) -%}
  -- Create target schema in synapse db if it does not
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '{{ target.schema }}')
  BEGIN
    EXEC('CREATE SCHEMA [{{ target.schema }}]')
  END
...

^ That would eval to:

  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'dbt_jyeo')
  BEGIN
    EXEC('CREATE SCHEMA [dbt_jyeo]')
  END

Which we do not want. Instead we want:

  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'marketing')
  BEGIN
    EXEC('CREATE SCHEMA [marketing]')
  END

---

I think the #260 change though - did not do as intended? Just logging out the values after modifying the macro to add some debugging:

-- macros/get_test_sql.sql
{% macro synapse__get_test_sql(main_sql, fail_calc, warn_if, error_if, limit) -%}
  {% set target_schema = var('synapse_test_schema', generate_schema_name()) %}
  {% do print('>>>> Value of calling generate_schema_name() in this macro: ' ~ target_schema ~ ' <<<<') %}
  {% do print('>>>> Value of calling this.schema in this macro: ' ~ this.schema ~ ' <<<<') %}

  -- Create target schema in synapse db if it does not
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '{{ target_schema }}')
  BEGIN
    EXEC('CREATE SCHEMA [{{ target_schema }}]')
  END

  {% if main_sql.strip().lower().startswith('with') %}
    {% set testview %}
      {{ target_schema }}.testview_{{ range(1300, 19000) | random }}
    {% endset %}

    {% set sql = main_sql.replace("'", "''")%}
    EXEC('create view {{testview}} as {{ sql }};')
    select
      {{ "top (" ~ limit ~ ')' if limit != none }}
      {{ fail_calc }} as failures,
      case when {{ fail_calc }} {{ warn_if }}
        then 'true' else 'false' end as should_warn,
      case when {{ fail_calc }} {{ error_if }}
        then 'true' else 'false' end as should_error
    from (
      select * from {{testview}}
    ) dbt_internal_test;

    EXEC('drop view {{testview}};')

  {% else -%}
    select
      {{ "top (" ~ limit ~ ')' if limit != none }}
      {{ fail_calc }} as failures,
      case when {{ fail_calc }} {{ warn_if }}
        then 'true' else 'false' end as should_warn,
      case when {{ fail_calc }} {{ error_if }}
        then 'true' else 'false' end as should_error
    from (
      {{ main_sql }}
    ) dbt_internal_test
  {%- endif -%}
{%- endmacro %}

-- macros/gsn.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- else -%}
        {{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

# dbt_project.yml
name: analytics
profile: all
version: "1.0.0"

models:
  analytics:
    +materialized: table
    +schema: marketing

tests:
  analytics:
    +schema: marketing

$ dbt --debug test
05:06:30  1 of 1 START test unique_foo_c ................................................. [RUN]
05:06:30  Re-using an available connection from the pool (formerly list_dbtmsft.azsyn.cipool_marketing, now test.analytics.unique_foo_c.ddb367a659)
05:06:30  Began compiling node test.analytics.unique_foo_c.ddb367a659
05:06:30  Writing injected SQL for node "test.analytics.unique_foo_c.ddb367a659"
05:06:30  Began executing node test.analytics.unique_foo_c.ddb367a659
>>>> Value of calling generate_schema_name() in this macro:  <<<<
>>>> Value of calling this.schema in this macro: marketing <<<<
05:06:30  Writing runtime sql for node "test.analytics.unique_foo_c.ddb367a659"
05:06:30  Using synapse connection "test.analytics.unique_foo_c.ddb367a659"
05:06:30  On test.analytics.unique_foo_c.ddb367a659: /* {"app": "dbt", "dbt_version": "1.8.9", "profile_name": "all", "target_name": "sy", "node_id": "test.analytics.unique_foo_c.ddb367a659"} */



  -- Create target schema in synapse db if it does not
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '')
  BEGIN
    EXEC('CREATE SCHEMA []')
  END

  select
      
      count(*) as failures,
      case when count(*) != 0
        then 'true' else 'false' end as should_warn,
      case when count(*) != 0
        then 'true' else 'false' end as should_error
    from (


select
    c as unique_field,
    count(*) as n_records

from "marketing"."foo"
where c is not null
group by c
having count(*) > 1

    ) dbt_internal_test
05:06:30  Opening a new connection, currently in state closed
05:06:30  fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=dbtlabssynapseci.sql.azuresynapse.net;Database=dbtmsft.azsyn.cipool;Authentication=ActiveDirectoryServicePrincipal;UID={1e74e997-4c1f-41c5-8520-15b1f11a9b35};PWD=***;encrypt=Yes;TrustServerCertificate=No;APP=dbt-synapse/1.8.9;ConnectRetryCount=3
05:06:30  fabric adapter: Connected to db: dbtmsft.azsyn.cipool
05:06:31  fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Parse error at line: 1, column: 16: Incorrect syntax near ']'. (103010) (SQLExecDirectW)")
05:06:31  On test.analytics.unique_foo_c.ddb367a659: ROLLBACK
05:06:31  On test.analytics.unique_foo_c.ddb367a659: Close
05:06:31  Database Error in test unique_foo_c (models/sch.yml)
  ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Parse error at line: 1, column: 16: Incorrect syntax near ']'. (103010) (SQLExecDirectW)")
  compiled code at target/run/analytics/models/sch.yml/unique_foo_c.sql
05:06:31  1 of 1 ERROR unique_foo_c ...................................................... [ERROR in 1.03s]

^ We observe:

>>>> Value of calling generate_schema_name() in this macro:  <<<<
>>>> Value of calling this.schema in this macro: marketing <<<<

generate_schema_name() returned empty. Perhaps what we want is instead to use this.schema - which can be seen above to have returned the schema we wanted "marketing".

[b-per]

Thanks @jeremyyeo

#260 was implemented to fix a bug when people didn't have the relevant access to create new schemas.

So, maybe we should change var('synapse_test_schema', generate_schema_name() ) to var('synapse_test_schema', target.schema ) instead?

But I am quite sure that we won't want to revert #260 entirely.

I am also wondering if generate_schema_name for tests is only returning data if store_failures is true? (haven't tried it)

[tundeanderson]

I also ran into this issue when attempting to upgrade to dbt-core 1.8. For now I'm using the workaround @jeremyyeo mentioned by populating the synapse_test_schema var in dbt_project.yml