Snowflake Recipes: A Problem-Solution Approach to Implementing Modern Data Pipelines

Table of Contents
About the Authors
About the Technical Reviewer
Preface
Chapter 1: Introduction to Snowflake
	Recipe 1-1. Connecting to Snowflake
		Problem
		Solution
			Native Snowflake
				Snowsight
				SnowSQL
				Snowflake APIs
				Snowpark API
			Snowflake Connector(s)
				Python
				Spark
				Kafka
			Snowflake Driver(s)
				Node.js
				JDBC
				ODBC
	Recipe 1-2. Selecting an Appropriate Cloud Provider
		Problem
		Solution
			Amazon Web Services (AWS)
			Microsoft Azure
			Google Cloud Platform (GCP)
		Key Differentiators
		Additional Considerations
	Recipe 1-3. Snowflake Organizations
		Problem
		Solution
	Recipe 1-4. Snowflake Editions
		Problem
		Solution
			Standard Edition
			Enterprise Edition
			Business Critical Edition
			Virtual Private Snowflake (VPS)
Chapter 2: Bringing Your Data into Snowflake
	Recipe 2-1. Ready in AWS Cloud
		Prerequisites
			Step 1: Create Buckets with Appropriate Configuration
			Step 2: Activate Security Token Services
		Reading S3 Data When Encryption Is SSE-S3 or SSE-KMS
			Problem
			Solution
				Step 1: Configure Access Permissions for the S3 Bucket
				Step 2: Create IAM Policies for KMS
				Step 3: Create the IAM Role in AWS
				Step 4: Create a Cloud Storage Integration in Snowflake
				Step 5: Retrieve the AWS IAM User for your Snowflake Account
				Step 6: Grant the IAM User Permissions to Access Bucket Objects
				Step 7: Create External Stages in Snowflake
		Reading S3 Data When Encryption Is SSE-CSE
			Problem
			Solution
				Step 1: Create an IAM User and Grant Access to S3
				Step 2: Write a Client Program to Put Files in S3
				Step 3: Alter or Create Storage Integration
				Step 4: Test Decryption
				Step 5: Create a Snowflake Stage to Access the S3 Data
		Reading S3 Data Using Access Keys
			Problem
			Solution
	Recipe 2-2. Ready in Azure Cloud
		Prerequisites
			Step 1: Create a Storage Account with MMK Encryption
			Step 2: Create a Storage Account with CMK Encryption
		Reading Azure Storage When Encryption Is SSE-MMK or SSE-CMK
			Problem
			Solution
				Step 1: Create a Storage Integration in Snowflake
				Step 2: Retrieve the Azure App Name for Your Snowflake Integration
				Step 3: Grant Snowflake Access to Storage Location and Key Vault
				Step 4: Create External Stages in Snowflake
		Reading Azure Storage When Your Encryption Is Using CSE
			Problem
			Solution
		Reading Azure Storage Using SAS tokens
			Problem
			Solution
				Step 1: Generate a SAS Token
				Step 2: Create a Stage Referencing the SAS Token
	Recipe 2-3. Snowflake Object Types
		Problem
		Solution
			Persisted Query Results
			Permanent Tables
			External Tables
			Directory Tables
			Regular Views
			Materialized Views
			Secure Views
			Secure Materialized Views
			Temporary Tables
			Transient Tables
			Streams
			Dynamic Tables
			Hybrid Tables
			Event Tables
			Iceberg Tables
			Making the Right Choice
				External Tables
				Transient Tables
				Temporary Tables
				Materialized Views
				Streams
				Dynamic Tables
				Hybrid Tables
				Permanent Tables
				Iceberg Tables
Chapter 3: Handling Atypical Data
	Recipe 3-1. Handling Semi-Structured Data
		Handling JSON Data in Snowflake
		Problem
			Prerequisites
			Solution
		Handling Avro Data in Snowflake
		Problem
			Prerequisites
			Solution
		Handling ORC Data in Snowflake
		Problem
			Prerequisites
			Solution
		Handling Parquet Data in Snowflake
		Problem
			Prerequisites
			Solution
		Handling XML Data in Snowflake
		Problem
			Prerequisites
			Solution
	Recipe 3-2. Schema Detection
		Schema Detection
		Problem
		Solution
		Table Schema Evolution
		Problem
			Solution
	Recipe 3-3. Binary and Unstructured Data
		Handling Binary Data in Snowflake Tables
		Problem
		Solution
		Handling Binary Data in Snowflake Stages
		Problem
			Prerequisites
			Solution
	Recipe 3-4. Geospatial Data
		Problem
			Prerequisites
			Solution
				GEOGRAPHY Data Type
				GEOMETRY Data Type
				Choosing Between GEOGRAPHY and GEOMETRY Data Types
	Recipe 3-5. JSON Data Operations
		Problem
		Solution
			Differences Between Array, Object, and Variant
			Which One Should You Use?
			Exploring Various Functions for Parsing and Creating JSON Objects
