Snowflake Medallion Architecture

An enterprise-grade Snowflake data platform reference implementation featuring a medallion (Bronze → Silver → Gold) architecture with full security, observability, and governance controls — aligned to the CIS Snowflake Foundations Benchmark.

---

Architecture Overview

┌─────────────────────────────────────────────────────────────────────────┐
│                          DATA SOURCES                                   │
│   SQL Server (Legacy)      AWS S3 (Files)      Snowflake Native         │
└──────────────┬─────────────────┬──────────────────────┬────────────────┘
               │                 │                      │
               ▼                 ▼                      ▼
┌─────────────────────────────────────────────────────────────────────────┐
│                     BRONZE LAYER  (RAW_DB)                              │
│          Raw, unmodified ingestion — schema-on-read, immutable          │
│   • Snowpipe for S3 auto-ingest        • External stages                │
│   • ADF / Fivetran for SQL Server      • Full audit metadata            │
└──────────────────────────────────┬──────────────────────────────────────┘
                                   │  dbt (validation + type casting)
                                   ▼
┌─────────────────────────────────────────────────────────────────────────┐
│                    SILVER LAYER  (FOUNDATION_DB)                        │
│     Cleansed, validated, conformed — single source of truth             │
│   • Null checks / referential integrity   • SCD Type 2 snapshots        │
│   • Standardised column naming            • PII masking applied         │
└──────────────────────────────────┬──────────────────────────────────────┘
                                   │  dbt (business logic + aggregations)
                                   ▼
┌─────────────────────────────────────────────────────────────────────────┐
│                      GOLD LAYER  (ANALYTICS_DB)                         │
│          Curated, domain-oriented data products                         │
│   • Customer 360           • Revenue summary                            │
│   • Product performance    • Row-level security enforced                │
└──────────────────────────────────┬──────────────────────────────────────┘
                                   │
                                   ▼
┌─────────────────────────────────────────────────────────────────────────┐
│                        CONSUMPTION LAYER                                │
│   Microsoft Power BI (SAML/OAuth + AD group RLS)                        │
│   Data Science / ML workloads      Ad-hoc SQL (analysts)                │
└─────────────────────────────────────────────────────────────────────────┘

---

Repository Structure

