Ade Bench Cross DB Tasks

ADE-Bench Cross-DB Task Authoring

Overview

Tasks in ade-bench can target DuckDB (default) or Snowflake. A task's files go through a deterministic four-stage lifecycle. Patches that ignore this order produce context mismatches or "reversed patch" prompts that block execution.

Container Lifecycle (in order)

1. MIGRATION   shared/migrations/<name>/migration.sh + migration.patch
               — Converts shared project files from DuckDB to Snowflake syntax
2. SETUP       tasks/<id>/setup.sh  +  tasks/<id>/setup/changes.patch
                                       tasks/<id>/setup/changes.snowflake.patch (optional)
               — Creates the puzzle state the agent must solve
3. AGENT       (agent works here)
4. SOLUTION    tasks/<id>/solution.sh  +  tasks/<id>/solutions/changes.patch
                                          tasks/<id>/solutions/changes.snowflake.patch (optional)

Critical: Migration runs before setup and solution. Any line migration touches is in its post-migration state when patches run.

Patch File Pattern

Always apply the base patch, then conditionally apply the snowflake delta. This applies to both setup.sh and solution.sh:

patch -p1 < /app/setup/changes.patch           # always (use /sage/solutions/ for solution.sh)

if [[ "$*" == *"--db-type=snowflake"* ]]; then
    patch -p1 < /app/setup/changes.snowflake.patch  # additive delta only
fi

Never use if/else to choose between two alternative patches. The base patch handles DuckDB; the snowflake patch stacks on top for Snowflake.

changes.snowflake.patch is a delta — it only contains changes that differ from the DuckDB result. It never replicates what migration already did.

Migration Guidelines

Migration's job: Convert DuckDB-specific syntax to Snowflake equivalents across all tasks sharing a project.

• Schema names: schema: main → schema: public
• Date functions: STRPTIME(...) → TO_DATE(TO_TIMESTAMP(...))
• Generator functions, STRFTIME, etc.

Don't add migration hunks for files that setup removes. If setup strips a config block entirely, migration touching a field inside that block is wasted work that breaks setup's patch. Example: if setup/changes.patch deletes an entire {{ config(...) }} block from src_hosts.sql, migration should not change schema= inside that same block.

Modifying dbt_project.yml: use yq, not patch

The migration process calls yaml.safe_load / yaml.safe_dump on dbt_project.yml to update the profile name. This completely reformats the file — sorted keys, no comments, different whitespace. Any patch against the original layout will fail.

Use yq for all dbt_project.yml changes in setup.sh and solution.sh:

# Add a variable
yq -i '.vars.surrogate_key_treat_nulls_as_empty_strings = true' dbt_project.yml

# Delete a variable
yq -i 'del(.vars.quickbooks.using_department)' dbt_project.yml

# Disable a package model
yq -i '.models.quickbooks_source["stg_quickbooks__refund_receipt"]["+enabled"] = false' dbt_project.yml

yq performs semantic YAML operations so key order and formatting don't matter.

Setup Patch Guidelines

Setup creates the puzzle. setup/changes.patch runs after migration.

When setup's base patch can't match post-migration content (e.g. migration changed schema="main" to schema="public" in a line the setup patch must remove):

# setup.sh
patch -p1 --batch < /app/setup/changes.patch || true   # graceful fail on Snowflake

if [[ "$*" == *"--db-type=snowflake"* ]]; then
    patch -p1 --batch --forward < /app/setup/changes.snowflake.patch
fi

• --batch: prevents interactive "Apply anyway?" prompt (avoids blocking)
• --forward: skips reversed/already-applied hunks without prompting
• || true: base patch may partially fail on Snowflake; that's expected

setup/changes.snowflake.patch is the DuckDB base patch with post-migration values substituted (e.g. schema="public" instead of schema="main" in the removed lines).

Solution Patch Guidelines

Context line mismatch (migration changed a context line)

If migration rewrites a line that appears as a context line ( ) in changes.patch, drop it from context:

# Before — STRPTIME is a context line; migration changes it to TO_DATE(...)
 @@ -11,7 +11,7 @@
          transaction_type,
          CAST(STRPTIME(transaction_created_date, '%m/%d/%Y %H:%M:%S') AS DATE) AS transaction_created_date,
          transaction_modified_date,
 -        product_id,
 +        product_id::varchar AS product_id,

# After — start hunk after the changed line, no STRPTIME context
 @@ -14,4 +14,4 @@
 -        product_id,
 +        product_id::varchar AS product_id,
          quantity,
          purchase_order_id,
          customer_order_id,

- line mismatch (migration changed a line being removed)

If migration rewrites a line that is a - line (a line being deleted), 0-context doesn't help — the removed line must match exactly. Use changes.snowflake.patch with the post-migration value:

# changes.snowflake.patch — removes config block after migration changed schema
@@ -1,9 +1,0 @@
-{{
-    config(materialized="table", schema="public", ...)
-}}
-