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.

What dbt is, in one paragraph#

dbt (data build tool) is an open-source framework for transforming data inside a data warehouse using SQL. Instead of writing ad-hoc scripts or stored procedures, you express each transformation as a model — a SELECT statement saved as a .sql file in a version-controlled project. dbt compiles those models, figures out the order they depend on each other, and runs them against your warehouse. It brings software-engineering habits — version control, code review, automated testing, generated documentation — to the SQL transformation layer.

dbt with Fabric Data Warehouse — architecture

The core dbt concepts#

Models. A model is just a SELECT statement. dbt wraps it in the DDL needed to turn the result into a real object in the warehouse. You never write CREATE TABLE yourself; you describe what the data should look like and dbt handles how it lands.

ref(). Models reference each other with the {{ ref('other_model') }} function instead of hard-coded table names. From these references dbt builds a dependency graph (a DAG) and always runs upstream models before downstream ones. This is the single most important dbt idea: the dependency order of your entire transformation pipeline is derived from the code itself, not maintained by hand.

Materializations. A materialization decides what a model becomes in the warehouse: a view (recomputed on every query), a table (rebuilt on every dbt run), an incremental table (only new/changed rows processed on each run), or an ephemeral model (inlined as a CTE, never persisted). You choose per model with a config line.

Tests. dbt ships declarative data tests — not_null, unique, accepted_values, relationships — that you attach to columns in a YAML file, plus the ability to write custom SQL tests. dbt test runs them all and fails loudly when data breaks a contract.

Docs. dbt generates a browsable documentation site from your models, their descriptions, and the dependency graph, so the lineage of every table is discoverable rather than tribal knowledge.

Where Fabric fits: the dbt-fabric adapter#

dbt itself is warehouse-agnostic; a per-platform adapter translates dbt's compiled SQL into each engine's dialect. Fabric Data Warehouse is supported through the dbt-fabric adapter — a Python package on PyPI installed with pip install dbt-fabric — rather than through anything built into the Fabric portal [S1]. dbt runs outside Fabric, on your laptop or a build server: you need Python, the Microsoft ODBC Driver for SQL Server, and an existing Fabric Warehouse (a trial capacity works) before the adapter can connect [S1]. The adapter is open source, maintained on GitHub under the microsoft organization, and adapter bugs are filed there rather than through general Fabric support [S1].

Within the Fabric ecosystem, dbt targets the Warehouse workload specifically — the T-SQL, SQL-analytics-endpoint side of Fabric. Your dbt models compile to T-SQL and their outputs are ordinary warehouse tables and views, which downstream Fabric experiences (Power BI, SQL queries, other pipelines) consume like any other warehouse object.

Setting it up#

The connection lives in dbt's profiles.yml file. For Fabric you configure an output block with type: fabric, the SQL analytics endpoint of your warehouse as the host, the database and schema names, an ODBC driver string, and an authentication mode [S1]. Authentication is Microsoft Entra ID based — for local development the tutorial path is signing in with the Azure CLI (az login) and letting dbt pick up those credentials [S1].

A nice consequence of the adapter model: dbt projects are largely portable between platforms. Moving a project from, say, an Azure Synapse dedicated SQL pool to Fabric Warehouse is documented as installing the new adapter, changing the type value in profiles.yml, and rebuilding — no model rewrite is implied [S1] (dialect-specific SQL in your models can still need attention, but the framework itself does not change).

The everyday workflow#

Once the profile is in place, the standard validation loop — demonstrated in Microsoft's tutorial against the open-source jaffle_shop demo project — is [S1]:

  1. dbt debug — verifies your profile, credentials, and connectivity to the warehouse.
  2. dbt seed — loads small CSV seed files (sample or reference data) into warehouse tables.
  3. dbt run — compiles every model and executes it against the warehouse in dependency order.
  4. dbt test — runs the declared data tests against the freshly built tables.

That loop — edit a model, dbt run, dbt test — is the daily rhythm of dbt development. Everything is a text file, so the whole project sits in git and moves through pull requests like application code.

Why you'd use dbt on Fabric at all#

Fabric already has transformation tools (Dataflow Gen2, notebooks, pipelines), so dbt earns its place mainly when: your team already knows dbt and has projects to bring; you want transformation logic in version-controlled, testable SQL rather than visual tools; or you want one framework spanning multiple warehouse platforms. Because support comes via a community/Microsoft-maintained open-source adapter rather than a first-party Fabric feature [S1], it is worth confirming that the T-SQL your models need is supported by Fabric Warehouse — a theme the intermediate and expert lessons pick up in depth.

Worked example: investment analytics gold layer#

Here is the smallest end-to-end slice of a real project — building the gold layer for an investment book of record. Silver already holds conformed Delta tables in a lakehouse called lh_analytics (positions, transactions, instruments, portfolios, returns_daily, risk_measures); dbt turns those into a clean star schema in the Fabric Warehouse wh_gold.

Project layout. A dbt project is just folders of .sql and .yml files:

graphql
investment_gold/
  dbt_project.yml
  models/
    staging/
      stg_positions.sql
      schema.yml
    marts/
      dim_instrument.sql
      fact_positions.sql
      schema.yml

A staging model is a thin view that reads one silver table and renames/casts columns so nothing downstream touches raw names. Staging models are usually materialized as views (cheap, always fresh):

sql
-- models/staging/stg_positions.sql
{{ config(materialized='view') }}

select
    position_id,
    portfolio_id,
    instrument_id,
    as_of_date,
    cast(quantity as decimal(28,6))   as quantity,
    cast(market_value as decimal(28,2)) as market_value
from {{ source('silver', 'positions') }}

A dimension gives each instrument one row with descriptive attributes:

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

select
    instrument_id,
    isin,
    instrument_name,
    asset_class,
    currency
from {{ source('silver', 'instruments') }}

Tests are declared, not coded. This schema.yml says the key must be unique and never null — dbt turns each into a SQL query that must return zero rows:

yaml
# models/marts/schema.yml
version: 2
models:
  - name: dim_instrument
    columns:
      - name: instrument_id
        tests: [unique, not_null]

The loop. From your terminal (dbt runs outside Fabric, in Python, against the Warehouse [S1]):

bash
dbt debug      # confirms the profiles.yml connection to wh_gold works
dbt run        # builds stg_positions (view) and dim_instrument (table) in wh_gold
dbt test       # runs the unique + not_null checks

If dbt run succeeds you now have a dim_instrument table and a stg_positions view sitting in wh_gold, queryable with ordinary T-SQL and ready for Power BI. That is the whole beginner rhythm: write a model, declare a test, run, test — then add the next model. The full investment star schema (four dimensions, four facts) is just more of these same two file shapes.

Key takeaways#

  • dbt turns SQL transformations into a tested, versioned, documented project; ref() gives you an automatic dependency graph.
  • Fabric Warehouse is a dbt target via the dbt-fabric PyPI adapter, driven from outside Fabric with Python and the ODBC driver [S1].
  • Connection setup is a profiles.yml block (type: fabric, SQL analytics endpoint, Entra ID auth) [S1].
  • The verify-everything loop is dbt debug → dbt seed → dbt run → dbt test [S1].