Snowflake Master Class for Data Engineers-AWS-Zero to Expert

Sign up for a Snowflake account and start a 30-day free trial with $400 of usage on AWS, enterprise edition, in US West Oregon, then activate via email as admin.

Explore navigating worksheets in Snowsight, the modern Snowflake web UI, and learn to create, organize, and run SQL and Python worksheets, manage folders, and share worksheets with teammates.

Learn how Snowflake's new Workspaces UI replaces Snowsight and worksheets with a gradual rollout. Existing and new accounts will transition, while deprecation timelines remain to be announced.

Understand why Snowflake introduces workspaces to replace the classic console with Snowsight. Access a unified IDE-like environment that supports Python worksheets, dbt projects, and native Git integration to boost productivity.

Navigate the Snowflake Workspaces UI to organize projects, create and rename workspaces, manage files and folders, and securely reference and run SQL scripts from the workspace.

Manage multiple Snowflake workspaces, move or copy files between ETL_DEV and ETL_QA, and use migrate.sql to create a database, schema, table, and rows.

Explore how to access and manage worksheets in Snowflake Workspaces, open worksheets in the Workspaces SQL editor, and use Shared with you and Scratchpad to collaborate and store temporary worksheets.

Navigate the workspace sql editor in learn workspaces, rename explore.sql to iexplore.sql, and run semicolon-separated queries. Set role and warehouse, query tpch_sf1 customer and supplier data.

Explore how to use the Snowflake results window to search all columns, perform partial and unique-value searches, select columns, review query details, and download results as csv.

Collapse query results to view longer SQL statements, collapse the workspace sidebar for more editor space, and use query history to retrieve prior query IDs without rerunning.

Learn to use Snowflake workspaces to view multiple query results in a split, tabbed window. Pin and unpin tabs to lock results and keep earlier data while you run queries.

Explore how to compare data before and after updates in Snowflake using Workspaces split results pane for side-by-side viewing, with pinning and active window behavior.

Practice splitting the sql editor to compare two files side by side, rename duplicates, run independent queries, and merge or resize splits to review code versions efficiently.

Learn how Column Stats in Workspaces provide automatic, contextual statistics for the results, including distributions, sums, and averages of displayed data, which apply only to the shown data.

Navigate Snowflake's database explorer in workspaces to locate databases, tables, and schemas with searches and prefixes. Preview structures and sample data, then copy names or insert into a sql editor.

Explore Snowflake AI Copilot inline in Workspaces, an AI SQL assistant that converts natural language to SQL, generates queries and DDL, and formats code.

Explore Snowflake editions—from standard to Virtual Private Snowflake—and see how time travel, encryption, multi-cluster warehouses, materialized views, private connectivity, HIPAA, and PCI compliance differ.

Understand the four Snowflake cost types: compute, storage, cloud services, and data transfer, and how credits, editions, and storage plans influence your cloud data costs.

Execute a lab that creates the HRMS database and HR schema, drops existing tables with if exists, and recreates seven tables, then verifies them with show tables.

Populate the HR data model tables by executing inserts after setting the HRMS database context and schema, truncating each table to avoid duplicates, and verifying counts with a final union.

Explain columnar storage and why Snowflake uses it, plus an overview of Snowflake architecture's three layers and the virtual warehouse. Includes a lab exercise on creating a virtual warehouse.

Explore how columnar storage, used in data warehouses and big data analytics, delivers faster queries, better compression, and efficient parallel loading by storing data column-wise.

Explore Snowflake architecture across database storage, query processing, and cloud services, including micro-partitions, virtual warehouses, AWS S3 storage, and authentication, access control, and security.

Explore how a Snowflake virtual warehouse, compute layer for SQL queries, supports DML and DDL operations and scales from extra small to 6 extra large with parallel processing across cores.

Create and manage a virtual warehouse in Snowflake via web ui or Snowsight sql. Configure auto suspend and auto resume, adjust size, and monitor query history for performance.

Explore Snowflake's three-layer architecture—database storage, query processing, and cloud services—and how data is stored as micro-partitions on AWS S3.

Master storage integration, file formats, and stages in Snowflake and apply labs to create a storage integration, a csv file format, and a stage for loading data.

Understand storage integration, a Snowflake object that enables 2-way connectivity with AWS S3 to read, load, and unload data between Snowflake and S3 buckets.

Create a storage integration to connect Snowflake with AWS S3, control access via allowed and blocked locations, use an AWS role ARN, and validate connectivity for loading data from learn2cloud-snowflake.

