Operational data engineering
A Governed Data Warehouse From Incomplete SaaS Data
A U.S.-based outdoor equipment retail and wholesaler ran on a third-party SaaS operational system that was never built for analytics, and reconciled its numbers by hand every month. We built a custom Python ETL engine and a governed PostgreSQL star-schema warehouse — with every business rule and the monthly revenue reconciliation enforced in version control — that became the trusted foundation under their decision-grade Power BI analytics, a companion case study.
Numbers nobody could fully trust
The business ran on a third-party SaaS operational system that was never built for analytics. Getting a clean number meant exporting from a bifurcated operational database and reconciling it by hand — slow, fragile, and impossible to fully trust.
- The source wasn't analytics-readyoperational tables, not a model anyone could report on
- Revenue was reconciled manually every month, by people, in spreadsheets
- Context was scatteredHR pay rates and timekeeping punches lived nowhere near the operational revenue
- No single source of truth the business could point at and believe
Before anyone could build a dashboard worth trusting, the data underneath had to be made trustworthy. That's this story; the dashboards are a companion one.
A custom engine, not a stack of point tools.
Rather than wire together Fivetran, dbt, and a scheduler, we built a purpose-built, config-driven ETL engine — so every source, transform, and business rule lives in version control and the core stays maintainable.
Source operational database → a custom Python ETL engine → a governed PostgreSQL star-schema warehouse. From there it feeds the decision-grade Power BI analytics — a companion case study.
The engine
One config-driven pipeline, source to star schema.
The load runs as an ordered graph of phases — connect, preflight, full sync, enrich, build the star schema, reconcile — and seals an audit record on every run, success or failure.
How a refresh runs.
Preflight
Row-count guardrails, connection checks, and an object-ownership audit gate the run before a single row moves.
Full sync
Source tables reload into Postgres in parallel — truncate, pull, batch-insert, analyze — so every run lands at a known, clean state.
Enrich
HRIS pay rates and timekeeping punches are joined in, turning raw operational data into a unified labor-and-revenue model.
Build the star
Dimensions, facts, and bridge tables refresh into a clean star schema, with materialized views doing the heavy lifting.
Reconcile
A mandatory check compares warehouse revenue to a control total computed straight from the source. The delta must be $0.00 — or the data doesn't ship.
Every number traces back to a rule.
The warehouse isn't a pile of SQL — it's governed. More than ninety business rules (revenue recognition, deleted-record exclusion, internal-revenue zeroing, classification, normalization) live in a version-controlled registry: each one written down, owned, and tied to the exact warehouse object that enforces it.
An audit tool checks that every rule is actually present in the warehouse DDL — automated proof that the build does what the rule book says. Those same rules carry up into the BI measures, so a figure on an executive dashboard traces all the way back to the rule that defines it.
Financial-grade governance
The gate that has to pass.
If the delta is ever non-zero, the close fails and nothing downstream ships. Across 25 months of closes, it held at zero.
The payoff showed up immediately.
There was a second reconciliation, and it's where the return landed first. Once the warehouse stabilized on the company’s published canonical business definitions, it didn't just match the operational source — it began disagreeing, on purpose, with the manual spreadsheets and workbooks the team had been building and exporting from the operational database.
Those hand-built reports had quietly drifted. The governed warehouse surfaced the variances against them — catching real errors in the numbers leadership had been deciding on — and the work started paying for itself almost immediately.
One model out of three systems.
Operational revenue alone doesn't tell you margin. The engine enriches the source with HRIS pay-rate data and timekeeping punch data — reconciling ghost and duplicate punches along the way — to build a unified labor-and-revenue model, so cost and profitability are real, not estimated.
Tools & stack
- Python
- Plugin-based DAG runtime
- YAML
- psycopg2
- Parallel workers
- PostgreSQL
- MySQL
- Star schema (dims / facts / bridges)
- Materialized views
- Version-controlled rule registry
- Automated rule-to-DDL audit
- Zero-delta revenue reconciliation
- Ownership & freshness gates
- OpenTelemetry
- Isolated audit logging
- JSON
- Containerized runs
Why it mattered
Trust
Manual monthly reconciliation became an enforced, zero-delta gate — so the revenue number is the same in the warehouse as in the source, to the penny.
Ownership
One team owns the whole data layer in version control — engine, schema, and rules — so it's maintainable and auditable, not a black box.
Foundation
A governed, reconciled warehouse is the bedrock the dashboards stand on. Trustworthy analytics start here.
You can't build a dashboard worth trusting on data nobody trusts. So we made the data trustworthy first — and proved it every month.
Evidence
What changed in the work.
- A custom, config-driven Python ETL engine — every source, transform, and rule in version control, not a stack of point tools
- 90+ codified business rules in a governed registry, each audited to the exact warehouse object that enforces it
- A mandatory zero-delta revenue reconciliation against a control total computed directly from the source — $0.00 across 25 monthly closes
- 30 materialized views and 30+ supporting views in a governed PostgreSQL star schema
- Cross-system enrichment — operational revenue joined to HRIS pay and timekeeping punches into one labor-and-revenue model

