Regression in 1.7.2rc2 - WITH inside custom tests

[matsonj]

When invoking custom tests in the previous version, the generated SQL looks like this:

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 "db_name"."dbo_dbt_test__audit"."custom_test_name"
  
    ) dbt_internal_test
    

however, in 1.7.2rc2, it generates SQL like this:

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 (
      
      <test_definition.sql>
  
    ) dbt_internal_test
    

Since it is dropping the raw SQL query into the executed SQL query, this breaks any custom tests that use CTEs.

Workaround: Don't use CTEs in custom tests.

[pl-pr]

@matsonj
If you want to use CTEs in custom tests, start your code with WITH. Then it works correctly. Perhaps you have a comment in SQL format /*.... */ or --...., then change it to the Jinja format {#.... #}.

https://github.com/microsoft/dbt-fabric/blob/main/dbt/include/fabric/macros/materializations/tests/helpers.sql#L3

[matsonj]

The custom test appropriately uses as CTE. The problem is in 1.4 (previous version), the test was first added as a view and then executed. In 1.7, the test is tested directly with a subquery, which means you cannot use CTEs inside of custom tests.

[matsonj]

@pl-pr I will provide more notes here shortly. Thanks for the feedback

[G14rb]

@matsonj unfortunately it's because of the dependency to dbt-fabric. This, like many other errors, is fixed in the version 1.8.0
microsoft/dbt-fabric@57f2aa6
Well, there is another error related to the version 1.8.0
microsoft/dbt-fabric#168
And also I found that if you have comments at the beginning of the sql it won't work since it checks if the sql starts with 'with'
main_sql.strip().lower().startswith('with')