Data compare

Put two tables, two query results, or two pieces of SQL side by side and see exactly what differs. Rows added, removed or changed by key; columns and table options that moved; lines of SQL that don't match.

Opening it

• ⌘ ⇧ C in the editor, or ⌘ K → Compare tables…. When the tab you are in shows a table, that table becomes the left source and the right picker opens.
• Header More Actions menu → Compare.
• Right-click a table in the schema tree, or on its tab → Compare with…. The table becomes the left source and the right picker opens, so the next thing you do is choose what to compare it with.
• Results history → Compare Results (that result becomes the left source).

Compare is a tab, and there’s only one; opening it again reuses it. Three modes sit at the top: Data, Queries, Schema. Switching modes clears the sources. The tab is named Compare in the tab bar, in your language.

Data

The setup is one screen. Two cards for the left and right source, then Match rows by, then Scope, and a line above Compare that says where it runs and what it costs.

Choosing the sources

Each card starts as one field, Choose a table or query result. It opens a picker with Query Results first (results from open tabs, kept for 24 hours), then every project → dataset → table. Search covers both. Picking closes the picker.

A picked table shows its name and dataset, then what matters for the comparison: rows, size, location, how it is partitioned and clustered. A query result shows its query, its age and its rows. Change opens the picker again, and the arrows between the cards swap the sides.

As of reads the table at an earlier time, up to 7 days back. The menu offers 1 hour to 7 days ago and a custom time, and the label is written in your language. Setting it on both sides to different times compares one table with itself over time. That runs as a two-step script: it saves the earlier version first, then compares.

One BigQuery query can’t read tables in two locations. When BigQuery would run the comparison, the setup says which location each table is in, and Compare stays off until both are in the same place. Small tables compare fine in your browser.

Match rows by

Choosing a different table on either side starts a new pair: the keys, partition ranges, filters and ignored columns of the old pair are cleared, and the best key for the new pair is suggested again. A filter is SQL written against the old table, so it is cleared even when a column of the same name exists in the new one. Swapping the sides, or setting a different As of time, keeps everything.

Tick the columns that identify a row. Suggested keys come first, each with the reason: a declared Primary key (a composite key is one entry and ticks all its columns), a Clustering column, or a column that Looks like an id. The best suggestion is ticked for you once per pair of tables; untick it if it is wrong and it stays unticked. A primary key that has a column missing in one table, or a column of a type that can’t be matched on, is not suggested. Choose… lists the other common columns. STRUCT, ARRAY, JSON, GEOGRAPHY, INTERVAL and RANGE columns are never offered. At least one key is required before Compare enables.

A key has to identify one row. Before it runs, a comparison looks for keys that repeat, in your browser or with a query in BigQuery that reads the key columns of both tables (its size is in the estimate). If a key repeats, nothing is compared. The setup says how many values repeat on each side, and you add a column to the key. After a clean run it reads Unique on both sides. Ticking a key never starts a billed query.

Compare whole rows instead hashes whole rows: you get added and removed rows only, never changed.

Scope

• Partition range: for partitioned tables, a range per side or Same filter for both, with presets from Today to Last 90 days. The setup says in words what is read. A table that requires a partition filter gets the last 12 months until you choose a range. A table partitioned by an integer range has no default, so you choose one. Any other partitioned table is read in full.
• Filters: WHERE conditions such as status = 'active', ANDed together. Text that isn’t a SQL expression is refused when you add it. A name that isn’t a column is reported when the comparison starts. When rows are matched whole, a filter can only use compared columns; a filter on any other column is refused in the setup.
• Ignore columns: left out of the comparison and the results. Use it for updated_at and other columns that differ on every write.
• Not compared: columns that can’t be compared, with the reason. A column with a different type in each table (STRING against INT64), an array in one table only, or a column that exists on one side only. Columns whose names differ only in letter case are matched, and the setup says so.

Where the comparison runs

The line above Compare says it. A small comparison runs in your browser at no cost: Runs in your browser · 113 rows · no query cost. A comparison runs in BigQuery when a side is a view, a materialized view, an external table or a snapshot (the browser can only read plain tables), when the two tables hold more than 10,000 rows together, when a side has a partition range or an As of time, or when a table’s row count is unknown. Then the line shows what it reads and costs: Runs in BigQuery · reads 6.1 MB · $0.00, in the same format as the status bar. Only the differences come back as rows. The query counts everything else itself, in the same statement, so the estimate already includes it and nothing is billed twice. BigQuery runs the joined rows once for the counts and once for the rows, so the bytes are billed once and the join takes about twice the slot time; a slot-priced estimate says so.

If the estimate is over your per-query limit, the line says so with the limit, and Compare stays off. Narrow the partition range or ignore columns. Any change to the sources, time, range, filters, ignored columns or keys makes the estimate out of date. Compare waits for the new one. ⌘Enter (CtrlEnter on Windows and Linux) presses it when it is enabled.

Open comparison SQL in new tab shows the generated query.

Reading the results

A line above the results names the sources, the key, the filters and ignored columns, where it ran, what it read and when it finished. Edit goes back to the setup with everything kept; New comparison starts empty; Compare again (⌘Enter or CtrlEnter) runs the same comparison once more. It follows the same rule as Compare in the setup: it waits for an estimate, and it is off when the estimate is over your per-query limit, with the reason beside it. Your budget is checked when the run starts.

The results start with four tiles: Added (right only), Removed (left only), Changed (same key, other values) and Unchanged. They are also the row filter: pick one to see only those rows, pick it again to see all. Under the tiles are the number of rows each side has and a bar of how they split. In BigQuery the counts come from the query itself and cover every row; Unchanged is counted but its rows are not listed. When rows are matched whole there is no Changed tile.

