zero gravitySOLUTIONS
← All insightsPRODUCT DATA / DATA RELIABILITY GUIDE

Check warehouse data before publishing business reports

QUICK ANSWER

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.

BATCH PUBLICATION / KEEP THE TRUSTED GENERATION VISIBLE
LoadedData exists in staging; reports do not use it yet.
ValidatedRequired integrity and reconciliation checks pass.
QuarantinedA failed check names the affected scope and repair owner.
PublishedThe reporting pointer moves to one complete generation.
SupersededThe previous generation remains available under the recovery policy.
A successful load is an intermediate state. Publication is the point at which the business accepts the batch for reporting.

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.

FROM INSIGHT TO ACTIONCustom software development

Publish business data with evidence behind it.

We help implement staging, quality gates, and reconciliation so your reporting tables expose complete, explainable data generations.

Explore software developmentDiscuss your build

Start with your goals.
We’ll work through the next step together.