DAA-C01 Question 228
Single answerSet the contexts (e.g., database, schema, virtual warehouse, role)A data analyst is troubleshooting a worksheet in Snowsight that should query SALES_DB.ANALYTICS.REVENUE_SUMMARY using the ANALYST_WH role. The analyst can successfully run the fully qualified statement SELECT * FROM SALES_DB.ANALYTICS.REVENUE_SUMMARY;, but the unqualified statement SELECT * FROM REVENUE_SUMMARY; fails with an object not found error. The analyst also wants to ensure compute charges are attributed to the intended warehouse before rerunning several heavy queries. Which action should the analyst take first to make the unqualified query succeed and ensure the session uses the intended compute resource?
- A
Run: USE ROLE ANALYST_WH; USE DATABASE SALES_DB; USE SCHEMA ANALYTICS; USE WAREHOUSE ANALYST_WH;
- B
Run: USE ROLE ANALYST_ROLE; USE DATABASE SALES_DB; USE SCHEMA ANALYTICS; USE WAREHOUSE ANALYST_WH;
- C
Run: USE DATABASE SALES_DB.ANALYTICS; USE WAREHOUSE ANALYST_WH;
- D
Run: ALTER SESSION SET CURRENT_DATABASE = 'SALES_DB', CURRENT_SCHEMA = 'ANALYTICS', CURRENT_WAREHOUSE = 'ANALYST_WH';
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, unqualified object names are resolved using the current database and current schema in the session. Since the fully qualified query succeeds, the analyst likely has the needed object privileges, but the session context is not set correctly for unqualified references. The proper fix is to set the active role, database, schema, and warehouse explicitly using USE ROLE, USE DATABASE, USE SCHEMA, and USE WAREHOUSE. This is also a best practice when moving between worksheets, users, or automation contexts because session state is not always what the user assumes. Snowflake documentation on session context explains that USE DATABASE and USE SCHEMA control object name resolution, while USE WAREHOUSE selects the virtual warehouse that executes queries and incurs compute charges. USE ROLE determines which privileges are active for the session.
- A. Incorrect.
Incorrect. USE ROLE expects a role name, not a warehouse name. ANALYST_WH is intended to be the virtual warehouse in this scenario, so this command would fail or reference the wrong object type. Although setting the database, schema, and warehouse is conceptually correct, the role context is invalid here.
- B. Correct.
Correct. To run an unqualified query like SELECT * FROM REVENUE_SUMMARY;, the session must have the correct current database and schema set, in this case SALES_DB and ANALYTICS. To ensure compute usage is charged to the intended warehouse, the session should also set USE WAREHOUSE ANALYST_WH. Setting the active role to ANALYST_ROLE is also appropriate because object access and warehouse usage privileges are evaluated in the current role context.
- C. Incorrect.
Incorrect. USE DATABASE accepts only a database name, not a combined database.schema identifier. The schema must be set separately with USE SCHEMA ANALYTICS or USE SCHEMA SALES_DB.ANALYTICS. While setting the warehouse is necessary for compute attribution, this option would not correctly establish both object resolution contexts.
- D. Incorrect.
Incorrect. Snowflake uses USE DATABASE, USE SCHEMA, USE WAREHOUSE, and USE ROLE to set session context. ALTER SESSION is used for session parameters, but CURRENT_DATABASE, CURRENT_SCHEMA, and CURRENT_WAREHOUSE are not set this way. This option reflects a common misconception that all session context is controlled through session parameters.