SnowPro Associate: Platform Question 178
Single answer○ SELECT *A data engineering team created a reporting query against a Snowflake table named SALES_FACT. The query currently uses SELECT * because analysts want all existing columns. A new column named LOAD_BATCH_ID is later added to SALES_FACT. After the change, a downstream process that expects a fixed column layout starts failing because the query result now includes the extra column. The team wants to keep using SELECT * but prevent this issue in the future by returning all columns except a few operational fields. Which approach should they use?
- A
Use
SELECT * EXCLUDE (LOAD_BATCH_ID, ETL_CREATED_TS) FROM SALES_FACT - B
Use
SELECT ALL COLUMNS EXCEPT (LOAD_BATCH_ID, ETL_CREATED_TS) FROM SALES_FACT - C
Use
SELECT * OMIT (LOAD_BATCH_ID, ETL_CREATED_TS) FROM SALES_FACT - D
Use
SELECT * REPLACE NULL AS LOAD_BATCH_ID FROM SALES_FACT
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, SELECT * can be refined using keywords such as EXCLUDE, REPLACE, and RENAME to make wide-column queries more practical. For this scenario, the key requirement is to keep most columns while excluding specific operational columns that should not appear in the result. SELECT * EXCLUDE (...) is designed for exactly this use case. This helps reduce maintenance compared with manually listing every business column, while avoiding the instability caused when new technical columns are added. As a best practice, teams should be cautious with raw SELECT * in production pipelines because schema changes can alter result sets unexpectedly. Snowflake documentation for the SELECT command describes supported SELECT * modifiers, including EXCLUDE, and explains how they affect projected columns.
- A. Correct.
Correct. Snowflake supports
SELECT * EXCLUDE (...)to return all columns from the referenced object except the specified columns. This is the practical way to keep a broadly inclusive projection while excluding operational or unwanted fields that could disrupt downstream consumers expecting a stable subset of columns. - B. Incorrect.
Incorrect. This syntax is not valid in Snowflake SQL. Although it sounds similar to SQL features in other platforms or natural-language descriptions of the requirement, Snowflake uses the
EXCLUDEkeyword withSELECT *, notALL COLUMNS EXCEPT. - C. Incorrect.
Incorrect.
OMITis not a valid Snowflake keyword for column projection in aSELECT *statement. This distractor reflects a plausible guess based on everyday terminology, but it is not supported syntax. - D. Incorrect.
Incorrect. Snowflake does support
SELECT * REPLACE (...)for replacing the value of one or more selected columns in the result set, but it does not remove columns from the output. In this scenario, the problem is the presence of the extra column, not its value.