How do I see which rows changed between two versions of a table?

Recipe verified on 2026-10-29 Ran on a private test instance

Short answer

Read both versions at once with AS OF ALLVERSIONS BETWEEN the two dates, group by the row's key, and keep the keys whose values differ (MIN(x) <> MAX(x)) or that appear in only one version (COUNT(*) < 2). The first are changed rows, the second added or removed ones.

Steps

Connect your assistant to your instance (this writes, so it needs your own instance with a ReadWrite key from the accounts portal; the public demo is read-only). In Claude Code:

claude mcp add --transport http mcpdata https://[you].mcpdata.org/mcp --header "Authorization: Bearer $CDB_API_KEY"

Replace [you] with your instance's name. Other apps: Connect your AI.

Load the daily files as a history. The sample positions/book folder, one file per day:

cdb-localdata add ./samples/positions --to https://[you].mcpdata.org --namespace changes --name changes
cdb-localdata sync

Find the changed, added and removed rows.

import cdb
s = cdb.Session("https://[you].mcpdata.org")
window = "AS OF ALLVERSIONS BETWEEN #2026-10-01# AND #2026-10-02#"
changed = s.execute_sql(f"SELECT position_id, MIN(quantity) AS low, MAX(quantity) AS high FROM changes.book {window} "
                        "GROUP BY position_id HAVING low <> high").to_dataframe()
one_day = s.execute_sql(f"SELECT position_id, COUNT(*) AS days FROM changes.book {window} "
                        "GROUP BY position_id HAVING days < 2").to_dataframe()
print(len(changed), "positions changed quantity, e.g.:")
print(changed.head(5).to_string(index=False))
print(len(one_day), "positions are in only one of the two days:", ", ".join(sorted(one_day["position_id"])))

Ask your question. Give your AI this prompt:

Using mcpdata, save a view of the positions in changes.book whose quantity changed between 2026-10-01 and 2026-10-02, and report it.

What your AI does: the tool calls it makes for this prompt, and what came back

create_view(name="changes.quantity_changed", select_sql="SELECT position_id, MIN(quantity) AS low, MAX(quantity) AS high FROM changes.book AS OF ALLVERSIONS BETWEEN #2026-10-01# AND #2026-10-02# GROUP BY position_id HAVING low <> high") → created true, view changes.quantity_changed

create_report(name="Positions whose quantity changed", view="changes.quantity_changed") → report_id 9e00d851a13c4f80addd89039796a474

run_report(report_id="9e00d851a13c4f80addd89039796a474") → status ok, rows 25

Output of the steps above, from the last run

25 positions changed quantity, e.g.:
position_id    low   high
      P0159 -45830 -45590
      P0028  39510  40000
      P0076  23380  23450
      P0161 -31780 -31710
      P0136 -41890 -41650
3 positions are in only one of the two days: P0018, P0064, P0195

The assistant needs mcpdata connected (step 1) to run the query.

Comparing two exports by eye or in a spreadsheet misses things. With both days as versions of one table, the difference is a GROUP BY: every key is checked, and the result is just the rows that moved.

How do I see which rows changed between two versions of a table?

Steps

Why this works

Related questions