Snowflake SnowPro Advanced Architect - Practice Exam 1

The SnowPro Advanced: Architect credential recognizes specific knowledge and skills related to the design, development, and implementation of Snowflake solutions. Passing this Certification showcases your ability to apply Snowflake best practices, advanced tuning, and real-world deployment scenarios.

What does a response error code of 429 from the insertFiles, insertReport, or loadHistoryScan API of Snowpipe mean?

What is the purpose of the SYSTEM$CLUSTERING_INFORMATION function?

What is the purpose of external functions in Snowflake?

When are materialized views NOT recommended?

Select the correct object hierarchy from the following options:

What is the meaning of “Percentage scanned from cache” in the query profiler menu when running a query?

Which scaling policies does Snowflake support for multi-cluster warehouses?

Does the VALIDATION_MODE support COPY statements that transform the data during a load?

When copying files, there might be errors. What are the options that you can specify in the COPY INTO clause?

Q10

Can we clone a temporary table into a permanent one?

Q11

What command can we use to delete a share from the Snowflake account?

Q12

How does Snowflake charge for the compute resources of the cloud services layer?

Q13

What is the function of the insertReport Snowpipe endpoint?

Q14

Can we create materialized views without any additional cost?

Q15

Which role can view account-level Credit and Storage Usage?

Q16

Can a user set a Column-level Security masking policy on a table or view column with the APPLY MASKING POLICY privilege?

Q17

Which command cannot we perform in a Shared database?

Q18

Why does Snowflake NOT recommend adding more than 3-4 columns into a cluster key?

Q19

What is the best option to clone a table called MYTABLE?

Q20

What is the definition of securable objects?

Q21

Does Snowflake implement row-level and column-level security policies?

Q22

What is the meaning of the "Bytes spilled to remote storage" of the query profiler?

Q23

Can we replicate databases across regions and cloud providers?

Q24

Which of the following objects cannot be replicated?

Q25

Does the VARIANT data type impose a 16MB size limit on individual rows?

Q26

Can we replicate a Database that has been created from a Share?

Q27

What is the meaning of “Local Disk IO” in the query profiler menu when running a query?

Q28

Does the Kafka connector create one pipe for each partition in a Kafka topic?

Q29

What Snowflake object executes code outside Snowflake, known as remote service?

Q30

Which command can we use to convert JSON NULL values to SQL NULL values?

Q31

What of these metrics is not a metric that appears in the Execution Time screen of the Query Profiler?

Q32

Person1 is using the role SECURITYADMIN. Person 1 creates a role named DBA_ROLE that will manage the warehouses in the Snowflake account. Person1 now needs to switch to that role. What command(s) need to be executed to switch the context of this worksheet?

Q33

Can an Object Owner grant object privileges in a regular schema?

Q34

Why does Snowflake recommend using the insertReport endpoint instead of the loadHistoryScan when using Snowpipe?

Q35

Can one masking policy be applied to multiple columns?

Q36

Which objects does the Kafka connector create for each topic?

Q37

What is Row-level Security in Snowflake?

Q38

What types of stages does Snowpipe support?

Q39

After performing the following query:

SELECT * 
FROM MYTABLE 
WHERE email='test@test.com'

you see in the query profiler the following information:

Can you spot the issue?

Q40

Can a Schema Owner grant object privileges in a managed access schema?

Q41

When we create a table using the following command:

CREATE TABLE USERS(
	USER_ID NUMBER, 
	USER_NAME VARCHAR
);

What datatype will Snowflake use for the USER_ID parameter?

Q42

What are the possible use-cases of using cross-cloud and cross-region replication?

Q43

What is the difference between the insertReport and the loadHistoryScan endpoints from Snowpipe?

Q44

Which statement is false about the following task?

CREATE TASK mytask1 
 	 WAREHOUSE = mywh 
 	 SCHEDULE = '5 minute' 
WHEN 
 	 SYSTEM$STREAM_HAS_DATA('ST1')
AS 
 	INSERT INTO mytable(id,nm)
	SELECT id, nm
	FROM st1 
	WHERE 
	METADATA$ACTION='INSERT';

Q45

How can we optimize the performance of the queries?

Q46

A Snowflake developer has created a masking policy with the following syntax:

create or replace masking policy mp
AS (val string) returns string ->
 CASE
 WHEN current_role() in('DEVROLE') 
  THEN val
 ELSE '*********'
END;

Which statements are correct about this policy?

Q47

Why does Snowflake not recommend using SELECT * in the definition of a materialized view?

Q48

Is it good practice to drop the Search Optimization Service before re-clustering the entire table and re-adding it after this process finishes?

Q49

Where can we use the command STATEMENT_TIMEOUT_IN_SECONDS?

Q50

What tables are we able to list using the command SHOW TABLES?

Q51

What Access Control Privileges do you need in order to add or remove the Search Optimization Service for a table? (SELECT TWO):

Q52

What does a successful response from the insertFiles Snowpipe endpoint mean?

Q53

Does Snowflake call the remote service directly when executing external functions?

Q54

When is the parameter INTEGRATION from Snowpipe required?

Q55

How can we add a clustering key to the existing table MYTABLE in the columns USER and CREATED_AT?

Q56

When will a multi-cluster warehouse start a new cluster if it’s running with the economy scaling policy?

Q57

A large join query takes around 3 hours to complete in an L warehouse. After increasing the warehouse size to XL one, the query's performance didn't improve. What can be the cause of it?

Q58

How many credits will consume a medium-size warehouse with 2 clusters running in maximized mode for 3 hours?

Q59

A department from our company will run complex aggregation queries to a small subset of data from a huge table that doesn’t change much. After running these queries, we realized that the queries take a lot of time because the table is not clustered on those columns. What is the most optimal solution that we should implement?

Q60

Which are the two limitations of the insertReport API of Snowpipe?