feat(athena): add build_strategy config for incremental and table materializations

[dtaniwaki]

resolves #1829
docs dbt-labs/docs.getdbt.com/#

Problem

Athena incremental models always stage data into a __dbt_tmp table via CTAS before applying MERGE or INSERT. The tmp table exists to support automatic batching when TOO_MANY_OPEN_PARTITIONS occurs, but many models never hit this limit. For these models, the tmp table is pure overhead — unnecessary S3 writes, doubled scan costs, and added latency.

Solution

Add build_strategy config option to control how a model's source SQL is materialized. The default tmp_table preserves today's behavior; subquery skips the full CTAS and uses the compiled SQL directly as a subquery.

• Empty tmp table for schema comparison: CTAS ... WITH NO DATA (zero data scan) preserves the process_schema_changes flow, so on_schema_change is fully supported
• Merge strategy: MERGE INTO target USING (subquery) AS src ON ...
• Append strategy: INSERT INTO target SELECT ... FROM (subquery)
• Insert overwrite strategy: subquery feeds the partition-overwrite INSERT directly
• Table materialization: CREATE TABLE AS subquery skips the intermediate tmp table
• Works with both Iceberg and Hive table types

Example configuration:

# dbt_project.yml or model config
models:
  my_project:
    +build_strategy: subquery

-- model file
{{ config(build_strategy='subquery') }}
select ...

Constraints:

• subquery is incompatible with force_batch — batching requires physical tmp table partition metadata
• subquery is not supported with Python models
• With subquery, TOO_MANY_OPEN_PARTITIONS raises an error instead of auto-batching (clear message guides user to switch back to tmp_table)

Test infrastructure (Terraform)

The functional tests in tests/functional/adapter/test_build_strategy.py exercise every combination of build_strategy × incremental strategy × table type against a real Athena account. The Terraform below provisions the minimal AWS resources needed.

Terraform (click to expand)

terraform {
  required_version = ">= 1.5"

  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.0"
    }
  }
}

provider "aws" {
  profile = "<your-aws-profile>"
  region  = "<your-region>"
}

locals {
  name = "<your-test-name>"
}

resource "aws_s3_bucket" "test" {
  bucket        = local.name
  force_destroy = true
}

