Snowflake SnowPro Advanced Architect - Practice Exam 2

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 is the meaning of “Processing” in the query profiler menu when running a query?

To which entity do we grant privileges?

Which command will you run to list all privileges granted to a role?

Are AWS Lambda and Microsoft Azure Functions examples of remote services in external functions?

Which command can we use to refresh a materialized view?

Which of the following REST endpoints does Snowpipe API provide?

Which of the following objects are NOT securable objects?

What is the output of the command

SELECT TOP 100 AGE 
FROM USERS;

You have a query that will take 20 minutes to be executed in a data warehouse. This warehouse auto-suspends after 15 minutes. What will happen with the query?

Q10

Which of these commands require a running warehouse?

Q11

Which types of tables typically benefit from creating a cluster key?

Q12

One query takes a lot of time, and you see in the query profiler the following information:

What might be the cause of this?

Q13

Which command will fail if you have a table created with the following DDL query?

CREATE TABLE MYTABLE 
	(ID INTEGER, NAME VARCHAR)

Q14

Which are the two VARIANT columns each schema has in every Snowflake table loaded by the Kafka connector?

Q15

When creating a table using the command:

CREATE TABLE MY_TABLE 
	(NAME STRING(100));

What would the command "DESC TABLE MY_TABLE;" display as the column type?

Q16

When will you use the maximized mode of a multi-cluster warehouse?

Q17

What are the supported values for the VALIDATION_MODE parameter while using the COPY INTO <TABLE> command?

Q18

Which of the following features is not supported by Snowpipe for data loading?

Q19

Can a Schema Owner grant object privileges in a regular schema?

Q20

Will this query cost compute credits?

CREATE OR REPLACE TABLE MYTABLE LIKE MY_TABLE

Q21

Which command can we use to drop pipes from the Kafka connector?

Q22

Can the definition of a materialized view contain an ORDER BY clause?

Q23

Will this query cost compute credits considering that the previous query ran 5 minutes ago?

SELECT * 
FROM TABLE(
    RESULT_SCAN(LAST_QUERY_ID())
);

Q24

What will happen to ALTER a column setting it to NOT NULL if it contains NULL values?

Q25

Two tables have been created in the same schema with the same information. One is a Secure View; the second one is a standard View. When executing a query, the profiler doesn’t show the same information. How is this possible?

Q26

Which of the following statements are the best practices for the ACCOUNTADMIN role?

Q27

Which of these Snowflake components/objects is NOT typically used in building continuous ELT pipelines?

Q28

Which command can we use to list all the object references of a view?

Q29

What types of objects does Snowflake return when we execute the function GET_OBJECT_REFERENCES?

Q30

Which parameter allows us to schedule the task_1 to run every day with a CRON expression?

Q31

Which command can we use to restore a dropped share?

Q32

We want to generate a JSON object with the data from a table called users_table, composed of two columns (AGE and NAME), ordered by the name column. How can we do it?

Q33

Which ALTER commands will impact a column's availability in Time Travel?

Q34

Which are the benefits of the Search Optimization Service?

Q35

What type of authentication does the Kafka connector use?

Q36

Which clusters from a multi-cluster warehouse will be resized if we resize the warehouse?

Q37

Can you specify more than one file format while loading a table.

Q38

Why does the REMOVE command from a stage improve the COPY INTO command the next time it’s executed?

Q39

What two requirements are necessary for the remote service to be called by the Snowflake external function?

Q40

After how many hours does Snowflake cancel our running SQL statement by default?

Q41

Which pipes are cloned when cloning a database or schema?

Q42

What are the two current limitations of external functions?

Q43

Select all the different ways that we can access to the USER_NAME field if we have a table called MYTABLE with a variant column called JSONTEXT with the following structure:

{
	"USER_NAME": "Bob",
	"USER_AGE ": 40,
	"TECHNOLOGY": "Snowflake"
}

Q44

When will a multi-cluster warehouse shut down if it’s running with the standard scaling policy?

Q45

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

Q46

How many credits will consume a medium-size warehouse with 2 clusters running in auto-scaled mode for 3 hours, considering that the first cluster runs continuously and the second one runs for 30 minutes in the second hour?

Q47

How can a role which is the owner of some objects in a share block access them if he is not the owner of that share?

Q48

Which command will use warehouse credits?

Q49

What is the best practice to follow when calling the Snowpipe loadHistoryScan endpoint?

Q50

Which way to create a Snowpipe is correct if your data is hosted in AWS S3?

Q51

You have a multi-cluster warehouse running with the standard scaling policy. The maximum number of clusters is set to 8. If a lot of queries are queried, and the warehouse is constantly starting new clusters, what is the maximum time the warehouse will start all the clusters?

Q52

What is the difference between clustering a table and using searching optimization only talking about costs?

Q53

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

Q54

One query takes a lot of time, and you see in the query profiler the following information:

What might be the cause of this?

Q55

What types of tables are good candidates for implementing search optimization?

Q56

The time-travel retention period of a table is configured to be ten days. You now increase the retention period to 20 days. What will happen with the table's data after this increment? (SELECT TWO)

Q57

After running the function SYSTEM$CLUSTERING_INFORMATION in a table, it returns the following information:

What parameters indicate that the table is not well-clustered?

Q58

What are three of the limitations of the Search Optimization Service?

Q59

What is the maximum time that Snowflake can run a query?

Q60

Will this query cost compute credits considering that the previous query ran 5 minutes ago?

CREATE OR REPLACE TABLE MTBL AS 
    SELECT * 
    FROM TABLE
    (RESULT_SCAN(LAST_QUERY_ID())
);