snowflake-medallion/
├── docs/
│   ├── architecture/
│   │   ├── security-model.md           # RBAC hierarchy, masking, RLS, audit
│   │   ├── cis_benchmark_compliance.md # CIS Snowflake Benchmark control mapping
│   │   ├── multi_account_architecture.md # Multi-account topology guide
│   │   └── cortex_ai.md                # Cortex AI layer: governance + capabilities
│   └── runbooks/
│       ├── onboarding.md               # New developer setup guide
│       ├── incident-response.md        # 5 incident playbooks (data loss, pipeline, etc.)
│       └── environment_promotion.md    # How to promote changes dev → staging → prod
├── infrastructure/
│   └── snowflake/
│       ├── account_setup/
│       │   ├── 01_databases.sql        # Databases and schemas
│       │   ├── 02_warehouses.sql       # Warehouse sizing and auto-suspend
│       │   ├── 03_roles.sql            # RBAC role hierarchy and grants
│       │   ├── 04_users.sql            # Service accounts and human user templates
│       │   ├── 05_network_policies.sql # Network rules + IP allowlisting (CIS 2.1/2.2)
│       │   ├── 06_password_policies.sql # Strong password + session timeout (CIS 1.3/1.4)
│       │   ├── 07_scim_integration.sql # Azure AD SCIM provisioning (CIS 1.6)
│       │   └── 08_authentication_policies.sql # SSO-only humans, key-pair-only services, MFA
│       ├── security/
│       │   ├── 01_object_tags.sql      # DATA_SENSITIVITY, PII_CATEGORY, COMPLIANCE_SCOPE tags
│       │   ├── 02_data_classification.sql # Auto-classification profiles + manual log
│       │   ├── 03_column_masking_policies.sql # PII dynamic masking (email, phone, name, DOB, IP, ...)
│       │   ├── 04_row_access_policies.sql # Domain isolation, tenant, regional residency RLS
│       │   ├── 05_managed_access_schemas.sql # Prevent privilege escalation (CIS 3.5)
│       │   ├── 06_cis_compliance_checks.sql  # Audit queries for every CIS control
│       │   └── 07_trust_center.sql     # Trust Center access + CIS Benchmarks scanner package
│       ├── monitoring/
│       │   ├── 01_resource_monitors.sql # Credit consumption alerts per warehouse
│       │   ├── 02_audit_logging.sql     # Audit views over SNOWFLAKE.ACCOUNT_USAGE
│       │   ├── 03_alerts.sql            # Operational alerts (freshness, DQ, DDL)
│       │   └── 04_privileged_access_alerts.sql # Security alerts (CIS 5.3/6.1/6.2)
│       ├── integrations/               # S3 storage integration, Snowpipe, SQL Server
│       ├── backup/                     # Time travel config, cross-region replication
│       ├── multi_account/
│       │   ├── 01_organizations_setup.sql   # Snowflake Organizations + account provisioning
│       │   ├── 02_cross_account_sharing.sql # Secure Data Sharing between accounts
│       │   └── 03_account_replication.sql   # Failover Groups for HA/DR
│       └── cortex/
│           ├── 00_cortex_governance.sql     # CORTEX_USER hygiene, model allowlist, AI cost alert
│           ├── 01_semantic_views.sql        # Semantic models for Cortex Analyst / Intelligence
│           ├── 02_cortex_search.sql         # Search service over platform runbooks/docs
│           ├── 03_anomaly_monitoring.sql    # ML anomaly detection on credit spend
│           └── 04_document_ai_ingestion.sql # AI_PARSE_DOCUMENT bronze pattern for PDFs
├── dbt/
│   ├── dbt_project.yml
│   ├── profiles.yml.example            # Connection template (copy to ~/.dbt/profiles.yml)
│   ├── packages.yml                    # dbt_utils, dbt_expectations, audit_helper, elementary
│   ├── models/
│   │   ├── bronze/
│   │   │   ├── sources.yml             # Source definitions with freshness checks
│   │   │   ├── sqlserver/              # brz_sqlserver_{customers,orders,order_items,products}
│   │   │   └── s3/                     # brz_s3_{events,transactions}
│   │   ├── silver/
│   │   │   ├── customers/              # slv_customers
│   │   │   ├── orders/                 # slv_orders, slv_order_items
│   │   │   ├── products/               # slv_products
│   │   │   └── events/                 # slv_events
│   │   └── gold/
│   │       ├── marketing/              # gld_customer_360
│   │       ├── finance/                # gld_revenue_summary
│   │       └── operations/             # gld_product_performance
│   ├── seeds/
│   │   ├── _seeds.yml                  # Seed column types, tests, and documentation
│   │   ├── ref_data_domains.csv        # Business domain definitions and PBI role mappings
│   │   └── ref_country_regions.csv     # ISO country → GDPR/CCPA/residency zone mapping
│   ├── macros/                         # audit_columns, generate_schema_name, safe_cast_*, etc.
│   ├── snapshots/                      # snp_customers (SCD Type 2)
│   └── tests/singular/                 # Business-rule tests across all layers
├── powerbi/
│   ├── saml_oauth_setup.md             # Azure AD SSO + OAuth integration guide
│   └── row_level_security/
│       ├── rls_snowflake_setup.sql     # Applies row access + masking policies to gold tables
│       └── rls_policies.md             # AD group → role → data access matrix
└── scripts/
    ├── setup/
    │   └── bootstrap.sh                # Ordered, role-aware execution of all infra scripts
    └── utilities/
        ├── clone_for_dev.sh            # Zero-copy dev/test clones from production schemas
        └── cost_report.sql             # Credit consumption and storage cost queries

---

Quick Start

Prerequisites

