Resolving Snowflake MERGE Issues for Versioned AppsFlyer Data with dbt Incremental Models

[hediketari]

The problem I’m having

I’m trying to load versioned data from AppsFlyer into Snowflake using an incremental dbt model that leverages a MERGE statement. However, dbt keeps generating a statement like:

create or replace temporary view tilt.bronze.raw_af_ssot_user_acquisition__dbt_tmp
as (
merge into tilt.bronze.raw_af_ssot_user_acquisition …
);

Snowflake doesn’t allow MERGE statements inside a CREATE VIEW (temporary or otherwise), so I get a syntax error:
SQL compilation error:
syntax error line 7 at position 0 unexpected ‘merge’.
syntax error line 12 at position 28 unexpected ‘over’.

The context of why I’m trying to do this

	•	AppsFlyer Data: AppsFlyer provides “versioned” reports, and I only want the latest version for each install date.
	•	Snowflake + dbt: I use dbt for transformations and want an incremental model that merges new data into a final table while discarding older versions.
	•	Versioned Ingestion: By capturing the version from AppsFlyer’s S3 path, I can sort out the most recent rows for each install date. A MERGE fits this logic perfectly in Snowflake.

What I’ve already tried

	1.	Incremental Strategy:
	•	I set {{ config(materialized=‘incremental’, incremental_strategy=‘merge’, unique_key=‘install_date’) }} in my dbt model.
	•	Ensured the model’s logic uses WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT.
	2.	Schema and Permissions:
	•	Verified my dbt role has CREATE TABLE, USAGE, and SELECT on tilt.bronze.
	•	Confirmed the model was set to produce a table in tilt.bronze schema.
	3.	Searching for Tests or References:
	•	Looked for any tests, ephemeral models, or macros referencing raw_af_ssot_user_acquisition.
	•	Ran dbt ls --select raw_af_ssot_user_acquisition+, which shows no direct downstream models.
	•	Double-checked .yml files to ensure no store_failures: true or hidden references.
	4.	Trying Standalone Runs:
	•	Ran dbt run -m raw_af_ssot_user_acquisition directly (no success).
	•	Confirmed that the “create or replace temporary view” snippet is still produced, implying something is wrapping my MERGE statement.
	5.	Reviewing dbt/Environment Versions:
	•	I’m on a custom dbt build (e.g., dbt_version: 2024.xx.xx+), so I can’t easily see if older or newer standard dbt versions would behave differently.

[a_slack_user]

It sound like you literally wrote merge into… in your dbt model. Is that correct? If so, you don’t write the merge yourself in dbt. dbt will write it for you (at a later step - after it creates that temporary view).

_{Note: @Deena Bernett originally posted this reply in Slack. It might not have transferred perfectly.}