SnowPro Associate: Platform Question 180
Single answer○ SELECT *A data engineering team maintains a reporting query that currently uses SELECT * from a Snowflake table containing customer transaction data. Over time, new columns have been added to the table, and downstream dashboards started showing unexpected fields and occasional schema-related issues in exported files. The team wants to keep the query concise while reducing the risk of unintended column changes affecting consumers. Which approach best addresses this requirement?
- A
Continue using
SELECT *because Snowflake automatically prevents new columns from appearing in query results unless the query is recompiled. - B
Replace
SELECT *with an explicit column list so the query returns only the intended columns even if the table schema changes later. - C
Use
SELECT DISTINCT *so only the original columns are returned and newly added columns are ignored. - D
Create a larger virtual warehouse so schema changes caused by
SELECT *are handled more efficiently at runtime.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, SELECT * is convenient for ad hoc exploration, but it is risky in production queries consumed by dashboards, applications, or file exports because the result set can change when the underlying table schema changes. The safest approach is to explicitly name required columns. This aligns with SQL best practices and Snowflake usage guidance: use SELECT * mainly for exploration or short-term analysis, and prefer explicit column selection when downstream systems rely on stable schemas. Options involving DISTINCT or warehouse sizing address different concerns and do not control schema evolution effects.
- A. Incorrect.
Incorrect. In Snowflake,
SELECT *returns all columns currently defined in the referenced table or view at query execution time. If new columns are added, they can appear in results and affect downstream processes. Snowflake does not shield consumers from schema drift simply because a query was previously written. - B. Correct.
Correct. Replacing
SELECT *with an explicit column list is the recommended way to control schema stability for downstream consumers. This ensures only the required columns are returned, even if additional columns are added to the underlying table later. It is a common best practice for production reporting, exports, and interfaces that depend on a predictable column set. - C. Incorrect.
Incorrect.
DISTINCTremoves duplicate rows based on the full selected row content; it does not limit which columns are returned.SELECT DISTINCT *still includes all current columns, including any newly added ones, so it does not solve schema stability issues. - D. Incorrect.
Incorrect. Warehouse size affects compute resources and performance, not result-set schema behavior. Increasing warehouse size does nothing to prevent
SELECT *from returning newly added columns.