snowflake-reference-architecture

name	snowflake-reference-architecture
description	Implement Snowflake reference architecture with medallion pattern and Snowflake-native design. Use when designing a new Snowflake data platform, setting up bronze/silver/gold layers, or establishing architecture standards for a Snowflake deployment. Trigger with phrases like "snowflake architecture", "snowflake medallion", "snowflake best practices layout", "snowflake data platform design".
allowed-tools	Read, Grep
version	1.5.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","data-warehouse","analytics","snowflake"]
compatibility	Designed for Claude Code

Snowflake Reference Architecture

Overview

Production-ready Snowflake architecture using the medallion pattern (bronze/silver/gold), role-based access, and workload-isolated warehouses.

Architecture Overview

                    ┌──────────────────────┐
                    │   Data Sources        │
                    │ (S3, APIs, DBs, SaaS) │
                    └──────────┬───────────┘
                               │
                    ┌──────────▼───────────┐
                    │   BRONZE (Raw)        │
                    │   Snowpipe / COPY     │
                    │   VARIANT columns     │
                    └──────────┬───────────┘
                               │ Streams + Tasks
                    ┌──────────▼───────────┐
                    │   SILVER (Cleansed)   │
                    │   Typed columns       │
                    │   Deduped, validated   │
                    └──────────┬───────────┘
                               │ Dynamic Tables
                    ┌──────────▼───────────┐
                    │   GOLD (Business)     │
                    │   Aggregated          │
                    │   Analytics-ready     │
                    └──────────┬───────────┘
                               │
                    ┌──────────▼───────────┐
                    │   Consumers           │
                    │   BI tools, APIs,     │
                    │   Data Sharing        │
                    └──────────────────────┘

Database Layout

-- One database per environment, schemas per layer
CREATE DATABASE PROD_DW;

-- Bronze: Raw ingested data (append-only, VARIANT columns)
CREATE SCHEMA PROD_DW.BRONZE;

-- Silver: Cleansed, typed, deduplicated
CREATE SCHEMA PROD_DW.SILVER;

-- Gold: Business-level aggregations and dimensions
CREATE SCHEMA PROD_DW.GOLD;

-- Staging: Temporary tables for ETL processing
CREATE SCHEMA PROD_DW.STAGING;

-- Utility: Stored procedures, UDFs, file formats
CREATE SCHEMA PROD_DW.UTILITY;

Instructions

Step 1: Bronze Layer (Raw Ingestion)

-- Store raw data as VARIANT for schema-on-read
CREATE TABLE PROD_DW.BRONZE.RAW_EVENTS (
    ingestion_time TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    source_file (),
    raw_data VARIANT
);


 FILE FORMAT PROD_DW.UTILITY.JSON_INGEST
  TYPE   STRIP_OUTER_ARRAY  ;


 STAGE PROD_DW.UTILITY.S3_EVENTS_STAGE
  STORAGE_INTEGRATION  s3_integration
  URL  
  FILE_FORMAT  PROD_DW.UTILITY.JSON_INGEST;


 PIPE PROD_DW.BRONZE.EVENTS_PIPE
  AUTO_INGEST  

    PROD_DW.BRONZE.RAW_EVENTS (source_file, raw_data)
   ( METADATA$FILENAME, $  .UTILITY.S3_EVENTS_STAGE);