testing macros defined using CTEs

[dataders]

as I work on #59, I've had to disable ~17 tests in integration_tests/dbt_utils/dbt_project.yml for the same reason. The reason why is because of the pivot to the test block where code to be tested gets injected as a subquery into the result of get_test_sql(). For the ~17 or so macros being tested, they all use CTEs, which aren't allowed as subqueries in TSQL. previously this was only an issue for bespoke tests, where the solution was to tell users that CTEs in bespoke tests were not supported.

However, this issue is a bigger blocker, because now:

• we don't have the ability to test dbt-utils macros in tsql, without first rewriting them to not use CTEs, and
• even existing sqlserver__ ports of dbt-utils macros that were working would now need to be refactored to not use CTEs in order to be tested

the above two points are possible, but not ideal. Here's a commit of me doing it once, but I'm not looking forward to how this will look for a macro like get_date_spine.

your input is much appreciated as always @jtcohen6! It's a bummer that I didn't consider this scenario when testing dbt-sqlserver with dbt-core==0.20.0 initially.

[jtcohen6]

@swanderz I recall discussing (somewhere) that T-SQL adapters might need to override the built-in test materialization, in addition to the get_test_sql macro, to do the following:

• Create a temp table (instead of a subquery) that contains result of the test query
• Select from that temp table to get fail_calc, should_warn, should_error`
• If store_failures is enabled, make it a permanent table instead!

That feels a bit hacky, but it's the best workaround I can think of for the lack of nested-CTE support in T-SQL. I think all parts should be doable with existing dbt-core constructs.