Tool	Version	Notes
Snowflake account	Enterprise edition or higher	Business Critical for Failover Groups
Python	3.11+	Use pyenv to manage versions
dbt-snowflake	1.8.x	See dbt/packages.yml for exact constraint
Snowflake CLI (snow)	3.x	For running infrastructure scripts (SnowSQL is legacy; bootstrap.sh supports both)
AWS CLI	2.x	Required for S3 storage integration setup
Azure CLI	latest	Required for Azure AD / SAML SSO + SCIM configuration

1. Clone and configure

git clone https://github.com/your-org/snowflake-medallion.git
cd snowflake-medallion
cp .env.example .env
# Edit .env with your Snowflake account identifier and credential paths

2. Bootstrap Snowflake infrastructure

Each script specifies its required role in the header comment. The bootstrap script handles role switching automatically:

chmod +x scripts/setup/bootstrap.sh
./scripts/setup/bootstrap.sh --env dev --account <your-account>

# For production (requires explicit confirmation):
./scripts/setup/bootstrap.sh --env prod --account <your-prod-account>

Or run individual phases manually (examples use Snowflake CLI with a named connection — snow connection add — swap in snowsql -f if still on SnowSQL):

# Account setup (databases, warehouses, roles, users, network policies,
#                password/session policies, SCIM, authentication policies)
snow sql -f infrastructure/snowflake/account_setup/01_databases.sql
# ... through 08_authentication_policies.sql

# Security controls (tags, masking, row access, managed access, Trust Center)
snow sql -f infrastructure/snowflake/security/01_object_tags.sql
# ... through 07_trust_center.sql

# Monitoring and alerting (resource monitors, audit views, operational + security alerts)
snow sql -f infrastructure/snowflake/monitoring/01_resource_monitors.sql
# ... through 04_privileged_access_alerts.sql

# External integrations (S3, Snowpipe, SQL Server)
snow sql -f infrastructure/snowflake/integrations/01_storage_integration_s3.sql
snow sql -f infrastructure/snowflake/integrations/02_external_stages.sql
snow sql -f infrastructure/snowflake/integrations/03_sqlserver_integration.sql

