Programming In Snowflake Masterclass 2024 Hands-On!

by World-Class Snowflake Expert, former Data Superhero and SnowPro Certification SME

What you'll 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

Requirements

Basic SQL knowledge

Basic knowledge of programming in Python

Nice to have basic skills in other programming languages: SQL scripting, JavaScript, C#, Java, Scala…

No prior knowledge of Snowflake or other data warehouses is expected!

Basic knowledge of working with a git repository such as GitHub

Description

Who I AmThe 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 LearnHow 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 AboutSQL (DDL/DML/DCL) and Snowflake ScriptingStored Procedures, User-Defined Functions (UDFs), User-Defined Table Functions (UDTFs)Python Client, Snowpark for Python, Python WorksheetsStreamlit Web Apps, Streamlit for Snowflake, Snowflake Native Apps FrameworkSecure Data Sharing and Data Clean RoomsSharing with private Data Exchange or public MarketplaceSnowflake SQL REST API and Snowpipe REST APIQuerying metadata from Account Usage and Information SchemaI tried to cover indeed ALL possible APIs that Snowflake offers today!What is NOT Included in This CourseData 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 BuildCDC Data Pipelines with streams and tasks, or dynamic tablesGeneric hierarchical data viewerHierarchical metadata viewer (for data lineage and object dependencies, role hierarchy, etc.)Enhanced query profileScript automation accessing the SQL REST APIWe’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.

Overview

Section 1: Introduction

Lecture 1 Welcome to this Course

Lecture 2 Best Ways to Benefit from this Course

Lecture 3 Initial Requirements and Project Setup

Section 2: Warehouses and Cost Estimates

Lecture 4 Introduction

Lecture 5 Hands On: Sign-up for a Free Trial Account

Lecture 6 Hands On: Use a Very Small Warehouse

Lecture 7 Hands On: Run Query with a Very Large Warehouse

Lecture 8 Hands On: Just Start a Large Multi-Cluster Warehouse

Lecture 9 Review: Overview of the Snowflake Architecture

Lecture 10 Hands On: Apply Best Practices for Data and Warehouses

Lecture 11 Review: Snowflake Best Practices for Compute and Storage

Section 3: Loading and Accessing CSV Data

Lecture 12 Introduction

Lecture 13 Hands On: Create Query Context

Lecture 14 Hands On: Infer Schema from Staged CSV Files

Lecture 15 Hands On: Copy Data into Tables

Lecture 16 Hands On: Create External S3 Stage

Lecture 17 Review: End-to-End CSV File Uploading

Section 4: Loading and Processing JSON Data

Lecture 18 Introduction

Lecture 19 Review: File Formats

Lecture 20 Hands On: Upload JSON Data

Lecture 21 Hands On: Transform JSON Data

Lecture 22 Review: JSON Data Transformations

Lecture 23 Hands On: Flatten JSON Data

Lecture 24 Review: JSON Data Flattening

Lecture 25 Hands On: Add Constraints and Materialized Views

Lecture 26 Review: Temporary Tables and Materialized Views

Section 5: Sample Data Extraction and Generation

Lecture 27 Introduction

Lecture 28 Hands On: Extract Data Samples with Snowflake

Lecture 29 Hands On: Generate Synthetic Data with Snowflake

Lecture 30 Hands On: Generate Synthetic Data with Python

Lecture 31 Review: Data Sample Extraction and Generation

Lecture 32 Review: Sequences and Identity Columns

Section 6: Stored Procedures and Recursive SQL

Lecture 33 Introduction

Lecture 34 Hands On: Prepare Hierarchical Data

Lecture 35 Hands On: Query Hierarchical Data with Joins

Lecture 36 Hands On: Query Hierarchical Data with Recursive SQL

Lecture 37 Review: SQL Queries for Hierarchical Data

Lecture 38 Hands On: Create UDFs in SQL

Lecture 39 Hands On: Create Stored Procedure in JavaScript

Lecture 40 Review: Stored Procedures and Functions

Section 7: SQL and Snowflake Scripting

Lecture 41 Introduction

Lecture 42 Hands On: Use Case Sensitive Identifiers

Lecture 43 Hands On: Use Identifier References

Lecture 44 Hands On: Access JSON Object Properties

Lecture 45 Review: Identifiers and Variables in Snowflake SQL

Lecture 46 Hands On: Clone Table Data

Lecture 47 Review: Duplicating Table Data and Zero-Copy Cloning

