How I Transformed My Pipeline with Medallion

For two months, my projector fleet pipeline did one thing.

The poller ran. Every fifteen minutes it queried our networked projectors over PJLink and dumped the results into TimescaleDB hypertables. Power state, lamp hours, fan speed, the works. It worked. The data landed. End of story.

Except the data was the only thing landing.

It started with two projectors. Then fifteen, at a couple of sites. Then more sites came online, more racks, more devices, until “which projectors should we be polling?” (that’s the *list*, the inventory) stopped being something I held in my head. I’d open DataGrip, UNION the per-site seed tables (a few at first, eventually all sixteen as more sites came online), JOIN against projector, eyeball the diff, scribble a WHERE clause, and save the query for next time. Two days later someone would ask the same question, and I’d reopen the saved UNION, tweak it for the new context, and run it again — not noticing I was rebuilding the same answer.

Then someone would ask “is that one drifting?”. Different query. Different ad-hoc SQL.

Then “why is the Manifest saying site1-room01-proj exists at 192.0.2.4 when PJLink is responding from a totally different hostname at that IP?”. Now I’m grepping.

The pipeline wrote data. The decisions all happened in my head, with a terminal open.

This was fine, at first. Then we added more sites. Then more devices. Then the IP Manifest got out of sync with reality at one site. Then at three. The CSVs were still in git, technically authoritative, but the truth was in the hypertables, mostly. Sometimes. When PJLink was reachable.

Here’s what I didn’t realize: you don’t notice the moment your single-table approach stops scaling. You notice the moment you’ve reopened a saved JOIN, tweaked it, and re-run it three times in a week, only to look up and realize you hadn’t actually answered the question. You’d just added another version to the pile.

What finally got under my skin wasn’t an outage. It wasn’t a customer complaint. It was reaching for DataGrip to ask “which devices are in the Manifest but not responding to PJLink?” for what felt like the hundredth time, and seeing that the pipeline only did half the job. I did the other half, every time.

So I added a layer.

Then another. Then a third.

The day stg_device_reconciliation ran for the first time (that’s the model that does a FULL OUTER JOIN between IP Manifest intent and PJLink reality, with conflict severity attached), I sat there with a query result in front of me. Devices the Manifest insisted existed, that PJLink couldn’t reach. Devices PJLink confirmed, that the Manifest didn’t know about. Hostname mismatches I’d been nodding past for months.

I didn’t have to write the JOIN anymore. I had to read it.

That’s the Medallion Architecture. Bronze, Silver, Gold: what each layer is, why I added them in that order, and what changed when the pipeline stopped being a write log and started being a source of decisions.

Bronze in my pipeline is two things.

The first is the TimescaleDB hypertables that the poller writes to: power_reading, health_snapshot, pjlink_connection_attempts, plus a couple more. The poller queries a projector over PJLink, gets a result, and inserts. No JOINs. No reshaping. The shape of the row matches the shape of the response.

The second is the IP Manifest, sixteen CSVs in seeds/, one per site, hand-curated, version-controlled in git. These are the *intent* layer: “these are the devices we believe exist at each site, with these names and IPs.” The poller doesn’t write to these; humans do, when a device gets installed or moved.

Now, here’s where the textbook gets fuzzy.

You’ll read in a lot of medallion explanations that Bronze is immutable. Append-only. Never mutates. And in spirit, sure. That’s how Databricks’ own deployment guide phrases it: ”Data is append-only, never updated or deleted.”

But click a few pages over in the same docs and that picture softens. Their VACUUM command (the one that physically removes Delta files) defaults to a 7-day retention threshold and explicitly destroys time-travel history past it. Snowflake’s Time Travel maxes out at 90 days; after that, rows drop into Fail-safe for another non-configurable 7, and then they’re gone. Microsoft Fabric’s medallion guide actively recommends bounding raw history to “the last month, or other appropriate period of time.” In my pipeline, pjlink_connection_attempts has a 90-day retention policy and rows age out. Pure-append Bronze, kept hot and queryable forever, is the exception, not the rule.

So I had to get clearer with myself about what Bronze actually is.

