SnowPro Associate: Platform Question 177
Single answer○ SELECT *A data engineering team maintains a reporting query in Snowflake that currently uses SELECT * from the SALES.PUBLIC.ORDERS table. After a new sensitive column named CUSTOMER_SSN is added to the table, downstream users unexpectedly begin seeing that column in their query results. The team wants to continue using SELECT * for convenience, but they need to prevent specific columns from appearing in the output without listing every allowed column manually. Which SQL approach best meets this requirement?
- A
Use
SELECT * EXCLUDE CUSTOMER_SSN FROM SALES.PUBLIC.ORDERS; - B
Use
SELECT ALL COLUMNS EXCEPT CUSTOMER_SSN FROM SALES.PUBLIC.ORDERS; - C
Use
SELECT * OMIT CUSTOMER_SSN FROM SALES.PUBLIC.ORDERS; - D
Use
SELECT * REPLACE NULL AS CUSTOMER_SSN FROM SALES.PUBLIC.ORDERS;
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, SELECT * returns all columns from the referenced object, including newly added columns. That can create governance and downstream compatibility issues when schemas change. To keep the convenience of SELECT * while omitting sensitive or unwanted columns, Snowflake supports SELECT * EXCLUDE column_name. Snowflake also supports related extensions such as ILIKE, REPLACE, and RENAME with SELECT *, but only EXCLUDE removes columns from the output. From a best-practice perspective, teams should be cautious with SELECT * in production workloads because schema changes can affect both security exposure and result shape. When SELECT * is used, EXCLUDE can help reduce risk in evolving schemas.
- A. Correct.
Correct. Snowflake supports
SELECT * EXCLUDE ...to return all columns except the named column or columns. This is the most appropriate solution when a team wants the convenience ofSELECT *but needs to suppress specific fields from the result set. It is especially useful when schemas evolve and new columns are added. - B. Incorrect.
Incorrect. Although this syntax may look familiar from other SQL dialects or seem intuitive,
SELECT ALL COLUMNS EXCEPT ...is not valid Snowflake syntax. A candidate might choose this because some platforms use similar wording, but Snowflake uses theEXCLUDEkeyword withSELECT *. - C. Incorrect.
Incorrect.
OMITis not a valid keyword for column exclusion in Snowflake SELECT syntax. This distractor targets the misconception that Snowflake supports a generic English-like synonym for excluding columns. - D. Incorrect.
Incorrect.
REPLACEis a valid Snowflake extension that can be used withSELECT *, but this option is syntactically and functionally wrong for the stated goal.REPLACEis used to substitute the expression returned for an existing column in the result, not to remove the column from the output. Even if written correctly, it would still includeCUSTOMER_SSNin the result set.