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 assumes you know what dbt is and how the dbt-fabric adapter connects (see the beginner lesson). Here the question changes from how do I run it to how do I run it well: authentication that survives production, environments and CI/CD, where dbt sits among Fabric's other transformation tools, and the platform constraints you inherit by targeting Fabric Warehouse.

dbt with Fabric Data Warehouse — architecture

Authentication: CLI for humans, service principals for machines#

All dbt-fabric authentication is Microsoft Entra ID based, and Microsoft's guidance splits cleanly by who is running dbt [S1]:

  • Interactive development — authenticate with the Azure CLI (az login); the adapter picks up your CLI token. Developers use their own identities, so warehouse access mirrors each person's actual permissions and is auditable per user [S1].
  • Automated/production runs — use a service principal [S1]: an application identity with a client ID and secret (or certificate) granted access to the warehouse. CI pipelines and schedulers should never run as a person; a departing employee or an expired user token must not be able to break the nightly build.

The practical pattern is one profiles.yml with multiple targets — a dev target using CLI auth against a development warehouse, and a prod target using service principal auth, with the secret supplied via environment variables ({{ env_var('...') }}) rather than committed to the repo. Grant the service principal the least warehouse permission that lets it build its schemas.

Environments and CI/CD#

dbt's environment story maps naturally onto Fabric workspaces: give development, test, and production separate workspaces (or at minimum separate warehouses/schemas), and point each dbt target at its own. Because a dbt project is plain text, the delivery pipeline is standard software engineering:

  1. Developers branch, edit models, and run against the dev target.
  2. A pull request triggers CI: dbt build (run + test) against a test warehouse using the service principal.
  3. Merge to main triggers a production dbt build, again under the service principal.

Two Fabric-specific notes. First, your build agent needs the same client prerequisites as a laptop — Python and the Microsoft ODBC Driver for SQL Server — because dbt executes outside Fabric and connects over the SQL analytics endpoint [S1]. Second, project portability cuts both ways: since switching platforms is essentially an adapter install plus a profiles.yml type change [S1], teams migrating from Azure Synapse dedicated SQL pools can usually carry their dbt project across and treat the migration as a re-point-and-rebuild — then spend their effort validating dialect edge cases rather than rewriting pipelines.

Orchestration: Airflow inside Data Factory#

dbt has no native scheduler in Fabric. Microsoft's documented operationalization path is to pair dbt with Apache Airflow inside Fabric Data Factory, using Airflow DAGs to trigger dbt run/dbt build on a schedule [S1]. Architecturally that means the orchestration layer is Airflow's Python environment hosted by Data Factory, your dbt project is made available to that environment, and the DAG's tasks invoke dbt with the production (service principal) target.

Decision guidance: if you already operate CI/CD runners (GitHub Actions, Azure DevOps), scheduling dbt build there is a perfectly serviceable alternative and keeps one execution environment for CI and production. The Airflow-in-Data-Factory route earns its complexity when you want orchestration visible inside Fabric, need dependencies between dbt runs and other Fabric activities, or already think in DAGs. Either way, remember the runs themselves execute in the warehouse — the orchestrator only issues commands.

Placement: dbt vs Dataflow Gen2 vs notebooks#

Choosing dbt vs other Fabric transformation tools

Fabric offers several transformation surfaces, and the honest answer is that they overlap. A defensible division of labor:

  • dbt — SQL-first transformation within the Warehouse, when you want version control, tests, lineage, and code review as first-class citizens, or when a team brings an existing dbt practice. Its unit of work is the model; its output is warehouse tables/views.
  • Dataflow Gen2 — low-code, Power Query-based ingestion and shaping, strongest for analysts, for connector breadth, and for landing data into Fabric. It is a poor fit for a large, interdependent transformation codebase.
  • Notebooks (Spark) — Python/Scala transformations over Lakehouse/OneLake data, unstructured or semi-structured work, ML feature engineering, and anything that outgrows SQL. They operate on the lake side rather than through the warehouse's T-SQL endpoint.

A common composite: ingest with pipelines/Dataflow Gen2, do heavy or non-tabular work in notebooks, and let dbt own the modeled, tested semantic-ready layer inside the Warehouse. Avoid splitting one logical transformation layer across two tools — the lineage and testing benefits of dbt evaporate if half the logic lives in a dataflow it cannot see.

Note also that dbt-fabric targets the Warehouse experience. If your architecture is Lakehouse-centric, the SQL analytics endpoint over a Lakehouse is read-only for T-SQL, so dbt cannot materialize tables there; a Warehouse (or a different execution approach) is required for dbt's writes. Verify current endpoint capabilities against Microsoft documentation before committing.

The constraint you inherit: Fabric Warehouse's T-SQL surface#

Fabric Warehouse does not support the full SQL Server command set, and dbt projects inherit those platform-wide limits — unsupported T-SQL operations and unsupported table data types constrain your models exactly as they would constrain hand-written T-SQL; they are not adapter bugs [S1]. The adapter compensates for some missing commands (MERGE-like and ALTER-like behaviors) by generating CREATE TABLE AS SELECT plus DROP/CREATE sequences instead [S1] — the expert lesson examines what that means for incremental models and cost.

