Fabric Medallion Analytics, Built with an AI Coding Agent
Subject
830 stores / 74 states
Scope
13 requirements
Built by
Claude Code, human-reviewed
Status
Phase 7 of 7
Blu isn't a real company. I made it up, along with its 830 stores, 74 states,
and three years of stubbornly messy transaction data. What's real is the platform I built to
make sense of all of it: a Microsoft Fabric Medallion pipeline, built together with an AI
coding agent, with me catching and correcting it every time it got something wrong.
00 · The brief
Blu Grocery Chain had a design for an on-prem warehouse that never actually got
built: OLTP into T-SQL staging, into a star schema, into SSAS, out to Power BI. It also had
thirteen numbered questions it still couldn't answer, like which vendors' delays actually hurt
delivery, how often POS hardware gets swapped out, and whether a change in someone's marital
status moves their overtime hours. The brief was to rebuild it as a Fabric Medallion platform
instead, and to fix the original design's problems rather than carry them forward.
What follows is that platform, told the way I'd walk someone through a refinery
floor: raw material in, material refined, material shipped.
01 · How it's built
Ore to ingot
How data actually moves. Bronze loads once and stays out of the schedule; Silver and Gold are the part that runs itself every night.
Bronze: raw intakebronze_lh · Lakehouse
I landed nine OLTP tables and six HR extracts untouched, plus five normalized lookup
tables the original design document never even mentioned. I only found those by querying
the live database schema directly instead of trusting the paperwork. The big tables get
written in 150,000-row chunks, because trying to write all 1.1 million rows of the sales
table in a single shot kept timing out.
Field note: the on-prem gateway that was supposed to carry
this data into Fabric has a relay error I never managed to fix. I ruled out a proxy
misconfiguration, antivirus interference, firewall rules, clock skew, and an Azure outage,
one by one. Rather than get stuck on it, Bronze writes straight to OneLake from a script
running on my own machine. It's the one place in this project that doesn't run natively
inside Fabric, and it's the only layer with an actual, documented reason for that.
Silver: cleaned and reconciledsilver_transform · Notebook
This layer runs as one PySpark notebook instead of a warehouse. A Lakehouse's SQL
endpoint is read-only, and the raw HR files aren't Delta tables yet, so no T-SQL engine
could even reach them. Every cleaning rule in here got checked against the live data
before I trusted it. None of it came straight from the design document.
Field note: 86 of the 830 stores had a raw
StateID that disagreed with the state you'd get by looking up their city. The
likely cause was synthetic city names colliding, like Birmingham, England versus
Birmingham, Alabama. I resolved it by trusting the city-derived join and logging the
conflict instead of quietly picking a side. I found the same kind of thing in the HR data:
143,000 duplicate absence records, 131,000 duplicate misconduct records, and 48,000
completely blank rows. Every one of those got counted before it got fixed.
Gold: dimensional, versionedgold_wh · Warehouse
Twelve dimensions, five facts that only ever get appended to. Type 1 dimensions update
in place. Type 2 dimensions (Product, POS Channel, Vendor, Employee) actually keep their
version history: when a row changes, the old version closes out with a real end date, and
the new version opens with no end date at all. That's what finally makes a question like
"how often does POS hardware get replaced" answerable. Getting data from Bronze through
Silver into Gold is incremental too. Only rows Gold doesn't already have get reprocessed,
so a missed night quietly catches itself up instead of needing a separate backfill.
A correction, caught before Consumption got built on top of it
My first build dropped and rebuilt every dimension from scratch on every single
run, keyed by a surrogate that just counted up.
That was wrong. A design that rebuilds everything every run can never
hold onto the version history several of these 13 requirements actually need. I fixed
it: tables get created once, changes get real upserts and real version history, and the
surrogate keys are derived from the business key itself, so a rebuild always lands on
the same key it used before. I ran one clean historical reset under the corrected
design, and Gold hasn't been dropped since.
Same three pipeline runs, two designs. The first build gave every row a new identity each time, whether or not anything changed. The corrected one keeps a single identity and only opens a new version the moment the real row actually changes.
Consumption: shippedDirect Lake · Reports
The Blu Gold Semantic Model sits directly over the warehouse's OneLake files in Direct
Lake mode, so there's no import step and no DirectQuery involved. It has thirty-seven
relationships and about twenty-five measures, and I traced every one of them back to one
of the thirteen original requirements. Blu Department Reports (Sales, Purchasing, HR) sits
on top of that. I hand-wrote both of them as raw TMDL and PBIR text and deployed them
straight through the REST API, because the officially recommended authoring tools needed
admin rights and a desktop app I didn't have in this environment.
02 · Running on its own
A pipeline that keeps itself going
A native Fabric Data Pipeline runs Silver into Gold on a schedule set through
Fabric's own Job Scheduler: 1:00 AM, every night, running on Fabric's own infrastructure
whether or not my laptop happens to be on. Success and failure go out as separate emails
through a native Office 365 Outlook activity, which (by Microsoft's own design) only
works with a real, signed-in Outlook account, never a service principal.
I left Bronze out of the nightly run on purpose. It's a one-time historical
load, not something trickling in daily from a live source, so scheduling it every night
wouldn't actually accomplish anything.
Direct Lake semantic model (TMDL), Power BI reports (PBIR)
Orchestration
Fabric Data Pipelines, Job Scheduler API
Tooling
Python, Fabric REST API, service-principal and notebook-native auth
Built by
Claude Code, with every architectural call reviewed by me
04 · Rules I kept
Learned the hard way, then applied for the rest of the build
01
Check the live system, not the paperwork. A design document tells you
what someone intended, not a guaranteed-complete inventory of what actually exists. I only
found the five missing lookup tables and the Store.StateID conflict by querying the live
data directly, not by trusting the STTM.
02
Nothing outside Fabric moves data without a real, documented reason.
Bronze's gateway failure is that reason. Silver and Gold don't have one, so they run
natively inside Fabric even where building locally first would have been quicker.
03
Nothing gets deleted outright. When something's superseded, I flag it
for a person to remove themselves rather than quietly deleting it myself.
04
A "Completed" message is not proof of anything. I re-checked row
counts, null keys, and idempotency live after almost every step. A doubled HR fact table,
a silently corrupted generated notebook, and an end-date ordering bug were only ever
caught because I went and looked.