Snowflake

Snowflake – Cloud Data Warehousing Course

Snowflake + SQL will be covered in this course along with end-to-end data engineering project implementation and Web UI design using Streamlit

  1. Introduction & History of Snowflake
    • Snowflake History
    • Cloud Data Platform
    • Modern Data Architecture
    • Value of Cloud data Platform
    • One Platform, One Data Copy, Many Workload
  2. Snowflake editions and Self-Registration
    • How to choose Snowflake editions
    • How to choose cloud Providers & Editions
    • How to choose Compute & Storage Pricing
    • Create User
    • Validating snowflake instance
  3. Snowflake Architecture
    • Snowflake architecture
    • 3 Layers Services (Storage, Compute & Service)
    • Snowflake at a glance
    • Snowflake Capabilities & Unique features
    • Classic Web UI Layout
    • Switching Role & Performance
  4. Snowflake Legacy WebUI
    • Classic Web Ui Layout
    • Switching Role & Performance
    • Top Bar (Databases, Shares, Data Marketplace, Warehouse, History)
    • SQL Worksheet & Object Navigations
    • Query Profile & Other Features
  5. SnowSight (Modern Web UI)
    • Access SnowSight via Preview App
    • Legacy Web UI vs SnowSight
    • Worksheets & Data Profiling
    • Dashboard & Sharing
  6. Snowflake Unique Features
    • Time Travel
    • Zero Copy Cloning
    • Dynamic Tables
    • Data Sharing
    • Data Market Place
  7. Snowflake Objects – Databases, Schema & Tables
    • Snowflake Account & Container Hierarchy
    • Account, Database & Schema Level Objects
    • Display & Describe Objects
    • Table Objects – Permanent, Transient, Temporary & External
  • Snowflake Objects (What’s New)
    • Stage Objects
    • File Formats
    • Integration Objects
    • Pipe Objects
    • Stream & Task Objects
    • Monitoring Objects
    • Sequence Objects
  • Snowflake Bulk/Batch Load
    • Overview of data loading
    • Understand stage concept
    • Internal & External stage
    • Data Loading Feature
    • Data Loading Considerations
    • Bulk load using COPY command
  • Continuous Data Load
    • Overview of Continues Data Loading
    • Snowpipe hands on
    • Continuous data loading considerations
  • External Table & Data Load
    • External Table architecture in Snowflake
    • SQL Construct + Options
    • Query External Table
    • External Table Auto Refresh
    • External Table Information Schema
  • Virtual Warehouse
    • VWH Concept
    • VWH Size & Price (Different Edition)
    • VWH Auto Resume/Suspend Properties
    • Multi Cluster Concept
    • VWH Performance
    • VWH Monitoring
  • Snowflake Cluster Concept
    • How micro partition works in snowflake
    • Micro partition & Columnar/Compressed Format
    • What is Clustering Key
    • Auto Partition & re-clustering
    • Clustering Depth & Clustering Overlap
    • Cost of re-clustering with partition key
    • Query Performance Pruning & Query Profile
  1. Time travel
    • Data Protection & Time Travel
    • Time Travel & Snowflake Editions
    • Time Travel & Fail Safe
    • Time Travel for Permanent, Transient & Temporary tables
    • Account Level Configuration for Time Travel
    • Extended SQL for Time Travel & Keywords
    • Cost of time travel
    • Drop & Undrop Table, Schema & Table (Time Travel Feature)
  1. Data Cloning Feature
    • Zero Copy Cloning
    • Objects that can be cloned
    • Cost Of Cloning
    • DB & Schema Cloning
    • Roles & Grants & Cloning
  2. Snowflake Data Sharing – Reader’s account
    • How Sharing Works
    • Types of Sharing in snowflake
    • Cross region & Cross Cloud Sharing
    • Reader Account
    • Snowflake Edition Sharing
    • Storage & Compute Cost
    • Limitation with Sharing
  3. Snowflake Stream Objects
    • How does Stream work?
    • Type of Streams + SQL Construct
    • How to consume Stream data?
    • Stream & External Table
    • Cloning Stream
    • Offset Concept + Data Retention Parameters
    • Limitation of Streams
  4. Snowflake Task Objects
    • How does task object work?
    • Task SQL Constructs & Scheduling
    • Serverless Task Vs Non Serverless Task
    • Stream & Task Together
    • Cloning a Task
    • Task Tree (Vs DAG)
    • Task Limitations
  1. ETL in Snowflake (Streams & Task together)
    • How Task works with Stream
    • Understand system$stream_has_data
    • CDC & Delta Processing
    • Merge Statements
    • End to End Data Flow
  2. Snowflake Role Hierarchy
    • What are RBAC & DAC Concepts
    • Default Roles in snowflake
    • Role hierarchy & Its power
    • Grants Privileges
    • SQL Commands for Roles
    • Roles & Data Privacy
    • Design & Implementation Roles in Snowflake
  3. Stored Procedure, UDF’s & Views
    • Stored procedure overview in Snowflake
    • Stored procedure languages – SQL, JavaScript, Scala and Python
    • Stored procedures vs UDFs
    • SQL Injection
    • Design tips for stored procedure
  4. Snowflake Information Schema
    • Information Schema Views
    • Information Schema table functions
    • Account Usage vs Information Schema
  5. Snowflake Monitoring & Billing
    • Resource monitoring
    • Types of resource monitor
    • Resource monitor frequency
    • Resource monitor Notification
    • RM Trigger & Query Suspension Action
  6. Snowflake Connectors (JDBC/ODBC/Python)

JDBC

  • Snowflake JDBC Driver Type
  • Java Version Support
  • Configuration parameters
  • Authentication Type
  • Java SQL API & JDBC API
  • Snowflake JDBC Transaction

ODBC

  • How to set up ODBC Driver
  • ODBC version & Snowflake Cloud
  • ODBC Driver & MS Excel
  • Programming language Support
  • Data type Compatibility

Python

  • Install Python connector
  • Authentication Approaches
  • Python API Extension
  • Interaction with snowflake
  • Working with Pandas
  • Data loading via Python
  • Reporting tools with Snowflake

Build Web UI using Streamlit

            *****Snowflake data engineering end to end project overview with UI effects****