dbt-fabricspark 1.12.13

A Microsoft Fabric Spark adapter plugin for dbt

Fabric Spark - dbt

dbt adapter for Fabric Spark supporting SQL models.

dbt Docs · Fabric Lakehouse with Spark · Fabric Lakehouse Livy API

dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.

dbt is the T in ELT. Organize, cleanse, denormalize, filter, rename, and pre-aggregate the raw data in your warehouse so that it's ready for analysis.

dbt-fabricspark

The dbt-fabricspark package contains all of the code enabling dbt to work with Apache Spark in Microsoft Fabric. This adapter connects to Fabric Lakehouses via Livy endpoints and supports both schema-enabled and non-schema Lakehouse configurations.

Key Features

• Livy session management with session reuse and robust connectivity across dbt runs
• Lakehouse with schema support — auto-detects schema-enabled lakehouses and uses three-part naming (lakehouse.schema.table)
• Lakehouse without schema — standard two-part naming (lakehouse.table)
• Materializations: table, view, incremental (append, merge, insert_overwrite, microbatch, delete+insert), seed, snapshot
• Fabric Environment support via environmentId configuration
• Security: credential masking, UUID validation, HTTPS + domain validation, thread-safe token refresh
• Resilience: HTTP 5xx retry with exponential backoff, bounded polling with configurable timeouts

Getting started

• Install dbt
• Read the introduction and viewpoint

To contribute to this adapter codebase, see CONTRIBUTING.md.

Installation

pip install dbt-fabricspark

For local development using Azure CLI authentication (authentication: CLI), install with the cli extra:

pip install dbt-fabricspark[cli]

Note: The azure-cli is an optional dependency is only required for the CLI authentication mode. Service Principal (SPN) and Fabric Notebook (fabric_notebook) authentication modes do not need it.

Issues, bug-bashing, help us help you

⚠️ Here's how you can get your issue triaged and fixed ASAP

In the age of AI, we should be innovating and shipping high-quality software daily.

So in this adapter, we try to fix bugs and ship features fast - and keep an extremely high bar for test coverage in CI before PRs merge to main.

Once you open an issue, please - try to be as descriptive as possible to give our human/AI maintainers the necessary details to reproduce the issue rapidly.

For example - if you can - create a dummy repro dbt project in your GitHub account - so the maintainers can reproduce your problem ASAP - see an example a well-written GitHub issue here.

If the issue is complex - once we have a fix identified, to gain more confidence, we might ask you install the adapter right from a PR branch to ensure the repro is gone in your setup as well, like so:

pip install git+https://github.com/microsoft/dbt-fabricspark.git@dev/somebranch/123

Configuration

Use a Livy endpoint to connect to Apache Spark in Microsoft Fabric. Configure your profiles.yml to connect via Livy endpoints.

Connection Modes

The adapter supports two connection modes via the livy_mode setting:

• Local mode (livy_mode: local) — Connects to a self-hosted Spark instance running in a Docker container (contributed by @mdrakiburrahman). This mode supports the reuse_session flag and does not require Fabric compute, making it ideal for offline development and testing.

• Fabric mode (livy_mode: fabric, default) — Connects to Apache Spark in Microsoft Fabric via the Fabric Livy API. For development workflows, enable reuse_session: true to persist the Livy session ID to a local file (configured via session_id_file, defaults to ./livy-session-id.txt). On subsequent dbt runs, the adapter reuses the existing session from the persisted file instead of creating a new one. If the file does not exist or the session has expired, a new session is created automatically.

Lakehouse without Schema

For standard Lakehouses (schema not enabled), use two-part naming. The schema field is set to the lakehouse name:

fabric-spark-test:
  target: fabricspark-dev
  outputs:
    fabricspark-dev:
        # Connection
        type: fabricspark
        method: livy
        endpoint: https://api.fabric.microsoft.com/v1
        workspaceid: <your-workspace-id>
        lakehouseid: <your-lakehouse-id>
        lakehouse: my_lakehouse
        schema: my_lakehouse
        threads: 1

        # Authentication (CLI for local dev, SPN for CI/CD)
        authentication: CLI
        # client_id: <your-client-id>              # Required for SPN
        # tenant_id: <your-tenant-id>              # Required for SPN
        # client_secret: <your-client-secret>      # Required for SPN

        # Fabric Environment (optional)
        # environmentId: <your-environment-id>

        # Session management
        reuse_session: true
        # session_idle_timeout: "30m"              # Opt-in only. Setting this triggers
                                                   # Fabric to bypass starter pools and
                                                   # cold-start an on-demand cluster.
        # session_id_file: ./livy-session-id.txt   # Default path

        # Timeouts
        connect_retries: 1
        connect_timeout: 10
        http_timeout: 120                          # Seconds per HTTP request
        session_start_timeout: 600                 # Max wait for session start (10 min)
        statement_timeout: 3600                    # Max wait for statement result (1 hour)
        poll_wait: 10                              # Seconds between session start polls
        poll_statement_wait: 5                     # Seconds between statement result polls
        azure_cli_process_timeout: 10              # Subprocess timeout for `az` token refresh
                                                   # (CLI auth). Raise under high concurrency.

        # Retry & Shortcuts
        retry_all: true
        # create_shortcuts: false
        # shortcuts_json_str: '<json-string>'

        # Spark configuration (optional)
        # spark_config:
        #   name: "my-spark-session"
        #   spark.executor.memory: "4g"

In this mode:

• Tables are referenced as lakehouse.table_name
• The schema field should match the lakehouse name
• All objects are created directly under the lakehouse

Lakehouse with Schema (Schema-Enabled)

For schema-enabled Lakehouses, you can organize tables into schemas within the lakehouse. The adapter auto-detects whether a lakehouse has schemas enabled via the Fabric REST API (properties.defaultSchema):

fabric-spark-test:
  target: fabricspark-dev
  outputs:
    fabricspark-dev:
        type: fabricspark
        method: livy
        endpoint: https://api.fabric.microsoft.com/v1
        workspaceid: <your-workspace-id>
        lakehouseid: <your-lakehouse-id>
        lakehouse: my_lakehouse
        schema: my_schema                          # Different from lakehouse name

In this mode:

• Tables are referenced using three-part naming: lakehouse.schema.table_name
• The schema field specifies the target schema within the lakehouse
• dbt's generate_schema_name and generate_database_name macros are lakehouse-aware
• Schemas are created automatically via CREATE DATABASE IF NOT EXISTS lakehouse.schema
• Incremental models use persisted staging tables (instead of temp views) to work around Spark's REQUIRES_SINGLE_PART_NAMESPACE limitation

Schema Detection

The adapter detects whether a lakehouse has schemas enabled using two complementary mechanisms:

1. Runtime detection (Fabric REST API): During connection.open(), the adapter calls the Fabric REST API to fetch lakehouse properties. If the response contains defaultSchema, the lakehouse is treated as schema-enabled and three-part naming is used.

2. Parse-time detection (profile heuristic): During manifest parsing (before any connection is opened), the adapter checks whether schema differs from lakehouse in your profile. When they differ (e.g., lakehouse: bronze, schema: dbo), the adapter infers schema-enabled mode. This ensures correct schema resolution at compile time.

Important: For schema-enabled lakehouses, always set schema to a value different from lakehouse in your profile (e.g., schema: dbo). If schema equals lakehouse, the adapter cannot distinguish schema-enabled from non-schema mode at parse time, and the lakehouse name will be used as the schema name instead.

Lakehouse Type	lakehouse	schema	Naming
Without schema	my_lakehouse	my_lakehouse	my_lakehouse.table_name
With schema	my_lakehouse	dbo	my_lakehouse.dbo.table_name

Cross-Lakehouse Writes

A single profile can write to multiple lakehouses using the database config on individual models. The profile's lakehouse is the default target; set database on a model to redirect writes to a different lakehouse in the same workspace.

