Snowflake Financial Services Demo (FINSERV)

Full-stack Snowflake demo project implementing a medallion architecture data pipeline for a fictional financial services company. Covers 25+ Snowflake features across data engineering, Snowpipe Streaming, Cortex AI functions, AI/ML, security, data governance, and application development.

---

Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                           FINSERV_DB                                        │
│                                                                             │
│  ┌──────────┐    ┌──────────┐    ┌──────────────┐    ┌────────────────┐    │
│  │   BASE   │───▶│   RAW    │───▶│   CURATED    │───▶│  CONSUMPTION   │    │
│  │          │    │          │    │              │    │                │    │
│  │ 7 tables │    │ 7 streams│    │ 4 dynamic    │    │ 6 dynamic      │    │
│  │ GENERATOR│    │ Snowpipe │    │   tables     │    │   tables       │    │
│  │ data     │    │ S3 land. │    │              │    │ 2 SPs, 2 UDFs │    │
│  │ Streaming│    │ Streaming│    │              │    │ 1 UDTF         │    │
│  │ ingest   │    │ CDC      │    │              │    │ Cortex AI Fns  │    │
│  └──────────┘    └──────────┘    └──────────────┘    └────────────────┘    │
│       │                │                                      │            │
│       │           Task DAG                              MCP Server         │
│       │          (4 tasks)                          Cortex Search (2)      │
│       ▼                                             Streamlit Dashboard    │
│  CSV Generator                                      ML Notebooks (2)      │
│  S3 Upload                                          Semantic Model        │
│                                                                             │
│  ┌──────────────────────────────────────┐    ┌────────────────────────┐    │
│  │          SECURITY LAYER              │    │     GOVERNANCE         │    │
│  │ 9 roles (RBAC), network policy       │    │ 4 tags, CLASSIFY      │    │
│  │ 5 masking policies, 2 RAPs           │    │ tag-based masking      │    │
│  │ session policy, IP restrictions      │    │ aggregation/projection │    │
│  │                                      │    │ 5 DMFs, audit views    │    │
│  └──────────────────────────────────────┘    └────────────────────────┘    │
└─────────────────────────────────────────────────────────────────────────────┘

---

Prerequisites

Requirement	Details
Snowflake Account	Enterprise edition or higher (for Dynamic Tables, Cortex AI)
Role	ACCOUNTADMIN (or equivalent with CREATE WAREHOUSE, DATABASE privileges)
Python	3.11+ (for local notebooks, CSV generator, Streamlit)
Packages	snowflake-connector-python, snowflake-snowpark-python, streamlit, pandas, altair
Optional	AWS S3 bucket (for Snowpipe ingestion — demo works without it)

---

Quick Start

# 1. Clone the repo
git clone <repo-url> && cd snowpark

# 2. Deploy core pipeline (files 01-08) in order
#    Execute each .sql file in Snowsight or via SnowSQL

# 3. Run advanced SQL patterns
#    Execute 09_snowpark_sql_sheet.sql

# 4. Deploy Snowpark Python objects
#    Execute 10_snowpark_python_sheet.sql

# 5. Explore Cortex AI functions
#    Execute 11b_cortex_ai_functions.sql

# 6. Deploy Cortex AI services
#    Execute 13_cortex_search_agent.sql and 14_managed_mcp_server.sql

# 7. Run the Streamlit dashboard
streamlit run 15_streamlit_dashboard.py

# 8. Validate the pipeline
#    Execute 18_monitoring_and_validation.sql

# 9. Test incremental data flow
#    Execute 19_incremental_test_data.sql

# 10. Explore performance concepts
#    Execute 20_performance_exploration.sql

---

File-by-File Execution Guide

Phase 1: Foundation (Files 01-02)

#	File	What It Does	Objects Created
01	01_setup_database.sql	Creates warehouse, database, and 4 medallion schemas	FINSERV_WH (X-SMALL), FINSERV_DB, schemas: BASE, RAW, CURATED, CONSUMPTION
02	02_base_tables_and_data.sql	Creates 7 base tables and populates them with GENERATOR() synthetic data	CUSTOMERS (2K rows), ACCOUNTS (3K), TRANSACTIONS (10K), RISK_ASSESSMENTS (2K, VARIANT), MARKET_DATA (5K, VARIANT), SUPPORT_TICKETS (1K, TEXT), COMPLIANCE_DOCUMENTS (200, TEXT+VARIANT)

