Snowflake Data Engineer

Introduction

• Types of Databases - OLTP, OLAP, NoSQL.
• Need for a Cloud Data warehouse.
• MPP, On Premise MPP, Cloud MPP.
• Snowflake High-level architecture.
• Snowflake Layers.
• Snowflake Editions.
• Snowflake work environment setup.
• Snowflake Ecosystem.
• Snowflake Client Connectivity.
• Working with ODBC | JDBC | Python  & Snowpark Connectors | Spark Connector.
• Configuring SnowSQL CLI
• SnowSQL CLI hands-on.
• Snowflake Web GUI & Snowsight walk-through.
• Working with the Configuration file.
• Working with multiple Snowflake accounts.
• Preview & Active features.

 Architecture: Virtual Warehouses

• Introduction to Virtual Warehouses.
• Components and functions of a virtual warehouse.
• Types of Virtual Warehouses.
• Auto/manual suspend and resume.
• Multi-cluster Warehouses
• Auto resizing.
• Auto scaling policies.
• Warehouse caching.
• Understanding Warehouse load and managing.
• Optimal usage of Warehouses and Guidelines.
• Resource monitors.
• Warehouse Billing procedure.

 Architecture: Micro-partitions

• Introduction to Columnar storage.
• Micro-partitions.
• Micro-partition actions during DDL and DMLs.
• Query Pruning.
• Data Clustering.
• Clustering Depth, Micro-partitions overlapping, Clustering ratio
• Choosing Clustering keys
• Auto and manual clustering
• Billing criteria.

 Architecture: Cloud services layer

• Functions of Cloud service layer.
• Result cache.
• Metadata operations only SQLs.
• Billing criteria.

Snowflake SQL Optimization

• Discussion on query issues.
• Query Profiler.
• Understanding the query plan.
• Steps to improve query performance.

Snowflake Data Dictionary

• Information schema.
• Snowflake database.
• Account usage schema.
• Other schemas in the SNOWFLAKE database.

Table functions

• Table functions and uses.
• Creating a table function.
• Built-in table functions.
• Querying dictionary data using table functions.

Snowflake database objects

• Databases 
• Schemas
• Managed access Schema
• Types of Snowflake tables
• Temporary tables
• Transient tables
• External tables
• Directory tables
• Types of Snowflake views
• Regular views
• Secure views
• Materialized views 
• Sequences.
• Auto increment/Identity columns.
• Snowflake Masking Policy.
• Snowflake Row Access Policy.
• Materialized views vs Cached Results.
• Zero copy cloning.
• Managing storage cost.

Snowflake Data recovery features

• Time travel.
• Fail-safe.
• Database replication.
• Billing procedure.

AWS CLI

• Installing and configuring AWS CLI.
• Cloud Storage Services.
• Learning AWS CLI Commands.

Snowflake-specific database objects

• Stages
• Internal stage

              - User stage

              - Table stage

              - Named Internal stage

• External stage
• Querying stage files.
• File format and available options.
• Data share.
• Reader accounts.
• Warehouses and Snowflake Reader account.
• Streams & Tasks.
• TAGs.
• Search Optimization Service (SOS).

Data Loading

• COPY INTO.
• INSERT.
• COPY options.
• Validation mode.
• Loading semi-structured data.
• Data loading considerations.
• Optimizing data loads.
• Data loading practical framework.

              - Split, compress, and move.  

• Identify and fix the data loading issues.
• Related dictionary tables and table functions.

Data Unloading

• Basics of data unloading.
• COPY options.
• Exporting JSON data.
• Understanding threads.
• Data unloading limitations.

Snowpipe

• Snowpipe basics
• Snowpipe consideration and guidelines
• Creating a Snowpipe
• AWS Storage Integration.
• AWS ARN
• AWS Notification Services and billing.
• Dealing with Issues during continuous data loading.
• Snowpipe billing criteria.
• Related dictionary objects and table functions.

Snowflake SQL

• Data and Time functions.
• Collation.
• Working with Time-Zone.
• MERGE statement.
• Pivoting.
• Window functions.
• Multi-table inserts.

Processing Semi-structured Data

• Supported file formats.
• Flattening functions.
• Loading JSON data.
• JSON data processing hands-on.
• Sequences to load JSON data into normalized tables.

Working with Functions

• Snowflake functions and uses.
• Creating a function.
• Working with Built-in functions.

Working with Parameters

• List the parameters.
• Alter the parameter value.
• Account level parameters.
• Session level parameters.
• Object level parameters.

Working with Variables

• Define variables and calling.
• list variables.

Snowflake User Management & Access Control

• Roles
• Hands-on with System roles and responsibilities

              - ACCOUNTADMIN

              - SYSADMIN

              - USERADMIN

              - SECURITYADMIN

              - PUBLIC

• Custom roles.
• Secondary role.
• Creating users & options.
• Granting rights on future objects.
• Transfer of rights on cloned objects.
• Working with privileges 

 Stored procedures

• Overview.
• Difference between procedures and function.
• Caller rights and Owner rights procedures.
• Writing procedures in JavaScript.
• Writing procedures in Python.
• Writing procedures in SQL.
• Stored procedures exercises.

Overview on Snowpark

Overview of Snowflake Scripting

Example Data pipeline framework

• Discussion on ELT Datapipeline
• Config tables.
• Log tables.
• Transformation Scripts
• Role of Python & Unix.