Snowflake SnowPro Core Certification Summary
Chapter 21: Snowflake SnowPro Course Summary
Published in
8 min readDec 7, 2021
After several months, we reached the last chapter of the Snowflake SnowPro Core Certification course. All the theory has already been explained. Although it has been an intense journey, I hope it has been rewarding and allows you to achieve your goal of passing the exam and learning about this magnificent technology. I also hope you enjoyed reading it as much as I enjoyed writing it.
In this last chapter, I will summarize some of the concepts we’ve seen during the course, to have them detailed and organized. Let’s go with it!
Remember that you can find all the chapters from the course at the following link:
SNOWFLAKE SUMMARY
Snowflake Architecture
- DDL → Data Definition Language. It refers to the commands to interact with Tables, Schemas, etc. → Create, Drop, Alter…
- DML → Data Manipulation Language. → Insert, Update, Delete…
- Snowflake editions → Standard, Enterprise, Business Critical.
- Cloud Providers → AWS, Azure, GCP.
- Snowflake CLI Client → SnowSQL.
- Snowflake layers → Centralized Storage, Compute data, Cloud Services & Cloud Agnostic Layer.
Chapters explaining these concepts:
Micro-partitions & Clustering keys
- Storage of micro-partitions → Between 50 and 500MB of uncompressed data organized in a columnar way.
- Metadata in micro-partitions → Range of values for each column, number of distinct values, and other properties for optimization.
- Commands to get the Clustering Depth → SYSTEM$CLUSTERING_DEPTH, SYSTEM$CLUSTERING_INFORMATION.
- Indicators to determine if you need to define clustering keys → Queries are running slow & Large clustering depth.
Chapters explaining these concepts:
Tables
Views
- Can we define clustering keys in materialized views? → Yes.
- Materialized views limitations → Time Travel // Current_Time.
Chapters explaining these concepts:
Stages
- Types of stages → External & Internal
- Types of internal stages → User, Table, named internal
- Identifier of user stage → “@~”
- Identifier of table stage → “@%”
- Stage metadata → METADATA$FILENAME & METADATA$FILE_ROW_NUMBER
Chapters explaining these concepts:
COPY Command
- In which stages does it work? → In all of them.
- What command does it execute in case of failure? → ABORT_STATEMENT.
- Maximum file size using the Snowflake UI → 50 MB. If you want to COPY bigger files, you should use SnowSQL.
- Command to ignore the Metadata Cache → FORCE = TRUE.
- Metadata cache duration → 64 days.
SnowPipe
- What command does it execute in case of failure? → SKIP_FILE.
- Metadata cache duration → 14 days.
- Can SnowPipe use Resource monitors? → No.
Chapters explaining these concepts:
PUT & GET
- In which stages do they work? → Only internal stages.
- Executable from the Snowflake UI? → No
Chapters explaining these concepts:
Warehouses
- Params that you can configure → Auto suspend & Auto Resume & Maximum / Minimum Clusters & Scaling Policy & Size & Name.
- Minimum edition for Multi-cluster warehouses → Enterprise.
- Multi-warehouse modes → Maximized & Auto Scale.
- Scaling policy → Standard & Economy.
Chapters explaining these concepts:
Resource Monitors
- Params → Credit Quota, Monitor Level, Schedule & Actions.
- Types of actions → Notify, Notify & Suspend, Notify & Suspend Immediately.
Chapters explaining these concepts:
Cache
- Types → Metadata, Query Result, Warehouse.
- For how long does the metadata cache store the results? → 64 days.
- For how long does the query result cache store the results? → One day.
- For how long does the warehouse cache store the results? → Until the warehouse is disconnected.
Chapters explaining these concepts:
Storage Features
- Time travel data retention period by default → One day. We can extend it to 90 days, but it’s one day by default.
- Time Travel for transients and temporary tables → One day.
- Time travel for permanent tables → Until 90 days.
- Snowflake edition to extend time travel until 90 days → Enterprise
- How to recover data using fail-safe → Contacting Snowflake support.
- Layer in charge of performing the zero-copy clone → Cloud Services Layer.
- Can I clone an external table? → No.
Chapters explaining these concepts:
Roles
- Approaches for the access management → DAC & RBAC.
- Key concepts → User, Role, Securable Object, Privilege
- Default Roles → ACCOUNTADMIN, SECURITYADMIN, SYSADMIN, USERADMIN & PUBLIC
Chapters explaining these concepts:
Shares
- Types of shares → Inbound & Outbound.
- Role that can create shares → ACCOUNTADMIN.
- Can you Clone the objects from an inbound Share? → No.
- Can you do Time Travel in objects from a Share? → No.
- Can you share an inbound share to a different account? → No.
- GRANT USAGE ON <db> TO <share> → Assign privileges to a share.
Chapters explaining these concepts:
Stored Procedures & UDFs
Chapters explaining these concepts:
Tasks
- Maximum number of children tasks → 100. A task can only have 100 children.
- Maximum number of a tree of tasks → 1000.
Transactions
- Number of hours in which Snowflake would abort a transaction if it goes to the detached state → 4 hours.
Chapters explaining these concepts:
Streams
- Different types of streams → Standard, append-only & insert-only streams.
- Columns from a stream → METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID.
- Can we create streams from external tables? → Yes. Insert-only streams.
- Can we create streams from views? → As a new feature of March 2022, Streams are supported in views and secure views!
Chapters explaining these concepts:
File Formats
- Structured Data formats → CSV.
- Semi-Structured Data formats → JSON, Parquet, XML, Avro, ORC.
- Data type to store semi-structured data as arrays or objects → Variant.
- Limit size of a variant data type → 16 MB.
- How are no-native values from a variant being stored? → As Strings.
Sequences
- Function to generate a set of distinct values → nextval.
Chapters explaining these concepts:
Ecosystem, Compliance, Data Marketplace & Security
- Types of partner connect in Snowflake → Technology & Solution partners.
- Categories to classify Snowflake partners → Data Integration, ML & Data Science, Security & Governance, Business Intelligence, SQL Editors, and Programming Interfaces.
- Snowflake is compliant with… → HITRUST / HIPAA, ISO/IEC 27001, FedRAMP Moderate, PCI-DSS, SOC 2 Type II, SOC 1 Type II and GxP.
- Data Marketplace listings → Standard Data Listing and Personalized Data Listing.
- Security features that Snowflake provides → Dynamic Data Masking and External Tokenization.
Chapters explaining these concepts:
Grants and other useful commands
- SHOW GRANTS OF ROLE <role> → See a role's users.
- SHOW GRANTS TO ROLE <role> → See a role's privileges.
- Necessary grants to clone a table → SELECT.
- Necessary grants to clone a pipe/stream/task → OWNERSHIP.
- Necessary grants to clone other objects → USAGE.
- Necessary grants to see the TASK_HISTORY → ACCOUNTADMIN OR OWNERSHIP on a task OR MONITOR_EXECUTION.
Practice Exam Questions
- Click here to be able to take one complete exam on Medium.
- Find more than 500 Snowflake SnowPro questions at FullCertified, including a real exam simulator to thoroughly prepare for the exam and the possibility to download all the questions on PDF.
Thanks for Reading!
And this is the end of the course; I hope you found it helpful! If so, and you want to support my work, you can do so in the following ways:
- The BEST way is to follow me on Medium here.
- Feel free to clap if this post is helpful for you! :)
Finally, I would like to tell you that you can ask me any question, and if you have any recommendations for another course that you would like to see, you can comment! See you soon