Bronze isn’t “data lives forever.” Bronze is no transformation on the way in. The poller doesn’t decide if a power-state response is “healthy.” It records that the projector said POWR=1 at this timestamp, and that’s all. The Manifest CSVs aren’t “cleaned” before they enter Bronze; they’re stored as the human typed them, casing inconsistencies and all.

The contract is: what landed in Bronze is what the source said. Truth-as-recorded. Aging out, retention, partition rotation: those are operational concerns, not contract violations.

Why does the distinction matter?

Because if I’d been holding the textbook “never mutates” rule, I’d have either fought retention policies that are operationally sane, or felt vaguely guilty about my pipeline “not doing medallion right.” Both are useless states.

The honest version (no transformation on the way in, retention by policy, mutation by aging) is one I can defend, automate against, and teach.

Bronze is the first place layering pays off, and it pays off in the form of not arguing. The Manifest says X. PJLink says Y. Both land in Bronze, untouched. The argument happens in Silver.

Silver is where Bronze gets argued with.

Some of what Silver does is uncontroversial: trim whitespace, cast types, dedupe, drop nulls, conform formats. The part I had to convince myself of was the rest of it. That multi-source reconciliation (joining two systems that disagree about reality) belongs in Silver too, and not in Gold.

When I went looking, I expected a fight. There wasn’t one. Microsoft Fabric’s medallion docs literally list ”Match customer records across systems” as a Silver operation. Databricks walks through joining a customers dataset with a transactions dataset to produce customer_transactions, at the Silver layer. And the shape of my Silver output (conflict rows tagged with severity, kept around for review rather than dropped) maps to a pattern Databricks ships in DLT called “Quarantine invalid records.” I wasn’t off-script. I was on it.

Let me show you both.

First, the conformance pass. stg_ip_manifest unions sixteen per-site CSVs, trims, and classifies device type:

That’s textbook Silver. Trim, normalize, classify, drop empties.

But there’s a wrinkle. Some of our sites are physically clustered. Same street block, sometimes the same IP octets. The IP Manifest reflects that: one CSV file site1_client.csv) actually contains devices for three site codes site1, site2, site3) because those sites are co-located. So the same model also resolves co-located site codes:

Is that conformance, or is that business logic? Honestly, it’s both. And that’s fine, as long as I name it.

Now, the reconciliation. stg_device_reconciliation does a FULL OUTER JOIN on IP between cleaned Manifest (intent) and PJLink (reality), and emits conflict severity:

Some teams argue Silver is overloaded and shift quality work back to source data products. I keep reconciliation in Silver because the output is still a conformed view of reality, not yet a decision. The decision (what to do about the conflict) happens in Gold. Silver makes the disagreement visible and structured. Gold acts on it.

The bigger lesson came from a sentence I almost glossed past in the Databricks docs themselves: ”Following the medallion architecture is a recommended best practice but not a requirement.” The vendor who coined the term frames it as a pattern, not a spec. The Delta Lake docs go further. They call it ”an optional, flexible framework” and explicitly say you can build a Gold table by joining Bronze and Silver. dbt’s equivalent staging-intermediate-marts taxonomy opens with ”This guide is just a starting point.” Same stance, different wording.

Once that pattern clicked, the layers stopped feeling like rules. They felt like jobs: Bronze records, Silver conforms, Gold decides. And once it’s the job you’re tracking, you place your own work where it fits, not where the diagram says.

Tests live here too. Both not_null on ip_address and accepted_values on device_type projector, reserved, other, unknown) run as part of the dbt build. If a CSV ever introduces a fifth device type, the build fails before Silver lands. The Bronze layer would have happily ingested it; Silver is where the contract gets enforced.

That’s the layered-test payoff in miniature: each layer guards its own promises.

Here’s where it gets interesting.

If Silver gets the disagreement visible, Gold gets it acted on. Two of our Gold marts in this project don't feed dashboards at all; they drive the system itself.

mart_polling_candidates is the canonical example. It’s a table, not a view. Every refresh, it UNIONs the IP Manifest projectors with PJLink-confirmed devices that aren’t in the Manifest. That catches undocumented devices the Manifest missed:

The poller reads from mart_polling_candidates to know what to poll. Which means the next poll cycle’s Bronze ingest is shaped by a Gold mart.

