Snowflake - Dynamic Table Masterclass & Data Pipeline

Explore Snowflake stage objects, including external and internal stages, and learn to describe, list, and load data from S3, table stages, and user stages.

Explore Snowflake native objects from stage and file formats (CSV) to sequence keys, CDC types, constructs, and tasks for DAG-based data pipelines; learn CTE and window functions for end-to-end pipelines.

Discover how to create and use Snowflake file format objects to query staged data with dollar notation, using csv, json, parquet, avro, orc, or xml formats and their properties.

Explore Snowflake sequence objects, configure start and increment values, read next values with dot next value, and attach the sequence as a surrogate key default.

Discover how Snowflake stream objects track changes on a base table by recording inserts, deletes, and updates (updates create delete plus insert), with metadata fields and append-only options.

Explore creating and wiring tasks and a task tree in Snowflake to build a dynamic dag for end-to-end data pipelines, including dependencies, forks, and merges.

Learn to use common table expressions to break a complex query into steps, rank balances within market segments, and compute the gap from the top two balances' average.

Explore Snowflake window functions by seeing how the row number and rank functions operate with over, partition, and order by, illustrated on the customer table.

Understand end-to-end data flow in a Snowflake data warehouse, including how to orchestrate pipelines, manage dynamic tables, and compare standard, transient, temporary, and external tables and views.

Explore orchestration options for Snowflake data pipelines in the dynamic table masterclass, from Apache Airflow setups to Fivetran with dbt, and Snowflake native streams and tasks for end-to-end workflows.

Learn end-to-end data flow in Snowflake from raw to clean to consumption layers, using pipe objects, streams, and tasks to automate merges, and see how dynamic tables simplify complex dependencies.

Design an end-to-end data flow in Snowflake, building source, raw, clean, and consumption schemas with file format, stage, streams for customers and orders, and dimension and fact tables and merges.

Explore how to model customer and order data in Snowflake. Use Key Sheet to visualize CSV data and learn partitioned internal stages and context-based copy commands.

Create a legacy database with source, raw, clean, and consumption schemas, a csv file format with skip header, and a stage for loading customer and order data.

Load Germany customer data and Germany order data, list the stage location, and validate with dollar notation; then bulk load 100 more customer rows and bulk order data, revalidate.

Load country specific customer and order data into Snowflake from a stage, using delta and bulk partitions for Germany, validate row counts, and track files with metadata during loading.

Create a customer and an order table with streams. Set a root orchestration task, add copy tasks to load data into customer and order_raw, validate via snow site graph view.

Learn continuous data ingestion into the snowflake raw schema by building customer and order raw tables, append-only streams, and a task tree that copies data from stage to target tables.

Create the clean schema with customer clean and order clean tables linked to three streams, then implement merge tasks from raw data, validate objects, and review the task tree graph.

Creates a continuous data ingestion to the clean layer, with customer clean and order clean tables, standard streams, and a dag populating targets from raw data using row_number deduplication.

Build the consumption schema by creating customer, date time, and priority tables from clean data, then an order fact table at order level, with a suspended merge workflow for review.

Build a Snowflake data pipeline that continuously ingests into a consumption layer, creating customer, date, and priority dimensions and an order fact with task dependencies.

Activate the pipeline objective by sequentially resuming tasks and watching how each task moves data from the stage location to the fact table, demonstrated on snow site.

Resume tasks and monitor customer and order data flow by executing from child to root, reviewing run history, and validating scheduled copy tasks and merges in stream and task plus.

Load datasets across multiple countries, trace data flow from stage to the fact table, validate data with select statements, and review the run history tab and its limitations.

Learn to load additional customer data from Canada and India using bulk and delta methods, monitor end-to-end flow, validate via sql, and troubleshoot streaming task issues.

Track and analyze Snowflake tasks with the task history table, query by run id, measure durations, identify failures, and prepare to build dynamic table structures.

