by World-Class Snowflake Expert, former Data Superhero and SnowPro Certification SME
What you will learn
Query anything in Snowflake through SQL and Snowflake Scripting
Use most if not all programming APIs offered by Snowflake
Access and use Snowflake as a software or data developer
Build real-life tools and apps with and for Snowflake
Process and access hierarchical data and metadata in Snowflake Data Cloud
Description
Who I Am
- The only world-class expert from Canada selected for the Snowflake Data Superhero program in 2021.
- SnowPro Certification SME (Subject Matter Expert): i.e. many exam questions have been created by me.
- Passed four SnowPro certification exams to date (with no retakes): Core, Architect, Data Engineer, Data Analyst.
- Specialized in Snowflake for the past few years, I worked for Snowflake Partner companies, and I served dozens of clients in this capacity or as an independent consultant.
- Today I continue to work with Snowflake, but I am no longer affiliated with their company in any capacity. I recently left their programs to keep my professional independence.
What You Will Learn
- How to access, expand and automate Snowflake through most if not ALL their existing APIs.
- How to build useful real-life tools and small apps with Snowflake APIs.
- How to ingest CSV and JSON data into Snowflake, through data pipelines and Snowpipe.
- How to write medium to complex data analytics queries for Snowflake.
- How to optimize queries, compute, storage and overall costs for Snowflake.
- How to process and render semi-structured and hierarchical data and metadata in Snowflake.
- I tried to cover almost EVERYTHING from Snowflake, only from a programming perspective.
What Snowflake APIs You Will Learn About
- SQL (DDL/DML/DCL) and Snowflake Scripting
- Stored Procedures, User-Defined Functions (UDFs), User-Defined Table Functions (UDTFs)
- Python Client, Snowpark for Python, Python Worksheets
- Streamlit Web Apps, Streamlit for Snowflake, Snowflake Native Apps Framework
- Secure Data Sharing and Data Clean Rooms
- Sharing with private Data Exchange or public Marketplace
- Snowflake SQL REST API and Snowpipe REST API
- Querying metadata from Account Usage and Information Schema
- I tried to cover indeed ALL possible APIs that Snowflake offers today!
What is NOT Included in This Course
‘;
}});
- Data Science and Machine Learning APIs.
- Most external integrations, such as external functions, or Kafka and Spark connectors.
- Integrations with data transfer applications or other third-party partner apps.
- Client driver programming in Go, PHP, Java etc.
- Snowpark programming in Java or Scala.
- Main focus was on SQL and Python, with small extra snippets in JavaScript, C#, Java, Scala.
Real-Life Applications You Will Learn To Build
- CDC Data Pipelines with streams and tasks, or dynamic tables
- Generic hierarchical data viewer
- Hierarchical metadata viewer (for data lineage and object dependencies, role hierarchy, etc.)
- Enhanced query profile
- Script automation accessing the SQL REST API
- We’ll build all apps from scratch. We’ll use then Streamlit – for many of them – to create simple web apps, local or remote. We may deploy them into Snowflake, as Streamlit Apps. Or even share them with local partner accounts, as Native Apps.
- I sold tools similar to these to real-life clients and Snowflake partners!
No other course, book or documentation around – including those from Snowflake! – will offer as much insights, hands-on exercises and knowledge transfer as my course here, guaranteed!
Enroll today, to keep this course forever. And help me continue to update it with new APIs Snowflake comes frequently up with.
Introduction
Welcome to this Course
Best Ways to Benefit from this Course
Initial Requirements and Project Setup
Warehouses and Cost Estimates
Introduction
Hands On: Sign-up for a Free Trial Account
Hands On: Use a Very Small Warehouse
Hands On: Run Query with a Very Large Warehouse
Hands On: Just Start a Large Multi-Cluster Warehouse
Review: Overview of the Snowflake Architecture
Hands On: Apply Best Practices for Data and Warehouses
Review: Snowflake Best Practices for Compute and Storage
Check Your Knowledge
Loading and Accessing CSV Data
Introduction
Hands On: Create Query Context
Hands On: Infer Schema from Staged CSV Files
Hands On: Copy Data into Tables
Hands On: Create External S3 Stage
Review: End-to-End CSV File Uploading
Check Your Knowledge
Loading and Processing JSON Data
Introduction
Review: File Formats
Hands On: Upload JSON Data
Hands On: Transform JSON Data
Review: JSON Data Transformations
Hands On: Flatten JSON Data
Review: JSON Data Flattening
Hands On: Add Constraints and Materialized Views
Review: Temporary Tables and Materialized Views
Check Your Knowledge
Sample Data Extraction and Generation
Introduction
Hands On: Extract Data Samples with Snowflake
Hands On: Generate Synthetic Data with Snowflake
Hands On: Generate Synthetic Data with Python
Review: Data Sample Extraction and Generation
Review: Sequences and Identity Columns
Check Your Knowledge
Stored Procedures and Recursive SQL
Introduction
Hands On: Prepare Hierarchical Data
Hands On: Query Hierarchical Data with Joins
Hands On: Query Hierarchical Data with Recursive SQL
Review: SQL Queries for Hierarchical Data
Hands On: Create UDFs in SQL
Hands On: Create Stored Procedure in JavaScript
Review: Stored Procedures and Functions
Check Your Knowledge
SQL and Snowflake Scripting
Introduction
Hands On: Use Case Sensitive Identifiers
Hands On: Use Identifier References
Hands On: Access JSON Object Properties
Review: Identifiers and Variables in Snowflake SQL
Hands On: Clone Table Data
Review: Duplicating Table Data and Zero-Copy Cloning
Hands On: Snowflake Scripting Procedure with Cursor
Hands On: Snowflake Scripting Procedure with ResultSet
Hands On: Snowflake Scripting Procedure Calling a JavaScript Function
Hands On: Test the Generic Hierarchical Viewer in SQL
Review: Snowflake Scripting
Hands On: Implement Transaction
Hands On: More Transaction Use Cases
Review: Transactions in Snowflake
Check Your Knowledge
Snowflake Data Pipelines
Introduction
Batch & Stream Data Transfer to Snowflake
Hands On: Manual CDC with MERGE Statement
Hands On: Enable Change Tracking on a Table
Hands On: CDC with Streams and Tasks
Hands On: CDC with Dynamic Table
Review: CDC (Change Data Capture) in Snowflake
Hands On: Continuous Data Loading with Snowpipe on S3
Review: Snowpipe on Amazon S3
Check Your Knowledge
Hierarchical Data Visualizations
Introduction
Hands On: Transform Tabular Data into JSON Format
Review: Hierarchical Data Formats (JSON, XML, and YAML)
Hands On: Show Graphs (with GraphViz)
Hands On: Show Hierarchical Charts (with Plotly)
Hands On: Show Animated Charts (with D3)
Check Your Knowledge
Streamlit Web Applications
Introduction
Hands On: Create Your First Streamlit Web App
Hands On: Add Functionality with a Streamlit Tab Control
Review: Introduction to Streamlit
Review: Streamlit Layout Components and Interactive Widgets
Hands On: Cache Streamlit Data
Review: Streamlit Session and Data Caching
Hands On: Deploy and Share a Web App in Streamlit Cloud
Review: Deploying a Web App in Streamlit Cloud
Check Your Knowledge
Client Programming with Snowflake
Introduction
Hands On: Install Snowflake Tools and Utilities
Review: Snowflake Tools and Utilities
Hands On: Connect to Snowflake from a Python Client
Hands On: Connect to Snowflake from .NET in C#
Hands On: Connect to Snowflake from NodeJS in JavaScript
Review: Snowflake Connector for Python
Hands On: Connect the Hierarchical Data Viewer to Snowflake
Check Your Knowledge
Snowpark API
Introduction
Review: Snowpark for Python Architecture
Hands On: Create Query with DataFrame API
Snowpark API: The Object Model
Hands On: Create Stored Procs and UDFs in Python/Java/Scala
Review: Procedures and UDFs in Python, Java, Scala
Hands On: Customize Stored Proc from Python Worksheet
Review: Python Worksheets
Hands On: Create and Call Stored Procs and UDFs from Python
Review: Snowpark for Python
Check Your Knowledge
Streamlit in Snowflake
Introduction
Hands On: Deploy a Streamlit Web App in Snowflake
Review: Streamlit in Snowflake
Review: Event Tables and Alerts
Check Your Knowledge
Access Control in Snowflake
Introduction
Hands On: Create Script for Users, Roles and Privileges
Hands On: Inspect Users, Roles and Privileges
Review: Users, Roles and Privileges
Hands On: Run SnowSQL Scripts using Variable Substitution
Review: Multi-Tenant Architectures with Var Substitution
Check Your Knowledge
Snowflake REST APIs
Introduction
Hands On: Configure Key Pair Authentication and Generate JWT
Review: Key Pair Authentication in Snowflake
Hands On: Run a Query from Script with the SQL REST API
Review: The Snowflake SQL REST API
Hands On: Ingest Files with Snowpipe REST API
Review: The Snowpipe REST API
Check Your Knowledge
Data Governance in Snowflake
Introduction
Hands On: Tag Objects
Hands On: Classify Data
Review: Data Governance in Snowflake
Hands On: Create a Restricted View
Hands On: Create a Masking Policy
Hands On: Create a Tag-Based Masking Policy
Hands On: Create a Row Access Policy
Review: Column Masking and Row Access Policies
Check Your Knowledge
Data Sharing in Snowflake
Introduction
Hands On: Create an Inbound Data Share
Hands On: Consume an Outbound Data Share
Review: Secure Data Sharing and Reader Accounts
Hands On: Create a Listing for Private Data Shares
Review: Data Exchange and Snowflake Marketplace
Hands On: Solve Yao’s Millionaire Problem for Data Clean Rooms
Hands On: Create Data Clean Room with Row Access Policies
Review: Data Clean Rooms
Check Your Knowledge
Create a Multi-Page Hierarchical Metadata Viewer
Introduction
Review: Information Schema vs Account Usage
Hands On: Create ER Diagram Viewer
Review: ER Diagram Viewer
Hands On: Create Users and Roles Inspector
Review: Users and the Role Hierarchy
Hands On: Create Object Dependency Inspector
Review: Object Dependencies in Snowflake
Hands On: Create Data Lineage Inspector
Review: Data Lineage in Snowflake
Hands On: Create Task Dependencies Inspector
Review: Task Dependencies and Task Workflow Runs
Hands On: Deploy as Multi-Page Streamlit in Snowflake App
Check Your Knowledge
Snowflake Native App Framework
Introduction
Hands On: Deploy the Hierarchical Data Viewer as a Native App
Review: Snowflake Native App Framework
Check Your Knowledge
Create an Admin Dashboard in Snowsight
Introduction
Hands On: Create an Administration Dashboard in Snowsight
Review: System Views for Account Usage
Check Your Knowledge
Data Analytics with Snowflake
Introduction
Hands On: Convert Subqueries to CTEs
Hands On: Create Aggregated Queries
Hands On: Pivot and Unpivot Data
Review: Intermediate Data Analytics in SQL
Hands On: Query Data from the Past
Review: Time Travel and Fail-safe
Check Your Knowledge
Advanced Analytics with Snowflake
Introduction
Hands On: Query with QUALIFY
Hands On: Moving Average Query
Hands On: Query with Rank Functions
Hands On: Query with Offset Functions
Review: Window Functions
Hands On: Use Statistical Functions
Review: Statistical Functions
Check Your Knowledge
Snowflake Query Optimization
Introduction
Hands On: Use the Query History
Hands On: Use the Result Cache
Hands On: Interpret the Query Profile
Hands On: Interpret a Query Plan from EXPLAIN
Hands On: Enhanced Query Profile and Analyzer
Review: Query Performance Tools
Hands On: Inspect a Table Cluster Distribution
Review: Clustering Keys
Check Your Knowledge
Wrapping Up
Congratulations, You Made It!
Bonus Lecture: More Courses to Explore
