Data Diffs

SQLBuild can compare schemas and row-level data between two targets. This lets you validate that changes produce the expected results before they reach production.

sqb diff FROM:TO compares two targets, for example prod:dev.

FROM and TO resolve the authoritative database/schema namespaces to compare. SQLBuild uses the TO target’s named connection for the complete comparison and accesses both namespaces through fully qualified relations. It does not resolve or open the FROM target’s connection. The TO connection must therefore be able to read both namespaces; missing credentials for that execution connection still fail before warehouse inspection.

sqb diff prod:dev --full --select customer_status_snapshot

Comparison modes

Every diff requires exactly one mode:

Full diff

Compares both schema and row-level data for the selected models:

sqb diff prod:dev --full --select fact_orders

Rows are joined on the model’s unique_key and compared column by column. The output shows:

• Row counts for each side
• How many rows are equal, unequal, or only in one side
• Which columns have mismatches with match percentages
• Example values showing what changed

Schema-only diff

Compares column names and types without looking at row data:

sqb diff prod:dev --schema-only --select fact_orders

Useful for quick structural checks or when row comparison would be too expensive.

Bounded diff

Compares only a recent window of data using the model’s cursor:

sqb diff prod:dev --bounded 14d --select hourly_order_activity

For timestamp cursors, the bound is a duration (14d, 6h, 30m). For integer cursors, the bound is an integer value. If the model has no cursor configured, the diff falls back to a full row comparison.

Deterministic key sampling

A cursor bound limits the range of data but does not guarantee a predictable number of rows. Use deterministic key sampling to cap the wide value comparison for a high-volume model:

[defaults]
row_diff_sample_rows = 100000
row_diff_sample_seed = 0

[path_defaults.intermediate]
row_diff_sample_rows = 25000

The settings use the normal configuration precedence: project defaults, matching path_defaults, the model’s MODEL() header, and finally CLI overrides. A model can disable an inherited sample and request exhaustive comparison with row_diff_sample_rows 0:

MODEL (
  materialized table,
  unique_key [order_id, line_id],
  row_diff_sample_rows 0,
);

Override the effective policy for one invocation with --sample-rows and --sample-seed, or force an exhaustive comparison with --exhaustive:

sqb diff prod:dev --bounded 14d --sample-rows 50000 --sample-seed 7 --select order_lines
sqb diff prod:dev --bounded 14d --exhaustive --select order_lines

SQLBuild applies the complete cursor bound first. It then selects the lowest deterministic hashes from the union of unique keys found on either side and uses that same key set for both relations. This avoids false side-only rows caused by independently sampling each side. Composite keys are encoded with component lengths before hashing, and key columns break hash ties deterministically.

Schema comparison, bounded row counts, cursor minimum/maximum values, and null/duplicate key checks remain exhaustive. Only the wide column-by-column comparison is sampled. If the bounded union has no more keys than the configured limit, SQLBuild reports the comparison as exhaustive.

Sampled success means that no differences were found in the sampled keys; it is never reported as complete table equality. The output shows the bounded key population, compared key count, seed, and percentage evaluated.

Cursor coverage

Before comparing values, SQLBuild reports exact bounded COUNT, MIN(cursor), and MAX(cursor) for each side. If cursor extents differ, it warns and continues with the requested comparison. It does not ask for confirmation, silently narrow to the overlap, or hide rows that exist on only one side.

Row matching

Rows are matched between the two sides using the model’s unique_key. Models without a unique_key can use schema-only diff but cannot run full or bounded row comparisons.

The diff output categorises rows as:

• Equal - same key, same values on both sides
• Unequal - same key, different values (with per-column breakdown)
• Left only - exists in the FROM side but not TO
• Right only - exists in the TO side but not FROM

Tolerances

Numeric columns can have tolerance rules to avoid false positives from floating-point differences or acceptable variance. Configure tolerances in the model’s MODEL() header:

MODEL (
  materialized incremental,
  ...
  row_diff_tolerances (
    by_column (
      total_revenue_cents (
        absolute 1,
      ),
    ),
  ),
);

Tolerance rules support:

• absolute - maximum allowed absolute difference (e.g. 1 means values differing by 1 or less are treated as equal)
• relative - maximum allowed relative difference as a decimal (e.g. 0.01 for 1%)

Tolerances can be set per-column (by_column) or per-type (by_type).

Excluded columns

Columns that are expected to differ between the two sides (like timestamps or context-specific values) can be excluded from the row comparison:

MODEL (
  materialized incremental,
  ...
  row_diff_exclude_columns [latest_order_status],
);

Excluded columns are still shown in the schema comparison but skipped during row-level diffing. A column cannot be in both row_diff_exclude_columns and unique_key.

Verbose output

Add --verbose or -v to see more example rows for mismatches and side-only rows:

sqb diff prod:dev --full --select customer_status_snapshot --verbose

Default example limits are 3 per category. Verbose mode increases this to 10. You can also set exact limits:

sqb diff prod:dev --full --select customer_status_snapshot --max-column-examples 20 --max-row-only-examples 5

Example limits only control diagnostic values printed after comparison. They are separate from row_diff_sample_rows, which controls how many unique keys receive the wide value comparison.

Structured output

Use --json-output PATH to write a stable structured result while retaining the normal terminal summary:

sqb diff prod:dev --bounded 14d --sample-rows 50000 --select order_lines --json-output diff.json

Each model records schema_only, exhaustive, or sampled comparison scope; requested and observed cursor coverage; bounded population and compared key counts; seed and configured limit; row result counts; and changed-column counts. The top-level status distinguishes no_differences_found from differences_found.

Invocation safety limits

Use --max-models and --max-columns to put explicit hard limits around a broad selector. SQLBuild fails visibly instead of truncating the selected models or compared columns:

sqb diff prod:dev --bounded 14d --sample-rows 50000 --max-models 10 --max-columns 80 --select path:models/intermediate

These limits are optional and apply to the complete invocation. --max-columns checks the larger observed schema for each model before starting its row comparison.

Selectors

Diff requires --select in the current version. You can use any selector syntax:

# Diff a single model
sqb diff prod:dev --full --select customer_status_snapshot

# Diff all models in a path
sqb diff prod:dev --schema-only --select path:models/marts

# Diff models with a specific tag
sqb diff prod:dev --full --select tag:acceptance

Exit codes

sqb diff returns exit code 0 when all selected models have no differences, and 1 when any model has schema or row differences. This makes it usable in CI pipelines as a validation gate.