Chapter 4: Data Security and Privacy
	Recipe 4-1. Compliance Regulations
		Problem
		Solution
	Recipe 4-2. Security Best Practices
		Multi-Factor Authentication
		Private Link
		Network Policies
		Role-Based Access Control
		Problem
		Solution
	Recipe 4-3. Data Privacy
		Snowflake Row Access Policies (Row-Level Security)
		Snowflake Masking Policies (Column-Level Security)
		Snowflake Classification
		Problem
		Solution
			Adding Masking Polices
			Implementing Row-Level Security
			A Closer Look at the Data Classification
	Recipe 4-4. Data Encryption
		Problem
		Solution
			Using Amazon Web Services’ Key Management Service
Chapter 5: Handling Near and Real-Time Data
	Recipe 5-1. Data Loading Using Snowpipe
		Problem
		Prerequisites
		Solution
			Step 1: Create an external stage.
				Step 2: Confirm file formats.
				Step 3: Create a table.
				Step 4: Create a pipe.
				Step 5: Configure the S3 bucket to use the event notification.
				Step 6: Test the ingestion.
		Problem
		Prerequisites
		Solution
		Problem
		Prerequisites
		Solution
			Configure the Event Grid Subscription
			Step 2: Creating a Notification Integration in Snowflake
			Create a Stage and a Pipe
			Verify and Test the Ingestion
		Problem
		Solution
		Problem
		Prerequisites
		Solution
	Recipe 5-2. Data Loading Using Streams and Tasks
		Problem
		Solution
		Problem
		Solution
		Problem
		Solution
	Recipe 5-3. Data Loading Using Kafka
		Problem
		Prerequisites
			Create a Cluster and a Topic in Confluent Cloud
		Solution
			Step 1
			Step 2
			Step 3
	Recipe 5-4. Change Tracking
		Problem
		Solution
	Recipe 5-5. Dynamic Tables
		Problem
		Solution
	Recipe 5-6. Iceberg Tables
		Problem
		Solution
			Step 1
			Step 2
			Step 3
			Step 4
			Step 5
		Problem
		Solution
			Step 1: Create an External Volume
				Step 1-1: Create an IAM Policy to Grant Access to S3
				Step 1-2: Create an IAM Role
				Step 1-3: Create an External Volume in Snowflake
				Step 1-4: Grant IAM User Permission to Access Bucket Objects
			Step 2: Create an Iceberg Table
Chapter 6: Programmable Data Pipelines
	Recipe 6-1. Using Client APIs
		Problem
		Solution
			Snowflake Python Connector
				Step 1: Install the Connector
				Step 2: Test the Connector
		Problem
		Solution
			Snowflake JDBC Driver
				Step 1: Install the JDBC driver
				Step 2: Test the Connector
		Problem
		Solution
			Snowflake Python API
				Step 1: Install the API Client
				Step 2: Test the Connector
			Snowflake REST API
	Recipe 6-2. Using Snowpark API
		Snowpark: Client Side and Server Side
		Snowpark Python
		Problem
		Solution
		Problem
		Solution
			Step 1: Install the Connector
			Step 2: Change Import Statements in Your Code
	Recipe 6-3. Using Snowflake Functions and Stored Procs
		Stored Procedures
		User-Defined Functions
		Problem
		Solution
		Problem
		Solution
			SQL UDF and SQL Stored Procedure
			Javascript UDF and Javascript Stored Procedure
			Python UDF and Python Stored Procedure
			Java UDF and Java Stored Procedure
	Recipe 6-4. What and How of Snowpark Python API
		Problem
		Solution
			Approach 1: Local Code Using the Snowpark API
			Approach 2: Local Functions as UDFs and Stored Procs
			Approach 3: UDFs and Stored Procs Directly
			Approach 4: A Blended Approach
		Problem
		Solution
	Recipe 6-5. Snowflake SQLAlchemy Toolkit
		Problem
		Solution
