Incorrect syntax near 'column' error adding a New Column with on_schema_change='sync_all_columns'

[AdamG30]

Hi.

I'm running some test cases evaluating dbt using the Synapse provider. I came across this error when adding a new column:

Test Case - Add column NEW_COLUMN

Model

{{ config(materialized='incremental'
, index='clustered index(OLDACNUM)'
, dist='REPLICATE'
, on_schema_change='sync_all_columns' )    }}

SELECT TOP (1000) 
      [RecordCreateRunId]
      ,[DEBTORNUM]
      ,[OLDACNUM]
      ,[CREDCOLACTION]
      ,[LASTSTMTDATE]
      ,[CUST_TYPE]
      ,[LETTERDATE]
      ,[CHARGE_CODE]
      ,[NUM_REMINDERS]
      ,[PAYMENT_CODE]
      ,[CYCLE_GROUP]
      ,ODS_START_DATE
      ,1 AS NEW_COLUMN	  
FROM [DBO].[AR_DEBTOR]
{% if is_incremental() %}
  -- this filter will only be applied on an incremental run
  WHERE ODS_START_DATE > (select max(ODS_START_DATE) from {{ this }})
{% endif %}

Incremental Run

• dbt run --select STG_DEBTORS_NEW

Generated Code from Log

 if object_id ('"DBT"."STG_DEBTORS_NEW__dbt_tmp_temp_view"','V') is not null
    begin
    drop view "DBT"."STG_DEBTORS_NEW__dbt_tmp_temp_view"
    end


   
    
  if object_id ('"DBT"."STG_DEBTORS_NEW__dbt_tmp"','U') is not null
    begin
    drop table "DBT"."STG_DEBTORS_NEW__dbt_tmp"
    end


   EXEC('create view DBT.STG_DEBTORS_NEW__dbt_tmp_temp_view as
    

SELECT TOP (1000) 
      [RecordCreateRunId]
      ,[DEBTORNUM]
      ,[OLDACNUM]
      ,[CREDCOLACTION]
      ,[LASTSTMTDATE]
      ,[CUST_TYPE]
      ,[LETTERDATE]
      ,[CHARGE_CODE]
      ,[NUM_REMINDERS]
      ,[PAYMENT_CODE]
      ,[CYCLE_GROUP]
      ,ODS_START_DATE
      ,1 AS NEW_COLUMN	  
FROM [DBO].[AR_DEBTOR]

  -- this filter will only be applied on an incremental run
  WHERE ODS_START_DATE > (select max(ODS_START_DATE) from "DBNAME"."DBT"."STG_DEBTORS_NEW")

    ');

  CREATE TABLE "DBT"."STG_DEBTORS_NEW__dbt_tmp"
    WITH(
      DISTRIBUTION = REPLICATE,
      clustered index(OLDACNUM)
      )
    AS (SELECT * FROM DBT.STG_DEBTORS_NEW__dbt_tmp_temp_view)

   
  
  if object_id ('"DBT"."STG_DEBTORS_NEW__dbt_tmp_temp_view"','V') is not null
    begin
    drop view "DBT"."STG_DEBTORS_NEW__dbt_tmp_temp_view"
    end

...

3:51:52.439447 [debug] [Thread-1 (]: 
    In "DBNAME"."DBT"."STG_DEBTORS_NEW":
        Schema changed: True
        Source columns not in target: [<Column NEW_COLUMN (int)>]
        Target columns not in source: []
        New column types: []

03:51:52.479450 [debug] [Thread-1 (]: SQLServer adapter: Using synapse connection "model.dbtPOC.STG_DEBTORS_NEW".
03:51:52.480445 [debug] [Thread-1 (]: SQLServer adapter: On model.dbtPOC.STG_DEBTORS_NEW: 
    alter table "DBNAME"."DBT"."STG_DEBTORS_NEW"

            
               add column NEW_COLUMN int
            

            
  
03:51:52.955452 [debug] [Thread-1 (]: SQLServer adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 5, column: 20: Incorrect syntax near 'column'. (103010) (SQLExecDirectW)")
03:51:52.956517 [debug] [Thread-1 (]: On model.dbtPOC.STG_DEBTORS_NEW: ROLLBACK
03:51:52.958450 [debug] [Thread-1 (]: On model.dbtPOC.STG_DEBTORS_NEW: Close
03:51:52.963454 [debug] [Thread-1 (]: finished collecting timing info
03:51:52.964453 [debug] [Thread-1 (]: Database Error in model STG_DEBTORS_NEW (models\DataVault\CHANGE\STG_DEBTORS_NEW.sql)
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parse error at line: 5, column: 20: Incorrect syntax near 'column'. (103010) (SQLExecDirectW)")
03:51:52.968452 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '96d48387-ac3a-4327-a899-1ccb95fb9418', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001CFFD4F1270>]}
03:51:52.970454 [error] [Thread-1 (]: 1 of 1 ERROR creating incremental model DBT.STG_DEBTORS_NEW .................... [�[31mERROR�[0m in 6.11s]
03:51:52.977454 [debug] [Thread-1 (]: Finished running node model.dbtPOC.STG_DEBTORS_NEW
	
	

Outcome

• Failed with Error Incorrect syntax near 'column'
• Offending code was:

    alter table "DBNAME"."DBT"."STG_DEBTORS_NEW"

            
               add column NEW_COLUMN int

• should be:

  alter table "DBNAME"."DBT"."STG_DEBTORS_NEW" add NEW_COLUMN int

I would suggest a fix, but I couldn't find an SQL server or synapse macro specifically for "add column" is this just defaulting to the default provider?

Versions dbt=1=1=2, sqlserver=1.1.0, synapse=1.1.0

[prdpsvs]

alter_relation_add_remove_columns() macro should be added to dbt-sqlserver project - thttps://github.com/dbt-labs/dbt-core/blob/a181cee6aef38eeb20e68c81cbee557b8db09968/core/dbt/include/global_project/macros/adapters/columns.sql#L60

Reported this issue in dbt-sqlserver project - dbt-msft/dbt-sqlserver#326