Check warehouse data before publishing business reports
Warehouse loads should pass explicit quality checks before becoming the reporting generation. Validate freshness, key uniqueness, relationships, and business totals in staging, quarantine failed batches, and retain the previous complete generation until the replacement is ready.
Inside this article 5 sections +
A warehouse load can finish successfully while introducing duplicate accounts, orphaned usage records, or an incomplete day. If reports read the table immediately, users see plausible numbers before anyone notices the defect. Quality checks need a publication boundary, not only an alert after the dashboard changes.
Load into a staging generation, validate it, and promote it to the reporting surface only when the required checks pass. Keep the previous trusted generation available with an honest freshness label while a failed replacement is repaired.
Describe the batch that should exist
Store a manifest with batch ID, source boundary, expected partitions, schema version, extraction completion state, row counts where meaningful, and load timestamps. A latest-row timestamp alone cannot distinguish a complete batch from one recent record followed by a failed extraction.
Define freshness using the source boundary represented by the batch and the reporting requirement. A file arriving now may contain yesterday's data. Conversely, a source with no changes can still be current if the connector successfully checked it and the contract establishes that no new data was expected.
Separate missing data from valid zero activity. The manifest should tell the publication process whether a partition was observed and empty or never received. Treating both as zero can make an outage look like a sudden business decline.
Check keys and relationships explicitly
Amazon Redshift treats primary-key, unique, and foreign-key constraints as informational rather than enforcing them on load. The optimizer can use declared constraints as assumptions. Declare them only when your pipeline maintains the promised integrity, and verify that integrity before publishing.
Check uniqueness at the intended grain, such as account and usage date. Check that required relationships resolve, such as every usage row referring to a known account in the compatible account snapshot. Add domain checks for nonnegative counts, valid currencies, or permitted status values where the data contract requires them.
import sqlite3
cx = sqlite3.connect(":memory:")
cx.execute("CREATE TABLE accounts (account_id TEXT)")
cx.execute("CREATE TABLE usage_stage (account_id TEXT, day TEXT, quantity INTEGER)")
cx.execute("INSERT INTO accounts VALUES ('A')")
cx.executemany("INSERT INTO usage_stage VALUES (?, ?, ?)", [
("A", "2026-10-01", 3),
("A", "2026-10-01", 3),
("B", "2026-10-02", -1)
])
duplicates = cx.execute(
"SELECT account_id, day FROM usage_stage GROUP BY account_id, day HAVING COUNT(*) > 1"
).fetchall()
orphans = cx.execute(
"SELECT u.account_id FROM usage_stage u LEFT JOIN accounts a ON a.account_id=u.account_id WHERE a.account_id IS NULL"
).fetchall()
invalid = cx.execute("SELECT account_id FROM usage_stage WHERE quantity < 0").fetchall()
assert duplicates == [("A", "2026-10-01")]
assert orphans == [("B",)]
assert invalid == [("B",)]
This executable fixture verifies the relational logic using SQLite and invented records. It is not a Redshift performance test or a production schema. Run equivalent checks on representative Redshift staging data and inspect execution cost before adopting them at scale.
Compare totals without hiding offsetting errors
Reconcile source and target counts or amounts at a useful grain: date, tenant, product, or status. A global total can balance while one account is duplicated and another is missing. Preserve enough detail to locate the mismatch without exporting sensitive raw records into general reports.
Use exact comparisons where the contract is exact and documented tolerances where transformation legitimately changes representation. A tolerance should have a reason, owner, and version. Do not widen it automatically after a failure to make the batch pass.
Check null rates and distribution changes as investigation signals. A sudden increase in missing plan tier may indicate a broken join, but it could also reflect a real product change. These checks often need review rather than the same deterministic block used for duplicate business keys.
Make publication an atomic choice
Use a transaction, view switch, generation pointer, or another platform-appropriate mechanism so readers do not observe half of a promoted batch. Coordinate dependent tables whose relationship checks require the same source boundary. Publishing accounts from one snapshot and usage from another may create temporary orphans even when each table passed separately.
Retain the check results and manifest with the published generation. A dashboard discrepancy should be traceable to the exact batch, transformation version, and validation evidence. Avoid overwriting yesterday's quality report with today's status.
If a late correction arrives, create a new generation or controlled repair operation with its own validation. Do not edit published data invisibly and leave the original pass result attached to a state that no longer exists.
Design the repair path before the first quarantine
Assign owners by failure type: source extraction, transformation, identity mapping, schema, or business-definition mismatch. Include a bounded sample of failing keys and a reproducible query. “Data quality failed” without scope or evidence creates another manual investigation.
Test duplicate rows, missing parent records, an absent partition, a late batch, and an interrupted publication. Verify that the old generation remains readable and that the freshness indicator changes appropriately. A stale but clearly labeled report can be more useful than a silently corrupted current one.
Read data reconciliation for cross-system checks and metric governance for definitions. Our software implementation services connect those checks to the warehouse publication process.