Chapter 7: Data Reusability and Monetization
	Recipe 1-1. Data Democratization
		Problem
		Solution
		How It Works
			Unifying Fragmented Data
			Transforming Operations and Innovation
	Recipe 1-2. Data as a Product (DaaP)
		Problem
		Solution
		Solution
			Sunhaven Cove Tourism Insights
			Sunhaven Cove Safe Streets
	Recipe 1-3. Snowflake Marketplace
		Problem
		Solution
		How It Works
			Accessing the Marketplace
			Browsing the Treasure Trove
			Finding Your Data Gem
			Evaluating the Data Treasure
			Securing Your Data Haul
	Recipe 1-4. Data Monetization
		Problem
		Solution
		How It Works
			Data Preparation
			Data Asset Identification
			Impact
			Snowflake Data Cloud: The Ideal Platform for Data Monetization
Chapter 8: Data Recovery and Protection
	Overview
	Recipe 8-1. Fail-safe and Time Travel
		Problem
		Solution
	Recipe 8-2. Snowflake Clones
		Problem
		Solution
	Recipe 8-3. Account Replication and Failover (Disaster Recovery)
		Problem
		Solution
	Recipe 8-4. Client Redirect (Business Continuity)
		Problem
		Solution
Chapter 9: Application Integration
	Overview
	Recipe 9-1. Connecting Applications
		Problem
		Solution
			Prerequisites
			The Steps to Connect
	Recipe 9-2. Snowflake Unistore
		Problem
		Solution
	Recipe 9-3. Streamlit
		Problem
		Solution
			Key Components
				Import and Setup
				Input Form (Figure 9-11)
				Form Submission and Basic Validation
				Snowflake Merge
				Data Display
	Recipe 9-4. Snowflake for Applications
		Problem
		Solution
			Images: Self-Contained Packages
			Image Registry/Repositories: A Structured Library
			Compute Pools: Power on Demand
			Specification Files: Defining the Blueprint
			Services: The Heart of Execution
			Jobs: Focused Bursts of Processing
			Transform Data into Actionable Insights
			Streamline Workflows, Minimize Errors
			Targeted Collaboration and Customization
Chapter 10: Machine Learning
	Recipe 10-1. Snowpark and Third-Party Packages
		Problem
		Solution
			Import via Snowflake Stage
			Import Using Snowflake’s New Custom Package Feature
			Import from the Anaconda Snowflake Channel
	Recipe 10-2. Machine Learning
		Problem
		Solution
			Prerequisites
				Approach 1: Data in Snowflake but All Other Compute External to Snowflake
				Approach 2: The Data and Model in Snowflake but Compute External to Snowflake
				Approach 3: The Data, Model, and Code Runs in Snowflake
				Approach 4: Data, Model, and Code Run in Snowflake and Utilize Snowflake ML Features
					Snowflake ML Modeling
					Snowflake Model Registry
	Recipe 10-3. Snowpark Container Services
		Problem
		Solution
			Image Repository
			Compute Pool
			Service
				Step 1: Create the API.
				Step 2: Create a Snowflake role with the necessary grants.
				Step 3: Switch to the newly created role.
				Step 4: Create security integration, access integration and network rule.
				Step 5: Create a compute pool.
				Step 6: Create an image repository.
				Step 7: Create a stage for the service file.
					Step 8: Create a file named Dockerfile and add the code to build the image (refer to source code).
				Step 9: Push the image to the Snowflake repository.
				Step 10: Configure and push the service spec file.
				Step 11: Create a service.
				Step 12: Test the service using remote functions.
	Recipe 10-4. Snowflake Cortex
		Problem
		Prerequisites
			Step 1: Download the dataset.
			Step 2: Split large files into multiple smaller files.
			Step 3: Load data into Snowflake.
		Solution
		Problem
		Solution
			Step 1: Build a base query for the search.
			Step 2: Enable change tracking.
			Step 3: Build the Cortex Search service.
			Step 4: Test the Cortex Search service.
			Step 5: Consume the service via Snowpark API or REST API.
Index