DEV Community: Krishna Tangudu

How a Simple Warehouse Resize Saved Us 11% in Daily Credits While Boosting Performance

Krishna Tangudu — Thu, 07 May 2026 06:56:45 +0000

Our team hit a familiar Snowflake paradox: slower ETL runs arrived at the same time as FinOps alerts about rising credits.

The warehouse in question was WH_ETL_BRONZE_01, a multi-cluster warehouse dedicated to Bronze layer ingestion and merge workloads. What looked like a simple cost problem turned out to be a workload-isolation and concurrency problem.

The Problem

We started with this setup:

ALTER WAREHOUSE WH_ETL_BRONZE_01 SET
  WAREHOUSE_SIZE = 'XSMALL',
  MAX_CLUSTER_COUNT = 10,
  MIN_CLUSTER_COUNT = 1,
  SCALING_POLICY = 'STANDARD',
  AUTO_SUSPEND = 60,
  MAX_CONCURRENCY_LEVEL = 8;  -- default behavior

And we saw both of these at once:

Higher queue times (queued_overload_time)
Higher daily credits

At that point, we were loading 1,500+ tables through this single warehouse. That meant heavyweight Bronze MERGE workloads and smaller ingestion/utility queries were all competing in the same execution pool.

The critical issue was workload mix. Long-running MERGE statements (30 to 60 minutes) were running alongside tiny queries. When too many heavy MERGE statements landed on the same node/cluster, they competed for resources and all slowed down.

In other words, even with multi-cluster enabled, assignment patterns could create unstable performance if too many heavyweight queries were packed together.

We also learned we needed workload separation: large-table loads should run in a dedicated warehouse so they do not contend with smaller table loads and operational queries.

What We Changed

This was not a single before/after discovery from history. We ran controlled experiments in sequence, while splitting the largest table loads into a separate warehouse path.

Phase 1: Baseline

ALTER WAREHOUSE WH_ETL_BRONZE_01 SET
  WAREHOUSE_SIZE = 'XSMALL',
  MAX_CLUSTER_COUNT = 10,
  MIN_CLUSTER_COUNT = 1,
  SCALING_POLICY = 'STANDARD',
  AUTO_SUSPEND = 60,
  MAX_CONCURRENCY_LEVEL = 8;

Phase 2: First Experiment

ALTER WAREHOUSE WH_ETL_BRONZE_01 SET
  WAREHOUSE_SIZE = 'XSMALL',
  MAX_CLUSTER_COUNT = 10,
  MIN_CLUSTER_COUNT = 1,
  SCALING_POLICY = 'STANDARD',
  AUTO_SUSPEND = 60,
  MAX_CONCURRENCY_LEVEL = 2;

Goal: force scale-out behavior earlier and reduce the chance that many heavy MERGE jobs share the same node.

At the same time, we moved larger-table processing to a separate warehouse so those jobs would not compete with the remaining 1,500+ table ingestion flow.

Phase 3: Final Optimization

ALTER WAREHOUSE WH_ETL_BRONZE_01 SET
  WAREHOUSE_SIZE = 'SMALL',
  MAX_CLUSTER_COUNT = 10,
  MIN_CLUSTER_COUNT = 1,
  SCALING_POLICY = 'STANDARD',
  AUTO_SUSPEND = 60,
  MAX_CONCURRENCY_LEVEL = 3;

This became the best balance for our workload profile.

Final state for this ETL warehouse path:

SMALL size
MAX_CONCURRENCY_LEVEL = 3
Large-table workloads isolated to a separate warehouse

Why This Helped

From deeper analysis of the workload behavior:

The number of queries and output volume could look similar across runs, but bytes scanned at table level could still rise.
Heavy MERGE overlap was a key instability driver.
On slow runs, many heavy MERGE statements could get assigned to the same node, causing resource contention.
On fast runs, fewer heavy MERGE statements shared a node, leaving room for short-running queries.
Isolating large-table workloads reduced cross-workload contention in the primary Bronze ETL warehouse.

Lowering concurrency reduced the tendency to over-pack heavyweight merges. Then moving to SMALL provided enough per-query resources to cut elapsed time and queueing further. Separating large-table loads into a dedicated warehouse stabilized performance for the rest of the pipeline.

Configuration Summary

Phase	Size	Max Clusters	Max Concurrency	Intent
Baseline	XSMALL	10	8	Initial setup
Experiment	XSMALL	10	2	Force earlier scale-out / reduce heavy-query packing
Final	SMALL	10	3	Balance throughput, queueing, and cost

The two final intentional changes vs baseline were:

WAREHOUSE_SIZE: XSMALL -> SMALL
MAX_CONCURRENCY_LEVEL: 8 -> 3

How We Measured

We used Snowflake ACCOUNT_USAGE views for both performance and cost, comparing baseline and final optimization windows.

Query Performance (Before/After)

SELECT
  CASE
    WHEN start_time < '2026-05-05' THEN 'XSMALL Period'
    ELSE 'SMALL Period'
  END AS period,
  COUNT(*) AS total_queries,
  ROUND(AVG(total_elapsed_time)/1000, 2) AS avg_elapsed_sec,
  ROUND(AVG(queued_overload_time)/1000, 2) AS avg_queued_sec,
  ROUND(MAX(total_elapsed_time)/1000, 2) AS max_elapsed_sec
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE warehouse_name = 'WH_ETL_BRONZE_01'
  AND start_time >= '2026-04-30'
GROUP BY period
ORDER BY period;

Credit Consumption (Daily)

SELECT
  CASE
    WHEN start_time::DATE < '2026-05-05' THEN 'XSMALL Period'
    ELSE 'SMALL Period'
  END AS period,
  ROUND(SUM(credits_used_compute) / COUNT(DISTINCT start_time::DATE), 2) AS daily_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE warehouse_name = 'WH_ETL_BRONZE_01'
  AND start_time::DATE >= '2026-04-30'
GROUP BY period
ORDER BY period;

Results

Cost

Period	Size	Daily Credits
Baseline (5 days)	XSMALL	15.12/day
Optimized (3 days)	SMALL	13.50/day

Daily credits dropped by about 11% after upsizing.

Performance

Metric	XSMALL	SMALL	Improvement
Avg query time	26.15s	24.13s	8% faster
Max query time	3,475s	2,850s	18% faster
Total queue time	2.20 min	0.28 min	87% less queuing

Note: The intermediate XSMALL plus concurrency 2 phase was used to validate behavior and direction. The published KPI table above compares the stable baseline period against the final tuned period.

The Counterintuitive Lesson

In some workloads, a bigger warehouse can cost less.

1. Faster execution means earlier suspend

Queries completed faster on SMALL, so the warehouse reached AUTO_SUSPEND = 60 sooner. Less runtime plus less idle overhead translated to fewer daily credits.

2. Higher concurrency can reduce cluster sprawl

Concurrency must match workload shape. In our case, moving from default 8 down to 2 first improved isolation for heavy MERGE jobs, then landing at 3 with SMALL gave the right balance of throughput and stability.

3. Less queueing improves throughput and utilization

With 87% less queue time, work finished in tighter windows. The warehouse did useful work and went to sleep sooner.

Key Takeaways

Do not assume smaller is always cheaper.
Monitor queued_overload_time closely. Persistent queueing often means you are under-sized or under-concurrented.
Tune MAX_CONCURRENCY_LEVEL with warehouse size and workload type; they should be optimized together.
Isolate heavyweight large-table workflows into a dedicated warehouse to reduce overlap and contention.
Use both QUERY_HISTORY (performance) and WAREHOUSE_METERING_HISTORY (cost) for before/after decisions.
Keep aggressive auto-suspend for bursty workloads. Faster queries plus short suspend windows compound savings.

Closing Thought

Warehouse right-sizing is a performance and FinOps exercise, not just a cost-control exercise. In many real workloads, a slightly larger warehouse with slightly higher concurrency can win on both speed and spend.

Disclosure and Scope

Results reflect our specific environment and workload profile; outcomes may vary.

From Glue to Horizon: Our Real Journey Building an Iceberg Lakehouse on Snowflake

Krishna Tangudu — Mon, 04 May 2026 14:18:45 +0000

We set out to build an open lakehouse: Iceberg tables on AWS S3, Spark/Glue for pipelines, Snowflake for analytics compute power. What could go wrong? Everything—from query performance implosions to uncloneable dynamic tables. Here's the unfiltered journey, including why we pivoted to Snowflake-managed Iceberg via Horizon Catalog and abandoned automated dynamic tables for explicit, observable incremental processing.

TL;DR

❌ Phase 1 failed: Glue-generated Iceberg files (32-64MB) or bigger size caused 5-10x slower Snowflake queries
✅ Phase 2 wins: Snowflake-managed Iceberg auto-compacts to 256-512MB, 2-5x faster, ~35% cost savings
⚠️ Dynamic Tables gotcha: Cannot clone, opaque refresh timing—unusable for production DevOps
✅ Solution: Explicit Streams + Tasks on log tables—boring, debuggable, production-ready
🎯 Key decision: Data team owns Bronze→Silver only; business owns Gold (saved endless remodeling debates)
💰 Reality check: "Cloud-neutral" = readable across engines, not free migration

Our Team Context (Yours Will Differ)

Before diving into architecture decisions, here's who we are:

Skills: Strong SQL/dbt, limited Spark/Scala experience
Priorities: Ship fast, avoid operational black boxes
Constraint: No dedicated DevOps for Glue cluster tuning

This shaped every decision below. A team fluent in Spark would have made different trade-offs.

🎯 The Goal

Unify data from SAP HANA (change data capture), Salesforce Data Cloud, and raw event streams into a single cloud-neutral lakehouse—no proprietary lock-in, full cross-tool interoperability.

Why Iceberg?

We evaluated Delta Lake, Apache Hudi, and Apache Iceberg a couple of years ago. Here's the comprehensive comparison that drove our decision:

Criterion	Iceberg	Delta Lake	Hudi
Partition Evolution	✅ Change without rewrite	❌ Requires full table rewrite	❌ Not supported
Primary Keys	✅ Native support	❌ Not supported	✅ Supported
Automated Compaction	✅ MAINTAIN ICEBERG TABLE	⚠️ Manual (OPTIMIZE tuning)	⚠️ Manual/semi-auto
Schema Evolution	✅ Full (add/drop/rename/reorder)	✅ Add/drop columns	⚠️ Limited
Engine Compatibility	✅ Spark, Trino, Flink, Snowflake, Dremio	⚠️ Spark-first, limited others	⚠️ Spark-first
Platform Support	✅ AWS Glue/Athena, Azure, GCP, Snowflake	✅ AWS, Azure (Fabric native), Databricks	⚠️ AWS, limited Azure
File Format Flexibility	✅ Parquet, ORC, Avro	⚠️ Parquet only	✅ Parquet, ORC, Avro
Community Governance	✅ Apache Foundation (vendor-neutral)	⚠️ Databricks-controlled	✅ Apache Foundation

Why We Chose Iceberg (and Ruled Out Delta/Hudi)

Iceberg's winning factors for our context:

Partition evolution without rewrites: Our SAP data's partitioning strategy evolved over time (daily → monthly as data matured). Iceberg lets us change partition specs without rewriting billions of rows. Delta requires full table rewrite—a multi-day, multi-TB operation we couldn't afford.
Snowflake-managed support: Only Iceberg offers native managed tables in Snowflake Horizon Catalog with automatic compaction. Delta/Hudi would lock us into external table limitations with the Phase 1 performance issues.
Automated compaction: Snowflake's MAINTAIN ICEBERG TABLE handles file optimization automatically. Delta requires manual compaction tuning in Spark—expertise our SQL-first team doesn't have.
Primary key enforcement: Iceberg supports primary keys natively, critical for our SAP source data integrity (customer IDs, order numbers). Delta lacks this—you must enforce it in application logic.
Vendor-neutral governance: Apache Foundation stewardship means no single vendor controls the spec. Delta's governance is tied to Databricks' business interests.

Bottom line: Iceberg was the safe bet for multi-engine flexibility without vendor lock-in. If you're Azure-only with Fabric, Delta is pragmatic. If you're Databricks-native, Delta is the path of least resistance. But for AWS + Snowflake + future optionality, Iceberg was the only choice.

Why Snowflake for Compute?

Three differentiators that closed the decision:

1. Separation of Storage & Compute

Scale workloads independently to meet business demands while enabling detailed chargeback per team or domain—without disrupting other workloads. → Deep dive: Understanding Snowflake Virtual Warehouses

2. Automatic Caching + Smart Pruning (RELY Operators)

Sub-second query performance on petabyte-scale data through intelligent result/metadata caching and constraint-based optimization. → Deep dive: RELY Constraint for Star-Schema Queries

3. Compute Billing Precision

POC result: Our thousands of BI dashboards were only charged for calculation time—not data transfer to BI tools. Estimated 40–60% cost savings vs. **competitors **billing full query duration.

🏛️ Medallion Architecture

Our lakehouse follows the classic three-layer medallion model, tailored for SAP source systems:

┌─────────────────────────────────────────────────────────────┐
│                    DATA SOURCES                             │
│   SAP HANA (CDC)  │  Salesforce Data Cloud  │  Raw Streams │
└────────────┬──────────────────┬─────────────────────┬───────┘
             │                  │                     │
             ▼                  ▼                     ▼
┌─────────────────────────────────────────────────────────────┐
│  🥉 BRONZE LAYER — AWS Glue Spark → Iceberg writes to S3   │
│  Raw ingestion, no transformation, full fidelity            │
└─────────────────────────┬───────────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│  🥈 SILVER LAYER — Iceberg Tables (AWS-hosted, S3)         │
│  Matches source table structures                            │
│  e.g., KNA1 (Customers), MARA (Materials), VBAK (Orders)   │
└─────────────────────────┬───────────────────────────────────┘
                          │  Stream on log_table
                          ▼
┌─────────────────────────────────────────────────────────────┐
│  🥇 GOLD LAYER — Snowflake-Managed Iceberg (Horizon Cat.)  │
│  Proper dimensions & facts with business names             │
│  e.g., DIM_CUSTOMER, FACT_SALES_ORDER, DIM_PRODUCT         │
└─────────────────────────┬───────────────────────────────────┘
                          │
                          ▼
              BI Tools / ML / Fabric Export

Sounds ideal? Early reality: Snowflake choked on our Glue-generated files.

The Architecture Decision Nobody Talks About: Layer Ownership

Here's what we learned the hard way: Don't own Gold if you don't have to.

Why this boundary matters:

Silver = Source truth: Matches SAP table structures (KNA1, MARA, VBAK). Data engineering controls quality, structure, and change tracking.
Gold = Business semantics: DIM_CUSTOMER, FACT_SALES_ORDER. Business teams decide how to model, aggregate, and interpret.
Boundary = Contract: Silver provides clean, change-tracked source data with explicit SLAs; business teams own downstream transformations.

This Decision Saved Us From:

❌ Endless "why did the customer count change?" debates (business definition shifts, not data quality issues)

❌ Remodeling dimensions every quarter when business logic evolves

❌ Being the bottleneck for every dashboard request

❌ Owning interpretations of business rules we don't fully understand

Your team may differ, but define the ownership boundary early or you'll own every downstream interpretation forever. The tools (dbt, Iceberg, Snowflake) don't enforce this—you must.

Phase 1: Glue-Managed Iceberg + Snowflake External Tables ❌

The Performance Trap

We started simple: Spark jobs in Glue created Iceberg tables stored in Glue Catalog; Snowflake linked them as external Iceberg tables.

What Broke: File Size Mismatch

Snowflake's Iceberg scanner is optimized for specific file characteristics per official recommendations:

Parameter	Snowflake Recommendation	What Glue Produced
File size	256 – 512 MB	32 – 64 MB (many small files)
Row group size	16 – 256 MB	< 16 MB
Row groups per file	Multiple (for parallelism)	Often 1

The result: Full table scans instead of pruned reads, query times 5–10x slower than expected.

