Download Snowflake SnowPro Certification Study Guide Test With Complete Verified Solutions| Updated and more Exams Computer Science in PDF only on Docsity!
Snowflake SnowPro Certification
Study Guide Test
What are the two values for SCALING_POLICY when creating a virtual warehouse? - โ โ 1) Standard
- Economy Which of the two can be cloned? A) Internal Named Stages B) External Named Stages - โ โ B) External Named Stages When a database or schema is cloned, it also clones the contained snowpipes that reference an internal stage (T/F) - โ โ False - It doesn't clone snowpipes that reference internal stages, but it does clone ones that refernce external stages Can Virtual Warehouses be cloned? - โ โ No, they can't Can ACCOUNTADMINS view results of other users queries? - โ โ No, they can only see the queries they ran When creating a stage, the URL doesn't need the "https://" in front of the name (T/F) - โ โ True Which objects can you clone? - โ โ 1) Database Objects
- Individual external named stages
- Snowpipes
- Sequences
- Streams
- Tasks Can you specify time travel requirements when cloning? - โ โ Yes, this is done by suing the AT and BEFORE commands Can Transient tables be cloned? - โ โ Yes Reclustering has to be done manually in Snowflake (T/F) - โ โ False
- Reclustering is automatic, no maintenance is needed Which user types can see the account button? - โ โ 1) ACCOUNTADMIN
- SECURITYADMIN Data replication only needs to happen once per cloud/region (T/F) - โ โ True - Once it's been replicated to that cloud/region, it can be shared as many times as needed What is a maximized Virtual Warheouse? What is an auto-scale Virtual Warehouse? - โ โ Maximized is where the max and min amount of clusters is the same value. This ensures control over the used resources. Auto-scale is where the max and min amount of clusters is different. Clustering Keys improve performance on all queries that filter or sort on that column (T/F) - โ โ True - This includes some joins A share can only contain one database (T/F) - โ โ False - A share can contain several databases, as long as they are in the same Snowflake account
When creating a warehouse, what unit of time is the value of the auto_suspend parameter in? - โ โ Seconds Which command allows users to see the query id of the second query executed in the current session? - โ โ SELECT last_query_id(2) Data Sharing Consumers must use the ACCOUNTADMIN role to create a database based on the share (T/F) - โ โ True Data Sharing Consumers can consume data residing from any region (T/F) - โ โ False - Consumer accounts must reside in the same Cloud Provider Region as the Provider account Providers can only share data with users who have accounts (T/F) - โ โ False - Providers can set up reader accounts for users who aren't snowflake customers What are schemas? Is there a limit to the objects contained in a schema? - โ โ Logical groupings of database objects, such as tables and views by concept or purpose. There's no limit to objects contained in a schema. You can have multiple databases in one schema (T/F) - โ โ False - Schemas exist within databases. You can have multiple schemas within one database though. What are sequences? - โ โ Sequences are used to generate unique numbers across sessions and statements, including concurrent statements Virtual Warehouses have to be manually turned off (T/F) - โ โ False - Auto-suspending can turn them off
Auto-Suspension limits has to be determined when the Virtual Warehouse is configured (T/F) - โ โ False - it can be changed after initial configuration How is compute billing charged (units of time) - โ โ 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? - โ โ 16 MB compressed What is the difference in query impact between regular JSON types (strings and numbers) and non-native JSON types (timestamps) - โ โ Regular JSON types are easier and quicker to query than Non-Native types What are the 4 mains sections of the Snowflake UI - โ โ 1) Databases - Storage
- Warehouses - Compute
- Worksheets
- History (ACCOUNTADMIN sees the account area) What are Snowflake connectivity options? - โ โ 1) SnowSQL
- ODBC
- JDBC
- Python
3) XML
- Avro
- Parquet
- ORC Data doesn't have to be staged prior to being loaded (T/F) - โ โ False - Data must be staged prior to loading Once data is loaded, it's compressed and converted automatically (T/F)
- โ โ True Where does Snowflake store the various information/statistics regarding database, tables, columns, and files? - โ โ Metadata Manager in the Cloud Services Layer Can a cached query be run without a Virtual Warehouse running? - โ โ Yes - No compute is used in a query that's cached in the results cache What is the Query Profile used for? - โ โ Analyze the execution details of a query What are the most common kinds of technology partner? - โ โ Data Integration and Business Intelligence technology partners What advantages does Snowflake have over On-Premise EDWs? - โ โ 1) Instant Scalability
- Separation of compute and storage
- No need for data distribution
What advantages does Snowflake have over other Cloud EDWs? - โ โ 1) Concurrency
- Automatic failover and disaster recovery
- Built for the cloud What advantages does Snowflake have over Hadoop? - โ โ 1) No hardware to manage
- Automatic failover and disaster recovery
- Native SQL (include on semi-structured) What advantages does Snowflake have over Data Engines - โ โ 1) No need to manage data files
- Automated cluster management
- Native SQL What advantages does Snowflake have over Apache Spark? - โ โ 1) No need to manage data files
- Automated cluster management
- Full SQL Support What are the best practices for data modeling in Snowflake? - โ โ
- 3NF
- Data Vault
- Star Schema / Snowflake Schema
What are two requirements for Clustering Keys to improve performance? - โ โ 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? - โ โ 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) - โ โ 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? - โ โ 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? - โ โ They can impose limits on the number of credits Warehouses consume in order to help control costs and avoid unexpected credit usage Resource Monitors can only send notifications. They can't suspend actions. (T/F) - โ โ False - They can send notifications and suspend
Resource Monitors can only be created by ACCOUNTADMINs (T/F) - โ โ True Resource Monitors can only be viewed and modified by ACCOUNTADMINs (T/F) - โ โ False - Enabled users with other roles can view and modify Resource Monitors Which role has the highest permissions? - โ โ ACCOUNTADMIN What's the minimum recommended number of ACCOUNTADMINs? - โ โ 2 Which account level is recommended to own a majority of the objects?
- โ โ SYSADMIN Snowflake can run within a customer's VPC (T/F) - โ โ False - Snowflake can't run in a VPC Which transformations are available during COPY or INSERT functions - โ โ 1) Column Reodering
- Column Omission
- Casts
- Truncating text strings that exceed the target length How is Snowflake's internal staging charged? - โ โ It incurs standard storage costs, but isn't subject to additional costs with Time Travel or Fail-Safe What extra transformation is available during a Snowpipe load? - โ โ Using snowpipes to load micro-batches into staging tables can allow for more transformations
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? - โ โ 5 minutes What is the url naming convention for AWS? - โ โ https://account_name.region.snowflakecomputing.com What is the url naming convention for Azure? - โ โ https://account_name.region.azure.snowflakecomputing.com What is the url naming convention for GCP? - โ โ https://account_name.region.gcp.snowflakecomputing.com What two architectural types is Snowflake a hybrid of? - โ โ 1) Shared-Disk DB
- Shared-Nothing DB What compression type does Snowflake automatically use to compress newly loaded files? - โ โ Gzip What is the auto encryption for newly loaded files? - โ โ 128-bit keys Which command removes the outer array structure when loading JSON files? - โ โ STRIP_OUTER_ARRAY Files uploaded to Snowflake Staging Areas aren't automatically encrypted (T/F) - โ โ 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? - โ โ 1000 files Which command explodes compounded VARIANT values into multiple rows? - โ โ FLATTEN What are the 3 types of internal stages - โ โ 1) User Stage
- Table Stage
- Internal Named Stage What things are important to know about User Stages? - โ โ 1) They can only be accessed by a single user
- They can't be altered or dropped
- You can't set a file format. It needs to be specified in the COPY command to table What things are important to know about Table Stages? - โ โ 1) They can be accessed by multiple users
- Can't be altered or dropped
- Can't set file format, need to specify in COPY command to table
- No transformation while loading What things are important to know about Internal Named Stages? - โ โ 1) They're database objects
- They can load data into any tables (needs user with privilege to do so)
- Ownership of stage can be transferred
Temporary Tables can't have the same names as other non-temp tables within a DB/Schema (T/F) - โ โ False - They can have the same name When is the data in a Transient Table purged? - โ โ It stays until it's dropped What's the difference between a Transient Table and a Permanent Table? - โ โ Transient Tables have no Fail-Safe mode What's the difference between Non-Materialized and Materialized views? - โ โ 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? - โ โ List What does the FORCE command do when using the COPY INTO statement? - โ โ 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? - โ โ GRANT USAGE What's the command to allow a share to have access to additional databases that contain objects referenced by the view you wish to share using GRANT ? - โ โ GRANT REFERENCE_USAGE Can you replicate data to other users who are on a different provider or region than you (AWS, Azure, GCP)? - โ โ Yes You can create a clone of shared database as a consumer (T/F) - โ โ False - You can't clone the database or any schemas/tables
How long does a Snowpipe store metadata? - โ โ 14 days What is quiesce mode? - โ โ The mode a server's in when it's waiting to shut down. A cloned object retains all granted privileges of the source object (T/F) - โ โ False - It doesn't retain any granted privileges A cloned database or schema's child objects retain privileges of the source objects (T/F) - โ โ True What are the 3 Cloud Providers available for Snowflake - โ โ 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) - โ โ True How does Snowflake describe their architecture? A) Multi-Cluster, Shared Data B) Single-Cluster, Shared Data - โ โ A) Multi-Cluster, Shared Data What are the 3 components of Snowflake's architecture (both the name and descriptor) - โ โ 1) Storage - Databases
- Compute - Virtual Warehouses
- Cloud Services - Front End What are the Cloud Object Storage options? - โ โ 1) AWS S
Snowflake uses ANSI-SQL (T/F) - โ โ True What is Snowflake's semi-structured data type called? - โ โ VARIANT What are the three types of caching in Snowflake? - โ โ 1) Warehouse
- Metadata
- Results All cache data are cleared if the underlying data changes (T/F) - โ โ True What does Warehouse Caching do? When is the data invalidated? Where does it live? - โ โ Stores data that's been loaded into Virtual Warehouses during querying. The data is invalidated if it's suspended. Lives in Compute. What does Metadata Caching do? Where does it live? - โ โ It stores various info about tables. It lives in the Cloud Services layer. What does Results Caching do? Where does it live? - โ โ It stores results of queries that have been executed within the past 24 hours (assuming underlying data hasn't changed). It lives in the Cloud Services Layer. A customer needs to have a preexisting account with AWS or Azure in order to have a Snowflake account (T/F) - โ โ False What are Availability Zones? - โ โ Data centers in certain regions
How many availability zones is your data replicated in? - โ โ 3 data zones in a region What does it mean by "automatic" micro-partitions - โ โ 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) - โ โ True New Partitions are based on physical properties (T/F) - โ โ True How does Snowflake optimize storage of semi-structured data? - โ โ Repeated elements within the strings How far back does the Time Travel protection go? - โ โ 90 days Zero-Copy Cloning operates on more than just the metadata (T/F) - โ โ False - It's a metadata-only operation There's a difference in storage cost allocation between structured and semi-structured data (T/F) - โ โ False - There's no difference A user can't be assigned multiple roles (T/F) - โ โ False - A user can be assigned multiple roles If a user creates an object, what permissions do others have? - โ โ 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? - โ โ AES 256 Strong Encryption
