Sybex's Study Guide for Snowflake SnowPro Core Certification: COF-C02 Exam

Sybex’s

Study Guide for Snowflake SnowPro Core

Certification

COF‐C02 EXAM

Hamid Mahmood Qureshi

Copyright © 2023 by John Wiley & Sons, Inc. All rights reserved.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey.

Published simultaneously in Canada.

978-1-119-82444-2

978-1-119-82445-9 (ebk.)

978-1-119-82446-6 (ebk.)

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at www.wiley.com/go/permission.

Trademarks: WILEY, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Further, readers should be aware that websites listed in this work may have changed or disappeared between when this work was written and when it is read. Neither the publisher nor authors shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www.wiley.com.

Library of Congress Control Number: 2022939912

Cover image: © Jeremy Woodhouse/Getty Images

Cover design: Wiley

To my wife and my children, who have provided support and encouragement throughout the strenuous process of writing this book.

Acknowledgments

Although this book lists my name as author, it would not be in its current form without the absolutely amazing team that contributed to its creation. Acquisitions Editor Devon Cajas got the wheels rolling on this book. Janet Wehner served as project manager, supported by Managing Editor Pete Gaughan and Saravanan Dakshinamurthy, content refinement specialist. Special thanks to the technical editor, Hassaan Sajid, who reviewed each chapter for content, structure, and technical correctness; Nancy Carrasco, who proofread and corrected many of my writing shortcomings; and Christopher Marland, who reviewed the book technically for correctness. Special thanks to all the other people who contributed to this book.

About the Author

Hamid Mahmood Qureshi is a Senior Cloud and Datawarehouse Professional with two decades of experience architecting, designing, and leading the deployment of many data warehouses and business intelligence solutions. He has substantial experience and qualifications with various data analytics systems, including Teradata, Oracle, Hadoop, and modern cloud-based tools like Snowflake. Having worked extensively with traditional technologies. combined with his knowledge of modern platforms, he has accumulated substantial practical expertise in data warehousing and analytics, which he has captured in his publications.

About the Technical Editor

Hassaan Sajid has 13 years of experience in data warehousing and business intelligence (BI/DW) in the retail, telecommunications, banking, insurance, and government sectors. He is currently working at Coles Australia, where his role as a senior technical analyst enables the business to understand the power and value of data. In the past, he has worked with various clients in Australia, UAE, Pakistan, Saudi Arabia, and the United States in multiple BI/DW roles including BI architect, BI developer, ETL developer, data modeler, operations analyst, data analyst, and technical trainer. He holds a master's degree in business intelligence and is a Professional Scrum Master. He is also certified in Snowflake, MicroStrategy, Tableau, Power BI, and Teradata. His hobbies include reading, traveling, and photography.

Table of Exercises

Introduction

Why should you learn Snowflake? Over the last few years, Snowflake has become increasingly popular with data‐led organizations and data enthusiasts. Snowflake is a data warehousing and data analysis platform built for the cloud and has been built from scratch to take full advantage of cloud features. It provides several novel features that changes how you work with data platforms.

With its increasing popularity, learning Snowflake means you increase your standing in the job market. Even if you are already knowledgeable in one or more data warehousing platforms, understanding Snowflake will give you an edge when searching for a new job or aiming for a promotion. Snowflake does not have a steep learning curve because of its simplicity and its use of SQL as the primary language. Once you have grasped the foundations of Snowflake architecture, the rest of the learning naturally flows.

Snowflake has a range of certifications available. SnowPro Core certification is at the foundation level, while other role‐specific advanced certifications are also available for focused areas such as data engineering, administration, or data science on the Snowflake platform. The SnowPro Core certification is a prerequisite for the advanced certifications; therefore, it's a good way to kick‐start your Snowflake journey.

This book aims to help you pass the SnowPro Core exam. This exam covers all the foundational concepts, such as Snowflake architecture, virtual warehouses, storage, security, Time Travel, cloning, data sharing, and data loading. These topics are emphasized in this book to help you prepare for the exam. Even after you pass the SnowPro Core exam, this book should remain a useful reference for you.

What Is Snowflake?

Snowflake is a relatively new cloud‐optimized database, designed primarily for data warehousing solutions on the cloud. Snowflake Inc. was founded in 2012 by three data warehousing experts: Benoit Dageville, Thierry Cruanes, and Marcin Zukowski. Snowflake was designed from the ground up as a cloud‐only data warehouse platform.

