Snowflake – The Complete Masterclass

Sign up for a 30-day free trial with $400 credit to set up a Snowflake account. Choose business critical edition on AWS US East, activate by email, and begin exploring.

Learn to use the Snowflake interface by creating sql worksheets, navigating databases and schemas, and running queries with auto-complete and fully-qualified table names.

Explore how to manage Snowflake worksheets, set database and schema context, query with fully qualified table names, and organize work using folders, roles, and warehouses.

Explore the three-layer Snowflake architecture—storage on external cloud providers with hybrid columnar storage, a muscle-like query processing layer of virtual warehouses, and a cloud services layer handling security and optimization.

Set up and manage Snowflake warehouses using the interface and sql commands, with role requirements, warehouse sizing, auto suspend and auto resume, multi-cluster scaling, and cost considerations.

Create a Snowflake warehouse using CQL commands, specify size, min and max cluster counts, enable auto suspend and auto resume, and set the scaling policy economy.

Alter warehouses using the alter warehouse command to change properties, suspend or resume operations, and adjust settings like warehouse size and auto_suspend; then drop warehouses via sql or the interface.

Explore auto scaling for a multi-cluster warehouse in Snowflake, comparing standard and economy policies that start additional clusters and shut down based on workload and checks.

Navigate Snowflake databases, schemas, and tables with the interface or SQL, create and manage them, explore privileges and roles, and use worksheets for queries.

Learn to load data into a Snowflake table using the copy into command from an external CSV in S3, including dataset prep in OUR_FIRST_DB and verification of 500 rows loaded.

Consolidate and integrate data from sources for reporting and analysis. A data warehouse uses raw data, a data integration layer, and an access layer to support etl and staging areas.

Learn how cloud computing reduces data center overhead by outsourcing infrastructure to providers, and how Snowflake as a software-as-a-service handles storage, OS, and data management for your data warehouse.

Snowflake editions cover standard, enterprise, business critical, and virtual private editions, with pricing varying by edition, and highlight features like time travel and multi-cluster warehouses.

Explore Snowflake pricing basics, with decoupled compute and storage costs, active warehouses priced by time and size, paid in credits, and region-based variations.

Explore Snowflake pricing across compute, storage, and data transfer, comparing on-demand vs capacity storage, noting compression, regional cloud differences, and free data ingress with charged egress.

Learn to monitor Snowflake costs with cost management tools, credits, and warehouse selection, analyzing compute, storage, and data transfer, and using tags to categorize expenses.

Create resource monitors in Snowflake to control account and warehouse credit usage with a monthly quota, and set threshold actions, notify, suspend after current work, or suspend immediately.

Learn to create and configure resource monitors in Snowflake, set account or warehouse budgets, define monthly quotas, and choose actions like notify, suspend, or suspend immediately.

Explore the Snowflake roles hierarchy, including system roles and public. Learn how custom roles are assigned to sysadmin and privileges granted via grant and inherited by users.

Explore bulk data loading in Snowflake using stages, the copy command, and warehouses, with notes on data transformations. Learn about continuous loading with Snowpipe and serverless warehouses for up-to-date data.

Understand Snowflake stages as the objects that locate data for loading via the copy method, focusing on external stages (AWS S3, Google Cloud Storage, Azure Blob Storage) and their credentials.

Create and configure a stage to load data from an external source, using an S3 bucket and credentials; describe the stage. Prepare to use copy into for loading data.

Load csv data into the orders table in Snowflake using copy, selecting files from a stage, skipping headers, and using wildcards; Snowflake tracks loaded files to avoid reloading.

Learn to transform data during the copy command by selecting a subset of columns. Use case, cast, and substring with aliases for ETL loading.

Explore advanced copy command transformations in Snowflake, including loading subset of columns, mapping to targeted columns, and using default values and auto increment for seamless data loading.

Explore how the ON_ERROR copy option controls error handling in Snowflake loading, showing ABORT_STATEMENT, CONTINUE, SKIP_FILE, and error-limit behaviors across multi-file loads.

Create and reuse file format objects in Snowflake, control type, field delimiter, and skip header, and apply them in copy commands alongside stage objects and describe operations.

Learn to load data from an external stage into Snowflake tables using the copy command, with two loading methods, stage creation, on error options, and a file format object.

Explore how copy options modify the behavior of the copy into command in Snowflake, using simple syntax to set options like on_error and prepare for deeper exploration.

learn how to use snowflake's validation mode with copy into to validate data without loading, return errors or a limited number of rows, and review detailed error messages.

Learn to handle errors in Snowflake copy commands using validation mode to return rejected records, then store them in a rejected table and use last_query_id and split_part to process them.

Set a size limit on the copy command to cap bytes loaded, with the first file always loaded and subsequent files stopped once the limit is exceeded.

Learn how the return failed only option highlights files partially loaded with errors in a copy command, especially when on error continues, enabling focused error handling in Snowflake.

Understand the truncate columns option in Snowflake, which truncates values beyond the target varchar length when true, avoiding errors that occur with the default false.

Explore the force option in Snowflake loading: set force to true to reload previously loaded files even when unchanged, risking duplicates, while the default prevents reloading.

Learn to query Snowflake's load history using information schema and account usage views to view last load time, row counts, and errors for specific tables.

