Snowflake Masterclass[stored proc+demos+Best practices+Labs] 

Course structure.

Latest updates !!!!

Module 1 introduction.

Creating snowflake trail account

Snowflake web console introduction part1

Snowflake web console introduction part2

Web console limitation

Creating database and table intro to sample data.

Section revise notes

Snowflake environment setup

Assignment -- Section -1

Snowflake architecture introduction.

Shared disk architecture.

Shared nothing architecture part 1

Shared nothing architecture part 2

Snowflake architecture revisit.

The impact.

Snowflake architecture.

Further reading Resources

Introduction.

[Architecture demo] Caching in snowflake.

[Architecture demo] Caching revisit.

[Architecture demo] Lessons learned.

Clarifications

Caching

Caching assignment

introduction.

Query processing in snowflake.

Micro partitions in snowflake.

Micro partitions depth.

[Demo] Applying clustering brief intro.

Clustering

Micro-partition assignment 1.

Micro-partition assignment 2.

Create clustered table.

Section introduction.

[Demo] Checking clustering information

[Demo] How clustering works.

[Demo] Improve performance without applying clustering

[Demo] Manual re-clustering

[Demo] How to choose clustering keys.

Section Recap.

Snowflake clustering documentation.

Clustering-- Deep dive

Clustering deep dive assignment 1.

Clustering deep dive assignment 2.

Introduction to virtual warehouse.

Virtual warehouse creation.

Scaling policy

Virtual warehouse

What is performance tuning in snowflake.

How query optimization works.

Concurrency control.

[Demo] How update transaction works.

[Demo] No constraints.

Q/A

Performance : Executing update statements.

Performance : Adding new column.

Performance : Which query is good.

Introduction

Snowsight_overview_part1

Snowsight_overview_part2

Warehouse idle time intro

Warehouse analogy

Metadata views

Calculating idle cost

Adding dynamic filters

Creating dashboard intro

Warehouse utilization dashboard.

Analysis by warehouse

Analysis for under utilization

Analysis for over utilization

Building dashboard

Leveraging snowsight

Conclusion

Introduction

Creating warehouse with QAS

Problem statement

QAS- Solution

QAS- Demo

QAS- scale factor

QAS- Eligible queries

Importent points

SOS introduction

Problem statement

SOS: Scenario-1

SOS: Scenario-2

SOS: Scenario-3

How SOS works

SOS: Summary

Query optimisation options

Module-2 Introduction.

Install AWS CLI in windows.

Install AWS CLI in Mac.

Install Snow CLI

Create GitHub account

Configure Gitpod

Snowcli from Gitpod

Things to be taken care.

Introduction to stages in snowflake.

Configuring connection to stage.

Types of internal stage

Introduction to stage object , file format and copy command.

Create stage object.

Create file format object.

Section intro.

Upload data using webconsole.

Scenario

[Demo] Load data from local system to snowflake-Part1

[Demo] Load data from local system to snowflake-Part2

[Demo] Load data from local system to snowflake-Part3

[Demo] Download data from snowflake to local system.

[Demo] Load data using named stage object.

Load data using snowCLI.

Prerequisites

Introduction.

Challenge-1

Challenge-2

Challenge-3

Challenge-4

Challenge-5

Challenge-6

challenge-7

Challenge-8

Section introduction.

Create aws account.

Scenario.

Create aws s3 bucket.

Create aws s3 policy.

Create aws role.

Create snowflake integration object.

Query aws s3 from snowflake.

Load data from s3 to snowflake.

Unload data to s3

Aws cli basics

Other options to copy.

Copy zip files

Upload data to aws s3 using aws web console and cli.

Section Recap.

Aws external staging area.

Introduction to external tables.

Why external tables.

Insert scenario.

Delete scenario.

Update scenario.

Partition in external tables.

Manual partition in external tables.

Auto refresh metadata.

Create gcs account.

Create bucket.

Copy command anatomy.

Create Gcp storage integration object.

Query Gcs data.

Load data into snowflake.

Unload Gcs data.

Create azure account

Connecting snowflake to azure blob storage

Upload data using AZCOPY

Query parquet data from azure

Load parquet data to snowflake

Unload parquet data to azure

Introduction.

[Demo] Validate before loading data to table.

[Demo] File level options.

[Demo] Collecting rejected records from copy command.

[Demo] Enforce and Truncate column option.

[Demo] Force option.

[Demo] Purge option.

Load history and copy history views

Copy command options.

Preparing data.

Load sample data.

Copy command performance.

Demo practice.

CH1:Tobacco data.

CH1:scenario-1

CH1:scenario-2

