sq diff

sq diff compares metadata or row data of sources and tables. See the diff guide for more.

BETA: Compare metadata, or row data, of sources and tables.

CAUTION: This feature is in beta testing. Please report any issues:


When comparing sources ("source diff"), the default behavior is to diff the
source overview, schema, and table row counts. Table row data is not compared.

When comparing tables ("table diff"), the default is to diff table schema and
row counts. Table row data is not compared.

Use flags to specify the elements you want to compare. The available
elements are:

  --overview   source metadata, without schema (source diff only)
  --dbprops    database/server properties (source diff only)
  --schema     schema structure, for database or individual table
  --counts     show row counts when using --schema
  --data       row data values
  --all        all of the above

Flag --data diffs the values of each row in the compared tables. Use with
caution with large tables.

Use --format with --data to specify the format to render the diff records.
Line-based formats (e.g. "text" or "jsonl") are often the most ergonomic,
although "yaml" may be preferable for comparing column values. The
available formats are:

  text, csv, tsv,
  json, jsona, jsonl,
  markdown, html, xml, yaml

The default format can be changed via:

  $ sq config set diff.data.format FORMAT

The --format flag only applies with data diffs (--data). Metadata diffs are
always output in YAML.

Note that --overview and --dbprops only apply to source diffs, not table diffs.

Flag --unified (-U) controls the number of lines to show surrounding a diff.
The default (3) can be changed via:

  $ sq config set diff.lines N

  sq diff @HANDLE1[.TABLE] @HANDLE2[.TABLE] [--data]


  Metadata diff

  # Diff sources (compare default elements).
  $ sq diff @prod/sakila @staging/sakila

  # As above, but show 7 lines surrounding each diff.
  $ sq diff @prod/sakila @staging/sakila -U7

  # Diff sources, but only compare source overview.
  $ sq diff @prod/sakila @staging/sakila --overview

  # Diff sources, but only DB properties.
  $ sq diff @prod/sakila @staging/sakila --dbprops

  # Compare source overview, and DB properties.
  $ sq diff @prod/sakila @staging/sakila -OB

  # Diff sources, but only compare schema.
  $ sq diff @prod/sakila @staging/sakila --schema

  # Compare schema table structure, and row counts.
  $ sq diff @prod/sakila @staging/sakila --SN

  # Compare everything, including table data. Caution: can be slow.
  $ sq diff @prod/sakila @staging/sakila --all

  # Compare metadata of actor table in prod vs staging.
  $ sq diff @prod/sakila.actor @staging/sakila.actor

  Row data diff

  # Compare data in the actor tables.
  $ sq diff @prod/sakila.actor @staging/sakila.actor --data

  # Compare data in the actor tables, but output in JSONL.
  $ sq diff @prod/sakila.actor @staging/sakila.actor --data --format jsonl

  # Compare data in all tables and views. Caution: may be slow.
  $ sq diff @prod/sakila @staging/sakila --data

  -U, --unified int     Generate diffs with <n> lines of context (default 3)
  -f, --format string   Output format (json, csvā€¦) when comparing data (default "text")
  -O, --overview        Compare source overview
  -B, --dbprops         Compare DB properties
  -S, --schema          Compare schema structure
  -N, --counts          When comparing table schema structure, include row counts
  -d, --data            Compare values of each data row (caution: may be slow)
  -a, --all             Compare everything (caution: may be slow)
      --help            help for diff

Global Flags:
      --config string      Load config from here
      --log                Enable logging
      --log.file string    Path to log file; empty disables logging
      --log.level string   Log level: one of DEBUG, INFO, WARN, ERROR
  -M, --monochrome         Don't colorize output
  -v, --verbose            Verbose output