┌──────────────────────────────────────────────────────┐
│              PHASE 1 ARCHITECTURE                    │
│                                                      │
│  AWS Glue Spark ──writes──▶ Iceberg (Glue Catalog)  │
│                                    │                 │
│                             S3 Parquet files         │
│                          (small, fragmented)         │
│                                    │                 │
│  Snowflake ◀──external table──────┘                 │
│  (slow scans, no auto-compaction)                    │
└──────────────────────────────────────────────────────┘

🔀 Decision Point: Invest in Spark Tuning or Snowflake-Managed?

Option A: Hire Spark expertise, tune file compaction settings

Timeline: 3-6 months
Ongoing cost: Maintain Spark expertise, monitor file sizes
Risk: Our team lacks Spark internals experience

Option B: Snowflake-managed Iceberg via Horizon Catalog

Timeline: 2 weeks
Ongoing cost: Snowflake MAINTAIN ICEBERG TABLE compute
Upside: Handles compaction automatically, team stays in SQL/dbt comfort zone

We chose Option B: Our team's strength is SQL/dbt, not Spark internals. Let Snowflake handle the file lifecycle.

Phase 2: Snowflake-Managed Iceberg + Horizon Catalog ✅

The Pivot

Snowflake-managed Iceberg tables put Snowflake in charge of the table lifecycle on your S3 bucket—Horizon Catalog governs metadata, access, and interoperability.

┌──────────────────────────────────────────────────────────┐
│               PHASE 2 ARCHITECTURE                       │
│                                                          │
│  AWS Glue Spark ──writes──▶ Snowflake Horizon Catalog   │
│                                    │                     │
│                    Horizon manages Iceberg metadata      │
│                    Auto-compaction to 256-512MB files    │
│                                    │                     │
│                             S3 (your bucket)             │
│                          Optimal Parquet layout          │
│                                    │                     │
│  Snowflake ◀──native read─────────┘                     │
│  (2–5x faster, full pruning, cloneable*)                 │
│                                                          │
│  BI Tools ◀── Snowflake compute                         │
│  Glue/Spark ◀── Iceberg open format (bidirectional)     │
└──────────────────────────────────────────────────────────┘

Wins

✅ 2–5x query speedup vs. Phase 1 external tables

✅ Automatic compaction to optimal file sizes via MAINTAIN ICEBERG TABLE

✅ Bidirectional access: Glue/Spark can write, Snowflake reads natively; BI tools use Snowflake compute

✅ Open format preserved: Gold layer exportable to Fabric/Polaris later

Trade-off: Snowflake compute is billed for maintenance runs—but total ops cost is lower than Phase 1's slow queries burning warehouse credits.

⚠️ Why We Abandoned Dynamic Tables for Production

Dynamic Tables on Iceberg sounded perfect—zero-code pipelines, automatic refresh. We hit two walls that forced us back to explicit patterns:

Problem 1 — No Cloning Support:

-- This FAILS silently on dynamic Iceberg tables
CREATE DATABASE prod_clone CLONE prod_db;
-- Dynamic Iceberg tables are simply skipped in the clone

DB/schema clones skip dynamic Iceberg tables entirely. This is a DevOps killer—no dev/test environment parity, no blue-green deploys.

Problem 2 — Opaque Refresh Timing:

Incremental refresh latency was unpredictable and nearly impossible to debug for SLA enforcement. Monitoring refresh lag and debugging failures was guesswork with no visibility into what triggered refreshes or why they were delayed.

🔀 Trade-off: Zero-Code vs. Zero-Surprise

Dynamic Tables promise automation but hide:

When refreshes actually run
What triggered the refresh
How to debug failures in production at 3am

Streams + Tasks = More code, but debuggable in production.

Architecture decision-makers: Optimize for production support, not dev convenience.

Resolution: Abandoned dynamic tables for Silver → Gold. Back to explicit Streams + Tasks.

Our Solution: Log Table Pattern

┌─────────────────────────────────────────────────────┐
│           SILVER → GOLD PIPELINE (Current)          │
│                                                      │
│  Silver Layer                                        │
│     │                                               │
│     ├──▶ silver.log_table (tracks all changes)      │
│                │                                    │
│                ▼                                    │
│     CREATE STREAM log_changes                       │
│     ON TABLE silver.log_table;          ◀── stream  │
│                │                                    │
│                ▼                                    │
│     CREATE TASK gold_refresh            ◀── task    │
│       SCHEDULE = '5 MINUTE'                         │
│       AS MERGE INTO gold.customer_360               │
│          USING log_changes ...;                     │
│                │                                    │
│                ▼                                    │
│     Gold Layer (Snowflake-Managed Iceberg)          │
└─────────────────────────────────────────────────────┘

-- The explicit, cloneable, debuggable pattern
CREATE STREAM log_changes ON TABLE silver.log_table;

CREATE TASK gold_refresh
  WAREHOUSE = compute_xs
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('log_changes')
  AS
  MERGE INTO gold.customer_360 tgt
  USING (
    SELECT * FROM log_changes WHERE metadata$action = 'INSERT'
  ) src
  ON tgt.customer_id = src.customer_id
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT ...;

Why This Wins:

✅ Full cloning support (dev/prod parity restored)

✅ Transparent costs — every execution logged in TASK_HISTORY

✅ Debuggable — stream offset visible, failures isolated

✅ Fabric-friendly — Gold can be exported as Iceberg/Parquet later

