SnowPro Associate: Platform Question 179
Single answer○ SELECT *A data engineering team maintains a reporting view in Snowflake that is currently defined as SELECT * FROM SALES.PUBLIC.ORDERS. The ORDERS table is frequently altered as new columns are added by upstream pipelines. Recently, a downstream BI dashboard began showing unexpected extra fields and changed column positions after a schema update. The team wants to reduce the risk of breaking downstream consumers while still querying the same table. Which action is the BEST choice?
- A
Keep using SELECT * because Snowflake automatically preserves the original output column list for downstream queries, even when new columns are added to the base table.
- B
Replace SELECT * with an explicit column list in the view definition so the projected columns and their order remain stable unless the view is intentionally changed.
- C
Use SELECT * and rely on Snowflake Time Travel so downstream dashboards continue to see the old column set after table schema changes.
- D
Use SELECT * with a larger virtual warehouse so schema changes are less likely to affect downstream dashboard results.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to replace SELECT * with an explicit column list. In Snowflake, SELECT * returns all columns from the referenced object, which can create operational risk when source tables evolve. For stable reporting layers, especially views consumed by BI tools, best practice is to define the exact columns needed rather than relying on wildcard projection. This improves schema stability, avoids unintentionally exposing new fields, and makes changes auditable and intentional. Snowflake documentation for SELECT describes wildcard column projection, and general data engineering best practices favor explicit column selection in production-facing views and dashboards.
- A. Incorrect.
Incorrect. SELECT * expands to all available columns from the referenced object. If the underlying table changes, especially when views are recreated or queries directly use SELECT *, downstream consumers can be affected by added columns or changed expectations. Snowflake does not treat SELECT * as a contract that freezes the output schema for consumers.
- B. Correct.
Correct. Explicitly listing the required columns is the best practice when downstream systems depend on a stable schema and column order. This avoids unintentionally exposing new columns added to the source table and makes schema changes intentional and controlled. In production reporting objects, avoiding SELECT * improves predictability and reduces downstream breakage.
- C. Incorrect.
Incorrect. Time Travel is for accessing historical data versions within a retention period; it is not a mechanism for preserving a stable projected column list for current dashboard queries. It does not solve the problem of a view or query using SELECT * exposing schema changes in normal operation.
- D. Incorrect.
Incorrect. Warehouse size affects compute resources and performance, not query semantics. Increasing the virtual warehouse will not prevent SELECT * from returning all columns in the referenced table or protect dashboards from schema drift.