# profiles.yml — profile targets the "bronze" lakehouse
fabric-spark:
  type: fabricspark
  lakehouse: bronze
  schema: dbo
  # ... other settings

-- models/silver/silver_orders.sql — writes to the "silver" lakehouse
{{ config(
    materialized='table',
    database='silver',
    schema='dbo'
) }}

select * from {{ ref('bronze_orders') }}

In this example:

• Seeds and bronze models write to bronze.dbo.* (the default lakehouse)
• Silver models write to silver.dbo.* via database='silver'
• Gold models write to gold.dbo.* via database='gold'
• All three lakehouses must exist in the same Fabric workspace and have schemas enabled

Cross-Workspace 4-Part Naming

For multi-workspace topologies — e.g. dev workspace reading shared marts from a prod workspace, or a single dbt project orchestrating bronze/silver/gold across separate workspaces — set workspace_name on a model's config(). The adapter renders the relation as a backtick-quoted four-part name so Fabric Spark routes the statement to the correct workspace catalog. Both reads (federated SELECT) and writes (cross-workspace CREATE TABLE AS SELECT) are supported against schema-enabled lakehouses.

If you set this in dbt_project.yml, prefer +meta.workspace_name to avoid dbt's custom-config deprecation warning:

models:
  my_project:
    marts:
      +meta:
        workspace_name: SharedWorkspace

Reads — stub-and-ref pattern

-- models/silver/from_prod_orders.sql
{{ config(
    materialized='view',
    workspace_name='ProdWorkspace',
    database='prod_silver_lh',
    schema='dbo',
    alias='orders'
) }}

-- This stub is never selected for materialization. It exists so other
-- models can `ref('from_prod_orders')` and resolve to the prod relation.
select cast(null as int) as id

Other models then read it normally via ref():

-- models/silver/orders_metrics.sql
{{ config(materialized='table') }}

select id, count(*) as n
from {{ ref('from_prod_orders') }}
group by id

dbt renders the cross-workspace reference as:

`ProdWorkspace`.`prod_silver_lh`.`dbo`.orders

Writes — cross-workspace CTAS

A model can also be materialized into another workspace by setting workspace_name directly on the target model. The Livy session stays bound to your profile's workspace; Fabric routes the CREATE TABLE against the remote workspace's catalog:

-- models/marts/shared_orders.sql
{{ config(
    materialized='table',
    file_format='delta',
    workspace_name='SharedWorkspace',
    database='shared_lh',
    schema='marts'
) }}

select * from {{ ref('orders') }}

dbt emits:

create or replace table `SharedWorkspace`.`shared_lh`.`marts`.shared_orders as
select * from …

The target schema (marts in shared_lh of SharedWorkspace) is created automatically by the adapter's standard schema pre-create flow — Fabric Livy supports cross-workspace CREATE DATABASE IF NOT EXISTS \SharedWorkspace`.`shared_lh`.`marts``, so no manual setup is required.

Use file_format='delta' for idempotent re-runs. The adapter emits CREATE OR REPLACE TABLE for delta tables, which re-materializes cleanly. Non-delta cross-workspace writes will fail on the second run with TABLE_ALREADY_EXISTS because adapter.get_relation is workspace-unaware and cannot detect the existing remote relation to drop it first.

Materializations validated end-to-end: table (full CTAS) and incremental (initial CTAS + MERGE INTO + --full-refresh). Other materializations (view, seed, snapshot, materialized_lake_view) share the same render and ensure_database_exists plumbing and should work cross-workspace, but are not exercised by functional tests in this repo.

Schema-enabled lakehouses only. Fabric Livy supports 4-part naming only against schema-enabled lakehouses. Setting workspace_name against a non-schema-enabled target raises a parse-time error.

Profile-level default workspace

Instead of setting workspace_name on every model, you can set it once in profiles.yml as a target-scoped default. When set, all relations in that target automatically use the workspace as a prefix — without any per-model config.

