🔥Rocking Snowflake with AWS🔥
Pre-requisites:
Snowflake & AWS Account
Stable internet connection while having hands-on
Basic SQL & Python Knowledge will be helpful but is not at all mandatory.
Thirst for New Learning
Humble Request to all readers:
You just need to do 3 things to support my channel
1)Like
2)Share
3) Subscribe to my YouTube Channel .
❄️Snowflake Essentials Concepts❄️
Introduction & Architecture
•
Getting Started
•
Snowflake - as a Service on AWS
•
Behind the Name - "Snowflake" ❄️❄️❄️
Fundamental SQL Concepts
•
Documentation Reference
Advanced SQL
•
Working with CTEs (Common Table Expressions) in Snowflake
•
Introduction to Window function
•
Calculate Cumulative Sum in Snowflake
•
Sliding Window concept in Snowflake
•
Introduction to the Partition By Window Function
•
Lag & Lead function in Snowflake
•
EAV Model & UNPIVOT in Snowflake in-depth intuition
•
Snowflake listagg function & within group clause in-depth intuition
•
HASH_AGG -- the "fingerprint" of Snowflake Table
•
Querying Hierarchical Data in-depth intuition | Snowflake
•
Solve any Correlated Subquery by following these steps
•
EXISTS subquery in Snowflake in-depth intuition
•
Simple way to find out all the objects used in a Snowflake view
•
Snowflake Qualify Function in-depth intuition
Transaction, Commit and Rollback in Snowflake
•
Documentation
•
Fundamental Video
•
Control Transactions in Python-Snowflake Connection
Snowflake Stored Procedure
•
Documentation
•
Fundamentals
•
Pass argument to the Snowflake procedure
•
Tactically return multiple values from Stored Procedure in Snowflake
•
Snowflake Stored Procedure Logging
•
Useful trick for Snowflake Stored Procedure in-depth intuition
•
Transaction in Stored Procedure in-depth intuition
•
Executing Multiple SQL Statements in a Stored Procedure
•
Understanding Caller’s Rights and Owner’s Rights in Snowflake Stored Procedures
•
Session Variables & viewing the definition of Stored Procedure with Owner's rights & Caller's right
s
Snowflake User-Defined Functions
Documentation
Snowflake CDC
Documentation
Snowflake Zero Copy Cloning
Documentation
Snowflake Time Travel
•
Documentation
•
In-depth explanation
•
Undrop a Table, Database or Schema
•
Data Retention Period in Snowflake
Fail-safe Property in Snowflake
Documentation
Snowflake Fail Safe in-depth intuition
Different types of Tables Snowflake
Documentation
Different types of Snowflake Table , Schema & Database
Snowflake Materialized Views
Documentation
Caching in Snowflake Data Warehouse
•
Documentation
•
Visualize Caching
Configuring a Snowflake Storage Integration to Access Amazon S3
•
Documentation
•
In-depth Explanation
Snowpipe
•
Documentation
•
Implementation with SQS
•
Implementation with SNS
•
Load JSON data from s3 using Snowpipe
•
Email Alert Mechanism on load faliure
•
Troubleshooting Snowpipe in-depth explanation
Explore Copy command in more detail
•
Know more about COPY INTO command (Table) in Snowflake
•
Saving rejected rows while executing copy command | Snowflake
Snowflake External Table
•
External Table refresh using SQS
•
External Table refresh using SNS
•
Snowflake External Table Metadata
•
Creating Partitioned External tables in Snowflake | Partition Pruning using metadata$filename
Snowflake Tasks
•
Documentation
•
Fundamentals
•
Scheduling a Stored Procedure using Snowflake Task
•
Create a Simple tree of TASKS
•
Snowflake Stored Procedure Parallel Execution using SQL & Snowflake Tasks (Part 1)
•
Snowflake Stored Procedure Parallel Execution using SQL & Snowflake Tasks (Part 2)
•
Error Notifications for Snowflake Tasks using SNS
•
Snowflake Task graphs Visualization in DAG format
Snowflake Data Unloading
•
Documentation
•
Fundamental explanation
•
Unload a Relational Table with multiple columns to JSON
Automatic Schema Detection in Snowflake
•
Documentation
•
In-depth Explanation
Snowflake Security & Access Control
Working with Roles and Users in Snowflake
Fundamental Video
Securing the Snowflake Account with Network Policies
Fundamental Video
Read server-side encrypted file in Snowflake
•
Documentation
•
Hands-on
Setup Multi-Factor Authentication (MFA) for your Snowflake Account
•
Documentation
•
Hands-on
Snowflake Data Masking
•
Documentation
•
In-depth explanation
•
Data Anonymization using Dynamic Data Masking
Snowflake Secure Views & Row-level security
•
Secure View & Row-level Security In-depth explanation
•
Implementing Row Access Policies in Snowflake
Snowflake with AWS Services
With S3
•
Generating S3 pre-signed URLs using Snowflake with Indepth Intuition
•
Transfer Data from S3 bucket to Snowflake via pandas
With EC2
•
Build Snowflake API with Flask and deploy in EC2 | Part 1
•
Build Snowflake API with Flask and deploy in EC2 | Part 2
•
Build Snowflake API with Flask and deploy in EC2 | Part 3
•
Key Pair Authentication in Snowflake for Linux machine from scratch
With AWS Lambda
•
Snowflake Lambda Data Loader – Example with AWS S3 Trigger
•
Snowflake-lambda-layer
•
Use AWS Lambda to call Snowflake Stored Procedure
•
Asynchronous layer over Snowflake
•
Snowflake External Functions
•
Snowflake External functions Part 2– How to do NLP and analyse product reviews stored in Sno
wflake
•
Sending Email Notifications from Snowflake Using External Functions
•
⚙️Automate Snowflake❄️Reporting using Tasks, AWS Lambda , AWS SES 📧
With AWS Glue/EMR
•
PySpark-Snowflake Connectivity
•
Connect EMR & Snowflake securely (Securing password with Secret Manager)
•
Connecting Snowflake with Python Shell Jobs in AWS Glue
Snowflake with Talend
•
Snowflake ETL using Talend
•
Transfer data from S3 bucket to Snowflake using Talend Snowflake components
•
Snowflake ELT using Talend
•
Generic Framework to load the data from s3 External Stage to Snowflake External Table
•
Deploy Talend Open Studio Standalone Job in another system (EC2 Linux machine)
•
Calling a Talend Job from an external Java application
•
Export Talend job as single fat jar & run the custom jar using step execution in AWS EMR Cluster
•
Building a Type 2 Slowly Changing Dimension in Snowflake using TALEND ELT Component 🔥🔥
Snowflake with Kafka
•
Documentation
•
Set up Streaming Data Pipeline to ingest Kafka Data To Snowflake with a Python Produce f
rom Scratch
•
Analyze Streaming Data from Amazon Managed Streaming for Apache Kafka Using Snowflake
•
🔥Kafka + s3 + Snowflake 🔥: Long-term searchable/queryable data retention
•
Handling Message Errors using Dead Letter Queues in Apache Kafka In-depth intuition
Snowpark with Python
•
Getting Started with Snowpark for Python on Snowflake
•
Read CSV Data from External Stage & write in Permanent Snowflake Table using Snowflake Sno
wpark
•
Read Parquet Data from External Stage & write in Permanent Snowflake Table using Snowflake
Snowpark
•
Read Json Data from External Stage , Flatten it & write in Snowflake Table using Snowflake Sno
wpark
•
Snowflake Python Stored Procedure as In Line Code in Snowflake worksheet | in-depth intuition
•
SnowPark❄️+Copy Command =🔥 | Loading & Unloading data using Copy Command in SnowPark
Snowflake with Airflow
•
Connect Apache Airflow to Snowflake Data Warehouse
•
End To End News📰 Data Pipeline with Apache Airflow Snowflake & AWS Services
•
Building a Batch Data Pipeline using Airflow, Spark, EMR & Snowflake
•
🔍 Harnessing the Power of Airflow: S3 File Sensor + Talend Integration Demystified!
Snowflake End-to-End Projects
•
End To End News📰 Data Pipeline with Apache Airflow Snowflake & AWS Services
•
Building Serverless Data Stream pipeline using Kinesis data streams and Firehose for Snowflake
•
Email Deliverability Analytics using SendGrid, Snowflake & AWS Big Data Services in near-real ti
me
•
Building a Batch Data Pipeline using Airflow, Spark, EMR & Snowflake
•
How to do Natural Language Processing & analyse product reviews stored in Snowflake
•
CDC Pipeline from MySQL RDS to Snowflake (from scratch)
•
Generic Job to load the data from s3 to Snowflake using copy command & capture the audit logs
•
Building Real-Time Data Pipelines with Kafka , Faust & Snowflake
•
ETL SFTP to Snowflake : End-to-End Data Pipeline Project using AWS Transfer Family, Lambda,
Glue, Snowflake❄️, and S3 Magic!🔮
•
Building a No-Code Real-Time Cryptocurrency Data Pipeline with Kafka, Snowflake & AWS 💸💲
V.V.I. Topics for Snowflake Interview
•
Schema on Read in Snowflake
•
Union & Union All Difference
•
Solving Tree Node Problem with Correlated Subquery
•
Snowflake SCD Type 2 in-depth explanation
•
Simple way to implement Type 1 SCD in Snowflake
•
I have an existing table and I dropped that table and I created new table with same table name as
old and with different data. Is there any way to get the old table data in Snowflake?
•
Is it mandatory to create integration object or external stage before executing copy command?
•
How can you execute multiple Snowflake Queries/Stored Procedure in parallel?
Snowflake Stored Procedure Parallel Execution using SQL & Snowflake Tasks (Part 1)
Snowflake Stored Procedure Parallel Execution using SQL & Snowflake Tasks (Part 2)
Execute queries in parallel against Snowflake using Python-Snowflake connector in-depth intuiti
on
•
Is Fail-Safe a reliable way to create Dev/Test/QA Environment?
No , Fail-Safe is not a good option for Dev/Test/QA or other non-production environment. It is
only good for important production level data.
•
When using a LATERAL FLATTEN in a semi-structured data query, what does the LATERAL do?
LATERAL breaks an array into multiple elements.
•
Can we do Time Travel for External Tables?
No , as External Tables are not managed by Snowflake.
•
John created a table Employee in worksheet1(Session 1) but when he ran SELECT * from
Employee in worksheet2(Session 2) after setting up the correct Role , Database & Schema , he
received error 'Table doesn't exist'. What could be the reason?
Employee Table created by John is a Temporary Table which is tied to a session only.
•
How to test Snowflake queries which are used for processing Data in ELT Pipeline?
•
What table type should we use for Staging/Landing Table?
•
How to handle error notification at run for example Snowpipe is created but 100 records are going
in error daily how to get notification for error records?
There are 2 steps in monitoring, you completed the set up , went to chill and after 2 days
production system Snowpipe suddenly broke down😅
then step 1: alert should be sent to all developer then only they will come to check health , else
no-one will even know issue happened in Snowpipe, so for Snowpipe alarm functionality check
this video , in-depth explanation
Snowflake Snowpipe - Email Alert Mechanism
Now next step , once alert triggered, developer will come to check what happened
Then to troubleshoot that check this video
Troubleshooting Snowpipe in-depth explanation
•
In which case did you use Transient Table?
