Course Content

Snowflake Course Content

Overview
 This course provides Snowflake concepts & expertise to help get you started on implementing solutions using Snowflake, getting prepared for Snowflake certifications

Key Takeaways
 Most of the concepts covered above will be supported by hands-on labs and demos.
 Recording will be provided after the class for lifetime
 Reference material to some key snowflake topics

1. Overview
 Snowflake History
 Introduction to Snowflake
 Competitors of Snowflake
 Architecture of Snowflake
 Services Layer
 Compute Layer
 Storage Layer
 Operations Flow in Snowflake
 Different Virtual Warehouse
 Different Types of Editions
 Features available in different Editions
 Pricing Options for different Editions
 Discussion on Credit/Hour
 Storage Cost
 Signup the Snowflake
 Walkthrough the interface
 Download SnowSQL CLI Software
 Installing the SnowSQL CLI
 Setting up the Config file

2. Compute Layer/Virtual Warehouse
 Introduction to Compute Layer
 Introduction to the Virtual Warehouses
 Different Sizes/Credits of the Virtual Warehouses
 Creating the Virtual Warehouses from UI
 Discussing the options in creating the virtual Warehouse
 Naming Standards
 Modes of the Virtual Warehouse
 Maximize Mode
 Auto Scale Mode
 Standard Mode
 Scaling Modes
 Standard
 Economy
 Auto Resume Options
 Auto Suspend
 Creating the Virtual Warehouse using SQL Commands
 Different Options available for creating the virtual Warehouses.
 Discussing the Warehouse Metering History view
 Discussing the Warehouse Loading History view

3. Staging in Snowflake
 Introduction to Staging
 Types of Staging
 Internal Stage
 External Stage
 Internal Stage
 User Stage
 Table Stage
 Named Stage
 Loading the data into User Stage
 Loading the data into User Stage by creating a folder
 Loading multiple files into User Stage by creating a folder using regular expressions
 Listing the files in User Stage.
 Loading the data into Table Stage
 Listing the files in Table Stage
 Creating a Database for controlling the stage objects (Optimal for admin activities)
 Creating the Schema for different purposes
 Creating a Snowflake Managed Internal Stage
 Loading the data into the internal stage
 Listing the files into the internal stage

4. External Stages, AWS External Stage
 Introduction to the external stage
 Walkthrough the AWS Console.
 Walkthrough IAM and S3 Service in AWS
 Create a bucket and Folder in AWS to connect to SF
 Create a user in AWS and get the credentials to connect to Snowflake.
 Assign the necessary policies to the AWS user
 Create an External Stage Object from Web UI of SF
 Create External Staging Object with AWS Role & SF Integration Object
 AWS Role
 S3 Policy Assignment to the Role
 Creating the Integration Object
 Create the staging Object
 Upload the files and list all the files in SF
5. Azure External Stage
 Introduction to the storage account
 Creating the storage account in Azure
 Creating the container in the storage account
 Creating the Storage Integration Object in Snowflake
 Creating the Trusted relationship between Azure and SF
 Creating the External Storage Object in SF
 Giving the permissions to access container from SF
 List the files in the External Storage Object
 Copy the data from the External stage to the table

6. Google Cloud External Stage
 Introduction to the GCP storage account
 Creating the bucket in GCP
 Creating the folder in the bucket
 Creating the Storage Integration Object in Snowflake
 Creating the External Storage Object in SF
 Giving the permissions to access container from SF
 List the files in the External Storage Object
 Copy the data from the External stage to the table

7. Copy Into Command
 Introduction of loading the data in the snowflake
 Loading the data from the Web UI
 Different options available in Copy Into command
 Loading a single file into a table
 Loading the selected files into a table
 Loading Multiple files using pattern
 Introduction to the file format
 Validation for the errors while loading the data using copy command
 Loading the data into the Table by Skipping the errors
 Capturing the errors into a table
 Unloading the data into the External Stage
 Different Options while unloading the data
 Overwrite
 Naming the file
 Unload a File with no compression
 Place header in the file
 Taking out the file name extension (_0_0_0)

8. Snowpipe in AWS
 Introduction to the Snowpipe
 Create a bucket and Folder in AWS
 Creating a Role for Snowpipe
 S3 Policy Assignment to the Role
 Creating the Integration Object
 Create the staging Object
 Create a Pipe in Snowflake
 Configuring the Event Notification in AWS
 Upload the files and see if data is loaded in Table

9. Snowpipe in Azure
 Introduction to the Snowpipe in Azure
 Creating the Storage Account in Azure
 Creating the Container
 Creating a Queue
 Creating the Event
 Create Notification Integration
 Creating the Stage
 Creating a Pipe in Snowflake
 Upload the files and see if data is loaded in Table

10. Tables in Snowflake
 Introduction of Tables
 Different type of the tables and differences
 Design Considerations based on the environment
 Introduction to Data Retention Policy
 Lab: Creating of different Tables and identifying the differences.
11. Time Travel in Snowflake
 Introduction of Time Travel
 Data Life Cycle and Time Travel operations
 Time Travel SQL Extensions
 Parameters for Time Travel
 Offset
 Timestamp
 Statement
 Un Drop
 Querying Historical Data
12. Cloning
 Introduction of Cloning
 Cloning of Tables
 Cloning Database and Schemas
 Cloning Considerations
 Discussing Fail Safe
 Querying the Space for Table, Time Travel and Fail Safe
13. Streams
 Introduction to the streams
 Create a Stream
 Identify Key Aspects of Stream
 METADATA$ACTION
 METADATA$UPDATE
 METADATA$ROW_ID
 Load the Data into the Targe Table using Streams
 Insert (New Records added in the stage Table)
 Update (Records updated in the stage Table)
 Delete (Records deleted in the stage Table)
14. Tasks
 Introduction to the Tasks
 Introduction to the Schedules in Tasks
 Create a Task (Stand Alone Task)
 Create a dependent Task
 Parent Task
 Child Task
 Order of resuming the task
 Order of suspending the task
 Attach the stream to the task for scheduling.

15. Caching
 Introduction to caching
 Types of Cache
 Lab for Caching
 Clustering in Snowflake
 Lab for Clustering

16. Data Sharing
 Introduction to Data Sharing
 Introduction to Reader Account
 Inbound and Outbound Shares
 Create Shares
 Data Sharing with External SF Accounts
 Data Market Place
 Inbound Share with Market Place

17. Roles in Snowflake
 Introduction to Pre-Defined Roles
 Introduction to Custom Roles in SF
 Create Custom Roles in SF
 Creating the Users in SF
 Privileges in SF
 Lab: Creating Roles and assigning the Privileges

18. Connecting to External Tools
 Connecting to Tableau
 Connecting to Power BI
 Connecting to Tableau Prep
 Connecting to ETL (Azure Data Factory)

19. Functions and Stored Procedures
 Introduction to the Functions
 Scalar Function
 Table Functions
 Introduction to Stored Procedures
 Lab: Stored Procedures and Functions

20. Materialized Views, External Tables
 Introduction of Materialized Views
 Materialized View Refreshes
 Limitations of Materialized Views
 Introduction to the External Tables
 Creating and Querying the Data from the External Tables
 Networking Policies
 Querying JSON Data.