Phase 2: Ingestion (Files 03-04)

#	File	What It Does	Objects Created
03	03_csv_generator_and_s3_upload.py	Local Python — generates CSV files and optionally uploads to S3	CSV files (local), S3 objects (optional)
04	04_s3_stage_and_snowpipe.sql	Creates file format, S3 landing tables, external stage, Snowpipe, and Snowpipe Streaming demo (Python SP for synthetic micro-batch ingestion)	CSV_FORMAT, 3 landing tables, stage, pipes, STREAMING_TRANSACTIONS table, STREAM_STREAMING_TRANSACTIONS stream, SP_STREAM_SYNTHETIC_TRANSACTIONS SP

Phase 3: Medallion Pipeline (Files 05-08)

#	File	What It Does	Objects Created
05	05_raw_layer.sql	Creates 2 CDC streams + 2 event tables for alerting/escalation	TRANSACTIONS_STREAM, SUPPORT_TICKETS_STREAM, TRANSACTION_ALERTS, TICKET_ESCALATIONS
06	06_curated_layer.sql	Builds curated dynamic tables	DT_CUSTOMER_PROFILE, DT_TRANSACTION_ENRICHED, DT_SUPPORT_ENRICHED, DT_MARKET_LATEST, DT_RISK_FACTORS_PARSED, DT_COMPLIANCE_ENRICHED
07	07_consumption_layer.sql	Builds consumption-layer analytics tables	DT_CUSTOMER_360, DT_DAILY_FINANCIAL_METRICS, DT_RISK_DASHBOARD, DT_CHANNEL_PERFORMANCE, DT_CHURN_FEATURES, DT_MONTHLY_REVENUE, DT_MARKET_OVERVIEW, DT_COMPLIANCE_SUMMARY, DT_RISK_FACTOR_SUMMARY
08	08_tasks_and_dag.sql	Creates a 4-task DAG for event-driven alerting	TASK_ROOT_SCHEDULER → TASK_DETECT_FLAGGED_TXN + TASK_ESCALATE_TICKETS → TASK_REFRESH_METRICS

Phase 4: Advanced SQL & Snowpark (Files 09-11)

#	File	What It Does	Objects Created
09	09_snowpark_sql_sheet.sql	10 advanced SQL patterns: window functions, LATERAL FLATTEN, PIVOT/UNPIVOT, CTEs, ROLLUP, percentiles, fraud detection	Read-only queries — no persistent objects
10	10_snowpark_python_sheet.sql	6 Python-in-SQL objects: 2 stored procedures, 2 UDFs, 1 UDTF	SP_RFM_SEGMENTATION, SP_PROCESS_TRANSACTIONS, ANOMALY_SCORE UDF, RISK_TIER UDF, PARSE_RISK_FACTORS UDTF, SP_PIPELINE_SUMMARY
11	11_snowpark_python_notebook.ipynb	Local Jupyter notebook — Snowpark DataFrame API exploration	No Snowflake objects (runs locally)
11b	11b_cortex_ai_functions.sql	18-section showcase of all Cortex AI functions — classic (COMPLETE, SENTIMENT, SUMMARIZE, EXTRACT_ANSWER, TRANSLATE, CLASSIFY_TEXT, EMBED_TEXT_768) and modern (AI_EXTRACT, AI_FILTER, AI_AGG, AI_REDACT, AI_SIMILARITY, AI_SUMMARIZE_AGG, ENTITY_SENTIMENT, AI_COMPLETE with named params). Includes Python SP and UDF	SP_AI_TICKET_ANALYZER SP, UDF_RISK_NARRATIVE UDF

Phase 5: Cortex AI & MCP (Files 12-14)

#	File	What It Does	Objects Created
12	12_cortex_analyst_semantic_model.yaml	Semantic model YAML for Cortex Analyst (upload to stage)	Stage file at @CONSUMPTION.CORTEX_STAGE/
13	13_cortex_search_agent.sql	Creates Cortex Search services on support tickets and compliance docs	SEARCH_SUPPORT_TICKETS, SEARCH_COMPLIANCE_DOCS, CORTEX_STAGE
14	14_managed_mcp_server.sql	Creates a Snowflake-managed MCP server exposing Search, SQL, and custom UDF/SP tools	FINSERV_MCP_SERVER with 6 tools