# profiles.yml
my_profile:
  outputs:
    prod:
      type: fabricspark
      workspace_name: "vd-domain-prod"   # default workspace for this target
      lakehouse: silver_lh
      schema: dbo
      ...
    dev:
      type: fabricspark
      workspace_name: "vd-ephemeral-dev"
      ...

Precedence (lowest → highest):

Source	Example
No workspace (current default)	two- or three-part names
Profile workspace_name	all relations in target get 4-part names
Model config(workspace_name=...)	overrides the profile default for that model

The profile value is accessible in Jinja as target.workspace_name, so you can inspect or branch on it in macros.

Schema-enabled lakehouses only. If workspace_name is set in the profile but the target lakehouse does not have schemas enabled, the adapter logs a warning and ignores the value.

How it works

Your profile binds to one workspace + lakehouse — that's where the Livy session lives, and every SQL statement is issued from that session. workspace_name is a rendering decoration applied to the relation; Fabric Livy then federates the statement through its metastore so a SELECT returns rows from the other workspace and a CREATE TABLE AS SELECT writes into the other workspace's lakehouse.

When to use it

• Cross-workspace reads — A dev workspace references shared dimensions, regulatory data, or prod marts without copying via OneLake shortcuts.
• Cross-workspace writes — A consolidation pipeline that aggregates data from one workspace into a shared analytics workspace, without spinning up a second dbt project / profile per target workspace.
• Multi-workspace project topologies — One dbt project that needs to read from and/or write to several workspaces.

When not to use it

• Non-schema-enabled lakehouses — Use OneLake shortcuts instead; the adapter errors at parse time to surface the constraint.

Permissions

The principal authenticated by your profile (CLI user or SPN) must have read access on both workspaces — the local one (where the Livy session runs) and the remote one whose data you reference.

Quoting & casing

Each segment is independently backtick-quoted, so workspace names with spaces or mixed case (e.g. dbt Fabric Spark 1) round-trip correctly.

Sources

{{ source(...) }} supports cross-workspace 4-part naming too — declare workspace_name under a source's (or table's) config block in sources.yml, just like models use config(workspace_name=...). Source reads, freshness, and generic tests on sources all render the 4-part name against the remote workspace (schema-enabled lakehouses only).

# models/sources.yml
sources:
  - name: my_bronze_source
    database: remote_lakehouse
    schema: my_schema
    config:
      workspace_name: RemoteWorkspaceName
    tables:
      - name: my_table

select * from {{ source('my_bronze_source', 'my_table') }} then resolves to `RemoteWorkspaceName`.`remote_lakehouse`.`my_schema`.my_table.

Case-sensitive identifiers

By default the adapter renders identifiers unquoted, so Fabric Spark folds them to lowercase (MyTable → mytable). To preserve exact casing, set quote_identifiers: true — but two settings are required together:

# profiles.yml
my_profile:
  outputs:
    prod:
      type: fabricspark
      quote_identifiers: true
      spark_config:
        name: my-session
        conf:
          spark.sql.caseSensitive: "true"

• quote_identifiers: true backtick-quotes every relation's identifier so the emitted SQL preserves casing.
• spark.sql.caseSensitive: "true" (under spark_config.conf) makes Spark resolve the mixed-case names instead of folding them. The adapter warns at connection time if quote_identifiers is on without it.

⚠️ spark.sql.caseSensitive is session-wide and affects columns too — it changes column resolution across every model (joins, select *, schema comparisons, MERGE/incremental matching can break if column casing is inconsistent). Enable only when you need case-sensitive object names.

Both settings default to off, so existing projects render byte-identical SQL.

Incremental strategies

incremental models accept these incremental_strategy values via config():

Strategy	file_format	unique_key	Behavior
append (default)	any	optional	Insert all new rows; no updates or deletes.
merge	delta	optional	MERGE INTO — update matched rows, insert the rest. Supports advanced merge options.
insert_overwrite	delta	—	Overwrite matched partitions (partition_by), or the whole table when unpartitioned.
microbatch	delta	—	Per-batch delete (by partition_by) then insert; used by dbt's microbatch.
delete+insert	delta	required	Delete target rows whose unique_key(s) appear in the new data, then insert all new rows.