After bootstrapping, verify your CIS compliance posture (and enable the Trust Center's CIS Benchmarks scanner package — see infrastructure/snowflake/security/07_trust_center.sql):

snow sql -f infrastructure/snowflake/security/06_cis_compliance_checks.sql

3. Configure dbt

cd dbt
pip install "dbt-snowflake~=1.8.0"
cp profiles.yml.example ~/.dbt/profiles.yml
# Edit ~/.dbt/profiles.yml with your Snowflake credentials

dbt deps           # install packages (dbt_utils, dbt_expectations, elementary)
dbt debug          # verify connection
dbt seed           # load reference data (data domains, country/GDPR mapping)
dbt run            # run all models
dbt test           # run data quality tests

See docs/runbooks/onboarding.md for the full developer onboarding guide including RSA key-pair setup and per-developer schema isolation.

4. Apply Power BI row-level security

After dbt has created the gold tables, apply Snowflake RLS and masking policies:

snowsql -f powerbi/row_level_security/rls_snowflake_setup.sql

Then follow powerbi/saml_oauth_setup.md to configure the Azure AD app registration and Snowflake OAuth security integration.

---

Data Model

Silver Layer (FOUNDATION_DB) — conformed entities

Model	Grain	Source
slv_customers	One row per customer	SQL Server via Fivetran
slv_orders	One row per order	SQL Server via Fivetran
slv_order_items	One row per order line item	SQL Server via Fivetran
slv_products	One row per product	SQL Server via Fivetran
slv_events	One row per web/app event	S3 via Snowpipe

Gold Layer (ANALYTICS_DB) — curated data products

Model	Domain	Description
gld_customer_360	Marketing	Customer profile + lifetime order metrics + RFM segmentation
gld_revenue_summary	Finance	Daily revenue roll-up by product category, country, and channel
gld_product_performance	Operations	Product scorecard: velocity, margin, return rate, fulfilment speed

Gold models include a DATA_DOMAIN column used as the anchor for Snowflake Row Access Policies. Power BI users see only the rows for their AD group's domain.

Reference Data (FOUNDATION_DB.REFERENCE) — seed tables

Seed	Description
ref_data_domains	Business domain definitions, owning teams, and Power BI role mappings
ref_country_regions	ISO 3166-1 country → region, GDPR/CCPA applicability, and data residency zone

---

Security Model

This implementation enforces least-privilege RBAC aligned to the CIS Snowflake Foundations Benchmark:

Role	Purpose
LOADER_ROLE	Service account for data ingestion (Fivetran / ADF / Snowpipe)
TRANSFORMER_ROLE	dbt service account — reads bronze, writes silver/gold
DATA_ENGINEER_ROLE	Platform engineers — full read across all layers, DDL in DEV_DB
DATA_ANALYST_ROLE	Business analysts — read gold + silver
DATA_SCIENTIST_ROLE	ML/DS team — read gold + silver, can create dev schemas
POWERBI_ROLE	Power BI gateway service account — read all of ANALYTICS_DB
POWERBI_MARKETING_ROLE	PBI users in AD group PBI_MARKETING (RLS: Marketing rows only)
POWERBI_FINANCE_ROLE	PBI users in AD group PBI_FINANCE (RLS: Finance rows only)
POWERBI_OPERATIONS_ROLE	PBI users in AD group PBI_OPERATIONS (RLS: Operations rows only)

Key controls applied at every layer:

• Network policies — IP allowlisting per service account and per human user population (CIS 2.1/2.2)
• Password policy — 14-char minimum, 90-day rotation, 5-attempt lockout (CIS 1.3)
• Session policy — 60-minute idle timeout for users, 30-minute for privileged accounts (CIS 1.4)
• SCIM provisioning — Azure AD lifecycle management for automated deprovisioning (CIS 1.6)
• Key-pair authentication — all service accounts; no passwords (CIS 1.5)
• Managed access schemas — prevents schema owners from escalating object access (CIS 3.5)
• Dynamic column masking — PII columns (email, phone, name, DOB) masked per role (CIS 4.3)
• Row access policies — domain isolation, tenant isolation, regional data residency (CIS 4.4)
• Object tags — PII category and compliance scope (GDPR, PCI_DSS, etc.) on all sensitive columns
• Security alerts — ACCOUNTADMIN usage, network policy changes, role/user changes (CIS 5.3/6.x)

See docs/architecture/security-model.md for the full model and docs/architecture/cis_benchmark_compliance.md for the complete CIS control mapping.

---

Multi-Account Architecture

For organisations that require environment isolation, business unit separation, or cross-region disaster recovery, this repo includes a multi-account layer under infrastructure/snowflake/multi_account/:

Script	Purpose
01_organizations_setup.sql	Snowflake Organizations setup, account provisioning, org-level cost views
02_cross_account_sharing.sql	Secure Data Sharing between accounts (gold tables, reference data, monitoring)
03_account_replication.sql	Failover Groups for cross-region HA/DR (RPO: 10 min, RTO: ~5 min)

Common topologies:

• Account-per-environment (recommended): <ORG>-DEV / <ORG>-STAGING / <ORG>-PROD
• Account-per-BU: <ORG>-FINANCE / <ORG>-MARKETING / <ORG>-SHARED-SERVICES
• Hub-and-spoke: PROD hub shares gold data to dedicated Analytics and Data Science accounts

See docs/architecture/multi_account_architecture.md for a detailed guide on choosing a topology and configuring each pattern.

---

Deployment & Scheduling

This repo does not ship CI workflow files (earlier GitHub Actions workflows were removed). The recommended execution model is native to Snowflake:

dbt runs — dbt Projects on Snowflake (GA)

dbt Projects on Snowflake runs dbt Core natively inside Snowflake — no external runner, and the SVC_DBT_TRANSFORMER key never leaves your secrets manager:

-- One-time: deploy the dbt/ directory as a DBT PROJECT object
-- (from a Snowsight Workspace connected to this git repo, or via `snow dbt deploy`)

-- Nightly production run, scheduled with a serverless task:
CREATE OR ALTER TASK FOUNDATION_DB.PUBLIC.TASK_DBT_NIGHTLY
    SCHEDULE = 'USING CRON 0 4 * * * UTC'
AS
    EXECUTE DBT PROJECT FOUNDATION_DB.PUBLIC.SNOWFLAKE_MEDALLION
        ARGS='build --target prod';

For PR validation, run snow dbt execute (or dbt build --target ci) against the ci target — it builds everything in an ephemeral TEST_DB schema named after the run (see dbt/profiles.yml.example).

Infrastructure SQL — Snowflake CLI

Deploy infrastructure changes from any runner (or a developer machine) with key-pair auth via scripts/setup/bootstrap.sh, or per-script:

snow sql -f infrastructure/snowflake/security/03_column_masking_policies.sql \
    --temporary-connection --account "$SNOWFLAKE_ACCOUNT" \
    --user "$SNOWFLAKE_USER" --private-key-file "$SNOWFLAKE_PRIVATE_KEY_PATH" \
    --role SECURITYADMIN

If you prefer GitHub Actions/GitLab CI, wrap those two commands in a workflow — use a self-hosted runner with a stable egress IP so the service-account network policy can stay tight (see 05_network_policies.sql), and never allowlist shared hosted-runner IP ranges.

---

Data Quality

All models include dbt tests for:

• not_null and unique on all primary keys
• relationships (referential integrity across layers)
• accepted_values on all categorical/status columns
• dbt_utils.expression_is_true on financial amounts (non-negative)
• dbt_expectations.expect_column_values_to_be_between on timestamps and rates

Custom singular tests:

• test_gold_revenue_no_negative — no negative revenue in the gold finance layer
• test_silver_no_orphan_orders — orphan order rate below 1%
• test_customer_360_completeness — all active customers present in the gold 360 view

Test failures are stored in MONITORING_DB.DATA_QUALITY and trigger the ALERT_DBT_TEST_FAILURES Snowflake alert within 30 minutes.

---

Cortex AI Layer

An optional, governance-first Cortex AI layer lives under infrastructure/snowflake/cortex/ — run 00_cortex_governance.sql before anything else in it (Snowflake grants CORTEX_USER to PUBLIC by default; the script revokes it, pins a model allowlist, sets the cross-region posture, and adds an AI-spend budget alert). Capabilities included:

Capability	What you get
Semantic views + Cortex Analyst	Governed NL Q&A over gld_revenue_summary / gld_customer_360 (RLS-aware, PII excluded from the semantic model)
Cortex Search	"Ask the runbooks" retrieval service over docs/ for on-call engineers and agents
ML anomaly detection	Learned baseline on daily credit spend replaces static cost thresholds
Document AI ingestion	AI_PARSE_DOCUMENT bronze pattern extending the medallion to PDFs/DOCX
AISQL in dbt	Opt-in gold/ai model using AI_CLASSIFY to fill missing product categories (enable_ai_enrichment var)
Agents / Snowflake Intelligence	Assembly guidance for domain-scoped agents over the semantic views + search service

See docs/architecture/cortex_ai.md for the full design, guardrails checklist, and cost model.

---

CIS Snowflake Benchmark Compliance

This template is designed to satisfy all applicable controls in the CIS Snowflake Security Foundations Benchmark v1.0. The implementation status of each control is documented in docs/architecture/cis_benchmark_compliance.md.

Run the compliance check script after bootstrapping to verify your posture:

snowsql -a <your-account> \
  -r ACCOUNTADMIN \
  -f infrastructure/snowflake/security/06_cis_compliance_checks.sql

The final query in the script returns a single-row dashboard showing the count of gaps across the key CIS sections. All values should be 0.

---

Contributing

See CONTRIBUTING.md for branching strategy, PR process, naming conventions, and SQL style guide.

See docs/runbooks/environment_promotion.md for the full process of promoting changes from development through to production.

Credentials must never be committed. Use <PLACEHOLDER> tokens for any org-specific values in SQL scripts, and run a secret scanner (e.g. gitleaks or GitHub secret scanning / push protection) on the repository.