Top 10 Tactics to get Snowflake Certified
Snowflake Certification Guide
This article outlines my top 10 tactics to prepare for the Snowflake Certification Exam. We'll review the available certification options and explain what to expect from the exam and how to prepare.
Finally, I'll provide a list of carefully crafted Snowflake Exam questions and include lots of tips and advice on where to find both free and paid for training.
Why trust this guide?
Having designed and built data warehouse systems since the 1990s I worked at Snowflake for nearly five years as a Senior Solution Architect and then Senior Instructor. I've completed the SnowPro certification exam three times along with the Advanced Architect and Data Engineering exams and helped prepare, author and review many exam questions at Snowflake.
I finally left Snowflake to write a book; this guide is my way of "giving something back" to the industry's many Data Engineers and Architects.
What Snowflake Certification Exams are available?
The diagram below illustrates the learning path for students wishing to complete Snowflake certification.
Effectively, you need to start with the Snowflake SnowPro Core exam, demonstrating a knowledge and understanding of the key Snowflake technologies. From here, you can specialize in one of a number of Advanced Certification options based on your role, including Advanced Architect, Administrator, Data Engineer, Data Scientist, and Data Analyst.
Tactics towards Snowflake Certification
Here's a quick summary of my advice. Read on for details, including my experience and advice.
Get hands-on experience. Set up a free Snowflake trial account and start with the Snowflake in 20 minutes tutorial to get started. Also, take a look at Snowflake QuickStarts which includes a huge number of guided tutorials including Transformation in Snowflake , Getting Started with Time Travel and Semi-Structured Data Processing. There is no substitute for real hands-on experience.
Get some Free training. For example, Snowflake University includes a series of short training modules, including a Data Warehouse Workshop and a Data Engineering Workshop. There's also some free training on YouTube, including a series of training courses with an overview of subjects around preparing for the SnowPro exam and getting Snowflake Certified.
Attend some paid training. Snowflake provides a four-day instructor-led Fundamentals Training Course, which covers most subjects. At around $3,000, this is probably one to get your employer to pay for, but it's an effective way to cover a huge range of subjects quickly. Snowflake also produces a recorded (on-demand) training course, the SnowPro Core exam preparation course, which is a more reasonable $375, but you should have prior Snowflake experience.
Download the Official Guide: The SnowPro Core Snowflake Certification Exam Guide is a PDF guide produced by Snowflake and provides an overview of the exam content. It's freely available, but you must first set up a free Snowflake Community account. The real value of this guide is the links to the documentation you need to read.
Read up on Snowflake: I've been writing about Snowflake technology for the past five years on www.Analytics.Today. If you're new to Snowflake, read What is the Ideal Cloud Data Warehouse or Top 3 Snowflake Performance Tuning Tactics. There is also a great overview of the Snowflake architecture in Top 10 Strategies for Cost Optimization in Snowflake.
Carefully read the question: Easily the most common mistake and the easiest to fix. I've completed the Snowflake Certification exam three times and never run out of time, so don't rush the exam. Take care to carefully read the question and examine each option. If you're unsure, mark the question and return to it later.
Read the Documentation: It may sound tedious, but the Snowflake Documentation is well written. Pay particular attention to the COPY INTO command, which has many options, and these frequently come up on the exam.
Test your knowledge. Once ready for the exam, you can try a trial exam for less than $15 (or local currency equivalent). These Udemy Practice Tests include 100-600 questions. If you can pass one of these, you're probably ready for the real thing. There are also several freely available "flash cards" at Quizlet Flashcards, Quizlet Test, and Quizlet SnowPro.
Focus on the gaps: Once you've completed a Udemy Practice Test, you can see which questions you got wrong. You need to focus on these subjects - not the ones you already know. Review the Revision Guide and look up any details in the Snowflake Documentation you were unsure about. Applying for the real thing once you can confidently pass a trial exam.
Set up your environment: Most people attempt the exam remotely on their home computer. When you sign up for the exam, you must carefully read the instructions and set up your environment. You are not allowed to have other people in the room with you and must keep a clear desk. Finally, you’ll need to download some software and have your webcam and internet speed tested. You should get all this done well before the exam, log in 30 minutes before your scheduled time, and settle your mind.
Snowflake Certification Exam Subjects
The subjects in the exam are grouped into six domains and overall they cover around 80% of the entire Snowflake technology.
Snowflake Overview & Architecture:
A comparison of Snowflake to legacy solutions including elastic storage and compute
Understanding of the 3-Tier architecture (Cloud, Compute and Storage)
The Snowflake hierarchy including Organization, Accounts, Database, Schemas, Table types, Views and Stored Procedures
Data Storage & Protection
How Snowflake stores data including Micro-Partitions, data clustering and storage monitoring
Data protection including Time Travel, Fail Safe, Cloning and Replication
Virtual Warehouses
Principles including Virtual Warehouse Size and credit usage, Scaling Up and Out for concurrency and workload
Virtual warehouse parameters and behavior
Best Practices for Scaling Up and Out and how to monitor and manage resources
Accounts & Security
Account management, usage and views
Security features include MFA, Encryption, Network Security Policies and Authentication methods.
Roles and privileges, the role hierarchy and privilege inheritance
Data Protection including data masking, row-level policies and secure views
Data Movement
Commands including COPY, SNOWPIPE, PUT, GET and VALIDATE options
Types of stage and supported File Formats
Data unloading including available formats techniques for fast unloads
Data loading and unloading best practices including file sizes, formats and folder structure
Loading and querying semi-structured data including file formats, the VARIANT data type and flattening nested data.
Snowflake Performance Techniques
SQL Optimization, the Query Profile, and Query Lifecycle, including caching
An understanding of Clustering, Materialized Views, Search Optimization, and Query Acceleration Service
What's the Snowflake Certification Exam Like?
The exam consists of 100 questions multiple-choice questions which you can take either online or at a test centre - although most people use the online method. There are three types of questions:
True or False. Select which one is correct.
Select one answer from the set of options.
Select two or three correct answers from the set.
To give you a flavor of the exam, I've written 20 purpose-built questions which follow the official Snowflake question writing standards later in this article.
Having successfully completed both the SnowPro and Advanced exams, I found the SnowPro the hardest. In addition to a huge range of subjects, the questions are often quite specific and detailed, testing pure knowledge rather than experience.
Sadly, there are few (if any) scenario questions where common sense or experience would be an advantage, and it's really a question of brute knowledge. If you know the facts sufficiently, you'll pass the test - often regardless of your IT experience.
To be clear, almost no questions require a programming background, although knowledge and experience of SQL are definite prerequisites.
You are normally given 115 minutes (nearly two hours) to complete the exam, and I've never run out of time, so don't feel you need to rush.
It's advisable to carefully read every question first (some can be tricky) and take your time. You can mark a question and return to it later if you're unsure.
I must admit, I've been hugely impressed by Snowflake's effort to improve the exam over time. Without giving anything away, they constantly perform a statistical analysis to identify badly worded questions or ambiguous options. If you disagree with a question, there's an option to point this out. You won't get any credit for a wrong answer, but your comments will be reviewed and acted upon.
Is Snowflake Certification Worth it?
For me personally, it was a matter of confidence rather than cash. When you start with any new technology, you don't know what you don't know, and it's easy to feel uneasy when people talk about subjects you're unsure about. I found the certification forced me to understand subjects like Account-level security that I'm little interested in. While it's becoming impossible to be a Snowflake expert at everything, with the certification, you'll gain a good understanding of the key concepts and a basic knowledge of the rest.
Almost all Snowflake Partner companies require SnowPro certification, and if you're starting in Snowflake technology, it should give you an edge over other candidates.
Ironically, I found the Snowflake Advanced Architect exam easier (there are many more scenario-type questions and less dependence upon detailed knowledge). There are far fewer individuals with Advanced Certification, and this should really help you stand out.
Be aware. However, you need the SnowPro certification before attempting the Advanced Options.
What training is available leading to Snowflake Certification?
Training sounds like an easy way to learn, but in reality, there's no easy route, and even attending a $3,000 Snowflake instructor-led training course is no guarantee of success. You still need to put the effort in, but each option has different strengths.
Free Training is always a bonus, but much of the Snowflake free training is very basic and I have found the YouTube content can at times be out of date.
The paid training costs anything from $15 to $3,000 and regardless of the method used, you'll still need to dedicate the time and effort to learn.
Free Training
There are a number of freely available recorded videos including from:
Snowflake University: Snowflake provides a series of free short training modules including Data Warehouse Workshop and a Data Engineering Workshop. These tend to be best for people with little Data Warehousing experience as they require no prior experience. They consist of a series of short animated videos followed by questions to confirm you were listening. At the end of each course, you earn a "badge" to encourage you to continue. A good start for absolute beginners.
YouTube: Has a lot of videos available. Sadly the Snowflake Playlist tends to be full of marketing material, but there's a series of free introductions by Joyce Avilla (author of Snowflake: The Definitive Guide) which are fairly good. Be wary of any YouTube videos older than 2 years - Snowflake is developing incredibly fast and you may be misled by out-of-date content.
Paid "on-demand" Training
Snowflake provides a recorded (on-demand) training course, the SnowPro Core exam preparation course, specifically for IT professionals preparing for the Snowflake Certification exam, which includes most subjects. At $375 per seat, you'd likely need your employer to pay for it, but it's a possible option.
Finally, Udemy provides a range of training courses, although to date, only the Ultimate SnowPro Core Certification course specifically targets the certification exam. These course prices vary daily and are frequently on sale. Avoid paying the full price by signing up and getting notified about offers. Expect to pay around USD 20 or equivalent in local currency.
Paid "instructor-led" Training
Snowflake deliver an instructor-led Fundamentals Training Course for around $3,000 and covers most subjects. Be aware, however, none of the Snowflake training courses specifically match the certification exam and you'll still need to put in additional effort. The real value of this method is the "hands-on labs" which guide you through a series of steps to complete a task. This course is delivered in APAC, EMEA and US time zones and languages include English, French and Japanese.
Other 3rd party training courses. I couldn't really comment upon or recommend them as I've no direct experience and I'm not open to bribery (thanks all the same to the companies that have reached out to me).
Getting a Snowflake Trial Account
While you may have access to Snowflake at work, it's unlikely you'll have complete authority to try out stuff for yourself. I'd therefore recommend setting up a free Snowflake trial account which gives you an entire month and $400 of credits (enough computing power to run an X-SMALL Virtual Warehouse continuously for several days).
Unless you specifically need to deploy on Azure or GCP it's recommended you select:-
Enterprise or Standard Edition
AWS
US East (Ohio)
Be aware Enterprise Edition gives you few advantages over Standard Edition, but it will cost around $3.00 per credit instead of $2.00, making your credits go further. Either way, you'll unlikely burn through $400 of credits in a month, and you can always sign up again for another month.
Having set up a Snowflake Account, take a look at Snowflake QuickStarts which includes a huge number of guided tutorials including Transformation in Snowflake , Getting Started with Time Travel and Semi-Structured Data Processing. These will take you step-by-step though a process to complete a hands-on task.
How to Prepare for the Snowflake Certification Exam?
Once you have a trial account set up, you can do things (for example create or drop databases or roles) that you will be prevented from doing on your work account.
You're already off to a good start, but the best advice is to read the "Top 10 Tactics" at the beginning of this article.
However, if you think you're ready for the exam, you need to take one final step, and it should only cost around USD $15-20. You should search Udemy for SnowPro Certification Exam Practice Tests and take one or more practice exams.
While I wouldn't normally advise people to spend their hard-earned cash, I found these practice exams to be priceless. Be careful to buy a practice exam rather than a training course, and be guided by the reviews. Be aware that some exam questions are out-of-date, and I spotted a few mistakes whereby Snowflake had released new features since the practice exam was written. Otherwise, they're a great way to test your knowledge and reassure you're on the right track.
The best advice is to review your answers at the end and revise the subjects you were weakest on.
There are also several freely available "flash cards" at Quizlet Flashcards, Quizlet Test and Quizlet SnowPro, although these are no substitute for a practice exam.
Finally, don't forget the 20 sample questions to give you an idea of what to expect.
Example Snowflake Certification Questions
Given the fact I've written lots of questions for the Snowflake Certification Exams, it was relatively easy to write the kind of questions you should expect. To my surprise, there is an entire science behind writing precise and unambiguous questions. You may notice (for example) that each answer is approximately the same length which is a deliberate tactic so no single option stands out.
Q1. You execute a COPY statement against table X to load some data, and then CLONE it to X2 and repeat the COPY to the new table. What’s the outcome?
A) The X2 table has the data loaded twice
B) The COPY against X2 fails with an error
C) The COPY against X2 finishes, but loads no entries
Q2. Virtual Warehouses are charged in credits which of the following are true:
A) The warehouse is charged on a per-minute basis with a minimum 60 seconds
B) The warehouse is charged on a per-second basis with a minimum 60 seconds
C) The warehouse is charged per hour with a minimum of 30 minutes
Q3. You are working with a team of people testing a new data pipeline and having executed a COPY statement you find no rows were loaded. What’s the most likely reason?
A) The data has already been loaded and Snowflake keeps track of files loaded using metadata
B) The format is incorrect and the load silently failed. You need to check for error messages
C) The columns defined in the table don’t match the data loaded and the load failed to complete
Q4. You need to resize a warehouse from X-SMALL to SMALL. How would you achieve this?
A) Wait until all the queries have finished and execute an ALTER WAREHOUSE SET WAREHOUSE_SIZE = ‘SMALL’;
B) Suspend the warehouse to prevent new queries and then ALTER WAREHOUSE SET WAREHOUSE_SIZE = ‘SMALL’;
C) Execute ALTER WAREHOUSE SET WAREHOUSE_SIZE = ‘SMALL’ immediately. New queries will run on the SMALL warehouse.
Q5. Under what circumstances would you execute ALTER WAREHOUSE SET MIN_CLUSTER_COUNT = 1, MAX_CLUSTER_COUNT = 5?
A) You are executing queries that are processing terabytes of data and need more clusters to complete the work on time.
B) It’s month end and there’s a significant increase in the number of concurrent users executing queries on the warehouse.
C) Neither of the above options is correct.
Q6. When creating a virtual warehouse Snowflake recommends setting the AUTO_SUSPEND = 0.
A) True
B) False
Q7. How frequently does Snowflake release new versions of software?
A) Every fortnight
B) Every week
C) Every month
D) As needed
Q8. Which of the following is the most powerful system-defined role in Snowflake?
A) SYSTEMDBA
B) ACCOUNTDBA
C) ACCOUNTADMIN
C) SYSADMIN
D) USERADMIN
Q9. Your end-user queries against a SALES table WHERE SALE_DATE = current day are increasingly slow on a MEDIUM size warehouse as the data volume has grown from 250GB to 1TB. What’s the most cost-efficient way of improving query performance?
A) Execute an ALTER WAREHOUSE SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 to provide more compute power
B) Execute an ALTER WAREHOUSE set WAREHOUSE_SIZE = LARGE to process the larger data volume and complete the queries faster
C) Execute an INSERT OVERWRITE statement to sort the data by SALE_DATE to improve the partition elimination and reduce elapsed time
D) Execute ALTER TABLE SALES SET SEARCH_OPTIMIZATION = TRUE to build a Search Structure which will improve search times.
Q10. Under what circumstances will multiple users make use of the RESULTS cache? (Choose any two)
A) They run queries using the same virtual warehouse.
B) They run queries using the same USER name.
C) Their query SQL is exactly the same except for spaces.
D) They run their queries in the same 24-hour period.
Q11. Which of the following are valid types of Snowflake table?
A) EXTERNAL
B) TRANSIENT
C) TEMPORARY
D) PERMANENT
E) All the above
Q12. When unloading data to an internal stage which option will unload the data fastest?
A) PARALLEL_EXECUTION=TRUE
B) PARALLEL_UNLOAD=TRUE
C) SINGLE=FALSE
D) None of the above
Q13. Which of the following are valid types of Snowflake VIEW? (Choose any three).
A) PERMANENT VIEW
B) TEMPORARY VIEW
C) SECURE VIEW
D) MATERIALIZED VIEW
E) EXTERNAL VIEW
Q14. Which tables will gain the most from clustering?
A) Tables in the size range 100MB to 250MB.
B) Tables in the size range 1TB or upwards.
C) Tables in the size range 100GB to 250GB
Q15. Which type of function returns a single value? (Choose any two)
A) Aggregate Function
B) Window Function
C) Scalar Function
D) Table Function
Q16. Snowflake recommends you should always extract VARIANT data to a fixed table structure.
A) True
B) False
Q17. It is possible to CLONE tables between two accounts provided they are in the same REGION and CLOUD provider.
A) True
B) False
Q18. What is the maximum number of days of time travel on a PERMANENT table in the ENTERPRISE edition of Snowflake?
A) 90 Days
B) 7 Days
C) 1 Day
Q19. Which privilege do you need to execute queries on a virtual warehouse?
A) OPERATE
B) USAGE
C) EXECUTE
D) MODIFY
Q20. Snowflake supports database replication only between Snowflake Accounts in the same Region.
A) True
B) False
Answers
Q1. A. The X2 table has the data loaded twice. A COPY statement keeps a record of the data files loaded in the table metadata, and this normally prevents loading duplicate entries. However, when table X was cloned to X2, the metadata is not copied and the new COPY re-loaded the data again.
A2. B. Virtual warehouses are charged per-second with a minimum charge of 60 seconds.
A3. A. When data is loaded using a COPY statement, Snowflake tracks which files have already been loaded using metadata. Once loaded, a file will not be reloaded.
A4. C. You can execute ALTER WAREHOUSE SET WAREHOUSE_SIZE = 'SMALL' immediately as existing queries will continue to run and new queries will start on the larger warehouse.
A5. B. Executing ALTER WAREHOUSE SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 will allow the warehouse to support additional concurrent queries on the warehouse. This will not improve the warehouse's ability to process an increased volume of data.
A6. B. False. If you set the AUTO_SUSPEND = 0 the warehouse will continue to execute until you execute ALTER WAREHOUSE SUSPEND. The best practice is to set the AUTO_SUSPEND to between 60 seconds and 600 seconds depending on the need to keep the warehouse cache warm.
A7. B. Snowflake releases new software versions weekly.
A8. C. ACCOUNTADMIN is the most powerful System Defined Role and should be used with caution and granted to as few users as possible.
A9. C. The most cost-efficient way to improve query performance on a large (1TB+) sized table being queried by a given DATE is to improve data clustering. For the initial clustering sorting the data is the most efficient method. To keep the data clustered, you could also consider creating a cluster key.
A10. C&D. Queries will use the RESULT CACHE if the query text is the same (except for blank spaces) and they are executed within the same 24-hour period. The warehouse or user's name is not relevant.
A11. E. External, Transient, Temporary and Permanent are all examples of valid Snowflake table types.
A12. C. When unloading data to a stage you have two options. SINGLE=TRUE which is slower and creates a single data file or SINGLE=FALSE which allows the query to execute in parallel across all CPUs and produce multiple data files. This is the fastest method to unload data.
A13. C&D. Snowflake supports a SECURE and MATERIALIZED VIEW. It does not accept PERMANENT, TEMPORARY or EXTERNAL VIEW as correct syntax.
A14. B. Tables sized from 1TB upwards will gain the most benefit from clustering. However, there may be a significant benefit on queries against smaller tables.
A15. A&C. Both Scalar and Aggregate functions return a single value.
A16. B. False. Data can be queried directly against a VARIANT column. However, if query performance is insufficient consider extracting the data to a fixed table structure to gain from improved partition elimination.
A17. B. False. It is not possible to clone tables across an Account boundary in any circumstances. Tables can only be cloned within the same Snowflake Account.
A18. A. Permanent tables on Enterprise edition support up to 90 days of Time Travel. The STANDARD edition supports a maximum of 1 day of Time Travel.
A19. B. You need USAGE privilege to execute queries on a Virtual Warehouse.
A20. B. False. Snowflake supports Database Replication between any Account or cloud provider. These can be in the same region or across regions
Notice Anything Missing?
No annoying pop-ups or adverts. No bull, just facts, insights and opinions. Sign up below, and I will ping you a mail when new content is available. I will never spam you or abuse your trust. Alternatively, you can connect on LinkedIn and Twitter or leave a comment below.
Disclaimer: The opinions expressed on this site are entirely my own and will not necessarily reflect those of my employer.
John A. Ryan