At the architecture level, the implications to plan for:

  • Review model SQL and seed/data types early. Anything relying on SQL Server-specific types or commands should be validated against Fabric Warehouse's documented T-SQL surface before migration, not after.
  • Expect rebuild-style DDL. Because schema evolution is implemented via recreate patterns rather than in-place ALTER [S1], treat model contract changes as full-table events in planning and capacity terms.
  • File adapter issues in the right place. The adapter is open source under the microsoft GitHub org, and that repository — not general Fabric support — is the channel for adapter-specific problems [S1]. Factor that support model into your platform-risk assessment.

Worked example: investment analytics gold layer#

This walks the full staging→marts flow for an investment book of record, the environment split, and how the whole thing runs on a schedule. Silver conformed Delta tables live in a lakehouse lh_analytics; dbt builds the star schema in the Warehouse wh_gold.

Declaring the silver source. dbt needs to know where "silver" is. Because gold lives in the Warehouse and the tables live in the lakehouse, the models read them through the Warehouse's cross-database three-part names (lakehouse.schema.table) — the SQL analytics endpoint over lh_analytics makes the lakehouse queryable from wh_gold:

yaml
# models/staging/sources.yml
version: 2
sources:
  - name: silver
    database: lh_analytics      # the lakehouse, reached via its SQL analytics endpoint
    schema: dbo
    tables:
      - name: positions
      - name: transactions
      - name: returns_daily
      - name: risk_measures
      - name: instruments
      - name: portfolios

Staging → marts. Staging views (stg_positions, stg_returns, stg_risk_measures, …) normalise each silver table. Marts assemble the star: conformed dimensions (dim_date, dim_instrument, dim_portfolio, dim_benchmark) and facts. A daily-return fact sits at the grain one row per portfolio × instrument × date and points at the dimensions:

sql
-- models/marts/fact_returns_daily.sql
{{ config(materialized='table') }}

select
    r.as_of_date,
    r.portfolio_id,
    r.instrument_id,
    r.daily_return,
    r.benchmark_id
from {{ ref('stg_returns') }} r

Relationship + grain tests. The most valuable tests at this level assert referential integrity and grain. relationships checks every foreign key resolves to a dimension; a unique test on the composite business key guards the grain:

yaml
# models/marts/schema.yml
version: 2
models:
  - name: fact_returns_daily
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [as_of_date, portfolio_id, instrument_id]
    columns:
      - name: instrument_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_instrument')
              field: instrument_id
      - name: portfolio_id
        tests:
          - relationships:
              to: ref('dim_portfolio')
              field: portfolio_id

Environments. Keep dev and prod as separate targets in profiles.yml, each pointing at a different Warehouse (or schema) so a developer's dbt run never touches production gold:

yaml
investment_gold:
  target: dev
  outputs:
    dev:
      type: fabric
      server: "<wh_gold_dev endpoint>"
      database: wh_gold_dev
      authentication: CLI          # developer's own Entra ID via az login
      schema: dbo
    prod:
      type: fabric
      server: "<wh_gold endpoint>"
      database: wh_gold
      authentication: ServicePrincipal   # unattended service principal for scheduled runs
      schema: dbo

CLI (interactive Entra ID) auth is the documented pattern for a developer at the keyboard; a service principal is the pattern for unattended runs [S1].

Scheduling. dbt has no native scheduler on Fabric. The documented path is an Apache Airflow job inside Data Factory that runs dbt build (which is run + test in dependency order) on a cadence [S1]. So the production loop is: silver refreshes → the Airflow job fires dbt build --target prod → gold is rebuilt and tested in one pass.

Point-in-time correctness for risk. Risk measures (VaR, exposures, sensitivities) are only meaningful as of the date they were computed. When you join stg_risk_measures to positions, join on the risk engine's own as_of_date, not today's date — otherwise you attach yesterday's risk to today's book. This is a modelling decision, not a platform behaviour: make the as_of_date part of the fact's grain and carry it through every downstream join.

Placing the workload. dbt is the tested, version-controlled SQL modelling layer in the Warehouse; Dataflow Gen2 is for low-code ingestion; Spark notebooks are for the Python/Delta-shaped conform work in silver. The clean division: notebooks own bronze→silver, dbt owns silver→gold.

Key takeaways#

  • Entra ID everywhere: CLI auth for developers, service principals for CI and scheduled runs [S1].
  • Map dbt targets to Fabric workspaces per environment; run CI with dbt build on standard runners equipped with Python + ODBC [S1].
  • Scheduled production runs are documented via Apache Airflow in Data Factory, not a native dbt scheduler [S1].
  • Choose dbt for the tested, versioned SQL modeling layer in the Warehouse; Dataflow Gen2 for low-code ingestion; notebooks for Spark-shaped work.
  • You inherit Fabric Warehouse's T-SQL and data-type limits wholesale — design models to them from day one [S1].