🌐 Cross-Platform Reality: Why We Can (and Can't) Pivot

Current State: AWS + Snowflake, Iceberg on S3

✅ Open format preserved: Can read Iceberg from Spark, Trino, Athena

✅ Gold exportable: Stream to Parquet → Fabric mirroring works

⚠️ Fabric constraint: OneLake wants Delta + same Azure region for zero-copy

⚠️ Horizon lock-in: Snowflake-managed Iceberg metadata tied to Horizon Catalog

What "Cloud-Neutral" Actually Means

Not: "Deploy anywhere tomorrow with zero effort"

Actually: "Readable by multiple engines, movable with effort"

If We Had to Migrate to Fabric:

Layer	Migration Effort	Estimated Timeline
Bronze → Silver ingestion	Rewrite Glue jobs to Delta	2-3 weeks
Silver → Gold dbt models	Port to Fabric SQL (syntax diffs)	1-2 months
Gold Iceberg tables	Export as Parquet, re-create in Fabric Warehouse	1-2 weeks

┌────────────────────────────────────────────────────────────┐
│                  CATALOG LANDSCAPE                         │
│                                                            │
│  Our Setup: AWS us-east-1                                  │
│  ┌────────────────────────────────────────────────────┐   │
│  │  Snowflake Horizon Catalog (current)               │   │
│  │  + AWS Glue Catalog (bronze/silver ingestion)      │   │
│  └────────────────────────────────────────────────────┘   │
│                                                            │
│  Future Options:                                           │
│  ┌─────────────────┐    ┌──────────────────────────────┐  │
│  │ Polaris Catalog │    │ Microsoft Fabric OneLake      │  │
│  │ (Snowflake SaaS)│    │ (wants Delta; Azure-region   │  │
│  │ Maturing fast   │    │  only for zero-copy)          │  │
│  └─────────────────┘    └──────────────────────────────┘  │
│                                                            │
│  Gold Streams → Parquet export → Fabric compatible ✅      │
└────────────────────────────────────────────────────────────┘

Our Decision:

We prioritized Snowflake ecosystem depth over day-1 multi-cloud portability.

Iceberg gave us an exit path, not a free exit. For teams needing Fabric OneLake zero-copy, starting with Delta on Azure is the pragmatic choice.

💰 Cost Reality Check

Phase 1 (Glue + External Iceberg):

Glue: costs to write and maintain iceberg tables
Snowflake query costs: 3x higher due to full scans

Phase 2 (Snowflake-managed Iceberg):

MAINTAIN ICEBERG TABLE: in warehouse credits
Query costs: 60% reduction (pruning + caching works correctly)
Net savings: 35% monthly vs. Phase 1

Takeaway: Optimize for query performance where your users actually spend time, not just ingestion costs.

🔑 Lessons for Your Lakehouse

1. File sizes first

Target 256-512MB Parquets or let Snowflake MAINTAIN ICEBERG TABLE handle it automatically. Small files kill Snowflake performance.

2. Clone-test early

Dynamic Iceberg tables are powerful but not clone-safe for databases/schemas. Test your DevOps workflow before committing to production.

3. Horizon bidirectional is a game-changer

If your team uses both Spark and Snowflake, Horizon gives you the best of both without choosing sides. Write with Spark, read with Snowflake—all on the same Iceberg tables.

4. Streams > automation black boxes

Explicit Streams + Tasks always win in production ops: observable at 3am, debuggable from logs, cloneable for dev/test.

5. Phase your migration

Don't try to migrate all layers at once. Start with Gold (highest query frequency), validate performance, then move Silver.

6. Define ownership boundaries early

Bronze→Silver→Gold isn't just technical layers—it's organizational boundaries. Decide who owns what before the first production table.

When to Use What: Decision Matrix

Your Situation	Recommendation	Why
Team strong in Spark, need multi-engine	Glue-managed Iceberg + External tables	Keep expertise where it is
Team SQL-first, Snowflake primary engine	Snowflake-managed Iceberg	Let Snowflake handle file lifecycle
Must support Fabric OneLake zero-copy	Delta Lake on Azure	Iceberg works but not zero-copy on Fabric
Need dev/prod clones + SLA guarantees	Streams + Tasks (avoid Dynamic Tables)	Observable, debuggable
Bronze/Silver only (like us)	dbt incremental + Developer Toolkit	Explicit watermark control

Bottom line: Cloud-neutral means readable across tools, not free migration. Choose the platform that matches your team's strengths, use open formats for portability insurance.

What's Next for Us

Investigate Polaris Catalog or wait for Horizon Catalog for true multi-vendor Iceberg metadata management
Evaluate cost/performance of streaming directly from Kafka → Snowflake Iceberg
Explore Iceberg v3 features for enhanced BCDR and CDC capabilities

🆕 Update: Iceberg v3 Features (March 2026) That Could Change Your Decision

Since our original evaluation, Snowflake released Apache Iceberg v3 support in public preview (March 2026) with capabilities that address several of our pain points and unlock new architectural patterns.

What's New in Iceberg v3

1. Cross-Region Replication for Snowflake-Managed Tables

What it enables: BCDR failover and replication groups for Iceberg tables across regions
Why it matters: Previously, our DR strategy required complex Parquet exports. Now, Snowflake-managed Iceberg tables can replicate with full consistency (including row lineage and deletion vectors)
Architecture impact: We can now deploy active-passive DR without custom tooling
📖 Replication Config Docs

2. Catalog-Linked Databases

What it enables: Connect to remote Iceberg catalogs (AWS Glue, Polaris, etc.) with automatic namespace discovery and read/write support
Why it matters: Our Phase 1 external table limitations are eliminated—we can now write back to Glue Catalog-managed tables from Snowflake
Architecture impact: True bidirectional catalog federation; could unblock our "Glue as ingestion, Snowflake as analytics" hybrid
📖 CREATE Catalog-Linked Database Docs

Which Features Would Have Changed Our Decision?

Would have stayed with our choice (Iceberg + Horizon):

✅ Cross-region replication validates our bet on Snowflake-managed Iceberg (Delta still doesn't have this)
✅ Catalog-linked databases eliminate the Phase 1 external table pain without abandoning Glue ingestion

Bottom Line: Iceberg v3 Strengthens the "Safe Bet"

For SQL-first teams on Snowflake, Iceberg v3 eliminates the last major operational friction points we encountered. The combination of Horizon Catalog + v3 features delivers the "cloud-neutral with vendor optimization" balance we were seeking.

What's your biggest Iceberg-on-Snowflake headache? Drop it below 👇

Snowflake #ApacheIceberg #Lakehouse #DataEngineering #HorizonCatalog #DataArchitecture #AWS #OpenLakehouse

Part 4: Clone ++ Parallelization and Production Features

Krishna Tangudu — Thu, 30 Apr 2026 13:58:45 +0000

Previously: In Part 3, we automated permission management with dynamic RBAC provisioning.

In this post: Scale your cloning operations with parallel processing, add resume-from-failure capabilities, implement audit logging, and build production-grade orchestration.

The Performance Problem

Our repointing solution works, but doesn't scale:

-- Sequential processing (6 schemas)
CALL sp_repoint_schema('dev_db', 'prod_db', 'ADMIN');  -- 5 min
CALL sp_repoint_schema('dev_db', 'prod_db', 'INTEGRATION');     -- 8 min
CALL sp_repoint_schema('dev_db', 'prod_db', 'GOLD');       -- 12 min
CALL sp_repoint_schema('dev_db', 'prod_db', 'SILVER');            -- 10 min
CALL sp_repoint_schema('dev_db', 'prod_db', 'PLATINUM');       -- 7 min
CALL sp_repoint_schema('dev_db', 'prod_db', 'ARCHIVE');         -- 3 min

-- Total: 45 minutes ⏰

Problem: Each schema blocks the next. We're not using Snowflake's compute parallelism.

Solution: ASYNC/AWAIT Pattern

Snowflake's ASYNC and AWAIT keywords enable parallel execution:

-- Launch all schemas in parallel
ASYNC (CALL sp_repoint_schema('dev_db', 'prod_db', 'ADMINISTRATION'));
ASYNC (CALL sp_repoint_schema('dev_db', 'prod_db', 'INTEGRATION'));
ASYNC (CALL sp_repoint_schema('dev_db', 'prod_db', 'ANALYTICS'));
ASYNC (CALL sp_repoint_schema('dev_db', 'prod_db', 'DATA'));
ASYNC (CALL sp_repoint_schema('dev_db', 'prod_db', 'REPORTING'));
ASYNC (CALL sp_repoint_schema('dev_db', 'prod_db', 'ARCHIVE'));

-- Wait for all to complete
AWAIT ALL;

-- Result: ~12 minutes (limited by slowest schema)

Speedup: 45 minutes → 12 minutes = 73% faster

Parallel Architecture

High-Level Flow

SP_REPOINT_PARALLEL (Orchestrator)
├─ Get all schemas in clone
├─ For each schema:
│  └─ ASYNC (SP_REPOINT_SCHEMA_AND_LOG)
├─ AWAIT ALL
└─ Aggregate results

SP_REPOINT_SCHEMA_AND_LOG (Logging Wrapper)
├─ Call SP_REPOINT_SCHEMA
├─ Capture result
└─ Insert into temp results table

SP_REPOINT_SCHEMA (Worker)
├─ Repoint views
├─ Repoint procedures
├─ Repoint functions
├─ Repoint tasks
└─ Return JSON result

Orchestrator Pattern

-- Simplified orchestrator logic
CREATE OR REPLACE PROCEDURE sp_clone_repoint_parallel(
    clone_db VARCHAR, 
    source_db VARCHAR
)
AS
BEGIN
    -- Create temp table for results
    CREATE TEMP TABLE temp_repoint_results (
        schema_name VARCHAR,
        result VARIANT,
        completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
    );

    -- Get all schemas
    LET schema_rs RESULTSET := (
        SELECT SCHEMA_NAME 
        FROM clone_db.INFORMATION_SCHEMA.SCHEMATA 
        WHERE SCHEMA_NAME != 'INFORMATION_SCHEMA'
    );

    -- Launch parallel workers
    FOR rec IN schema_rs DO
        ASYNC (
            CALL sp_repoint_schema_and_log(:clone_db, :source_db, rec.SCHEMA_NAME)
        );
    END FOR;

    -- Wait for all workers
    AWAIT ALL;

    -- Aggregate results
    LET final_result := (
        SELECT OBJECT_CONSTRUCT(
            'parallel_schemas', COUNT(*),
            'total_duration_seconds', 
                DATEDIFF('second', MIN(completed_at), MAX(completed_at)),
            'schema_results', ARRAY_AGG(result)
        )
        FROM temp_repoint_results
    );

    RETURN :final_result;
END;

Why temp tables? ASYNC procedures can't return values directly. We collect results in a temp table visible to the orchestrator.

Usage

CALL sp_clone_repoint_parallel('DEV_PROJECT_DB', 'PRODUCTION_DB');

-- Result:
-- {
--   "parallel_schemas": 6,
--   "total_duration_seconds": 720,  -- 12 minutes
--   "schema_results": [
--     {"schema": "ADMINISTRATION", "views_fixed": 12, "procedures_fixed": 8},
--     {"schema": "ANALYTICS", "views_fixed": 98, "procedures_fixed": 42},
--     ...
--   ]
-- }

The Same Pattern for Streams

Parallel stream recreation follows identical architecture:

-- Orchestrator launches per-schema stream workers
CREATE OR REPLACE PROCEDURE sp_clone_recreate_streams_parallel(...)
AS
BEGIN
    CREATE TEMP TABLE temp_stream_results (...);

    FOR each schema:
        ASYNC (CALL sp_recreate_streams_schema_and_log(...));

    AWAIT ALL;

    RETURN aggregated_results;
END;

Resume-from-Failure: Step-Based Tracking

The Problem

Cloning is multi-step:

Delete old RBAC mappings
Clone database
Revoke production grants
Repoint objects
Recreate streams
Create new roles
Apply RBAC mappings
Transfer ownership
Suspend tasks
Validate clone

What happens if Step 5 fails? You don't want to start over!

The Solution: Step Logging

Track each step in a dedicated table:

CREATE TABLE clone_step_log (
    log_id NUMBER AUTOINCREMENT,
    audit_id NUMBER,          -- Links to clone_audit_log
    clone_db VARCHAR,
    step_number NUMBER,
    step_name VARCHAR,
    status VARCHAR DEFAULT 'PENDING',  -- PENDING, IN_PROGRESS, SUCCESS, FAILED
    result VARIANT,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    completed_at TIMESTAMP
);

Logging Pattern

// In master clone procedure
function executeStep(stepNum, stepName, stepFunction) {
    // Log step start
    execSQL(
        "INSERT INTO clone_step_log " +
        "(audit_id, clone_db, step_number, step_name, status) " +
        "VALUES (..., " + stepNum + ", '" + stepName + "', 'IN_PROGRESS')"
    );

    try {
        // Execute the step
        var result = stepFunction();

        // Log success
        execSQL(
            "UPDATE clone_step_log " +
            "SET status = 'SUCCESS', result = '" + JSON.stringify(result) + "', " +
            "    completed_at = CURRENT_TIMESTAMP() " +
            "WHERE step_number = " + stepNum + " AND status = 'IN_PROGRESS'"
        );

        return result;
    } catch (e) {
        // Log failure
        execSQL(
            "UPDATE clone_step_log " +
            "SET status = 'FAILED', result = OBJECT_CONSTRUCT('error', '" + e.message + "'), " +
            "    completed_at = CURRENT_TIMESTAMP() " +
            "WHERE step_number = " + stepNum + " AND status = 'IN_PROGRESS'"
        );
        throw e;  // Re-throw to abort remaining steps
    }
}

Resume Logic

// Master procedure signature
CREATE PROCEDURE sp_clone_create_master(
    clone_type VARCHAR,
    name_part1 VARCHAR,
    name_part2 VARCHAR DEFAULT NULL,
    resume_from_step FLOAT DEFAULT 0  -- 👈 Resume parameter
)

// Execution logic
var steps = [
    {num: 1, name: 'DELETE_RBAC', fn: deleteRBACMappings},
    {num: 2, name: 'CLONE_DATABASE', fn: cloneDatabase},
    {num: 3, name: 'REVOKE_GRANTS', fn: revokeGrants},
    {num: 4, name: 'REPOINT', fn: repointParallel},
    {num: 5, name: 'STREAMS', fn: recreateStreamsParallel},
    {num: 6, name: 'CREATE_ROLES', fn: createRoles},
    {num: 7, name: 'APPLY_RBAC', fn: applyRBAC},
    {num: 8, name: 'ICEBERG', fn: handleIceberg},
    {num: 9, name: 'SUSPEND_TASKS', fn: suspendTasks},
    {num: 10, name: 'VALIDATE', fn: validateClone}
];

for (var i = 0; i < steps.length; i++) {
    if (steps[i].num < resume_from_step) {
        continue;  // Skip this step
    }

    executeStep(steps[i].num, steps[i].name, steps[i].fn);
}

Resuming

-- Initial attempt fails at step 5
CALL sp_clone_create_master('PROJECT', 'ANALYTICS', 'DEV');
-- ERROR at step 5: Stream recreation failed

-- Check what happened
SELECT step_number, step_name, status, result
FROM clone_step_log
WHERE clone_db = 'DEV_ANALYTICS_DB'
ORDER BY step_number;

-- Fix the issue, then resume from step 5
CALL sp_clone_create_master('PROJECT', 'ANALYTICS', 'DEV', 5);
-- ✅ Steps 1-4 skipped, execution resumes from step 5

Key benefit: No need to wait another 30 minutes to re-clone. Just fix and resume.

Audit Logging: Observability

The Audit Table

CREATE TABLE clone_audit_log (
    audit_id NUMBER AUTOINCREMENT,
    clone_db VARCHAR NOT NULL,
    clone_type VARCHAR NOT NULL,      -- PROJECT, RELEASE
    action VARCHAR NOT NULL,          -- CREATE, DROP, UPDATE
    project_name VARCHAR,
    env_name VARCHAR,                 -- DEV, QA, STAGING
    source_db VARCHAR DEFAULT 'PRODUCTION_DB',
    status VARCHAR NOT NULL,          -- IN_PROGRESS, SUCCESS, FAILED
    error_msg VARCHAR(4000),
    created_by VARCHAR DEFAULT CURRENT_USER(),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    completed_at TIMESTAMP
);

Key Metrics

-- Clone success rate (last 30 days)
SELECT 
    COUNT(*) AS total_clones,
    SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS successful,
    ROUND(100.0 * successful / total_clones, 2) AS success_rate
FROM clone_audit_log
WHERE created_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())
AND action = 'CREATE';

-- Average duration by environment
SELECT 
    env_name,
    AVG(DATEDIFF('minute', created_at, completed_at)) AS avg_minutes
FROM clone_audit_log
WHERE status = 'SUCCESS' AND completed_at IS NOT NULL
GROUP BY env_name;

-- Most common failure points
SELECT 
    step_name,
    COUNT(*) AS failure_count
FROM clone_step_log
WHERE status = 'FAILED'
AND started_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY step_name
ORDER BY failure_count DESC;

Task Suspension: Cost Control

The Problem

Cloned databases inherit active tasks from production:

SHOW TASKS IN DATABASE dev_project_db;
-- Result: 23 tasks, STATE = 'started' ⚠️
-- Running hourly, daily, etc. in DEV!

Cost: $200-500/month per clone in wasted compute.

The Solution

// Auto-suspend all tasks in clone
function suspendTasks() {
    var schemas = getAllSchemas(cloneDb);
    var tasksSuspended = 0;

    for each schema:
        var tasks = execSQL("SHOW TASKS IN SCHEMA " + schema);

        for each task in tasks:
            if (task.state === 'started'):
                execSQL("ALTER TASK " + task.name + " SUSPEND");
                tasksSuspended++;

    return {tasks_suspended: tasksSuspended};
}

Integration: Add as Step 9 in clone pipeline.

Result: All tasks suspended by default in non-prod clones.

Iceberg Table Handling

Auto-Grant Volume Access

// Step 8: Handle Iceberg tables
function handleIcebergTables() {
    var icebergTables = execSQL(
        "SELECT DISTINCT external_volume " +
        "FROM information_schema.tables " +
        "WHERE table_type IN ('ICEBERG TABLE', 'DYNAMIC ICEBERG TABLE') " +
        "AND external_volume IS NOT NULL"
    );

    for each volume in icebergTables:
        execSQL("GRANT USAGE ON EXTERNAL VOLUME " + volume + " TO DATABASE " + cloneDb);
        volume_grants++;

    return {
        volume_grants_applied: volume_grants,
    };
}

Master Orchestration

Bringing it all together:

-- One command to rule them all
CALL sp_clone_create_master('PROJECT', 'ANALYTICS', 'DEV');

Behind the scenes:

Step 1: DELETE_RBAC_MAPPINGS      [3 sec]
Step 2: CLONE_DATABASE             [3 sec]  ← Snowflake native
Step 3: REVOKE_GRANTS              [45 sec]
Step 4: REPOINT_PARALLEL           [8 min]  ← ASYNC/AWAIT
Step 5: RECREATE_STREAMS_PARALLEL  [2 min]  ← ASYNC/AWAIT
Step 6: CREATE_ROLES               [1 min]
Step 7: APPLY_RBAC_MAPPINGS        [15 sec]
Step 8: HANDLE_ICEBERG             [5 sec]
Step 9: SUSPEND_TASKS              [10 sec]
Step 10: VALIDATE                  [30 sec]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Total: ~12 minutes

Result:
✅ Fully functional dev environment
✅ Correct permissions
✅ All references updated
✅ Streams recreated
✅ Tasks suspended
✅ Iceberg configured
✅ Validated and ready

Performance Tuning Tips

1. Warehouse Sizing

-- Use larger warehouse for parallel operations
ALTER WAREHOUSE clone_wh SET WAREHOUSE_SIZE = 'MEDIUM';

-- After clone completes
ALTER WAREHOUSE clone_wh SET WAREHOUSE_SIZE = 'SMALL';

2. Batch Processing

For 50+ schemas:

-- Process in batches to avoid overwhelming warehouse
-- Batch 1: Schemas 1-10
-- Batch 2: Schemas 11-20
-- etc.

3. Query Optimization

-- ❌ Scans entire database
SELECT * FROM information_schema.views 
WHERE view_definition ILIKE '%prod_db%';

-- ✅ Filter by schema first
SELECT * FROM information_schema.views 
WHERE table_schema = 'ANALYTICS'
AND view_definition ILIKE '%prod_db%';

Production Metrics: The Full Picture

Metric	Manual	Semi-Auto	Fully Automated
Time to clone	1-2 days	4-6 hours	8-12 minutes
Human intervention	Constant	Occasional	None
Error rate	15-20%	5-8%	<1%
Concurrent clones	1	2-3	10+
Resume capability	No	Partial	Full
Cost per clone	High	Medium	Low
Audit trail	Manual	Basic	Complete

Key Takeaways

Parallelization is essential - ASYNC/AWAIT delivers 73% speedup
Resume-from-failure saves hours - Step tracking enables smart recovery
Observability matters - Audit logs provide accountability and insights
Task suspension prevents waste - Auto-suspend saves $200-500/month per clone
Iceberg needs attention - External volumes and dynamic tables require special handling

What We've Built

Over this 4-part series, we created a production-grade cloning solution:

✅ Handles permissions - Dynamic RBAC provisioning

✅ Repoints references - All object types updated

✅ Recreates streams - With correct offsets

✅ Processes in parallel - 73% faster

✅ Resumes from failure - No starting over

✅ Logs everything - Complete observability

✅ Suspends tasks - Cost control

✅ Handles Iceberg - External volumes and dynamic tables

✅ Validates results - Health checks

One command:

CALL sp_clone_create_master('PROJECT', 'myproject', 'DEV');

Result: Fully functional dev environment in ~8 minutes.

Going Further

Clone Scheduling

-- Weekly QA refresh
CREATE TASK refresh_qa_clone
  SCHEDULE = 'USING CRON 0 6 * * 1 America/Los_Angeles'
AS
  CALL sp_clone_update('PROJECT', 'myproject', 'QA');

Self-Service UI

Build a web interface for teams to request/manage clones.

Data Masking

Apply dynamic masking policies after cloning for PII protection.

Cost Tracking

Tag clones with cost centers for chargeback.

Auto-Expiration

Drop clones after N days to control costs.

Resources

Code Repository: github.com/LALITHASWAROOPK/snowflake_cloning

Blog Series:

- Part 4: Parallelization and Production Features (this post)

Did this help? Star the repo and share with your team!

Questions? Open an issue on GitHub or comment below.

Previous: Part 3: Solving Permissions and RBAC in Cloned Databases

Series Start: Introduction and Overview

Part 3: Solving Permissions and RBAC in Cloned Databases

Krishna Tangudu — Tue, 28 Apr 2026 14:51:22 +0000

Previously: In Part 2, we fixed all the broken database references. But even with correct references, you still can't access anything without proper permissions!

In this post: Learn how to programmatically manage permissions in cloned databases with dynamic role creation, ownership transfers, and automated RBAC provisioning.

The Permission Problem (Recap)

After cloning:

CREATE DATABASE dev_project_db CLONE production_db;
SHOW GRANTS ON DATABASE dev_project_db;

Result:

| privilege | grantee_name   |
|-----------|----------------|
| OWNERSHIP | PROD_ADMIN     |  ⚠️ Wrong environment!
| USAGE     | PROD_READ_ONLY |  ⚠️ Dev needs different roles

This creates three immediate problems:

Wrong Roles - Production roles shouldn't access dev
No Access - Dev team roles aren't granted anything
Ownership Lock - Can't modify without PROD_ADMIN privileges

The Solution: Four-Stage Automation

Our approach automates permission management in four stages:

Stage 1: Temporary Ownership Transfer
   ↓
Stage 2: Permission Cleanup
   ↓
Stage 3: Dynamic Role Creation
   ↓
Stage 4: RBAC Mapping Application

Let's dive into each.

Stage 1: Temporary Ownership Transfer

First, we need control. Grant temporary ownership to a privileged service account:

// Pseudocode for understanding
for each schema in clone_database:
    GRANT OWNERSHIP ON SCHEMA to SERVICE_ROLE COPY CURRENT GRANTS

    for each object_type in [TABLES, VIEWS, PROCEDURES, ...]:
        GRANT OWNERSHIP ON ALL object_type to SERVICE_ROLE COPY CURRENT GRANTS

Key insight: COPY CURRENT GRANTS preserves existing permissions while changing ownership.

Usage

CALL sp_grant_temp_ownership('dev_project_db');

-- Result:
-- {
--   "schemas_granted": 4,
--   "objects_transferred": 1250,
--   "errors": []
-- }

Now we have control to make changes.

Stage 2: Permission Cleanup

Revoke production-specific grants, especially future grants that auto-apply to new objects:

-- Example: Revoke future ownership from production roles
REVOKE OWNERSHIP ON FUTURE TABLES IN SCHEMA dev_db.analytics 
  FROM ROLE PROD_ANALYTICS_OWNER;

For multiple schemas and object types, we automate this:

// Pseudocode
for each schema in schemas:
    prod_role = source_database + schema_owner_suffix

    for each object_type in [TABLES, VIEWS, PROCEDURES, ...]:
        REVOKE OWNERSHIP ON FUTURE object_type IN SCHEMA 
          FROM ROLE prod_role

Stage 3: Dynamic Role Creation

Instead of hardcoding role names, we generate them dynamically based on:

Database name (environment-specific)
Schema name (domain-specific)
Access level (READ, READ_WRITE, ADMIN)

The Pattern

<DATABASE>_<SCHEMA>_<LEVEL>

Examples:
DEV_PROJECT_DB_ANALYTICS_READ
DEV_PROJECT_DB_ANALYTICS_READ_WRITE
DEV_PROJECT_DB_DATA_ADMIN

The Logic

// Simplified version for understanding
for each schema in schemas:
    for each access_level in [READ, READ_WRITE, ADMIN]:
        role_name = clone_db + "_" + schema + "_" + access_level

        CREATE ROLE IF NOT EXISTS role_name
        GRANT USAGE ON DATABASE to role_name
        GRANT USAGE ON SCHEMA to role_name

        if (access_level == READ):
            GRANT SELECT ON ALL TABLES to role_name
            GRANT SELECT ON FUTURE TABLES to role_name
        else if (access_level == READ_WRITE):
            GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES to role_name
        else if (access_level == ADMIN):
            GRANT ALL ON SCHEMA to role_name

Why this scales: From 3 schemas to 300, the pattern stays the same.

Full implementation: sql/04_clone_rbac.sql#L97-L185

Example Usage

CALL sp_create_clone_roles(
    'DEV_PROJECT_DB',
    ARRAY_CONSTRUCT('ADMINISTRATION', 'ANALYTICS', 'DATA')
);

-- Result:
-- {
--   "roles_created": [
--     "DEV_PROJECT_DB_ADMINISTRATION_READ",
--     "DEV_PROJECT_DB_ADMINISTRATION_READ_WRITE",
--     "DEV_PROJECT_DB_ADMINISTRATION_ADMIN",
--     "DEV_PROJECT_DB_ANALYTICS_READ",
--     "DEV_PROJECT_DB_ANALYTICS_READ_WRITE",
--     "DEV_PROJECT_DB_ANALYTICS_ADMIN",
--     "DEV_PROJECT_DB_DATA_READ",
--     "DEV_PROJECT_DB_DATA_READ_WRITE",
--     "DEV_PROJECT_DB_DATA_ADMIN"
--   ],
--   "total_created": 9
-- }

Stage 4: RBAC Mapping

The final piece: map clone-specific roles to functional roles that users actually have:

CLONE ROLE                           FUNCTIONAL ROLE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DEV_PROJECT_DB_ANALYTICS_READ   →    DATA_ANALYST_ROLE
DEV_PROJECT_DB_ANALYTICS_WRITE  →    DATA_ENGINEER_ROLE  
DEV_PROJECT_DB_ANALYTICS_ADMIN  →    PROJECT_ADMIN_ROLE

Configuration Table

Store mappings in a table (not hardcoded):

CREATE TABLE rbac_mapping (
    schema_role VARCHAR(255),         -- Clone-specific role
    environment VARCHAR(40),           -- DEV, QA, STAGING
    functional_role VARCHAR(255),     -- User's actual role
    operation VARCHAR(40),            -- GRANT or REVOKE
    execute_indicator VARCHAR DEFAULT 'Y'
);

-- Example mappings with placeholder
INSERT INTO rbac_mapping (schema_role, environment, functional_role, operation)
VALUES 
    ('{{CLONE_DB}}_ANALYTICS_READ', 'DEV', 'DATA_ANALYST_ROLE', 'GRANT'),
    ('{{CLONE_DB}}_ANALYTICS_WRITE', 'DEV', 'DATA_ENGINEER_ROLE', 'GRANT'),
    ('{{CLONE_DB}}_ANALYTICS_ADMIN', 'DEV', 'PROJECT_ADMIN_ROLE', 'GRANT');

Note: {{CLONE_DB}} is replaced dynamically at runtime.

The Mapping Logic

// Pseudocode
mappings = SELECT * FROM rbac_mapping WHERE environment = target_env

for each mapping in mappings:
    schema_role = mapping.schema_role.replace('{{CLONE_DB}}', actual_clone_db)

    if mapping.operation == 'GRANT':
        GRANT ROLE schema_role TO ROLE mapping.functional_role
    else:
        REVOKE ROLE schema_role FROM ROLE mapping.functional_role

Full implementation: sql/04_clone_rbac.sql#L187-L245

Result

CALL sp_apply_rbac_mapping('DEV_PROJECT_DB', 'DEV');

-- Now developers can use their normal roles:
USE ROLE DATA_ANALYST_ROLE;
SELECT * FROM dev_project_db.analytics.customer_summary;  -- ✅ Works!

Putting It All Together

Complete permission setup in one command:

CALL sp_setup_clone_permissions(
    clone_db => 'DEV_PROJECT_DB',
    source_db => 'PRODUCTION_DB',
    environment => 'DEV'
);

-- Behind the scenes:
-- ✅ Temporary ownership transferred
-- ✅ Production grants revoked
-- ✅ 9 new environment-specific roles created
-- ✅ RBAC mappings applied
-- ✅ Developers have appropriate access

Key Design Principles

1. Configuration Over Code

-- ❌ Hardcoded in procedures
GRANT ROLE dev_analytics_read TO ROLE data_analyst;

-- ✅ Configuration-driven
INSERT INTO rbac_mapping VALUES (...);
CALL sp_apply_rbac_mapping(...);

Benefits:

Non-developers can manage permissions
Different mappings per environment
Audit trail of changes

2. Dynamic Role Generation

// This scales from 3 schemas to 300
role_name = database + "_" + schema + "_" + access_level

3. Temporary Ownership Pattern

User requests clone
   ↓
Service role takes ownership
   ↓
Service role makes all changes
   ↓
Service role transfers ownership to target roles

Never make permission changes as the requesting user.

4. Future Grants Are Critical

-- ❌ Only current objects
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE analyst;

-- ✅ Current AND future objects
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics TO ROLE analyst;

Common Pitfalls (And How We Avoid Them)

Pitfall 1: Not Using COPY CURRENT GRANTS

-- ❌ Drops all existing grants during ownership transfer
GRANT OWNERSHIP ON SCHEMA my_schema TO ROLE new_owner;

-- ✅ Preserves grants during transfer
GRANT OWNERSHIP ON SCHEMA my_schema TO ROLE new_owner COPY CURRENT GRANTS;

Our code: Always uses COPY CURRENT GRANTS

Pitfall 2: Forgetting Object Types

Don't forget:

DYNAMIC TABLES
ICEBERG TABLES
EVENT TABLES
STAGES, FILE FORMATS, SEQUENCES

Our code: Comprehensive object type list in sql/04_clone_rbac.sql

Pitfall 3: Not Handling Missing Roles

// ❌ Fails if role doesn't exist
execSQL("GRANT ROLE clone_role TO ROLE functional_role");

// ✅ Graceful error handling (in our procedures)
try {
    execSQL("GRANT ROLE ...");
    success_count++;
} catch (e) {
    errors.push({error: e.message});
}

Our code: Try-catch blocks around all grant operations

Production Metrics

After implementing automated RBAC:

Metric	Before	After
Time to grant permissions	45-90 min	< 2 min
Permission errors per clone	8-12	0-1
Security audit failures	4/mo	0/mo
Developer self-service	No	Yes
Concurrent clone setup	1 at a time	10+ parallel

What's Next?

We've solved both major challenges:

✅ Database reference repointing (Part 2)
✅ Permissions and RBAC (Part 3)

But our solution still processes schemas sequentially. In Part 4, we'll make it production-ready with:

Parallel processing with ASYNC/AWAIT (73% faster)
Resume-from-failure capabilities
Audit logging and observability
Task suspension for cost control
Production-grade orchestration

Next: Part 4: Parallelization and Production Features →
Previous: Part 2: Repointing Database References

About This Series

This is Part 3 of a 4-part series on production-grade Snowflake database cloning. All code is available in the GitHub repository with complete documentation and examples.

Part 2: Snowflake Clone++: Repointing Database References and Recreating Streams

Krishna Tangudu — Fri, 24 Apr 2026 15:40:39 +0000

Previously: In Part 1, we saw how zero-copy cloning is revolutionary but leaves us with broken references everywhere.

In this post: Learn how to find and fix hardcoded database references in views, stored procedures, functions, tasks, and Iceberg tables — plus how to recreate streams that broke during cloning.

The Reference Problem

Your cloned database has perfect permissions, but nothing works:

-- Try to query a view in the clone
SELECT * FROM dev_project_db.analytics.customer_metrics;

-- Error: Object 'PRODUCTION_DB.SILVER.CUSTOMERS' does not exist

The view definition is still pointing to production:

SELECT GET_DDL('VIEW', 'dev_project_db.analytics.customer_metrics');

-- Result:
CREATE OR REPLACE VIEW dev_project_db.analytics.customer_metrics AS
SELECT 
    customer_id,
    COUNT(*) as order_count
FROM production_db.silver.customers  -- ⚠️ Wrong database!
GROUP BY customer_id;

The code was copied exactly as-is. Every database reference needs updating.

The Challenge: Finding All References

Database references hide everywhere:

1. Views (The Easy Ones)

SELECT table_name, view_definition
FROM dev_project_db.information_schema.views
WHERE view_definition ILIKE '%production_db%';
-- Result: 186 views need fixing

2. Stored Procedures (The Tricky Ones)

SELECT procedure_name, procedure_definition
FROM dev_project_db.information_schema.procedures
WHERE procedure_definition ILIKE '%production_db%';
-- Problem: JavaScript, SQL, Python, Scala, Java...

3. Functions

SELECT function_name, function_definition
FROM dev_project_db.information_schema.functions
WHERE function_definition ILIKE '%production_db%';

4. Tasks (The Sneaky Ones)

SELECT name, definition
FROM dev_project_db.information_schema.tasks
WHERE definition ILIKE '%production_db%';
-- Tasks can call procedures that call views...

5. Streams (The Broken Ones)

SHOW STREAMS IN DATABASE dev_project_db;
-- Result: Every stream shows STALE = TRUE

Strategy: GET_DDL + String Replacement

Our battle plan:

Identify objects with stale references (INFORMATION_SCHEMA)
Extract full DDL using GET_DDL() function
Replace all occurrences of source database with clone database
Execute updated DDL to recreate the object

Simple concept, but devils in the details.

Repointing Views

Views are straightforward because their definitions are directly accessible:

// Conceptual flow
var viewRS = execSQL(
    "SELECT TABLE_NAME, VIEW_DEFINITION " +
    "FROM clone_db.INFORMATION_SCHEMA.VIEWS " +
    "WHERE TABLE_SCHEMA = '" + schema + "' " +
    "AND VIEW_DEFINITION ILIKE '%source_db%'"
);

while (viewRS.next()) {
    var viewDef = viewRS.getColumnValue(2);

    // Replace database references (case-insensitive)
    var newDef = viewDef
        .split(sourceDb).join(cloneDb)
        .split(sourceDb.toLowerCase()).join(cloneDb.toLowerCase());

    // Execute updated DDL
    execSQL(newDef);
    views_fixed++;
}

Why this works:

VIEW_DEFINITION contains full CREATE statement
Simple string replacement updates all references
Re-executing DDL replaces the view atomically

Full implementation: sql/02_clone_repoint.sql#L75-L130

Repointing Procedures (The Hard Part)

Procedures are tricky because:

INFORMATION_SCHEMA truncates long definitions
Must use GET_DDL() for full text
Must handle parameter signatures correctly

The Parameter Signature Problem

-- INFORMATION_SCHEMA shows:
PROCEDURE_NAME: calculate_metrics
ARGUMENT_SIGNATURE: (start_date DATE, end_date DATE, customer_id NUMBER)

-- But GET_DDL requires type-only signature:
SELECT GET_DDL('PROCEDURE', 'schema.calculate_metrics(DATE, DATE, NUMBER)');
--                                                      ^^^^ Types only!

We must parse signatures to extract just the types.

Parsing Logic

// Convert "(start_date DATE, end_date DATE)" 
// To: "(DATE, DATE)"
function stripParamNames(sig) {
    if (!sig || sig.trim() === "()") return "()";

    var params = sig.replace(/[()]/g, "").split(",");
    var types = [];

    for (var i = 0; i < params.length; i++) {
        var parts = params[i].trim().split(/\s+/);
        // "start_date DATE" → ["start_date", "DATE"]
        types.push(parts.slice(1).join(" ")); // Take type part
    }

    return "(" + types.join(", ") + ")";
}

The Repointing Flow

// Get procedures that reference source database
var procRS = execSQL(
    "SELECT PROCEDURE_NAME, ARGUMENT_SIGNATURE " +
    "FROM clone_db.INFORMATION_SCHEMA.PROCEDURES " +
    "WHERE PROCEDURE_DEFINITION ILIKE '%source_db%'"
);

while (procRS.next()) {
    var procName = procRS.getColumnValue(1);
    var procSig = procRS.getColumnValue(2);
    var typeSig = stripParamNames(procSig);  // Key step!

    // Get full DDL with correct signature
    var ddl = execSQL(
        "SELECT GET_DDL('PROCEDURE', '" + 
        cloneDb + "." + schema + "." + procName + typeSig + "')"
    );

    // Replace database references
    var newDDL = ddl
        .split(sourceDb).join(cloneDb)
        .split(sourceDb.toLowerCase()).join(cloneDb.toLowerCase());

    // Recreate procedure
    execSQL(newDDL);
    procedures_fixed++;
}

Full implementation: sql/02_clone_repoint.sql#L132-L195

Repointing Functions

Functions work exactly like procedures (same signature challenge):

// Same pattern as procedures
// 1. Find functions with stale references
// 2. Strip parameter names from signatures
// 3. Get full DDL using GET_DDL('FUNCTION', ...)
// 4. Replace database references
// 5. Execute updated DDL

Full implementation: sql/02_clone_repoint.sql#L197-L250

Repointing Tasks

Tasks have an additional requirement: SUSPEND first.

// Get tasks that reference source database
var taskRS = execSQL(
    "SELECT NAME FROM clone_db.INFORMATION_SCHEMA.TASKS " +
    "WHERE DEFINITION ILIKE '%source_db%'"
);

while (taskRS.next()) {
    var taskName = taskRS.getColumnValue(1);

    // IMPORTANT: Suspend before modifying
    execSQL("ALTER TASK " + taskName + " SUSPEND");

    // Get DDL, replace references, recreate
    var taskDDL = execSQL("SELECT GET_DDL('TASK', '" + taskName + "')");
    var newTaskDDL = taskDDL.split(sourceDb).join(cloneDb);
    execSQL(newTaskDDL);

    tasks_fixed++;
    // Tasks remain SUSPENDED (intentional for non-prod)
}

Key Point: Tasks remain SUSPENDED after repointing. Perfect for dev/test environments.

Full implementation: sql/02_clone_repoint.sql#L252-L295

Recreating Streams (The Special Case)

Streams can't be "repointed" — they must be dropped and recreated.

Why Streams Are Different

When you clone:

Production Stream                 Cloned Stream (BROKEN)
  ├─ Tracks: prod_db.data.orders    ├─ Still tracks: prod_db.data.orders ⚠️
  ├─ Offset: Transaction 12,456     ├─ Offset: LOST ⚠️
  └─ Status: Current                └─ Status: STALE ⚠️

The stream object clones but:

Still tracks the source table in production
Loses its offset position
Becomes immediately STALE

Recreation Flow

// Get all streams in schema
execSQL("SHOW STREAMS IN SCHEMA " + cloneDb + "." + schema);
var streamRS = execSQL(
    'SELECT "name", "base_tables", "stale" ' +
    'FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))'
);

while (streamRS.next()) {
    var streamName = streamRS.getColumnValue(1);

    // Get stream DDL
    var ddl = execSQL(
        "SELECT GET_DDL('STREAM', '" + 
        cloneDb + "." + schema + "." + streamName + "')"
    );

    // Replace database references
    var newDDL = ddl.split(sourceDb).join(cloneDb);

    // Drop and recreate
    execSQL("DROP STREAM IF EXISTS " + streamName);
    execSQL(newDDL);

    streams_recreated++;
}

Full implementation: sql/03_clone_streams.sql#L65-L140

Important: Stream Offset Behavior

-- Original stream (in production)
CREATE STREAM prod_stream ON TABLE production_db.data.customers;
-- Has been tracking changes for weeks, offset at transaction 12345

-- After recreating in clone
CREATE STREAM dev_stream ON TABLE dev_db.data.customers;
-- Offset starts at CURRENT_TIMESTAMP (no historical changes)

Implication: Cloned streams don't have historical change data. They start fresh.

Validation: Ensuring Everything Worked

After repointing, verify the clone is healthy:

// Check for views still referencing production
var staleViews = execSQL(
    "SELECT TABLE_SCHEMA, TABLE_NAME " +
    "FROM clone_db.INFORMATION_SCHEMA.VIEWS " +
    "WHERE VIEW_DEFINITION ILIKE '%source_db%'"
);

// Check for procedures still referencing production
var staleProcs = execSQL(
    "SELECT PROCEDURE_SCHEMA, PROCEDURE_NAME " +
    "FROM clone_db.INFORMATION_SCHEMA.PROCEDURES " +
    "WHERE PROCEDURE_DEFINITION ILIKE '%source_db%'"
);

// Check for stale streams
var staleStreams = execSQL(
    "SHOW STREAMS WHERE stale = 'true' " +
    "OR base_tables ILIKE '%source_db%'"
);

// Generate report
return {
    status: (staleViews.count + staleProcs.count + staleStreams.count === 0) 
        ? 'PASS' : 'WARN',
    stale_views: staleViews.count,
    stale_procedures: staleProcs.count,
    stale_streams: staleStreams.count
};

Full implementation: sql/02_clone_repoint.sql#L297-L365

Usage

CALL sp_validate_clone('DEV_PROJECT_DB', 'PRODUCTION_DB');

-- Result:
-- {
--   "status": "PASS",
--   "stale_views": 0,
--   "stale_procedures": 0,
--   "stale_streams": 0
-- }

Performance: Sequential vs Parallel

For large databases with many schemas:

Approach	Time for 6 schemas
Sequential (one at a time)	~45 minutes
Parallel (ASYNC/AWAIT)	~8 minutes

We'll cover parallelization in detail in Part 4.

Common Gotchas

1. Case Sensitivity

// ❌ Won't catch lowercase references
var newDDL = ddl.split(sourceDb).join(cloneDb);

// ✅ Handle both cases
var newDDL = ddl.split(sourceDb).join(cloneDb)
                .split(sourceDb.toLowerCase()).join(cloneDb.toLowerCase());

2. Partial Matches

// If source_db = "PROD"
// This accidentally replaces "PRODUCTION_TABLE" → "DEV_DUCTION_TABLE"

// Solution: Use specific database names or word boundaries

3. Hardcoded Strings in Procedures

// This won't be caught by simple string replacement
CREATE PROCEDURE my_proc() AS
$$
    var db = "PRODUCTION_DB";  // Hardcoded!
    snowflake.execute({sqlText: "SELECT * FROM " + db + ".schema.table"});
$$;

Best practice: Use parameters or configuration tables, not hardcoded strings.

Handling Iceberg Tables

Iceberg tables introduce additional complexity when repointing because they reference external volumes.

The Challenge

After cloning, Iceberg tables still point to production external volumes:

-- Check Iceberg table after cloning
SHOW ICEBERG TABLES IN dev_db;
-- EXTERNAL_VOLUME: prod_iceberg_volume ⚠️

-- Try to query
SELECT * FROM dev_db.data.events_iceberg;
-- Error: Database DEV_DB does not have READ access to EXTERNAL VOLUME 'prod_iceberg_volume'

Solution: Grant Volume Access

// During repoint, identify and grant Iceberg volume access
var icebergTables = execSQL(
    "SELECT DISTINCT external_volume " +
    "FROM clone_db.INFORMATION_SCHEMA.TABLES " +
    "WHERE table_type IN ('ICEBERG TABLE', 'DYNAMIC ICEBERG TABLE') " +
    "AND external_volume IS NOT NULL"
);

for each volume in icebergTables:
    execSQL("GRANT READ ON EXTERNAL VOLUME " + volume + " TO DATABASE " + clone_db);

Security consideration: Dev environment now has read access to production Iceberg storage. This is usually acceptable for clones since:

They share the same data anyway (zero-copy)
Cost tracking separates dev/prod compute
Any writes are isolated to a dedicated storage location and do not interfere with production data.

Dynamic Iceberg Tables

Dynamic Iceberg tables lose their "dynamic" status after cloning:

// Flag dynamic Iceberg tables for manual review
var dynamicIceberg = execSQL(
    "SELECT table_schema, table_name " +
    "FROM clone_db.INFORMATION_SCHEMA.TABLES " +
    "WHERE table_type = 'DYNAMIC ICEBERG TABLE'"
);

// Log warning: These tables exist but won't refresh automatically
// Must be recreated or converted to static if needed in clone

check known limitations here:

Production Metrics

After implementing automated repointing:

Metric	Before (Manual)	After (Automated)
Time to repoint 6 schemas	8-12 hours	8 minutes (parallel)
Missed references	10-15 per clone	0-1 per clone
Failed views	5-8	0
Human errors	Several per clone	None
Success rate	80-85%	99%+

What's Next?

We've now solved the most visible problem:

✅ Database reference repointing
✅ Stream recreation
✅ Iceberg table handling

But even with all references fixed, you still can't access anything without proper permissions! In Part 3, we'll tackle:

Permission management - Dynamic role creation
RBAC automation - Configuration-driven grants
Ownership transfers - Breaking free from production roles

Then in Part 4:

Parallel processing with ASYNC/AWAIT (73% faster)
Resume-from-failure capabilities
Production-grade orchestration

Next: Part 3: Solving Permissions and RBAC
Previous: Part 1: The Problem and the Promise

About This Series

This is Part 2 of a 4-part series on production-grade Snowflake database cloning. All code is available in the GitHub repository with complete documentation and examples.

Part 1: Understanding Snowflake Cloning and why we need Clone++

Krishna Tangudu — Fri, 24 Apr 2026 15:35:20 +0000

Mastering Snowflake Database Cloning: A Production Guide

A 4-part series exploring enterprise-scale Snowflake database cloning with real-world solutions for permissions, parallel processing, and automation.

Why This Series?

Snowflake's zero-copy cloning is one of its most powerful features—but getting from a simple CREATE DATABASE ... CLONE command to a production-grade cloning system requires solving numerous challenges that aren't obvious until you try.

This series shares battle-tested patterns from managing 20+ database clones .

What You'll Learn

Why simple cloning fails in production environments
How to handle permissions and RBAC across environments
Strategies for updating database references at scale
Parallel processing techniques that deliver 73% performance improvements
Resume-from-failure capabilities for reliability
Production-grade observability and cost controls

Part 1: The Problem and the Promise

The Zero-Copy Clone Revolution

Picture this: You need a complete copy of your 2TB production database for testing. In traditional databases, this means:

Hours or days of data export/import
2TB of additional storage costs immediately
Expensive backup windows impacting production
Stale data by the time the copy finishes
Risk of impacting production performance

Snowflake changed everything with zero-copy cloning:
Note: This only works within same snowflake account

CREATE DATABASE dev_db CLONE production_db;

Three seconds later, you have a complete copy. Let that sink in: few seconds for 2TB.

The Magic: How Zero-Copy Works

Unlike traditional databases that copy data blocks, Snowflake clones share the underlying data files:

Production DB                     Dev DB (Clone)
  ├─ Table metadata ───────────────> Table metadata (new)
  ├─ Data files ←──────────────────── Points to same files
  └─ Micro-partitions                 (shared, not copied)

Key benefits:

✅ Instant creation - Metadata operation, not data copy

✅ Zero initial storage cost - Only pays for changes (deltas)

✅ No performance impact - No data movement from production

✅ Perfect point-in-time copy - Exact snapshot at clone time

✅ Independent evolution - Changes don't affect each other

Real-World Value

Use Case 1: Dev/Test Environments

Before Snowflake cloning:

Setup time: 2-3 days
Storage cost: $150/month per environment
Data freshness: 1-2 weeks old
Manual effort: 6+ hours per refresh

After Snowflake cloning:

Setup time: 8 minutes (automated)
Storage cost: ~$10/month (only deltas)
Data freshness: Real-time (clone anytime)
Manual effort: Zero

Use Case 2: Release Testing

We maintain parallel release environments:

-- Create isolated environment for Q2 release testing
CREATE DATABASE release_q2_2026 CLONE production_db;

-- Test new features without impacting production or other releases
-- Drop when release is complete - zero long-term storage cost

Use Case 3: Data Science Sandboxes

Data scientists can experiment freely:

-- Personal sandbox for ML model development
CREATE DATABASE ds_sarah_experiment CLONE production_db;

-- Try new transformations, test hypotheses, break things
-- Drop when done - total freedom, zero risk

Use Case 4: Incident Investigation

Production issue at 2 AM:

-- Clone production state at incident time
CREATE DATABASE incident_20260424_02am CLONE production_db AT(TIMESTAMP => '2026-04-24 02:00:00');

-- Investigate in isolated environment
-- No risk of further production impact
-- Preserve exact state for forensics

The Numbers: ROI of Cloning

For a 2TB database, comparing traditional vs Snowflake cloning:

Metric	Traditional Approach	Snowflake Clone
Initial copy time	8-24 hours	3 seconds
Storage cost (month 1)	$300	$0
Storage cost (month 3)	$300	$45 (15% changed)
Setup automation effort	High (complex)	Low (simple SQL)
Refresh frequency	Weekly	Daily/on-demand
Number of concurrent envs	2-3 (cost prohibitive)	10+ (economical)
Risk to production	High (load impact)	None (metadata only)

Annual savings per clone: ~$2,500 in storage + thousands in engineering time

The Wake-Up Call: When Reality Hits

So we eagerly created our first production clone:

CREATE DATABASE dev_project_db CLONE production_db;
-- ✅ Success! (3 seconds)

Excited, we told the dev team their environment was ready.

Five minutes later:

"Everything is broken. We can't access anything."

"The views are reading production data!"

"Streams are all stale."

"Why are tasks running in dev?"

Three days of troubleshooting revealed the harsh truth: Zero-copy cloning is only 10% of the solution.

Problem 1: Your "Dev" Database Is Secretly Reading Production

-- Check a simple view in the clone
SELECT GET_DDL('VIEW', 'dev_project_db.analytics.customer_summary');

Result:

CREATE OR REPLACE VIEW dev_project_db.analytics.customer_summary AS
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as order_count
FROM production_db.silver.customers c        -- ⚠️ Still reading PRODUCTION!
JOIN production_db.silver.orders o           -- ⚠️ Still reading PRODUCTION!
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

The problem:

Views hardcode database names
Procedures execute against production
Functions reference production tables
Tasks trigger production workflows

Out of 280 views, 186 had hardcoded production references. Our "dev" database was actually a production read client.

Impact: A developer testing a new ETL procedure almost deleted production data.

Problem 2: Streams Are Dead

-- Check stream health
SHOW STREAMS IN DATABASE dev_project_db;

Result: Every stream showed STALE = TRUE

Why?

Production Stream                 Cloned Stream (BROKEN)
  ├─ Tracks: prod_db.data.orders    ├─ Still tracks: prod_db.data.orders ⚠️
  ├─ Offset: Transaction 12,456     ├─ Offset: LOST ⚠️
  └─ Status: Current                └─ Status: STALE ⚠️

Streams don't get "repointed" during cloning. They still reference the source table and lose their offset position.

Impact: 47 CDC pipelines broken, 3 hours to identify and recreate all streams.

Problem 3: The Iceberg Table Trap

Our newest challenge: We use Iceberg tables managed by Snowflake for high-volume data.

-- Try to clone database with Iceberg tables
CREATE DATABASE dev_project_db CLONE production_db;
-- ✅ Success

-- But check the Iceberg tables
SELECT * FROM dev_project_db.data.events_iceberg;
-- ❌ Error: Cannot access external volume 'prod_iceberg_volume'

The Iceberg gotchas:

Dynamic Iceberg Tables Don't Clone

-- Production has dynamic Iceberg table
CREATE DYNAMIC ICEBERG TABLE production_db.data.streaming_events
  TARGET_LAG = '1 minute'
  EXTERNAL_VOLUME = 'prod_iceberg_volume'
  CATALOG = 'iceberg_catalog'
AS
  SELECT * FROM stream_source;

-- After cloning
SHOW DYNAMIC TABLES IN dev_project_db.data;
-- Result: Table exists but is NOT dynamic anymore ⚠️
-- Must be recreated as dynamic or converted to static

External Volumes Need Cross-Database Access

Even static Iceberg tables have a challenge:

-- Cloned Iceberg table still references production external volume
SHOW ICEBERG TABLES IN dev_project_db;
-- EXTERNAL_VOLUME: prod_iceberg_volume ⚠️

-- Must grant clone access to production volume
GRANT READ ON EXTERNAL VOLUME prod_iceberg_volume 
  TO DATABASE dev_project_db;

Security implication: Dev environment now has read access to production Iceberg storage. Not ideal, but necessary unless you:

Copy Iceberg data to dev storage (expensive, slow)
Create Iceberg tables as regular tables in clones (losesmeta benefits)

Impact: 2 additional hours per clone for Iceberg table handling, ongoing security audit concerns.

Problem 4: Tasks Running Wild and failing

Our production database had 23 scheduled tasks:

Hourly metric refreshes
Daily aggregations
Real-time alert processing

The clone inherited all these tasks—and they started executing!

SHOW TASKS IN DATABASE dev_project_db;
-- Result: 23 tasks, STATE = 'started' ⚠️

Consequences:

Tasks trying to write to production (errors)
Unnecessary compute costs (~$240/month per clone)
Alerts flooding wrong channels
Confusion about which environment is which

Impact: $240 wasted in first month before we noticed, plus several false production alerts.

Problem 5: Developers Locked Out of Their Own Database

-- Check permissions after cloning
SHOW GRANTS ON DATABASE dev_project_db;

Result:

| privilege | grantee_name      |
|-----------|-------------------|
| OWNERSHIP | PROD_ADMIN_ROLE   |  ⚠️ Production role!
| USAGE     | PROD_READ_ONLY    |  ⚠️ Production role!
| MONITOR   | PROD_DBA_ROLE     |  ⚠️ Production role!

The problem:

Clone inherited all production role grants
Dev team roles aren't granted anything
Can't modify permissions without production admin role
Production roles shouldn't exist in dev environment

Impact: Developers locked out of their own database for 4 hours while we manually fixed 450+ object permissions.

The Real Cost of "Simple" Cloning

Let's tally what a "3-second clone" actually cost us:

Issue	Time to Fix	Compute Cost	Risk
Manual permission fixes	4 hours	-	High
Finding/fixing view references	6 hours	-	Critical
Troubleshooting procedures	8 hours	-	Critical
Recreating streams	3 hours	-	Medium
Disabling errant tasks	2 hours	$240	Low
Iceberg table handling	2 hours	-	Medium (security)
Total	25 person-hours	$240	Multiple critical risks

And this was for one clone. We needed:

8 project teams × 3 environments (DEV, QA, STAGING) = 24 clones
3 release testing environments
Ad-hoc data science sandboxes

At scale, manual cloning wasn't sustainable.

Why Does This Happen?

Snowflake's clone operation does exactly what it promises: physically copy metadata. It clones:

✅ Table structures and data pointers

✅ View definitions (as-is, with all hardcoded references)

✅ Stored procedure code (as-is, with all hardcoded references)

✅ Function definitions (unchanged)

✅ Stream objects (but not their offset state)

✅ Task objects (including schedules and state!)

✅ Grants and ownership (exactly as they were)

✅ Iceberg table metadata (but not dynamic status)

It does NOT:

❌ Rewrite database references in code

❌ Adjust role assignments for target environment

❌ Fix stream offsets or source references

❌ Suspend tasks for non-prod use

❌ Handle Iceberg external volume permissions

❌ Validate that everything works in new context

Cloning is metadata surgery, not environment provisioning.

What We Needed: Clone++

Take the incredible power of zero-copy cloning and add:

Permission Management → Dynamically create and assign environment-appropriate roles
Reference Repointing → Find and fix ALL database references automatically
Stream Recreation → Drop and recreate streams with updated references
Task Control → Suspend tasks in non-production clones
Iceberg Handling → Manage external volumes and dynamic table conversions
Validation → Verify the clone is actually usable
Observability → Track operations, failures, and health
Recovery → Resume failed clones without starting over
Performance → Parallel processing for large databases

From 3 Seconds to 8 Minutes (And Worth It)

The final solution:

-- One command handles everything
CALL sp_clone_create_master('PROJECT', 'customer360', 'DEV');

-- 8 minutes later:
-- ✅ Database cloned (still 3 seconds!)
-- ✅ Permissions fixed (DEV roles, not PROD)
-- ✅ All references repointed (186 views, 64 procedures, 23 functions)
-- ✅ Streams recreated (47 streams)
-- ✅ Tasks suspended (23 tasks)
-- ✅ Iceberg tables configured (external volume access granted)
-- ✅ Validated (zero stale references)
-- ✅ Audit logged (complete tracking)
-- ✅ Ready to use

The trade-off: 3 seconds → 8 minutes

The benefit: 25 person-hours → 0 person-hours

The Journey Ahead

We've seen the problem, now let's solve it. Over the next posts, I'll show you exactly how we built this:

Key Takeaways

Zero-copy cloning is revolutionary - In few seconds database copies change everything
But it's only 10% of the solution - Production needs automation around it
Database references are everywhere - Views, procedures, functions, tasks, streams
Permissions are environment-specific - Production roles don't belong in dev
Iceberg adds complexity - External volumes and dynamic tables don't get cloned
Manual fixes don't scale - Automation is essential for multi-environment operations
The ROI is massive - 8 minutes vs 25 hours per clone

👉 Continue Reading

The most immediately visible problem? Views and procedures pointing to production.

In Part 2, I'll show you how to automatically find and fix every database reference using GET_DDL, string replacement, and parallel processing—plus how to handle Iceberg tables and recreate streams properly.

All code is available in the GitHub repository with comprehensive documentation.

From One Agent to Many: Building a Multi-Agent Team for Snowflake Administration (Part 2)

Krishna Tangudu — Tue, 21 Apr 2026 05:50:00 +0000

From One Agent to Many: Building a Multi-Agent Team for Snowflake Administration (Part 2)

In Part 1, we built an Admin Agent that answers questions about Snowflake usage, credits, and query history. It's incredibly useful for monitoring operational metrics.

But what if you want to go beyond monitoring and actually find waste and recommend optimizations?

In this post, I'll show you how to:

Build a Cost Optimizer Agent specialized in finding inefficiencies
Create an Orchestrator Agent that intelligently routes questions to the right specialist
Make them work together as a team

By the end, you'll have a multi-agent system where you can ask:

"How much could I save?" → Routes to Cost Optimizer
"Show me last month's credits" → Routes to Admin Agent
"Give me a complete analysis" → Routes to both agents

Let's build it! 🚀

Why Add a Second Agent?

The Admin Agent from Part 1 is great at answering "what happened?" questions:

"How many credits did we use?"
"Show me query counts by warehouse"
"What's our storage trend?"

But it doesn't analyze patterns or recommend actions. For that, we need a specialist focused on optimization.

Enter the Cost Optimizer Agent - designed to:

Detect idle warehouses wasting credits
Recommend auto-suspend settings
Identify oversized warehouses
Find queries that need optimization

Why separate agents instead of one big agent?

**FoArchitecture: Three Agents Working Together

Here's what we're building:

User Question (natural language)
        ↓
  Orchestrator Agent
    ↙           ↘
Admin Agent   Cost Optimizer Agent
    ↘           ↙
  Combined Response

Admin Agent (from Part 1):

Already built and working
Handles operational metrics
Answers "what happened?" questions

Cost Optimizer Agent (new):

Analyzes patterns for waste
Recommends optimizations
Answers "what should I fix?" questions

Orchestrator Agent (new):

Routes questions to appropriate specialist
Can call multiple agents for comprehensive answers
Users don't need to know which agent to ask

Example Questions:

"Which warehouses have high idle time?"
"Are any warehouses oversized for their workload?"
"Show me queries with excessive spillage"

Technical Example: Analyzing Warehouse Idle Time

Let me show you how these agents work together to identify and quantify optimization opportunities.

The Pattern (Same as Part 1)

Step 1: Base View - Calculate idle time by comparing warehouse uptime vs. query execution

Join WAREHOUSE_METERING_HISTORY (credits consumed) with QUERY_ATTRIBUTION_HISTORY (actual work done)
The gap = idle credits

Step 2: Semantic View - Map the data to natural language

FACTS: idle_credits, idle_percentage, idle_cost_usd
DIMENSIONS: warehouse_name, uptime_date
METRICS: sum of idle credits, average idle percentage

Step 3: Create Agent - Connect the semantic view to the Cortex agent

Agent instructions: "Find waste and recommend fixes"
Tool: IdleWarehouseAnalyst using the semantic view

Step 4: Test It

SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN(
    'COST_OPTIMIZER_AGENT',
    '{"messages":[{"role":"user","content":[{"type":"text","text":"Which warehouses have high idle time?"}]}]}'
);

📁 Full SQL Implementation: sql/06_create_cost_optimizer_views.sql | sql/07_create_cost_optimizer_semantic_views.sql | sql/10_create_cost_optimizer_agent.sql

Example Response:

BRONZE_LOADER_WH Analysis (Last 30 Days):

Idle Time: 68% 
Idle Credits: 892 credits
Pattern: Warehouse runs continuously but data loads are batch-based

Recommendation: Set AUTO_SUSPEND = 300 seconds (5 minutes)
Reasoning: Bronze data loads complete, then warehouse sits idle 
           waiting for next batch

Estimated savings: ~600 credits/month

Great! Now we have two specialized agents. But there's a problem...

Part 2: Adding the Orchestrator

The Problem: Now users need to know which agent to ask:

Questions about credits → Ask Admin Agent
Questions about optimization → Ask Cost Optimizer Agent

That doesn't scale. What if someone asks "Why are my costs high?" - which agent should handle that?

The Solution: An Orchestrator Agent that routes questions automatically.

The Pattern

Step 1: Routing Function - Python UDF that lets one agent call another

Takes agent FQN and question as parameters
Calls SNOWFLAKE.CORTEX.DATA_AGENT_RUN programmatically
Returns the agent's response

Step 2: Orchestrator Agent - Meta-agent that routes to specialists

Instructions define routing logic (keywords: "idle"→Cost, "credits"→Admin)
Tools reference the routing function
Can call multiple agents for comprehensive analysis

Step 3: Grant Permissions - Allow orchestrator to invoke specialist agents

📁 Full Implementation: sql/12_create_orchestrator_agent.sql

Part 3: Seeing It All Work Together

Now let's test the complete system.

Example 1: Simple Routing

Question: "How many credits did BRONZE_LOADER_WH use last month?"

What Happens:

Orchestrator receives question
Identifies this as operational metrics (keywords: "credits", "use")
Routes to Admin Agent
Admin Agent queries SV_WAREHOUSE_COST_ANALYSIS
Returns credit numbers

Response:

BRONZE_LOADER_WH Credit Usage (Last 30 Days):
Total Credits: 1,312
Average Daily: 43.7 credits
Active Days: 30 of 30 (runs daily for ETL loads)

Example 2: Optimization Routing

Question: "Is BRONZE_LOADER_WH wasting credits?"

What Happens:

Orchestrator receives question
Identifies this as optimization (keywords: "wasting")
Routes to Cost Optimizer Agent
Cost Optimizer queries SV_IDLE_WAREHOUSE_ANALYSIS
Returns analysis with recommendations

Response:

BRONZE_LOADER_WH Waste Analysis:

Idle Percentage: 68%
Idle Credits: 892 credits
Pattern: Batch ETL jobs complete quickly, warehouse stays running

Recommendation:
ALTER WAREHOUSE BRONZE_LOADER_WH SET AUTO_SUSPEND = 300;

Estimated Monthly Savings: ~600 credits
Reasoning: Bronze loads are periodic - warehouse doesn't need to 
           stay running between batch jobs

Example 3: Multi-Agent Analysis

Question: "Give me a complete analysis of BRONZE_LOADER_WH"

What Happens:

Orchestrator recognizes need for comprehensive analysis
Routes question to BOTH agents:
- Admin Agent: Get usage metrics
- Cost Optimizer: Get efficiency analysis
Aggregates both responses

Response:

BRONZE_LOADER_WH Complete Analysis:

📊 USAGE (from Admin Agent):
- Total Credits (30 days): 1,312
- Average Daily: 43.7 credits
- Primary Workload: Bronze layer ETL (raw data ingestion)
- Load Schedule: Hourly batches from source systems
- Active Days: 30 of 30

💰 EFFICIENCY (from Cost Optimizer):
- Idle Time: 68%
- Wasted Credits: 892 credits
- Pattern: Loads complete in ~15 min, then idle for 45 min
- Current auto_suspend: Not set (or >30 min)

RECOMMENDATION:
ETL warehouse with batch workload pattern. Jobs complete quickly 
but warehouse stays running between batches.

Action: SET AUTO_SUSPEND = 300 (5 minutes)
Impact: ~600 credits/month savings with no performance impact

Beautiful! The user just asked one question and got insights from two specialists.

Technical Architecture: How It Actually Works

Let me break down the three layers that make these agents work.

Layer 1: Base Views (Data Calculation)

These views do the actual computational work by querying ACCOUNT_USAGE. For example, idle warehouse detection compares:

WAREHOUSE_METERING_HISTORY (when warehouse was running)
QUERY_ATTRIBUTION_HISTORY (when queries were executing)
The gap = wasted compute

Layer 2: Semantic Views (Natural Language Mapping)

These teach the agent what the data means:

FACTS: Raw values (idle_credits, idle_percentage)
DIMENSIONS: Grouping columns (warehouse_name, date)
METRICS: Aggregations (SUM, AVG)

Layer 3: Agents (Natural Language Interface)

Each agent:

Receives natural language question
Determines which semantic view to query
Cortex Analyst generates SQL automatically
Executes and returns natural language answer

MCP Integration (Optional)

For easier access, you can build an MCP (Model Context Protocol) server to integrate with GitHub Copilot:

# Simplified MCP Server Pattern
def ask_agent(question: str, agent_fqn: str) -> dict:
    payload = {"messages": [{"role": "user", "content": [{"type": "text", "text": question}]}]}
    result = conn.cursor().execute(f"SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN('{agent_fqn}', '{json.dumps(payload)}')")
    return json.loads(result.fetchone()[0])

📁 Full MCP Server: mcp/server.py

How the Agents Work Together

Here's the beautiful part: the Admin Agent provides context, and the Cost Optimizer provides action.

Example: Analyzing Bronze ETL Warehouse

Question: "Should I optimize BRONZE_LOADER_WH?"

Admin Agent provides context:

BRONZE_LOADER_WH Usage:
  - Credits (30 days): 1,312
  - Workload: Bronze layer ETL
  - Schedule: Hourly batch loads

Cost Optimizer provides analysis:

Efficiency Analysis:
  - Idle time: 68%
  - Pattern: 15-min loads, 45-min idle gaps
  - Recommendation: SET AUTO_SUSPEND = 300
  - Expected savings: ~600 credits/month

Together, they provide:

Context - What the warehouse does (bronze ETL)
Root cause - Batch pattern creates idle gaps
Solution - Auto-suspend between batches
Impact - Savings estimate without performance impact

This combination ensures you get both what's happening and what to do about it in one conversation.

Quick Start: Adding to Your Existing Agent

If you built the Admin Agent from Part 1, here's the deployment sequence:

# 1. Cost Optimizer Agent
sql/06_create_cost_optimizer_views.sql          # Base views
sql/07_create_cost_optimizer_semantic_views.sql # Semantic layer
sql/10_create_cost_optimizer_agent.sql          # Agent

# 2. Orchestrator Agent  
sql/12_create_orchestrator_agent.sql            # Routing agent + function

# 3. Test it
SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN(
    'ORCHESTRATOR_AGENT',
    '{"messages":[{"role":"user","content":[{"type":"text","text":"Which warehouses are idle?"}]}]}'
);

📁 All SQL scripts: GitHub Repository

You now have a working multi-agent system!

Agent Execution Costs

Each agent query executes on a Snowflake warehouse and consumes credits:

Typical costs:

Simple query (1 warehouse, 1 dimension): ~0.001-0.002 credits
Complex query (joins, aggregations): ~0.005-0.01 credits
Very complex analysis (multiple time ranges): ~0.01-0.05 credits

Example usage:

100 agent queries/day
Average 0.005 credits per query
Total: 0.5 credits/day = ~15 credits/month
At $3.50/credit: ~$52/month

The agents themselves cost very little to run compared to the insights they provide.

Key Lessons Learned

1. Orchestration Makes It Seamless

Without the Orchestrator, users need to know:

Which agent handles which questions
How to call different agents
When to use multiple agents

With the Orchestrator:

Just ask naturally
Routing happens automatically
Multi-agent queries work transparently

2. Agent Communication Is Key

The routing function enables:

One agent calling another
Aggregating responses
Building complex workflows

Pattern: Function → Agent calls other agents via that function

3. Start with Two, Scale to Many

This architecture scales easily:

Each new agent is independent
Orchestrator routing rules are additive
No changes needed to existing agents

4. Test Routing Logic

Make sure to test:

Edge cases (ambiguous questions)
Multi-agent scenarios
Direct vs. orchestrated access
Response aggregation quality

What's Next?

In Part 3, I'll show you how to add a Security & Governance Agent that:

Audits role assignments and privileges
Detects failed login patterns (brute force attacks)
Identifies inactive users with access
Monitors for compliance violations

Then we'll have a complete three-agent team:

🔧 Admin - Operational metrics
💰 Cost Optimizer - Waste detection
🔐 Security - Compliance and access control

All coordinated by the Orchestrator.

Code Repository

All SQL scripts and complete implementation available at:
GitHub Repository

Includes:

Base view SQL for idle detection
Additional views for warehouse sizing, query optimization
Semantic view definitions
All three agent configurations
MCP server code for Copilot integration
Step-by-step deployment guide
API endpoints - Integrate with existing dashboards
Scheduled queries - Automated monitoring with alerts
GitHub Copilot - Ask questions directly from your IDEgithub.com/LALITHASWAROOPK/agent_snowflake_admin

Includes:

All SQL scripts (base views, semantic views, agents)
MCP server for GitHub Copilot integration
Deployment guide
Example questions and expected outputs

Conclusion

In Part 1, we built one agent. In Part 2, we:

✅ Added a specialized Cost Optimizer Agent
✅ Created an Orchestrator for intelligent routing
✅ Made them work together as a team

The multi-agent pattern is powerful because:

Each agent specializes in one domain
Orchestrator handles routing automatically
Easy to add new agents without changing existing ones
Users get comprehensive answers from one question

From the user's perspective:

Before: "Which agent should I ask about idle warehouses?"
After: "Which warehouses are idle?" (Orchestrator figures it out)

This is just the beginning. You can extend this architecture with:

Security agents for compliance
Performance agents for query optimization
Data quality agents for monitoring freshness
Custom agents for your specific needs

The pattern stays the same: Views → Semantic Views → Agent → Orchestrator

Questions or feedback? Drop a comment below!

Find this helpful? Give it a ❤️ and stay tuned for Part 3: Security & Governance Agent

Want to discuss? Connect with me on LinkedIn or Twitter

Quick Reference: Agent Responsibilities

Agent	Purpose	Example Questions
Admin	Operational metrics	"How many credits did BRONZE_LOADER_WH use?"
Cost Optimizer	Waste detection	"Is BRONZE_LOADER_WH running idle?"
Orchestrator	Intelligent routing	"Analyze BRONZE_LOADER_WH efficiency"

Part 1: Build an AI Admin Agent

Part 2: Multi-Agent Architecture (this post)

Part 3: Security & Governance Agent (coming soon)

Ask Your Snowflake Account Anything — Build an AI Admin Agent with Cortex + GitHub Copilot ( Part 1 )

Krishna Tangudu — Tue, 31 Mar 2026 15:57:14 +0000

TL;DR — Spin up a Snowflake Cortex Agent that can answer admin questions like "Which warehouses burned the most credits last month?" or "Who are my top 5 spenders?" — all from a natural-language prompt inside GitHub Copilot Chat.

The Problem

Snowflake ACCOUNT_USAGE contains everything you need to understand spend, performance, and security. The catch? You have to know the right table, the right join, and write it correctly every time.

What if you could just ask?

What You'll Build

GitHub Copilot Chat
      │  (natural language)
      ▼
MCP Server (Python, local)
      │  ask_admin tool
      ▼
Snowflake Cortex Agent
      │  routes to the right semantic view
      ▼
SNOWFLAKE.ACCOUNT_USAGE (+ optional ORGANIZATION_USAGE)

By the end you'll be able to type questions like these into Copilot Chat and get back answers with real numbers:

What are total compute credits by warehouse in the last 30 days?
Which users consumed the most credits this month?
How many failed logins occurred in the last 7 days?
What are serverless credits by service type?

Prerequisites

Requirement	Notes
Snowflake account (any paid edition)	`ACCOUNTADMIN` or a role with `ACCOUNT_USAGE` access
Python 3.9+	For the MCP bridge
VS Code + GitHub Copilot Chat	Agent mode enabled
Git	To clone the template

Step 1 — Clone the Template

git clone https://github.com/LALITHASWAROOPK/agent_snowflake_admin_assistant.git
cd agent_snowflake_admin_assistant/agent_snowflake_admin

Open the folder in VS Code:

code .

Step 2 — Deploy the SQL Objects (5 scripts, ~5 minutes)

All SQL is in the sql/ folder. Replace the four placeholders before running:

Placeholder	What to set
`<APP_DB>`	A database to hold your views/agent
`<APP_SCHEMA>`	Schema inside that database
`<ADMIN_ROLE>`	Role that owns the objects
`<DEVELOPER_ROLE>`	Role that will query the agent
`<EXEC_WAREHOUSE>`	Warehouse the agent will use at runtime

Run them in order in a Snowflake worksheet or SnowSQL:

@sql/01_create_views.sql        -- Base views over ACCOUNT_USAGE
@sql/02_create_semantic_views.sql  -- Semantic layer (natural-language metadata)
@sql/03_create_agent.sql        -- Cortex Agent wired to those views
@sql/04_create_budget.sql       -- Optional: spending guardrails
@sql/05_grants.sql              -- RBAC grants

What the Views Cover

01_create_views.sql builds seven base views, all sourced exclusively from SNOWFLAKE.ACCOUNT_USAGE — no custom tables, no proprietary schemas:

View	Source
`V_WAREHOUSE_COST_BY_TAG`	`WAREHOUSE_METERING_HISTORY` + `TAG_REFERENCES`
`V_QUERY_PERFORMANCE`	`QUERY_HISTORY`
`V_USER_SPEND`	`QUERY_ATTRIBUTION_HISTORY`
`V_SERVERLESS_COSTS`	Tasks, pipes, clustering, search, MV refresh
`V_STORAGE_USAGE`	`STORAGE_USAGE` + `DATABASE_STORAGE_USAGE_HISTORY`
`V_LOGIN_HISTORY`	`LOGIN_HISTORY`
`V_DATA_TRANSFER`	`DATA_TRANSFER_HISTORY` + `REPLICATION_USAGE_HISTORY`

02_create_semantic_views.sql wraps each base view with a COMMENT block that tells the Cortex Agent what the view is for — this is how the agent knows which view to query for any given question.

Why Semantic Views?

Snowflake Cortex Agents use semantic views as tool definitions. A semantic view is just a standard view with a COMMENT that describes its purpose in natural language. The agent reads those comments at inference time to decide which tool (view) answers the user's question.

No prompt engineering required on your end.

Step 3 — Start the MCP Bridge

The MCP (Model Context Protocol) server is a lightweight Python HTTP server that exposes one tool — ask_admin — which Copilot Chat can call.

Install dependencies

pip install -r mcp/requirements.txt

Configure your environment

Create a .env file in the repo root (never commit this):

SNOWFLAKE_ACCOUNT=<your_account>
SNOWFLAKE_USER=<your_user>
SNOWFLAKE_AUTHENTICATOR=externalbrowser
SNOWFLAKE_ROLE=<DEVELOPER_ROLE>
SNOWFLAKE_WAREHOUSE=<EXEC_WAREHOUSE>
SNOWFLAKE_AGENT_FQN=<APP_DB>.<APP_SCHEMA>.<AGENT_NAME>
MCP_PORT=3000

Supports externalbrowser (SSO), snowflake (password), and OAuth — set SNOWFLAKE_AUTHENTICATOR and the matching credential variable.

Launch the server

./scripts/start-mcp.ps1

Or directly:

python mcp/server.py

Verify it's up:

Invoke-RestMethod -Uri http://127.0.0.1:3000/health -Method Get
# → {"status":"ok"}

Step 4 — Connect Copilot Chat

The repo ships a pre-configured .vscode/mcp.json that wires the local MCP server into VS Code:

{
  "mcpServers": {
    "snowflake-admin": {
      "url": "http://127.0.0.1:3000"
    }
  }
}

VS Code picks this up automatically when you open the workspace. You'll see the ask_admin tool listed inside Copilot Chat's tool picker.

There's also a GitHub Copilot instruction file at .github/instructions/admin.instructions.md which gives Copilot context about the agent's scope so it routes questions there automatically.

Step 5 — Ask It Questions

Switch Copilot Chat to Agent mode and try:

What are my top 5 warehouses by compute credits in the last 30 days?

Which users have spent the most credits this month?

Show me failed logins by user in the last 7 days.

What serverless services are costing me the most right now?

What's my current storage usage broken down by database?

The agent selects the right semantic view, runs the query, and returns a plain-English answer with the numbers inline — no SQL, no context switching.

Optional: Budget Guardrails

04_create_budget.sql sets up a Snowflake Budget object so you can enforce credit limits at the agent level. Handy for keeping a shared admin agent from running runaway queries:

CALL <APP_DB>.<APP_SCHEMA>.<BUDGET_NAME>!SET_SPENDING_LIMIT(100);
CALL <APP_DB>.<APP_SCHEMA>.<BUDGET_NAME>!GET_SPENDING_HISTORY();

Architecture Recap

┌─────────────────────────────────────┐
│         GitHub Copilot Chat         │
│      (Agent mode + ask_admin tool)  │
└────────────────┬────────────────────┘
                 │ HTTP / MCP
┌────────────────▼────────────────────┐
│      MCP Bridge  (mcp/server.py)    │
│  Exposes: /mcp/tools/list           │
│           /mcp/tools/call           │
│           /health                   │
└────────────────┬────────────────────┘
                 │ snowflake-connector-python
┌────────────────▼────────────────────┐
│   SNOWFLAKE.CORTEX.DATA_AGENT_RUN   │
│   ← Cortex Agent (semantic router)  │
└────────────────┬────────────────────┘
                 │ picks the right tool
┌────────────────▼────────────────────┐
│     Semantic Views (SV_*)           │
│     Base Views    (V_*)             │
│     ↑ SNOWFLAKE.ACCOUNT_USAGE       │
└─────────────────────────────────────┘

Key Things to Know

Account Usage latency: SNOWFLAKE.ACCOUNT_USAGE is typically delayed by up to a few hours — not real-time.
Rate sheet: ORGANIZATION_USAGE.RATE_SHEET_DAILY is optional and depends on your edition/region.
Auth flexibility: The MCP server supports SSO (externalbrowser), OAuth, and password auth — configure once in .env.

Get the Code

github.com/LALITHASWAROOPK/agent_snowflake_admin_assistant

PRs and issues welcome. If you build on top of this, tag me — I'd love to see what you add.

How Snowflake’s RELY Constraint Supercharges Your Star‑Schema Queries

Krishna Tangudu — Thu, 19 Mar 2026 18:49:36 +0000

Snowflake’s RELY constraint is a powerful but under‑used hint that tells the optimizer it can trust your primary keys and foreign keys and use them to eliminate unnecessary work—especially redundant joins. In this post, I’ll explain what RELY does, how it helps with join elimination, and walk you through concrete examples you can adapt for your own schemas.

Why this matters to a data engineer?

If you’ve ever used wide, reusable models with hundreds of columns designed as a single source of truth, you know how easily queries can accumulate unnecessary joins. RELY helps here by letting Snowflake eliminate redundant joins when they’re logically not needed, so queries only scan the tables that actually contribute to the result.

In Snowflake, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints on standard tables are not enforced by default; they are mainly metadata for humans and tools. However, Snowflake lets you add a RELY property on those constraints to signal:

I guarantee this constraint is true in my data, and you can use it to rewrite my queries.

You set RELY like this:

ALTER TABLE dim_customer
  ALTER PRIMARY KEY RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_customer_id RELY;

When does RELY kick in?

Snowflake can use RELY‑tagged constraints to eliminate unnecessary joins when:

A table has a PRIMARY KEY or UNIQUE constraint marked RELY.
A related table has a FOREIGN KEY constraint also marked RELY.
The query joins on those keys, but doesn’t actually need any columns from one of the joined tables beyond the key itself.

In those situations, Snowflake can realize that the join is “logically redundant” and rewrite the plan to drop the table scan entirely.

This is a cost-saving measure as it rewrites the query to eliminate unnecessary tables, allowing for faster performance. Additionally, may allows us to use a smaller warehouse may help reduce overall costs.

Example 1: Simple join elimination with sample data

CREATE TABLE dim_customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR
);

