Snowflake Masterclass Online Course

Snowflake The Complete Masterclass (2022 Edition)

Introduction

• Introduction
• How to benefit best from the course?
• All course slides & resources

Getting started

• Sign up for free trial
• Login to account
• Getting to know the interface
• Snowflake architecture
• Architecture
• Setting up warehouse
• Setting up warehouse using SOL
• Setting up virtual warehouse
• Scaling policy
• Exploring tables & databases
• Loading data in snowflake
• Load data

Snowflake architecture

• What is a data warehouse?
• Cloud computing
• Snowflake editions
• Snowflake pricing
• Monitor Usage
• Roles in snowflake
• Architecture

Loading data

• Loading methods
• Understanding_stages
• Creating stage
• COPY command
• Create a stage & load data
• Transforming data
• More transformations
• Copy option: ON_ERROR
• File format object
• Summary
• Loading data
• Create file format object & use copy option

Copy options

• Understanding copy options
• VALIDATION MODE
• Using the copy options
• Working with rejected records
• SIZE_LIMIT
• RETURN FAILED ONLY
• TRUNCATECOLUMNS
• FORCE
• Load history
• Copy options
• Using the copy options

Loading unstructured data

• High-level steps
• Our data
• Creating stage & raw file
• Load raw JSON
• Parsing JSON
• Handling nested data
• Parsing & handling array
• Dealing with hierarchy
• Insert final data
• Querying PARQUET data
• Loading PARQUET data

Performance optimization

• Performance in Snowflake
• Create dedicated virtual warehouse
• Implement dedicated virtual warehouse
• Scaling up
• Scaling out
• Caching - Theory
• Maximize Caching
• Clustering - Theory
• Clustering - Practice
• Performance optimization

Loading from AWS

• Sign up for free trial
• Creating S3 bucket
• Upload files in S3
• Creating policy
• Creating integration object
• Loading from S3
• Handle JSON
• Loading from AWS

Loading from Azure

• Sign up for free trial
• Create a storage account
• Create a container
• Create integration object
• Create stage object
• Load CSV file
• Load JSON file

Loading from GCP

• Sign up for free trial
• Create a bucket
• Create integration object
• Create stage
• Query & load data
• Unload data

Snowpipe

• What is Snowpipe?
• High-level steps
• Creating stage
• Creating pipe
• Configure pipe & notifications
• Error handling
• Manage pipes
• Snowpipe

Time Travel

• Using time travel
• Time travel
• Restoring data
• UNDROP tables
• Undrop
• Retention time
• Time travel cost
• Time travel

Fail Safe

• Understanding Fail Safe
• Fail Safe storage
• Fail Safe

Types of tables

• Different table types
• Permanent tables & databases
• Transient tables & databases
• Temporary tables & databases
• Types of tables

Zero-Copy Cloning

• Understanding Zero-Copy Cloning
• Cloning tables
• Cloning schemas & databases
• Cloning with time travel
• Swapping tables
• Zero-Copy Cloning

Data Sharing

• Understanding data sharing
• Using data sharing
• Create share through the interface
• Sharing with non-snowflake users
• Creating a reader account
• Creating a database from share
• Set up users for share
• Sharing database & schema
• Secure vs. normal view
• Sharing a secure view
• Data Sharing

Data Sampling

• Why data sampling?
• Methods of data sampling
• Sampling data: Hands-on
• Data Sampling

Scheduling Tasks

• Understanding tasks
• Creating tasks
• Using CRON
• Understand tree of tasks
• Creating trees of tasks
• Calling a stored procedure
• Task history & error handling
• Tasks with condition
• Tasks

Streams

• Understanding streams
• INSERT operation
• UPDATE operation
• DELETE operation
• Process all data changes
• Combine streams & tasks
• Types of streams
• Changes clause
• Streams

Materialized Views

• Understand materialized views
• Using materialized views
• Refresh materialized views
• Maintenance costs
• When to use materialized views
• Limitations
• Materialized views

Dynamic Data Masking

• Understanding data masking
• Creating a masking policy
• Unset & replace policy
• Alter an existing policy
• Real life examples
• Data Masking V

Access Management

• Key concepts
• Roles overview
• ACCOUNTADMIN
• ACCOUNTADMIN in practice
• SECURITYADMIN
• SECURITYADMIN in practice
• SYSADMIN
• SYSADMIN in practice
• Custom roles
• Custom roles in practice
• USERADMIN
• USERADMIN in practice
• PUBLIC

Visualization - Power BI & Tableau

• Data Visualization
• Download & install Power BI
• Connect Power BI & Snowflake
• Working in Power BI
• Download & install Tableau
• Connect Tableau & Snowflake

Partner Connect - Connecting other tools

• Connecting to other tools
• Connect to ETL tool

Best Practises

• Best practices
• Virtual warehouses
• Table design
• Monitoring
• Retention period

Getting started (retired)

• Sign up for free trial
• Getting to know the interface
• Classical console view
• Setting up virtual warehouses (retired)
• Exploring tables & databases (retired)
• Loading data in snowflake (retired)

Loading data (retired)

• Creating stage
• COPY command (old interface)
• Transforming data (old interface)
• More transformations (old interface)
• Copy options & ON_ERROR (old interface)
• File format object (old interface)
• Summary (old interface)

Copy options (retired)

• Working with rejected records (old interface)
• VALIDATION MODE (old interface)
• RETURN FAILED ONLY (old interface)
• SIZE LIMIT (old interface)
• FORCE (old interface)
• TRUNCATECOLUMNS (old interface)
• Load history (old interface)