A VS Code extension for dbt. Open source under Apache 2.0, version 0.6.0

See what a dbt change did to the data, one CTE at a time.

Open a model’s diff in VS Code and click above any CTE. Preview its rows on either side, or compare both sides and get the rows only one of them returns. Queries run through dbt show, so Jinja, refs and your Snowflake profile work as in a real run.

Install from VS Code Marketplace Open VSX, for Cursor

⌘P ext install MarkPhamm.cte-diff

orders.sql (HEAD) ↔ orders.sqlPlaying

jaffle_shop’s orders model. One where in payments, and order_payments returns different totals without a changed character. Move your mouse over it to take control. Edit the queries in the playground.

Preview a CTE▶ Preview above payments
Compare both sides⇄ on order_payments: 3 rows changed
From the sidebar⇄ on final: a column added, one dropped

A diff shows what the SQL changed. It can’t show what the data did.

Checking it by hand means copying both versions into a worksheet, rendering the Jinja and writing the except yourself, CTE by CTE.

cte-diff marks every CTE that reads a changed one, and runs any CTE on both sides: from a diff, the sidebar, or two SQL files.

Try the change in the playground

From the demo above. Counts from running both sides on its sample data.
CTE	The SQL diff shows	The data did
orders	no change	same 8 rows
payments	`+ where status = 'success'`	3 of 12 rows gone
order_payments	no change	3 of 8 totals changed reads a changed CTE

Open a comparison from what you already have

A git change

Open the model from Source Control, then click ⇄ Compare a CTE… in the editor’s title bar.

Any two files

In the sidebar’s Compare Files, put one file in Old and one in New, then Open the diff. Good for a dev copy against prod’s.

Pasted SQL

Run CTE Diff: New Comparison. Paste the old query on the left and the new one on the right.

Read how to use it

How ⇄ Compare sides finds the rows that changed

It runs the CTE on both sides in one query, through dbt show, and keeps only the rows one side has. Here it is on order_payments from the demo.

1
Run the CTE on both sides

The old and the new order_payments, each with the CTEs it reads.

− old
order_id total_amount
11 18
14 32
52 45
87 18
+ new
order_id total_amount
11 18
14 24
52 30
87 9
2
Match rows by column name

Each row becomes text keyed by column name. Column order doesn’t matter, and a renamed or dropped column shows up as a difference, not an error.
- −{"order_id":11,"total_amount":18}
- −{"order_id":14,"total_amount":32}
- −{"order_id":52,"total_amount":45}
- −{"order_id":87,"total_amount":18}
- +{"order_id":11,"total_amount":18}
- +{"order_id":14,"total_amount":24}
- +{"order_id":52,"total_amount":30}
- +{"order_id":87,"total_amount":9}
3
Keep what only one side has

Rows on both sides drop out. What’s left is the change: each old row above its new one, matched on order_id.

order_id total_amount
3 changed rows
− 14 32
+ 14 24
− 52 45
+ 52 30
− 87 18
+ 87 9

− old
order_id	total_amount
11	18
14	32
52	45
87	18

+ new
order_id	total_amount
11	18
14	24
52	30
87	9

	order_id	total_amount
3 changed rows
−	14	32
+	14	24
−	52	45
+	52	30
−	87	18
+	87	9

Snowflake or DuckDB, one query, one dbt show run. It compares distinct rows: if only duplicates changed, the row counts differ but no rows are listed. Read how the comparison works

Before you install

dbt: dbt Fusion, or dbt-core 1.5 or later, with a working profile. Key-pair, SSO and password logins all work. A project .env is loaded first.
Warehouse: Previews work on any warehouse dbt supports. Comparisons run on Snowflake and DuckDB.
Workspace: A trusted one: dbt runs the project’s macros.

Install cte-diff and open your next review