SQLAlchemy Plugin usage

[trp-dan-oxlade]

Thanks for the excellent project!

I'm a total n00b to dbt and a bit lost, I got the basic setup working locally. I'm now trying to use SQLAlchemy as a plugin. I'm a bit confused about how I'm actually meant to use it.

my profile.yml looks like this:

dev:
  target: local
  outputs:
    local:
      type: duckdb
      path: /tmp/data.duckdb
      filesystems:
        - fs: s3
          anon: false
          key: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
          secret: "{{ env_var('AWS_SECRET_ACCESS_KEY') }}"
          client_kwargs:
            endpoint_url: "http://moto:7000"
      external_root: "s3://{{ env_var('S3_BUCKET') }}/data"
      threads: 4
      plugins:
        - module: sqlalchemy
          alias: sqla_db
          config:
            connection_url: "{{ env_var('SQLALCHEMY_URI') }}"

how would I then write SQL in my models to select from this? Is there some magic DuckDB query I execute, or do I need to configure dbt somehow to reference the sqla_db?

[jwills]

Hey Dan, thanks for trying this out! Happy to help here.

Plugins are mostly used for handling "special" sources in your dbt projects, which is any sort of data that requires you to execute some Python code in order to turn the source definition into something that DuckDB knows how to consume, which is usually a data frame. In the case of the SQLAlchemy plugin, you can configure sources in a YAML file like this example from the plugin tests:

version: 2
sources:
  - name: sql_source
    schema: main
    config:
      plugin: sql
      save_mode: ignore
    tables:
      - name: tt1
        description: "My first SQLAlchemy table"
        config:
          query: "SELECT * FROM {identifier} WHERE id=:id"
          params:
            id: 1
      - name: tt2
        config:
          table: "test_table2"

Here, we reference the plugin named sql in the config section of the source named sql_source, and we list the tables that are defined in the source with some per-table config information on how to query them. This listing is pretty dumb at the moment- each of the tables you want to query from the sqlalchemy database needs either a "query" or a "table" key in its config that gives the plugin instructions on how to fetch data from it.

Once this configuration is in place, you can refer to the tables in the source in your dbt models via the macro {{ source('sql_source', 'table_name') }}, just as you would for any other source in dbt.

If you ever need a hand, I hang out in the #db-duckdb channel in the dbt Slack and the #dbt channel in the DuckDB discord.

[trp-dan-oxlade]

awesome - thanks