How to Diff Data Between Two Environments (Without Burning Your Warehouse Budget)

Comparing the same table across two environments — staging versus production, pre-migration versus post-migration, PR build versus base — is one of the most common jobs in data engineering, and one of the easiest to do expensively. This guide gives you the SQL patterns, portable across Snowflake, BigQuery, PostgreSQL, and Databricks, ordered from cheapest to most decisive.

The discipline is the same everywhere: run cheap checks first, let their results decide whether the expensive checks are worth running, and cap anything row-level. A diff that scans both tables five times to confirm they were identical all along is a self-inflicted bill.

Rung 1: Row-Count Diff With Percent Delta

Start with one count per side: SELECT 'base' AS env, COUNT(*) AS n FROM prod.db.orders UNION ALL SELECT 'target', COUNT(*) FROM staging.db.orders, then compute the delta as (target - base) / base. Two details people get wrong. First, when the base count is zero the percent delta is undefined — not zero and not infinity. Report it as NULL or an explicit no-baseline status; coercing it to zero makes a brand-new table look like a perfect match. Second, compare at the same logical cut: if one environment loads continuously and the other loads nightly, filter both sides to a common watermark (WHERE loaded_at < :cutoff) or you are diffing freshness, not correctness.

Rung 2: Schema Diff via INFORMATION_SCHEMA

Before profiling values, confirm the columns even agree: SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders' on each side, diffed by name. Snowflake, PostgreSQL, and Databricks (Unity Catalog) all expose INFORMATION_SCHEMA.COLUMNS at the database or catalog level; BigQuery scopes it per dataset as region-or-dataset.INFORMATION_SCHEMA.COLUMNS. Watch the casing traps: Snowflake folds unquoted identifiers to uppercase, PostgreSQL folds them to lowercase, and BigQuery preserves case, so normalize with LOWER(column_name) before comparing across systems. Type names also differ by dialect (NUMBER vs NUMERIC vs DECIMAL), so compare normalized type families, not raw strings, when the two environments are different warehouses.

Rung 3: The 9-Stat Column Profile in One Pass

For each column of interest, compute a small battery of statistics in one scan per environment, not one query per stat: row count, null count, distinct count, min, max, average, and — where the dialect cooperates — median, plus string length min/max for text columns. A single SELECT COUNT(*), COUNT(col), COUNT(DISTINCT col), MIN(col), MAX(col), AVG(col) ... covers most of it. Median is the portability headache: PostgreSQL and Snowflake support PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) (Snowflake also has MEDIAN), Databricks has MEDIAN and percentile_approx, and BigQuery has no ordered-set aggregate — use APPROX_QUANTILES(col, 2)[OFFSET(1)] and accept that it is approximate. Note also that COUNT(DISTINCT col) is exact-but-costly on huge columns; APPROX_COUNT_DISTINCT (Snowflake, BigQuery, Databricks) is usually the right trade for a diff, as long as you use the approximate version on both sides so the comparison is apples to apples.

Diff the two profile rows per column and flag anything that moved beyond a tolerance: a null rate that jumped, a max that grew an order of magnitude, a distinct count that collapsed. Profile diffs are the best cost-to-signal ratio in the whole ladder — they catch most real regressions for the price of two table scans.

Rung 4: Top-K Category Distribution

For categorical columns, compare the value distributions, not just the distinct counts. Aggregate each side to (value, count), keep the top K (say 50) plus an other bucket, then FULL OUTER JOIN the two result sets on the value. Two traps: NULL never joins to NULL, so map it to a sentinel first — COALESCE(CAST(status AS STRING), '__NULL__') — or your null bucket silently appears as both added and removed; and compare shares (count over total) rather than raw counts, so a uniformly larger environment does not flag every category. A category present on one side only, or a share that moved several points, is a finding worth a row-level look.

Rung 5: Numeric Histograms — Compute Bin Edges Across Both Sides

Histograms make distribution shifts visible that means and medians hide. The classic mistake is binning each environment separately: each side computes its own min and max, the bin edges differ, and the two histograms are not comparable — bin 3 on one side covers a different range than bin 3 on the other, so the diff is meaningless even when the data is identical. Instead, compute shared edges first — SELECT LEAST(a_min, b_min), GREATEST(a_max, b_max) across both environments (or shared quantile edges from the union) — then bucket both sides with the same edges: WIDTH_BUCKET(amount, :lo, :hi, 20) on PostgreSQL and Snowflake, or the arithmetic form CAST(FLOOR((amount - lo) / ((hi - lo) / 20)) AS INT64) on BigQuery, which lacks WIDTH_BUCKET. Compare per-bin shares with the same null-sentinel join as the category pattern.

Rung 6: PK-Join Value Diff With a 7-Way Classification

The decisive check joins the two tables on the primary key and classifies every row. FULL OUTER JOIN base b to target t on the PK, then assign each row one of seven classes:

• •added — PK exists only in the target environment
• •removed — PK exists only in the base environment
• •identical — PK matched and every compared column is equal (null-safe)
• •changed — PK matched and at least one non-null value differs
• •nulled — PK matched and a value in base became NULL in target
• •filled — PK matched and a NULL in base became a value in target
• •duplicate_pk — the key is not unique on one or both sides, so row-level attribution is unreliable for that key

Null-safe equality is what separates changed from nulled and filled: use IS DISTINCT FROM on PostgreSQL and Databricks, Snowflake's EQUAL_NULL, or BigQuery's IS NOT DISTINCT FROM / coalesce-hash workaround. Always run the duplicate_pk check first (GROUP BY pk HAVING COUNT(*) > 1 on each side) — a fanned-out join inflates every other class and the diff lies to you. Return the class counts as the summary and a capped sample (a few hundred rows) per interesting class for inspection, with the differing columns pivoted out so a human can see old value next to new value.

A Note on EXCEPT

For a quick are-they-identical check, set difference works: SELECT * FROM a EXCEPT SELECT * FROM b and the reverse. Spellings differ — PostgreSQL and Snowflake use EXCEPT (also MINUS on Snowflake), Databricks supports both, and BigQuery requires the explicit EXCEPT DISTINCT form, which, being DISTINCT-based, also collapses duplicate rows and therefore misses pure duplication changes. EXCEPT tells you that rows differ, not which key or which column, so treat it as a tripwire that routes you to the PK-join diff, not as the diff itself.

Dialect Quirks at a Glance

Cost Discipline

The ladder exists because the rungs differ in price by orders of magnitude. Concretely: run counts and schema diff always — they are near-free and resolve most refactor-only comparisons on the spot. Run profiles only on the columns that matter, in one pass per side. Run distributions and histograms only on columns the profile flagged. Run the PK-join diff last, on a deterministic key sample first (WHERE MOD(ABS(HASH(pk)), 100) = 0 or the dialect equivalent) with the full join reserved for when the sample shows real damage — and always cap and paginate row-level output. Stop when the verdict is clear: if counts, schema, and profiles all match within tolerance, the marginal information from a full row diff rarely justifies scanning both tables again. Record what you skipped and why, so the comparison is honest about its own coverage.

This cheap-first, escalate-on-evidence progression is exactly what Data Workers' Data Guardrail Agent automates: it walks these rungs in cost order across environments, applies the dialect-specific SQL for your warehouse, and returns a structured verdict with the supporting evidence. For more practitioner guides like this one, see our resources library.