AI-generated content. This lesson was produced from model knowledge plus the cited public source; its claims have not yet passed Fabric Codex human verification. Verify limits and feature support against current Microsoft documentation before relying on them.

This lesson examines how the dbt-fabric adapter actually gets models built on an engine whose T-SQL surface is narrower than SQL Server's, what that means for incremental models and performance, and the failure modes to anticipate. Where the public record is thin, this lesson says so explicitly instead of guessing.

dbt with Fabric Data Warehouse — architecture

The core mechanical fact: emulation by recreation#

Fabric Data Warehouse does not support the same T-SQL command set as SQL Server, and several commands dbt's default materialization strategies lean on are among the gaps. The dbt-fabric adapter handles operations equivalent to ALTER TABLE ADD/ALTER/DROP COLUMN, MERGE, TRUNCATE, and sp_rename by translating them into CREATE TABLE AS SELECT (CTAS) plus DROP/CREATE sequences rather than issuing the native commands [S1].

This one fact drives most of the expert-level behavior. On engines with full DDL/DML surfaces, dbt can evolve a table in place — add a column, merge new rows, rename a relation to swap it into position. On Fabric, the adapter's macro layer instead computes the desired end state as a fresh table via CTAS (a first-class, set-based operation in Fabric Warehouse) and swaps objects by dropping and recreating.

Consequences worth internalizing:

  • "Alter" is really "rebuild." A schema change that looks incremental in your dbt diff — one new column — can execute as a full re-materialization of the relation. Cost scales with table size, not with the size of the change.
  • Swaps are not a single atomic rename. Where other adapters atomically promote a freshly built table by renaming it, a DROP-then-CREATE sequence has a window in which the old object is gone before the new one exists. The exact statement ordering, and what concurrent readers observe mid-swap, is implementation detail the tutorial does not document — confirm it by reading the materialization macros in the microsoft/dbt-fabric GitHub repository [S1] for the version you run.

Incremental models: what "incremental" buys you here#

dbt's incremental materialization exists to avoid full rebuilds: on each run, process only new or changed rows and apply them to the existing table. Adapters implement the "apply" step with one of a few strategies — typically append, delete+insert, or merge.

On Fabric, the source document establishes that MERGE-equivalent behavior is emulated via CTAS and DROP/CREATE rather than a native MERGE [S1]. The practical reasoning that follows (labeled inference, verify against the adapter docs for your version):

  • Append-style incrementals are the cheap case — inserting new rows into an existing table is well within the supported surface, so an insert-only incremental genuinely does less work than a full rebuild.
  • Update-style incrementals (rows replaced by key) cannot rely on a native MERGE; depending on adapter version and strategy, that means delete-then-insert or a CTAS-based reconstruction. In the worst case an "incremental" model approaches full-refresh cost — you have paid dbt's incremental complexity (is_incremental() branches, unique keys, late-arriving data) without proportional savings.
  • on_schema_change behavior inherits the rebuild economics above: schema evolution on an incremental model is exactly where the ALTER-emulation-by-CTAS cost lands [S1].

The discipline this implies: benchmark your incremental strategy on real volumes before assuming it beats table. Often a nightly full rebuild via one clean CTAS is simpler, more predictable, and not meaningfully slower. Don't inherit intuitions from adapters where MERGE is native.

Performance characteristics#

Compute is the warehouse's, not dbt's. dbt is a compiler and orchestrator; every expensive operation is a T-SQL statement executed by Fabric Warehouse compute against your capacity, while dbt's own footprint (Jinja compilation, ODBC calls) is negligible. Run cost is the sum of issued statements, and the rebuild-heavy translation above skews that toward large CTAS scans/writes. Watch capacity around scheduled runs, and size threads in profiles.yml knowing each thread is a parallel warehouse session on the same capacity.

Statistics. Fabric Warehouse relies on statistics for plan quality, and maintains them automatically (with manual CREATE/UPDATE STATISTICS also available). Since dbt materializations drop and recreate tables frequently, statistics on those tables restart with the object. Whether or when automatic statistics kick in on a freshly CTAS-created table, and whether that matters for the first downstream queries after a run, is not covered by the cited tutorial — treat it as an open question to test on your workload, and consult current Fabric statistics documentation. This is a genuine public-detail gap; do not assume.

Transaction semantics. Fabric Warehouse supports transactions with snapshot isolation, but its DDL-in-transaction behavior differs from SQL Server, and how dbt-fabric groups its multi-statement materializations transactionally — what a mid-run failure leaves behind — is adapter detail the tutorial does not specify [S1]. Idempotence is your safety net: rerunning dbt run rebuilds whatever a failed run left inconsistent. Design downstream consumers to tolerate brief windows of missing/stale relations during builds, and schedule runs off-peak.

Where things go wrong#

Failure patterns to anticipate, ordered roughly by how often they bite:

  1. Unsupported T-SQL or data types in model SQL. Platform-wide Warehouse limits apply to dbt-generated SQL exactly as to hand-written SQL [S1]. Models ported from SQL Server/Synapse fail on types or commands Fabric Warehouse does not support; audit before migrating, not during the first prod run.
  2. Incremental strategies that silently degrade to rebuilds. See above — validate the compiled SQL (dbt compile, target/ directory) so you know what is actually being executed.
  3. Mid-swap consumer errors. Downstream queries hitting a relation between its DROP and CREATE fail transiently. Retries and off-peak scheduling mitigate; atomic-swap guarantees should not be assumed.
  4. Auth drift in automation. Interactive CLI tokens work on laptops and then fail on schedulers; production runs belong on service principals with managed secret rotation [S1].
  5. Adapter/version skew. The adapter is an open-source project under the microsoft GitHub org and evolves independently of both dbt-core and the Fabric service [S1]. Pin adapter and dbt-core versions, read release notes before upgrading, and file adapter-specific issues on that repository rather than through Fabric support [S1].

