SnowPro Certification Study Guide with
Complete Solutions
What is the best way to store semi-structured data? - ANSWER - Data should be loaded and
stored in a VARIANT type column
What is the best practice for handling semi-structured data with 3rd party BI tools? - ANSWER -
Create a Snowflake view that parse the semi-structured column into structure columns for the BI
tool to retrieve
True or False: Micro-partitions are immutable? - ANSWER - True - subsequent changes of any
type to the data will be written to additional micro-partitions
A Snowflake mechanism that is used to limit the number of micro-partitions scanned by a query
is called ________? - ANSWER - Pruning
What command is used to load files into an Internal Stage within Snowflake?
A. PUT
B. COPY INTO
C. TRANSFER
D. INSERT - ANSWER - A. PUT
When loading data into Snowflake, the COPY command supports:
Choose 2 answers
A. Joins
B. Fitters
C. Data type conversions
D. Column reordering
E. Aggregates - ANSWER - C. Data Type Conversion & D. Column Reordering
,True or False: Snowpipe via RFST API can only reference External Stages as source. -
ANSWER - A. True
Which of the following are types of caching used by SF?
A. Warehouse Caching
B. Index Caching
C. Metadata Caching
D. Query result Caching - ANSWER - A, C, & D
Which of the following is not a layer in SF's Architecture?
Storage
Cloud Services
Data Catalog
Virtual Warehouses - ANSWER - Data Catalog
Which of the following techniques can be in SF to improve query performance?
A. Index Definition
B. Cluster Keys/Reclustering
C. Distribution Keys
D. Query Hints - ANSWER - B
True or False: SF offers tools to extract data from source systems. - ANSWER - False
On which of the following cloud platform can a Snowflake account be hosted?
Choose 2 answers
A. Amazon Web Services
B. Private Virtual Cloud
C. Oracle Cloud
,D. Microsoft Azure Cloud - ANSWER - A. Amazon Web Services & D. Microsoft Azure Cloud
How would you execute a series of SQL statements using Task?
A. include the SQL statements in the body of the task create task mytask.. as insert into target1
select., from stream_s1 where ..INSERT INTO target2 SELECT .. FROM stream .s1 where ..
B. A stored procedure can have only one DML statement per stored procedure invocation and
therefore you should sequence stored procedures calls in the task definition CREATE TASK
mytask.... AS call stored_prc1(); call stored_proc2t);
C. A stored procedure executing multiple SQL statements and invoke the stored procedure from
the task. CREATE TASK mytask.... AS callstored_proc_multiple_statements_inside():
D. Create a task for each SQL statement (e.g. resulting in task1. task2, etc) and string the series
of SQL statements by having a control taskcalling task 1, task 2 sequentially. - ANSWER - C
True or False: Snowflake charges a premium for storing semi-structured data. - ANSWER -
False
What are the three things customers want most from their enterprise data warehouse solution?
Choose 3 answers
A. On-premise availability
B. Simplicity
C. Open source based
D. Concurrency
E. Performance - ANSWER - B, D, E
Which of the following are true of multi-cluster Warehouses? Select all that apply below.
A. A multi-cluster Warehouse can add clusters automatically based on query activity
B. A multi-cluster Warehouse can automatically turn itself off after a period of inactivity
C. A multi-cluster Warehouse can scale down when query activity slows
D. A multi-cluster Warehouse can automatically turn itself on when a query is executed against it
- ANSWER - A, B, C, D
, True or False: A customer using SnowSQL / native connectors will be unable to also use the
Snowflake Web interface (UI) unless access to the UI is explicitly granted by supported. -
ANSWER - False
Which of the following DML commands isn't supported by Snowflake?
A. UPSERT
B. MERGE
C. UPDATE
D. TRUNCATE TABLE - ANSWER - A
Which type of data integration tools leverage SF's scalable compute for data transformation?
A. Data Replication
B. ELT
C. ETL
D. Streaming - ANSWER - B
Select all of the answers that describe SF micro-partitions. Micro-partitions
A. Are the physical data files that comprise SF's logical tables
B. Are written to a customer configured cloud storage location
C. Will be updated as their row & column values are updated
D. Enable horizontal and vertical query pruning - ANSWER - A & D
Which of the following are TRUE about SF data sharing?
A. Consumers can query shared tables in the same query as their own tables
B. Producers can share data to other databases such as Postgres and MySQL
C. You don't need a SF virtual WH to query shared data
Complete Solutions
What is the best way to store semi-structured data? - ANSWER - Data should be loaded and
stored in a VARIANT type column
What is the best practice for handling semi-structured data with 3rd party BI tools? - ANSWER -
Create a Snowflake view that parse the semi-structured column into structure columns for the BI
tool to retrieve
True or False: Micro-partitions are immutable? - ANSWER - True - subsequent changes of any
type to the data will be written to additional micro-partitions
A Snowflake mechanism that is used to limit the number of micro-partitions scanned by a query
is called ________? - ANSWER - Pruning
What command is used to load files into an Internal Stage within Snowflake?
A. PUT
B. COPY INTO
C. TRANSFER
D. INSERT - ANSWER - A. PUT
When loading data into Snowflake, the COPY command supports:
Choose 2 answers
A. Joins
B. Fitters
C. Data type conversions
D. Column reordering
E. Aggregates - ANSWER - C. Data Type Conversion & D. Column Reordering
,True or False: Snowpipe via RFST API can only reference External Stages as source. -
ANSWER - A. True
Which of the following are types of caching used by SF?
A. Warehouse Caching
B. Index Caching
C. Metadata Caching
D. Query result Caching - ANSWER - A, C, & D
Which of the following is not a layer in SF's Architecture?
Storage
Cloud Services
Data Catalog
Virtual Warehouses - ANSWER - Data Catalog
Which of the following techniques can be in SF to improve query performance?
A. Index Definition
B. Cluster Keys/Reclustering
C. Distribution Keys
D. Query Hints - ANSWER - B
True or False: SF offers tools to extract data from source systems. - ANSWER - False
On which of the following cloud platform can a Snowflake account be hosted?
Choose 2 answers
A. Amazon Web Services
B. Private Virtual Cloud
C. Oracle Cloud
,D. Microsoft Azure Cloud - ANSWER - A. Amazon Web Services & D. Microsoft Azure Cloud
How would you execute a series of SQL statements using Task?
A. include the SQL statements in the body of the task create task mytask.. as insert into target1
select., from stream_s1 where ..INSERT INTO target2 SELECT .. FROM stream .s1 where ..
B. A stored procedure can have only one DML statement per stored procedure invocation and
therefore you should sequence stored procedures calls in the task definition CREATE TASK
mytask.... AS call stored_prc1(); call stored_proc2t);
C. A stored procedure executing multiple SQL statements and invoke the stored procedure from
the task. CREATE TASK mytask.... AS callstored_proc_multiple_statements_inside():
D. Create a task for each SQL statement (e.g. resulting in task1. task2, etc) and string the series
of SQL statements by having a control taskcalling task 1, task 2 sequentially. - ANSWER - C
True or False: Snowflake charges a premium for storing semi-structured data. - ANSWER -
False
What are the three things customers want most from their enterprise data warehouse solution?
Choose 3 answers
A. On-premise availability
B. Simplicity
C. Open source based
D. Concurrency
E. Performance - ANSWER - B, D, E
Which of the following are true of multi-cluster Warehouses? Select all that apply below.
A. A multi-cluster Warehouse can add clusters automatically based on query activity
B. A multi-cluster Warehouse can automatically turn itself off after a period of inactivity
C. A multi-cluster Warehouse can scale down when query activity slows
D. A multi-cluster Warehouse can automatically turn itself on when a query is executed against it
- ANSWER - A, B, C, D
, True or False: A customer using SnowSQL / native connectors will be unable to also use the
Snowflake Web interface (UI) unless access to the UI is explicitly granted by supported. -
ANSWER - False
Which of the following DML commands isn't supported by Snowflake?
A. UPSERT
B. MERGE
C. UPDATE
D. TRUNCATE TABLE - ANSWER - A
Which type of data integration tools leverage SF's scalable compute for data transformation?
A. Data Replication
B. ELT
C. ETL
D. Streaming - ANSWER - B
Select all of the answers that describe SF micro-partitions. Micro-partitions
A. Are the physical data files that comprise SF's logical tables
B. Are written to a customer configured cloud storage location
C. Will be updated as their row & column values are updated
D. Enable horizontal and vertical query pruning - ANSWER - A & D
Which of the following are TRUE about SF data sharing?
A. Consumers can query shared tables in the same query as their own tables
B. Producers can share data to other databases such as Postgres and MySQL
C. You don't need a SF virtual WH to query shared data
