SnowPro Certification Study Guide – Complete
What is the best way to store semi-structured data? ✔️Ans - 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? ✔️Ans - 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? ✔️Ans - 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 ________? ✔️Ans - Pruning
What command is used to load files into an Internal Stage within Snowflake?
A. PUT
B. COPY INTO
C. TRANSFER
D. INSERT ✔️Ans - 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 ✔️Ans - C. Data Type Conversion & D. Column Reordering
True or False: Snowpipe via RFST API can only reference External Stages as
source. ✔️Ans - A. True
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 ✔️Ans - 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. ✔️Ans - C
True or False: Snowflake charges a premium for storing semi-structured data.
✔️Ans - 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 ✔️Ans - 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 ✔️Ans - 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. ✔️Ans - False
Which of the following DML commands isn't supported by Snowflake?
A. UPSERT
B. MERGE
C. UPDATE
D. TRUNCATE TABLE ✔️Ans - A
Which type of data integration tools leverage SF's scalable compute for data
transformation?
A. Data Replication
B. ELT
C. ETL
D. Streaming ✔️Ans - 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 ✔️Ans - 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
D. Data sharing is integrated with role-based access control ✔️Ans - A & D
True or False: When data is staged to a SF internal staging area using the PUT
command, the data is encrypted on the client's machine. ✔️Ans - 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 ✔️Ans - A, C, & D
Which of the following is not a layer in SF's Architecture?
Storage
Cloud Services
Data Catalog
Virtual Warehouses ✔️Ans - 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 ✔️Ans - B
True or False: SF offers tools to extract data from source systems. ✔️Ans -
False
Which of the following are options when creating a Virtual Warehouse?
A. Auto-suspend
B. Storage size
C. Auto-resume
D. Server Count ✔️Ans - A & C
True or False: The warehouse cache may be reset if a running warehouse is
suspended and then resumed. ✔️Ans - A
Which of the following best describes SF's processing engine?
Leverages Apache Spark
Based on Map Reduce
What is the best way to store semi-structured data? ✔️Ans - 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? ✔️Ans - 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? ✔️Ans - 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 ________? ✔️Ans - Pruning
What command is used to load files into an Internal Stage within Snowflake?
A. PUT
B. COPY INTO
C. TRANSFER
D. INSERT ✔️Ans - 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 ✔️Ans - C. Data Type Conversion & D. Column Reordering
True or False: Snowpipe via RFST API can only reference External Stages as
source. ✔️Ans - A. True
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 ✔️Ans - 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. ✔️Ans - C
True or False: Snowflake charges a premium for storing semi-structured data.
✔️Ans - 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 ✔️Ans - 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 ✔️Ans - 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. ✔️Ans - False
Which of the following DML commands isn't supported by Snowflake?
A. UPSERT
B. MERGE
C. UPDATE
D. TRUNCATE TABLE ✔️Ans - A
Which type of data integration tools leverage SF's scalable compute for data
transformation?
A. Data Replication
B. ELT
C. ETL
D. Streaming ✔️Ans - 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 ✔️Ans - 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
D. Data sharing is integrated with role-based access control ✔️Ans - A & D
True or False: When data is staged to a SF internal staging area using the PUT
command, the data is encrypted on the client's machine. ✔️Ans - 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 ✔️Ans - A, C, & D
Which of the following is not a layer in SF's Architecture?
Storage
Cloud Services
Data Catalog
Virtual Warehouses ✔️Ans - 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 ✔️Ans - B
True or False: SF offers tools to extract data from source systems. ✔️Ans -
False
Which of the following are options when creating a Virtual Warehouse?
A. Auto-suspend
B. Storage size
C. Auto-resume
D. Server Count ✔️Ans - A & C
True or False: The warehouse cache may be reset if a running warehouse is
suspended and then resumed. ✔️Ans - A
Which of the following best describes SF's processing engine?
Leverages Apache Spark
Based on Map Reduce