What we still don't know publicly#

To be explicit about this lesson's grounding boundary: the cited source establishes that the adapter uses CTAS + DROP/CREATE in place of ALTER/MERGE/TRUNCATE/sp_rename semantics [S1], but not the macro-by-macro statement sequences, transaction wrapping, temp-object naming, or failure-recovery behavior. Those live in adapter source and change across versions. For decisions that depend on them — swap atomicity, mid-run failure states, incremental internals — read the materialization macros of the version you deploy and test on a scratch warehouse. Treat any blog post asserting those details without a version number as stale until proven otherwise.

Worked example: investment analytics gold layer#

At expert level the interesting questions are what "incremental" actually costs on the dbt-fabric adapter, how to snapshot a slowly-changing dimension, and where warehouse compute gets spent. The star schema is the same one built in wh_gold over the lh_analytics silver tables.

Incremental facts and what they really cost. fact_positions is large — one row per portfolio × instrument × day — so a full rebuild every run is wasteful. The instinct is an incremental model:

sql
-- models/marts/fact_positions.sql
{{
  config(
    materialized='incremental',
    unique_key='position_sk',
    incremental_strategy='delete+insert'
  )
}}

select
    {{ dbt_utils.generate_surrogate_key(['position_id','as_of_date']) }} as position_sk,
    position_id,
    portfolio_id,
    instrument_id,
    as_of_date,
    quantity,
    market_value
from {{ ref('stg_positions') }}

{% if is_incremental() %}
where as_of_date > (select max(as_of_date) from {{ this }})
{% endif %}

The caveat that matters: Fabric Warehouse does not support the full MERGE/ALTER/UPDATE surface that dbt's incremental strategies assume on other databases, so the dbt-fabric adapter emulates them with CTAS + DROP/CREATE patterns [S1]. That means an "incremental" run on this adapter can, depending on strategy, still materialise and swap a table rather than doing a cheap in-place merge. Always inspect the compiled SQL (target/run/…) and benchmark against a plain table rebuild — on this adapter the incremental version is not automatically cheaper, and for a fact small enough to rebuild in seconds it may be simpler to skip incrementality entirely. Statement sequencing, transaction wrapping, and swap atomicity aren't documented in the setup tutorial [S1] — pin an adapter version and verify its macros rather than assuming SQL Server semantics.

Snapshotting dim_instrument (SCD2). Instrument attributes drift — an asset gets reclassified, a rating changes. If you need history, a dbt snapshot captures it:

sql
-- snapshots/dim_instrument_snapshot.sql
{% snapshot dim_instrument_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key='instrument_id',
    strategy='check',
    check_cols=['asset_class','rating','currency']
  )
}}
select instrument_id, isin, instrument_name, asset_class, rating, currency
from {{ source('silver', 'instruments') }}
{% endsnapshot %}

Trade-off: SCD2 gives you point-in-time-correct joins (fact rows join to the instrument version valid on their as_of_date) but multiplies dimension rows and complicates every downstream join with validity-window predicates. Reserve it for attributes whose history is genuinely queried; overwrite the rest.

Where compute goes. All dbt cost on Fabric is Warehouse compute [S1], and rebuild-heavy patterns concentrate that consumption at run time — a nightly dbt build that re-CTASes several wide facts is a capacity spike, not a trickle. Levers:

  • Keep V-Order enabled on gold outputs so Direct Lake and Power BI reads off wh_gold stay fast; the write-side cost is paid once at build time.
  • Avoid re-CTASing a wide fact when only a narrow date window changed — that is exactly the case where a genuinely incremental strategy (verified against compiled SQL) earns its keep.
  • Let statistics settle: freshly CTAS-created tables may not have the column statistics the optimiser wants, so the first queries after a rebuild can plan worse. Whether Fabric auto-creates stats on CTAS output is not something to assume — verify for your workload.

Test and telemetry hooks. dbt build runs tests inline, but you also want the result of each run captured. An on-run-end hook can write the run's model/test outcomes to a logging table (or emit them for an Azure Function to forward to Eventhouse for granular telemetry), while the run-level pass/fail summary lands in your orchestration's own audit store. Keep the high-cardinality per-model detail in the streaming sink and the one-row-per-run verdict in the operational metadata store.

Key takeaways#

  • The adapter's defining mechanic is emulation of ALTER/MERGE/TRUNCATE/rename semantics via CTAS + DROP/CREATE — rebuild economics, not in-place mutation [S1].
  • Incremental models may cost far more than their name suggests; inspect compiled SQL and benchmark against a plain table rebuild.
  • All cost is warehouse compute; rebuild-heavy patterns concentrate capacity consumption at run time.
  • Statement-level sequencing, transactionality, and swap atomicity are undocumented in the tutorial — verify in the adapter repo for your pinned version rather than assuming SQL Server behavior [S1].