DOCSEARCHbyAlgolia
Submit Docs

dbt-athena Adapter

website·Indexed 30 days ago

https://dbt-athena.github.io

A community-owned adapter for dbt Core that enables dbt to work with Amazon Athena. It supports Hive and Apache Iceberg table materializations, dbt snapshots, seeds, and AWS Lake Formation tags management. The documentation covers installation, IAM permissions, model configuration, and handling Athena service API call quotas.

Tokens
5.7K
Snippets
29
Records
44
Agent score
73%

What's inside dbt-athena

  1. Overview of dbt-athena adapter

    dbt-athena is a community-owned adapter for dbt Core that enables dbt to work with AWS Athena. It transforms data using SQL by leveraging Athena's CREATE TABLE AS and CREATE VIEW queries.

  3. Migrate Hive snapshots to dbt-core standards in dbt-athena 1.5

    dbt-athena v1.5 aligns Hive snapshots with dbt-core standards, which introduces breaking changes to existing snapshots. Migration is performed manually per snapshot using queries generated by the dbt CLI.

    Migration Steps:

    1. Install dbt-athena>=1.5.
    2. Run dbt snapshot SNAPSHOT_NAME for a specific snapshot. The command will fail and print a migration query in the stdout.
    3. Copy the provided migration query (excluding log timestamps) and execute it in Athena. The query typically:
      • Backs up the original table.
      • Transforms data into a temporary table.
      • Drops the original table.
      • Copies temporary data back to the original table.
      • Drops the temporary table.
    4. Verify the migrated table with sanity checks.
    5. Run dbt snapshot SNAPSHOT_NAME again to confirm the error is resolved.
    6. Repeat for all snapshots.
    7. Once verified over several runs, delete the backup tables created during the process.
    dbt snapshot SNAPSHOT_NAME

  4. Prepare AWS resources for dbt-athena

    To use dbt-athena, you must have an S3 bucket (e.g., my-bucket) to store data and an Athena database. While dbt will attempt to create the database automatically if it does not exist, you can manually create one using SQL or manage it via AWS Glue.
    CREATE DATABASE IF NOT EXISTS analytics_dev
COMMENT 'Analytics models generated by dbt (development)'
LOCATION 's3://my-bucket/'
WITH DBPROPERTIES ('creator'='Foo Bar', 'email'='foo@bar.com');

  5. Implement incremental table strategies

    dbt-athena supports three incremental strategies for updating tables:

    • insert_overwrite (Default): Deletes overlapping partitions and inserts new records. This requires the partitioned_by configuration; otherwise, it falls back to append.
    • append: Inserts new records without modifying existing data. Useful for logs or historical data where duplicates are acceptable.
    • merge: Conditionally updates, deletes, or inserts rows. This strategy is only available for Iceberg tables and must be used with a unique_key.
    {{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id',
    table_type='iceberg'
) }}

  6. Load CSV files into Athena using dbt seeds

    Seeds are CSV files located within your dbt project (typically in the seeds directory) that can be loaded into your Athena data warehouse. To load these files, use the dbt seed command.
    dbt seed

  7. Create and activate a Python virtual environment for dbt

    Use the built-in venv module to create an isolated environment for dbt. The environment must be reactivated every time a new shell window or session is opened.
    python3 -m venv dbt-env             # create the environment
source dbt-env/bin/activate         # activate the environment for Mac and Linux
dbt-env\Scripts\activate            # activate the environment for Windows

  8. Contribute to the dbt-athena community project

    The dbt-athena adapter is a community-owned open-source project on GitHub. Contributions are welcomed to ensure the adapter remains sustainable and robust. To get started with contributing, you can set up the project for local development or join the dbt Community Slack in the #db-athena channel to collaborate with other maintainers and users.

  10. Install dbt-athena from a specific Git branch

    To test a specific branch of dbt-athena, add the Git URL with the branch name (e.g., @main) and the package name fragment (#dbt-athena-community) to your requirements.txt file.
    # requirements.txt
git+https://github.com/dbt-athena/dbt-athena.git@main#dbt-athena-community
    pip install -r requirements.txt

  11. Install dbt-athena from a local directory for debugging

    To debug issues or add logs, install dbt-athena in editable mode by referencing the local path in your requirements.txt file, then install via pip.
    # requirements.txt
-e /Users/username/Code/dbt-athena
    pip install -r requirements.txt

  12. Implement dbt snapshots in dbt-athena

    The dbt-athena adapter supports dbt snapshots for both Hive and Iceberg tables. To implement a snapshot, create a .sql file in the snapshots directory of your dbt project. The adapter supports the standard dbt timestamp and check strategies, as well as the option to invalidate hard deletes.