SnowProTM Core Certification Companion: Hands-on Preparation and Practice (Certification Study Companion Series)

This study companion helps you prepare for the SnowPro Core Certification exam. The author guides your studies so you will not have to tackle the exam by yourself. To help you track your progress, chapters in this book correspond to the exam domains as described on Snowflake’s website. Upon studying the material in this book, you will have solid knowledge that should give you the best shot possible at taking and passing the exam and earning the certification you deserve. Each chapter provides explanations, instructions, guidance, tips, and other information with the level of detail that you need to prepare for the exam. You will not waste your time with unneeded detail and advanced content which is out of scope of the exam. Focus is kept on reviewing the materials and helping you become familiar with the content of the exam that is recommended by Snowflake. This Book Helps You Review the domains that Snowflake specifically recommends you study in preparation for Exam COF-C02 Identify gaps in your knowledge that you can study and fill in to increase your chances of passing Exam COF-C02 Level up your knowledge even if not taking the exam, so you know the same material as someone who has taken the exam Learn how to set up a Snowflake account and configure access according to recommended security best practices Be capable of loading structured and unstructured data into Snowflake as well as unloading data from Snowflake Understand how to apply Snowflake data protection features such as cloning, time travel, and fail safe Review Snowflake’s data sharing capabilities, including data marketplace and data exchange Who This Book Is For Those who are planning to take the SnowPro Core Certification COF-C02 exam, and anyone who wishes to gain core expertise in implementing and migrating to the Snowflake Data Cloud Table of Contents 6 About the Author 16 About the Technical Reviewer 17 Acknowledgments 18 Introduction 19 Chapter 1: Exam Overview 21 Exam Content 21 Exam Subject Areas 22 Exam Format 23 Supplementary Study Materials 23 Signing Up for a Free Trial 24 Sign-up Process 25 Trial Expiration 27 Registering for the Exam 28 Create a Snowflake Certification Portal Account 28 Start the Application 29 Schedule the Exam 30 Rescheduling and Cancellation Policy 31 Exam Results and Retake Policy 32 Recertification 32 Summary 33 Chapter 2: Introduction to the Snowflake Cloud Data Platform 34 Snowflake as a Cloud Service 34 Supported Cloud Platforms 35 Supported Cloud Regions 36 Snowflake Editions 36 Standard Edition 37 Enterprise Edition 37 Business Critical Edition 37 Virtual Private Snowflake (VPS) 38 Snowflake Releases 38 The Snowflake Web Interface 39 User Menu 40 Worksheets 41 Dashboards 45 Data 45 Snowflake Marketplace 45 Activity 45 Admin 46 Partner Connect 46 Help & Support 47 SnowSQL 47 Install SnowSQL 48 Configure SnowSQL 48 Connectors and Drivers 49 Setting Up Your Snowflake Account 49 Create a Virtual Warehouse 49 Configure the Snowflake Sample Database 50 Summary 51 Chapter 3: Snowflake Architecture 52 Architecture Layers 52 Database Storage 53 Query Processing 54 Cloud Services 54 Snowflake Objects 55 Tables 56 Permanent Tables 57 Temporary Tables 58 Transient Tables 59 Views 61 Data Types 62 Numeric Data Types 63 String and Binary Data Types 64 Logical Data Types 64 Date and Time Data Types 65 Semi-Structured Data Types 66 Geospatial Data Types 66 User-Defined Functions 67 Stored Procedures 71 Comparing UDFs and Stored Procedures 74 Streams 75 Creating Streams 76 Consuming Streams 79 Types of Streams 80 Tasks 81 Sequences 85 Summary 86 Chapter 4: Account Access and Security 88 Account Identifiers 89 Legacy Format 89 Account Name Within an Organization 90 Parameter Management 91 Network Security and Policies 93 User Authentication 95 Multi-Factor Authentication 95 Federated Authentication 100 Session Policies 103 Access Control 104 Securable Objects 105 Users 106 Roles 108 System-Defined Roles 109 Custom Roles 110 Granting and Revoking Privileges 111 Role Hierarchy and Privilege Inheritance 114 Setting Primary and Secondary Roles 115 Summary 116 Chapter 5: Storage and Performance Concepts 117 Micro-partitions 118 Data Clustering 121 Clustered Tables 123 Clustering Keys 124 Reclustering 127 Data Storage Monitoring 128 Search Optimization Service 133 Choosing Tables for Search Optimization 135 Choosing Queries for Search Optimization 136 Using Tables with Search Optimization 137 Cost of Search Optimization 138 The Query Profile Tool 140 Data Spilling 146 Data Caching 147 Micro-partition Pruning 148 Query History 148 Explain Execution Plan 150 Summary 151 Chapter 6: Virtual Warehouses 152 Warehouse Size 152 Multi-cluster Warehouses 154 Multi-cluster Warehouse Modes 155 Multi-cluster Warehouse Scaling Policies 156 Examples of Multi-cluster Credit Usage 157 Warehouse Scaling 160 Scaling Up 160 Scaling Out 161 Working with Warehouses 162 Creating a Warehouse 162 Starting or Resuming a Warehouse 168 Suspending a Warehouse 170 Resizing a Warehouse 171 Using a Warehouse 171 Monitoring Warehouse Loads 173 Warehouse Billing 176 Billing for User-Managed Warehouses 176 Billing for Cloud Services 177 Resource Monitors 178 Summary 184 Chapter 7: Data Loading and Unloading 185 Preparing Files for Data Loading 186 File Formats and Encoding 186 File Compression and Encryption 187 File Sizing Best Practices 188 Folder Structures 190 File Formats 190 Sample File and Table for Loading 191 Loading Using the Web Interface 192 Loading from Cloud Storage 197 Creating an External Stage 197 Directory Tables 199 Using the COPY Command 200 Copy Options 201 Lists of Files 203 Preventing Data Duplication 204 Validation Before Using the COPY Command 205 Removing Staged Files 206 Load History 206 Loading from the Local File System 207 Types of Internal Stages 207 Loading Files into an Internal Stage 208 Using the COPY Command 209 Loading Continuously Using Snowpipe 210 Detecting Staged Files 211 File Sizing for Snowpipe 213 Preventing Data Duplication 213 Creating a Storage Integration 214 Creating a Pipe 215 Removing Staged Files 217 Loading Using External Tables 217 Data Unloading 219 Unloading into Single or Multiple Files 219 Using the COPY and GET Commands 220 Summary 221 Chapter 8: Data Transformation 222 Data Sampling 222 Estimating Functions 223 Computing the Number of Distinct Values 224 Estimating Similarity of Two or More Sets 224 Estimating Frequent Values 226 Estimating Percentile Values 229 Post-Processing Query Results 231 Semi-Structured Data 232 Supported Semi-Structured Data Formats 232 Loading Semi-Structured Data 235 Detection of Column Definitions 236 Querying Semi-Structured Data 238 Selecting First-Level Elements 239 Using the FLATTEN Function 242 Using the GET Function 244 Querying Semi-Structured Data from a Stage 245 NULL Values 246 Unstructured Data 248 Working with Pre-Signed URLs 249 Working with Scoped or File URLs 250 Materialized Views 250 Comparison with Regular Views 252 Comparison with Tables and Cached Results 252 Materialized Views in the Query Optimizer 253 Privileges Related to Materialized Views 254 Creating Materialized Views 254 Limitations on Creating Materialized Views 257 Adding Clustering Keys to Materialized Views 257 Summary 258 Chapter 9: Data Protection 259 Time Travel 259 Data Retention Period 260 Querying Historical Data 263 Cloning Historical Objects 267 Dropping and Restoring Objects 268 Failsafe 269 Cost for Time Travel and Failsafe 270 Data Encryption 272 Client-Side Encryption 272 Key Rotation 273 Periodic Rekeying 274 Customer-Managed Keys 275 Tri-Secret Secure 276 Cloning 276 Database Replication 279 Replication Restrictions 280 Billing for Database Replication 281 Working with Database Replication 283 Summary 287 Chapter 10: Data Sharing 288 Secure Data Sharing 288 Snowflake Share Object 289 Reader Accounts 290 Direct Share 290 Creating a Direct Share 291 Consuming a Direct Share 297 Creating a Reader Account 300 Configuring a Reader Account 302 Snowflake Marketplace 303 Types of Data Listings 304 Get or Request Listings 305 Become a Provider 308 Data Exchange 309 Secure Views 309 Creating Secure Views 311 Best Practices for Using Secure Views 313 Secure User Defined Functions 314 Creating Secure UDFs 315 Best Practices for Secure UDFs 316 Summary 317 Chapter 11: SQL Scripting and Snowpark 318 Snowflake Scripting 318 Snowflake Scripting Blocks 319 Declaring Variables 321 Assigning Values to Variables 323 Branching and Loops 325 Cursors 327 Number of Rows Affected 330 Query ID of the Last Query 331 Handling Exceptions 331 More Information 334 Snowpark 334 Creating UDFs 335 Data Frames 336 More Information 336 What’s Next 337 Index 338