Demonstrate an end-to-end data flow in Snowflake using streams and tasks, compare object counts with dynamic tables, and explain pipe vs task for copy commands.

Explore the behavior and properties of dynamic tables using an employee entity, and review the layered schema while loading data from a snow site UI into raw and clean layers.

Review the 10,000-row employee data set with 11 fields, then load from stage to raw and clean layers using a dynamic table to filter inactive employees.

Create dynamic_table_db and define source, raw, clean, and consumption schemas. Configure a csv file format, load data from five employee csv files into a stage, and enable end-to-end data flow.

Create an employee raw table in the raw schema and load data from the internal stage to the raw layer using a copy command and a scheduled task.

Create a dynamic table in a clean schema to transform and load active employees, with renamed columns, a five-minute target lag, and automatic merges for inserts, updates, and deletes.

Trigger a delete or update on the source table and see the dynamic table refresh automatically, reflecting changes without manual intervention, using time travel and stream to surface altered rows.

Explore why dynamic tables cannot be created on stage data or stage-based views in Snowflake, and learn to stage data in permanent or transient tables before use.

Explore how to refresh, suspend, and alter dynamic tables, describe dynamic table details, and manage lag, incremental mode, downstream tasks, and dependencies in Snowflake.

Create a dynamic table that combines employee data with leave records from two source tables to show each active employee and their approved leave count.

Ingest data in Snowflake using a task and copy command to load employee data and leave data from a stage into raw tables, while managing dependencies and verifying results.

Create a dynamic table named employee leave clean dt with a two-minute target lag under the dt employee leave warehouse, joining employee and leave data to compute a leave count.

Explore creating dependent dynamic tables in Snowflake, configure full and incremental loads, and apply downstream target lag to synchronize end-to-end data pipelines.

Create an end-to-end flow using a dynamic table, four schemas, and stages; load sample data and copy from internal stage to raw layer, orchestrated by a task.

Create two raw tables and two tasks to load data from the stage into customer raw and order raw using copy commands, with a customer-to-order dependency and a one-minute schedule.

Create customer clean dynamic table and order clean dynamic table in the clean layer using a transform warehouse and window functions to load the latest records automatically.

Create dynamic tables for customer, date, and priority dimensions and the order fact table in the consumption layer using a sequence-based surrogate key and downstream target lag.

Demonstrates loading additional orders and customer data into a Snowflake dynamic table pipeline, auto-refreshing raw to clean layers and maintaining continuous data flow with scheduled tasks.

Demonstrates how to decide between incremental load and full load in Snowflake dynamic tables using natural keys, lag, and refresh mode.

Explore the purpose and features of dynamic tables in Snowflake, including summary-level activity and information captured, cost considerations, cloning, time travel, stream object support, shared objects, and table functions.

Explore the Snowflake dynamic table activity dashboard in Snowsight, monitor dynamic table status, lag metrics, and database/schema, and use filters, header sorting, and name-based search in this preview feature.

Explore how to estimate dynamic table costs by analyzing cloud service and warehouse expenses, with emphasis on dedicated virtual warehouses, usage dashboards, and account usage queries.

Explore how to clone dynamic tables in Snowflake, showing that cloning creates another dynamic table but cannot produce a standard or transient table; understand state and lag during cloning.

Explore how Snowflake dynamic tables support time travel, with retention up to 90 days and verification via extended SQL time travel statements.

Learn how to create a stream object on a dynamic table for change data capture, using the dynamic classifier and verifying the stream's base table type.

Learn how to share a dynamic table as a shared object in Snowflake, using account admin rights to publish a direct share within the same cloud provider and region.

Discover how dynamic table functions fetch run history and refresh details, including state, timestamps, partition and row changes, and refresh triggers, with dependency graphs for table relationships.

Learn to build a declarative data pipeline with dynamic tables in Snowflake, configuring a one-minute target lag, downstream, and monitoring lag metrics via a user-defined virtual warehouse.