A data warehouse brings data from different operational systems into a structure designed for reporting and analysis. Its value comes from consistent definitions, understandable history, and results that can be traced back to source records.
AI assistance can help draft ingestion code, SQL transformations, and dashboard queries. It cannot infer the meaning of revenue, an active customer, or a canceled order with certainty. Establish those definitions and verify small examples before scaling the pipeline.
Follow the data from source to answer
| Stage | Purpose | Evidence to retain |
|---|---|---|
| Source capture | Collect records from applications or files. | Source identifier, extraction time, and completeness marker. |
| Staging | Preserve and inspect incoming records. | Original fields, schema changes, and rejected rows. |
| Modeling | Create consistent entities and measurements. | Keys, transformation version, and business definitions. |
| Reporting | Aggregate and present results. | Filters, refresh time, units, and query version. |
Keep raw source snapshots according to a defined retention policy. A dashboard number should be explainable through its transformations and inputs. Refresh success alone does not prove every expected record arrived.
Define what one row represents
The grain of a table is the meaning of one row: one order, one order line, or one daily balance, for example. Mixing grains is a common route to inflated totals. The Kimball Group's discussion of fact tables emphasizes defining grain before choosing dimensions and facts.
Write definitions next to the schema. Specify whether an amount includes tax, discounts, refunds, or currency conversion; whether a timestamp is stored in UTC; and how corrections affect history. A customer who changes region raises another question: should past orders retain the region at purchase or move to the current region?
Worked example: a join that doubles revenue
Suppose order 101 has a total of 3,000 cents and two line items; order 102 has a total of 2,000 cents and one line item. Joining orders to lines and summing the order total produces 8,000 cents because order 101 appears twice. The correct order-level total is 5,000 cents.
This standalone SQL example uses a common table expression to define the two orders and aggregate at their own grain. It can run in SQLite or PostgreSQL.
WITH orders(order_id, customer_id, total_cents) AS (
VALUES (101, 'A', 3000), (102, 'A', 2000)
)
SELECT customer_id,
COUNT(*) AS order_count,
SUM(total_cents) AS total_cents
FROM orders
GROUP BY customer_id;Expected output: customer A, two orders, 5,000 cents. If the report also needs item counts, first aggregate lines to one row per order, then join that result to orders. Do not use SUM(DISTINCT total_cents) as a repair: two different orders may legitimately have the same total.
Validate the model before the visualization
- Keys: Check uniqueness at the declared grain and references to required parent records.
- Counts: Reconcile input records, exclusions, and output records with documented reasons.
- Totals: Compare a small known period with the source system using the same definition.
- Freshness: Report the newest complete interval, not merely the most recent execution time.
- History: Test late arrivals, corrections, refunds, and repeated ingestion.
Database constraints can enforce parts of the contract. PostgreSQL's constraint documentation covers uniqueness, required values, checks, and foreign keys. Some analytical platforms enforce a different subset, so verify behavior on the selected engine.
Separate data quality from business interpretation. A valid negative amount may be a refund; treating every negative value as an error could distort the report. See data cleansing for a reproducible review process.
Give an assistant the definitions and counterexamples
Using this schema, produce an order-level report. State the grain of each input and output. Explain each join's expected cardinality. Test two orders with equal totals, multiple lines per order, an order with no lines, and a refund. Show expected results before writing the final query.
Ask for query explanations and investigate assumptions about nulls, dates, and relationships. Run generated SQL against a small approved dataset first. Use parameterized inputs, read-only credentials, execution limits, and access controls for any natural-language query feature.
A model should not choose which sensitive columns a user may see. Apply the reporting user's permissions in the data-access layer, including downloads and cached results. Record enough query context to reproduce an answer while respecting data retention rules.
When should a team add a warehouse?
When cross-system reporting, consistent metrics, or historical analysis justify maintaining the pipeline. A small report may need only a well-designed query or extract. Start with one decision the report supports and expand after its numbers reconcile.
Schedule refreshes and missed-run alerts using the principles in job scheduling software.