`dbt run --empty` throws a syntax error while the model is ran correctly without the `--empty` flag

[ValBerthe]

• dbt-core version: 1.8.3
• dbt-duckdb version: 1.8.1

Hello,

dbt run --empty seems to throw a syntax error when dbt run works correctly.

dbt run --select my_model --empty
09:48:08  Running with dbt=1.8.3
09:48:08  Registered adapter: duckdb=1.8.1
09:48:08  Found 6 models, 1 seed, 1 operation, 9 data tests, 1 source, 1 exposure, 411 macros, 1 unit test
09:48:08  
09:48:09  
09:48:09  Running 1 on-run-start hook
09:48:09  1 of 1 START hook: my_project.on-run-start.0 .......................................... [RUN]
09:48:09  1 of 1 OK hook: my_project.on-run-start.0 ............................................. [OK in 0.00s]
09:48:09  
09:48:09  Concurrency: 1 threads (target='dev')
09:48:09  
09:48:09  1 of 1 START sql view model main.my_model ............................ [RUN]
09:48:09  1 of 1 ERROR creating sql view model main.my_model ................... [ERROR in 0.05s]
09:48:09  
09:48:09  Finished running 1 view model, 1 project hook in 0 hours 0 minutes and 0.82 seconds (0.82s).
09:48:09  
09:48:09  Completed with 1 error and 0 warnings:
09:48:09  
09:48:09    Runtime Error in model my_model (models/intermediate/my_model.sql)
  Parser Error: syntax error at or near "t"
09:48:09  
09:48:09  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Without the flag:

dbt run --select my_model
09:47:10  Running with dbt=1.8.3
09:47:10  Registered adapter: duckdb=1.8.1
09:47:10  Found 6 models, 1 seed, 1 operation, 9 data tests, 1 source, 1 exposure, 411 macros, 1 unit test
09:47:10  
09:47:11  
09:47:11  Running 1 on-run-start hook
09:47:11  1 of 1 START hook: my_project.on-run-start.0 .......................................... [RUN]
09:47:11  1 of 1 OK hook: my_project.on-run-start.0 ............................................. [OK in 0.00s]
09:47:11  
09:47:11  Concurrency: 1 threads (target='dev')
09:47:11  
09:47:11  1 of 1 START sql view model main.my_model ............................ [RUN]
09:47:12  1 of 1 OK created sql view model main.my_model ....................... [OK in 0.63s]
09:47:12  
09:47:12  Finished running 1 view model, 1 project hook in 0 hours 0 minutes and 1.43 seconds (1.43s).
09:47:12  
09:47:12  Completed successfully
09:47:12  
09:47:12  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

The model :

SELECT
	n.frame as FRAME_ID,
	n.frame_type,
	t.TARGET_MONTH,
	t.DAY,
	t.HOUR,
	0 AS impressions
FROM (
    SELECT DISTINCT
		frame,
		frame_type
		FROM {{ ref('int_raw') }}
) n
CROSS JOIN {{ ref('my_downstream_model') }} t

This prevents me from running unit tests in the CI.

Not sure if the issue is caused by dbt-duckdb's implementation of the --empty flag.

Glad to help resolve this issue!

[jwills]

yup, I can reproduce this-- figuring out what's going on, thanks for the report!

[jwills]

So this does not seem to be something I can override easily-- the workaround I cooked up is to use a CTE instead of an alias here, so your model needs to turn into something like this:

WITH n AS (
    SELECT DISTINCT
		frame,
		frame_type
		FROM {{ ref('int_raw') }}
),
t AS (
  SELECT TARGET_MONTH, DAY, HOUR
  FROM {{ ref('my_downstream_model') }}
)
SELECT n.frame as FRAME_ID,
	n.frame_type,
	t.TARGET_MONTH,
	t.DAY,
	t.HOUR,
	0 AS impressions
FROM n CROSS JOIN t

The issue here is that the --empty flag renders the {{ ref(...) }} and {{ source(...) }} macros in a way that isn't aware of the alias for the ref that you are adding after the fact (t in this case), which causes the compiled query to throw a syntax error b/c there are two aliases for the same relation right after each other (you can look in the target/compiled/ directory of your dbt project to see how that shakes out here.)

I'm going to file an issue upstream with the dbt-adapters folks to see how they want to handle this case going forward, but it's not something I can fix right now in the dbt-duckdb adapter itself AFAICT, so a workaround and a mental model of how this works is the best I can do for you at the moment. 🙇

[jwills]

Ah no it seems I'm wrong-- I think I can use the trick here dbt-labs/dbt-adapters#124 to fix this, going to put a PR together

[ValBerthe]

@jwills thanks a lot for the heads up! Will use the CTE workaround for now 😊