Snowflake-Labs/dcr-migration-tool

Snowflake-Labs/dcr-migration-tool

Description: Snowflake DCR P&C API to Collab API migration tool

Language: PLpgSQL

License: Apache-2.0

Stars: 1

Forks: 1

Open issues: 0

Created: 2026-03-10T04:57:27Z

Pushed: 2026-05-11T17:49:23Z

Default branch: main

Fork: no

Archived: no

README:

DCR Migration Tool

> Version: 2.3.0 | Target: Snowflake Collaboration Hub (API v2.0) | Source: Legacy Provider & Consumer (P&C) and UI Clean Rooms

---

Overview

The DCR Migration Tool is an automated engine that upgrades legacy P&C API and UI cleanrooms to the new Snowflake Collaboration Hub architecture. It abstracts the complexity of writing YAML specifications and API calls into a streamlined Plan → Execute → Finalize → Validate workflow.

Provider: Generate Plan > Execute Setup > Join (worksheet) > Validate

Consumer: Generate Plan > Execute Setup > Review + Join + Link (worksheet) > Validate

| Component | Description | |-----------|-------------| | Backend | Suite of Snowflake Stored Procedures (Python) for spec generation, orchestration, and audit logging | | Frontend | Streamlit App (running in Snowsight) providing a guided migration UI |

---

Features

• Automated Discovery — Detects your role (Provider or Consumer) and enumerates templates, datasets, and policies from the legacy cleanroom.
• Spec Generation — Converts legacy SQL templates and table policies into v2.0 compliant YAML specs with literal block style for readability.
• Smart Column Type Detection — Recognizes common join column abbreviations (HEM, HPN, IDFA, etc.) and maps them to valid Snowflake column_type identifiers.
• Python Cleanroom & UDF Migration — Reads SAMOOHA_CLEANROOM_.SHARED_SCHEMA.LOAD_PYTHON_RECORD and lists @APP.CODE/V1_0P1. Matches both P&C flows from Use Python in a clean room: inline load_python_into_cleanroom (BODY → code_body) and stage overload (imports in metadata → Collaboration artifacts + per-function imports, see custom functions). Stage paths default to @SAMOOHA_CLEANROOM_.APP.CODE//… with ` inferred from stage listings or V1_0P1. You remain responsible for Collaboration stage rules (internal stage, DIRECTORY, SNOWFLAKE_SSE, etc.). Rewrites template SQL to cleanroom.$( and registers templates with code_specs` linkage. Requires Data Clean Rooms 12.9+ for custom functions.
• UI cleanrooms — Cleanrooms created in the Snowflake UI have a human-readable name distinct from the cleanroom id. The tool resolves either name or id via VIEW_CLEANROOMS(), uses the id for all P&C API calls, and names the Collaboration migrated_ (UPPERCASE). Platform-privacy SQL templates (name contains platform_privacy / prod_sql_with_platform_privacy) are skipped in generated template specs; validate parity and data offerings accordingly.
• Safety Guardrails — Pre-flight checks block unsupported configurations (multi-provider, ML Jobs, SPCS) and warn about privacy downgrades (Differential Privacy).
• Deterministic Versioning — Provider and consumer artifact IDs use a shared suffix (currently `MIGRATION_V2`) so template, data offering, and collaboration registrations stay aligned.
• Parity Validation — Compares the new Collaboration against the legacy Cleanroom to verify template and data offering coverage.
• Audit Logging — Every migration run is logged to MIGRATION_JOBS with job ID, timestamps, status, and details.
• Migration History — "Migrated DCRs" view shows all past migrations with live collaboration status and job metadata.
• Re-migration Support — Teardown a failed collaboration and re-run; templates and data offerings are safely skipped if already registered.
• ML Jobs / SPCS / Differential Privacy Detection — Pre-flight checks scan LOAD_PYTHON_RECORD for compute pools and snowflake_ml_python, scan templates for SPCS service_functions. references, and detect DP noise injection patterns (addnoise, laplace, dp_noise). Unsupported cleanrooms are blocked; DP cleanrooms receive a privacy-downgrade warning.
• Consumer Manual Join SQL — Consumer EXECUTE returns a pre-built SQL script containing REVIEW + JOIN + LINK_DATA_OFFERING + SET_CONFIGURATION calls, ready for worksheet execution.
• Smart Schema Policy Generation — GENERATE_DATA_OFFERING_SPECS iterates all columns from DESC TABLE, using guess_type() + refine_type_by_data() to auto-classify each column as join_standard (with inferred column_type) or passthrough.
• Human-Readable Collaboration Naming — Resolves display names from VIEW_CLEANROOMS() for UPPERCASE naming (migrated_HUMAN_NAME).
• ReferenceUsageGrantMissing Auto-Remediation (Streamlit) — Parses error details, generates ready-to-copy GRANT REFERENCE_USAGE commands, and provides a one-click Teardown button with automated retry.
• Cleanroom Classification Display — Sidebar shows P&C vs UI classification with counts; 5-metric dashboard (Type, Role, Templates, Data Offerings, Status).
• Template Classification Breakdown — Review Plan tab shows PLATFORM_PRIVACY vs STANDARD counts with status icons.

---

Prerequisites

1. Snowflake Data Clean Room app must already be installed on your account. 2. You must have access to the SAMOOHA_APP_ROLE role. 3. The role must have permissions to create databases/schemas (for tool installation) and call Native App procedures.

Discovering the Migration Tool

| Channel | Details | |---------|---------| | Documentation (GA) | docs.snowflake.com/user-guide/cleanrooms/migration-to-collab | | Direct link | You may receive a link from Snowflake Support or Solutions Engineering | | GitHub (v1) | Download the code directly from the GitHub repository |

Installation

1. Deploy the Backend

1. Log in to **app.snowflake.com**. 2. Open a new SQL Worksheet. 3. Copy the contents of migration-backend.sql from the GitHub repository. 4. Click Run All.

This creates the DCR_SNOWVA.MIGRATION schema with all stored procedures and the MIGRATION_JOBS audit table.

Stored procedure entry points

| Procedure | Purpose | |-----------|---------| |…