support Ephemeral models

[dataders]

from #137, here's the proposed solution

Ephemeral models

The general approach outlined in microsoft/dbt-synapse@9180da5 is still the same, though I was doing it on easy mode, and didn't actually try to solve for ephemeral model compilation :)

The mechanism is: reimplement the compiler, define a new one (like in dbt/adapters/sqlserver/compilation.py), and reimplement methods like _inject_ctes_into_sql and _recursively_prepend_ctes.

Taking a step back: In order to support ephemeral models with CTEs on SQLServer and Synapse, given that T-SQL doesn't support CTEs nested inside either CTEs or subqueries, would we have to take the same approach as the one outlined above?

Namely, instead of recursively prepending ephemeral models as CTEs to the start of the query:

with  __dbt__cte__ephemeral_model as (


with my_cte as (
    
    select 1 as id
    
)

select * from my_cte
),ephemeral_model as (

select * from __dbt__cte__ephemeral_model

),

another_cte as (
    
    select 2 as id
    
)

select * from ephemeral_model
union all
select * from another_cte

Recursively prepend them as temp tables, to be executed in-transaction with the body of the query:

create table #ephemeral_model as (


with my_cte as (
    
    select 1 as id
    
)

select * from my_cte
);

with ephemeral_model as (

select * from #ephemeral_model

),

another_cte as (
    
    select 2 as id
    
)

select * from ephemeral_model
union all
select * from another_cte

[jtcohen6]

I don't think we should do this :)

Newer MSFT databases should support nested CTEs (#457). We shouldn't expose the full Compiler interface to adapters (dbt-labs/dbt-core#9134).