The monthly gross-margin bridge report: an idempotent Databricks pipeline

This is the monthly gross-margin bridge report I build in Databricks: a field-service company's raw work orders, staged through bronze, silver, and gold Delta tables, until January and February land on $3,950 in revenue, $2,950 in direct cost, and $1,000 in gross profit. Run the load twice and the silver table still reports six rows and six distinct work orders, the same count both times. That equality is the proof an incremental load can replay safely, and everything below builds to it.

Synthetic data. Seeded generator, no real company.

Why this report matters

• A duplicate resolved the wrong way reports the wrong month. The two versions of WO-1001 below differ by $250 in revenue; get the resolution rule wrong and the aggregate is off by that much, silently.
• A zero-revenue guard keeps a slow period from crashing the whole report instead of just reporting no margin to show, so a report scheduled to run every month does not break on the one month with nothing to report.
• An idempotent MERGE means a retried or rerun load never double counts. A scheduler retry at 2am or a manual rerun after a failure moves nothing that already landed correctly.
• Schema-level governance turns "who can see the raw extract versus who can see the finished margin" into a grant, not a redesign, because bronze, silver, and gold are already separate schemas.

What a lakehouse is

• A lakehouse keeps data as tables on cheap cloud object storage and treats the compute that reads them as a separate, on-demand thing. A traditional database welds storage and compute together; a lakehouse pulls them apart.
• The tables live as Delta files: Parquet data plus a transaction log that gives ACID writes, time travel, and schema enforcement. A SQL warehouse or a notebook cluster attaches to them only when a query actually runs.
• That separation changes how the work gets organized. Instead of one monolithic ETL job, data stages through layers of increasing trust: raw, cleaned, business ready. That staged pattern is the medallion architecture.
• This report builds all three layers, bronze, silver, and gold, then proves the one operation that makes an incremental pipeline safe to rerun: an idempotent MERGE.

The three layers, and what each one owes you

Each medallion layer has one job:

• Bronze preserves the raw extract almost untouched. Values arrive as text because that is how a source dump looks: everything is a string, casing is inconsistent, and duplicates are still present. Bronze is a faithful record of what landed, nothing more.
• Silver types and standardizes. It casts strings to real dates, decimals, and timestamps, normalizes messy categories, and resolves duplicates down to one trustworthy row per business key.
• Gold is the business-ready aggregate. It answers a specific question, here monthly revenue and gross margin for completed work, and is the thing a dashboard or a report reads.
• I set these up as three schemas inside one catalog: a dedicated fieldservice catalog on Databricks Free Edition if the workspace allows creating one, or the default workspace catalog otherwise. That catalog boundary is the same Unity Catalog governance I come back to at the end.

Bronze: preserve the raw extract

• Bronze's only job is fidelity. Every column lands as text, casing stays whatever the source sent, and duplicates stay in place, untouched.

Work order	Service date	Technician	Status	Revenue
WO-1001	2026-01-05	T-3	Completed	1200.00
WO-1002	2026-01-12	T-1	completed	900.00
WO-1003	2026-01-20	T-2	Scheduled	0.00
WO-1001	2026-01-05	T-3	COMPLETED	1450.00
WO-1004	2026-02-03	T-1	Completed	1600.00
WO-1005	2026-02-10	T-2	Cancelled	0.00

• Notice WO-1001 twice, and the status column in three different cases (Completed, completed, COMPLETED). Real extracts do this constantly, and bronze does not hide it.
• A one-line validation check turns the duplicate into a number instead of a surprise:

Metric	Value
Source rows	6
Distinct work orders	5
Duplicate versions	1

• Six rows landed, five real work orders exist, so exactly one duplicate version needs resolving. Now silver has a defined job.

Silver: type, standardize, and keep the newest version

• Silver does two things at once: it casts every string to its real type, and it normalizes the status casing so Completed, completed, and COMPLETED all become completed.
• It collapses each business key to one row with ROW_NUMBER: partition by work order id, order by source timestamp descending, keep rank 1. "Newest source timestamp wins" is an explicit, defensible rule, not an accident of load order.

• For WO-1001, the row updated on 2026-01-08 wins over the stale 2026-01-06 version.
• Six bronze rows in, one duplicate resolved, five silver rows out.

Gold: aggregate, and guard the division

• Gold answers the business question: one row per month, completed work orders only, with revenue, gross profit, and gross margin percent.
• The status = 'completed' filter is why silver had to normalize casing first: three different spellings of "completed" would have silently dropped rows from the aggregate.
• The margin division is guarded: a month with zero completed revenue returns no margin to report instead of erroring the whole job.
• Margin is computed from summed components, sum of profit over sum of revenue, not by averaging each order's percent. Averaging weights a 5-dollar order the same as a 5,000-dollar one, which is the wrong answer.

month	revenue	gross_profit	gross_margin_pct
2026-01	2350.00	1000.00	42.6
2026-02	1600.00	0.00	0.0

• WO-1003 (scheduled) and WO-1005 (cancelled) never reach gold because they are not completed.

• Both months together: $3,950 in revenue and $2,950 in direct cost bridge to $1,000 in gross profit, the same total January alone already carries, because February's completed work broke even.

The idempotent MERGE: safe to run twice

• Recomputing every table from scratch works at six rows. It breaks at billions of rows arriving hourly, which is why an incremental load has to apply only the new and changed records, and do it idempotently: running it twice produces the same result as running it once, no duplicates, no double counting.
• Here is an incoming batch: a newer correction to an existing order, a stale version of another order, and one genuinely new order.