Note: 14_mcp_server.py is the legacy custom Python MCP server. Use 14_managed_mcp_server.sql instead — it requires no external infrastructure.

Phase 6: Applications & ML (Files 15-17)

#	File	What It Does	Objects Created
15	15_streamlit_dashboard.py	Local Streamlit app — multi-tab KPI dashboard (Executive, Customers, Transactions, Risk, Channel)	Runs locally with streamlit run
16	16_ml_churn_classification.ipynb	Local notebook — customer churn prediction (XGBoost/Random Forest) using DT_CHURN_FEATURES	Trained model (local)
17	17_ml_revenue_regression.ipynb	Local notebook — revenue forecasting regression using DT_MONTHLY_REVENUE	Trained model (local)

Phase 7: Validation & Testing (Files 18-19)

#	File	What It Does	Objects Created
18	18_monitoring_and_validation.sql	Pipeline health checks: row counts, stream status, DT refresh history, task DAG, data quality spot checks	Read-only validation queries
19	19_incremental_test_data.sql	Two-batch incremental test: inserts 5 new customers, 4 accounts, 8 transactions, 2 tickets; triggers task DAG; verifies propagation	New rows in base tables, verifies DT refresh

Phase 8: Performance (File 20)

#	File	What It Does	Objects Created
20	20_performance_exploration.sql	10 performance concepts: EXPLAIN plans, warehouse sizing, clustering, search optimization, caching, spill analysis, query acceleration, resource monitors	TRANSACTIONS_CLUSTERED, FINSERV_WH_SMALL, FINSERV_MONITOR, PERFORMANCE_SUMMARY

Phase 9: Security & Data Governance (Files 21-22)

#	File	What It Does	Objects Created
21	21_security.sql	End-to-end security: RBAC hierarchy (5 functional + 4 access roles), network policy, session policy, dynamic data masking (PII + financial), row access policies (ticket assignment + country-based)	9 custom roles, FINSERV_NETWORK_POLICY, FINSERV_SESSION_POLICY, 5 masking policies, 2 row access policies, SUPPORT_AGENT_MAP, ANALYST_COUNTRY_MAP
22	22_data_governance.sql	Data governance: classification tags, automated SYSTEM$CLASSIFY, tag-based masking, aggregation policy, projection policy, 5 data metric functions, access history audit, governance summary dashboard	GOVERNANCE schema, 4 tags, 4 tag-based masking policies, 1 aggregation policy, 1 projection policy, 5 DMFs, GOVERNANCE_SUMMARY

---

Snowflake Features Learning Path

A sequential curriculum for learning Snowflake features using this project. Follow the order below — each topic builds on the previous.

Level 1: Core Platform

#	Topic	Feature	File	Key Concepts
1	Warehouses	Virtual Warehouses	01	Sizing (X-SMALL→4XL), AUTO_SUSPEND, AUTO_RESUME, INITIALLY_SUSPENDED
2	Databases & Schemas	Logical Organization	01	Namespacing, medallion architecture (BASE→RAW→CURATED→CONSUMPTION)
3	Table DDL	CREATE TABLE	02	Data types: NUMBER, VARCHAR, TIMESTAMP_NTZ, BOOLEAN, VARIANT, TEXT
4	Synthetic Data	GENERATOR()	02	UNIFORM(), RANDOM(), SEQ4(), ARRAY_CONSTRUCT(), OBJECT_CONSTRUCT()
5	Semi-Structured Data	VARIANT	02	JSON in columns, dot notation, bracket notation, type casting

Level 2: Data Loading

#	Topic	Feature	File	Key Concepts
6	File Formats	CREATE FILE FORMAT	04	CSV parsing: FIELD_DELIMITER, SKIP_HEADER, NULL_IF, TRIM_SPACE
7	Stages	External Stages (S3)	04	STORAGE_INTEGRATION, URL, encryption, DIRECTORY
8	COPY INTO	Bulk Loading	04	FROM stage, FILE_FORMAT, ON_ERROR, MATCH_BY_COLUMN_NAME
9	Snowpipe	Continuous Loading	04	AUTO_INGEST, SQS notifications, SYSTEM$PIPE_STATUS()
10	Snowpipe Streaming	Low-Latency Ingestion	04	Micro-batch INSERT via Python SP, configurable batch size/delay, CDC stream on landing table