Lecture 48 Hands On: Snowflake Scripting Procedure with Cursor

Lecture 49 Hands On: Snowflake Scripting Procedure with ResultSet

Lecture 50 Hands On: Snowflake Scripting Procedure Calling a JavaScript Function

Lecture 51 Hands On: Test the Generic Hierarchical Viewer in SQL

Lecture 52 Review: Snowflake Scripting

Lecture 53 Hands On: Implement Transaction

Lecture 54 Hands On: More Transaction Use Cases

Lecture 55 Review: Transactions in Snowflake

Section 8: Snowflake Data Pipelines

Lecture 56 Introduction

Lecture 57 Batch & Stream Data Transfer to Snowflake

Lecture 58 Hands On: Manual CDC with MERGE Statement

Lecture 59 Hands On: Enable Change Tracking on a Table

Lecture 60 Hands On: CDC with Streams and Tasks

Lecture 61 Hands On: CDC with Dynamic Table

Lecture 62 Review: CDC (Change Data Capture) in Snowflake

Lecture 63 Hands On: Continuous Data Loading with Snowpipe on S3

Lecture 64 Review: Snowpipe on Amazon S3

Section 9: Hierarchical Data Visualizations

Lecture 65 Introduction

Lecture 66 Hands On: Transform Tabular Data into JSON Format

Lecture 67 Review: Hierarchical Data Formats (JSON, XML, and YAML)

Lecture 68 Hands On: Show Graphs (with GraphViz)

Lecture 69 Hands On: Show Hierarchical Charts (with Plotly)

Lecture 70 Hands On: Show Animated Charts (with D3)

Section 10: Streamlit Web Applications

Lecture 71 Introduction

Lecture 72 Hands On: Create Your First Streamlit Web App

Lecture 73 Hands On: Add Functionality with a Streamlit Tab Control

Lecture 74 Review: Introduction to Streamlit

Lecture 75 Review: Streamlit Layout Components and Interactive Widgets

Lecture 76 Hands On: Cache Streamlit Data

Lecture 77 Review: Streamlit Session and Data Caching

Lecture 78 Hands On: Deploy and Share a Web App in Streamlit Cloud

Lecture 79 Review: Deploying a Web App in Streamlit Cloud

Section 11: Client Programming with Snowflake

Lecture 80 Introduction

Lecture 81 Hands On: Install Snowflake Tools and Utilities

Lecture 82 Review: Snowflake Tools and Utilities

Lecture 83 Hands On: Connect to Snowflake from a Python Client

Lecture 84 Hands On: Connect to Snowflake from .NET in C#

Lecture 85 Hands On: Connect to Snowflake from NodeJS in JavaScript

Lecture 86 Review: Snowflake Connector for Python

Lecture 87 Hands On: Connect the Hierarchical Data Viewer to Snowflake

Section 12: Snowpark API

Lecture 88 Introduction

Lecture 89 Review: Snowpark for Python Architecture

Lecture 90 Hands On: Create Query with DataFrame API

Lecture 91 Snowpark API: The Object Model

Lecture 92 Hands On: Create Stored Procs and UDFs in Python/Java/Scala

Lecture 93 Review: Procedures and UDFs in Python, Java, Scala

Lecture 94 Hands On: Customize Stored Proc from Python Worksheet

Lecture 95 Review: Python Worksheets

Lecture 96 Hands On: Create and Call Stored Procs and UDFs from Python

Lecture 97 Review: Snowpark for Python

Section 13: Streamlit in Snowflake

Lecture 98 Introduction

Lecture 99 Hands On: Deploy a Streamlit Web App in Snowflake

Lecture 100 Review: Streamlit in Snowflake

Lecture 101 Review: Event Tables and Alerts

Section 14: Access Control in Snowflake

Lecture 102 Introduction

Lecture 103 Hands On: Create Script for Users, Roles and Privileges

Lecture 104 Hands On: Inspect Users, Roles and Privileges

Lecture 105 Review: Users, Roles and Privileges

Lecture 106 Hands On: Run SnowSQL Scripts using Variable Substitution

Lecture 107 Review: Multi-Tenant Architectures with Var Substitution

Section 15: Snowflake REST APIs

Lecture 108 Introduction

Lecture 109 Hands On: Configure Key Pair Authentication and Generate JWT

Lecture 110 Review: Key Pair Authentication in Snowflake

Lecture 111 Hands On: Run a Query from Script with the SQL REST API

Lecture 112 Review: The Snowflake SQL REST API

