Snowflake - Build & Architect Data pipelines using AWS

Define data warehouse as a central repository for analyzing data from varied sources, and explore cloud data warehouses with on-demand scaling, distributed processing, and concurrency, including BigQuery and Snowflake.

Learn two core pillars of a data ecosystem: data warehouse management with schema and security best practices, and cloud-based data pipelines, orchestration, and deployment for analytics and machine learning.

Learn to set up a free Snowflake trial: choose standard edition, select AWS as cloud provider, pick a region like Frankfurt, then activate via the link.

Discover Snowflake’s cloud-native architecture, a hybrid of shared disk and shared nothing, with three layers: cloud services, compute, and storage, and scalable warehouses with varying pricing.

Explore how Snowflake objects are organized in a hierarchy from organization to accounts, schemas, and databases, and how tables, views, stages, procedures, and user-defined functions fit within this structure.

Explore how Snowflake virtual warehouses provide compute power through single and multi-cluster configurations, with min/max clusters and scaling policies (standard and economy) that auto scale and suspend when idle.

Learn how Snowflake uses credits to bill storage, compute, cloud services, and serverless features, and how to estimate costs across on-demand versus pre-purchased storage and multi-cluster warehouses.

Learn to monitor Snowflake consumption by using account usage views, track warehouse credits and run time, review query history, and estimate costs per warehouse.

Create and manage resource monitors in Snowflake to track consumption, set daily budgets, and take actions such as suspend or notify at account or warehouse levels.

Identify Snowflake table types—permanent defaults, temporary session-scoped tables, and dancing tables—and review time travel and failsafe for data recovery.

Create an e-commerce database with line item and orders tables, explore temporary and transient objects, and learn converting permanent tables to transient to save on time travel and failsafe costs.

Explore Snowflake views, including non materialized and materialized views, and learn how secure views enhance data privacy while boosting performance through modular transformations and clustering.

Create views, materialized views, and secured materialized views in Snowflake, perform aggregations, and manage access with roles and security controls.

Create a secured view by defining a role, granting usage on the warehouse, database, and schema, and granting select on secured and materialized views.

Explore the trade-offs of materialized, non-materialized, and secure views in Snowflake, outlining when to use each based on data freshness, usage frequency, and resource demands.

Explore micro partitions and clustering in Snowflake, implement clustering keys, profile queries, and apply built-in optimization features to improve performance on large datasets.

Explore how micro partitions divide data automatically in Snowflake, with clustering keys to optimize queries and pruning to avoid scanning unnecessary partitions.

Explore micro partitions and clustering keys in Snowflake, view clustering information, and compare automatic versus manual clustering through practical table examples.

Discover how micro-partitions enable quality pruning and efficient dml by targeting relevant partitions, while automatic clustering uses clustering keys to optimize analytical queries in Snowflake.

Explore clustering depth and cluster overlap in Snowflake, examining micro partitions, constant partitions, and how average depth and the partition depth histogram indicate how well a table is clustered.

Assess workloads to decide clustering needs in Snowflake, and select clustering keys—up to three columns ordered by low to high cardinality—testing with a clone and batching DML operations.

Complete a lab to check query profiles and history in Snowflake using the information schema and top 20 history to optimize data pipelines.

Explore how query processing and caching work in Snowflake, including the results cache, the local warehouse cache on SSD, and metadata caching.

Learn how Snowflake's search optimization accelerates point lookup queries on non-clustered tables by pruning partitions and reducing scans, while understanding cost and maintenance implications.

Learn how to ingest and unload data into Snowflake by creating stage and integration objects, supporting multiple file formats, automating ingestion, understanding costs, and exporting data to S3.

Explore data ingestion in Snowflake, including batch pipelines from a staging bucket (S3 or Google Cloud Storage) via Glue or Airflow, and real-time options with Kafka, Kinesis, or Kafka-Snowflake connectors.

Create an iam role to let Snowflake read data from s3, then set up a storage integration object in Snowflake and update the trust policy to authorize access.

Learn how to ingest CSV data from S3 into Snowflake by creating a development schema, line item table, file format, stage, and copy into the table.

Ingest json from S3 into Snowflake by creating a json file format and stage, copying into a variant-based table, and transforming semi-structured data into a structured line item table.

Automate data ingestion from S3 using storage integration and a stage, replacing manual copy commands with Snowflake's continuous data ingestion via micro-batches, and build a pipe for automated loading.

Create and configure a Snowpipe pipeline that automatically loads data from an S3 bucket into Snowflake, using a stage, pipe, event notifications, and SQS to trigger copies.

Estimate snowpipe billing using pipe usage history and load history, considering file size, compression, and transformation complexity, and optimize by avoiding excessive parallel loads.

Learn how to extract and unload data from Snowflake to S3 using a storage integration, with options for partitioned data and file formats, including JSON via object_construct.

Explore tasks and their types, create a custom role, apply best practices, and build standalone and dependent desks, executing them with the previously created customer.

Learn how Snowflake tasks, or desks, schedule single or multiple queries in a tree-like dependency, using serverless or user-managed compute, to orchestrate data ingestion, aggregation, and reporting.

Create and test Snowflake tasks by building a standalone desk and a dependent desk, with roles and permissions, schedule triggers at 8 a.m. UTC, and manage task dependencies.

Learn how compute and warehouse runtime drive costs as each desk runs an ethical query; view desk history via the system table from the account admin role.

