Diffing DuckDB branches and snapshots with plain SQL

Which rows changed on a branch, and which columns? Four queries, from a two-line EXCEPT to a reusable cell_diff macro. After the launch of the differential DuckDB v2 extension, a user asked: Would it also be possible to build diff views? Maybe it’s as simple as running a few SQL queries across different snapshots. It is that simple. differential doesn’t need a diff function, because every branch and every snapshot can be attached as an ordinary DuckDB catalog, side by side in one session. From there, a diff is a SQL query across two catalogs. This post builds that query up in four steps, explains how each one works, and ends with a macro you can reuse for any table. ApproachNeeds a keyShowsBest forEXCEPT both waysNoRows that exist on only one sideQuick checks, tables without a keyFULL JOIN on the keyYesEach row as added, removed or changedChange reportsChanged columnsYesWhich columns of a row changedNarrow tablesUnpivot + joinYesOne row per changed valueWide tables, a reusable macro…