DCR V1 to V2 Migration

Overview

Guides migration of a Snowflake Data Clean Room from V1 (SAMOOHA Provider/Consumer API) to V2 (Collaboration API). Produces local SQL scripts and reports — never executes DDL/DML against your accounts. Use this when you have an existing V1 clean room (calls into samoohabysnowflakelocaldb.provider. / consumer.) and want a generated V2 setup plus a side-by-side validation plan.

In scope: V1 inventories, JinjaSQL template conversion (drops the join_policy filter), provider + consumer setup scripts, cleanup scripts, validation checklist.

Out of scope: executing generated SQL, V0 direct-share setups, non-Snowflake clean rooms.

Prerequisites

1. "Snowflake Data Clean Rooms" (SAMOOHA) installed on both accounts.
2. Quick Start completed on both (CHECKMOUNTSTATUS() returns TRUE).
3. Access to the V1 provider account with SAMOOHAAPPROLE or ACCOUNTADMIN.

Workflow

Step 0 — Connection setup

Run cortex connections list, identify the active connection, confirm with the user that it points to the V1 provider account, and store it as provider_connection.

⚠️ STOPPING POINT: Do not run discovery queries until the user confirms the connection.

Step 1 — Discovery

Load discover/INSTRUCTIONS.md and execute its workflow using providerconnection. Output: discoveryreport.md (clean room name, linked datasets, join policies, templates, consumer accounts).

⚠️ STOPPING POINT: Present the discovery report and confirm before mapping.

Step 2 — Mapping

Map each V1 construct to V2. Reference v1v2mapping.md for the full table.

• Parties: V1 provider → V2 owner (COLLABORATION.INITIALIZE); V1 consumer → V2 runner (COLLABORATION.JOIN, COLLABORATION.RUN).
• Datasets: secure view → REGISTRY.REGISTERDATAOFFERING with allowedanalyses: templateonly. Wrap raw tables in a secure view first. Data stays in the provider DB.
• Join policy: V1 setjoinpolicy → V2 schemaandtemplate_policies per column (passthrough for join keys, timestamp for date columns).
• Templates: keep SQL logic; remove | joinpolicy filter and replace with hardcoded join columns (e.g. ON p.EMAILHASH = c.EMAILHASH); keep IDENTIFIER({{ sourcetable[0] }}) and bare {{ param }} unchanged; drop providerid/consumerid from analysis args. Version regex: ^[A-Za-z0-9]{1,20}$. Template ID: nameversion.
• Consumer datasets: V1 linkdatasets → V2 REGISTERDATAOFFERING + LINKLOCALDATAOFFERING.

⚠️ STOPPING POINT: Confirm mapping decisions before generating scripts.

Step 3 — Script generation

Load generate/INSTRUCTIONS.md. Get today's date with date +%Y%m%d (never hardcode). Suggest output dir YYYYMMDDV1toV2Output.

⚠️ STOPPING POINT: Confirm output directory before writing files.

Generate: v2providersetup.sql, v2consumersetup.sql, v2cleanup.sql, mappingreport.md.

Step 4 — Validation checklist

Generate validationchecklist.md covering pre-migration baseline, provider setup (grants USAGE + SELECT + REFERENCEUSAGE WITH GRANT OPTION, registered offerings/templates, INITIALIZE), consumer setup (USE SECONDARY ROLES NONE, LINKLOCALDATAOFFERING before first RUN, 3-part IDs ALIAS.OFFERINGID.DATASET_ALIAS), and per-template result comparison V1 vs V2.

Step 5 — Cleanup guidance

Generate cleanupguidance.md. Recommend a 2–4 week coexistence period. Include V1 teardown (provider.dropcleanroom, consumer.uninstall_cleanroom — keep the provider DB) and V2 teardown using the two-call async pattern: first COLLABORATION.LEAVE/TEARDOWN → status LEAVING/DROPPING → wait ~60s → second call → LEFT/DROPPED.

Step 6 — Summary

Print generated file list and next steps (run provider setup, run consumer setup, compare results, then cleanup).

Stopping Points

• Step 0 — confirm provider connection before any query
• Step 1 — confirm discovery inventory before mapping
• Step 2 — confirm mapping decisions before generation
• Step 3 — confirm output directory before writing files

Common Mistakes

• Executing generated SQL automatically. Always hand off files for human review.
• Linking raw tables. Wrap PII-bearing tables in a secure view first.
• Leaving | join_policy in converted templates. Replace with the literal join column.
• Dots in version names. Regex is ^[A-Za-z0-9_]{1,20}$ — use underscores.
• Missing role grants. Without USAGE + SELECT + REFERENCEUSAGE WITH GRANT OPTION on SAMOOHAAPP_ROLE, INITIALIZE/JOIN fails.
• Forgetting LINKLOCALDATA_OFFERING. First COLLABORATION.RUN will fail without it.
• Skipping USE SECONDARY ROLES NONE before Collaboration API calls.
• Calling LEAVE/TEARDOWN once. They are two-call async — wait ~60s and call again.
• Querying VIEWREGISTEREDDATAOFFERINGS after INITIALIZE and assuming the spec is broken. The spec consumes registry entries; use COLLABORATION.VIEWCOLLABORATIONS() post-JOIN.
• Calling COLLABORATION.REVIEW post-JOIN. REVIEW is pre-JOIN only.

Sub-flows

• discover/INSTRUCTIONS.md — V1 introspection queries
• generate/INSTRUCTIONS.md — V2 Collaboration API script generation
• v1v2mapping.md — reference mapping table