Moving a payments company’s reporting off the production database

The problem

The executive, finance and operations dashboards ran in Grafana and Power BI, straight on the production database. There was no warehouse or anything else in between.

The database itself is large. The core tables are very wide, with many columns per transaction, and hold millions of rows. It is set up to process payments quickly, and scanning weeks of history for a chart is a very different kind of work.

Rewriting the queries wouldn’t have helped much. Every chart had to scan those wide tables on the same database that was processing live payments.

• Picking more than a week of data crashed the dashboards. A 24-hour view took over 30 seconds.
• Reporting queries and live transactions were competing for the same database.
• Nothing was in version control or documented, so each new report started from zero.

What I built

• Warehouse. A ClickHouse database for analytics, separate from production. ClickHouse stores data by column, so a dashboard reads only the columns it needs, which matters a lot with tables this wide. Schema changes go through versioned migrations.
• Pipelines. Airflow syncs the key tables every 5 minutes using a read-only replica user. The jobs recover on their own after failures and include integrity checks and repair tools. A new table gets added to the same framework.
• Modelling. After each sync, pre-aggregated rollups are rebuilt. Dashboards read those, and the raw data is still there for detailed analysis.
• BI. Executive, operational and drill-down reports were rebuilt in Metabase, so everything is in one tool with role-based access.
• Semantic layer. Every table and column in Metabase has a description. AI agents read those descriptions through the Metabase API to learn the data model before they write a query.
• Engineering. Pipelines, migrations and dashboard definitions are in Git, with code review, CI and automated tests, so any change can be traced and rolled back.
• Operations. Slack alerts when something fails or recovers. There’s also a README, design notes, a glossary, a runbook and setup guides.

Results

• 24-hour views are more than 10× faster, and ranges that used to crash now load in a few seconds.
• Reporting no longer touches the production database.
• The team asks AI agents questions about their data, and the agents answer from the documented warehouse tables.
• Adding a new source, metric or dashboard now means extending what’s already there.