Learn how a Snowflake file format stores metadata about data files, dictates parsing for the COPY command, and supports CSV, JSON, AVRO, ORC, PARQUET, and XML.

Learn how CSV file formats in Snowflake handle field delimiter, record delimiter, headers, encoding, and error controls, enabling schema evolution and resilient data loads.

Create a CSV file format in Snowflake tailored to the AWS file, with delimiter comma, no header, NULL_IF handling for SQL nulls, double-quoted fields, and trim space, stored in hrms.etl.

Leverage a Snowflake stage as a temporary storage for data files loaded with COPY; external stages link to cloud storage, internal stages reside in Snowflake, and both can unload data.

Create or replace stage in Snowflake with storage integration and a file format, then use database and use schema to avoid hardcoding for reliable data loading.

Explore how storage integration connects AWS and Snowflake, configure roles with a trust policy, and use an optional external ID. Learn about AWS storage access, stage concepts, and file formats.

Learn about data latency and the three Snowflake loading types, master the COPY statement and its options, and practice loading data from S3 with labs and Snowsight.

Define data latency as the time from source change to its availability in target tables or data warehouse. Learn how generation, extraction, and load timing, plus pauses, drive architectural solution.

Explore Snowflake's loading options: bulk loading, near real time loading with Snowpipe, and real time loading via Kafka. Compare latency, cost, file formats, and practical use cases.

Learn how the Snowflake copy statement loads data from stages on AWS or from internal stages into tables and unloads data to stages, with position-based column mapping and duplicate protection.

Explore Snowflake copy options, including on error set to continue, to load all valid rows while rejecting bad ones, and review other copy options through a lab exercise.

Load data from AWS into Snowflake using copy into a target table from a CSV stage; learn force option to reload data and why default behavior prevents duplicates.

Load a subset of file columns into a Snowflake table with COPY and a SELECT, align fields by position, optionally include file names, and support auto-increment surrogate keys in warehousing.

Learn how to use the copy option files to load only the relevant file from a stage with two files, avoiding duplicates and preserving production access constraints.

Use the copy option pattern to load only employee files from the stage, ignoring department files and avoiding unnecessary credits.

Understand how the COPY option PURGE=TRUE deletes loaded files on a best-effort basis, with deletion tied to storage integration privileges and often preferred to archive via AWS.

Explore how the COPY option TRUNCATECOLUMNS=TRUE prevents load failures by truncating data that exceeds column length. The demo contrasts a false setting with a successful, truncated load into EMPLOYEES_COPY_TRUNCATECOLUMNS.

Learn how Snowflake's copy on_error options, from abort_statement to continue and skip_file, control load behavior and rollback.

Explain on_error options in snowflake copy; skip_file loads only error-free files, skipping bad ones, unlike abort_statement that aborts the entire load.

Explore the copy option skip_file_num in Snowflake, setting an error threshold (NUM) to skip files with too many errors and enable partial loads when under the limit.

Learn how skip_file_% dynamically adjusts tolerated errors based on file row counts in Snowflake's copy command, improving on skip_file_num by using a percentage value in quotes to set adaptive thresholds.

Explore how copy with on_error=continue enables loading all good rows from Redshift-to-Snowflake migrations, with no error limit, while rejected rows are left for later analysis and reprocessing.

Use the copy command with return_failed_only to isolate failed files during multi-file loads from S3, review partially loaded files and error details, and focus on rows parsed and loaded.

Learn how the copy option size_limit in Snowflake loads files in chunks, with at least one file loaded before checking the limit, to manage large migrations.

Validate data before loading with Snowflake's copy validation mode, choosing return_all_errors, return_errors, or return_n_rows to surface issues like salary type mismatches and adjust the target table accordingly.

Override the stage file format in copy to handle mismatched file properties, using field_delimiter and skip_header, or by supplying a new file format; both approaches validate with a successful load.

Demonstrates loading data from S3 into Snowflake without a stage by using AWS keys in the COPY statement, with FILE_FORMAT and a target table EMPLOYEES_NOSTAGE.

Use the copy statement to transform data during load with a select, including case, cast of salary, and try_cast, while controlling errors and applying multiple copy options.

Reprocess failed rows from a copy load by using validate to list rejections, clean data (remove dollars from salary and fix commission_pct), and reload the corrected rows with copy.

Teach non-technical users to load data into Snowflake via Snowsight Web UI Wizard, supporting CSV, JSON, ORC, Avro, Parquet with a 250 MB limit, creating a table and viewing SQL.

Explore data latency and three loading modes—bulk, near real time, real time—and copy options for Avro and JSON formats, plus Snowsight's add data wizard for loading from S3 and stages.

