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. sqb diff FROM compares FROM with the active target, the same default sqb clone --from uses.

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

A diff takes at most one mode flag. Without one, it runs a full diff guarded by table size.

Full diff

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

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

Leaving out the mode flag also runs a full diff, but only after a size check. Before reading any table data, SQLBuild reads each selected table’s row count from warehouse metadata on both sides. If a table is larger than that side’s max_full_rows limit (10,000,000 rows unless configured), or its size is unknown, the diff stops without scanning. See size guard for details. --full skips the check.

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 (
  description "One row per order",
  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 a row key. By default, row identity comes from each model’s unique_key. Repeated --key arguments override that configuration for one invocation:

sqb diff prod:dev --full --select order_lines \
  --key order_id \
  --key line_id

For a model without a stable row key, opt in to exact full-row multiset comparison with --unkeyed. Duplicate rows retain their multiplicity; SQLBuild never guesses a key.

sqb diff prod:dev --full --select daily_order_totals --unkeyed

Unkeyed diffs show sample complete rows that one side holds more often than the other, up to --max-row-only-examples per side; a row duplicated three more times on one side appears three times. In --json, they are examples.from_only_rows and examples.to_only_rows, and from_only_keys and to_only_keys stay empty because there is no key.

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 (
  description "Example 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 (
  description "One row per order",
  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

Compare raw queries

Query mode omits FROM:TO because each query identifies its own input. Supply each side inline or from a UTF-8 file, and choose keyed or unkeyed comparison explicitly:

sqb diff \
  --left-query 'SELECT order_id, amount_cents FROM analytics.orders_before' \
  --right-query 'SELECT order_id, amount_cents FROM analytics.orders_after' \
  --left-label before \
  --right-label after \
  --key order_id

sqb diff \
  --left-query-file queries/orders_before.sql \
  --right-query-file queries/orders_after.sql \
  --key account_id \
  --key order_id

Raw inputs must contain exactly one read-only SELECT, WITH, or VALUES expression. SQLBuild does not expand ref() or other SQLBuild templates in these inputs. The active target connection executes both sides; use --target NAME to select another configured connection.

Query mode performs a full comparison when no comparison mode flag is present. --schema-only is also available. --bounded is model-specific; filter both raw queries directly when a bounded query comparison is needed.

Raw-query discovery loads only project configuration, target configuration, and the selected adapter. It does not import unrelated project sinks, hooks, loaders, or providers.

Sampling, tolerances, and exclusions

Keyed query comparisons support deterministic union-key sampling and the same bounded mismatch examples as model comparisons:

sqb diff \
  --left-query-file queries/orders_before.sql \
  --right-query-file queries/orders_after.sql \
  --key order_id \
  --sample-rows 50000 \
  --sample-seed 7

Use repeated invocation overrides where needed:

sqb diff \
  --left-query-file queries/orders_before.sql \
  --right-query-file queries/orders_after.sql \
  --key order_id \
  --exclude-column loaded_at \
  --tolerance amount_cents:absolute=1 \
  --tolerance conversion_rate:relative=0.001

--exhaustive disables inherited or requested sampling. Unkeyed comparison is always exhaustive and exact, so it does not accept sampling or numeric tolerances.

Bounded example values

Comparison remains exact regardless of how examples are rendered. By default, long values are limited to 160 characters and show context around the first differing character. Every shortened value is marked as truncated and includes its original length.

Control evidence independently from the number of collected examples:

# Increase the displayed context without changing comparison scope.
sqb diff --left-query-file before.sql --right-query-file after.sql \
  --key order_id --max-value-length 500

# Retain keys and aggregate counts but suppress example values.
sqb diff --left-query-file before.sql --right-query-file after.sql \
  --key order_id --no-example-values

# Explicitly opt in to complete values. --verbose alone remains bounded.
sqb diff --left-query-file before.sql --right-query-file after.sql \
  --key order_id --full-example-values

Ordinary keys are kept complete. Exceptionally long keys are shortened with an explicit length and stable digest so an operator can still identify them without flooding terminal output.

Stable query results and cleanup

SQLBuild materializes each raw query once in the selected target’s default schema before comparing it. Artifacts use a reserved run-owned name and are dropped immediately when the command finishes, including after comparison errors.

Each successfully created artifact publishes an immutable query_diff_artifact fingerprint. The fingerprint is ownership evidence for crash recovery only: running and comparing the queries never reads historical fingerprints, and fingerprints do not represent planned, running, complete, or cleaned workflow states.

If a process stops before immediate cleanup, the next query diff and sqb janitor can remove an expired artifact only when its exact physical identity and reserved name match its ownership fingerprint. A matching name without ownership evidence is reported for manual investigation and is not deleted automatically.

The recovery TTL defaults to 24 hours and accepts SQLBuild fixed-duration syntax:

[diff]
query_artifact_ttl = "24h"

The TTL is a crash-recovery backstop, not the normal cleanup path. Historical ownership inspection problems are reported but do not prevent the current queries from running. Failure to publish ownership for a newly created artifact, or failure to remove a current-run artifact, is an execution error.

Query output and exit codes

Use --json-output PATH, or --json to emit one JSON document to stdout while all lifecycle progress remains on stderr. Query results include input_kind = "query", named left and right inputs, query_comparisons, sparse nonzero column_mismatches, lifecycle timings, and explicit example truncation metadata. Existing models output remains available for compatibility.

Raw-query automation distinguishes complete findings from incomplete evidence and execution failure:

Exit	Outcome
0	Complete comparison with no differences
1	Complete comparison with findings
2	Safety, schema, or key preflight prevented complete value evidence
3	Setup, connection, materialization, comparison execution, ownership, or cleanup failed

Human output and JSON use the same outcome. Lifecycle messages include elapsed connection, reconciliation, inspection, materialization, comparison, cleanup, and total durations.

Exit codes

In model mode, sqb diff returns exit code 0 when all selected models have no differences, 1 when any model has schema or row differences, and 2 when the size guard stopped a default full diff before reading data. Raw-query mode uses the exit codes in Query output and exit codes. This makes it usable in CI pipelines as a validation gate.