{"repo":"duckdb/dbt-duckdb","free":true,"listed":false,"github":"https://github.com/duckdb/dbt-duckdb","clone":"git clone https://github.com/duckdb/dbt-duckdb.git","description":"dbt adapter for DuckDB","language":"Python","stars":1334,"topics":["dbt","duckdb"],"license":"Apache-2.0","category":"data_transformation_tool","readme_excerpt":"## dbt-duckdb\n\n[DuckDB](http://duckdb.org) is an embedded database, similar to SQLite, but designed for OLAP-style analytics.\nIt is crazy fast and allows you to read and write data stored in CSV, JSON, and Parquet files directly, without requiring you to load\nthem into the database first.\n\n[dbt](http://getdbt.com) is the best way to manage a collection of data transformations written in SQL or Python for analytics\nand data science. `dbt-duckdb` is the project that ties DuckDB and dbt together, allowing you to create a [Modern Data Stack In\nA Box](https://duckdb.org/2022/10/12/modern-data-stack-in-a-box.html) or a simple and powerful data lakehouse with Python.\n\n### Installation\n\nThis project is hosted on PyPI, so you should be able to install it and the necessary dependencies via:\n\n`pip3 install dbt-duckdb`\n\nThe latest supported version targets `dbt-core` versions >= 1.8.x and `duckdb` version >= 1.0.0, but we work hard to ensure that newer\nversions of DuckDB will continue to work with the adapter as they are released.\n\n### Configuring Your Profile\n\nA super-minimal dbt-duckdb profile only needs *one* setting:\n\n````\ndefault:\n  outputs:\n    dev:\n      type: duckdb\n  target: dev\n````\n\nThis will run your dbt-duckdb pipeline against an in-memory DuckDB database that will not be persisted after your run completes. This may\nnot seem very useful at first, but it turns out to be a powerful tool for a) testing out data pipelines, either locally or in CI jobs and\nb) running data pipelines that operate purely on external CSV, Parquet, or JSON files. More details on how to work with external data files\nin dbt-duckdb are provided in the docs on [reading and writing external files](#reading-and-writing-external-files).\n\nTo have your dbt pipeline persist relations in a DuckDB file, set the `path` field in your profile to the path\nof the DuckDB file that you would like to read and write on your local filesystem. (For in-memory pipelines, the `path`\nis automatically set to the special value `:memory:`). By default, the `path` is relative to your `profiles.yml` file location.\nIf the database doesn't exist at the specified `path`, DuckDB will automatically create it.\n\n`dbt-duckdb` also supports common profile fields like `schema` and `threads`, but the `database` property is special: its value is automatically set\nto the basename of the file in the `path` argument with the suffix removed. For example, if the `path` is `/tmp/a/dbfile.duckdb`, the `database`\nfield will be set to `dbfile`. If you are running in in-memory mode, then the `database` property will be automatically set to `memory`.\n\n#### Persisting dbt Docs as Comments\n\ndbt model and column descriptions are not persisted to the database by default. If your project documents models in schema YAML files, enable\n`persist_docs` so dbt-duckdb writes those descriptions as DuckDB relation and column comments:\n\n```\nmodels:\n  +persist_docs:\n    relation: true\n    columns: true\n```\n\nPersisted comments are useful context for anyone exploring the database. They also help AI agents and other automated tools understand table\npurpose, column meaning, and expected grain before they generate queries.\n\n#### Using MotherDuck\n\nAs of `dbt-duckdb` 1.5.2, you can connect to a DuckDB instance running on [MotherDuck](http://www.motherduck.com) by setting your `path` to use a [md:<database> connection string](https://motherduck.com/docs/getting-started/connect-query-from-python/installation-authentication), just as you would with the DuckDB CLI\nor the Python API.\n\nMotherDuck databases generally work the same way as local DuckDB databases from the perspective of dbt, but\nthere are a [few differences to be aware of](https://motherduck.com/docs/architecture-and-capabilities#considerations-and-limitations):\n1. MotherDuck is compatible with client DuckDB versions 0.10.2 and newer.\n1. MotherDuck preloads a set of the most common DuckDB extensions for you, but does not support loading custom extensions or user-defined functions.\n\nAs of `dbt-duckdb` 1.9.6, you can also connect to a DuckDB instance running [hosted DuckLake on MotherDuck](https://motherduck.com/blog/ducklake-motherduck/) by creating a DuckLake on MotherDuck and then setting `is_ducklake: true` in your `profiles.yml`.\n\n```sql\n-- to use create your own database in MotherDuck first\nCREATE DATABASE my_ducklake\n  (TYPE ducklake, DATA_PATH 's3://...')\n```\n\nAn example profile is shown below under \"Attaching Additional Databases\". DuckLake must be identified so that safe DDL operations are applied by dbt.\n\n#### DuckDB Extensions, Settings, and Filesystems\n\nYou can install and load any core [DuckDB extensions](https://duckdb.org/docs/extensions/overview) by listing them in\nthe `extensions` field in your profile as a string. You can also set any additional [DuckDB configuration options](https://duckdb.org/docs/sql/configuration)\nvia the `settings` field, including options that are supported in the loaded extensions. You can also configure extensions from outside of the core\nextension repository (e.g., a community extension) by configuring the extension as a `name`/`repo` pair:\n\n```\ndefault:\n  outputs:\n    dev:\n      type: duckdb\n      path: /tmp/dbt.duckdb\n      extensions:\n        - httpfs\n        - parquet\n        - name: h3\n          repo: community\n        - name: uc_catalog\n          repo: core_nightly\n  target: dev\n```\n\nTo use the [DuckDB Secrets Manager](https://duckdb.org/docs/configuration/secrets_manager.html), you can use the `secrets` field. For example, to be able to connect to S3 and read/write\nParquet files using an AWS access key and secret, your profile would look something like this:\n\n```\ndefault:\n  outputs:\n    dev:\n      type: duckdb\n      path: /tmp/dbt.duckdb\n      extensions:\n        - httpfs\n        - parquet\n      secrets:\n        - type: s3\n          region: my-aws-region\n          key_id: \"{{ env_var('S3_ACCESS_KEY_ID') }}\"\n          secret: \"{{ env_var('S3_SECRET_ACCESS_KEY') }}\"\n  target: dev\n```\n\nAs of version `1.4.1`, we have added (experimental!) support for DuckDB's (experimental!) support for filesystems\nimplemented via [fsspec](https://duckdb.org/docs/guides/python/filesystems.html). The `fsspec` library provides\nsupport for reading and writing files from a [variety of cloud data storage systems](https://filesystem-spec.readthedocs.io/en/latest/api.html#other-known-implementations)\nincluding S3, GCS, and Azure Blob Storage. You can configure a list of fsspec-compatible implementations for use with your dbt-duckdb project by installing the relevant Python modules\nand configuring your profile like so:\n\n```\ndefault:\n  outputs:\n    dev:\n      type: duckdb\n      path: /tmp/dbt.duckdb\n      filesystems:\n        - fs: s3\n          anon: false\n          key: \"{{ env_var('S3_ACCESS_KEY_ID') }}\"\n          secret: \"{{ env_var('S3_SECRET_ACCESS_KEY') }}\"\n          client_kwargs:\n            endpoint_url: \"http://localhost:4566\"\n  target: dev\n```\n\nHere, the `filesystems` property takes a list of configurations, where each entry must have a property named `fs` that indicates which `fsspec` protocol\nto load (so `s3`, `gcs`, `abfs`, etc.) and then an arbitrary set of other key-value pairs that are used to configure the `fsspec` implementation. You can see a simple example project that\nillustrates the usage of this feature to connect to a Localstack instance running S3 from dbt-duckdb [here](https://github.com/jwills/s3-demo).\n\n#### Fetching credentials from context\n\nInstead of specifying the credentials through the settings block, you can also use the `CREDENTIAL_CHAIN` secret provider. This means that you can use any supported mechanism from AWS to obtain credentials (e.g., web identity tokens). You can read more about the secret providers [here](https://duckdb.org/docs/configuration/secrets_manager.html#secret-providers). To use the `CREDENTIAL_CHAIN` provider and automatically fetch credentials from AWS, specify the `provider` in the `secrets` key:\n\n```\ndefault:\n  outputs:\n    dev:\n      type: duckdb\n      path: /tmp/dbt.duckdb\n      extensions:\n        - httpfs\n        - parquet\n      secrets:\n        - type: s3\n          provider: credential_chain\n  target: dev\n```\n\n#### Scoped credentials by storage prefix\n\nSecrets can be scoped, such that different storage path can use different credentials.\n\n```\ndefault:\n  outputs:\n    dev:\n      type: duckdb\n      path: /tmp/dbt.duckdb\n      extensions:\n        - httpfs\n        - parquet\n      secrets:\n        - type: s3\n          provider: credential_chain\n          scope: [ \"s3://bucket-in-eu-region\", \"s3://bucket-2-in-eu-region\" ]\n          region: \"eu-central-1\"\n        - type: s3\n          region: us-west-2\n          scope: \"s3://bucket-in-us-region\"\n```\n\nWhen fetching a secret for a path, the secret scopes are compared to the path, returning the matching secret for the path. In the case of multiple matching secrets, the longest prefix is chosen.\n\n#### Attaching Additional Databases\n\nDuckDB supports [attaching additional databases](https://duckdb.org/docs/sql/statements/attach.html) to your dbt-duckdb run so that you can read\nand write from multiple databases. Additional databases may be configured via the `attach` argument\nin your profile that was added in dbt-duckdb `1.4.0`:\n\n```\ndefault:\n  outputs:\n    dev:\n      type: duckdb\n      path: /tmp/dbt.duckdb\n      attach:\n        - path: /tmp/other.duckdb\n        - path: ./yet/another.duckdb\n          alias: yet_another\n        - path: s3://yep/even/this/works.duckdb\n          read_only: true\n        - path: sqlite.db\n          type: sqlite\n        - path: postgresql://username@hostname/dbname\n          type: postgres\n        # Using the options dict for arbitrary ATTACH options\n        - path: /tmp/special.duckdb\n          options:\n            cache_size: 1GB\n            threads: 4\n            enable_fsst: true\n```\n\nFor DuckLake, use `ducklake:` for local; for MotherDuck-managed DuckLake use `md:` with `is_ducklake: true`.\n\n```yaml\na","default_branch":"master","files":150,"tree":[".flake8",".github/dependabot.yml",".github/workflows/main.yml",".github/workflows/nightly.yml",".github/workflows/release.yml",".gitignore",".pre-commit-config.yaml","CHANGELOG.md","CONTRIBUTING.md","LICENSE","MANIFEST.in","README.md","dbt/__init__.py","dbt/adapters/__init__.py","dbt/adapters/duckdb/__init__.py","dbt/adapters/duckdb/__version__.py","dbt/adapters/duckdb/cli.py","dbt/adapters/duckdb/column.py","dbt/adapters/duckdb/connections.py","dbt/adapters/duckdb/constants.py","dbt/adapters/duckdb/credentials.py","dbt/adapters/duckdb/environments/__init__.py","dbt/adapters/duckdb/environments/buenavista.py","dbt/adapters/duckdb/environments/local.py","dbt/adapters/duckdb/environments/motherduck.py","dbt/adapters/duckdb/impl.py","dbt/adapters/duckdb/plugins/__init__.py","dbt/adapters/duckdb/plugins/delta.py","dbt/adapters/duckdb/plugins/excel.py","dbt/adapters/duckdb/plugins/glue.py","dbt/adapters/duckdb/plugins/gsheet.py","dbt/adapters/duckdb/plugins/iceberg.py","dbt/adapters/duckdb/plugins/motherduck.py","dbt/adapters/duckdb/plugins/pd_utils.py","dbt/adapters/duckdb/plugins/postgres.py","dbt/adapters/duckdb/plugins/sqlalchemy.py","dbt/adapters/duckdb/relation.py","dbt/adapters/duckdb/secrets.py","dbt/adapters/duckdb/utils.py","dbt/include/__init__.py","dbt/include/duckdb/__init__.py","dbt/include/duckdb/dbt_project.yml","dbt/include/duckdb/macros/adapters.sql","dbt/include/duckdb/macros/catalog.sql","dbt/include/duckdb/macros/columns.sql","dbt/include/duckdb/macros/materializations/external.sql","dbt/include/duckdb/macros/materializations/hooks.sql","dbt/include/duckdb/macros/materializations/incremental.sql","dbt/include/duckdb/macros/materializations/incremental_strategy/delete_insert.sql","dbt/include/duckdb/macros/materializations/incremental_strategy/merge.sql","dbt/include/duckdb/macros/materializations/incremental_strategy/merge_config_validation.sql","dbt/include/duckdb/macros/materializations/incremental_strategy/merge_defaults.sql","dbt/include/duckdb/macros/materializations/incremental_strategy/microbatch.sql","dbt/include/duckdb/macros/materializations/incremental_strategy/validation_helper.sql","dbt/include/duckdb/macros/materializations/table.sql","dbt/include/duckdb/macros/materializations/table_function.sql","dbt/include/duckdb/macros/persist_docs.sql","dbt/include/duckdb/macros/seed.sql","dbt/include/duckdb/macros/snapshot_helper.sql","dbt/include/duckdb/macros/utils/any_value.sql","dbt/include/duckdb/macros/utils/date.sql","dbt/include/duckdb/macros/utils/dateadd.sql","dbt/include/duckdb/macros/utils/datediff.sql","dbt/include/duckdb/macros/utils/external_location.sql","dbt/include/duckdb/macros/utils/generate_series.sql","dbt/include/duckdb/macros/utils/lastday.sql","dbt/include/duckdb/macros/utils/listagg.sql","dbt/include/duckdb/macros/utils/normalize.sql","dbt/include/duckdb/macros/utils/splitpart.sql","dbt/include/duckdb/macros/utils/upstream.sql","dbt/include/duckdb/sample_profiles.yml","dev-requirements.txt","mypy.ini","pytest.ini","scripts/build-dist.sh","setup.cfg","setup.py","tests/__init__.py","tests/bv_test_server.py","tests/conftest.py","tests/create_function_plugin.py","tests/data/excel_file.xlsx","tests/ducklake.py","tests/functional/adapter/aliases/test_aliases.py","tests/functional/adapter/indexes/__init__.py","tests/functional/adapter/indexes/fixtures.py","tests/functional/adapter/indexes/test_indexes.py","tests/functional/adapter/simple_seed/test_fast_seed.py","tests/functional/adapter/store_test_failures_tests/test_store_test_failures.py","tests/functional/adapter/test_attach.py","tests/functional/adapter/test_basic.py","tests/functional/adapter/test_caching.py","tests/functional/adapter/test_changing_relation_type.py","tests/functional/adapter/test_community_extensions.py","tests/functional/adapter/test_concurrency.py","tests/functional/adapter/test_constraints.py","tests/functional/adapter/test_database_type.py","tests/functional/adapter/test_drop_relation.py","tests/functional/adapter/test_ducklake_features.py","tests/functional/adapter/test_ducklake_partitioned_by.py","tests/functional/adapter/test_ducklake_partitioned_by_integration.py","tests/functional/adapter/test_ducklake_sorted_by.py","tests/functional/adapter/test_ducklake_sorted_by_integration.py","tests/functional/adapter/test_empty.py","tests/functional/adapter/test_ephemeral.py","tests/functional/adapter/test_external.py","tests/functional/adapter/test_get_columns_in_relation.py","tests/functional/adapter/test_hooks.py","tests/functional/adapter/test_incremental.py","tests/functional/adapter/test_incremental_microbatch.py","tests/functional/adapter/test_persist_docs.py","tests/functional/adapter/test_python_model.py","tests/functional/adapter/test_rematerialize.py","tests/functional/adapter/test_simple_snapshot.py","tests/functional/adapter/test_sources.py","tests/functional/adapter/test_table_function.py","tests/functional/adapter/test_unit_testing.py","tests/functional/adapter/test_write_options.py","tests/functional/adapter/utils/test_date_spine.py","tests/functional/adapter/utils/test_utils.py","tests/functional/fsspec/test_filesystems.py","tests/functional/plugins/motherduck/fixtures.py","tests/functional/plugins/motherduck/test_macros.py","tests/functional/plugins/motherduck/test_motherduck.py","tests/functional/plugins/motherduck/test_motherduck_attach.py","tests/functional/plugins/motherduck/test_motherduck_ducklake.py","tests/functional/plugins/motherduck/test_motherduck_saas_mode.py","tests/functional/plugins/motherduck/test_motherduck_write_conflict.py","tests/functional/plugins/test_delta.py","tests/functional/plugins/test_excel.py","tests/functional/plugins/test_glue.py","tests/functional/plugins/test_gsheet.py","tests/functional/plugins/test_iceberg.py","tests/functional/plugins/test_mysql.py","tests/functional/plugins/test_plugins.py","tests/functional/plugins/test_postgres.py","tests/functional/plugins/test_sqlite.py","tests/unit/__init__.py","tests/unit/test_column.py","tests/unit/test_credentials.py","tests/unit/test_data_path_quoting.py","tests/unit/test_duckdb_adapter.py","tests/unit/test_escape_sql.py","tests/unit/test_external_utils.py","tests/unit/test_get_column_schema.py","tests/unit/test_glue.py","tests/unit/test_retries_connect.py","tests/unit/test_retries_query.py","tests/unit/utils.py","tox.ini"],"storefront":"/r/duckdb","claimed":false,"request_supported":{"post":"https://gitbuyer.com/r/duckdb/dbt-duckdb/request-supported","requests":0},"note":"indexed from public GitHub; nothing is for sale on this page. Clone it from GitHub. Paid listings live at /search."}