rocky-data / rocky-data/rocky

bug(engine): rocky snapshot cannot execute on DuckDB; the generated MERGE is a parser error

Open
#2,012 0 comments 0 reactions 0 assignees View on GitHub
bug engine
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.