Level 3: Change Data Capture

#	Topic	Feature	File	Key Concepts
11	Streams	CDC Tracking	05	SHOW_INITIAL_ROWS, METADATA$ACTION, METADATA$ISUPDATE, SYSTEM$STREAM_HAS_DATA()
12	Stream Types	Standard vs Append-only	05	Standard (full CDC), Append-only (inserts only)

Level 4: Transformations

#	Topic	Feature	File	Key Concepts
13	Dynamic Tables	Declarative Pipelines	06-07	TARGET_LAG (1 min, 5 min, DOWNSTREAM), REFRESH_MODE (FULL vs INCREMENTAL), INITIALIZE
14	Materialized Views	Auto-Maintained Views	06	MV on VARIANT data, automatic refresh, query rewrite
15	LATERAL FLATTEN	JSON Array Expansion	06, 09	FLATTEN(INPUT =>, OUTER => TRUE), VALUE, INDEX
16	QUALIFY	Window Filter	06	ROW_NUMBER() OVER (...) with QUALIFY for deduplication

Level 5: Orchestration

#	Topic	Feature	File	Key Concepts
17	Tasks	Scheduled Execution	08	SCHEDULE (CRON/interval), WAREHOUSE, AFTER (predecessors)
18	Task DAGs	Dependency Graphs	08	Root→children→grandchild, WHEN conditions, EXECUTE TASK
19	Stream + Task	Event-Driven Processing	08	WHEN SYSTEM$STREAM_HAS_DATA(), MERGE INTO with stream

Level 6: Advanced SQL

#	Topic	Feature	File	Key Concepts
20	Window Functions	Analytics	09	SUM/COUNT/ROW_NUMBER OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN)
21	PIVOT / UNPIVOT	Reshaping Data	09	PIVOT (AGG FOR col IN (...)), UNPIVOT (VALUE FOR METRIC IN (...))
22	GROUP BY ROLLUP	Subtotals	09	ROLLUP(), CUBE(), GROUPING SETS
23	PERCENTILE_CONT	Statistical Functions	09	WITHIN GROUP (ORDER BY ...), MEDIAN(), STDDEV()

Level 7: Snowpark Python

#	Topic	Feature	File	Key Concepts
24	Stored Procedures	Python SPs	10	LANGUAGE PYTHON, PACKAGES, HANDLER, session.table(), write.save_as_table()
25	Scalar UDFs	Python UDFs	10	RETURNS FLOAT/VARCHAR, single-row transform, pure Python
26	Table UDFs (UDTFs)	Python UDTFs	10	RETURNS TABLE(...), class with process() method, yield rows
27	DataFrame API	Snowpark DataFrames	11	col(), filter(), group_by(), agg(), join(), with_column()

Level 8: Cortex AI

#	Topic	Feature	File	Key Concepts
28	LLM Completions	CORTEX.COMPLETE	11b	Risk narratives, structured JSON output, temperature, response_format
29	Sentiment Analysis	CORTEX.SENTIMENT / ENTITY_SENTIMENT	11b	Per-row scoring (-1 to 1), per-entity sentiment extraction
30	Summarization	CORTEX.SUMMARIZE / AI_SUMMARIZE_AGG	11b	Single-doc abstracts, cross-row aggregated summaries
31	Question Answering	CORTEX.EXTRACT_ANSWER	11b	Q&A over document text, multi-question patterns
32	Translation	CORTEX.TRANSLATE	11b	Multi-language output (es, fr, de)
33	Classification	CORTEX.CLASSIFY_TEXT	11b	Zero-shot classification with custom label arrays
34	Embeddings	CORTEX.EMBED_TEXT_768	11b	Vector embeddings, VECTOR_COSINE_SIMILARITY for semantic matching
35	AI Extract & Filter	AI_EXTRACT / AI_FILTER	11b	Structured entity extraction, natural language boolean filtering, PROMPT()
36	AI Aggregate & Redact	AI_AGG / AI_REDACT	11b	Cross-row insights, PII redaction (full/selective/chained)
37	AI Similarity & Complete	AI_SIMILARITY / AI_COMPLETE	11b	Semantic scoring, named params, model_parameters, show_details
38	Semantic Models	Cortex Analyst	12	YAML schema: tables, dimensions, measures, time_dimensions, filters
39	Cortex Search	Vector Search	13	ON column, ATTRIBUTES, TARGET_LAG, embedding model
40	Cortex Agent	Multi-Tool Agent	13	Tool routing: analyst_text_to_sql, cortex_search