Changed columns lists each compared column with how many matched rows differ in it and the share of matched rows, most changes first. Columns with no change fold into one line. Pick a column to see only the rows where it changed, with a sample of the old and new value.

The rows show Unified (one row per key, the old value struck through above the new one) or Side by side (the left and right values of each column, key columns pinned). Your choice is remembered. A status icon in the first column says Added, Removed or Changed, and null is written in italics, so a change from NULL to an empty string is not a blank arrow. Values read the same in the browser and in BigQuery: timestamps as timestamps, arrays as JSON, and a STRUCT column as one column per field (metadata.name) in both. Ignoring a STRUCT column ignores all its fields. A NULL STRUCT and a STRUCT whose fields are all NULL count as the same.

A few results look different:

• No differences. The tiles show zeros and a note says how many matched rows and columns were compared. No rows are shown.
• More than 50,000 differing rows. BigQuery shows the first 50,000 by key and says so. The counts and the changed columns still cover every row. The comparison SQL has the same limit.
• Columns not compared. A line under the tiles counts them and lists each with its reason.
• After a reload. The summary is kept with the tab: counts, changed columns, the engine and when it finished. The rows are not. The tiles stay, a note says Results from 11:52 aren't kept after a reload and shows what Compare again reads. On a phone the tiles are two by two and the rows are Unified.

Export offers CSV (with diff), one row per difference with + − ~ markers, and Google Sheets with left and right columns side by side. Copy puts the visible rows on the clipboard as TSV with the same markers. Both use the rows as filtered on screen. Open comparison SQL opens the generated query for a BigQuery run.

Keyboard

The ? button in the header (or the ? key) lists the keys of the screen you are on. Letter and number keys work when the cursor is on the tab and not in a field, the editor or an open list, and never while a key is held or an input method is composing text.

Back to the setup keeps everything you chose (sources, keys, filters, ignored columns) and clears the result, so Compare again runs it once more. E and Esc do nothing while a comparison is running; use Cancel. An Esc that closes a tooltip or a picker does not also leave the results.

The four tiles also take the arrow keys, Home and End once one has focus. A status with no rows listed (Unchanged in a BigQuery run) has no number key. In Queries, F7 and ShiftF7 step through the changes, and E and Esc go back to the setup.

On a phone

The setup stacks the two cards, with the swap button turned to point up and down, and the line above Compare sits over a full-width button. Scope opens as a sheet from the bottom, with Done to close it. In the results the tiles are two by two, Changed columns is a list above the rows, and the rows are always Unified. The header wraps, so Edit and New comparison stay on screen.

Schema

Choose two tables and Compare. Above the two pickers, Object type switches between Tables, Views and Routines; the choice applies to both sides. Each side is shown as Left or Right with its full name, in the same neutral color. Column rows are marked + in right only, − in left only, ~ modified, with All (31) and per-status counts to filter. Type, mode and description are compared per column; nested RECORD fields are listed by dotted path.

Table Options Changed lists partitioning (time and integer-range), clustering, description, labels and require partition filter as old → new. The same labels in a different order are not a change. Views also get a diff of their definition; routines get only that.

Copy ALTER statements (tables only) copies statements that bring the left table to the right one: ADD COLUMN, DROP COLUMN, ALTER COLUMN … SET DATA TYPE, ALTER COLUMN … DROP NOT NULL and ALTER COLUMN … SET OPTIONS (description = …) (a description that was removed becomes NULL). Names are quoted. A new RECORD is added as one STRUCT<…> column and a repeated column as ARRAY<…>. Every statement is one BigQuery accepts. What BigQuery cannot do with ALTER comes out as a comment line starting with --, so it is visible where you paste it:

• adding a REQUIRED column to an existing table (a column that goes from REQUIRED to NULLABLE is written);
• adding, dropping or changing a field inside an existing RECORD, its description included;
• a type change other than widening (INT64 to NUMERIC, BIGNUMERIC or FLOAT64; NUMERIC to BIGNUMERIC or FLOAT64);
• a mode change other than REQUIRED to NULLABLE;
• dropping or retyping a partitioning or clustering column.

Table options (partitioning, clustering, table description, labels) are not copied. Type parameters such as NUMERIC(10, 2) or STRING(20) are not compared and not written.

Queries

Pick from Open tabs or Paste SQL on each side. A pasted side is a twelve-line editor with the same SQL highlighting as the query editor. Compare or Cmd+Enter (Ctrl+Enter on Windows and Linux) runs the comparison, also from inside a paste editor. It runs in your browser and costs nothing.

The result is a diff editor with unchanged lines folded (click a fold to open it). The line above it names what was compared: the tab name with the time its SQL was taken, and Pasted SQL. The counts +2 −1 ~2 are added, removed and changed lines. They are taken from the same diff the editor draws, so they always agree with the highlighting: swapped lines or a re-indented query are shown as changes, never as Identical. Unified shows one pane with the changes inline; Side by side shows two (a phone gets Unified). The choice is remembered, and it is the same switch as in Data results.

F7 and Shift+F7 move to the next and previous change, or use the arrows above the editor. Copy Diff puts the counts and both queries on the clipboard.

The editor shows the SQL as it was when you compared, not what the tab holds now. If you edit a source tab afterwards, a note says <tab> has changed since. with Compare again, which takes the tab’s SQL once more. If the tab is closed, the note says so and the SQL it had stays on screen.