Compiled SQL code for jaffle-shop results in errors

[ssugar]

Hi,

dbt and fabric plugin version from dbt --version: Core:

• installed: 1.8.0-rc2
• latest: 1.7.14 - Ahead of latest version!

Plugins:

• fabric: 1.8.3 - Up to date!

Using the jaffle-shop example repo. dbt seed with jaffle-data is working fine, but when I try dbt build, I get a bunch of errors that look like this:

4 of 46 START sql view model edw.stg_orders .................................... [RUN] Unhandled error while executing target/run/jaffle_shop/models/staging/stg_orders.sql ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '::'. (102) (SQLMoreResults)")

When I look at the compiled SQL code in jaffle-shop/target/compiled/jaffle_shop/models/staging/stg_orders.sql, I see the following code:

with

source as (

    select * from "DW"."raw"."raw_orders"

),

renamed as (

    select

        ----------  ids
        id as order_id,
        store_id as location_id,
        customer as customer_id,

        ---------- numerics
        subtotal as subtotal_cents,
        tax_paid as tax_paid_cents,
        order_total as order_total_cents,
        (subtotal / 100)::numeric(16, 2) as subtotal,
        (tax_paid / 100)::numeric(16, 2) as tax_paid,
        (order_total / 100)::numeric(16, 2) as order_total,

        ---------- timestamps

    CAST(DATEADD(day, DATEDIFF(day, 0, ordered_at), 0) AS DATE)
 as ordered_at

    from source

)

select * from renamed

The main issue appears to be the "::" prior to the numeric data type. If I manually change the query to this and run it in SSMS, it works:

with

source as (

    select * from "DW"."raw"."raw_orders"

),

renamed as (

    select

        ----------  ids
        id as order_id,
        store_id as location_id,
        customer as customer_id,

        ---------- numerics
        subtotal as subtotal_cents,
        tax_paid as tax_paid_cents,
        order_total as order_total_cents,
        cast(subtotal / 100 as numeric(16, 2)) as subtotal,
        cast(tax_paid / 100 as numeric(16, 2)) as tax_paid,
        cast(order_total / 100 as numeric(16, 2)) as order_total,

        ---------- timestamps

    CAST(DATEADD(day, DATEDIFF(day, 0, ordered_at), 0) AS DATE)
 as ordered_at

    from source

)

select * from renamed

Please let me know if any info is required - I can reproduce the issue quickly and easily with the well-known jaffle-shop project.

[ssugar]

Ok. I did some digging and figured out that this error is because of a macro in the jaffle-shop repo:

macro/cents_to_dollars.sql

The fix here is to add the following to that script:

{% macro fabric__cents_to_dollars(column_name) %} cast({{ column_name }} / 100 as numeric(16,2)) {% endmacro %}

I'll submit a pull request to jaffle-shop to account for the fabric adapter

I'm still running into a bunch of errors, but adding this code to the macro/cents_to_dollars.sql macro gets me much further now.