Work order	Service date	Technician	Status	Revenue	Source updated
WO-1002	2026-01-12	T-1	completed	1050.00	2026-01-15 10:00:00
WO-1003	2026-01-20	T-2	scheduled	0.00	2026-01-18 08:00:00
WO-1006	2026-02-14	T-3	completed	1300.00	2026-02-15 09:30:00

• The match key is the work order id. The guard is the incoming record's source timestamp actually being newer than what silver already holds, so a late-arriving old record can never overwrite good data.

• WO-1002 matches, and its incoming timestamp (2026-01-15) is newer than what silver holds (2026-01-12), so its revenue updates to 1050.
• WO-1003 matches, but its incoming timestamp (2026-01-18) is older than silver's (2026-01-19), so the stale version is ignored. The guard is doing its job: a late-arriving old record cannot overwrite good data.
• WO-1006 matches nothing, so it inserts.
• One update, one insert, one stale record correctly ignored. Silver now holds six rows.
• Run the identical MERGE again: WO-1002's incoming timestamp now equals what is stored, so the "newer than" test is false and nothing updates; WO-1006 already exists, so it does not insert. Zero updates, zero inserts.

• Total rows still equal distinct keys, six and six, before and after the second run. That equality is the idempotency proof: the pipeline can be replayed after a failure, or a batch delivered twice, and the table does not grow a duplicate. The timestamp guard is what makes it safe, not the MERGE keyword alone.

Reading the query profile

• Databricks attaches a query profile to every run: in the SQL editor, open the query history and click the execution.
• The reading discipline is the same at six rows and at a billion, so it is worth building the habit here, before there is real money riding on the answer.
• Dominant node: which operator burned the most wall-clock time. Usually a scan or a shuffle, rarely the thing I assumed going in.
• Files pruned versus files read on the scan: if a query filters to one month but the scan reads every file, the table is not partitioned or clustered in a way the filter can use.
• The shuffle a GROUP BY introduces: aggregation reshuffles data across the cluster, and the profile shows how many rows crossed the wire.
• Spill to disk: a stage ran out of memory and paged out. The first thing to chase on a slow real query.

Unity Catalog: why the three-level names matter

• The three-level names used here, fieldservice.bronze.work_orders, are not cosmetic. That is Unity Catalog, Databricks' governance layer, and each level governs something real:
  • A catalog is the top-level container: the natural boundary for an environment or a domain (a fieldservice catalog, a separate finance catalog), and permissions can be granted at this level to cover everything inside.
  • A schema groups related tables. The bronze, silver, and gold schemas here are exactly this: the medallion layers made into governance boundaries, so an analyst can read gold without touching raw bronze.
  • A grant is the actual access rule. GRANT SELECT ON SCHEMA fieldservice.gold TO <group> lets a reporting group query the business-ready layer and nothing else; MODIFY on silver stays with the engineering role.
• Governance belongs in the architecture from the start, not as a cleanup pass at the end. Because the layers are already separate schemas, who can read the clean numbers versus who can touch the raw extract is a grant, not a redesign.

My process on the job

• Preserve the raw extract untouched at the first landing zone, corrections included, so bronze always has what actually arrived.
• Turn a known issue into a one-line count before writing a fix: source rows versus distinct keys, right after landing.
• Resolve duplicates with one explicit, logged rule, newest source timestamp wins here, never a silent pick or "keep whichever loaded last."
• Normalize categorical fields, status casing here, in the same pass that types the data, before any filter reads them.
• Guard every division that could hit zero, so a slow period reports no margin to show instead of crashing the job.
• Key every incremental load on a stable business key plus a newer-than-what-is-stored guard, so a stale batch can never overwrite good data.
• Prove idempotency by running the identical load twice and checking the count does not move: total rows equal to distinct keys, before and after.
• Read the query profile on every run, not just the slow ones, so the habit is already there when a real bottleneck shows up.
• Put governance in the schema boundaries from day one: bronze and silver locked to engineering, gold open to reporting, so access control is a grant, not a redesign.

Tools I would use

• Databricks SQL editor and Delta Lake tables for the bronze, silver, and gold layers (Free Edition is enough to build and rerun all of this).
• MERGE INTO for the incremental, idempotent load.
• Unity Catalog for the catalog, schema, and grant governance boundaries.
• The built-in query profile view for reading a plan's dominant node, file pruning, shuffle, and spill.

Key takeaways

• Bronze keeps the raw extract almost untouched: everything as text, casing inconsistent, duplicate versions still present.
• Silver casts each string to its real type, normalizes the status casing, and uses ROW_NUMBER to keep one newest row per business key.
• Gold turns completed work into a monthly gross-margin bridge, guarded against a zero-revenue month: $3,950 revenue less $2,950 direct cost lands at $1,000 gross profit across January and February.
• The MERGE is idempotent because of one predicate, the incoming timestamp has to be newer than what is stored: total rows staying equal to distinct keys, six and six, is the proof it replays safely.
• Reading the query profile (dominant node, files pruned, shuffle, spill) and Unity Catalog's catalog, schema, and grant levels are the same discipline at six rows and at a billion; scale changes the stakes, not the habit.

Related posts

Other walkthroughs built on the same invented field-service company:

• Machine learning on this lakehouse: a legible completion model and a customer segmentation, both fed by the silver and gold tables above.
• Tracing gross margin from raw record to dashboard, following one order through these same layers.
• Six enterprise SQL patterns for dedup, idempotent loads, and a Type 2 dimension.
• The Power BI star schema and DAX measure dictionary that sits on top of the fact table.
• Putting the Power BI project under version control, so a measure edit gets a diff, a review, and an audit trail.