A medallion data warehouse
for a veterinary care chain

By the time the veterinary care platform was live across a growing clinic network, the business was generating data everywhere — payments in one system, appointments and clinical records in another, practice-management details in a third. Each system answered its own question well, but no one could answer the questions that spanned all of them.

How a customer's payments related to their appointments, how wellness plans tracked against visits, how the whole business was actually performing week over week — those answers lived between the systems. I came on as data architect and engineering lead to fix that: a data warehouse on a medallion architecture that pulled three disparate source systems into a single, governed source of truth — and put trustworthy, refreshable dashboards in the hands of the people running the business.

---

The challenge

The hard part wasn't any one system — it was that the important questions lived between them. The data came from three very different sources: the payments system, the vet care chain's own care platform for the customer-facing booking and care experience, and the practice-management system for clinical and operational records. Each had its own schema, its own idea of what a "customer" or a "product" was, and its own quirks.

• No single source of truth. Analysts pulled numbers from three places and reconciled by hand — so answers disagreed and nobody fully trusted the reports.
• Delta and full loads, both messy. Some sources supported incremental extraction; others needed periodic full reloads. Overlapping windows meant we had to avoid double-counting or dropping records.
• Inconsistent definitions across sources. The same real-world entity showed up three different ways, so data had to be standardized before it could be joined or aggregated.
• BI performance under load. Dashboards had to stay fast for PowerBI users even as the underlying data grew.

---

The approach

We committed to a medallion architecture — Bronze, Silver, Gold — on AWS, so every stage of the data's journey had a clear job and clear guarantees. Raw stayed raw, cleaning happened in one place, and business logic lived at the top. The whole thing was built to be re-run, audited, and trusted.

The ETL itself ran in Python, orchestrated through a FastAPI service, with the codebase split into focused repositories — shared ETL utilities, per-layer extraction and transformation logic, and the orchestration API — so each layer could evolve independently. We also put AI-assisted quality checks between layers, catching drift and anomalies as data moved from Bronze to Silver to Gold rather than discovering them in a dashboard later.

Every number on a dashboard could be traced all the way back to the source that produced it.

---

Why we built it custom instead of buying an ETL tool

Building a data warehouse is complicated, but it's been done everywhere. The critical choice is almost never whether to build one — it's whether to lean on an existing ETL tool like AWS Glue or Informatica PowerCenter, or to build the pipeline custom. Given a bottomless budget, most teams would choose custom. Budget is usually where that conversation stops. We didn't stop there — we designed our way around the trade-off.

The heart of the system was a way to externalize configuration — source-system definitions and source-to-target mappings expressed as plain JSON and YAML rather than buried in code or a vendor UI. Strip the beauty off any ETL tool and that's what you find at the foundation anyway; we just built directly on it. The result was a pipeline that was custom in capability but configuration-driven in practice — which is how a three-person backend team with no prior data-engineering experience stood the whole warehouse up in under two months. We leaned on the best of open source — PySpark for distributed transformation and Apache Iceberg for table versioning and lineage — so we got the guarantees of expensive tooling without the expensive tooling.

The deciding factor was the roadmap. AI agents need a specific, open base you control end to end — exactly what off-the-shelf tools don't give you.

We had already injected AI capabilities into the platform and planned to add more — quality agents, lineage agents — directly into the warehouse. Building custom meant those agents could sit on top of the raw and processed Parquet and Iceberg data and, for example, alert developers when a change in source data was about to break the ETL pipeline — before it broke. Business rules lived outside the code, externalized and configurable, so the pipeline became a simple series of steps that loaded context on demand and produced clean, analysis-ready datasets. New rules and new sources became configuration changes, not re-engineering.

---

Outcomes

The business went from three disconnected systems to one trusted source of truth. Analysts stopped reconciling numbers by hand, and the questions that used to be unanswerable — the ones that only make sense when payments, bookings, and clinical data sit side by side — became a dashboard refresh away.

The bigger lesson mirrors the rest of this engagement: unifying data isn't a one-time migration, it's an architecture decision. Get the layers right, and a business that's compounding month over month gets answers it can trust — and keeps getting them.

---

Tech & tools

---