agent patch used snowflake syntax on a bigquery project

TL;DR

The agent's SQL uses functions or syntax from the wrong warehouse. Rewrite the patch in the project's actual dialect (BigQuery here): swap the Snowflake-isms for BigQuery equivalents, compile, and run. Point the agent at the adapter docs for the project's warehouse going forward.

Error

agent patch used snowflake syntax on a bigquery project

Steps

1. Identify the dialect violations in the agent's patch: common ones are IFF vs IF, DATEADD vs DATE_ADD, :: casting vs CAST, double quotes vs backticks. Expected: A list of Snowflake-specific constructs in the patch.

2. Rewrite each in BigQuery syntax: IF(cond, a, b), DATE_ADD(d, INTERVAL 1 DAY), CAST(x AS type), backtick-quoted identifiers. Expected: The patch uses only BigQuery-valid syntax.

3. Run dbt compile --select the model and read the compiled SQL for any remaining violations. Expected: Compiled SQL is clean BigQuery.

4. Run dbt run --select the model to confirm it executes. Expected: Build succeeds on BigQuery.

When to use

• an agent patch fails with syntax errors naming Snowflake functions on BigQuery
• the agent clearly wrote for the wrong warehouse
• a patch copied from a Snowflake project into a BigQuery one

When not to use

• the SQL is correct BigQuery but logically wrong (that is a logic bug)
• the project genuinely uses Snowflake (then the patch is fine, check the profile)
• the error is a dbt compile error in jinja, not warehouse syntax

Tool compatibility

• dbt-bigquery adapter docs for the function list
• dbt compile to inspect generated SQL without running
• BigQuery console to test tricky expressions by hand

Variant phrasings

agent used postgres syntax on snowflake

Mirror image: rewrite with Snowflake functions. The diagnosis process is identical.

agent mixed dialects in one model

Rewrite the whole model in the project dialect; mixed-dialect models rot fast.

Why it happens

Agents train on SQL from every warehouse and default to whichever dialect is most common in their training, often Snowflake or Postgres. Without an explicit dialect constraint they emit the wrong one, and dbt only catches it at warehouse runtime.

Edge cases

• Cross-db macros in dbt-utils abstract some of this; prefer them over hand-rolled dialect SQL.
• Put the warehouse name in the agent's instructions so it writes the right dialect first try.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_q2n5xTeM8xgGmZ9-OghmTw