What are some of the advantages of using a local storage method? select all correct answers.
The SnowPro ™ Core Certification tests your core expertise implementing and migrating to Snowflake. As a certified professional you can design, develop and manage scalable and secure Snowflake solutions to drive business objectives. Show
This exam covers architectural principles, core aspects and best practices of Snowflake solutions covering Data Loading/Unloading, Performance, concurrency, scaling, data types, connectors, security and Account Management. SnowFlake SnowPro Core Exam is always highly recommended to go through practice exams/practice questions to make yourself familiar with the real exam pattern. Whizlabs offers a great set of practice questions for this certification exam This set consists of 50 questions across all the domains in a SnowFlake SnowPro Core certification. It will give you a clear idea about the type of questions that are asked in the real exam. SnowFlake SnowPro Core Certification Free Questions Please find the below set as a free exam exercise that might help you to understand the exam pattern. Account and SecurityQ 1. You are the owner of a table T1 which is in schema S1. The schema is in database D1. In order to grant read-only permissions of this table to a newly created role R1, you will need to…(select all that apply)A. Grant ‘USAGE’ on database D1 B. Grant ‘SELECT’ on database D1 C. Grant ‘USAGE’ on schema S1 D. Grant ‘SELECT’ on schema S1 E. Grant ‘USAGE’ on table T1 F. Grant ‘SELECT’ on table T1 Correct Answers: A, C, and F To allow a role to use your database objects, you, as the owner must grant appropriate privileges on the database, schema, and objects. First up, you will need to grant USAGE privilege on the parent database and schema. USAGE privilege enables the grantee to refer to the database and schema in the query. Then, you will need to grant SELECT privilege on the table. The SQL statements are given below: Grant usage on the database: grant usage on database D1 to role R1; Grant usage on the schema: grant usage on schema D1.S1to role R1; Grant the ability to query an existing table: grant select on table D1.S1.T1 to role R1; Q 2. Identify system-defined roles in Snowflake from the roles given below. (select four)A. AccountAdmin B. SysAdmin C. Auditor D. RoleAdmin E. SecurityAdmin F. Public Correct Answers: A, B, E, and F A role in snowflake is essentially a container of privileges on objects. Roles are assigned to users to allow them to perform actions on the objects. A role can be directly assigned to the user, or a role can be assigned to a different role leading to the creation of role hierarchies. The role at a higher level in the hierarchy inherits the privileges of the role at a lower level in the hierarchy. Snowflake defined 5 system-defined roles. These roles, the key features, and the hierarchy of system-defined roles are shown in the diagram below. account admin snowflakeSnowflake Virtual WarehousesQ 3. What are the minimum and the maximum number of clusters in a multi-cluster warehouse?
|
Table stage | User Stage | Named stage | |
Purpose | This is a table-specific stage. Automatically available for every Snowflake table. Can copy data into that specific table only. Can be used by any user. | This is a user-specific stage. Automatically available for every user. Can copy data into any table to which the user has access. One user cannot access another user’s stage. | Most flexible of the three. Can copy data into any table and can be used by any user having the privileges to access that stage. |
Name | Has the same name as the table name. | Has the same name as the user name | first-class database objects and can have any user-defined name |
Referenced as | @% |
@~ |
@ |
Data transformations | Not supported | Not supported | Supported |
Web UI | Cannot be seen in the Stages tab of Web UI | Cannot be seen in the Stages tab of Web UI | Can be seen in the Stages tab of Web UI |
Further Reading: Choosing a Stage for Local Files — Snowflake Documentation, Copying Data from an Internal Stage — Snowflake Documentation
Domain : Data loading/unloading
Q37 : While loading data into Snowflake, which of the following transformations are supported? (select all that apply)
A. Column reordering
B. Column omission
C. Derived columns
D. Cast operation
E. Truncation
F. Transpose
Correct Answers: A, B, D and E
Explanation
Snowflake supports 4 types of simple transformations while doing data loading.
The supported transformations are – reordering of columns, omission of columns, data type conversion (also called casting or typecasting) and truncating text strings that exceed the length in the target column. Derived columns and transpose or any other complex data transformation operations are not supported while data loading.
Domain : Data loading/unloading
Q38 : It is NOT possible to query data stored in the cloud in Snowflake without first loading data into a Snowflake table.
A. True
B. False
Correct Answer: B
Explanation
The above statement is false. Snowflake supports direct querying of data stored in a cloud location.
Imagine a large organization that has invested time and energy to build a data lake on the Azure cloud (or Amazon cloud or GCP). The data lake contains petabytes of data accumulated over many years across multiple channels. Now, the organization wants to query a portion of this data to derive useful insights without reloading data in Snowflake. For these kind of scenarios, Snowflake provides a feature called ‘External Tables’.
An external table in Snowflake references to your data files in external cloud storage (e.g., data lake). External tables store file-level metadata about the data files in the data lake, such as the filename, a version identifier and related properties. Now when you query an external table, Snowflake executes that query against the data stored in external cloud storage, as if it were inside a database.
➤ Practical Info – When you query an external table, it may appear as if data is being fetched from the database. But in reality, the data is fetched directly from external cloud storage every time you run a query on an external table. Therefore the source of truth for the data is always the external cloud store.
Further Reading: Overview of Data Loading — Snowflake Documentation
Domain : Snowflake Overview and Architecture
Q39 : From the statements given below, select the statements which are TRUE for reader accounts. (select all that apply)
A. A reader account facilitates data sharing with consumers that do not have a Snowflake account
B. One reader account can be used to access multiple shares provided by different provider accounts
C. A reader account consumes credit of the provider account
D. A reader account has the same access to Snowflake support as the provider account
Correct Answers: A and C
Explanation
Answer choice-A is correct. If consumers of your share do not have a Snowflake account (e.g., a consumer using other database products such as Oracle or PostgreSQL), Snowflake provides a functionality called ‘reader account’ which can be used to access the share. A reader is a special Snowflake account created by the provider of the share for the sole purpose of providing access to the share to a consumer. The provider fully manages the reader accounts.
Answer choice-B is incorrect. Each reader account belongs to the provider account that created it. This means that if you need to access multiple shares from different providers, you will need to have multiple reader accounts – one reader account per provider.
Answer choice-C is correct. A reader account will require a warehouse to execute queries on the data shared with it. As the reader account is not a licensed user of Snowflake, the warehouse needs to be provisioned by the provider account. Consequently, the provider will be charged for the credits consumed by the queries executed by the reader accounts it has created.
Answer choice-D is incorrect. The reader account does not have access to Snowflake support. The provider that creates and manages the reader account will be able to route questions to Snowflake support on behalf of the reader account.
➤ Practical Info: Technically, a reader account can consume an unlimited number of credits, which will be charged to the provider account. Therefore, to monitor and control the credit consumption by the reader accounts, the provider account should create resource monitors.
Further Reading: Managing reader accounts
Domain : Snowflake Storage and Protection
Q40 : Match the appropriate data protection feature given on the right with the scenario given on the left. (Assume Business-critical edition of Snowflake)
Scenario | Data Protection Feature | |
S1
S2 S3 S4 S5 |
The latest release rolled out today morning has corrupted data in the production environment.
A data issue in the production database was discovered 3 days after time travel has expired. Snowflake instance has crashed, but the cloud region is available. One availability zone in the cloud region where Snowflake was hosted has failed. The entire cloud region where Snowflake was hosted has failed (for example, due to some natural calamity or network outage) |
|
A. S1 – 2, S2 – 3, S3 – 5, S4 – 1, S5 – 4
B. S1 – 3, S2 – 2, S3 – 1, S4 – 5, S5 – 4
C. S1 – 4, S2 – 1, S3 – 5, S4 – 4, S5 – 3
D. S1 – 3, S2 – 1, S3 – 2, S4 – 5, S5 – 4
Correct Answer: D
Explanation
S1 – Latest release rolled out today morning has corrupted data in the production environment. Answer: Time-Travel As data got corrupted in the morning, you have time to recover this data yourself by using the Snowflake time-travel feature. Time-travel is available for every Snowflake edition. For the Standard Edition, you have 1 day (24 hours) of default time travel. For Enterprise edition and above, you get up to 90 days of time travel.
S2 – A data issue in the production database was discovered 3 days after time travel has expired
As it is given in this scenario that the time-travel window has expired, you can rely on the fail-safe feature of Snowflake and retrieve the older version of your data. The fail-safe period is 7 days after the expiry of time travel. Answer: Fail-safe
S3 – Snowflake instance has crashed, but the cloud region is available.
This scenario indicates that the Snowflake instance has crashed, but the underlying cloud provider/region is up. Therefore this is a Snowflake-specific problem. Snowflake provides a triple redundancy feature that will get activated in such scenarios to mitigate the impact on client workloads. This is completely transparent to the end-users. Answer: Triple Redundancy
S4 – One availability zone in the cloud region where Snowflake was hosted has failed.
Snowflake provides failover protection across three availability zones of your cloud provider region. Your data is synchronously replicated across the three availability zones. Therefore when your primary availability zone fails, the workload is automatically and transparently moved to one of the other availability zones. Answer: Automatic AZ Fail-over
S5 – The entire cloud region where Snowflake was hosted has failed.
To ensure resiliency in this scenario, You will use the ‘Database replication and failover/failback’ feature available in the Business Critical edition. This feature allows you to create a secondary database in another cloud provider/region. The secondary database is automatically synced with the primary database. In case of a cloud region failure, you can promote your secondary database to serve as the primary database and continue to process data. Answer: Database replication
Further Reading: How Snowflake protects your data and services
Domain : Performance and Tuning
Q41 : Select the statements that apply to clustering depth in a micro-partition. (select all that apply)
A. Clustering depth is the average depth of overlapping micro-partitions for a specified set of columns
B. The lower the clustering depth, the better clustered the table is
C. The higher the clustering depth, the better clustered the table is
D. Is an indication of whether the table needs an explicit clustering key
Correct Answers: A, B and D
Explanation
Answer choice-A is correct. Clustering depth is the average depth of overlapping micro-partitions. Clustering depth indicates how well the data is dispersed across micro-partitions.
Answer choice-B is correct and Answer choice-C is incorrect. The ideal clustering depth is 1 and it indicates that no micro-partitions overlap for a given set of columns. Therefore, when a search query is executed, Snowflake can directly find the right micro-partition. Any number greater than 1 indicates that when a search query is executed, Snowflake will find several micro-partitions and it will need to process these micro-partitions to prepare the resultset. For an empty table, the clustering depth is 0.
Answer choice-D is correct. In general, Snowflake organizes your data in micro-partitions in such a way that clustering depth is low. However, over time, as insert/updates are performed on the data, the data may no longer stay clustered optimally and clustering depth becomes large. This is specifically true for very large tables. When this happens, the search query performance becomes slower over time.
To improve the clustering of the data in micro-partitions, Snowflake allows you to designate one or more table columns/expressions as an explicit clustering key for the table. A clustering key co-locates rows in the same micro-partitions thereby improving search efficiency. Hence, high clustering depth indicates that the queries on that table may benefit by defining an explicit clustering key.
Further Reading: Clustering depth illustration – Snowflake documentation
Domain : Snowflake Virtual Warehouses
Q42 : Select statement(s) which are TRUE from the given set of statements concerning Snowflake virtual warehouses. (select all that apply)
A. Scaling up a virtual warehouse is an automatic process
B. Scaling up a virtual warehouse is a manual process
C. Scaling down a virtual warehouse is an automatic process
D. Scaling down a virtual warehouse is a manual process
E. Scaling out a virtual warehouse is an automatic process
F. Scaling out a virtual warehouse is a manual process
G. Scaling back a virtual warehouse is an automatic process
H. Scaling back a virtual warehouse is a manual process
Correct Answers: B, D, E and G
Explanation
Scaling up or scaling down a virtual warehouse is also known as warehouse re-sizing. When you re-size a warehouse, you change the T-shirt size of the warehouse (e.g. from S to L or from XL to M). This is a manual activity. You may want to increase the T-shirt size of your warehouse (i.e. scale up) if your query performance or data loading performance is slow. You will want to decrease the T-shirt size of your warehouse (i.e. scale down) if your query workload is low.
Scaling out or scaling back a virtual warehouse is adding or removing new warehouse clusters (without regard to the size of the warehouse). This is an automatic process managed transparently by Snowflake. Snowflake will automatically increase the number of warehouse clusters as the number of queries increases to prevent queries from queuing. When Snowflake determines that additional clusters are no longer needed, it will shut them down. In summary, Snowflake ensures that a multi-cluster warehouse dynamically adapts to increase or decrease in the number of queries without any user intervention.
Summary:
Scaling model | How? | For? |
Scale-Up/Down | Manual | Query/ Data Loading Performance |
Scale-Out/In | Automatic | Query concurrency/ |
Further Reading: Working with virtual warehouses in Snowflake – YouTube video
Domain : Account and Security
Q43 : In Snowflake, For each securable object, there is a set of privileges. The privileges provide fine-grained access control on the object. Each securable object has an owner that can assign these privileges directly to a user or a group of users.
A. True
B. False
Correct Answer: B
The above statement is false. This is because the owner of a securable object CANNOT grant privileges over the object directly to a user. The privileges must be assigned to roles. The roles can be granted to other roles creating a role hierarchy OR the role can be granted to a user or a group of users.
Further Reading: Access Control Framework – Snowflake Documentation
Domain : Snowflake Overview and Architecture
Q44 : Snowflake being a SaaS software, which of the following activities of an on-prem/hosted data warehouse are not required in Snowflake from the user’s perspective? (select all that apply)
A. Hardware sizing, purchase, or configuration
B. Hardware scaling
C. User and access management
D. Software installation, maintenance, or upgrades
E. Database Tuning
F. Site-level disaster recovery (due to loss of data center)
Correct Answers: A, B, D, E and F
Explanation
Snowflake is a true SaaS, cloud-native, data warehouse product. As it completely runs on public cloud infrastructure, it does not require end-users to purchase, configure or manage any on-prem hardware (answer choice A). Practically unlimited scalability available on tap means end users will not need to carry out costly and effort-intensive hardware scaling by themselves (answer choice B). All software maintenance, bug fixes, feature updates, etc. are rolled out completely by Snowflake without any end-user intervention (answer choice D). Database tuning Is no longer a task for the DBA in Snowflake as Snowflake does not support the creation of indexes, database partitions, (Answer choice-E). Your data is automatically replicated across three availability zones in the cloud region without any user intervention (Answer choice-F). Out of the given list of answer choices, only ‘User and access management is a user responsibility.
Further Reading: Snowflake data warehouse pros and cons – Snowflake community blog
Domain : Snowflake Storage and Protection
Q45 : Zero-copy cloning operation is supported for the following objects in Snowflake. (select all that apply)
A. A temporary Table
B. A transient Table
C. An external table
D. A permanent Table
E. A database created from a Share
F. Internal (named) Stage
Correct Answers: B and D
Explanation
- Zero-copy cloning is available only for the permanent and transient table types. It is not available for temporary and external table types.
- You cannot clone a database that is created from a Share.
- Also, zero-copy cloning is not available for stage objects.
Domain : Snowflake Virtual Warehouses
Q46 : A resource monitor can be created by…
A. Owner of the virtual warehouse
B. ACCOUNTADMIN role
C. SYSADMIN role
D. USERADMIN role
E. Any of the above
Correct Answer: B
Explanation
Resource monitors can only be created by account administrators (i.e. users with the ACCOUNTADMIN role). However, account administrators can choose to grant MONITOR and MODIFY privileges on resource monitors to the users with other roles as needed.
Further Reading: Access Control Privileges for Resource Monitors – Snowflake Documentation
Domain : Data loading/unloading
Q47 : You have a business-critical edition of Snowflake on AWS cloud. The data lake implementation of your organization is in Azure and utilizes Azure Data Lake Gen2 service. As your Snowflake account and your data lake are on different clouds, it is not possible to do bulk loading from Azure data lake into Snowflake using the COPY command.
A. True
B. False
Correct Answer: B
Explanation
This statement is not correct. Bulk loading using COPY INTO supports data loading into Snowflake from the data files on your local file system or in cloud storage external to Snowflake (Amazon S3, Microsoft Azure, or Google Cloud Storage) irrespective of where your Snowflake account is hosted.
Further Reading: Introduction to Data Loading – Youtube Video
Domain : Semi Structured data
Q48 : Consider a table vehicle_inventory that stores vehicle information of all vehicles in your dealership. The table has only one VARIANT column called vehicle_data which stores information in JSON format. The data is given below:
{
“date_of_arrival”: “2021-04-28”,
“supplier_name”: “Hillside Honda”,
“contact_person”: {
“name”: “Derek Larssen”,
“phone”: “8423459854”
},
“vehicle”: [
{
“make”: “Honda”,
“model”: “Civic”,
“variant”: “GLX”,
“year”: “2020”
}
]
}
Which of the following are valid SQL queries that retrieve supplier_name? (select all that apply)
A. select vehicle_data.supplier_name::string from vehicle_inventory
B. select vehicle_data:supplier_name::string from vehicle_inventory
C. select vehicle_data(supplier_name(string)) from vehicle_inventory
D. select vehicle_data(supplier_name’)::string from vehicle_inventory
Correct Answer: B
Explanation
In this example, supplier_name is the first level element in the JSON. To access this in SQL you will need to use the below syntax in the SQL if you are using . (dot) notation.
Therefore, the correct SQL statements would be –
select vehicle_data:supplier_name::string from vehicle_inventory
Further Reading: Querying Semi-Structured Data – Snowflake Documentation
Domain : Snowflake Virtual Warehouses
Q49 : Which of the following statements are true about multi-cluster Warehouses in Snowflake? (Select all that apply).
A. Multi-cluster warehouses support all properties of a single-cluster warehouse
B. Increasing the number of compute resources in a multi-cluster warehouse is an example of scaling up
C. A single cluster warehouse can be reconfigured to turn into a multi-cluster warehouse but vice-versa is not true
D. None of the above
Correct Answer: A
Explanation
Answer choice-A is correct. Multi-cluster warehouses support all the same properties and actions as single warehouses, including:
- Specifying warehouse size ( e.g. XS, S, M, L…)
- Resizing a warehouse at any time.
- Auto-suspending a running warehouse due to inactivity;
- Auto-resuming a suspended warehouse when new queries are submitted.
Answer choice-B is incorrect. Increasing the number of compute resources in a multi-cluster warehouse is an example of scaling out. Increasing the size of compute clusters is an example of scaling up.
Answer choice-C is incorrect. A single cluster warehouse can be reconfigured to turn into a multi-cluster warehouse. To do this, you will have to set the max_cluster_count to a value greater than 1 as shown in the example below:
ALTER WAREHOUSE “COMPUTE_WH” SET MAX_CLUSTER_COUNT = 3;
Similarly, a multi-cluster warehouse can be reconfigured to turn into a single cluster warehouse. To do this, you will have to set the max_cluster_count to a value equal to 1 as shown in the example below:
ALTER WAREHOUSE “COMPUTE_WH” SET MAX_CLUSTER_COUNT = 1;
Domain : Snowflake Overview and Architecture
Q50 : Using materialized views can be beneficial in the following scenarios. (select all that apply)
A. when you have a complex query that is frequently used
B. When the underlying data changes frequently
C. when the query results are smaller than the base table
D. When the query includes an external table
Correct Answers: A, C and D
Explanation
Answer choice-A is correct. Encapsulating a frequently used, complex query that normally takes a long time to execute into a materialized view can improve performance. This is because the query is executed automatically in the background and the computed result set is stored in the materialized view. Querying the view, then, becomes significantly faster compared to re-running the query.
Answer choice-B is incorrect. A background service in Snowflake refreshes the materialized view after changes are made to the base table. This service consumes credits. If the underlying data changes frequently, the background service has to be executed repeatedly which leads to higher credit consumption. Hence, it is not recommended to create materialized views on a base table that changes frequently.
Answer choice-C is correct. When the query results are smaller than the base table, the background service that refreshes the materialized view needs to compute results for a small set of records. This results in lower consumption of credits to keep the view up to date.
Answer choice-D is correct. Querying an external table is likely to be slower than querying native database tables as the data in an external table is stored in an external stage in the cloud (outside of Snowflake). Creating a materialized view brings the resultset locally within your Snowflake account thereby improving query performance.