Enterprise BI Transformation

The problem

Reporting ran directly against the operational SQL Server. Nightly aggregation jobs took 45+ minutes and regularly failed; six central reporting tables had drifted from each other, so Finance, Operations, and Merchandising each presented different numbers for the same week. Executives had stopped trusting the dashboards — which is the point at which a BI system is effectively dead, whatever it cost.

The architecture

The rebuild followed a strict separation between operational and analytical workloads:

• Ingestion — Airbyte streams the ERP databases, APIs, and legacy CSV drops into a raw landing layer, incrementally and idempotently.
• Warehouse — ClickHouse became the analytical engine. Columnar storage and vectorized execution took the nightly 45-minute aggregations to under 10 seconds — the 259× number that reframed what “waiting for a report” means internally.
• Transformation — the six ad-hoc tables were decomposed and rebuilt as 32 tested dbt models in medallion layers (raw → staging → marts), with documented lineage from source column to dashboard figure.
• Orchestration — Airflow runs the whole graph with retries, SLAs, and alerting. Pipeline failures went from a weekly firefight to an exception.
• Serving — Power BI semantic models read from the marts layer only. One definition of revenue, one calendar, one truth.

What it changed

The measurable outcome is speed and the ~$80K per year the automation returned in analyst hours and infrastructure. The more important outcome is that the Monday numbers match everywhere — the platform earned back executive trust, which is what analytics platforms are actually for.