Initial comment lines break empty_subquery_sql

[simonhampe]

Minimal example

Model

{{ config(
    materialized='table'
) }}
-- depends_on: {{ ref('mytable') }}
WITH input as (SELECT 1 as id)
SELECT * FROM input

YAML config for the model

version: 2

models:
  - name: example
    config:
      contract: {enforced: true}
    columns:
      - name: id
        data_type: int

What happens?

When running this with dbt run I get

Database Error in model example (models\minimalexample\example.sql)
  ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WITH'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")

Looking in dbt log I can see that the query it is trying to execute is

    select * from (
        
-- depends_on: "warehouse"."mart"."mytable"
WITH input as (SELECT 1 as id)
SELECT * FROM input
    ) dbt_sbq_tmp
    where 1 = 0

What's (likely) the reason?

Because contract enforcement is turned on, an empty subquery is generated.
In the macro https://github.com/dbt-msft/dbt-sqlserver/blob/master/dbt/include/sqlserver/macros/adapter/columns.sql#L1 it just checks whether the SQL starts with WITH. Since there is a comment before the WITH which is not trimmed, it uses the else clause, thus generating faulty SQL.

Possible fixes

• There is of course an easy workaround: Move your comment further down, inside the WITH statement. Not optimal in terms of legibility / structuring your code, but doable. Also not a solution that a user would intuitively deduce from the error message, as comments usually don't cause syntax errors.
• The macro could be made a bit more robust by trimming comment lines first (both -- and /*..*/). As I am not very experienced with dbt macros, I can't suggest a concrete solution myself right now.