Any clue where / why this error might happen - "Parser Error: syntax error at or near "-" "

[DanyC97]

Hi there,

Given the latest version of duckdb and dbt-core and using the below configuration, i'm bumping into an issue where the debug error message doesn't help at all where run dbt --debug docs generate

Note, if i have different targets of type bigquery and run dbt run it compiles and works as expected, no issues. Is just when using with duckdb ....

profile.yml

# This dbt profiles.yml config file.
dwh:
    docs: # dummy duckdb adapter. Currently this is a workaround to create documentation without connecting to a real DB (Ref:https://github.com/dbt-labs/dbt-core/issues/3947)
      path: /tmp/dbt.duckdb
      type: duckdb

in dbt_profile.yml i have defined a variable as below

vars:
   gcp_billing_datasets: ['project-bq-export.gcp_billing']

in the model sql file i have

{{ config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            "field": "usage_date",
            "data_type": "DATE",
            "granularity": "DAY"
        }
    )
}}

WITH billing_data AS (
    {%- for dataset in var("gcp_billing_datasets") -%}
        {% set dataset_query %}
            SELECT CONCAT("{{ dataset }}.", table_name) AS table_name
            FROM {{ dataset }}.INFORMATION_SCHEMA.TABLES
            WHERE table_name LIKE '%resource_v1%'
        {% endset %}

and the error is

09:45:46  DuckDB adapter: Error running SQL:

            SELECT CONCAT("project-bq-export.gcp_billing.", table_name) AS table_name
            FROM project-bq-export.gcp_billing.INFORMATION_SCHEMA.TABLES
            WHERE table_name LIKE '%resource_v1%'

09:45:46  DuckDB adapter: Rolling back transaction.
09:45:46  On model.dwh.dwh_gcp_billing: Close
09:45:46  Runtime Error in model dwh_gcp_billing (models/gcp/warehouse/dwh_gcp_billing.sql)
  Parser Error: syntax error at or near "-"

Now based on my understanding i don't need to provide the backticks to FROM{{ dataset }}.INFORMATION_SCHEMA.TABLES as i don't have any special chars. However i've tried to dig into your code as well as dbt-core ( i.e https://github.com/dbt-labs/dbt-core/tree/main/core/dbt/parser ) but couldn't find much.

Due to lack of more debug info i'm a bit in the dark, could the issue arises due to the way dbt handles string interpolation and how DuckDB interprets identifiers. ?

[DanyC97]

👋 @jwills , @guenp sorry to tag you directly. Any chance you might be familiar with the codebase, if so are you able to shed some light where i can start digging further?

thank you.

[jwills]

Mmm I think there are a few problems here.

1) You definitely need to add " around the {{ dataset }} variable in the model SQL; dbt doesn't do that for you for variables, only for relations it controls. 2) I don't understand why the resulting "project-bq-export.gcp_billing".INFORMATION_SCHEMA.TABLES relation exists-- what is project-bq-export.gcp_billing? Is it a DuckDB database? Something else?

[DanyC97]

hi @jwills , thank you for taking the time to respond

You definitely need to add " around the {{ dataset }} variable in the model SQL; dbt doesn't do that for you for variables, only for relations it controls.

ack, will do so

I don't understand why the resulting "project-bq-export.gcp_billing".INFORMATION_SCHEMA.TABLES relation exists-- what is project-bq-export.gcp_billing? Is it a DuckDB database? Something else?

this is not a duckDB db, is the

and the result is to build a dynamic list of tables which belongs to the dataset (which exist in a non US region) so we can loop over it

does that make sense?

[jwills]

@DanyC97 that does make sense, but I don't see how DuckDB would help with that problem since I assume you need to use BigQuery to read that data; dbt-duckdb doesn't support queries that are intended to run against BigQuery.

[DanyC97]

i understand, make sense.

Maybe a small help with be the error message return to provide a bit more meaningful info although i'm not sure if that is on dbt-core side or dbt-duckdb