Programming in Snowflake Masterclass Hands-On

Show how Snowflake scales compute with large virtual warehouses on big data, estimate costs, and share best practices for compute and storage during a quick demo with a free trial.

Learn to sign up for a free trial Snowflake account, choose enterprise edition and AWS region, activate the Snowsight console, and access up to 30 days or $400 credits.

Navigate the Snowflake web UI with your profile and accountadmin role, review the default x-small warehouse, and configure auto resume and auto suspend to manage costs.

Run and size large Snowflake queries with Snowsight on the Snowflake sample data tpcds_sf100tcl, using a 4x-large warehouse with 128 nodes to query the store_sales table and estimate costs.

Demonstrates starting and suspending a very large multi-cluster virtual warehouse in enterprise edition, revealing per-second billing, minimum one-minute resume charges, and overall cost implications.

Explore Snowflake architecture featuring elastic parallel processing that decouples compute from storage, virtual warehouses, cloud providers, SQL engine, REST API, serverless tasks, and time travel.

Review and suspend idle Snowflake warehouses, set default auto suspend after one minute, and monitor cost management, query history, and zero-copy clones to avoid wasted credits.

Minimize compute costs with an x-small, single-node warehouse that auto suspends after one minute and use standard edition with economy mode. Avoid duplication; limit time travel and fail-safe.

Upload CSV data into Snowflake using stages and the copy command, infer schemas, access staged files directly, including external stages like AWS S3, with a VSCode setup demo.

Navigate Snowflake’s web UI and sql worksheets to manage databases and schemas, switch contexts with use schema, and run ddl like create or drop database, and describe objects.

Upload a local csv to an internal stage, infer its schema with Snowflake table functions, and create a table from the inferred schema using a csv file format.

Explore how to generate and adjust table definitions with infer schema and get ddl, then load data into Snowflake tables using copy into from a stage with proper file formats.

Create an external stage in Snowflake with Amazon S3 by provisioning an S3 bucket and folder, configuring an IAM user and policy, and validating access and cleanup.

Learn end-to-end csv uploading in snowflake, covering copy into table, staging options, internal and external stages, put/get, and copy into from stage with file formats, validation, and infer schema.

Master Snowflake's file formats: CSV, JSON, Parquet. Upload JSON into a variant to bridge semi-structured data with tabular data using flatten and lateral joins; explore materialized views, keys, and constraints.

Explore Snowflake's file formats, including csv, json, parquet, and avro, with notes on xml, yaml not supported, and learn how to create named file formats for copy into and stages.

Upload and transform JSON data in Snowflake by creating a JSON file format, loading from a stage, inferring schema, and loading into a temporary table.

Explore transforming json data in Snowflake by flattening arrays with lateral flatten, casting extracted values, and creating a department table from json inputs.

Explore JSON data transformations in Snowflake using object and array functions to construct and query objects and arrays. Use a cheat sheet for functions like object_construct, object_keys, array_construct, and array_contains.

Learn to flatten hierarchical json data into a tabular format in Snowflake using parse_json, table flatten, and lateral flatten, including outer join options for missing managers or years.

Flatten json data in Snowflake by using table or lateral flatten to expand arrays into rows, handling managers and years, and using result scan and table returns for cached results.

Add primary key constraints to department number and a foreign key from employee. Join the tables to compute salaries per department, then create a view and a materialized view.

Learn how temporary and transient tables work in Snowflake, including session-only scope, no fail-safe, and purging after the session, plus when to use materialized views.

Generate realistic test data in Snowflake using sample data, the sample clause, and synthetic data functions, then compare sequences, identity columns, and Faker-based generation for benchmarking.

Learn two methods to create fake data in Snowflake: extract random samples from existing tables and generate synthetic data, with hands-on demo using a customer table and sample clause.

Generate synthetic data in Snowflake using a table generator and scalar random functions for fields like name, city, license plate, and email.

Generate 1000 synthetic records with the Python faker library to create realistic names, addresses, cities, states, and emails, then load into a pandas dataframe.

learn how to extract data samples and generate synthetic data in Snowflake using the sample clause and table sample, seed determinism, and generator functions for realistic testing.

Learn how Snowflake generates automatic identifiers with sequences and identity columns, including start values, increments, and the get next value function; compare with UUIDs and portable unique identifiers for migrations.

Explore how to represent and query hierarchical employee-manager data in Snowflake using recursive ctes and recursive views, and encapsulate common queries with views, stored procedures, and user-defined functions.

Learn to display hierarchical data by self-joining the employee table to link employees with their managers, then create a view and export a csv.

Explore querying hierarchical data with joins to reveal an employee's manager chain, including the manager of the manager, via left joins, path concatenation, and cte-based indentation.

Explore expressing hierarchies from tabular data in Snowflake using connect by queries, recursive CTEs, and recursive views; master path and level, indentation, and path cleanup for readable hierarchies.

Explore representing hierarchical data in SQL by building a view of employee and manager names, computing level and path, and comparing recursive CTEs, connect by, and recursive views in Snowflake.

Learn to create and call Snowflake user defined functions (UDFs) and user defined table functions (UDTs), and work with stored procedures, including examples like get manager and get subordinates.

Learn how to create and use a JavaScript stored procedure in Snowflake, including creating with create or replace, executing dynamic SQL, handling parameters, and returning results.

Review stored procedures and functions in snowflake, covering create procedure, create function for udf and udtf, returns clauses, sql scripting and javascript patterns, and considerations with python, java, and scala.

Explore the differences between Snowflake's SQL and Snowflake Scripting, including DDL, DML, zero-copy cloning, and transactions, with hands-on examples of cursor, resultset, and declarative versus procedural approaches.

