Test code reporting error when test code uses CTEs with line breaks after "with" keyword

[CamronBorealis]

Steps to reproduce:

1. Create test that uses CTEs with a line break after the "with" keyword
2. Execute the test

Expected result:

• Test should report pass or fail

Actual result:

• Test reports an error

Sample test code reporting error:

with

failures as (
    select *
    from {{ model }}
    where {{ column_name }} is null
)

select *
from failures

Sample test code that reports pass/fail:

with failures as (
    select *
    from {{ model }}
    where {{ column_name }} is null
)

select *
from failures

Suspected root cause:

This regex expression assumes the code style includes the "with" keyword on the same line as the identifier of the CTE:

https://github.com/dbt-msft/dbt-sqlserver/blob/be032aac1c964a5a7291fdcb3d0a6f28fe383a30/dbt/include/sqlserver/macros/materializations/tests.sql#L10

[cody-scott]

Good catch. I’m wondering if it’s better anyways to just create a view every time instead of this branching logic.

Will tackle this later today or tomorrow.