We Had Three Layers but Only Needed One Write
Last updated on
3 min read

We Had Three Layers but Only Needed One Write


One of the habits I picked up from Microsoft Fabric lakehouse diagrams was to draw a table after every transformation layer. Raw goes to Clean, Clean goes to Enriched, Enriched goes to Fact. Each arrow becomes a notebook, and every notebook writes something durable.

It looks wonderfully organized. It can also mean reading and writing the same slice of data three times when the next step needs it five seconds later.

The question that finally bothered me was not whether Bronze, Silver, and Gold were useful names. They were. It was whether a logical boundary had to become a storage boundary.

A layer is a responsibility

Consider a batch transformation with four responsibilities:

  1. Raw preserves the source and its lineage.
  2. Clean standardizes types, quantities, and obvious source defects.
  3. Enrich resolves reference data and business relationships.
  4. Fact produces the shaped dataset a downstream warehouse consumes.

Those responsibilities should be recognizable in code and tests. But if Clean and Enrich are always rebuilt together from the same bounded source window, persisting both may buy little recovery value. You pay for extra writes, metadata operations, and subsequent reads. You also acquire two more datasets whose cleanup boundary has to agree with the rest of the run.

Raw remains durable, while Clean and Enrich are temporary SQL views in one Spark session before the staged fact is persisted.

The stages remain visible; the durability boundaries move to where recovery needs them.

One way to implement it in Spark SQL is straightforward:

CREATE OR REPLACE TEMP VIEW clean AS
SELECT order_id, product_id, TRY_CAST(quantity AS DECIMAL(18, 3)) AS quantity
FROM raw_window;
CREATE OR REPLACE TEMP VIEW enriched AS
SELECT c.*, p.product_key
FROM clean c LEFT JOIN product_lookup p USING (product_id);
-- The durable write happens after the final shaping and checks.

The SQL is deliberately small. The point is the execution boundary: temporary views can form a readable sequence without requiring a Delta table for every intermediate result.

Free views are not free computation

There’s a catch, and it matters. A temporary view is a definition. It doesn’t guarantee the underlying work is computed only once. Reference the same expensive view in several checks and a final write, and Spark may revisit parts of the plan. Depending on the optimizer and execution, a cache or materialized intermediate can be the better choice.

I prefer to look at reuse, not naming. If a view is read once, caching it is often wasted effort. If several data-quality checks and downstream joins repeatedly evaluate it, selective caching may help. If the intermediate is too large to cache, or different jobs need it at different times, durable storage becomes reasonable again.

There are other good reasons to persist a layer:

  • It is consumed independently by several pipelines.
  • It is expensive to reproduce after a downstream failure.
  • It carries a data contract other teams depend on.
  • Its historical version needs to survive changes in reference data or transformation code.
  • It marks a useful checkpoint across separate execution sessions.

If none of these apply, a table can become a tax paid mainly to keep the diagram symmetrical.

Notebook boundaries make this a real decision

Split each stage into a separate notebook activity and you get modularity, logs, and targeted reruns. You may also get repeated Spark-session setup, repeated reads, and awkward handoffs. Put every stage in one enormous notebook and debugging becomes unpleasant for a different reason.

The compromise I like is to keep transformations named and testable, use one session where state reuse is worthwhile, and persist where the next independent unit of work needs a stable contract. The pipeline can still have clear orchestration boundaries around ingestion, transformation, and warehouse publication.

This isn’t an argument for eliminating Silver tables. It’s an argument for earning each physical one. On a large shared data platform, durable Silver is often the product. On a tightly coupled batch computation, it may be an unnecessary stopover.

The medallion pattern helped me separate responsibilities. The mistake was letting the picture decide how many times the data had to touch disk.

Next in “Microsoft Fabric migration and data correctness” Incremental Load Is Not One Thing →