Cannot reference attached DuckDB databases in sources.yml

[Phil-T1]

Hi,

I appreciate that this is probably something that I am missing, but I cannot see how it is possible to reference an attached DuckDB db in sources.yml?

profiles.yml:

attach:
  -path: "./attached_database.db"

I can successfully select from the attached database using the below without Jinja (double quotes must be included here): SELECT * FROM "attached_database"."attached_table"

However, if I use the following in sources.yml:

  - name: attached_source
    database: attached_database
    tables:
      - name: attached_table

Then SELECT * FROM {{ source('attached_source', 'attached_table') }} Compiles to and fails: SELECT * FROM "attached_source"."attached_database"."attached_table"

If I leave the database part out in sources.yml and use schema:

  - name: attached_source
    schema: attached_database
    tables:
      - name: attached_table

Then SELECT * FROM {{ source('attached_source', 'attached_table') }} Of course it compiles to (and fails): SELECT * FROM "main"."attached_database"."attached_table"

How do I correctly set sources.yml or correctly reference sources.yml to have a select compile to "attached_database"."attached_table"?

Thanks!

Phil.

[jwills]

You need to use both the "database" and the "schema" properties-- I'm assuming what you want is:

- name: attached_source
   database: attached_database
   schema: main

[jwills]

(If that doesn't work-- i.e., it does not then compile to "attached_database.main.attached_table", it's likely a bug)

[Phil-T1]

Thanks for the speedy response again Josh, much appreciated :)

I couldn't get it to work with your solution, but once you told me I should be aiming for compilation to: "attached_database"."main"."attached_table" then I managed to get it to work using:

sources:

  - name: attached_database
    schema: main
    database: attached_database
    tables:
      - name: attached_table

I had to try quite a few permutations to get this to succeed, is it worth updating the docs to give an example?

Thanks again,

Phil.

[jwills]

Ah gotcha— yeah, the list of tables in the source is a key part of the config. An example for the README would be most welcome!