A significant advantage of the Snowflake platform compared to other data warehouses is its differentiated architecture. Snowflake takes full advantage of the underlying cloud platform's novel features. The outcome is a hybrid architecture in which the computation or processing layer can be scaled independently of the storage layer. Snowflake's hybrid architecture also brings out a unique array of features not seen in many other databases, introducing concepts such as zero‐copy cloning, Time Travel, and secure data sharing.

Why Become SnowPro Core Certified?

There are several good reasons to get your SnowPro Core certification:

Professional Development   Certifications are an easy pathway for continuous professional development and adding to your skillset. Because the SnowPro Core certification exam tests all the foundational concepts of Snowflake, even preparing for the exam will give you enough technical skills to develop Snowflake‐based solutions.

Increases Your Marketability   SnowPro Core certification increases your marketability to prospective employers. SnowPro Core certified candidates may negotiate a better starting salary because hiring businesses don't need to spend on training and upskilling such candidates.

Takes You a Step Closer to Advanced Certifications   The SnowPro Core exam is a steppingstone to several advanced Snowflake certifications, such as SnowPro Advanced Data Engineer. Gaining the SnowPro Core certification will allow you to undertake any of the advanced Snowflake certifications suited to your line of work.

Provides Proof of Professional Achievement   Organizations recognize the importance and benefits of certifications such as SnowPro Core and are increasingly encouraging their employees to obtain certification. As a result, a growing number of individuals add the SnowPro Core certifications to their professional profiles every day.

Raises Employer Confidence   Management is more confident to take on significant initiatives and work for new clients when they have faith in the team's skills. Certifications are a great way to instill the skills in your team and gain management's confidence. If you can prove you have the necessary skills, they are more likely to undertake new initiatives and likely to put you in charge with confidence in your skills.

How to Become SnowPro Core Certified

The SnowPro Core certification is available to anyone who wishes to take the exam. There are no prerequisites; however, it is recommended that you develop an understanding and experience of the Snowflake platform with self‐study and by performing hands‐on exercises and experimentation with the Snowflake platform.

Snowflake's SnowPro Certification exam is administered by Pearson Vue, and you can take it in any of the 1,000+ testing centers worldwide or at home with a virtual proctor.

To register for the exam with Pearson Vue, visit https://home.pearsonvue.com/snowflake. You will need to register for a new account. Once you do, you can schedule your SnowPro Core exam.

Finally, Snowflake exam policies are subject to change. Please be sure to check www.snowflake.com/certifications for the current policies before you register and take the exam.

Like all exams, the SnowPro Core certification from Snowflake is updated periodically and may eventually be retired or replaced. At some point after Snowflake is no longer offering this exam, the old editions of our books and online tools will be retired. If you have purchased this book after the exam was retired, or are attempting to register in the Sybex online learning environment after the exam was retired, please know that we make no guarantees that this exam’s online Sybex tools will be available once the exam is no longer available.

Who Should Buy This Book

This book is useful for anyone who wishes to pass the SnowPro Core certification exam. If you're new to Snowflake, this book provides the basics you'll need to learn Snowflake from scratch. The book also provides in‐depth information you need to complete the exam.

How This Book Is Organized

This book consists of 10 chapters plus supplementary information, including a glossary and an assessment test, which follows this introduction. The chapters are organized as follows:

Chapter 1, Introduction and Overview, covers the history of Snowflake and discusses the various Snowflake certifications with a focus on the SnowPro Core certification.

Chapter 2, Snowflake Architecture, provides a detailed understanding of the unique Snowflake architecture, how the storage is decoupled from the compute, how data is stored in the form of micro‐partitions, and the advantages of this unique architecture. The chapter also covers the critical architectural layers in Snowflake.

Chapter 3, Interfaces and Connectivity, talks about various interfaces, focusing on Snowflake web interfaces and SnowSQL, through which a user can interact with a Snowflake instance. The chapter also describes various connectors and drivers that can be used to connect to Snowflake. Finally, the chapter discusses the partner ecosystem in Snowflake.

Chapter 4, Loading Data, discusses the variety of methods provided by Snowflake for loading data, including bulk data loading and processing data in a continuous manner. This chapter walks you through the concept of staging in Snowflake, describing the internal and external stage types. The chapter also discusses loading semi‐structured data, basic data transformations, and exporting data from Snowflake.

Chapter 5, Data Pipelines, discusses Snowflake's capability to schedule SQL statements and stored procedures through the concept of tasks. The chapter also touches on streams, which is Snowflake's method of providing change data capture.

Chapter 6, Continuous Data Protection, covers the components of continuous data protection and explores Time Travel, Fail‐Safe, and the undrop functionality, which are Snowflake features that help protect and recover data in the event of human error. The chapter also covers the concept of transient and temporary tables.

