A Boring (But Battle-Tested) Fabric Stack: Copy Job → dbt + Spark → MLV → Direct Lake

Data engineering has become a little ridiculous when it comes to tooling.

Take a straightforward transformation. In Microsoft Fabric, I could probably build it with Pandas, the PySpark DataFrame API, Spark SQL, dbt, plain SQL, Dataflow Gen2, or a notebook. Most of those options would work. That is exactly the problem.

I stopped asking, “Can this tool do the job?” and started asking, “Which tool should own this job?”

The stack I keep coming back to is deliberately boring:

It is not the flashiest architecture. It is the one I can explain quickly, operate without a maze of notebooks, and still understand six months after I build it. That matters more to me than squeezing one more tool into the diagram.

When Every Tool Can Do ETL, Which One Should You Actually Use?

Fabric gives us several tools with overlapping capabilities. That flexibility is useful, but it creates a predictable failure mode: we choose tools one transformation at a time, then discover that the platform has no coherent operating model.

One table is loaded by a Copy activity. Another is loaded by a notebook. A third uses Dataflow Gen2. Business logic appears in SQL, PySpark, Power Query, and DAX. A pipeline becomes the only place where the dependency graph is visible—and eventually even the pipeline is too complicated to trust.

The way out is to make ownership explicit. Each tool gets one primary responsibility:

The Architecture at a Glance

The architecture is a medallion pattern, but the important part is not the colours. It is the separation of responsibilities.

Key principles:

• Each tool has one clearly defined responsibility.
• Definition deployment and routine data refresh are separate lifecycles.
• Materialized Lake Views handle persistence, dependency ordering, and refresh execution.
• Direct Lake removes another data-movement step before the semantic model.

Bronze keeps a faithful, replayable copy of the source. Silver standardises and reconciles it. Gold reshapes it for analytics. Direct Lake exposes Gold through a governed semantic model without adding another data-copy step.

None of that is novel. That is a feature. The architecture is intentionally easy to reason about.

Bronze: Keep Ingestion Boring (Copy Job)

Bronze is not where I want to demonstrate my transformation skills. I want data landed reliably, with enough source fidelity to replay downstream processing when requirements change.

For straightforward ingestion, Fabric Copy Job is increasingly my default. It supports full copy, watermark-based incremental copy, and CDC-based replication. It also manages the state of the last successful incremental run, which removes a surprising amount of plumbing from a common ingestion pattern.

That means my Bronze layer can focus on a short list of responsibilities:

• land source data as Delta;
• retain useful source metadata, such as ingestion time and source file;
• avoid business rules that make the copy difficult to replay;
• monitor freshness, volume, and failure state.

If the ingestion requires coordination across unrelated systems, conditional branching, or a custom API loop, I use a Pipeline or notebook. “Keep it boring” does not mean pretending every source is simple. It means keeping complexity out of Bronze until the source actually demands it.

Silver: Use dbt + Fabric Spark with MLV for Your ODS

Once the data reaches Bronze, the problem changes. Now I need consistent types, stable keys, deduplication rules, reference-data joins, and a model that represents the operational entities the business actually uses.

This is where I like dbt-fabricspark. I still write Spark SQL, but I get a project structure, version control, ref(), documentation, tests, and a dependency graph. Those capabilities matter more than the syntax used to express any one transformation.

In my current setup, that dbt project runs locally and is version-controlled in Git. It is the source of truth for the Silver and Gold definitions. I use dbt when I change those definitions; I do not put an unscoped dbt run in the path of every data load.

{{ config(
    materialized='materialized_lake_view',
    database='silver',
    schema='dbo'
) }}

select
    cast(order_id as bigint)        as order_id,
    cast(customer_id as bigint)     as customer_id,
    cast(order_date as date)        as order_date,
    cast(quantity as int)           as quantity,
    cast(unit_price as decimal(18,2)) as unit_price
from {{ ref('bronze_orders') }}
where order_id is not null

The model is declarative. It says what silver_orders should contain and which upstream model it depends on. The adapter emits the Fabric-native Materialized Lake View definition; Fabric persists the result as a Delta table.

I think of Silver as an operational data store, not a dumping ground for “clean-ish” tables. It should answer questions like:

• What is an order, customer, product, or account across the platform?
• Which source wins when the same entity appears in multiple systems?
• Which records are valid, late, duplicated, or rejected?
• What grain can downstream models safely depend on?

If those answers are unclear, Gold will inherit the ambiguity.

MLV: Let Fabric Handle Incremental Processing

Materialized Lake Views are the part that makes this architecture more than “dbt models running on Spark.” An MLV stores the result as a Delta table, tracks dependencies, and lets Fabric manage refresh order and execution.

For Spark SQL-defined MLVs, Fabric’s optimal-refresh engine can choose among three strategies:

1. No refresh when the sources have not changed.
2. Incremental refresh when only eligible changes need processing.
3. Full refresh when the expression or source changes require a rebuild—or when a rebuild is cheaper.

Incremental refresh is not automatic magic. Change Data Feed must be enabled on every referenced source table, the sources must be Delta, and the query must use supported constructs. The current incremental path is also designed for append-only changes during the refresh cycle; updates or deletes can force a full refresh.

alter table bronze.orders
set tblproperties (delta.enableChangeDataFeed = true);