Level 9: Integration & Apps

#	Topic	Feature	File	Key Concepts
41	MCP Server	Managed MCP	14	CREATE MCP SERVER, tool types: CORTEX_SEARCH, SYSTEM_EXECUTE_SQL, GENERIC
42	Streamlit	Data Apps	15	st.connection("snowflake"), tabs, Altair charts, metrics
43	ML Pipelines	Model Training	16-17	Feature engineering from DTs, classification, regression

Level 10: Operations & Performance

#	Topic	Feature	File	Key Concepts
44	Pipeline Monitoring	Observability	18	INFORMATION_SCHEMA views, SYSTEM$ functions, data quality checks
45	Incremental Testing	CDC Validation	19	Insert→stream→task→DT refresh→verify counts
46	EXPLAIN Plans	Query Profiling	20	EXPLAIN USING TABULAR/JSON, execution plan analysis
47	Clustering	Storage Optimization	20	CLUSTER BY, SYSTEM$CLUSTERING_INFORMATION, automatic clustering
48	Search Optimization	Point Lookup Speed	20	ADD SEARCH OPTIMIZATION ON EQUALITY/SUBSTRING
49	Result Caching	Query Cache	20	USE_CACHED_RESULT, PERCENTAGE_SCANNED_FROM_CACHE
50	Resource Monitors	Cost Guardrails	20	CREDIT_QUOTA, TRIGGERS, NOTIFY/SUSPEND/SUSPEND_IMMEDIATE
51	Query Acceleration	Elastic Compute	20	SYSTEM$ESTIMATE_QUERY_ACCELERATION, QUERY_ACCELERATION_ELIGIBLE

---

Object Inventory

Tables (BASE)

Table	Rows	Key Columns
CUSTOMERS	2,000	CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, COUNTRY, ANNUAL_INCOME, CREDIT_SCORE
ACCOUNTS	3,000	ACCOUNT_ID, CUSTOMER_ID, ACCOUNT_TYPE, BALANCE, CREDIT_LIMIT, STATUS
TRANSACTIONS	10,000	TXN_ID, ACCOUNT_ID, TXN_DATE, TXN_TYPE, AMOUNT, CATEGORY, CHANNEL, IS_FLAGGED
RISK_ASSESSMENTS	2,000	ASSESSMENT_ID, CUSTOMER_ID, ASSESSED_AT, RISK_DATA (VARIANT)
MARKET_DATA	5,000	DATA_ID, TICKER, TRADE_DATE, MARKET_DATA (VARIANT)
SUPPORT_TICKETS	1,000	TICKET_ID, CUSTOMER_ID, SUBJECT, BODY (TEXT), PRIORITY, RESOLUTION_STATUS
COMPLIANCE_DOCUMENTS	200	DOC_ID, DOC_TYPE, DOC_CONTENT (TEXT), METADATA (VARIANT)
STREAMING_TRANSACTIONS	variable	TXN_ID (autoincrement), ACCOUNT_ID, TXN_DATE, AMOUNT, CATEGORY, CHANNEL, IS_FLAGGED, _BATCH_ID, _STREAMED_AT

Dynamic Tables