Explore high-level steps for loading unstructured and semi-structured data in Snowflake. Create a stage, load raw data into a variant column, then analyze, flatten, and load into a final table.

Understand the structure of JSON data, including objects, attributes, arrays, and nested values, and learn how to load this flexible data into Snowflake using stages and a variant column.

Establish a connection to the bucket by creating a json stage and json format, copy raw data from HR_data into json_raw table, then flatten and load into the final table.

Learn to parse json in Snowflake by converting raw data into a structured table, selecting city and name, and handling nested data such as salary and title.

Learn how to handle nested data in JSON by using dot notation to access child attributes, organize salary and title into separate columns, and prepare for array handling in Snowflake.

Learn to flatten nested JSON data in Snowflake using the flatten and table functions, extracting language and level values for dynamic, analytics-friendly relational tables.

Learn to insert final data from a dynamic variant column in Snowflake using create table as select or insert into, with the flatten function for JSON formats and on-the-fly querying.

Query parquet data from your stage, break it into a clean table of columns and rows, and convert integer date values to proper date types using the date function.

Query stage and packet data, add file name, row number, and load date metadata, and load into a destination table using a timestamp without time zone.

Learn how Snowflake optimizes performance through automatic micro-partitions, data warehouse design, and scalable warehouses, including scaling up, multi-cluster warehouses, and cluster keys to speed queries and cut compute costs.

Identify user groups and workloads, create dedicated Snowflake virtual warehouses for each group, assign access, balance size, avoid excessive granularity, and review usage patterns to refine over time.

Implement dedicated virtual warehouses in Snowflake by creating a data science and a DBA warehouse, configuring sizes, and granting roles and user access.

Learn how to scale up or down the warehouse by adjusting its size to match query complexity and ETL workload, and when to scale out as user workload changes.

Scale out with multi-cluster warehouses to handle concurrent queries, automate capacity using standard or economy auto-scaling, and apply enterprise best practices for default multi-cluster deployment.

Learn how Snowflake caches query results to speed up execution with automated, 24-hour caching that refreshes when underlying data changes; maximize benefits by routing similar queries to the same warehouse.

Demonstrate caching in practice by running a complex query twice on the same warehouse, showing first-time latency, second-time results reuse, and query profiling that reveals time spent on table scans.

Snowflake clustering speeds queries on large tables by organizing data into micro-partitions with automatic cluster keys, and it teaches when to use cluster by or alter table to adjust keys.

Practice clustering in Snowflake by creating an orders table, applying a cluster key, and measuring how query performance improves through partition pruning and expression-based clustering.

Sign up for an AWS free tier account with Amazon S3 storage, verify identity via a $1 credit card charge and text message, then sign in to AWS management console.

Create an S3 bucket in AWS, snowflakes3bucket123, select US east 1 to match your Snowflake account, then create folders such as csv and json and configure role-based policy for Snowflake.

Upload data to s3 folders by adding csv and json files, create and manage multiple buckets, and prepare a Snowflake loading policy and connection to ingest from buckets.

Create an IAM role to connect Snowflake to your AWS account’s buckets, granting S3 full access with an external ID and updating the trust relationship.

Create a storage integration object to securely connect Snowflake with an AWS S3 bucket. Define AWS_ROLE_ARN and STORAGE_AWS_ROLE_ARN, set trust in IAM, and prepare for loading data via copy commands.

Load data from S3 into Snowflake using storage integration, create a stage and file format, and execute a copy command with proper field qualifiers and null handling.

Configure a json file format and stage in Snowflake, extract json fields from a variant, convert date fields with date functions, and load cleaned data into a destination table.

Learn how to create a free Azure account, claim $200 credit for 30 days, verify your phone, and navigate the portal to configure services.

Learn to set up an Azure storage account, create a Snowflake resource group, choose a unique storage name and region, and configure locally redundant containers for data.

Create two containers, CSV and JSON, in your Azure storage account and upload the two files. Prepare a secure Snowflake to Azure connection for these containers in the next lecture.

Create a storage integration object in Snowflake to securely connect to Azure external stages. Configure the tenant ID and allowed locations, then grant permissions via Snowflake IAM.

Establish a Snowflake stage by creating a file format (skip_header=0) and linking it to an Azure storage integration, then test the connection by listing stage files.

Discover how to load a CSV into Snowflake by querying the file, creating a table from header columns, and copying data from a stage using a storage integration on Azure.

Learn to load json files in Snowflake by creating a json file format, using a storage integration, and loading via the copy command.

Sign up for the Google Cloud Platform free trial, register with a Google account, claim $300 credit for 90 days, and learn how to set up cloud storage buckets.

Create and name cloud storage buckets with globally unique names. Upload CSV and JSON files, then set a region and defaults and prepare an integration to connect GCP and Snowflake.

Create a secure gcp storage integration in snowflake by configuring the gcp_integration object, defining allowed bucket locations, and granting a storage admin service account in GCP.

Create file format object and stage, connect to storage integration, and test access by listing files in the GCP bucket; explore querying, copying, and unloading data in Snowflake and GCP.

Query and load data from a gcp bucket, adjust the file format to view headers, and create and populate the happiness table by copying data.

Unload data from a Snowflake table to a GCP bucket via copy into, using a stage, storage integration, file format, and correct permissions.