Doubling of escaped single quote for Snapshot generated SQL produces failed application

[dschoel-pr]

Running with dbt=1.8.8
Registered adapter: fabric=1.8.8

In a snapshot using dbt_utils.generate_surrogate_key, the snapshot execution fails with a SQL based error:
('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ''. (102) (SQLMoreResults)")

A debug level execution shows that the SQL produced to generate a staging temp view for the source data with double the amount of escaped single quotes:

EXEC('create view "silver"."mcp_InvoiceDetails__hist_snapshot_staging_temp_view" as

select 
  
    lower(convert(varchar(50), hashbytes(''''md5'''', ...
');

Some tracing shows that the following logic in the snapshot.sql macro, combined with downstream logic that packages the SQL into a string for EXEC are duplicating the attempt to add an escape single quote.

  -- Create a temporary view to manage if user SQl uses CTE
  {% set temp_snapshot_relation_sql = model['compiled_code'].replace("'", "''") %}
  {{ adapter.drop_relation(temp_snapshot_relation) }}

  {% call statement('create temp_snapshot_relation') -%}
    {{ get_create_view_as_sql(temp_snapshot_relation, temp_snapshot_relation_sql) }}
  {%- endcall %}

Tests show that removing the .replace on the top line allows the snapshot to function correctly.

Why is the replace in the logic? Can it be removed safely?

[prdpsvs]

@dschoel-pr , that's because the temp_snapshot_relation_sql is wrapped up in a view .

The view in Fabric DW is created using EXEC('create view ....'). A single quote needs to be replaced by two single quotes to execute the view correctly.

Here is an example, I tested with and there seems to be no problem.
`
EXEC('create view "dbo"."orders_snapshot__dbt_tmp_vw" as

select *,
    
CONVERT(VARCHAR(32), HashBytes(''MD5'', 
    coalesce(cast(order_id as varchar(8000)), '''')  + ''|'' + 

    coalesce(cast(order_date as varchar(8000)), '''') 
), 2)

as dbt_scd_id,
order_date as dbt_updated_at,
order_date as dbt_valid_from,
nullif(order_date, order_date) as dbt_valid_to
from (
select * from "dbt-local-tests"."dbo"."orders_snapshot_snapshot_staging_temp_view"
) sbq

;');
`

Can you share a repro with deps and commands you executed? If possible, share the logs file in the zip to validate your issue.
BTW, the dbt_utils is not fully supported. you may have to use t-sql utils.