bug(engine): rocky snapshot cannot execute on DuckDB; the generated MERGE is a parser error
- Dominant language
- Rust
- Stars
- 298
- Forks
- 18
- Avg merge
- 8h 7m
- Merged PRs (30d)
- 327
Description
`rocky snapshot` without `--dry-run` fails on the DuckDB adapter. The `engine/examples/snapshot` example therefore works only with `--dry-run`. Reproduced on engine 1.74.0.
There are three failures. The third one blocks the run even after you work around the first two.
## 1. The example's catalog `main` does not exist on DuckDB
The example sets `catalog = "main"` for source and target, and `[adapter.local]` has no `path`.
```bash
cd engine/examples/snapshot
rocky snapshot
```
```
Error: initial load failed: DuckDB error: Catalog Error: Catalog with name main does not exist!
```
An in-memory DuckDB names its catalog `memory`. A file database takes its catalog name from the file name, and DuckDB renames a file called `main.duckdb` to `main_db`. So no DuckDB database has the catalog `main`.
## 2. `initial_load` does not create the target schema
Work around (1): add `path = "warehouse.duckdb"` under `[adapter.local]`, set both `catalog = "warehouse"`, and seed the source.
```bash
duckdb warehouse.duckdb "CREATE SCHEMA raw; CREATE TABLE raw.customers AS SELECT 1 AS customer_id, 'a' AS name, TIMESTAMP '2026-01-01' AS updated_at;"
rocky snapshot
```
```
Error: initial load failed: DuckDB error: Catalog Error: Schema with name history does not exist!
```
## 3. `merge_1` emits `INSERT (*) VALUES (source.*, ...)`, which DuckDB rejects
Work around (2): `duckdb warehouse.duckdb "CREATE SCHEMA history;"`, then run again.
```
ERROR snapshot step failed step=merge_1 error=DuckDB error: Parser Error: syntax error at or near "*"
LINE 1: ...IMESTAMP, is_current = FALSE WHEN NOT MATCHED THEN INSERT (*) VALUES (source.*, CURRENT_TIMESTAMP, CAST(NULL AS TIMESTAMP...
^
Error: snapshot step failed: DuckDB error: Parser Error: syntax error at or near "*"
```
`initial_load` has already run at this point, so the failed run leaves an empty `history.customers_history` behind.
The limitation is known in the test suite. `engine/crates/rocky-duckdb/tests/scd2_null_safe.rs` records that DuckDB's MERGE does not accept `INSERT (*) VALUES (source.*, ...)`, and tests the change predicate through a join and an UPDATE instead. The DuckDB golden file `engine/crates/rocky-cli/tests/ir-golden/12-snapshot-scd2/duckdb.sql` pins the same statement.
Not verified: `merge_3` (`UPDATE ... WHERE ... NOT EXISTS (SELECT 1 FROM .raw.customers AS source WHERE target.customer_id = source.customer_id)`) names a `target` alias it does not declare. It may fail too once `merge_1` passes.
## Expected
Either `rocky snapshot` emits SQL that DuckDB executes, or the DuckDB adapter refuses a snapshot pipeline with a clear message before it creates the target table.
The `engine/examples/snapshot` README now says the example is dry-run only on DuckDB, and links here.
Contributor guide
Research direction
Start with engine/crates/rocky-duckdb/tests/scd2_null_safe.rs and engine/crates/rocky-cli/tests/ir-golden/12-snapshot-scd2/duckdb.sql, then reproduce the command in engine/examples/snapshot. Trace the DuckDB snapshot SQL generation, including initial_load, merge_1, and the unverified merge_3 path. Done means the example executes on DuckDB or rejects the pipeline clearly before creating the target table, with corresponding tests or golden output updated.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- data-engineering, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100