CREATE TABLE fact_sales (
  sale_id INT,
  customer_id INT,
  amount DECIMAL(10,2),
  sale_date DATE,
  CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES dim_customer (customer_id)
);

Sample data:

INSERT INTO dim_customer VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO fact_sales VALUES
  (101, 1, 100.00, '2026-01-01'),
  (102, 1, 150.00, '2026-01-05'),
  (103, 2, 200.00, '2026-01-03'),
  (104, 3, 300.00, '2026-01-04');

Initial query (no RELY):

SELECT
  f.customer_id,
  SUM(f.amount) AS total_amount
FROM fact_sales f
JOIN dim_customer d ON f.customer_id = d.customer_id
GROUP BY f.customer_id;

Output:

Query Plan:

Now enable RELY:

ALTER TABLE dim_customer
  ALTER PRIMARY KEY RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_customer_id RELY;

Under the hood, Snowflake sees:

dim_customer.customer_id is unique and trusted (PRIMARY KEY RELY).
fact_sales.customer_id is a trusted foreign key (fk_customer_id RELY).
The only column used from dim_customer is customer_id, which is already in fact_sales.

So the optimizer eliminates the join to dim_customer and rewrites the query plan like below:

The result is identical, but the execution plan no longer scans dim_customer at all.

Example 2: Join elimination with filters (and when it doesn’t kick in)