Learn to load semi-structured data into Snowflake, distinguish it from structured data, and use json with Snowflake's three semi-structured data types, including flattening and using union all in labs.

Discover how Snowflake handles semi-structured data with JSON, Parquet, Avro, ORC, and XML, enabling flexible loading into Snowflake from data lakes.

Explore JSON as a human-readable data exchange and storage format built on key-value pairs, with nested structures and wide use in IoT data transmission.

Explain the three semi-structured data types in Snowflake, object, array, and variant, and demonstrate how to create, query, and access nested data stored in these types.

Load json object data into Snowflake using a json file format and stage in AWS, create an object-column table, copy data, then access and unnest nested fields with dot notation.

Load the array from employee_array.json into a Snowflake table, then access elements by index, cast to string with aliases, and use ARRAY_SIZE to determine length.

Load JSON into a Snowflake variant data type column, extract employee_id, employee_name, and position, and prepare for later lateral flatten of arrays.

Demonstrate lateral flatten to convert JSON arrays into rows, enabling extraction of phone numbers and skills without manual indexing and illustrating correlation with outer fields.

Learn to parse multi json array files in Snowflake using union all with array indexes and lateral flatten to convert json into tabular data.

Learn how to load semi-structured data into Snowflake using JSON, Parquet, Avro, ORC and XML, and use object, array and variant types, with flatten to convert array elements into rows.

Explore what Snowpipe is and its best use cases for loading data into Snowflake. Complete labs to create Snowpipe, load data, troubleshoot errors, and track costs, plus learn Snowpipe limitations.

Snowpipe enables continuous data ingestion from S3 into Snowflake, automatically executing copy as soon as files arrive for near real-time loading and immediate reporting.

Create a snowpipe to ingest csv files from S3 into a snowflake table, using a csv file format and stage, with auto_ingest true and S3 event notifications triggering loads.

Configure AWS S3 event notifications to trigger Snowpipe for csv files with a prefix and optional suffix, and use alter pipe refresh and copy history to load and verify data.

Learn how Snowpipe restricts copy options, how ON_ERROR choices such as SKIP_FILE or CONTINUE affect loads, and why FILES, FORCE, PURGE, and SIZE_LIMIT are not permitted.

Troubleshoot Snowpipe errors by checking pipe status with SYSTEM$PIPE_STATUS, resuming paused pipes, inspecting COPY_HISTORY and VALIDATE_PIPE_LOAD for detailed errors, and re-processing fixed files via COPY or re-upload.

Data engineers troubleshoot snowpipe failures by creating an error table and a dedicated pipe. They surface all issues with information_schema.validate_pipe_load, fix the CSV data, and re-upload for successful load.

Monitor snowpipe costs by analyzing credits used and files inserted in information_schema.pipe_usage_history (14 days retention). Use date_range_start and date_range_end to review recent activity, lag up to 3 hours.

Learn to track snowpipe costs with information_schema.pipe_usage_history, reviewing credits_used, bytes_inserted, and files_inserted. Note information_schema stores 14 days; for older data or pipe-specific details, use account_usage.pipe_usage_history.

Explore the limitations of Snowpipe, including a 250 MB file cap, near real-time ingestion, limited copy options, higher costs with many small files, and no automatic retries.

Leverage Snowpipe, a serverless, continuous ingestion service that auto loads files into Snowflake on arrival. Supports csv, json, parquet, ORC, xml and provides latency and error checks.

Explore Snowflake tasks, their benefits and limitations, and learn to create, alter, schedule, and execute them; build task trees, manage dependencies and conditions, monitor execution, and use MERGE and COPY.

Snowflake tasks offer built-in job scheduling for dml, ddl, and calls, using cron-based timing to automate workflows and enable dag-like dependencies with streams for near real-time updates.

understand why tasks are inactive by default, how to resume or suspend with alter task resume and alter task suspend, serverless and warehouse-backed tasks use initial warehouse size and dependencies.

Monitor Snowflake tasks in real time with information_schema.task_history and manage dependencies and schedules, plus manual execution using execute task to ensure reliable, non overlapping runs and automatic retries.

Create and manage a Snowflake task named TASK_CREATE_TABLE in the COMPUTE_WH warehouse with a 10-minute schedule to run a SQL statement that creates SCHEDULE.EMPLOYEES and verify task details.

Run a task without a schedule manually by using the execute task command to create EMPLOYEES_NOSCHEDULE and verify the table.

Learn how to create and run a Snowflake task without a warehouse, using serverless compute by default, and adjust initial compute size with user_task_managed_initial_warehouse_size for compute-intensive tasks.

