Getting Started
Snowflake Web User Interfaces
Prep Work
Snowsight Orientation
Snowsight Preferences
Navigating Snowsight Worksheets
- Context Setting
- Improved Productivity
Snowflake Community
Snowflake Certifications
Snowday and Snowflake Summit Events
Important Caveats About Code Examples in the Book
Code Cleanup
Summary
Knowledge Check
Creating and Managing the Snowflake Architecture
Prep Work
Traditional Data Platform Architectures
- Shared-Disk (Scalable) Architecture
- Shared-Nothing (Scalable) Architecture
- NoSQL Alternatives
The Snowflake Architecture
The Cloud Services Layer
- Managing the Cloud Services Layer
- Billing for the Cloud Services Layer
The Query Processing (Virtual Warehouse) Compute Layer
- Virtual Warehouse Size
- Scaling Up a Virtual Warehouse to Process Large Data Volumes and Complex Queries
- Scaling Out with Multicluster Virtual Warehouses to Maximize
- Concurrency 35
- Creating and Using Virtual Warehouses
- Separation of Workloads and Workload Management
- Billing for the Virtual Warehouse Layer
Centralized (Hybrid Columnar) Database Storage Layer
- Introduction to Zero-Copy Cloning
- Introduction to Time Travel
- Billing for the Storage Layer
Snowflake Caching
- Query Result Cache
- Metadata Cache
- Virtual Warehouse Local Disk Cache
Code Cleanup
Summary
Knowledge Check
Creating and Managing Snowflake Securable Database Objects
Prep Work
Creating and Managing Snowflake Databases
Creating and Managing Snowflake Schemas
- INFORMATION_SCHEMA
- ACCOUNT_USAGE Schema
- Schema Object Hierarchy
Introduction to Snowflake Tables
Creating and Managing Views
Introduction to Snowflake Stages: File Format Included
Extending SQL with Stored Procedures and UDFs
- User-Defined Function (UDF): Task Included
- Secure SQL UDTF That Returns Tabular Value (Market Basket Analysis Example)
- Stored Procedures
Introduction to Pipes, Streams, and Sequences
Snowflake Streams (Deep Dive)
Snowflake Tasks (Deep Dive)
Code Cleanup
Summary
Knowledge Check
Exploring Snowflake SQL Commands, Data Types, and Functions
Prep Work 112
Working with SQL Commands in Snowflake 112
- DDL Commands
- DCL Commands
- DML Commands
- TCL Commands
- DQL Command
SQL Query Development, Syntax, and Operators in Snowflake
- SQL Development and Management
- Query Syntax
- Query Operators
- Long-Running Queries, and Query Performance and Optimization
- Snowflake Query Limits
Introduction to Data Types Supported by Snowflake
- Numeric Data Types
- String and Binary Data Types
- Date and Time Input/Output Data Types
- Semi-Structured Data Types
- Unstructured Data Types
- How Snowflake Supports Unstructured Data Use
Snowflake SQL Functions and Session Variables
- Using System-Defined (Built-In) Functions
- Creating SQL and JavaScript UDFs and Using Session Variables
- External Functions
Code Cleanup
Summary
Knowledge Check
Leveraging Snowflake Access Controls
Prep Work
Creating Snowflake Objects
Snowflake System-Defined Roles
Creating Custom Roles
- Functional-Level Business and IT Roles
- System-Level Service Account and Object Access Roles
Role Hierarchy Assignments: Assigning Roles to Other Roles
Granting Privileges to Roles
Assigning Roles to Users
Testing and Validating Our Work
User Management
Role Management
Snowflake Multi-Account Strategy
Managing Users and Groups with SCIM
Code Cleanup
Summary
Knowledge Check
Data Loading and Unloading
Prep Work
Basics of Data Loading and Unloading
- Data Types
- File Formats
- Data File Compression
- Frequency of Data Processing
- Snowflake Stage References
- Data Sources
Data Loading Tools
- Snowflake Worksheet SQL Using INSERT INTO and INSERT ALL Commands
- Web UI Load Data Wizard
- SnowSQL CLI SQL PUT and COPY INTO Commands
- Data Pipelines
- Third-Party ETL and ELT Tools
Alternatives to Loading Data
Tools to Unload Data
Data Loading Best Practices for Snowflake Data Engineers
- Select the Right Data Loading Tool and Consider the Appropriate Data
- Type Options
- Avoid Row-by-Row Data Processing
- Choose the Right Snowflake Virtual Warehouse Size and Split Files as Needed
- Transform Data in Steps and Use Transient Tables for Intermediate Results
Code Cleanup
Summary
Knowledge Check
Implementing Data Governance, Account Security, and Data Protection and Recovery
Prep Work
Snowflake Security
- Controlling Account Access
- Monitoring Activity with the Snowflake ACCESS_HISTORY Account Usage View
- Data Protection and Recovery
- Replication and Failover
Democratizing Data with Data Governance Controls
- INFORMATION_SCHEMA Data Dictionary
- Object Tagging
- Classification
- Data Masking
- Row Access Policies and Row-Level Security
- External Tokenization
- Secure Views and UDFs
- Object Dependencies
Code Cleanup
Summary
Knowledge Check
Managing Snowflake Account Costs
Prep Work
Snowflake Monthly Bill
- Storage Fees
- Data Transfer Costs
- Compute Credits Consumed
Creating Resource Monitors to Manage Virtual Warehouse Usage and Reduce Costs
- Resource Monitor Credit Quota
- Resource Monitor Credit Usage
- Resource Monitor Notifications and Other Actions
- Resource Monitor Rules for Assignments
- DDL Commands for Creating and Managing Resource Monitors
Using Object Tagging for Cost Centers
Querying the ACCOUNT_USAGE View
Using BI Partner Dashboards to Monitor Snowflake Usage and Costs
Snowflake Agile Software Delivery
- Why Do We Need DevOps?
- Continuous Data Integration, Continuous Delivery, and Continuous Deployment
- What Is Database Change Management?
- How Zero-Copy Cloning Can Be Used to Support Dev/Test Environments
Code Cleanup
Summary
Knowledge Check
Analyzing and Improving Snowflake Query Performance
Prep Work
Analyzing Query Performance
- QUERY_HISTORY Profiling
- HASH() Function
- Web UI History
Understanding Snowflake Micro-Partitions and Data Clustering
- Partitions Explained
- Snowflake Micro-Partitions Explained
Snowflake Data Clustering Explained
- Clustering Width and Depth
- Choosing a Clustering Key
- Creating a Clustering Key
- Reclustering
Performance Benefits of Materialized Views
Exploring Other Query Optimization Techniques
- Search Optimization Service
- Query Optimization Techniques Compared
Summary
Code Cleanup
Knowledge Check
Configuring and Managing Secure Data Sharing
Snowflake Architecture Data Sharing Support
The Power of Snowgrid
Data Sharing Use Cases
Snowflake Support for Unified ID 2.0
Snowflake Secure Data Sharing Approaches
Prep Work
Snowflake’s Direct Secure Data Sharing Approach
- Creating Outbound Shares
- How Inbound Shares Are Used by Snowflake Data Consumers
How to List and Shop on the Public Snowflake Marketplace
- Snowflake Marketplace for Providers
- Standard Versus Personalized Data Listings
- Harnessing the Power of a Snowflake Private Data Exchange
Snowflake Data Clean Rooms
Important Design, Security, and Performance Considerations
- Share Design Considerations
- Share Security Considerations
- Share Performance Considerations
- Difference Between Database Sharing and Database Cloning
- Data Shares and Time Travel Considerations
- Sharing of Data Shares
Summary
Code Cleanup
Knowledge Check
Visualizing Data in Snowsight
Prep Work
Data Sampling in Snowsight
- Fixed-Size Sampling Based on a Specific Number of Rows
- Fraction-Based Sampling Based on Probability
- Previewing Fields and Data
- Sampling Examples
Using Automatic Statistics and Interactive Results
Snowsight Dashboard Visualization
- Creating a Dashboard and Tiles
- Working with Chart Visualizations
- Aggregating and Bucketing Data
- Editing and Deleting Tiles
Collaboration
- Sharing Your Query Results
- Using a Private Link to Collaborate on Dashboards
Summary
Code Cleanup
Knowledge Check
Workloads for the Snowflake Data Cloud
Prep Work
Data Engineering
Data Warehousing
- Data Vault 2.0 Modeling
- Transforming Data within Snowflake
Data Lake
Data Collaboration
- Data Monetization
- Regulatory and Compliance Requirements for Data Sharing
Data Analytics
- Advanced Analytics for the Finance Industry
- Advanced Analytics for the Healthcare Industry
- Advanced Analytics for the Manufacturing Industry and Logistics Services
- Marketing Analytics for Retail Verticals and the Communications and Media Industry
Data Applications
Data Science
Cybersecurity Using Snowflake as a Security Data Lake
- Overcoming the Challenges of a SIEM-Only Architecture
- Search Optimization Service Versus Clustering
Unistore
- Transactional Workload Versus Analytical Workload
- Hybrid Tables
Summary
Code Cleanup
Knowledge Check
- Answers to the Knowledge Check Questions
- Snowflake Object Naming Best Practices
- Setting Up a Snowflake Trial Account