The symptom everyone recognizes
Someone in finance pulls a number. Someone in operations pulls what they believe is the same number. The two do not match. What follows is a familiar ritual: a meeting, a reconciliation, a spreadsheet, and a decision about which version is "right" made by whoever argues most persuasively rather than by anything a system can adjudicate.
Most mid-market companies treat this as a BI problem and go shopping for a better dashboard tool. It is not a BI problem. It is an architecture problem, and it lives several layers below the dashboard.
What is actually broken
In most high-growth companies, operational systems were adopted one at a time, each solving a real problem in isolation. Transactions here. Fulfillment there. Pricing somewhere else. Inventory in a fourth place. Each system is internally coherent. Collectively, they share nothing no common keys, no conformed dimensions, no agreed grain.
The only relationship between them is that they belong to the same company. When a business needs a cross-system number, someone bridges that gap by hand. Flat file exports. Lookups. Macros. The bridge lives in a spreadsheet, in one person's head, and nowhere else that a system can verify.
The architecture that fixes it
The fix is not a tool. It is a sequence of layers, each doing one job, with business logic living in exactly one place.
1. Define the metrics before you build anything
The most consequential work happens before a single pipeline exists: deciding what each number means, which system is authoritative for it, and how it is calculated. This is business systems analysis, not engineering and skipping it is why so many warehouse projects produce a faster version of the same ambiguity.
2. Ingestion: land raw, transform later
Extract from source systems and land raw data in object storage in our AWS builds, Amazon S3 before transforming anything. AWS Glue and Python-based ingestion services handle the bulk extraction. AWS Lambda handles lightweight, event-driven ingestion where a full job would be overkill. Landing raw preserves the ability to reprocess history when a business rule changes. It will change.
3. Orchestration: two tools, two jobs
Using both Apache Airflow and AWS Step Functions is not redundancy. It is matching the orchestrator to the specific shape of the workloads:
- Manages end-to-end analytical pipelines.
- Monitors dependencies, scheduling, retries, backfills, and dbt execution.
- Used where the structured SQL DAG is the ultimate priority.
- Orchestrates event-driven, AWS-native workflows.
- Coordinates lightweight serverless triggers across S3, Lambda, and Glue.
- Used where immediate event-based processing is the priority.
4. The warehouse: layered, not flat
Amazon Redshift acts as our central warehouse. To make database lineage meaningful, models must strictly follow a layered dependency rule (each layer may only query the layer directly beneath it):
5. Transformation: dbt, and only dbt
dbt manages every SQL transformation inside Redshift: cleansing, business rules, fact/dimension modeling, incremental processing, testing, documentation, and lineage tracking. The rule that matters: core transformations and financial calculations live in dbt, never duplicated in the BI tool. A metric has one home. The moment it has two, they drift, and you are back to two analysts with two answers.
6. The semantic layer: the piece most teams skip
This is the layer that converts a warehouse into an enterprise-wide source of truth.
The dbt Semantic Layer, powered by MetricFlow, centrally defines governed business metrics, dimensions, entities, relationships, and approved calculation logic. Those definitions are maintained as version-controlled code inside the dbt project. Redshift stores the underlying models and executes the generated metric queries.
One definition. Governed in code. Reviewed like code. Consistent everywhere it is consumed.
7. BI: governed self-service, not a second logic layer
Metabase functions as the governed self-service and visualization layer, built strictly on certified dbt data marts. Analysts get real freedom to explore. They do not get the freedom to redefine revenue.
Why this is the same architecture AI requires
The metric definitions are explicit, machine-readable, and authoritative. The model is no longer guessing at business logic it is querying logic that already exists.
This is the whole argument: a company cannot jump from manual spreadsheet reporting to asking an AI system about its revenue. The semantic layer is the bridge, and it has to be built before the AI is worth anything. AI is not the starting point. It is what a correctly engineered data foundation earns you.