dbt-athena-community

Warnings

• breaking Adapter version must match `dbt-core` major version. `dbt-athena-community` versions are tightly coupled with `dbt-core`. A common footgun is upgrading `dbt-core` without upgrading the adapter.
  Fix: Always ensure `dbt-athena-community` and `dbt-core` major versions match (e.g., `dbt-core~=1.10.0` requires `dbt-athena-community~=1.10.0`). Check the adapter's `pyproject.toml` or documentation for exact compatibility.
• gotcha The `s3_staging_dir` in `profiles.yml` is a mandatory configuration. All queries executed by dbt-athena-community require this S3 path.
  Fix: Ensure the `s3_staging_dir` is correctly configured in your `profiles.yml` and that the AWS IAM role/credentials used have write permissions to this S3 location. Forgetting this or misconfiguring permissions is a very common setup error.
• gotcha Performance and cost considerations with `table` materialization on large datasets.
  Fix: While `view` materialization is common, using `table` materialization without proper partitioning, bucketing, or `iceberg` table strategies can lead to high Athena costs and slow build times. Consider incremental models and `iceberg` tables for production use cases with large data volumes.
• gotcha Confusion between `dbt-athena-community` and the official `dbt-athena` adapter.
  Fix: Users sometimes confuse `dbt-athena-community` with the official `dbt-athena` adapter maintained by dbt Labs (found in `dbt-adapters` repo). While `dbt-athena-community` is widely used and often preferred for its features, ensure you're installing and referencing the correct package. This registry entry specifically pertains to `dbt-athena-community`.
• gotcha Potential `boto3` and `pyathena` version conflicts with other libraries.
  Fix: `dbt-athena-community` depends on specific versions of `boto3` and `pyathena`. Conflicts with other libraries in the same Python environment requiring different versions of these can lead to runtime errors. It's often best practice to use a dedicated virtual environment for dbt projects to isolate dependencies.

Install

• pip install dbt-athena-community Install `dbt-athena-community`

Imports

• dbt CLI

  Interact via dbt CLI commands like 'dbt run' after configuring profiles.yml

  dbt adapters are primarily configured via YAML files (e.g., `profiles.yml`, `dbt_project.yml`) and used via the dbt CLI, not direct Python imports of adapter-specific classes for end-users.

Quickstart

This quickstart demonstrates how to configure `dbt-athena-community` using a `profiles.yml` file and run a sample dbt model. It creates temporary `profiles.yml` and `dbt_project.yml` files and then executes a `dbt run` command via `subprocess`. You need to replace placeholder AWS credentials/S3 path or ensure your environment has `~/.aws/credentials` configured. Ensure `dbt-core` is installed in your environment for the `dbt` CLI command to be found.

import os
import subprocess
import yaml
from pathlib import Path
import shutil

# Setup a temporary dbt profiles directory and project
temp_dbt_dir = Path("./temp_dbt_profiles")
temp_dbt_dir.mkdir(exist_ok=True)
profiles_path = temp_dbt_dir / "profiles.yml"

# Use environment variables for sensitive data or set placeholders
aws_access_key_id = os.environ.get("AWS_ACCESS_KEY_ID", "YOUR_ACCESS_KEY") # For IAM user
aws_secret_access_key = os.environ.get("AWS_SECRET_ACCESS_KEY", "YOUR_SECRET_KEY") # For IAM user
aws_session_token = os.environ.get("AWS_SESSION_TOKEN", "") # For temporary credentials
s3_staging_dir = os.environ.get("DBT_ATHENA_S3_STAGING_DIR", "s3://your-dbt-athena-bucket/staging/")
athena_workgroup = os.environ.get("DBT_ATHENA_WORKGROUP", "primary")
athena_database = os.environ.get("DBT_ATHENA_DATABASE", "dbt_athena_db")
aws_region = os.environ.get("AWS_REGION", "us-east-1")

profiles_content = {
    "my_athena_project": { # This name must match 'profile' in dbt_project.yml
        "target": "dev",
        "outputs": {
            "dev": {
                "type": "athena",
                "s3_staging_dir": s3_staging_dir,
                "database": athena_database,
                "schema": "dbt_schema",
                "region_name": aws_region,
                "work_group": athena_workgroup,
                # Authentication: Use one of the following methods
                "aws_profile_name": "default", # Uses ~/.aws/credentials profile
                # OR directly provide credentials (less secure for production)
                # "aws_access_key_id": aws_access_key_id,
                # "aws_secret_access_key": aws_secret_access_key,
                # "aws_session_token": aws_session_token, # Optional
                # Other common optional settings
                "poll_interval": 5, # Seconds between status checks
                "num_retries": 10,
                "threads": 4
            }
        }
    }
}

with open(profiles_path, "w") as f:
    yaml.dump(profiles_content, f, default_flow_style=False)

print(f"Profiles file created at: {profiles_path}")

# Create a minimal dbt project structure
project_dir = Path("./temp_dbt_project")
project_dir.mkdir(exist_ok=True)
(project_dir / "models").mkdir(exist_ok=True)

dbt_project_yml_content = f"""
name: 'my_athena_project'
version: '1.0.0'
config-version: 2

profile: 'my_athena_project'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"
clean-targets:
  - "target"
  - "dbt_packages"
  - "logs"

models:
  my_athena_project:
    +materialized: view
"""
with open(project_dir / "dbt_project.yml", "w") as f:
    f.write(dbt_project_yml_content)

# Create a sample model
model_sql_content = """
-- models/my_first_model.sql
SELECT 1 AS id, 'hello from dbt-athena' AS message
"""
with open(project_dir / "models" / "my_first_model.sql", "w") as f:
    f.write(model_sql_content)

print(f"dbt project created at: {project_dir}")

# Attempt to run dbt (this requires dbt-core to be installed in the environment)
try:
    print("\nAttempting to run dbt...")
    # Set DBT_PROFILES_DIR for the subprocess to use our temp profiles.yml
    env_vars = os.environ.copy()
    env_vars["DBT_PROFILES_DIR"] = str(temp_dbt_dir.resolve())
    
    result = subprocess.run(
        ["dbt", "run", "--project-dir", str(project_dir.resolve())],
        check=True,
        capture_output=True,
        text=True,
        env=env_vars
    )
    print("dbt run successful!")
    print(result.stdout)
except subprocess.CalledProcessError as e:
    print(f"dbt run failed with exit code {e.returncode}: {e}")
    print("Stdout:", e.stdout)
    print("Stderr:", e.stderr)
except FileNotFoundError:
    print("Error: 'dbt' command not found. Ensure dbt-core is installed (pip install dbt-core).")
finally:
    # Clean up temporary files/directories
    if temp_dbt_dir.exists():
        shutil.rmtree(temp_dbt_dir)
        print(f"Cleaned up {temp_dbt_dir}")
    if project_dir.exists():
        shutil.rmtree(project_dir)
        print(f"Cleaned up {project_dir}")