Snowflake Essentials - A Comprehensive Masterclass

Explore Snowflake's architecture across cloud services, the query processing layer, and database storage, and learn how compute and storage separation, micro partitions, and auto scaling enable scalable, concurrent data workloads.

Create and manage virtual warehouses in Snowflake using the web interface and SQL, configuring auto resume, auto suspend, multi-cluster scaling, and query acceleration, then verify with shows and drops.

Master Snowflake sql, the sql dialect for Snowflake, and Snowflake scripting for procedural logic and stored procedures. Use Snowflake sql, the command line client, to connect and automate data pipelines.

Explore the four sql statement types in snowflake, ddl, dml, select, and access management, and learn to use create, alter, drop, insert, update, delete, merge, copy, grant, revoke, and privileges.

Master DDL statements to create databases and schemas, describe tables, insert data, alter and drop objects, and explore zero copy cloning, create table like, and create table as select.

Explore Snowflake constraint types—not null, unique, primary key, and foreign key—and how inline and out-of-line definitions affect enforcement, naming, and cross-system compatibility.

Explore dml statements in Snowflake, including insert, delete, update, and merge with a table for learning. Learn idempotent practices, pre-checks before delete or update, and limiting selects to optimize costs.

Learn snowflake access control, including roles, users, data control language statements, and the hybrid role based and ownership based model, with system defined and custom roles and least privilege.

Learn how system-defined and custom roles govern access in Snowflake, manage grants and inheritance across account admin, org admin, and sysadmin roles, and enable databases, schemas, warehouses for data scientists.

Learn to create users, assign and grant roles, enable future access, and manage permissions across schemas and tables using Snowflake DCL statements.

practice best access control by using minimum privileged roles, setting sysadmin as default where possible, and building a scalable role hierarchy with granular, intermediary privileges for warehouses.

Learn how to load data from files into Snowflake using stage objects, including external and internal stages, and understand file formats, security, and the difference between bulk and continuous load.

Create a database, schema, and customer table, then load a 50-row csv via the UI using skip first line, comma delimiter, and optional quotes; verify 50 rows inserted.

Learn how to bulk load data into Snowflake with copy into, loading from an S3 external stage, including CSV and Parquet formats, file patterns, and error handling options.

Learn about stage objects in Snowflake, including internal stages (table, user, named) and external stages, and how to load and unload data to cloud storage.

Connect to an AWS external stage using a storage integration object, create the integration and IAM role, then validate access by listing files and loading data into Snowflake.

Connect to AWS using IAM user credentials, generate an access key and secret key, and configure a Snowflake stage to access S3 files.

Explore transforming data during load into Snowflake, including creating a transformation stage, column reordering, trimming, null handling, and parsing phone numbers into country, area, and local parts.

Master Snowflake query syntax by covering the select essentials, common table expressions, time travel options, and key clauses like join, lateral, pivot, values, where, group by, having, and limit.

Explains inner, left, right, full, cross, and natural joins, on conditions, aliases, and Cartesian results. Uses customer and orders to illustrate join types and best practices for clear sql.

Explore aggregate functions on groups of rows: count, sum, min, max, and average. Learn count behavior with nulls and distinct values, and use group by, ordinal referencing, rollup, and cube.

Learn how window functions operate on a defined window of rows with over, partition by, and window frames, enabling per-row max, sliding and cumulative views, year-to-date totals, and alerts.

Learn how to use Snowflake window functions over partitioned and ordered windows, employing cumulative and sliding frames to compute sums, averages, and counts.

Explore window functions in snowflake: define frame by rows or range, use partition by and order by, rank, dense_rank, row_number, lead, lag, and qualify to filter results.

Learn how common table expressions create named subqueries with the with clause, improving readability, reusability, and maintenance, including recursive ctes for sequences and Fibonacci.

Explore set operators to combine query results using intersect, except, minus, union all, and union, and see how duplicates affect results and performance.

Define session variables in Snowflake, reference them with the dollar sign, and use select into in Snowflake scripting to assign query results to variables.

Learn how to pivot to turn column values into wider columns using aggregate functions like sum, count, and average, and how unpivot rotates columns back to rows for BI dashboards.

Explore Snowflake string data types like varchar and related variants, learn how short or default lengths impact storage, and see how to create transient tables and perform insertions with escapes.

Learn about Snowflake numeric data types, including number, decimal, and float, and how precision and scale affect storage, rounding, and accuracy for financial data.

Learn date, time, and timestamp data types in Snowflake, including table creation, formatting, precision, and time zone concepts like no time zone, local time zone, and daylight saving time.

Explore Snowflake's conditional expression functions, including case, if, coalesce, isnull, is not null, and nullif, and learn to handle nulls in joins and queries.

Learn how to convert column data types using cast, try_cast, and convert, with practical examples on the orders table to prepare data for upcoming scalar functions.

Discover string scalar functions such as replace, translate, split, left/right pad, trim, and substring, and learn case-sensitive and case-insensitive matching with like and i like.

Explore Snowflake numeric functions, including zero if null, floor, round, trunk with scale, absolute value, sign, power, square root, and random with uniform for ten rows of data 1–100.

Learn common scalar date time functions in snowflake, including date add, date diff, date trunc, last day, extract parts, day of week, epoch, and timestamp casting.

Explore the theory of data recovery in Snowflake, covering data retention periods by edition, time travel for querying past table states, and fail-safe recovery as a last resort.

Master time travel and data retention in Snowflake by adjusting table, schema, and database retention, and restoring with at or before, query history, and clone with time travel.

Explore secure data sharing in Snowflake, enabling providers to share read-only data with consumer accounts without data copies, via direct share, marketplace, or specified consumers using shares.

Create direct shares in Snowflake to share tables and objects with read-only access. Configure via SQL or SnowSite, grant privileges, and create a consumer database from the share.

Secure views protect privacy by hiding definitions and underlying objects, enable sharing to data shares, and can be tested with simulated data sharing to model consumer views.

Learn snowflake scripting to write procedural code with control flow using while, for, and if statements, declare and bind variables, and create anonymous blocks for tables and stored procedures.

Create Snowflake stored procedures with Snowflake scripting, define procedures with no inputs, return varchar, and use SQL language, then apply loops, conditionals, and date functions to generate results.

Explore result sets and cursors in Snowflake stored procedures, learn counting and filtering with parameters, and master dynamic SQL, execute immediate, and binding variables for runtime table selection.

learn snowflake error handling in scripting blocks, covering built-in exceptions and creating a custom exception; handle statement and expression errors with when, then, and other.

Learn to create Snowflake store procedures in Python, compare SQL-first and Python implementations, and use Snowpark Python with runtime 3.10, including handlers and date logic.