SELECT
  f.customer_id,
  SUM(f.amount) AS total_amount
FROM fact_sales f
JOIN dim_customer d ON f.customer_id = d.customer_id
WHERE d.customer_name = 'Alice'
GROUP BY f.customer_id;

Here, dim_customer.customer_name is used in the filter, so the join on dim_customer is not redundant even with RELY. The optimizer must still fetch dimension data to evaluate the WHERE clause, so join elimination does not occur.

Example 3: RELY in a multi‑dimension star schema

CREATE OR REPLACE TABLE dim_customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR
);

CREATE OR REPLACE  TABLE dim_channel (
  channel_id INT PRIMARY KEY,
  channel_name VARCHAR
);

CREATE OR REPLACE TABLE fact_sales (
  sale_id INT,
  customer_id INT,
  channel_id INT,
  amount DECIMAL(10,2),
  sale_date DATE,

  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES dim_customer (customer_id),
    CONSTRAINT fk_channel FOREIGN KEY (channel_id) REFERENCES dim_channel (channel_id)
);

## Sample Data

-- dimensions
INSERT INTO dim_customer VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO dim_channel VALUES (1, 'Online'), (2, 'In‑store');

-- facts
INSERT INTO fact_sales VALUES
  (101, 1, 1, 100.00, '2026-01-01'),
  (102, 1, 2, 200.00, '2026-01-02'),
  (103, 2, 1, 150.00, '2026-01-03');

