AMAZON ATHENA

CONNECT WITH ACCESS KEYS — THE COMMON CASE

One output in profiles.yml is all it takes. Three things are always required — the region, the Athena database, and an S3 path for query results — plus whatever credentials you use. This is a complete, working profile with an access-key pair:

# ~/.dbt/profiles.yml
my_project:
  target: athena_dev
  outputs:
    athena_dev:
      type: athena
      region_name: us-east-1
      schema: analytics                       # the Athena database
      s3_staging_dir: s3://my-bucket/athena-results/
      aws_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
      aws_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY') }}"
      threads: 4

Run dvt debug and DVT tests the connection on both of its layers — the dbt layer that builds models and the Sling layer that moves data. There is no server to install and nothing to run locally; dvt sync reads this profile and installs the Athena driver, and the rest is IAM: the identity you connect as needs Athena query rights, read access to the Glue catalog, and read/write on the S3 buckets involved.

IAM ROLE OR ENVIRONMENT — NO KEYS IN THE PROFILE

Leave the key fields out entirely and DVT falls back to the standard AWS credential chain: environment variables, ~/.aws/credentials, SSO, or the instance/task role when you're running on EC2, ECS or Lambda. The cleanest profile is the one with no secrets in it:

athena_iam:
  type: athena
  region_name: us-east-1
  schema: analytics
  s3_staging_dir: s3://my-bucket/athena-results/

Prefer a specific entry from ~/.aws/credentials? Name it with aws_profile_name:

athena_named:
  type: athena
  region_name: us-east-1
  schema: analytics
  s3_staging_dir: s3://my-bucket/athena-results/
  aws_profile_name: data-team               # entry in ~/.aws/credentials

TEMPORARY CREDENTIALS (STS)

Credentials from sts assume-role, SSO sessions, or federated logins come as a trio — key, secret, and a session token. Pass all three and DVT hands them through together:

athena_sts:
  type: athena
  region_name: us-east-1
  schema: analytics
  s3_staging_dir: s3://my-bucket/athena-results/
  aws_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
  aws_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY') }}"
  aws_session_token: "{{ env_var('AWS_SESSION_TOKEN') }}"

Session tokens expire — usually within an hour. For anything long-running or scheduled, prefer the IAM-role path above and let AWS refresh credentials for you.

HOW ATHENA BEHAVES IN DVT

Everything stages through S3. s3_staging_diris where Athena writes every query's results before anyone reads them — that's how the service works, and it's why the field is required. Table data itself can live somewhere separate: set s3_data_dirand built models land there; leave it unset and the staging dir serves both jobs, matching dbt-athena's own behavior.

Workgroups. work_group(note the underscore) runs your queries in a specific Athena workgroup — the AWS-side lever for cost limits, per-team result locations, and engine versions. Omit it for the account's primary workgroup.

athena_team:
  type: athena
  region_name: us-east-1
  schema: analytics
  s3_staging_dir: s3://my-bucket/athena-results/
  work_group: data-eng                      # cost limits live here

Serverless timing. Every query includes a little startup latency, and results are staged to S3 before retrieval — fine for models and federation extracts, noticeable if you expect millisecond point-lookups. Athena is a scan engine; let it scan.

Reading it live. To use this connection as a federation source, point a source at the output by name:

# models/sources.yml
sources:
  - name: data_lake
    meta:
      connection: athena_dev    # the profiles.yml output above
    schema: raw_data            # the Athena database to read
    tables:
      - name: clickstream
      - name: server_logs

INCREMENTAL MODELS — ICEBERG OR HIVE DECIDES THE LIST

Athena is the engine where the accepted strategies belong to the table, not to the adapter. There is no Python-side list to consult at all; validation happens in the materialization, against the table type it resolves for the model. On an Iceberg table you may use append, microbatch and merge. On a Hivetable — which is everything that isn't Iceberg — you may use append, insert_overwrite and microbatch. microbatch is not its own execution path either: it is rewritten to merge on Iceberg and to insert_overwrite on Hive before anything runs.