Snowflake stores unquoted identifiers in uppercase and makes keywords case insensitive. Use double quotes to force case sensitive identifiers and avoid mixed case to prevent confusion or duplicates.

Learn to use identifier references in Snowflake: convert to identifiers with the identifier function, handle case with quotes, reference columns by position, and manage session variables.

Explore accessing json object properties and array elements in Snowflake by loading json into a variant, creating a table, and using dot notation and indexing to extract values.

Review identifiers and variables in Snowflake SQL, including case sensitivity and quoting. Learn to use parse_json for json, session variables with a dollar sign, and parameterized query placeholders.

Master techniques to duplicate and clone table data using create table like, create table as select, and zero-copy clone, and to compare truncate, delete, and update operations in Snowflake.

Explore Snowflake sql concepts from ddl and dml to dql, including show and describe, create table, ctas, zero-copy cloning, and begin transaction to commit or rollback.

Demonstrate snowflake scripting by building a generic stored procedure that uses a cursor to return a dynamic employee manager view. Pass a table name and bind variables for dynamic references.

Learn how to create a Snowflake scripting procedure that returns a result set directly, replacing the cursor and executing the query immediately, and understand its internal cursor behavior.

Learn to use snowflake scripting to create a JavaScript function UDF that returns SQL statement as a string and call it from a procedure using string interpolation and execute immediate.

Test the generic hierarchy viewer with portfolio data loaded from CSV, creating a portfolio table with child and parent columns and validating indented, dot-separated paths via a JavaScript store procedure.

Explore Snowflake scripting, Snowflake's procedural extension to SQL designed for stored procedures, enabling variables, cursors, result sets, and control flow like begin blocks, loops, and exceptions.

Explore implementing and managing transactions in Snowflake scripting, including begin transaction vs begin block, commit, rollback, abort, and describing transactions with transaction IDs.

Explore scoped transactions and the behavior of inner and outer transactions in Snowflake, including begin transaction, commit, rollback, autocommit effects, and how DDL statements affect transactions.

Master Snowflake transactions by using begin transaction and begin block, then commit or rollback. Compare explicit versus implicit transactions, autocommit behavior, and scope rules, including read committed isolation.

Implement change data capture and streaming in Snowflake for near real-time synchronization, and automate CSV ingestion with Snowpipe using an external stage on AWS S3, employing MERGE and streams.

Discover how batch initial loads and CDC-based increments populate a Snowflake stage and snapshot table, applying inserts, updates, and deletes via merge, change tracking, streams and tasks, or dynamic tables.

Demonstrate manual CDC with a merge statement by syncing a source stage table to a target, handling insert, update, and delete via a del flag and ID match.

Enable change tracking on the source table, then use the information function to view the snapshot of changes since a saved timestamp and update the target.

Master cdc data pipelines with Snowflake streams and tasks, creating a source and target, using a merge statement, and managing scheduling and credits between serverless and warehouse tasks.

Discover change data capture with dynamic tables in Snowflake, using streams and tasks to propagate source updates and track inserts, updates, and deletes at a dynamic target.

Explore manual and automated change data capture in Snowflake using merge, streams, tasks, and dynamic tables with change tracking.

Learn how to set up a Snowpipe with auto ingestion from an S3 external stage, configure AWS credentials, and load csv data into a Snowflake table.

Snowpipe enables continuous data loading from external stages such as S3, Azure storage, or Google storage when auto ingest is enabled, triggering a copy into a table on file arrival.

Explore hierarchical data formats such as JSON, XML, and YAML, and discover free open-source visualization options like Graphviz, Plotly, and D3 for graphs, charts, and trees.

Transform tabular data into json by prototyping with Python, pandas, and simple templates, converting an employee manager csv into json, xml, and yaml formats with hierarchical structures.

Learn how to work with hierarchical data formats json, xml, and yaml in snowflake, including converting between formats, validating with online tools, and building parent-child structures with a children property.

Explore creating hierarchical graphs with Graphviz by loading csv data into a pandas data frame, generating a digraph of edges, and customizing labels, styles, and directions.

Create and render interactive hierarchical charts with plotly using a pandas data frame, including treemap and sunburst, in a python 3.9 virtual environment.

Explore how to build animated charts with d3, including collapsible tree, radial dendrogram, and circular packing, by wiring template html to json data in code examples.

Explore how to upload csv files, select columns, and share a Streamlit prototype online, with layout components, widgets, state, data cache, and deployment.

Build your first Streamlit web app to visualize hierarchical data and interactive charts using JSON, XML, YAML formats, D3, and Plotline, with a guided environment setup.

Explore building a Streamlit app with a multi-tab layout, exposing data frames, graphs, and charts via top controls and a left sidebar, with graphviz, plotly, and d3 visuals.

Explore Streamlit as a minimal, rapid application development framework for data science, integrated with Snowflake and Snowpark, enabling quick prototypes and easy multi-page local web apps.

Explore the six Streamlit layout components—sidebar, tabs, columns, expander, container, and empty—and add text elements and interactive widgets such as inputs, selects, and file uploads, with onchange events.

Learn to build a streamlit app that uploads a csv file, lets users choose child and parent columns, and caches data loading for efficient visualization.

Explore Streamlit session state and data caching with st.session_state and callbacks across full page reruns. Build multi-page apps with a pages folder and cache data and resources for efficient rendering.

Publish your Streamlit web app to Streamlit Cloud by deploying from a GitHub repository. Make it public, add a gitignore, and use requirements.txt for deployment.

Deploy your web app to Streamlit Cloud via a GitHub repository, authorize on the fly, and auto refresh on push; ensure slash paths and updated requirements.txt.