Knowledge Base

Overview

MERGE ON lets you incrementally upsert into a materialized view by matching rows between the query result (the implicit source) and the existing view (the implicit target). Use it to avoid full overwrites, prevent duplicates, and keep data fresh.

• Works inside CREATE MATERIALIZED VIEW only.
• source and target aliases are auto-generated for the MERGE ON condition and the WHEN MATCHED/WHEN NOT MATCHED clauses.
• Typical use cases: deduplication, incremental refresh, attribute enrichment.

Example 1: Internal dataset incremental upsert

CREATE MATERIALIZED VIEW "ongoing_Feed"
REFRESH_SCHEDULE = '@monthly'
WRITE_MODE = 'append'
AS
SELECT
    "ip_address" AS ip,
    "sha256_hashed_email"."value" AS sha256,
    "sha1_hashed_email"."value" AS sha1,
    "md5_hashed_email"."value" AS md5,
    "mobile_id_unique_identifier"."value" AS maid,
    "mobile_id_unique_identifier"."type" AS maid_type,
    CAST("event_timestamp" AS TIMESTAMP) AS "event_timestamp",
    CURRENT_TIMESTAMP as last_modified_at
FROM
    company_data.audience_feed
WHERE
    "event_timestamp" >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
    AND "sha256_hashed_email"."value" IS NOT NULL
    AND "ip_address" IS NOT NULL
    AND "mobile_id_unique_identifier"."value" IS NOT NULL
MERGE ON target.last_modified_at > CURRENT_TIMESTAMP - INTERVAL '20' DAY
WHEN MATCHED THEN UPDATE SET
  ip = source.ip,
  sha256 = source.sha256,
  sha1 = source.sha1,
  md5 = source.md5,
  maid = source.maid,
  maid_type = source.maid_type,
  event_timestamp = source.event_timestamp,
  last_modified_at = source.last_modified_at
WHEN NOT MATCHED THEN INSERT (
  ip,
  sha256,
  sha1,
  md5,
  maid,
  maid_type,
  event_timestamp,
  last_modified_at
) VALUES (
  source.ip,
  source.sha256,
  source.sha1,
  source.md5,
  source.maid,
  source.maid_type,
  source.event_timestamp,
  source.last_modified_at
);

• The MERGE ON condition restricts updates/inserts to rows considered “fresh” (e.g., within a license window). This is optional but useful for avoiding stale overwrites.

Example 2: Enrich from Rosetta Stone with composite keys and null-safe equality

CREATE MATERIALIZED VIEW "email_geo_enrichment"
WRITE_MODE = 'append'
REFRESH_SCHEDULE = '@weekly'
AS (
  SELECT
    ds.user_id,
    rs."sha256_hashed_email"."value" AS sha256,
    rs."geo_country_code" AS country_code,
    rs."geo_region" AS region,
    rs."nio_last_modified_at" AS rs_modified_at,
    CURRENT_TIMESTAMP AS last_modified_at
  FROM company_data."internal_users" ds
  JOIN narrative."rosetta_stone" rs
    ON rs."unique_id"."value" = ds.unique_id
)
MERGE ON
  target.user_id IS NOT DISTINCT FROM source.user_id
  AND target.sha256 IS NOT DISTINCT FROM source.sha256
WHEN MATCHED THEN UPDATE SET
  country_code = source.country_code,
  region = source.region,
  rs_modified_at = source.rs_modified_at,
  last_modified_at = source.last_modified_at
WHEN NOT MATCHED THEN INSERT (
  user_id,
  sha256,
  country_code,
  region,
  rs_modified_at,
  last_modified_at
) VALUES (
  source.user_id,
  source.sha256,
  source.country_code,
  source.region,
  source.rs_modified_at,
  source.last_modified_at
);

• Uses a composite key (user_id, sha256) with IS NOT DISTINCT FROM to handle nullable values safely.

Best practices

• Use a stable key for matching. Prefer a single unique identifier when available; otherwise use a composite of stable attributes.
• For nullable identifiers, prefer IS NOT DISTINCT FROM in the MERGE ON condition.
• Consider a time-bounded clause (e.g., only update if within the last N days) to respect license refresh windows. This is optional.
• Keep WRITE_MODE = 'append' for incremental merge semantics.

Common pitfalls

• Non-unique or changing match keys can cause repeated inserts or updates to the wrong rows; ensure MERGE ON uses a stable, unique key (or composite).
• Using plain = on nullable fields can misclassify matches; use IS NOT DISTINCT FROM instead.
• Overly broad MERGE ON conditions can cause frequent rewrites; scope to the minimum key set and optional recency window.
• MERGE ON is only supported within CREATE MATERIALIZED VIEW—standalone MERGE statements are not runnable in Data Studio.

Related reading