Case Study
How a Retailer Got 98% Accurate Reports and a Faster Order System from One Data Warehouse.
A retail and fulfillment business faced peak-hour slowdowns and crashes from heavy reporting, conflicting definitions across teams, and developers pulled away from product work to answer data requests. We built a separate data warehouse with shared definitions, BI dashboards, and AI-assisted answers for more than 300 staff.
- Staff using BI or the analyst agent
- 300+
- Project timeline
- 4 Months
- Reporting accuracy, up from 65%
- 98%
The Challenge
Heavy Reporting Was Disrupting Daily Operations.
Teams needed consistent answers without disrupting operations or pulling developers away from product work. That meant a separate reporting layer, shared definitions, and direct access through BI and AI.
The Business Context
Orders arrived through several storefronts, then moved through fulfillment, shipping, invoicing, and reconciliation with partners and accounting. One order system supported that work. More than 300 staff across operations, customer support, finance, and partner management also depended on its data throughout the day.
The database processing orders was also the only source for reporting. Heavy queries competed with live orders during peak hours, slowing the system and sometimes causing it to crash. Teams also used different definitions for measures such as an active customer or a shipped order, making their numbers difficult to compare. Developers regularly stepped away from product work to answer data requests. Moving the queries alone would not resolve all three problems: the business also needed shared definitions and a way for staff to get answers directly.
- Heavy reporting usage slowed the order system during peak hours and sometimes caused it to crash.
- Teams used different definitions for measures such as an active customer or a shipped order.
- Developers regularly stepped away from product work to answer data requests.
What We Delivered
We separated reporting from the order system and built a path from operational data to a cloud data warehouse, governed reporting views, BI dashboards, and AI-assisted answers. We also built Data Assist, a data intelligence platform that lets staff ask questions of the business’s data in plain language. The work covered data history, change data capture from the order database, data enrichment, orchestrated and scheduled loads, a governed connection between the AI analyst and the data, automated review of the reporting definitions, and repeatable deployment.
The Solution
Three Problems, and What We Built for Each.
01 / 03
Live ordersHeavy reportsWhat the system can handle The Problem
Reports Slowed the Order System at Its Busiest Time
Heavy reports and live orders shared one database. At peak hours the system slowed, and sometimes crashed.
Live ordersWhat the system can handle What We Built
Reports Moved to Their Own Warehouse
Every change in the order system is copied to a separate warehouse. Reports run there, so orders have the system to themselves.
How We Built It
- 01
The Order SystemEvery change is captured as it happens, without adding load to orders
Change Data CaptureAWS DMSStreams each change out of the order database as it happens
The WarehouseSnowflakeKeeps the full history that reports need
Data EnrichmentdltSeparate pipelines add further data to the warehouse
Dagster runs every load on a schedule and restarts it from a known point. The environment is written as code in Terraform.
02 / 03
“Active customer” means…
- Operations
- Support
- Finance
The Problem
Each Team Counted Things Its Own Way
An active customer or a shipped order meant something different in each team, so the numbers did not match.
“Active customer” means…
- Operations
- Support
- Finance
What We Built
One Agreed Definition for Every Measure
Each measure is defined once in the warehouse, and every dashboard and answer uses it. Reporting accuracy rose from 65% to 98%.
How We Built It
Data as It Was RecordedSnowflakeArrives in the warehouse from every system
Each Measure, Defined Oncedbt“Active customer” has one meaning, written in one place
Every Dashboard and AnswerPower BIAll of them read that one definition
Every change to a definition is tracked and reviewed before it goes live.
03 / 03
The development queue
- Product feature
- Report request
- Product fix
- Report request
The Problem
Developers Were Answering Data Requests
Routine questions about the numbers went to the development queue, taking time away from product work.
Which orders shipped late last week?
Here are the late shipments, by day and carrier.
View the queryFrom the warehouse
What We Built
Staff Get Their Own Answers
More than 300 staff use dashboards or ask a question in plain words, and see the answer with the query behind it.
How We Built It
The WarehouseSnowflakeThe only place reports and answers read from
DashboardsPower BIFor the numbers teams check every day
Questions in Plain WordsData AssistThe answer comes back with the query behind it
Data Assist is an analyst agent built with Vercel eve, with no path back to the order system.
How the Pieces Fit Together
Before: One System Doing Two Jobs
- Customer Orders
- Staff Reports
One Order SystemSlows at peak hours
Heavy reports competed with live orders, causing peak-hour slowdowns and occasional crashes.
Reporting Accuracy65%
After: Orders and Reports Each Get Their Own Path
OrdersLeft to do its one job
Customer Orders
Orders, fulfillment, and finance
Order System
Handles live orders only
Every change is copied across, continuously
ReportingEvery load scheduled and orchestrated with Dagster
Change Capture
Streams every change from the order system
Data Enrichment
Further data added through its own pipelines
Data Warehouse
History and shared definitions
Dashboards
The numbers teams check every day
AI Analyst Agent
Ask in plain words, and see the query behind the answer
300+ Staff
Operations, support, and finance
Reports never touch the order system.
Reporting Accuracy98%
Technologies Used
Snowflake
Data Warehouse
dbt
Data Modeling & Transformation
AWS DMS
Change Data Capture
Dagster
Pipeline Orchestration & Scheduling
dlt
Data Enrichment Pipelines
AWS
Cloud Infrastructure
Power BI
Business Reporting
Terraform
Infrastructure as Code
Vercel eve
AI Agent Framework
Data Assist
Data Intelligence Platform
GitHub
Version Control
How the System Was Built
- 01
Move Reporting Away from Order Processing
To remove the heavy reporting workload from the system processing orders, a change feed brings operational data into a separate cloud warehouse. Opening a report no longer starts a query against the order database.
Technical Detail: Move Reporting Away from Order Processing
Change data capture (CDC) through AWS Database Migration Service (DMS) streams row-level changes from the transactional database. This replaces unpredictable reporting reads with the ongoing overhead of capturing changes.
- 02
Give Teams Shared Definitions and Reliable History
A layered model turns incoming changes into consistent reporting data. Teams use the same definitions, and historical questions retain the context of the period being reviewed.
Technical Detail: Give Teams Shared Definitions and Reliable History
Raw changes are retained as received. A cleaning layer standardizes types, keys, and deletes, using record hashes to pass through real changes. Facts and slowly changing dimensions preserve history. Governed, secured views expose the reporting model.
- 03
Keep Data Moving on a Repeatable Schedule
Scheduled, incremental loads move new changes through the warehouse. Loads can restart from a known point and run on a schedule independent of operational peak hours.
Technical Detail: Keep Data Moving on a Repeatable Schedule
Dagster orchestrates the loads. Change data capture from the order database runs through AWS Database Migration Service (DMS), and dlt pipelines handle data enrichment. Orchestration maintains a watermark for each table, recording the last processed point. Loads are incremental and restartable rather than repeatedly processing the full source history.
- 04
Make Answers Available through BI and AI
genius office built Data Assist, a custom data intelligence platform that interacts with Vercel eve. Alongside BI dashboards, the analyst agent lets staff ask questions in plain language and see the query behind each answer. Both use the same governed reporting views.
Technical Detail: Make Answers Available through BI and AI
The analyst agent uses Vercel eve. We also built a Model Context Protocol (MCP) integration for the agent. The agent generates and runs queries against the reporting layer only. Both BI and the agent read from the warehouse, with no route for reporting queries to reach the transactional database.
- 05
Maintain Standards as the Model Grows
A review agent checks proposed data-model changes against the project’s coding and modeling standards before release, helping keep work consistent across seven business domains.
Technical Detail: Maintain Standards as the Model Grows
Automated checks assess proposed model changes against the same project conventions before human review. This supports consistent modeling as new tables, columns, and domains are added.
- 06
Make Deployment Repeatable
Data schemas, infrastructure, and pipeline configuration are versioned together, with the same deployment approach across local, development, staging, and production environments.
Technical Detail: Make Deployment Repeatable
Schema changes, infrastructure, and pipeline configuration are maintained as code. Disaster recovery scripts sit alongside the deployment assets.
The Results
More Accurate Reporting. More Room for Operations.
Measured Reporting Accuracy
- Previous Reporting Layer in the Transactional System
- 65%
- New Reporting Layer
- 98%
33 Percentage-Point Increase
Reporting accuracy increased from 65% to 98%. The project was delivered over four months.
What Was Delivered
More than 300 staff now use the BI platform or analyst agent for their numbers. Both read only from the warehouse. The order system no longer serves reporting reads, and dashboards and AI answers use the same governed definitions.
What the Team Reported
The client’s team reported that the time-of-day slowdowns associated with reporting were no longer a recurring concern. Routine reporting requests moved away from the development queue and into BI and the analyst agent.
The accuracy figures relate to reporting. Improvements in order-processing performance are reported by the client’s team; no before-and-after latency measurement or post-change crash count is included in this account.
What Changed for the People Doing the Work
- Operations & Support
- Staff can ask a business question and see the answer and its query in the same session, without requesting a report from a developer.
- Finance
- Shared definitions give reconciliation a consistent starting point, instead of a separate query and interpretation for each team.
- Developers
- Routine reporting questions go to BI and the analyst agent, reducing the reporting requests reaching the engineering queue.
- Data Engineers
- Automated review checks modeling conventions before human review, while the data model preserves history as it changes.
- Leadership
- Dashboards and AI answers draw from the same governed views, giving discussions a consistent set of numbers.
What This Project Reinforced
Make the Better Path the Available Path
Reporting needed a useful replacement, along with a clear boundary that kept queries away from the order system.
Build History into the Foundation
Moving reporting into a warehouse also meant supporting questions about previous periods. Historical context belonged in the model from the start.
Give AI the Same Governed Data as BI
An agent querying inconsistent source tables would inherit those inconsistencies. Shared reporting views gave both access paths the same definitions.
Keep Standards Part of Everyday Delivery
Automated review made the project’s conventions repeatable as the model expanded, instead of depending on one person to check every change.