Dual-Dialect Patterns

The dbt project runs on two SQL engines: DuckDB for local development and CI smoke tests, and Microsoft Fabric Warehouse (T-SQL) for DEV, UAT, and PROD environments. This dual-dialect approach gives developers sub-second local feedback while production runs against the real Fabric engine.

The trade-off is that SQL must be written to work on both dialects. This page documents every known compatibility difference and the patterns to handle them.

Why Dual-Dialect?

The decision to use DuckDB locally was driven by three factors:

1. Speed -- A full local build completes in seconds, not minutes. Developers can iterate rapidly without waiting for Fabric cold-starts or network round-trips.
2. Offline capability -- Local development requires no Azure connectivity. Developers can work on planes, at home without VPN, or when Fabric is down for maintenance.
3. Cost -- Every Fabric Warehouse query consumes Capacity Units (CUs). Running hundreds of test builds per day against Fabric would be expensive. DuckDB is free.

The local target uses a DuckDB file backed by Parquet seed data in dev-data/. The duckdb target (used by CI smoke tests) runs in-memory for even faster execution.

Compatibility Matrix

Code Patterns

Recursive CTEs

DuckDB requires the RECURSIVE keyword; Fabric T-SQL does not. Use a Jinja conditional:

{% if target.name in ('local', 'duckdb') %}WITH RECURSIVE{% else %}WITH{% endif %}
cte_hierarchy as (
    select id, parent_id, name, 0 as depth
    from {{ ref('stg_ax__categories') }}
    where parent_id is null

    union all

    select c.id, c.parent_id, c.name, h.depth + 1
    from {{ ref('stg_ax__categories') }} c
    inner join cte_hierarchy h on c.parent_id = h.id
)
select * from cte_hierarchy

Date Casting

Never use bare datetime2 -- Fabric requires explicit precision. The safest approach is the cross-DB macro, but explicit casting also works:

-- Preferred: use the cross-DB macro
cast(order_date as {{ cast_timestamp() }})

-- Also acceptable: explicit precision (works on both engines)
cast(order_date as datetime2(6))

Left-Padding (No lpad in T-SQL)

T-SQL does not have an lpad() function. Use the portable right() pattern:

-- WRONG: breaks on Fabric
-- lpad(cast(month_number as varchar), 2, '0')

-- CORRECT: works on both engines
right('00' || cast(month_number as varchar), 2)

-- For 3-digit padding:
right('000' || cast(item_code as varchar), 3)

Source YAML Target Conditions

Source YAML files must exclude ALL local targets to prevent DuckDB from trying to connect to Fabric databases:

sources:
  - name: ax
    {% if target.name not in ('local', 'duckdb') %}
    database: "Lakehouse_Bronze"
    schema: "ax"
    {% endif %}
    tables:
      - name: inventtable
      - name: salestable

Missing either local or duckdb from the exclusion list causes "Catalog does not exist" errors during local builds.

Boolean Casting

DuckDB has a native boolean type; Fabric uses bit. The cross-DB macro handles this:

cast(is_active as {{ cast_boolean() }})
-- Resolves to: cast(is_active as boolean)   on DuckDB
-- Resolves to: cast(is_active as bit)       on Fabric

Common Pitfalls

DuckDB passes but Fabric fails. This is the most common issue. DuckDB is more permissive: it is case-insensitive, accepts implicit type conversions, and tolerates bare datetime2. Always run the Fabric slim CI before merging. When in doubt, test against your feature warehouse with dbt build --target feat-NAME --profiles-dir ..

Quoting identifiers. DuckDB uses double quotes; Fabric supports both double quotes and brackets. However, quoting introduces case-sensitivity in Fabric. The safest approach is to never quote identifiers -- use snake_case names that work unquoted on both engines.

String functions. Some string functions differ. When writing complex string manipulation, check both engines' documentation. The cross-DB macros in dbt/macros/cross_db/ handle the most common cases.

Empty monitoring sources. Monitoring tables are created by a post-build script and do not exist on the first run. Always use mon_source_or_empty() in monitoring staging models:

{{ mon_source_or_empty('stg_monitoring__run_results') }}

Incremental Models

Some mart models use materialized: incremental to avoid full-table rebuilds. Key patterns:

• Merge strategy (incremental_strategy: merge): Uses unique_key to upsert rows. Works on both DuckDB and Fabric.
• Full refresh: Force a complete rebuild with dbt build --full-refresh --target <env> --profiles-dir .. Use when schema changes or data corrections require a clean slate.
• Snapshot models: Use reporting_date from dim_calendar for point-in-time queries, not current_timestamp(). This ensures consistent snapshot behavior across both dialects.
• Dialect note: DuckDB and Fabric handle merge semantics identically for simple unique-key merges. Complex merge predicates should be tested against Fabric CI before merging.

Verification Checklist

Before submitting any PR that includes dbt model changes:

• No bare datetime2 -- always datetime2(6) with explicit precision
• No lpad() -- use portable right('00' || ...) pattern
• Recursive CTEs use target-conditional WITH RECURSIVE
• Source YAML target conditions exclude BOTH local and duckdb targets
• No bracket identifiers [column]
• dbt build --target local --profiles-dir . passes all tests

Related Pages

• dbt Pipeline Overview -- Pipeline architecture and business logic
• Coding Conventions -- Full verification checklist
• Developer Workflow -- Local development setup and build process