## Now Enable Rely

ALTER TABLE dim_customer
  ALTER PRIMARY KEY RELY;

ALTER TABLE dim_channel
  ALTER PRIMARY KEY RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_customer RELY;

ALTER TABLE fact_sales
  ALTER CONSTRAINT fk_channel RELY;

If you run a query that only aggregates by fact keys:

SELECT
  f.customer_id,
  f.channel_id,
  SUM(f.amount) AS total_amount
FROM fact_sales f
JOIN dim_customer d_c ON f.customer_id = d_c.customer_id
JOIN dim_channel d_ch ON f.channel_id = d_ch.channel_id
GROUP BY f.customer_id, f.channel_id;

Here, dim_channel and dim_customer are only used for their keys, which are already in fact_sales. With RELY, Snowflake can understand that:

dim_customer.customer_id is unique and trusted.
dim_channel.channel_id is unique and trusted.
The join columns are already present in fact_sales.

So it can eliminate both joins, effectively turning the query into:

How RELY helps performance in practice
By enabling RELY on trusted PK/FK constraints, you give the optimizer permission to:

Drop unnecessary dimensions from analytical queries that only reference key columns.
Short‑circuit joins and reduce the number of operators in the plan, which often lowers merge‑join/hash‑join overhead.
Reduce memory and compute usage, especially for large fact tables that are joined repeatedly in BI or ad‑hoc workloads.

