Snowflake ADA-C01 Exam - Topic 4 Question 17 Discussion

Actual exam question for Snowflake's ADA-C01 exam

Question #: 17
Topic #: 4

A Snowflake Administrator needs to persist all virtual warehouse configurations for auditing and backups. Given a table already exists with the following schema:

Table Name : VWH_META

Column 1 : SNAPSHOT_TIME TIMESTAMP_NTZ

Column 2 : CONFIG VARIANT

Which commands should be executed to persist the warehouse data at the time of execution in JSON format in the table VWH META?

A1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (),
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID(1) ) ) ;

B1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (), *
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ())) ;

C1. SHOW WAREHOUSES;
2. INSERT INTO VWH_META
SELECT CURRENT_TIMESTAMP (),
OBJECT CONSTRUCT (*)
FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ()));

D1. SHOW WAREHOUSES;
2. INSERT INTO VWH META
SELECT CURRENT TIMESTAMP (), *
FROM TABLE (RESULT_SCAN (SELECT
LAST QUERY ID(-1)));

Show Suggested Answer

Suggested Answer: A

A masking policy is a SQL expression that transforms the data in a column based on the role that queries the column1. The is_role_in_session function returns true if the specified role is in the current session2. Therefore, the masking policy in option A will mask the column data with asterisks whenever it is queried through a view owned by the MASKED_VIEW_ROLE3. The other options use different functions that do not check the ownership of the view, but rather the current role, the invoker role, or the available roles in the session45. These functions may not return the desired result if the role that owns the view is different from the role that queries the view.

by Brent at Jun 29, 2024, 07:51 PM

Limited Time Offer

25%

Off

Get Premium ADA-C01 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Johnson

3 months ago

I’m surprised they didn’t include a direct JSON function in any of the options!

upvoted 0 times

...

Sommer

3 months ago

I’m leaning towards D, but C does seem more complete.

upvoted 0 times

...

Dalene

3 months ago

Wait, why is everyone picking C? What’s wrong with B?

upvoted 0 times

...

Rosio

4 months ago

Yeah, I agree with C. It looks solid for JSON format.

upvoted 0 times

...

Alise

4 months ago

I think option C is the right choice. It uses OBJECT CONSTRUCT.

upvoted 0 times

...

Thora

4 months ago

I feel like option B is close, but I remember we specifically talked about needing to use CURRENT_TIMESTAMP() instead of CURRENT TIMESTAMP(). I hope I got that right!

upvoted 0 times

...

Michel

4 months ago

I’m a bit confused about the LAST_QUERY_ID function. I thought it should be LAST_QUERY_ID(1) for the most recent query, but I can't recall if that's always the case.

upvoted 0 times

...

Bethanie

4 months ago

I think option C sounds familiar because it uses OBJECT CONSTRUCT, which we practiced in class. It seems like the right way to format the JSON data.

upvoted 0 times

...

Eileen

5 months ago

I remember we discussed the importance of using the correct syntax for the INSERT statement, but I'm not sure if we should use OBJECT CONSTRUCT or just * in the SELECT clause.

upvoted 0 times

...

Virgie

5 months ago

I'm a little confused by the different ways to reference the last query ID. I'll need to double-check the documentation to make sure I'm using the right approach. Better to be safe than sorry on an exam question like this.

upvoted 0 times

...

Daniela

5 months ago

Hmm, I'm a bit unsure about this one. I'll need to carefully review the syntax and make sure I understand how the OBJECT_CONSTRUCT function works in Snowflake. I don't want to risk getting the query wrong.

upvoted 0 times

...

Maryrose

5 months ago

This looks straightforward. I'd go with option C - it seems to capture the warehouse configurations in JSON format and insert them into the VWH_META table along with the current timestamp.

upvoted 0 times

...

Mindy

5 months ago

Option B looks good to me. Selecting the * from the RESULT_SCAN should give me all the warehouse configuration details, and I can just insert that along with the current timestamp.

upvoted 0 times

...

Kate

5 months ago

Based on the information provided, I would recommend changing the rating outlook to negative. The high court order is a major setback that could significantly impact the project's viability and the company's ability to meet its obligations. We need to signal this risk to investors, while waiting for the outcome of the appeal.

upvoted 0 times

...

Ceola

10 months ago

I'm going with option C. It's the only one that explicitly converts the result set to JSON format, which seems like the requirement here. Gotta love those object construction tricks!

upvoted 0 times

Jill

9 months ago

I agree, option C is the only one that converts the result set to JSON format. Smart choice!

upvoted 0 times

...

Rosio

10 months ago

Option C is definitely the way to go. Object construct is the key here.

upvoted 0 times

...

Eleonora

10 months ago

Option B looks like the way to go. Capturing the entire result set from SHOW WAREHOUSES and inserting it into the table is a solid approach.

upvoted 0 times

...

Remedios

10 months ago

Ha! I bet the Snowflake Administrator is wishing they had a magic wand to make this process easier. Persistence can be a real pain sometimes.

upvoted 0 times

...

Martin

10 months ago

I'm not sure about using LAST_QUERY_ID(-1) in option D. Shouldn't we use the most recent query ID? Otherwise, we might get the wrong data.

upvoted 0 times

...

Gail

10 months ago

But option C uses OBJECT CONSTRUCT, which is needed to persist the data in JSON format.

upvoted 0 times

...

Nguyet

11 months ago

I disagree, I believe option D is the right choice.

upvoted 0 times

...

Gail

11 months ago

I think the correct answer is option C.

upvoted 0 times

...

Ezekiel

11 months ago

Option C looks good to me. It captures the warehouse configurations in JSON format using OBJECT_CONSTRUCT(*) and saves the data to the VWH_META table. Simple and straightforward solution.

upvoted 0 times

Sabine

9 months ago

Great choice! Option C definitely seems like the right command to execute for this task.

upvoted 0 times

...

Annabelle

9 months ago

I think I will go with Option C as well. It's simple and straightforward.

upvoted 0 times

...

Dan

9 months ago

I agree, it seems like the most efficient way to persist the warehouse data for auditing and backups.

upvoted 0 times

...

Ramonita

9 months ago

Option C looks good to me. It captures the warehouse configurations in JSON format using OBJECT_CONSTRUCT(*) and saves the data to the VWH_META table.

upvoted 0 times

...