Snowflake Masterclassstored procdemosBest practicesLabs
What you’ll learn
Snowflake architecture in detail.
How data processing happens in snowflake.
Secure data sharing in snowflake.
Understand different caching layers in snowflake.
Continues data load using snowpipe.
Clustering tables in snowflake and how it works under the hood.
Load data and unload data from AWS , GOOGLE and AZURE cloud
Loading unstructured data.
Snowflake internal stage and external stages.
Snowflake data sampling and its importance.
Detail overview about, snowflake clone , failsafe and data retention.
Unique features of Snowfalke ( time travel , undrop , table swap)
Scheduling using Tasks
Streams in snowflake
Materialized views
Best practices in snowflake
Snowpro certification compatible.
Snowflake stored procedures.
Data masking
Snowsight and warehosue utilization dashboard.
Snowflake access control.
Requirements
Familiarity with SQL language and basic knowledge on database.
Description
If you would like to understand how to build workflows using Airflow, Talend and Snowflake Please check out my new course,Snowflake cloud database with ETL(Airflow+Python+Talend)Latest update : Added new section on snowflake search optimisation service.( 9th April 2023 ) Added new section on snowflake external tables. Added new section on ROW ACCESS POLICY in snowflake. Added new section on Tagging in Snowflake This course is the result of my over 4 years of experience working with Snowflake. Snowflake is a fantastic tool to handle huge volumes of data. You can load, query and export, and run transactional queries on TB’s and PB’s of data easily.You don’t have to worry about infrastructure setup, security, performance, tuning, Garbage collection, node failures etc etc… if you have already worked with Spark with EMR or with Data bricks you will be amazed by snowflake performance and simplicity.But there is a catch, if you are not fully aware of how things works in snowflake, you will easily loose control and end up writing costly queries and end up using many of snowflake features wrongly.I mentioned costly queries because snowflake is build for performance.Costly queries and wrong design decisions in snowflake will sky rocket your snowflake bill. I have made lot of such mistakes and learned from those mistakes.This course is an effort to walk you through such experiences so that you don’t end up doing same mistakes which i did!!!Few things can only be learned by experience you can’t find them in documentations this course will expose such details to you.Hope you join me in this journey……………….The course is broadly divided into 3 modules,Module#1 Lectures ————>: 38 Real time Demos—–>: 11 Assignments———>: 10 Quiz——————>: 6 This module will concretize snowflake fundamental concepts and architecture. By the end of this module you will be having very strong foundation about snowflake.Module#2 Lectures ————>: 46 Real time Demos—–>: 31 Assignments———>: 14 Quiz——————>: 2 In this module i will walk you through, different techniques and options to load and unload data in snowflake. By the end of this module, you will be having great confidence in using COPY command and different data processing objects in snowflake.Module#3 Lectures ————>: 45 Real time Demos—–>: 35 Quiz——————>: 3 In this module, we closely evaluate important features of snowflake. This module helps you to understand, how each feature works under the hood. Through this knowledge you will be in a better position to decide, when to use the feature and when not to use the snowflake features.High level overview of all modules with sections details is given below,In module#1, > Snowflake environment setup. > Snowflake architecture. > Snowflake caching , micro-partitions, clusters. > Snowflake clustering deep dive. > Snowflake virtual warehouse layer. > Performance tuning and DB characteristics.In module#2, > Loading data in snowflake. > Loading data using internal stage. > Load data from aws s3. > Load data from google gcs. > Load data from Azure. > Copy command options. > Working with copy command. > Assignments. > Loading unstructured dataIn module#3, > Snowpipe in snowflake. > Data sharing in snowflake. > Time travel. > Fail safe. > Clone feature. > Data sampling. > Tasks in snowflake. > Streaming in snowflake. > Continuous data load. > Materialized views.
Overview
Section 1: High level overview
Lecture 1 Course structure.
Lecture 2 Latest updates !!!!
Section 2: /********Module-1 starts******/
Lecture 3 Module 1 introduction.
Section 3: Snowflake environment setup
Lecture 4 Creating snowflake trail account
Lecture 5 Snowflake web console introduction part1
Lecture 6 Snowflake web console introduction part2
Lecture 7 Web console limitation
Lecture 8 Creating database and table intro to sample data.
Lecture 9 Section revise notes
Section 4: Snowflake architecture
Lecture 10 Snowflake architecture introduction.
Lecture 11 Shared disk architecture.
Lecture 12 Shared nothing architecture part 1
Lecture 13 Shared nothing architecture part 2
Lecture 14 Snowflake architecture revisit.
Lecture 15 The impact.
Lecture 16 Further reading Resources
Section 5: Caching in snowflake data warehouse
Lecture 17 Introduction.
Lecture 18 [Architecture demo] Caching in snowflake.
Lecture 19 [Architecture demo] Caching revisit.
Lecture 20 [Architecture demo] Lessons learned.
Lecture 21 Clarifications
Section 6: Clustering in snowflake.
Lecture 22 introduction.
Lecture 23 Query processing in snowflake.
Lecture 24 Micro partitions in snowflake.
Lecture 25 Micro partitions depth.
Lecture 26 [Demo] Applying clustering brief intro.
Section 7: Clustering — Deep dive.
Lecture 27 Section introduction.
Lecture 28 [Demo] Checking clustering information
Lecture 29 [Demo] How clustering works.
Lecture 30 [Demo] Improve performance without applying clustering
Lecture 31 [Demo] Manual re-clustering
Lecture 32 [Demo] How to choose clustering keys.
Lecture 33 Section Recap.
Lecture 34 Snowflake clustering documentation.
Section 8: Introduction to virtual warehouse creation.
Lecture 35 Introduction to virtual warehouse.
Lecture 36 Virtual warehouse creation.
Lecture 37 Scaling policy
Section 9: Performance tuning and DB characterstics
Lecture 38 What is performance tuning in snowflake.
Lecture 39 How query optimization works.
Lecture 40 Concurrency control.
Lecture 41 [Demo] How update transaction works.
Lecture 42 [Demo] No constraints.
Lecture 43 Q/A
Section 10: Snowsight and warehouse dashboard
Lecture 44 Introduction
Lecture 45 Snowsight_overview_part1
Lecture 46 Snowsight_overview_part2
Lecture 47 Warehouse idle time intro
Lecture 48 Warehouse analogy
Lecture 49 Metadata views
Lecture 50 Calculating idle cost
Lecture 51 Adding dynamic filters
Lecture 52 Creating dashboard intro
Lecture 53 Warehouse utilization dashboard.
Lecture 54 Analysis by warehouse
Lecture 55 Analysis for under utilization
Lecture 56 Analysis for over utilization
Lecture 57 Building dashboard
Lecture 58 Leveraging snowsight
Lecture 59 Conclusion
Section 11: Query acceleration service
Lecture 60 Introduction
Lecture 61 Creating warehouse with QAS
Lecture 62 Problem statement
Lecture 63 QAS- Solution
Lecture 64 QAS- Demo
Lecture 65 QAS- scale factor
Lecture 66 QAS- Eligible queries
Lecture 67 Importent points
Section 12: Search optimisation service
Lecture 68 SOS introduction
Lecture 69 Problem statement
Lecture 70 SOS: Scenario-1
Lecture 71 SOS: Scenario-2
Lecture 72 SOS: Scenario-3
Lecture 73 How SOS works
Lecture 74 SOS: Summary
Lecture 75 Query optimisation options
Section 13: /*******Module-2 starts*******/
Lecture 76 Module-2 Introduction.
Lecture 77 [Optional] Virtual machine setup
Lecture 78 [Optional] Mount folder
Lecture 79 [Optional] Connect from vs code
Lecture 80 Install AWS CLI in windows.
Lecture 81 Install AWS CLI in Mac.
Lecture 82 Install Snow CLI
Lecture 83 Things to be taken care.
Section 14: Loading data in snowflake.
Lecture 84 Introduction to stages in snowflake.
Lecture 85 Configuring connection to stage.
Lecture 86 Types of internal stage
Lecture 87 Introduction to stage object , file format and copy command.
Section 15: Load data using internal stage.
Lecture 88 Section intro.
Lecture 89 Upload data using webconsole.
Lecture 90 Scenario
Lecture 91 [Demo] Load data from local system to snowflake-Part1
Lecture 92 [Demo] Load data from local system to snowflake-Part2
Lecture 93 [Demo] Load data from local system to snowflake-Part3
Lecture 94 [Demo] Download data from snowflake to local system.
Lecture 95 [Demo] Load data using named stage object.
Section 16: Load data from AWS s3
Lecture 96 Section introduction.
Lecture 97 Create aws account.
Lecture 98 Scenario.
Lecture 99 Create aws s3 bucket.
Lecture 100 Create aws s3 policy.
Lecture 101 Create aws role.
Lecture 102 Create snowflake integration object.
Lecture 103 Query aws s3 from snowflake.
Lecture 104 Load data from s3 to snowflake.
Lecture 105 Unload data to s3
Lecture 106 Aws cli basics
Lecture 107 Other options to copy.
Lecture 108 Copy zip files
Lecture 109 Upload data to aws s3 using aws web console and cli.
Lecture 110 Section Recap.
Section 17: Snowflake external tables
Lecture 111 Introduction to external tables.
Lecture 112 Why external tables.
Lecture 113 Insert scenario.
Lecture 114 Delete scenario.
Lecture 115 Update scenario.
Lecture 116 Partition in external tables.
Lecture 117 Manual partition in external tables.
Lecture 118 Auto refresh metadata.
Section 18: Load data from Google GCS.
Lecture 119 Create gcs account.
Lecture 120 Create bucket.
Lecture 121 Copy command anatomy.
Lecture 122 Create Gcp storage integration object.
Lecture 123 Query Gcs data.
Lecture 124 Load data into snowflake.
Lecture 125 Unload Gcs data.
Section 19: Loading data from Azure.
Lecture 126 Create azure account
Lecture 127 Connecting snowflake to azure blob storage
Lecture 128 Upload data using AZCOPY
Lecture 129 Query parquet data from azure
Lecture 130 Load parquet data to snowflake
Lecture 131 Unload parquet data to azure
Section 20: Copy options
Lecture 132 Introduction.
Lecture 133 [Demo] Validate before loading data to table.
Lecture 134 [Demo] File level options.
Lecture 135 [Demo] Collecting rejected records from copy command.
Lecture 136 [Demo] Enforce and Truncate column option.
Lecture 137 [Demo] Force option.
Lecture 138 [Demo] Purge option.
Lecture 139 Load history and copy history views
Section 21: [Demo] Working with copy command.
Lecture 140 Preparing data.
Lecture 141 Load sample data.
Lecture 142 Copy command performance.
Section 22: Assignments.
Lecture 143 Assignment document: To load data to two different tables.
Lecture 144 Assignment document : Unload data to internal stage.
Lecture 145 Assignment document : Create integration object.
Lecture 146 Assignment document : Using snowflake as query engine.
Lecture 147 Assignment document : Loading aws open data.
Section 23: Loading unstructured data
Lecture 148 Loading unstructured data introduction.
Lecture 149 Load json data
Lecture 150 Load xml data
Lecture 151 Loading unstructured data: Method-1
Lecture 152 Loading unstructured data : Method-2
Lecture 153 Loading unstructured data : Method-3
Section 24: /*******Module-3 starts*******/
Lecture 154 Module 3 introduction.
Section 25: Snow pipe in snowflake.
Lecture 155 Snow pipe introduction.
Lecture 156 Snowpipe demo
Lecture 157 Snowpipe analysis part-1
Lecture 158 Snowpipe analysis part-2
Lecture 159 Snowpipe analysis part-3
Section 26: Data sharing in snowflake
Lecture 160 Data sharing quick introduction.
Lecture 161 Data share demo
Lecture 162 Creating secure view
Lecture 163 Difference between secure and normal view.
Lecture 164 Creating snowflake reader account.
Lecture 165 Configure reader account.
Lecture 166 Restriction on reader account.
Lecture 167 Data share significance.
Lecture 168 Further reading Resources
Section 27: Time Travel in snowflake
Lecture 169 Time travel demo.
Lecture 170 Time travel in snowflake and how it works.
Lecture 171 Table retention period.
Lecture 172 More on time travel.
Section 28: Fail safe in snowflake
Lecture 173 Fail safe in snowflake.
Lecture 174 Access fail safe from web console.
Lecture 175 Relation between fail safe and retention period.
Lecture 176 Table types in snowflake.
Lecture 177 Permanent table and failsafe.
Lecture 178 Transient table.
Lecture 179 Temporary table.
Lecture 180 Creating transient database in snowflake.
Lecture 181 Fail safe further reading.
Section 29: Clone feature in snowflake
Lecture 182 Clone feature in snowflake and how it works.
Lecture 183 Clone feature demo.
Lecture 184 Table swap in snowflake.
Section 30: Data sampling in snowflake.
Lecture 185 Snowflake sampling introduction.
Lecture 186 Row vs Block sampling.
Lecture 187 Sampling Demo
Lecture 188 Clone vs Sampling.
Lecture 189 Reference- Sampling
Section 31: Scheduling in snowflake-TASKS
Lecture 190 Snowflake tasks introduction.
Lecture 191 Snowflake task demo.
Lecture 192 Task dependency intro.
Lecture 193 Task dependency demo
Section 32: Streaming in snowflake.
Lecture 194 Introduction to streams.
Lecture 195 Streaming demo part 1
Lecture 196 Streaming demo part 2
Lecture 197 Types of stream
Lecture 198 CHANGE clause
Lecture 199 Slowly changing dimension using streams.
Section 33: [Demo] continuous data load in snowflake
Lecture 200 Demo overview
Lecture 201 Code preparation
Lecture 202 Execution
Section 34: Materialized views
Lecture 203 Materialized view intro
Lecture 204 MV under the hood
Lecture 205 MV maintenance cost
Lecture 206 Before using MV
Lecture 207 Limitation of Materialized views.
Section 35: /*******Module-3 ends*******/
Lecture 208 Module-3 ends.
Section 36: /****Module-4****/
Lecture 209 Data governacence and Administration
Section 37: Data masking
Lecture 210 Introduction to data masking.
Lecture 211 Understand data masking
Lecture 212 Data masking scenario 1
Lecture 213 Data masking scenario 2
Lecture 214 Segregation of duties
Lecture 215 Apply data masking (HIPAA version 1)
Lecture 216 HIPAA rules version 2
Lecture 217 Problem with masking
Lecture 218 Using views
Lecture 219 Using views ( continued )
Section 38: Row access policy
Lecture 220 Introduction.
Lecture 221 Row access policy demo
Lecture 222 why row access policy?
Lecture 223 Segregation of duties
Lecture 224 Mapping tables.
Lecture 225 Dml operations
Lecture 226 Conclusion.
Section 39: Tagging in snowflake
Lecture 227 Tagging in snowflake
Lecture 228 Create and apply tag
Lecture 229 Seggregation of duties
Lecture 230 Prepare environment
Lecture 231 Applying tag
Lecture 232 Tag based masking policy part1
Lecture 233 Tag based masking policy part2
Lecture 234 Masking based on tag value.
Lecture 235 Classification
Lecture 236 Classification demo
Section 40: Stored procedures
Lecture 237 Section introduction
Lecture 238 Stored procedure basics
Lecture 239 Returning result from stored procedure
Lecture 240 Using control statements.
Lecture 241 Using bind variables.
Lecture 242 Table fill rate procedure.
Lecture 243 Error scenarios.
Lecture 244 Handling error scenarios.
Lecture 245 Logging error messages.
Lecture 246 Transactions in stored procedures.
Lecture 247 Caller and owner procedures.
Lecture 248 Using session variables in procedures.
Lecture 249 Owner and Caller procedure use cases.
Section 41: Access control in snowflake
Lecture 250 Access-control introduction.
Lecture 251 Objects and role hierarchy.
Lecture 252 Default roles.
Lecture 253 Creating database: Level1
Lecture 254 Creating database : Level2
Lecture 255 Creating database : Level3
Lecture 256 Creating database : Level4
Lecture 257 Creating database : Level5
Lecture 258 Managed schema introduction.
Lecture 259 Managed schema demo.
Lecture 260 Building hierarchy introduction.
Lecture 261 Building hierarchy demo.
Lecture 262 Hierarchy for analyst.
Lecture 263 Future grants.
Lecture 264 SharedDB objects
Lecture 265 Summary.
Section 42: Snowflake best practices
Lecture 266 Best practices introduction.
Lecture 267 Best practices while doing clone.
Lecture 268 Best practice while creating database and tables.
Lecture 269 Best practice while using virtual warehouse.
Lecture 270 Best practice while using retention period and time travel.
Section 43: Bonus section
Lecture 271 Snowflake with spark
Developers planning to learn snowflake database.,Architects who want to understand best practices followed while using snowflake database.
Course Information:
Udemy | English | 21h 42m | 9.63 GB
Created by: Pradeep H C
You Can See More Courses in the IT & Software >> Greetings from CourseDown.com