Leave incremental_strategy unset and you get insert_overwrite — and on an Iceberg table that is a trap worth naming, because insert_overwrite is not on the Iceberg list. An unconfigured Iceberg incremental model therefore fails validation on sight. Iceberg models must state their strategy explicitly. There is no default alias in this adapter for the sentinel other engines use.

The table type itself is resolved in a fixed order: an explicit table_type config on the model wins; failing that, the table_format of the catalog named by catalog_name — the default glue catalog yields Iceberg, the information-schema catalog yields Hive; failing that, hive.

{{ config(
    materialized='incremental',
    table_type='iceberg',              -- iceberg: append | microbatch | merge
                                       -- hive:    append | insert_overwrite | microbatch
    incremental_strategy='merge',      -- state it: the unset default is insert_overwrite,
                                       -- which iceberg refuses
    unique_key='order_id'              -- required by merge (and iceberg microbatch)
) }}

select order_id, status, updated_at
from {{ source('data_lake', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

insert_overwrite requires partitioned_by. Without it, the strategy silently becomes append — no warning, no error, and since that is also the unset default, an unpartitioned Hive model with no incremental config at all appends. The same missing partitioned_by under microbatch is a hard error instead: dbt-athena 'microbatch' incremental strategy for hive tables requires a partitioned_by config.

merge executes only on Iceberg — on Hive it never gets that far, having been rejected at validation — and it requires unique_key, a single column or a list, as does microbatch on Iceberg since that becomes a merge. Miss it and you get Merge strategy must implement unique_key as a single column or a list of columns. incremental_predicates, when provided under merge, must be a list. Merge also unlocks the configs no other strategy here reads: delete_condition, update_condition, insert_condition, the mutually exclusive merge_update_columns / merge_exclude_columns pair, and per-column update rules via merge_update_columns_rules with merge_update_columns_default_rule (default replace; the recognised rule names are coalesce, sum, append, append_distinct and replace). Every write path, whichever strategy you pick, is retried in batches when Athena reports its open-partition limit; force_batch opts into per-partition batching up front. Incremental models here support Python as well as SQL.

An unsupported strategy is refused by the table type it was checked against, and the message names the list it was checked against:

Invalid incremental strategy provided: delete+insert
Incremental models on Iceberg tables only work with 'append', 'microbatch'
or 'merge' (v3 only) strategy.

Invalid incremental strategy provided: merge
Expected one of: 'append', 'insert_overwrite', 'microbatch'

When Athena is the target of a federated f_incremental, the strategy stops compiling into Athena SQL and starts selecting a load mode: DVT computes the model in DuckDB and lands rows through its data-movement layer. The federated vocabulary is append, merge and delete+insert— which crosses Athena's native list rather than matching it. insert_overwrite, this engine's own default, and microbatch are both refused there by name: DVT025, "has no Sling equivalent — f_incremental supports append, merge and delete+insert". The unset default changes lanes too: a federated model that names no strategy is treated as merge by the loader, not as Athena's insert_overwrite — so the same model can be legal federated and refused the moment it is flipped back to native Athena. merge and delete+insert collapse to the same upsert on unique_key, and a missing unique_key becomes a full refresh with a warning rather than an error.

One more refusal is specific to Athena: a keyless f_incremental — one driven by a watermark instead of a unique_key — is rejected with DVT026 here, because the data-movement connector has not been verified to honour an update-key-only load on Athena. That verification exists today for Postgres, Redshift, MySQL, DuckDB, SQLite, SQL Server, Oracle, ClickHouse and Trino; Athena is refused alongside StarRocks, Snowflake, Databricks, BigQuery and Fabric. Give the model a unique_key and it runs.

REFERENCE — EVERY PROFILE FIELD

Everything the Athena profile reads. type, region_name, schema and s3_staging_dir are the hard requirements; credentials come from the profile or the AWS chain.