Chapter 7, Cloning and Data Sharing, describes zero‐copy cloning and how it works behind the scenes. The chapter then explores data sharing and the three approaches to data sharing in Snowflake.

Chapter 8, Performance, focuses on the performance optimization features and techniques that you can use to improve query performance and, at times, reduce costs. The chapter covers scaling up and down a virtual warehouse and autoscaling a virtual warehouse to accommodate increased concurrency. This chapter also touches on data clustering and materialized views, which can be used to optimize query execution.

Chapter 9, Security, explains several Snowflake features that enable security at various levels in the Snowflake software stack. The chapter discusses security implementation starting from the data storage layer to other levels such as authentication control, data access control or authorization, and network‐level controls to manage access to your Snowflake instance.

Chapter 10, Account and Resource Management, covers the prebuilt views and table functions that enable Snowflake customers to keep track of their Snowflake credit and storage usage. The chapter also discusses resource monitors used for tracking and managing credit usage. Finally, the chapter discusses Snowflake's release management processes.

Chapter Features

Each chapter begins with a list of the SnowPro Core exam objectives that are covered in that chapter. The book doesn't cover the objectives in order, so you shouldn't be alarmed at some of the odd ordering of the objectives within the book.

Each chapter has exercises that apply the newly taught material. At the conclusion of each chapter, you will find two exam preparation tools:

Exam Essentials   This section provides a summary of the key information presented in the chapter. You should have a complete grasp of the information summarized in this section.

Review Questions   Each chapter concludes with a set of review questions. You should answer these questions and compare your response against the provided answers. If you are unable to correctly answer at least 80 percent of these questions, you should revisit the chapter or at least the areas that you do not fully understand.

The review questions, assessment test, and other testing elements in this book are not derived from the SnowPro Core exam questions; therefore, you should not memorize the answers and assume that doing so would enable you to pass the exam. You should study the fundamentals and understand the concepts, which will enable you to answer the exam questions and pass the SnowPro Core exam. Learning the underlying topics is also the approach that will serve you best in the workplace—the ultimate goal of certification exams.

Bonus Digital Contents

This book is accompanied by an online learning environment that provides several additional elements. Items available among these companion files include the following:

Practice Tests   All of the questions in this book appear in our proprietary digital test engine—including the 30‐question assessment test at the end of this introduction and the questions that make up the review question sections at the end of each chapter. In addition, there is a 100‐question practice exam.

Electronic Flashcards   The digital companion files include 170 questions in flashcard format (a question followed by a single correct answer). You can use these to review your knowledge of the SnowPro Core exam objectives.

Glossary   The key terms from this book, and their definitions, are available as a fully searchable PDF.

Interactive Online Learning Environment and Test Bank

You can access all these resources at www.wiley.com/go/sybextestprep.

Conventions Used in This Book

This book uses certain typographic styles in order to help you quickly identify important information and to avoid confusion over the meaning of words such as on‐screen prompts. In particular, look for the following styles:

Italicized text indicates key terms that are described at length for the first time in a chapter. (Italics are also used for emphasis.)

A monospaced font indicates SQL code, contents of files, filenames, and Internet URLs.

In addition to these text conventions, which can apply to individual words or entire paragraphs, a few conventions highlight segments of text:

A note indicates information that's useful or interesting but that's somewhat peripheral to the main text.

A tip provides information that can save you time or frustration and that may not be entirely obvious. A tip might describe how to get around a limitation or how to use a feature to perform an unusual task.

Sidebars

A sidebar is like a note but longer. The information in a sidebar is useful, but it doesn't fit into the main flow of the text.

Real World Scenario

Real‐World Scenario

A real‐world scenario is a type of sidebar that describes a task or example that's particularly grounded in the real world. This may be a situation I or somebody I know has encountered, or it may be advice on how to work around problems that are common in real, working Snowflake environments.

EXERCISES

An exercise is a procedure you should try on your own to help you learn about the material in the chapter. Don't limit yourself to the procedures described in the exercises, though! Tinker with different Snowflake features to really learn about Snowflake.

SnowPro Study Guide Exam Objectives

Sybex's Study Guide for Snowflake SnowPro Core Certification has been written to cover every SnowPro exam objective at a level appropriate to its exam weighting. The following table provides a breakdown of this book's exam coverage, showing you the weight of each section and the chapter where each objective or subobjective is covered:

Domain 1.0: Snowflake Cloud Data Platform Features and Architecture

Domain 2.0: Account Access and Security

Domain 3.0: Performance Concepts

Domain 4.0: Data Loading and Unloading