Skip to main content

Operational data engineering

A Governed Data Warehouse From Incomplete SaaS Data

Medium Size BusinessU.S.-based outdoor equipment retail and wholesaler

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.

$0.00reconciliation, 25 monthly closes
90+rules audited to the warehouse
30materialized views
2systems enriched in

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.

Sources
SaaS operational database — sales, service, rentals, parts, warrantyHRIS — employees and pay ratesTimekeeping — punch data
Governed warehouse
PostgreSQL star schema — dimensions, facts, bridges30 materialized views, 30+ supporting viewsAudit-logged, idempotent full reloadsFeeds the Power BI semantic model
If the monthly revenue reconciliation doesn't net to zero, the data doesn't ship.

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.

01

Preflight

Row-count guardrails, connection checks, and an object-ownership audit gate the run before a single row moves.

02

Full sync

Source tables reload into Postgres in parallel — truncate, pull, batch-insert, analyze — so every run lands at a known, clean state.

03

Enrich

HRIS pay rates and timekeeping punches are joined in, turning raw operational data into a unified labor-and-revenue model.

04

Build the star

Dimensions, facts, and bridge tables refresh into a clean star schema, with materialized views doing the heavy lifting.

05

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

ETL engine
  • Plugin-based DAG runtime
  • psycopg2
  • Parallel workers
Data
  • Star schema (dims / facts / bridges)
  • Materialized views
Governance
  • Version-controlled rule registry
  • Automated rule-to-DDL audit
  • Zero-delta revenue reconciliation
  • Ownership & freshness gates
Operations
  • OpenTelemetry
  • Isolated audit logging
  • 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