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.
| id⏷ | name⏷ | value⏷ | category⏷ | |
|---|---|---|---|---|
| ~ | 80 | item_80 | 0 | |
| ~ | 60 | item_60 | 0 | |
| 3 | 86 | item_86 | 860 | 1 |
| − | 93 | item_93 | 930 | 3 |
| 5 | 82 | item_82 | 820 | 2 |
| − | 94 | item_94 | 940 | 4 |
| + | 102 | new_item_102 | 10200 | 0 |
| + | 105 | new_item_105 | 10500 | 0 |
Opening it
- ⌘⇧C in the editor, or ⌘K → Compare.
- Header More Actions menu → Compare.
- Right-click a table in the schema tree → Compare (it becomes the left source).
- 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 both sources.
Data
Pick a left and a right source from the same tree: Query Results (results from open tabs, kept for 24 hours) and every project → dataset → table. Search covers both.
Choosing key columns
Tick the Key Columns that identify a row. Only columns present in both sources with a scalar type qualify; STRUCT and ARRAY columns never do. Columns named id, *_id, *_key, uuid and similar are suggested, but nothing is selected for you — at least one key is required before Compare enables.
Compare without keys (row hash) hashes whole rows instead: you get added and removed rows only, never changed.
Narrowing the scan
- Partitioning — for partitioned tables, a date range per side or Same filter for both, with presets from Today to Last 90 days. A table that requires a partition filter gets the last year by default.
- Filters — WHERE conditions such as
status = 'active', ANDed together. They’re passed through as written; a bad expression fails when the comparison runs. - Excluded Columns — dropped from the comparison and from the results. Use it for
updated_atand other columns that differ on every write.
Which engine runs the comparison
The footer shows which engine will run and, for SQL, the dry-run bytes and cost:
| Engine | When | How |
|---|---|---|
| In-Memory | Both sides together ≤ 10,000 rows, or row counts unknown | Fetches up to 10,000 rows per side and diffs them in the browser. No extra BigQuery cost; partition filters are ignored |
| SQL | Larger | A FULL OUTER JOIN on the keys with IS DISTINCT FROM per column, run in BigQuery; only differences come back, so the Unchanged filter is empty |
In SQL mode STRUCT fields are flattened and compared one by one (metadata.name), ARRAY columns are compared as a whole by fingerprint, and NULL equals NULL. Open comparison SQL in new tab in the footer shows the generated query.
Reading the results
The bar reads 5 added | 10 removed | 9 changed; the buttons filter to All / Added / Removed / Changed / Unchanged. Rows are tinted green (added — right only), red (removed — left only) or amber (changed); a changed cell shows the old value struck through, then the new one. After a run, Estimate Cost / Run Comparison in the right panel re-run with new keys or filters.
Export offers CSV (with diff) — one row per difference with + − ~ markers — and Google Sheets with left and right columns side by side. The copy button puts the visible rows on the clipboard as TSV with the same markers.
Schema
Choose two tables — or two views, or two routines, via the icons in the search field — and Compare. 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.
| Column Name⏷ | Type⏷ | Mode⏷ | Description⏷ | |
|---|---|---|---|---|
| + | defect_type_key | STRING | NULLABLE | Surrogate key for the defect type dimension |
| + | classification.severity_level | INTEGER | NULLABLE | Numeric severity level (1=lowest to 5=highest) |
| ~ | description | STRING | Full description of what this defect type means | |
| − | category_key | STRING | REQUIRED | Primary key: Normalized category code |
Table Options Changed lists partitioning, clustering, description, labels and require partition filter as old → new. Copy as DDL turns the column differences into ALTER TABLE … ADD COLUMN / DROP COLUMN / ALTER COLUMN … SET DATA TYPE statements that would bring the left table to the right one. Views also get a diff of their definition; routines get only that.
Queries
Pick from Open Tabs or Paste SQL on each side. The result is two editors with line numbers, lines tinted green (added), red (removed) or amber (modified), with counts in the header and Copy Diff. Lines are matched by position, so an inserted line near the top shows as a run of modified lines below it — the badges count net additions and removals instead.