Important caveats and best practices

Because RELY is a trust‑me hint, a few caveats matter:

You are responsible for enforcing UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. If those constraints are violated, query results with RELY can differ from those with NORELY.
Always validate data via ETL (e.g., dbt tests) before setting RELY, as violations can lead to suboptimal plans
Timestamp_tz PKs or hybrid tables limit elimination

Part 2: Zero-Copy data federation Snowflake Customer 360 Data to Salesforce Sales Reps

Krishna Tangudu — Wed, 18 Mar 2026 18:00:00 +0000

In Part 1, we connected Salesforce Data Cloud to Snowflake for analytics. In this blog, you'll flip the direction: surface your Snowflake Customer 360 model (Orders, Service contracts, invoices, Repairs, etc.) directly into Salesforce so your sales and service teams can activate it in real time—without ETL or data duplication.

Snowflake ↔ Salesforce Data Cloud ↔ Salesforce CRM

Snowflake: Owns Customer 360 models, health scores, churn risk

Zero-Copy: Data Cloud queries Snowflake live (pushdown SQL)

Salesforce: Activates signals (flows, UI, campaigns)

Architecture Pattern

Your data team builds 'CUSTOMER_360_VIEW' with 'ACCOUNT_ID', 'HEALTH_SCORE', 'CHURN_RISK', 'NEXT_BEST_PRODUCT'
Modeled once, governed in Snowflake (roles, masking, row access)
Updated via your ELT pipelines (daily/hourly)

Step 1: Building data model in Snowflake


-- ============================================================
-- COMPLETE DEMO SCRIPT: Accounts + Customer 360 + Final Table
-- ============================================================

-- Optional: set your context
-- USE ROLE <your_role>;
-- USE WAREHOUSE <your_warehouse>;
-- USE DATABASE <your_database>;
-- USE SCHEMA <your_schema>;

-- ------------------------------------------------------------
-- 1) Clean up old objects (safe for reruns)
-- ------------------------------------------------------------
DROP TABLE IF EXISTS ACCOUNT_C360_SNAPSHOT;
DROP TABLE IF EXISTS CUSTOMER_360_METRICS;
DROP TABLE IF EXISTS ACCOUNTS;

-- ------------------------------------------------------------
-- 2) Create source tables
-- ------------------------------------------------------------
CREATE OR REPLACE TABLE ACCOUNTS (
    ACCOUNT_ID NUMBER,
    ACCOUNT_NAME STRING,
    INDUSTRY STRING,
    ANNUAL_REVENUE NUMBER(18,2),
    DELETED_AT TIMESTAMP_NTZ
);

CREATE OR REPLACE TABLE CUSTOMER_360_METRICS (
    ACCOUNT_ID NUMBER,
    HEALTH_SCORE FLOAT,
    CHURN_RISK FLOAT,
    NPS_SCORE NUMBER(5,2),
    ARR NUMBER(18,2),
    NEXT_BEST_PRODUCT STRING,
    LAST_ENGAGEMENT_DATE DATE
);

-- ------------------------------------------------------------
-- 3) Insert sample data (toy companies)
-- ------------------------------------------------------------
INSERT INTO ACCOUNTS (ACCOUNT_ID, ACCOUNT_NAME, INDUSTRY, ANNUAL_REVENUE, DELETED_AT) VALUES
(1001, 'Bright Blocks Toys',  'Toys', 25000000, NULL),
(1002, 'Rocket Wheels Co',    'Toys',  8000000, NULL),
(1003, 'Cuddle Critters Inc', 'Toys', 12000000, NULL),
(1004, 'Puzzle Planet Ltd',   'Toys', 30000000, NULL),
(1005, 'Old Toy Factory',     'Toys',  5000000, '2025-01-15 10:00:00'),
(NULL, 'Bad Row',             'Toys',   100000, NULL);