Schema	Table	TARGET_LAG	Refresh Mode	Rows
CURATED	DT_CUSTOMER_PROFILE	1 minute	FULL	2,000
CURATED	DT_TRANSACTION_ENRICHED	1 minute	FULL	10,000
CURATED	DT_SUPPORT_ENRICHED	1 minute	FULL	1,000
CURATED	DT_MARKET_LATEST	1 minute	FULL	~50
CURATED	DT_RISK_FACTORS_PARSED	1 minute	INCREMENTAL	6,000
CURATED	DT_COMPLIANCE_ENRICHED	1 minute	FULL	200
CONSUMPTION	DT_CUSTOMER_360	DOWNSTREAM	FULL	2,000
CONSUMPTION	DT_DAILY_FINANCIAL_METRICS	DOWNSTREAM	FULL	181
CONSUMPTION	DT_RISK_DASHBOARD	DOWNSTREAM	FULL	1,273
CONSUMPTION	DT_CHANNEL_PERFORMANCE	DOWNSTREAM	FULL	905
CONSUMPTION	DT_CHURN_FEATURES	5 minutes	FULL	2,000
CONSUMPTION	DT_MONTHLY_REVENUE	5 minutes	FULL	7
CONSUMPTION	DT_MARKET_OVERVIEW	DOWNSTREAM	FULL	~50
CONSUMPTION	DT_COMPLIANCE_SUMMARY	DOWNSTREAM	FULL	~10
CONSUMPTION	DT_RISK_FACTOR_SUMMARY	DOWNSTREAM	FULL	~20

Streams (RAW)

TRANSACTIONS_STREAM, SUPPORT_TICKETS_STREAM, STREAM_STREAMING_TRANSACTIONS (CDC on Snowpipe Streaming landing table)

Event Tables (RAW)

Table	Purpose
TRANSACTION_ALERTS	Flagged transactions detected by stream-driven task
TICKET_ESCALATIONS	High/urgent tickets escalated by stream-driven task

Tasks (RAW)

Task	Schedule	Predecessors
TASK_ROOT_SCHEDULER	5 MINUTE	—
TASK_DETECT_FLAGGED_TXN	—	TASK_ROOT_SCHEDULER
TASK_ESCALATE_TICKETS	—	TASK_ROOT_SCHEDULER
TASK_REFRESH_METRICS	—	TASK_DETECT_FLAGGED_TXN, TASK_ESCALATE_TICKETS

Cortex AI Services

Object	Type	Schema
SEARCH_SUPPORT_TICKETS	Cortex Search Service	CONSUMPTION
SEARCH_COMPLIANCE_DOCS	Cortex Search Service	CONSUMPTION
FINSERV_MCP_SERVER	Managed MCP Server	CONSUMPTION
CORTEX_STAGE	Stage (semantic model)	CONSUMPTION

Snowpark Python Objects (CONSUMPTION)

Object	Type	Description
SP_RFM_SEGMENTATION	Stored Procedure	RFM customer segmentation
SP_PROCESS_TRANSACTIONS	Stored Procedure	Transaction channel summary
SP_PIPELINE_SUMMARY	Stored Procedure (TABLE)	Pipeline health report
ANOMALY_SCORE	UDF	Z-score anomaly detection
RISK_TIER	UDF	Composite risk tier
PARSE_RISK_FACTORS	UDTF	Parse VARIANT risk data
SP_AI_TICKET_ANALYZER	Stored Procedure	Batch Cortex AI sentiment + classification
UDF_RISK_NARRATIVE	UDF	Risk narrative via Cortex REST API

Snowpark Python Objects (BASE)

Object	Type	Description
SP_STREAM_SYNTHETIC_TRANSACTIONS	Stored Procedure	Snowpipe Streaming emulation — generates synthetic micro-batches

---

Troubleshooting

Issue	Cause	Fix
ASSESSMENT_DATE not found	Column is named ASSESSED_AT in DDL	Use ASSESSED_AT
RISK_DATA:risk_level returns NULL	Field is credit_history (string)	Use RISK_DATA:credit_history
RISK_DATA:factors not found	Field is risk_factors (array)	Use RISK_DATA:risk_factors
COUNT(*) AS ROWS fails	ROWS is a reserved word	Use ROW_COUNT
DT shows FULL refresh mode	Complex queries (subqueries, CURRENT_TIMESTAMP, upstream FULL)	Expected behavior; no fix needed
Cortex Agent DDL fails	CREATE CORTEX AGENT not available in all regions	Skip agent creation; Search services work independently
Snowpipe creation fails	No real S3 bucket configured	Deploy file format + landing tables only
Insufficient privileges	Wrong role or connection	Use ACCOUNTADMIN role on default connection

---

License

Internal demo project — not for production use.