mart_pjlink_work_orders is another operational Gold table. It joins non-OK PJLink statuses with device context from the Manifest, sorts by remediation priority, and flags Epson devices as candidates for remote configuration. It’s not a dashboard; it’s a queue.

mart_conflicts is the dashboard one. Severity-sorted, human-readable. Surfaces what stg_device_reconciliation made visible.

That's three Gold tables. Two drive the system itself: mart_polling_candidates for the next poll, mart_pjlink_work_orders for the remediation queue. The third, mart_conflicts, feeds the review dashboard.

There’s also a presentational Gold layer in this project mart_projector_status, mart_site_summary) that feeds Grafana dashboards. It’s a meaningfully different kind of Gold from the operational marts. But that’s a different essay; if I write it, you’ll see what’s interesting about how it relates to the operational ones.

Four things changed once Bronze, Silver, and Gold were actual layers and not vibes.

Reproducibility. make refresh_all rebuilds every staging model and mart from Bronze, in order. Bronze never gets touched. If a Silver model has a bug, I fix the SQL, re-run, and Gold rebuilds. No one says “wait, did you also reload the seeds?” because seed reloading is the first step of the chain. The pipeline is one command, end to end, and nothing important is hand-managed.

Layered tests. Every layer guards its own promises. Tests on stg_ip_manifest enforce that device_type is one of projector / reserved / other / unknown. That catches CSV typos before they reach reconciliation. Tests on mart_polling_candidates enforce unique on ip_address. That catches a Manifest/PJLink overlap bug before the poller reads a duplicate row. There’s even a contract test on hours_accumulated that fails the build if a downstream consumer’s expected shape silently drifts. The build fails noisily, in the right layer, before bad data lands.

Auditability. When someone asks “why is site1-room01-proj marked manifest_only_not_responding?”, I can walk them up the stack. The reconciliation status came from a CASE in `stg_device_reconciliation`, against ths row in `stg_ip_manifest, sourced from thisline of `site1_client.csv, against zero rws in `stg_pjlink_devices, because PJLink hasn’t seen tis IP since `last_seen_at. That walk up the stack is data lineage in practice. It’s how we troubleshoot now. I don’t have to remember the logic; it’s in the layer.

Trust gradient. This is the one that surprised me. Once Bronze, Silver, and Gold were named, the team (even just me, talking to myself in commit messages) got specific about which layer to believe. “Bronze is what we received. Silver is what we cleaned and reconciled. Gold is the decision.” That language doesn’t exist when every table sits at the same conceptual altitude. Even with a dbt project and a passing test suite, tests prove correctness within a table. They don’t tell you which layer of trust the table belongs to. Medallion does.

Layering didn’t remove the work. It made the work legible.

(Not shown: the presentational marts (mart_projector_status, mart_site_summary) that feed Grafana dashboards. Different consumers, different essay.)

If I could go back to the version of me writing the same JOIN three times a week, I’d say four things.

1. Start the layer before you need it. I waited until the JOINs piled up. By then, the failure mode had already shaped my mental habits. If I’d added staging models the day the second site went live, even a thin one, the muscle would have been there when reconciliation became urgent.

2. Layered tests catch what MVP didn’t anticipate. I didn’t pre-write every test; that’s not realistic. We built for MVP and layered tests on as the failure modes surfaced. What changed with medallion is where the tests live. Once the layers existed, every new test had a natural home: hygiene tests on Silver, contract tests on Gold, source tests on Bronze. Without layers, a new test is just another script in the pile. With layers, it’s part of a stack that fails in the right place at the right time.

3. Don’t let “just one more CASE statement” off the hook. That’s how single-table pipelines grow into liabilities. The CASE statements aren’t wrong individually; they’re wrong cumulatively. Layer them out, even if it feels like over-engineering. It isn’t.

4. Gold doesn’t have to mean “BI table.” My pipeline’s most valuable Gold mart isn’t on a dashboard; it’s the one the poller reads to decide what to poll. The textbook flow still holds: Gold still feeds Grafana through mart_projector_status and mart_site_summary. But it also feeds itself. Same medallion, with a closed loop added.

I'm curious whether your Gold layer drives anything operationally, or if it's still all dashboards. If you've got a mart that closes its own loop the way mart_polling_candidates does, I'd love to see what it looks like. Comments are open.

Leave a comment