delete+insert is a key-based full row-replace: it deletes every target row whose unique_key appears in the incoming set and then inserts all incoming rows. Use it instead of merge when you want matched keys replaced wholesale rather than updated column-by-column. It requires file_format: delta and a unique_key (a single column or a list) — omitting the key raises a compile-time error. Optional incremental_predicates are ANDed into the delete match to scope it to a window.

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='id',
    file_format='delta'
) }}

Advanced merge options

When incremental_strategy='merge' (on file_format: delta) you can shape the generated MERGE INTO statement with the following optional config() keys. All of them default to today's behavior, so existing merge models are unaffected.

Config	Type	Default	Effect
target_alias	string	DBT_INTERNAL_DEST	Alias used for the target relation in the MERGE (and in your conditions).
source_alias	string	DBT_INTERNAL_SOURCE	Alias used for the staged source in the MERGE (and in your conditions).
matched_condition	string	—	Extra predicate AND (…) on the WHEN MATCHED … THEN UPDATE clause.
not_matched_condition	string	—	Extra predicate AND (…) on the WHEN NOT MATCHED … THEN INSERT clause.
skip_matched_step	bool	false	Omit the WHEN MATCHED clause entirely (insert-only merge).
skip_not_matched_step	bool	false	Omit the WHEN NOT MATCHED clause entirely (update-only merge).
not_matched_by_source_condition	string	—	Extra predicate AND (…) on the WHEN NOT MATCHED BY SOURCE clause.
not_matched_by_source_action	string	—	Emits WHEN NOT MATCHED BY SOURCE when set to delete or update set … — e.g. propagate deletes.
merge_with_schema_evolution	bool	false	Enable MERGE schema evolution so new source columns are added to the target automatically.

matched_condition, not_matched_condition and not_matched_by_source_condition should reference the target/source using the aliases above (defaulting to DBT_INTERNAL_DEST / DBT_INTERNAL_SOURCE). not_matched_by_source_action only produces a clause when it is delete or starts with update; any other value is ignored. merge_with_schema_evolution sets the standard Delta spark.databricks.delta.schema.autoMerge.enabled session setting before the merge rather than emitting a proprietary SQL clause, so it works on Fabric Runtime 1.3 (Spark 3.5 / Delta Lake 3.2) and local Livy alike. These options require Fabric Runtime 1.3 or newer.

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    file_format='delta',
    target_alias='t',
    source_alias='s',
    matched_condition='s.updated_at > t.updated_at',
    not_matched_by_source_condition="t.status <> 'archived'",
    not_matched_by_source_action='delete',
    merge_with_schema_evolution=true
) }}

Configuration Reference