resource "aws_s3_bucket_public_access_block" "test" {
  bucket                  = aws_s3_bucket.test.id
  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

resource "aws_glue_catalog_database" "test" {
  name = replace(local.name, "-", "_")
}

resource "aws_athena_workgroup" "test" {
  name        = local.name
  description = "Functional test workgroup for dbt-athena build_strategy"

  configuration {
    enforce_workgroup_configuration = true

    result_configuration {
      output_location = "s3://${aws_s3_bucket.test.bucket}/output/"
    }
  }
}

output "s3_staging_dir" {
  value = "s3://${aws_s3_bucket.test.bucket}/staging/"
}

output "s3_tmp_table_dir" {
  value = "s3://${aws_s3_bucket.test.bucket}/tmp/"
}

output "database" {
  value = aws_glue_catalog_database.test.name
}

output "workgroup" {
  value = aws_athena_workgroup.test.name
}

Checklist

• I have read the contributing guide and understand what's expected of me
• I have run this code in development and it appears to resolve the stated issue
• This PR includes tests, or tests are not required/relevant for this PR
• This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX

[Copilot]

Pull request overview

Adds a new Athena adapter config (build_with_subquery) to avoid staging incremental model results into a physical __dbt_tmp CTAS table for append and Iceberg merge, reducing S3 write/read overhead and scan cost by using the compiled model SQL as a subquery instead.

Changes:

• Introduces build_with_subquery adapter config and materialization logic to use USING ({{ compiled_code }}) / FROM ({{ compiled_code }}) while still creating an empty tmp table for schema comparison.
• Adds guardrails/errors for unsupported combinations (Python models, force_batch, insert_overwrite) and for TOO_MANY_OPEN_PARTITIONS when subquery mode is enabled.
• Adds unit + functional tests covering SQL generation and end-to-end behavior for merge/append and incompatibility with force_batch.

Reviewed changes

Copilot reviewed 8 out of 8 changed files in this pull request and generated 2 comments.

Show a summary per file

File	Description
dbt-athena/src/dbt/adapters/athena/impl.py	Adds build_with_subquery to AthenaConfig and documents intended behavior.
dbt-athena/src/dbt/include/athena/macros/materializations/models/incremental/incremental.sql	Reads build_with_subquery and wires subquery mode into append + Iceberg merge paths (empty CTAS for schema comparison).
dbt-athena/src/dbt/include/athena/macros/materializations/models/incremental/helpers.sql	Extends incremental_insert to optionally insert from a subquery and error on open partitions in subquery mode.
dbt-athena/src/dbt/include/athena/macros/materializations/models/incremental/merge.sql	Extends iceberg_merge to optionally USING (subquery) and error on open partitions in subquery mode.
dbt-athena/tests/unit/test_config.py	Adds unit coverage for AthenaConfig default/override of build_with_subquery.
dbt-athena/tests/unit/test_build_with_subquery.py	Adds unit tests validating generated SQL for insert/merge and open-partitions behavior.
dbt-athena/tests/functional/adapter/test_build_with_subquery.py	Adds functional coverage for Iceberg merge/append and Hive append plus force_batch incompatibility.
dbt-athena/.changes/unreleased/Features-20260406-225458.yaml	Adds changelog entry for the new feature.

---

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

[Copilot]

The build_with_subquery validation block runs unconditionally, which will raise a compiler error even on --full-refresh runs (e.g., when build_with_subquery=True and force_batch=True). The new functional tests expect full-refresh to succeed and only the incremental run to fail, and it’s also reasonable for full-refresh to ignore this incremental-only optimization. Consider gating these checks to only apply on incremental runs (e.g., when existing_relation is not none and not should_full_refresh()), or update the tests/behavior consistently.

Suggested change

	{% if force_batch %}
	{% do exceptions.raise_compiler_error('build_with_subquery is incompatible with force_batch. Batching requires data in the tmp table.') %}
	{% endif %}
	{% if strategy == 'insert_overwrite' %}
	{% do exceptions.raise_compiler_error('build_with_subquery is not supported with insert_overwrite strategy.') %}
	{% endif %}
	{% if existing_relation is not none and not should_full_refresh() %}
	{% if force_batch %}
	{% do exceptions.raise_compiler_error('build_with_subquery is incompatible with force_batch. Batching requires data in the tmp table.') %}
	{% endif %}
	{% if strategy == 'insert_overwrite' %}
	{% do exceptions.raise_compiler_error('build_with_subquery is not supported with insert_overwrite strategy.') %}
	{% endif %}
	{% endif %}

[dtaniwaki]

I think there're pros and cons in ignoring the error in the full-refresh phase. The error will be eventually raised in the incremental phase, so we should catch it earlier.

[iconara]

Nice to see this being implemented, I think it will be appreciated.

[iconara]

Is there any chance that compiled_code could end with a comment? I assume that this could happen if for example the model's last line has a comment. In that case a newline is needed before the closing paragraph to avoid the rest of the statement ending up inside of the comment.

[dtaniwaki]

I fixed it 👍
f95a3ae

[iconara]

Same here, a newline before the closing parenthesis is needed if there is a possibility that compiled_code could end with a comment.

[dtaniwaki]

I fixed it 👍
f95a3ae

[dtaniwaki]

@colin-rogers-dbt @iconara Could you review this PR? I think direct subquery insert also solves many situations of HIVE_TOO_MANY_PARTITIONS and ICEBERG_TOO_MANY_OPEN_PARTITIONS.

[iconara]

I'm not familiar enough with the way the jinja templates for CTAS/INSERT/MERGE worked before to say anything about that part, but based on the tests I think this is sound. I left a philosophical comment about naming, and how looking at this from an outside perspective, this new way of handling incremental loads is probably how most would expect it to work by default, and the config naming should reflect that (the current default behavior needs to stay the current default, but naming can be used to signal what the "basic" or simplest behavior is).

[iconara]

If you aren't familiar with dbt-athena since before this change, you may be confused by the naming of this property. The name is a response to the current default, but the current default is actually not the default behavior that you would expect.

It's natural to add a feature with a config that enables it, but I'm thinking that in this case we are adding something that probably should have been the default mode, and it's only for historical reasons it ended up being added later. At least to me, using a temp table is the alternative way of doing this.

One way to convey this is to use a config that is not true or false, but enables one of two (or more) modes, like incremental_strategy with values "subquery" or "tmp_table" (default).

[dtaniwaki]

That sounds reasonable. incremental_strategy is the same as dbt-core's config like incremental_strategy: insert_overwrite. How about build_strategy?

[dtaniwaki]

What do you think about applying this mode to table materialization? This behavior benefits all the materialization types rather than the incremental materialization.

I made another PR over this PR in dtaniwaki#12.

[iconara]

build_strategy sounds good to me, that works for table materialization too.

[iconara]

Nitpick: I would say "Batching requires a temp table"