Explore change data capture and streams, learn stream types, and create and execute streams; understand data retention, staleness, and change tracking in Snowflake tables, plus an end-to-end pipeline project.

Discover Snowflake streams for change data capture, tracking DML operations and row changes. Use streams to incrementally load production tables from staging via Snowpipe, enabling streaming analytics and auditing.

Learn Snowflake data streams: standard streams track all dml changes; penalty streams capture only new rows; insert-only streams suit labels. Includes creating streams and using insert as select and merge.

Create and explore append-only streams on a member table, observe offsets, ingest rows, and note that updates and deletes are not captured by these streams.

Explore how streams operate inside a transaction in snowflake, using atomic units to route streamed data into production tables based on type and ensure all statements commit together.

Learn how Snowflake streams become stale due to data retention limits, time travel, and offsets, and how unconsumed streams affect billing and behavior.

Learn change tracking in Snowflake as a read-only alternative to streams, enable it on a table, and use the changes function to query inserts and updates between timestamps.

Build a continuous data ingestion pipeline from S3 arrivals to a production table using Snowpipe, a JSON staging area, and a CDC stream with scheduled merges of inserts and deletes.

Create a Snowflake integration object using account admin to access the bucket; build a raw json table and a stream on the line item; schedule a one-minute merge task.

Copy the uploaded file into the stage, ingest it into the line_item production table, and verify the stream captures the data for the Snowflake data pipelines lab.

Set up an end-to-end data pipeline by ingesting files from S3 into Snowflake with snow pipe, configuring event notifications, and moving data from raw to production line item tables.

Explore Snowflake user defined functions (UDFs) for reusable, code-based data operations. Learn about scalar and tabular UDFs, and how JavaScript and Python enable them for controlled, efficient data access.

Learn to create and use scalar user-defined functions in Snowflake, define parameters and return types, and run them in queries to compute total quantity shipped.

Learn to write a tabular UDF in SQL, extending a scalar UDF to return a table of supply key and quantity sold by ship date, and how to call it.

Implement javascript user-defined functions in snowflake by building scalar and tabular UDFs, including a range-based ip address generator using a prefix and start/end values.

Understand push down in Snowflake and other big data tools, contrasting load-first filter-later with early filtering. Learn how push down boosts performance and reduces memory use, while recognizing confidentiality risks.

Discover how pushdown can indirectly expose the underlying data via restricted views like line_items_real, and learn to prevent leakage by creating secure UDS.

Learn to create a secured UDF in Snowflake by adding the secure keyword, assigning a dedicated role and test user, and understanding privacy-focused trade-offs and push down optimization limits.

Explore external functions, why you need them, and how to build an item use lambda function that calls an external API via a proxy API gateway on IWC to Snowflake.

Learn how external functions in Snowflake call remote code through an API gateway proxy, enabling a Lambda function as the remote service.

Deploy eight lambda functions that call an open api to fetch currency exchange rates via get requests, and configure Snowflake external functions with proper data formats and status codes.

Create an IAM role to grant Snowflake access to database components through the API gateway, naming it currency conversion external role and linking it to the prior S3–Snowflake integration.

Create a rest API in API Gateway, configure a currency resource with a post method linked to a lambda function in Frankfurt, deploy to production, and test the endpoint.

Secure and deploy an API gateway integrated with a Lambda function, configure method request with authorization and API key, and finalize a resource policy for Snowflake integration.

Create and test your first Snowflake external function that calls an API gateway and a Lambda function, using an API integration, IAM role, and resource invocation for currency conversion.

Learn to write Python scripts that connect to Snowflake, deploy them with AWS Glue, transform data in Snowflake, and orchestrate tasks with Airflow by building a Snowflake-integrated DAG.

Install the Snowflake Python connector and connect to your Snowflake account from a local machine using snowflake.connector.connect with username, password, account, region, and optional warehouse, database, schema, and role.

Introduces AWS Glue as a serverless platform to run Python and Spark jobs. Explains its data catalog, connections, crawlers, and deploying a Python script via the Python shell in Glue.

Learn how AWS Glue deploys and runs your Python scripts in a temporary virtual machine, handles dependencies with wheel files, and executes Snowflake queries.

Parameterize your AWS Glue Python scripts by passing supply key and ship date, accessing values via ogs, and ensuring the job runs with required parameters.

Learn to run banners scripts locally and deploy them on AWS Glue, using pandas and the read_sql function to fetch data and monitor the Glue job.

Learn how Spark 3.1 enables pushdown, letting Snowflake handle most transformations through lazy evaluation and reducing data movement when you enable the push down option.

Deploy a pyspark job in AWS Glue to read from Snowflake and write to a new Snowflake table, using jars in S3 and Glue 3.1 with Spark 3.1.

Set up a managed airflow cluster on AWS for Snowflake data pipelines, including creating an S3 bucket and configuring a CloudFormation VPC with subnets.

Configure a Snowflake connection in Airflow with host, account, region, warehouse, and database to ingest data from S3 into Snowflake and trigger a Glue job.

Deploy a PySpark transformation in AWS Glue to read selected columns, inner join on orders and line items, aggregate by ship mode and total price, and write to Snowflake.

Set up an Airflow DAG to copy data into Snowflake with the Snowflake operator, then trigger a Spark/Glue job, while managing stages, formats, and credentials.