SnowPro Associate: Platform Question 200
Single answer○ Structured dataA retail company stores daily sales records in a Snowflake table with strongly typed columns such as ORDER_ID NUMBER, ORDER_DATE DATE, CUSTOMER_ID NUMBER, and TOTAL_AMOUNT NUMBER(12,2). Analysts frequently join this table to other relational tables and apply filters on ORDER_DATE and CUSTOMER_ID. The data engineering team wants to maintain data quality and optimize query performance for these common reporting workloads. Which approach is the MOST appropriate for this structured data use case?
- A
Load the sales data into a regular Snowflake table with explicitly defined column data types and use standard SQL constraints and relational joins as needed.
- B
Store the sales records as a single VARIANT column in a table because VARIANT provides better performance than typed columns for date and numeric filtering.
- C
Convert the sales records into staged files only and query them directly with SELECT statements, avoiding Snowflake tables to preserve structure.
- D
Load the data into an external table because external tables enforce stronger relational integrity for structured transactional data than standard Snowflake tables.
Show answer and explanation
Correct answer: A
Explanation
This scenario is about choosing the correct Snowflake storage model for structured data. Structured data has a predefined schema and is best represented in standard Snowflake tables with explicit column definitions. This allows teams to use SQL naturally for joins, filtering, aggregations, and governance. In contrast, VARIANT is optimized for semi-structured data, not as a general substitute for well-defined relational columns. External tables are used when data remains in external cloud storage, and querying staged files is typically limited to temporary or ad hoc use cases rather than production reporting. Snowflake documentation and best practices distinguish standard tables for structured relational workloads from VARIANT-based handling of semi-structured formats.
- A. Correct.
Correct. For structured data with known schema, strongly typed columns in a standard Snowflake table are the best fit. This supports efficient SQL querying, filtering, joins, and data quality controls. Snowflake is designed to work well with relational, structured datasets using native column definitions such as NUMBER and DATE. This is the practical choice for reporting workloads that frequently filter and join on specific attributes.
- B. Incorrect.
Incorrect. VARIANT is intended for semi-structured data such as JSON, Avro, ORC, Parquet, or XML, not as a preferred replacement for strongly typed relational columns when the schema is already known and stable. Although Snowflake can query data inside VARIANT, using a single VARIANT column for structured sales records would typically reduce clarity and can complicate filtering, type enforcement, and governance.
- C. Incorrect.
Incorrect. Querying staged files directly is useful for limited inspection or ad hoc access, but it is not the preferred design for managed, high-quality structured reporting data. Staged files do not provide the same table-based management, schema enforcement, and operational simplicity expected for recurring joins and filters in production analytics.
- D. Incorrect.
Incorrect. External tables are primarily used to query data stored outside Snowflake, such as in cloud object storage. They do not provide stronger relational integrity than standard Snowflake tables. For structured transactional or reporting data that the company wants to manage inside Snowflake, a standard internal table is generally the appropriate design.