CH1:scenario-3

CH1:scenario-4

CH1:scenario-5

CH1:scenario-6

Tobacco_scenario:solution

CH2: Intro

CH2: Load multiple files - 1

CH2:Load multiple files - 2

CH2:solution

CH3:intro

CH3:unload data scenario

CH3:scenario-1

CH3:scenario-2

CH3:scenario-3

Data unload solution.

CH4: External view

CH4: External view solution

CH5: Climate data.

CH5: scenario-1

CH5: scenario-2

CH5:scenario-3

Load data into snowflake table using internal stage.

Assignment load data to multiple tables

Unload data to internal stage.

Create integration object.

Use snowflake as query engine over aws s3.

Loading aws open data to snowflake.

Loading unstructured data introduction.

Load json data

Load xml data

Loading unstructured data: Method-1

Loading unstructured data : Method-2

Loading unstructured data : Method-3

Load json book data.

Load twitter data.

Unload and load parquet data.

Module 3 introduction.

Snow pipe introduction.

Snowpipe demo

Snowpipe analysis part-1

Snowpipe analysis part-2

Snowpipe analysis part-3

Snow pipe quiz.

Snowpipe assingment.

Data sharing quick introduction.

Data share demo

Creating secure view

Difference between secure and normal view.

Creating snowflake reader account.

Configure reader account.

Restriction on reader account.

Data share significance.

Further reading Resources

Assignment-1: Create shared account.

Assignment-2 : Create views

Assignment-3 : Create shared database

Data sharing quiz.

Time travel demo.

Time travel in snowflake and how it works.

Table retention period.

Time travel quize

More on time travel.

Assignment : Time travel

Time travel and retention period.

Fail safe in snowflake.

Access fail safe from web console.

Relation between fail safe and retention period.

Table types in snowflake.

Permanent table and failsafe.

Transient table.

Temporary table.

Creating transient database in snowflake.

Fail safe quize

Fail safe further reading.

Assignment:- Fail safe.

Clone feature in snowflake and how it works.

Clone feature demo.

Table swap in snowflake.

Cloning quiz

Assignment:-Cloning.

Snowflake sampling introduction.

Row vs Block sampling.

Sampling Demo

Clone vs Sampling.

Reference- Sampling

Assignment:-Data sampling.

Snowflake tasks introduction.

Snowflake task demo.

Task dependency intro.

Task dependency demo

Introduction to streams.

Streaming demo part 1

Streaming demo part 2

Types of stream

CHANGE clause

Slowly changing dimension using streams.

Demo overview

Code preparation

Execution

Materialized view intro

MV under the hood

MV maintenance cost

Before using MV

Materialized view quiz

Limitation of Materialized views.

Assignment :- Materialized views.

Queries used in this section.

Introduction

Introduction on views

Understanding materialized views

Dynamic table introduction

Dynamic table under the hood - part 1

Dynamic table under the hood - part 2

Revise dynamic table

Target lag

Full refresh vs Incremental refresh

Example scenario

Approach: Normal way

Approach : Using dynamic table.

SCD type 2 in dynamic table

Dependency in dynamic table

Downstream property

Module-3 ends.

Data governacence and Administration

Introduction to data masking.

Understand data masking

Data masking scenario 1

Data masking scenario 2

Segregation of duties

Apply data masking (HIPAA version 1)

HIPAA rules version 2

Problem with masking

Using views

Using views ( continued )

Introduction.

Row access policy demo

why row access policy?

Segregation of duties

Mapping tables.

Dml operations

Conclusion.

Tagging in snowflake

Create and apply tag

Seggregation of duties

Prepare environment

Applying tag

Tag based masking policy part1

Tag based masking policy part2

Masking based on tag value.

Classification

Classification demo

Section introduction

Stored procedure basics

Returning result from stored procedure

Using control statements.

Using bind variables.

Table fill rate procedure.

Error scenarios.

Handling error scenarios.

Logging error messages.

Transactions in stored procedures.

Caller and owner procedures.

Using session variables in procedures.

Owner and Caller procedure use cases.

Access-control introduction.

Objects and role hierarchy.

Default roles.

Creating database: Level1

Creating database : Level2

Creating database : Level3

Creating database : Level4

Creating database : Level5

Managed schema introduction.

Managed schema demo.

Building hierarchy introduction.

Building hierarchy demo.

Hierarchy for analyst.

Future grants.

SharedDB objects

Summary.

Best practices introduction.

Best practices while doing clone.

Best practice while creating database and tables.

Best practice while using virtual warehouse.

Best practice while using retention period and time travel.

Snowflake with spark