dbt-duckdb

dbt-duckdb copied to clipboard

My model utilizes the read_json() function to fetch JSON from an API like this:

WITH source AS (
    SELECT *
    FROM read_json(
        'https://url/?parameter={{ var("parameter") }}', 
        auto_detect=true, format='auto', 
        maximum_object_size=104857600
    )
)

SELECT column
FROM source

The profile I use looks like this:

dbt_duckdb_small:
  target: "{{ env_var('DBT_TARGET', 'nonprod')}}"
  outputs:
    prod:
      type: duckdb
      use_credential_provider: aws
      settings:
          # Some S3 settings
      schema: "{{ env_var('DBT_TARGET_SCHEMA', 'schema_prod')}}"
    nonprod:
      type: duckdb
      use_credential_provider: aws
      settings:
          # Some S3 settings
      schema: "{{ env_var('DBT_TARGET_SCHEMA', 'schema_nonprod')}}"

Running the model with target prod works, but the second time I run it on target prod, I get the following error:

➜  dbt run -s <model> --profile=dbt_duckdb_small --target=prod
10:23:28  Running with dbt=1.8.7
10:23:29  Registered adapter: duckdb=1.9.1
10:23:29  Found 75 models, 36 sources, 785 macros
10:23:29  
10:23:29  Concurrency: 1 threads (target='prod')
10:23:29  
10:23:29  1 of 1 START sql external model <model> ............. [RUN]
10:24:44  1 of 1 ERROR creating sql external model <model> .... [ERROR in 75.46s]
10:24:44  
10:24:44  Finished running 1 external model in 0 hours 1 minutes and 15.59 seconds (75.59s).
10:24:44  
10:24:44  Completed with 1 error and 0 warnings:
10:24:44  
10:24:44    Runtime Error in model <model>
  Parser Error: syntax error at or near "["
10:24:44  
10:24:44  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Subsequent runs also fail. When I switch target to nonprod, the run succeeds again. When running afterwards with target nonprod, it fails again. It only seems to succeed on the run where I switch target. Am I missing something? The API endpoint is valid and doesn't contain malformed JSON.

I'm not super experienced with dbt or the dbt-duckdb adapter. If any more info is needed please ask.