Discover how to execute multiple sql statements in a single Snowflake task using anonymous blocks, stored procedures, or multiple tasks, with examples creating and querying employees tables.

Learn how Snowflake tasks use an optional WHEN clause to control execution, with examples of true and false conditions and STREAM_HAS_DATA usage.

Create and manage a Snowflake task tree with a root, child, grandchild, and great-grandchild tasks to control execution order via dependencies, and enable all descendants with TASK_DEPENDENTS_ENABLE.

Discover how to document task dependencies in Snowflake using the task_dependents information schema, capturing parent and child relationships, and use recursive true to reveal the full DAG tree.

Monitor Snowflake tasks with information_schema.task_history to track start/end times, state, error_message, and error_code, and use account_usage for earlier data beyond the 7-day limit.

Snowflake finalizes a dag by running a finalize task after all tasks complete, logging to task_log; the lab demonstrates creating task_log_finalize, suspending the root task, and using unset and alter.

Use merge with a task to update or insert into the employees_dim_merge table, using hr.employees as the source, and track changes with last_updated_ts and last_inserted_ts.

Automate loading with a Snowflake task using copy to populate employees_copy from weekly files. Schedule task_employee_copy for Monday to Friday at 4 am and verify data after execution.

Explore snowflake tasks scheduling, cyclic and cron, with optional serverless compute and warehouse sizing, and build a dag of tasks with when conditions, dependencies, history, and monitoring in Snowsight.

Explore what streams are and how they process data changes, compare stream types, and apply change data capture with task integration, including standard and append-only streams.

Learn how Snowflake streams enable near real-time change data capture and use metadata$action and metadata$isupdate to track insert and delete events for updates.

Discover three stream types in Snowflake: standard (default) tracks all dml changes, including inserts, updates, deletes, and truncates; append-only captures inserts only and is faster; insert-only applies to external tables.

Create a Snowflake standard stream on CDC.EMPLOYEES to capture insert, update, and delete changes, then merge them into EMPLOYEES_DIM_CDC.

Pair streams with task sql using the merge statement to update data at intervals. Poll system$stream_has_data to trigger inserts or updates in the target table when the stream has data.

Explore merging data from more than one table by joining the EMPLOYEES stream with the DEPARTMENTS table and merging into the target EMP_DEPT_DIM_CDC using a merge statement.

Create a stream on a view that combines employees, departments, and the city field from locations to capture changes. Use the stream to merge updates into the target table.

Explore how append-only streams capture inserts while ignoring updates and deletes. See a hands-on example creating, consuming, and testing an append-only stream on the employees table.

Learn to empty a stream in Snowflake by recreating it, not truncating, after selecting data from the employees table and inserting new rows.

Explore how the changes clause enables change tracking to capture inserts, updates, and deletes within a time window, addressing streams' single-use behavior and supporting repeated data consumption.

Learn how streams capture inserts, updates, and deletes, propagate changes to a target via merge, create table, or insert, and use metadata$action and metadata$isupdate to show before and after values.

Learn how data unloading transfers data from Snowflake tables to AWS S3 and why it matters. Explore use cases, features, and labs, including JSON, Parquet formats and partitioned unloading.

Export data from Snowflake to external storage such as Amazon S3, Google Cloud Storage, or Azure Blob, enabling applications, data feeds via SFTP, and ad-hoc sharing with business users.

Snowflake unloading uses the copy statement to export data, joining multiple tables into a single dataset and applying options like overwrite, header, or compression, with formats CSV, Parquet, and JSON.

Unload Snowflake data to S3 as csv using a dedicated stage and file format, control compression, set a file name prefix and overwrite option for reproducible file naming.

Learn how to use Snowflake unload copy options single, max_file_size, header, and overwrite to generate either multiple smaller files or a single large file, based on data size and headers.

Join employees and departments to unload multi-table data with a select and copy, producing files with the emp_dept prefix. Use a view like emp_dept_unload to simplify multi-table unloads.

Learn to unload partitioned data with Snowflake using copy into S3, partitioning by department name or hire year, enabling partition pruning for faster data lake queries.

Unloads relational data from Snowflake to JSON files on S3 using a JSON file format, an AWS ETL stage, and OBJECT_CONSTRUCT in the COPY statement.

Unload Snowflake data to S3 as partitioned Parquet files to improve storage and query performance, and highlight Parquet format creation, stage setup, and copy unload.

Unload data from Snowflake to cloud storage such as AWS, Azure, or GCP. Use options like single file, header, overwrite, max file size, and compression, with CSV, Parquet, and JSON.