Skip to main content
SQLBuild can compare schemas and row-level data between two build contexts. This lets you validate that changes produce the expected results before promoting them. sqb diff FROM:TO compares:
  • two targets (e.g. prod:dev) in direct mode, or
  • two virtual environments (VDEs) when virtual environments are enabled.
The mechanics below are identical for both; only what FROM and TO refer to changes. In direct mode, 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.
SQLBuild diff output showing schema comparison, row-level differences, and changed column values between two build contexts

​
Comparison modes

Every diff requires exactly one mode:

​
Full diff

Compares both schema and row-level data for the selected models:
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:
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:
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:
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:
Override the effective policy for one invocation with --sample-rows and --sample-seed, or force an exhaustive comparison with --exhaustive:
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:
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:
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:
Default example limits are 3 per category. Verbose mode increases this to 10. You can also set exact limits:
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:
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:
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:

​
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.