Download Snowflake SnowPro Certification Study Guide and more Exams Computer Science in PDF only on Docsity!
Guide Rated A
What are the 3 Cloud Providers available for Snowflake - correct answer ✅1) AWS - a provider since inception
- Azure Cloud Platforms - generally available as of Sept. 2018
- Google Cloud Platform - trialed late 2019 Snowflake is currently the only DW built for the cloud (T/F) - correct answer ✅True How does Snowflake describe their architecture? A) Multi-Cluster, Shared Data B) Single-Cluster, Shared Data - correct answer ✅A) Multi- Cluster, Shared Data What are the 3 components of Snowflake's architecture (both the name and descriptor) - correct answer ✅1) Storage - Databases
- Compute - Virtual Warehouses
- Cloud Services - Front End What are the Cloud Object Storage options? - correct answer ✅1) AWS S
- Azure Blobs
- GCP How are storage costs calculated? - correct answer ✅A daily average of all compressed data storage, including data stored according to Time Travel retention policy and failsafe procedures
Guide Rated A
Which Microsoft and Amazon systems does Compute leverage? - correct answer ✅1) AWS EC
- Azure Compute Compute can't scale out as queries are running (T/F) - correct answer ✅False Which services does Cloud Services provide? - correct answer ✅1) Security Management
- Infrastructure Management
- Metadata Management
- Query Optimization Are Snowflake's Storage (DB) and Compute (WH) layers A) Separate and Independent B) Together and Dependent - correct answer ✅A) Separate and Independent In addition to multiple Virtual Warehouses, Multi-Cluster means individual Virtual Warehouses can... - correct answer ✅Scale up or down automatically Multi-Cluster turn off clusters when activity slows down (T/F) - correct answer ✅True
Guide Rated A
A customer needs to have a preexisting account with AWS or Azure in order to have a Snowflake account (T/F) - correct answer ✅False What are Availability Zones? - correct answer ✅Data centers in certain regions How many availability zones is your data replicated in? - correct answer ✅3 data zones in a region What does it mean by "automatic" micro-partitions - correct answer ✅A partition scheme doesn't need to be defined up front. Snowflake determines and creates it. Data partitioning is automatically completed in the natural ingestion order (T/F) - correct answer ✅True New Partitions are based on physical properties (T/F) - correct answer ✅True How does Snowflake optimize storage of semi-structured data? - correct answer ✅Repeated elements within the strings How far back does the Time Travel protection go? - correct answer ✅90 days
Guide Rated A
Zero-Copy Cloning operates on more than just the metadata (T/F)
- correct answer ✅False - It's a metadata-only operation There's a difference in storage cost allocation between structured and semi-structured data (T/F) - correct answer ✅False - There's no difference A user can't be assigned multiple roles (T/F) - correct answer ✅False - A user can be assigned multiple roles If a user creates an object, what permissions do others have? - correct answer ✅All users with the role that created the object will have access to it, as well as the users and roles that the role that created the object role up to. Which encryption method is used? - correct answer ✅AES 256 Strong Encryption What are the three "higher editions" (stronger security) of Snowflake? - correct answer ✅1) Enterprise
- Enterprise for Sensitive Data
- Virtual Private Snowflake What security protections does Snowflake provide - correct answer ✅- Single Sign On through federated authentication
- Periodic Rekeying of encrypted data
- HIPPA Compliance
Guide Rated A
You can have multiple databases in one schema (T/F) - correct answer ✅False - Schemas exist within databases. You can have multiple schemas within one database though. What are sequences? - correct answer ✅Sequences are used to generate unique numbers across sessions and statements, including concurrent statements Virtual Warehouses have to be manually turned off (T/F) - correct answer ✅False - Auto-suspending can turn them off Auto-Suspension limits has to be determined when the Virtual Warehouse is configured (T/F) - correct answer ✅False - it can be changed after initial configuration How is compute billing charged (units of time) - correct answer ✅Compute is charged for a base 1 minute of start up and then each second following that What is the max size of VARIANT data in a column? - correct answer ✅16 MB compressed What is the difference in query impact between regular JSON types (strings and numbers) and non-native JSON types (timestamps) - correct answer ✅Regular JSON types are easier and quicker to query than Non-Native types
Guide Rated A
What are the 4 mains sections of the Snowflake UI - correct answer ✅1) Databases - Storage
- Warehouses - Compute
- Worksheets
- History (ACCOUNTADMIN sees the account area) What are Snowflake connectivity options? - correct answer ✅1) SnowSQL
- ODBC
- JDBC
- Python
- Node.JS What are the two commands for loading data? How do they differ?
- correct answer ✅1) Copy - Bulk insert and more performant
- Insert - Row-by-row insert and less performant In a Copy function, you can insert on a SELECT against a staged file, but can't use a WHERE clause (T/F) - correct answer ✅True In an INSERT Function, you can insert on a SELECT against a staged file, but can't use a WHERE clause (T/F) - correct answer ✅False - You can use the where clause
Guide Rated A
Once data is loaded, it's compressed and converted automatically (T/F) - correct answer ✅True Where does Snowflake store the various information/statistics regarding database, tables, columns, and files? - correct answer ✅Metadata Manager in the Cloud Services Layer Can a cached query be run without a Virtual Warehouse running? - correct answer ✅Yes - No compute is used in a query that's cached in the results cache What is the Query Profile used for? - correct answer ✅Analyze the execution details of a query What are the most common kinds of technology partner? - correct answer ✅Data Integration and Business Intelligence technology partners What advantages does Snowflake have over On-Premise EDWs? - correct answer ✅1) Instant Scalability
- Separation of compute and storage
- No need for data distribution What advantages does Snowflake have over other Cloud EDWs? - correct answer ✅1) Concurrency
- Automatic failover and disaster recovery
- Built for the cloud
Guide Rated A
What advantages does Snowflake have over Hadoop? - correct answer ✅1) No hardware to manage
- Automatic failover and disaster recovery
- Native SQL (include on semi-structured) What advantages does Snowflake have over Data Engines - correct answer ✅1) No need to manage data files
- Automated cluster management
- Native SQL What advantages does Snowflake have over Apache Spark? - correct answer ✅1) No need to manage data files
- Automated cluster management
- Full SQL Support What are the best practices for data modeling in Snowflake? - correct answer ✅1) 3NF
- Data Vault
- Star Schema / Snowflake Schema What is the only constraint that Snowflake enforces? - correct answer ✅NOT NULL Which main constraints aren't enforced by Snowflake? - correct answer ✅1) Referential Integrity
Guide Rated A
What are two requirements for Clustering Keys to improve performance? - correct answer ✅1) Table must be large enough to reside upon many micro-partitions
- Clustering Keys have to provide significant filtering What are some of the indicators that Clustering Keys are beneficial? - correct answer ✅1) Queries on the table are running slower than expected or have noticeably degraded over time
- The clustering ratio for the table is very low and the clustering depth is very large Micro-Partitions aren't immutable (T/F) - correct answer ✅False - Once they've been written, they will never be changed or overwritten. Rather, subsequent changes of any type to the data will be written to additional micro-partitions What are two ways to effectively and efficiently use Virtual Warehouses? - correct answer ✅1) Experiment with different types of queries and sizes
- Don't focus on warehouse size since they can be suspended when not in use What can Resource Monitors do? - correct answer ✅They can impose limits on the number of credits Warehouses consume in order to help control costs and avoid unexpected credit usage
Guide Rated A
Resource Monitors can only send notifications. They can't suspend actions. (T/F) - correct answer ✅False - They can send notifications and suspend Resource Monitors can only be created by ACCOUNTADMINs (T/F) - correct answer ✅True Resource Monitors can only be viewed and modified by ACCOUNTADMINs (T/F) - correct answer ✅False - Enabled users with other roles can view and modify Resource Monitors Which role has the highest permissions? - correct answer ✅ACCOUNTADMIN What's the minimum recommended number of ACCOUNTADMINs?
- correct answer ✅ 2 Which account level is recommended to own a majority of the objects? - correct answer ✅SYSADMIN Snowflake can run within a customer's VPC (T/F) - correct answer ✅False - Snowflake can't run in a VPC Which transformations are available during COPY or INSERT functions - correct answer ✅1) Column Reodering
- Column Omission
- Casts
Guide Rated A
2) BZIP
- Deflate
- Raw_Deflate What file compression methods can Snowflake not automatically detect? - correct answer ✅1) Brotli
- Zstandard When creating a Warehouse, what's the difference between size and clusters? - correct answer ✅The size details the number of servers in each cluster The clusters is the max/min numbers the warehouse can scale up/down to What's the shortest amount of time that can pass before a Warehouse can auto-suspend? - correct answer ✅5 minutes What is the url naming convention for AWS? - correct answer ✅https://account_name.region.snowflakecomputing.com What is the url naming convention for Azure? - correct answer ✅https://account_name.region.azure.snowflakecomputing.com What is the url naming convention for GCP? - correct answer ✅https://account_name.region.gcp.snowflakecomputing.com
Guide Rated A
What two architectural types is Snowflake a hybrid of? - correct answer ✅1) Shared-Disk DB
- Shared-Nothing DB What compression type does Snowflake automatically use to compress newly loaded files? - correct answer ✅Gzip What is the auto encryption for newly loaded files? - correct answer ✅128-bit keys Which command removes the outer array structure when loading JSON files? - correct answer ✅STRIP_OUTER_ARRAY Files uploaded to Snowflake Staging Areas aren't automatically encrypted (T/F) - correct answer ✅False - They're automatically encrypted with 128-bit or 256-bit keys What's the max amount of files that can be uploaded at once? - correct answer ✅1000 files Which command explodes compounded VARIANT values into multiple rows? - correct answer ✅FLATTEN What are the 3 types of internal stages - correct answer ✅1) User Stage
- Table Stage
- Internal Named Stage
Guide Rated A
When unloading data, which compression methods are allowed? - correct answer ✅1) Gzip
- Bzip
- Brotoli
- Zstandard What's the max file size default when unloading data? - correct answer ✅16 MB What's the max # of server clusters in a virtual warehouse? - correct answer ✅ 10 What are the 4 types of tables? - correct answer ✅1) Temporary Tables
- Transient Tables
- Permanent Tables
- External Tables When is the data from a Temporary Table purged? - correct answer ✅After the session ends Temporary Tables aren't visible to other users (T/F) - correct answer ✅True
Guide Rated A
Temporary Tables can't have the same names as other non-temp tables within a DB/Schema (T/F) - correct answer ✅False - They can have the same name When is the data in a Transient Table purged? - correct answer ✅It stays until it's dropped What's the difference between a Transient Table and a Permanent Table? - correct answer ✅Transient Tables have no Fail-Safe mode What's the difference between Non-Materialized and Materialized views? - correct answer ✅Results are stored in materialized views. This leads to faster performance and contributes towards storage costs. Which command displays all the files in a stage? - correct answer ✅List What does the FORCE command do when using the COPY INTO statement? - correct answer ✅It forces all files in a stage to load into snowflake, regardless of whether or not they've been loaded before. This can result in duplicate data What's the command to allow a share to have access to databases and views? - correct answer ✅GRANT USAGE