Lecture 113 Hands On: Ingest Files with Snowpipe REST API

Lecture 114 Review: The Snowpipe REST API

Section 16: Data Governance in Snowflake

Lecture 115 Introduction

Lecture 116 Hands On: Tag Objects

Lecture 117 Hands On: Classify Data

Lecture 118 Review: Data Governance in Snowflake

Lecture 119 Hands On: Create a Restricted View

Lecture 120 Hands On: Create a Masking Policy

Lecture 121 Hands On: Create a Tag-Based Masking Policy

Lecture 122 Hands On: Create a Row Access Policy

Lecture 123 Review: Column Masking and Row Access Policies

Section 17: Data Sharing in Snowflake

Lecture 124 Introduction

Lecture 125 Hands On: Create an Inbound Data Share

Lecture 126 Hands On: Consume an Outbound Data Share

Lecture 127 Review: Secure Data Sharing and Reader Accounts

Lecture 128 Hands On: Create a Listing for Private Data Shares

Lecture 129 Review: Data Exchange and Snowflake Marketplace

Lecture 130 Hands On: Solve Yao's Millionaire Problem for Data Clean Rooms

Lecture 131 Hands On: Create Data Clean Room with Row Access Policies

Lecture 132 Review: Data Clean Rooms

Section 18: Create a Multi-Page Hierarchical Metadata Viewer

Lecture 133 Introduction

Lecture 134 Review: Information Schema vs Account Usage

Lecture 135 Hands On: Create ER Diagram Viewer

Lecture 136 Review: ER Diagram Viewer

Lecture 137 Hands On: Create Users and Roles Inspector

Lecture 138 Review: Users and the Role Hierarchy

Lecture 139 Hands On: Create Object Dependency Inspector

Lecture 140 Review: Object Dependencies in Snowflake

Lecture 141 Hands On: Create Data Lineage Inspector

Lecture 142 Review: Data Lineage in Snowflake

Lecture 143 Hands On: Create Task Dependencies Inspector

Lecture 144 Review: Task Dependencies and Task Workflow Runs

Lecture 145 Hands On: Deploy as Multi-Page Streamlit in Snowflake App

Section 19: Snowflake Native App Framework

Lecture 146 Introduction

Lecture 147 Hands On: Deploy the Hierarchical Data Viewer as a Native App

Lecture 148 Review: Snowflake Native App Framework

Section 20: Create an Admin Dashboard in Snowsight

Lecture 149 Introduction

Lecture 150 Hands On: Create an Administration Dashboard in Snowsight

Lecture 151 Review: System Views for Account Usage

Section 21: Data Analytics with Snowflake

Lecture 152 Introduction

Lecture 153 Hands On: Convert Subqueries to CTEs

Lecture 154 Hands On: Create Aggregated Queries

Lecture 155 Hands On: Pivot and Unpivot Data

Lecture 156 Review: Intermediate Data Analytics in SQL

Lecture 157 Hands On: Query Data from the Past

Lecture 158 Review: Time Travel and Fail-safe

Section 22: Advanced Analytics with Snowflake

Lecture 159 Introduction

Lecture 160 Hands On: Query with QUALIFY

Lecture 161 Hands On: Moving Average Query

Lecture 162 Hands On: Query with Rank Functions

Lecture 163 Hands On: Query with Offset Functions

Lecture 164 Review: Window Functions

Lecture 165 Hands On: Use Statistical Functions

Lecture 166 Review: Statistical Functions

Section 23: Snowflake Query Optimization

Lecture 167 Introduction

Lecture 168 Hands On: Use the Query History

Lecture 169 Hands On: Use the Result Cache

Lecture 170 Hands On: Interpret the Query Profile

Lecture 171 Hands On: Interpret a Query Plan from EXPLAIN

Lecture 172 Hands On: Enhanced Query Profile and Analyzer

Lecture 173 Review: Query Performance Tools

Lecture 174 Hands On: Inspect a Table Cluster Distribution

Lecture 175 Review: Clustering Keys

Section 24: Wrapping Up

Lecture 176 Congratulations, You Made It!

Data Engineers new to Snowflake, looking to acquire deep knowledge of the APIs,Software Engineers and Developers looking to access Snowflake in different ways,Data Analysts looking for medium to complex queries they can write for Snowflake,Data Architects looking to design data pipelines for Snowflake,Software Architects looking to design apps and tools with and for Snowflake,Any other practitioner who enjoys data and coding, new or not to Snowflake