This is still a major simplification. I no longer need a custom watermark table for every model, a notebook that decides whether to merge, or a pipeline branch for every dependency. Fabric already has the lineage and refresh state. I would rather use that than rebuild it.

Gold: Facts and Dimensions on Your ODS

Silver represents operational truth. Gold represents analytical intent.

I build Gold facts and dimensions on the Silver ODS rather than reaching back to Bronze. That keeps source reconciliation in one place and gives analytical models a stable contract.

{{ config(
    materialized='materialized_lake_view',
    database='gold',
    schema='dbo'
) }}

select
    order_id,
    customer_id,
    product_id,
    order_date,
    quantity,
    quantity * unit_price as sales_amount
from {{ ref('silver_orders') }}

The fact table declares its grain and reusable business calculations. Dimensions hold descriptive attributes, hierarchy keys, and history at the level the reporting model needs.

I keep measures such as year-to-date sales or margin percentage in the semantic model when they are truly analytical expressions. I keep row-level, reusable business meaning—such as sales_amount or a conformed customer key—in Gold. That separation avoids both extremes: a semantic model full of data-engineering repairs, or a Gold layer that tries to pre-answer every possible BI question.

Direct Lake: Model, Don’t Move

By the time Gold is ready, the curated data already exists in OneLake as Delta tables. I do not want another ETL process whose only purpose is to copy that data into a reporting store.

Direct Lake lets a Power BI semantic model load from Delta tables in OneLake without a traditional Import copy. The semantic model still owns relationships, hierarchies, measures, formatting, and reporting security. It just does not need a second physical copy of the Gold data to do that work.

Direct Lake does not excuse poor modeling. Wide tables, high-cardinality columns, weak relationships, and badly designed security can still produce a bad model. It simply removes a movement step that does not add business value.

The Boundary That Matters: Deployment Is Not Refresh

This is where my implementation is deliberately less automated than most architecture diagrams suggest.

At the moment, I develop and run the dbt project locally. The project is version-controlled in Git, and that repository is the source of truth for every Silver and Gold model definition.

When I change a model, that change begins in the dbt project. I can review the SQL, inspect the dependency graph, test it locally, and commit it like any other code change. Running dbt then applies the changed Materialized Lake View definition to Fabric and allows the affected model to rebuild.

That is a deployment event. It does not belong in the routine data pipeline.

Change dbt model
     ↓
Review and commit to Git
     ↓
Run dbt against Fabric
     ↓
Apply the changed MLV definition
     ↓
Rebuild and validate the affected model

If no model definition changed, I do not need dbt to reapply anything. The MLV definitions already exist in Fabric.

The Fabric Pipeline handles a different lifecycle: routine data operations.

Run Copy Job
     ↓ success
Land source changes in Bronze
     ↓ success
Refresh existing MLV lineage
     ↓
Monitor, validate, and notify

During that refresh, Fabric works with the definitions that are already deployed. Its optimal-refresh engine can skip an unchanged MLV, incrementally process eligible source changes, or perform a full refresh when required.

This separation prevents a data arrival from accidentally becoming a code deployment. A normal ingestion run should not issue CREATE OR REPLACE statements or rebuild model definitions that have not changed.

It also makes failures easier to classify:

• If the SQL definition is wrong, the problem belongs to the dbt project and its Git history.
• If source data did not arrive, the problem belongs to Copy Job or Bronze.
• If an existing MLV did not refresh correctly, the problem belongs to the Fabric refresh lifecycle.
• If a definition change caused a rebuild, that was an intentional deployment—not a side effect of the scheduled Pipeline.

I may eventually move dbt execution from my local environment into a managed CI/CD runner or Fabric dbt Job. That would change where the command runs, but it should not change this boundary.

Inside the MLV refresh boundary, Fabric already knows that Silver must run before Gold because Gold references Silver. It can order the lineage accordingly. The Pipeline does not need one activity per model.

The Refresh Materialized Lake View activity currently refreshes all MLVs in the selected Lakehouse. “Refresh all” is not the same as “recompute all”: for each view, optimal refresh can skip, process incrementally, or rebuild.

Bringing It All Together

Here is the operating model I want the team to remember:

The strongest part of this stack is not any individual product feature. It is that an incident has an obvious home. A missing source row is an ingestion problem. A duplicated customer is a Silver problem. An incorrect fact grain is a Gold problem. A broken year-to-date calculation is a semantic-model problem.

Clear responsibility makes troubleshooting faster and design reviews less subjective.

Final Thoughts

Copy Job gets the data in. dbt defines the transformations. Fabric Spark executes them. Materialized Lake Views persist and refresh them. Pipeline coordinates the large stages. Direct Lake serves the result.

I would not force this stack onto every workload. High-frequency streams belong in Real-Time Intelligence. Complex procedural transformations may still need PySpark. Some sources need custom notebooks or a proper Pipeline. MLV incremental refresh has eligibility rules. Direct Lake still needs a carefully designed semantic model.

But this is the default I now argue against, rather than the architecture I assemble from scratch every time.

The less orchestration and state-management machinery I write myself, the less machinery I have to understand six months from now. That is not boring in the dismissive sense. It is boring in the way good infrastructure is boring: predictable, observable, and quietly doing its job.

Further reading: Copy Job overview; Materialized Lake Views overview; optimal MLV refresh; Fabric dbt Job; dbt-fabricspark; and Direct Lake overview.