Option	Type	Default	Description
type	string	—	Must be fabricspark
method	string	livy	Connection method
endpoint	string	https://api.fabric.microsoft.com/v1	Fabric API endpoint URL
workspaceid	string	—	Fabric workspace UUID
lakehouseid	string	—	Lakehouse UUID
lakehouse	string	—	Lakehouse name
schema	string	—	Schema name. Must equal lakehouse for non-schema lakehouses, must differ from lakehouse for schema-enabled (e.g., dbo)
workspace_name	string	—	Optional default workspace for cross-workspace 4-part naming. When set and the lakehouse has schemas enabled, all relations without a model-level workspace_name will be rendered with this workspace prefix. Ignored for non-schema lakehouses. Exposed as target.workspace_name in Jinja.
quote_identifiers	bool	false	When true, backtick-quotes table identifiers so Fabric Spark preserves their casing instead of folding to lowercase. Requires spark_config.conf { "spark.sql.caseSensitive": "true" } to take effect (the adapter warns if it's missing). Session-wide — also affects column resolution. See Case-sensitive identifiers.
threads	int	1	Number of threads for parallel execution
Authentication
authentication	string	CLI	Auth method: CLI, SPN, or fabric_notebook
client_id	string	—	Service principal client ID (SPN only)
tenant_id	string	—	Azure AD tenant ID (SPN only)
client_secret	string	—	Service principal secret (SPN only)
accessToken	string	—	Direct access token (optional)
Environment
environmentId	string	—	Fabric Environment ID for Spark configuration
spark_config	dict	{}	Spark session configuration (must include name key)
Session Management
reuse_session	bool	false	Keep Livy sessions alive for reuse across runs
session_id_file	string	./livy-session-id.txt	Path to file storing session ID for reuse
session_idle_timeout	string	—	Optional Livy session idle timeout (e.g. 30m, 1h). Leave unset to keep Fabric starter-pool acceleration; setting a value injects spark.livy.session.idle.timeout into the session conf, which Fabric treats as session-immutable and falls back to an on-demand cluster.
high_concurrency	bool	true	Use high-concurrency Livy API so each dbt thread gets its own REPL — see High-concurrency Livy
Timeouts & Polling
connect_retries	int	1	Number of connection retries
connect_timeout	int	10	Connection timeout in seconds
http_timeout	int	120	Seconds per HTTP request to Fabric API
session_start_timeout	int	600	Max seconds to wait for session start
statement_timeout	int	3600	Max seconds to wait for statement result
poll_wait	int	10	Seconds between session start polls
poll_statement_wait	int	5	Seconds between statement result polls
azure_cli_process_timeout	int	10	Subprocess timeout (seconds) for AzureCliCredential when acquiring/refreshing tokens under authentication: CLI. Raise it when high-concurrency builds trigger az account get-access-token refresh storms that fail with "Failed to invoke the Azure CLI". No effect for other auth methods.
Other
retry_all	bool	false	Retry all operations on failure
create_shortcuts	bool	false	Enable Fabric shortcut creation
shortcuts_json_str	string	—	JSON string defining shortcuts
livy_mode	string	fabric	fabric for Fabric cloud, local for local Livy
livy_url	string	http://localhost:8998	Local Livy URL (local mode only)

Authentication Modes

Mode	Value	Use Case	Required Fields
Azure CLI	CLI	Local development. Uses az login credentials.	None (run az login first)
Service Principal	SPN	CI/CD and automation. Uses Azure AD app registration.	client_id, tenant_id, client_secret
Fabric Notebook	fabric_notebook	Running dbt inside a Fabric notebook. Uses notebookutils.credentials.	None (runs in Fabric runtime)

High-concurrency Livy

By default the adapter uses Fabric's high-concurrency Livy API (high_concurrency: true). Each dbt thread acquires its own HC session — and therefore its own REPL — inside a single underlying Livy session shared via a deterministic sessionTag derived from (workspaceid, lakehouseid). Statements from different REPLs execute in parallel inside the same Spark application, so increasing threads buys us throughput.

When reuse_session: true, the underlying Livy session also stays warm between dbt invocations (until Fabric's spark.livy.session.idle.timeout elapses), so the next run skips Spark cold-start entirely.

Set high_concurrency: false to fall back to the single-session-per-process mode, where one Livy session serves every thread and statements queue FIFO inside — useful as an escape hatch when debugging any problems with the high-concurrency API.

Fabric packs REPLs onto one underlying Livy session up to spark.highConcurrency.max (the "dynamic session sharing" limit; see the "Limits" note in the Microsoft Learn HC Livy docs), whose default is 5. Two things count against that limit:

• one REPL per dbt worker thread (threads), plus
• dbt's persistent main-thread ("master") connection, which is also a REPL — it is held for relation-cache listing at startup and again at on-run-end.

So a single build holds up to threads + 1 REPLs concurrently. When that exceeds the cap, dbt still works correctly — Fabric simply spins up a second underlying Livy session to host the overflow REPL(s), and the same sessionTag makes future acquires snap-attach to whichever underlying session has room.

What that means in practice:

Property	Shared across underlying sessions?
OneLake Delta tables (dbt model outputs)	Yes — same lakehouse storage
Catalog / metastore (SELECT FROM <other_model>)	Yes — same Fabric catalog
Temp views (CREATE TEMPORARY VIEW ...)	No — REPL/session-local
Session-level Spark configs (SET spark.sql.X = ...)	No
Cached datasets / UDFs / broadcast vars	No

Because dbt-fabricspark materializations always write permanent Delta / MLV objects, model-to-model refs resolve correctly regardless of which underlying session produced or consumes the table. Macros that depend on session-local state (temp views, in-session configs) are the only ones that could surprise — none ship with this adapter today.

Cost tradeoff: each additional underlying Livy session is a separate Spark cluster billed for the duration of the run plus the spark.livy.session.idle.timeout afterwards. Because the master connection consumes one REPL slot on top of the workers, the single-session ceiling is threads ≤ spark.highConcurrency.max − 1 — i.e. threads ≤ 4 at the default cap of 5. (threads: 5 needs 6 REPLs, so it spills to a second session.)

To run threads ≥ 5 inside one billed session, raise the cap in your profile — Fabric honors it at session-create time, no Environment required:

spark_config:
  name: <your-app-name>
  conf:
    spark.highConcurrency.max: "50"   # keep ≥ threads + 1

Alternatively, attach a Fabric Environment whose Spark properties set spark.highConcurrency.max. Keep the cap ≥ threads + 1 so a single build stays on one billed session; raise threads only when the extra parallelism beats the extra compute spend.

High-concurrency has no effect in local mode as this is a Fabric specific construct.

Materialized Lake Views

Materialized lake views are a Fabric-native construct that materializes a SQL query as a Delta table in your lakehouse, with automatic lineage-based refresh managed by Fabric.

Prerequisites

• Schema-enabled lakehouse
• Fabric Runtime 1.3+
• Source tables must be Delta tables

Basic Usage

-- models/silver/silver_cleaned_orders.sql
{{ config(
    materialized='materialized_lake_view',
    database='silver',
    schema='dbo'
) }}

SELECT
    o.order_id,
    o.product_id,
    p.product_name,
    o.quantity,
    p.price,
    o.quantity * p.price AS revenue
FROM {{ ref('bronze_orders') }} o
JOIN {{ ref('bronze_products') }} p
    ON o.product_id = p.product_id

Configuration Options

Option	Type	Default	Description
materialized	string	—	Must be 'materialized_lake_view'
database	string	target lakehouse	Target lakehouse for cross-lakehouse writes
schema	string	target schema	Target schema within the lakehouse
partitioned_by	list	—	Columns to partition the MLV by
mlv_comment	string	—	Description stored with the MLV definition
mlv_constraints	list	[]	Data quality constraints (see below)
tblproperties	dict	—	Key-value metadata properties
enable_cdf	bool	true	Auto-enable Change Data Feed on source tables
mlv_on_demand	bool	false	Trigger immediate refresh after creation
mlv_schedule	dict	—	Schedule config for periodic refresh (see below)

Data Quality Constraints

{{ config(
    materialized='materialized_lake_view',
    mlv_constraints=[
        {"name": "valid_quantity", "expression": "quantity > 0", "on_mismatch": "DROP"},
        {"name": "valid_price", "expression": "price >= 0", "on_mismatch": "FAIL"}
    ]
) }}

Each constraint has:

• name — Constraint identifier
• expression — Boolean expression each row must satisfy
• on_mismatch — DROP (silently remove violating rows) or FAIL (stop refresh with error, default)

Change Data Feed

The adapter automatically enables Change Data Feed (CDF) on all upstream source tables referenced via ref() before creating the MLV. This enables optimal incremental refresh. To disable:

{{ config(
    materialized='materialized_lake_view',
    enable_cdf=false
) }}

On-Demand Refresh

Trigger an immediate MLV lineage refresh after creation:

{{ config(
    materialized='materialized_lake_view',
    mlv_on_demand=true
) }}

This calls the Fabric Job Scheduler API:

POST /v1/workspaces/{workspaceId}/lakehouses/{lakehouseId}/jobs/RefreshMaterializedLakeViews/instances

Scheduled Refresh

Create or update a periodic refresh schedule. The adapter uses the Fabric Job Scheduler API to manage schedules. Only one active schedule per lakehouse lineage is supported — the adapter automatically updates an existing schedule if one is found.

Cron schedule (interval in minutes):

{{ config(
    materialized='materialized_lake_view',
    mlv_schedule={
        "enabled": true,
        "configuration": {
            "startDateTime": "2026-04-10T00:00:00",
            "endDateTime": "2026-12-31T23:59:59",
            "localTimeZoneId": "Central Standard Time",
            "type": "Cron",
            "interval": 10
        }
    }
) }}

Daily schedule (specific times):

{{ config(
    materialized='materialized_lake_view',
    mlv_schedule={
        "enabled": true,
        "configuration": {
            "startDateTime": "2026-04-10T00:00:00",
            "endDateTime": "2026-12-31T23:59:59",
            "localTimeZoneId": "Central Standard Time",
            "type": "Daily",
            "times": ["06:00", "18:00"]
        }
    }
) }}

Weekly schedule (specific days and times):

{{ config(
    materialized='materialized_lake_view',
    mlv_schedule={
        "enabled": true,
        "configuration": {
            "startDateTime": "2026-04-10T00:00:00",
            "endDateTime": "2026-12-31T23:59:59",
            "localTimeZoneId": "Central Standard Time",
            "type": "Weekly",
            "weekdays": ["Monday", "Wednesday", "Friday"],
            "times": ["08:00"]
        }
    }
) }}

Full Example with All Options

{{ config(
    materialized='materialized_lake_view',
    database='gold',
    schema='dbo',
    partitioned_by=['product_type'],
    mlv_comment='Product sales summary with quality checks',
    mlv_constraints=[
        {"name": "positive_revenue", "expression": "total_revenue >= 0", "on_mismatch": "DROP"}
    ],
    tblproperties={"quality_tier": "gold"},
    enable_cdf=true,
    mlv_on_demand=true
) }}

SELECT
    product_id,
    product_name,
    product_type,
    SUM(quantity) AS total_quantity_sold,
    SUM(revenue) AS total_revenue
FROM {{ ref('silver_order_items') }}
GROUP BY product_id, product_name, product_type

Generated SQL:

CREATE OR REPLACE MATERIALIZED LAKE VIEW gold.dbo.product_sales_summary
(
    CONSTRAINT positive_revenue CHECK (total_revenue >= 0) ON MISMATCH DROP
)
PARTITIONED BY (product_type)
COMMENT 'Product sales summary with quality checks'
TBLPROPERTIES ("quality_tier"="gold")
AS
SELECT ...

Limitations

• No ALTER on definition — Changing the SELECT query, constraints, or partitioning requires drop + recreate. The adapter uses CREATE OR REPLACE which handles this automatically.
• Only RENAME via ALTER — ALTER MATERIALIZED LAKE VIEW ... RENAME TO ... is the only supported ALTER operation.
• No DML — INSERT, UPDATE, DELETE are not supported on MLVs.
• No UDFs — User-defined functions are not supported in the SELECT query.
• No time-travel — VERSION AS OF / TIMESTAMP AS OF syntax is not supported.
• No temp views as sources — The SELECT query can reference tables and other MLVs, but not temporary views.
• Schedule is per-lakehouse — One active schedule per lakehouse lineage, not per MLV.

FAQs

No support for Python Models

For adapter stability, test coverage and high quality - this adapter is only focusing on SQL models with no support planned for Python models.

For Python code execution, consider Microsoft Fabric notebooks.

Reporting bugs and contributing code

• Want to report a bug or request a feature? Let us know on Slack, or open an issue
• Want to help us build dbt? Check out the Contributing Guide

Join the dbt Community

• Be part of the conversation in the dbt Community Slack
• Read more on the dbt Community Discourse

Code of Conduct

Everyone interacting in the dbt project's codebases, issue trackers, chat rooms, and mailing lists is expected to follow the dbt Code of Conduct.