INSERT INTO CUSTOMER_360_METRICS (
    ACCOUNT_ID, HEALTH_SCORE, CHURN_RISK, NPS_SCORE, ARR, NEXT_BEST_PRODUCT, LAST_ENGAGEMENT_DATE
) VALUES
(1001, 0.91, 0.22, 60, 1800000, 'STEM Robot Kit',           '2026-03-10'),
(1002, 0.67, 0.45, 35,  650000, 'Turbo Track Racing Set',   '2026-03-05'),
(1003, 0.43, 0.81, 10,  420000, 'Deluxe Plush Care Pack',   '2026-02-20'),
(1004, 0.80, 0.70, 55, 2200000, 'Master Puzzle Collection', '2026-03-14'),
(9999, 0.95, 0.10, 70,  900000, 'Collector Train Set',      '2026-03-01'); -- no matching account

-- ------------------------------------------------------------
-- 4) Create final table using your view definition logic
-- ------------------------------------------------------------
CREATE OR REPLACE TABLE ACCOUNT_C360_SNAPSHOT AS
SELECT
    A.ACCOUNT_ID,
    UPPER(A.ACCOUNT_NAME) AS ACCOUNT_NAME,
    A.INDUSTRY,
    A.ANNUAL_REVENUE,
    C360.HEALTH_SCORE,
    C360.CHURN_RISK,
    C360.NPS_SCORE,
    C360.ARR,
    C360.NEXT_BEST_PRODUCT,
    C360.LAST_ENGAGEMENT_DATE,
    CASE
        WHEN C360.HEALTH_SCORE >= 0.8 THEN 'Healthy'
        WHEN C360.HEALTH_SCORE >= 0.5 THEN 'At Risk'
        ELSE 'Critical'
    END AS HEALTH_STATUS,
    CASE
        WHEN C360.CHURN_RISK >= 0.7 THEN 'High'
        WHEN C360.CHURN_RISK >= 0.4 THEN 'Medium'
        ELSE 'Low'
    END AS CHURN_RISK_LEVEL,
    CURRENT_TIMESTAMP() AS LAST_UPDATED
FROM ACCOUNTS A
INNER JOIN CUSTOMER_360_METRICS C360
    ON A.ACCOUNT_ID = C360.ACCOUNT_ID
WHERE A.ACCOUNT_ID IS NOT NULL
  AND A.DELETED_AT IS NULL;

Data Cloud keeps a **virtual reference to Snowflake views—no data duplication, always fresh**

Step 2: Connecting Salesforce Data Cloud to Snowflake using zero copy data federation

First, review your network policies and ensure that Salesforce Data Cloud IPs are included.

Salesforce Data Cloud connection:

Note: Make sure the private key is unencrypted and eliminate comments, such as "BEGINNING OF PRIVATE KEY" and "END OF PRIVATE KEY".

Create a Snowflake Data Stream in Data Cloud:

Select the required object from Snowflake:

Configure with below details:

Field	Value	Notes
Object Name	Customer_360	Simple, no special chars; this is the internal name Data Cloud uses
Category	Accounts	Helps organize data streams by business domain
Primary Key	ACCOUNT_ID	The unique identifier that joins to Salesforce Account records
Organization Unit Identifier	ACCOUNT_ID	(Optional; keep same as Primary Key unless you have a multi-tenant model)

Choose Live/Schedule:

Enable Select Acceleration only when necessary.
This approach allows Salesforce to cache data on a scheduled basis instead of querying Snowflake for every request.
When users attempt to load large volumes of data but only select a small subset of columns, querying Snowflake directly can become unnecessarily expensive. Leveraging Select Acceleration in these scenarios helps reduce compute costs and improves query performance by avoiding repeated full-table scans.

Step 3. Salesforce as Activation Layer

Sales reps see Snowflake signals in Account/Opportunity pages, flows create tasks automatically

Maps ACCOUNT_ID → Data Cloud profile, pulls HEALTH_SCORE, CHURN_RISK

Live signals drive real-time actions without swivel-chair between BI and CRM.

Monitoring:

Queries fired by Salesforce can be monitored in snowflake in Query History:

Governance & Ownership

Snowflake: Data ownership, model logic, calculations
Data Cloud: Unification, activation logic
Salesforce: UX, workflows, business rules

Key patterns from production:

Snowflake auto-scaling handles Data Cloud query bursts
Explicit 'GRANT SELECT' on views (not tables) for least privilege
Data Cloud refresh schedule matches Snowflake pipeline cadence

Production Impact

Before: Sellers export CSV from Snowflake → manual Salesforce upload
After: Zero-copy signals → automatic tasks → faster CSM response

What's next?

Salesforce Data Cloud also provides an option for file‑based federation when using the Snowflake Polaris Catalog Account.

This enables data access without continuously querying Snowflake, offering an alternative integration pattern for specific use cases.

Whether this capability will be extended to the Horizon Catalog remains to be seen—something to watch as the platform evolves.

Part 1: Zero-Copy Sharing – Salesforce Snowflake for Analytics

Krishna Tangudu — Thu, 12 Mar 2026 16:00:00 +0000

The ETL Nightmare Ends

Salesforce holds your richest customer data—opportunities, accounts, contacts—but getting it into Snowflake for analytics usually means painful ETL pipelines, schema drift, and maintenance hell. Zero-copy data Federation sharing changes everything: Salesforce publishes secure shares that land in Snowflake as native objects, queryable instantly without duplication or latency.

We are currently in the process of implementing Salesforce Sales Cloud. In this first part of a 2-part series, I'll walk through the exact OAuth setup, common integration traps (network policies! URL underscores!), and best practices for analytics teams.

Architecture Overview

Zero-copy sharing flips traditional pipelines: Salesforce Data Cloud becomes the secure publisher, Snowflake the consumer.

Layer	Owner	Contains
Source	Salesforce Sales Cloud	Raw CRM objects (OPPORTUNITY, ACCOUNT, CONTACT)
Share	Salesforce Data Cloud	Secure data share with selected objects
Consumer DB	Snowflake	Read-only shared objects like SFDC.OPPORTUNITY
Analytics Layer	Snowflake	Views, models, dashboards built on shared data

Salesforce Datacloud handles object sharing; Snowflake analysts query familiar objects immediately. No Fivetran, no Airflow, no storage costs for duplicates.

Step-by-Step Setup

Before you begin:

Make sure that your Snowflake admin adds the Data 360 IP Addresses to the allowlist.

Supported Regions for Snowflake Integration

In Snowflake
Step 1: Snowflake Security Integration

CREATE OR REPLACE SECURITY INTEGRATION sf_integration
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://test.salesforce.com/services/cdp/SnowflakeOAuthCallback'
  ENABLED = TRUE
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE;

Prod tip: Swap test.salesforce.com for login.salesforce.com in production.

Step 2: Snowflake Integration User (Not required if the user creating a data target on Salesforce already has a login using Azure or a native user in Snowflake.)

CREATE OR REPLACE USER sf_integration_user
  LOGIN_NAME = 'sf_integration_user'
  PASSWORD = '<secure-password>'
  DEFAULT_ROLE = 'sf_integration_role'
  EMAIL = 'your-email@company.com';
Grant minimal privileges: USAGE on warehouse, SELECT on consumer database.

Step 3: Client Secrets & Salesforce Config

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('sf_integration');

Copy both client ID and secret to Salesforce Data Cloud.

Step 4: Salesforce Data Target
Salesforce team configures:

Account URL: https://my-account-us-east-1.snowflakecomputing.com
Critical: if your Snowflake URL has underscores (my_account_us-east-1), convert to hyphens (my-account-us-east-1) in the Account URL field.

Authentication: OAuth (using secrets from step 3)

Step 5: Publish to Share:
The Salesforce team should create a data stream and a data lake object, then add this object to the created Data Share Target.

Data Stream ( Address ):

Data Lake Object:

Salesforce publishes share → Snowflake receives:

CREATE DATABASE sf_shared FROM SHARE <ShareName>;

Integration Issues & Fixes
80% of failures happen here. Lessons from this activity:

Network Policies Block Everything
Snowflake network policies can silently prevent Salesforce Data cloud IP ranges.
Check: SHOW NETWORK POLICIES;
Fix: Add Salesforce Data cloud IPs

Account URL Underscores → Hyphens
Salesforce rejects Snowflake URLs with _. Always transform:

Check Permissions
Check the permissions of the user who is trying to establish the connection

Note: This setup is only required one time.

Best Practices for Analytics Teams

Layered Architecture

SF_SHARED (raw, read-only)
↓
SF_ANALYTICS (views, materialized)
↓
BI tools (Power BI, Tableau)

Governance

Enable only semantic view access : grant USAGE on raw share to business users
Create weekly Alert to monitor Schema drift breaks downstream views Salesforce owns object changes (new fields, deletions)
Materialize frequent aggregates like below

CREATE MATERIALIZED VIEW analytics.opp_pipeline_summary AS
SELECT stage, COUNT(*) as count, AVG(amount) as avg_amount
FROM sf_shared.sfdc.opportunity
GROUP BY stage;
Cost Control

Use multi-cluster warehouses for concurrent BI queries
Monitor: QUERY_HISTORY() for expensive shared table scans

Take Away:
0 TB duplicated storage, 30% faster pipeline deployment, 100% schema fidelity.

Quick Wins Checklist

[ ] URL underscores → hyphens
[ ] Network policy allows Salesforce IPs
[ ] Test OAuth token refresh before prod
[ ] Raw share → semantic views (don't expose raw)

What's Next (Part 2 Teaser)
This gets Salesforce data into Snowflake seamlessly. In Part 2, we reverse direction: Snowflake Customer 360 data flowing back to sales reps in Salesforce via data federation

Quick Start Your Integration

✅ Fork: https://github.com/LALITHASWAROOPK/salesforce-snowflake-zero-copy

Part 2 → Customer 360 (Snowflake → Salesforce)

Krishna Tangudu

Mar 18

Part 2: Zero-Copy data federation Snowflake Customer 360 Data to Salesforce Sales Reps

#cloud #dataengineering #salesforce #snowflake

Comments

4 min read

Try this yourself: Share your biggest Salesforce-Snowflake integration pain point in the comments!

Setting up Snowflake–Power BI Connectivity with Azure AD SSO and Auto-Provisioning

Krishna Tangudu — Thu, 12 Mar 2026 14:18:02 +0000

Overview:

This post walks through how to enable end-to-end SSO from Power BI to Snowflake using Azure AD, while automatically provisioning users and roles via SCIM. It’s designed for enterprise BI scenarios where analysts connect with DirectQuery or scheduled refresh and you want their Azure AD identity and group membership to control Snowflake access without manual user administration.

Step 1. Configure SSO to Snowflake with Azure AD

Step 1.1: Register Snowflake in Azure AD
Ask your Azure team to register the Snowflake application in Azure AD using these values (replace with your account locator):

Identifier (Entity ID):
https://<account_locator>.snowflakecomputing.com/

Reply URL:
https://<account_locator>.snowflakecomputing.com/fed/login

Sign On URL:
https://<account_locator>.snowflakecomputing.com/

Sign Out URL:
https://<account_locator>.snowflakecomputing.com/fed/logout

Step 1.2: Collect SAML metadata from Azure AD

EntityID in the form: https://sts.windows.net// → use as SAML2_ISSUER

Location in the form: https://login.microsoftonline.com//saml2 → use as SAML2_SSO_URL

X509Certificate → use as SAML2_X509_CERT

SAML2_PROVIDER → set to CUSTOM when Azure AD is the IdP

Step 1.3: Create the SAML2 integration in Snowflake

Run as ACCOUNTADMIN:

USE ROLE ACCOUNTADMIN;

CREATE SECURITY INTEGRATION <AZURE_AD_SSO_NAME>
  TYPE = SAML2
  ENABLED = TRUE
  SAML2_ISSUER = '<EntityID from metadata>'
  SAML2_SSO_URL = '<Location from metadata>'
  SAML2_PROVIDER = 'CUSTOM'
  SAML2_X509_CERT = '<X509 certificate>'
  SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'AzureADSSO'
  SAML2_ENABLE_SP_INITIATED = TRUE;

This enables browser SSO into Snowflake via Azure AD

Step 2: Enable Automatic Provisioning with SCIM

2.1 Create functional Azure groups
Have the support/AD team create AD groups following a functional-role naming convention, for example:

DEVELOPER-SNOWFLAKE
ADMIN-SNOWFLAKE
SUPPORT-SNOWFLAKE

These group names will become Snowflake role names via SCIM and act as default roles.

Step 2.2: Create SCIM integration in Snowflake

Run as ACCOUNTADMIN:

CREATE ROLE IF NOT EXISTS AAD_PROVISIONER;

GRANT CREATE USER ON ACCOUNT TO ROLE AAD_PROVISIONER;
GRANT CREATE ROLE ON ACCOUNT TO ROLE AAD_PROVISIONER;

GRANT ROLE AAD_PROVISIONER TO ROLE ACCOUNTADMIN;
GRANT ROLE AAD_PROVISIONER TO ROLE SYSADMIN;

CREATE OR REPLACE SECURITY INTEGRATION AAD_PROVISIONING
  TYPE = SCIM
  SCIM_CLIENT = 'azure'
  RUN_AS_ROLE = 'AAD_PROVISIONER';

Then generate the SCIM access token:

SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('AAD_PROVISIONING');

Notes:

Token validity is 6 months; you must regenerate it periodically.
Add monitoring/alerting to renew before expiry

Step 2.3 Share SCIM details with Azure team

Tenant URL:
https://.snowflakecomputing.com/scim/v2/

Secret Token: The SCIM access token generated above

They will configure the Snowflake enterprise app in Microsoft Entra ID (Azure AD) for automatic provisioning, following Microsoft’s “Configure Snowflake for automatic user provisioning with Microsoft Entra ID” tutorial.

Step 3. Configure SSO from Power BI to Snowflake

Step 3.1: Security Integration creation

CREATE OR REPLACE SECURITY INTEGRATION CM_SC_<ACCOUNT_LOCATOR>_SSO_POWERBI_SNFK
  TYPE = EXTERNAL_OAUTH
  ENABLED = TRUE
  EXTERNAL_OAUTH_TYPE = AZURE
  EXTERNAL_OAUTH_ISSUER = '<EntityID from Step 2 (https://sts.windows.net/.../)>'
  EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://login.windows.net/common/discovery/keys'
  EXTERNAL_OAUTH_AUDIENCE_LIST = (
    'https://analysis.windows.net/powerbi/connector/Snowflake',
    'https://analysis.windows.net/powerbi/connector/snowflake'
  )
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'upn'
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name';
--UPN in Azure AD matches the login_name or email

ALTER SECURITY INTEGRATION CM_SC_<ACCOUNT_LOCATOR>_SSO_POWERBI_SNFK
  SET EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE'; -- This allows additional roles

Step 3.2 Grant Snowflake access for Power BI users
Grant appropriate warehouse and database access to the Snowflake roles that are created.

GRANT USAGE ON WAREHOUSE <WAREHOUSENAME>
  TO ROLE "DEVELOPER-SNOWFLAKE";

GRANT USAGE ON DATABASE <DATABASENAME>
  TO ROLE "DEVELOPER-SNOWFLAKE";

GRANT USAGE ON SCHEMA <DATABASENAME.SCHEMANAME>
  TO ROLE "DEVELOPER-SNOWFLAKE";

GRANT SELECT ON ALL TABLES IN SCHEMA <DATABASENAME.SCHEMANAME>
  TO ROLE "DEVELOPER-SNOWFLAKE";

Step 3.3 Network policy and IP ranges

If you use Snowflake network policies, ensure the policy allows:

Power BI service IP ranges
Azure AD IP ranges
Microsoft publishes updated IP ranges here:
https://www.microsoft.com/en-us/download/details.aspx?id=56519

Step 4: Using it from Power BI

Once everything above is configured:

In Power BI (Desktop or Service), use the Snowflake connector.
Sign in with your Azure AD (organizational) account.
The connector obtains an Azure AD token, which Snowflake validates via the EXTERNAL_OAUTH integration, mapping the upn claim to the Snowflake login_name.

Note:
If connecting using PowerBI Service, please use Server Name as all lower case, otherwise MS PBI interface gives weird errors.