Geek Culture
Published in

Geek Culture

SnowFlake SnowPro Core Certification Summary

Chapter 21: SnowFlake SnowPro Course Summary

After several months, we have 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.

Summary of the SnowPro Core Certification Exam.
Summary of the SnowPro Core Certification Exam.

In this last chapter, I will summarize some of the concepts that we’ve seen during the course that we have to memorize, to have them in a detailed and organized way. 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.
SnowFlake Features by Edition (I).
SnowFlake Features by Edition (I).
SnowFlake Features by Edition (II).
SnowFlake Features by Edition (II).

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, 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

Difference between SnowFlake tables.
Difference between SnowFlake tables.

Views

  • Can we define clustering keys in materialized views? → Yes.
  • Materialized views limitations → Time Travel // Current_Time.
SnowFlake views.
SnowFlake views.

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 1 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 different account? → No.
  • GRANT USAGE ON <db> TO <share> → Assign privileges to a share.

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? → No

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 to… → HITRUST / HIPAA, ISO/IEC 27001, FedRAMP Moderate, PCI-DSS, SOC 2 Type II, SOC 1 Type II and GxP.
  • Data Marketplace listingsStandard 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 the users that a role has.
  • SHOW GRANTS TO ROLE <role> → See the privileges that a role has.
  • 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.

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:

  1. The BEST way is following me on Medium here.
  2. Feel free to give claps to all the chapters from this course that were useful for you, and share them in your social networks, this would be very helpful for me.
  3. Not a Medium member yet? Support my writing on Medium by joining through the link below:

Finally, I would like to tell you that you can ask me any question you may have, and if you have any recommendation of another course that you would like to see, you can put it as a comment! See you soon

--

--

--

A new tech publication by Start it up (https://medium.com/swlh).

Recommended from Medium

TrustBase Tinder Program Officially Launched

Orion: A next-generation automation testing tool

My Software Engineering Manager Bucket

Classifying GSoC Organizations using Python

Making sense out of the fast data and stream processing conundrum

Resource monitoring using Grafana + Telegraf (for Windows)

Building a REST API in R with TDD, Docker and CI/CD — Part 3/3

Learning Python is like learning a spoken language — I thought.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Gonzalo Fernandez Plaza

Gonzalo Fernandez Plaza

Computer Science Engineer & Tech Lead @ThePowerMBA 🖥️. Data enthusiast 📊. Publishing AWS & SnowFlake ❄️ courses. https://plazagonzalo.medium.com/membership

More from Medium

SnowFlake SnowPro Core Practice Exam

SnowFlake SnowPro Core Practice Exam

Roadmap To Complete Snowflake Snow Pro Certificate

Integrating Splunk Cloud Reports with Matillion ETL and Snowflake

How to Schedule a Snowflake task as depended task ?