SnowPro Associate: Platform Question 114
Single answer● Data typesA retail company is loading order data from CSV files into a Snowflake table. The source file contains these fields: ORDER_ID values such as 100045, ORDER_TS values such as 2025-02-14 16:30:45, TOTAL_AMOUNT values such as 149.95, and IS_PRIORITY values stored as TRUE or FALSE. Analysts need to run numeric aggregations on TOTAL_AMOUNT, filter by ORDER_TS ranges, and use IS_PRIORITY in Boolean predicates without repeated casting. Which table design is the BEST choice for this use case?
- A
Define ORDER_ID, ORDER_TS, TOTAL_AMOUNT, and IS_PRIORITY all as VARCHAR to maximize load flexibility, then cast them in queries as needed
- B
Define ORDER_ID as NUMBER, ORDER_TS as TIMESTAMP_NTZ, TOTAL_AMOUNT as NUMBER(10,2), and IS_PRIORITY as BOOLEAN
- C
Define ORDER_ID as FLOAT, ORDER_TS as DATE, TOTAL_AMOUNT as VARCHAR, and IS_PRIORITY as STRING
- D
Define ORDER_ID as BOOLEAN, ORDER_TS as TIMESTAMP_TZ, TOTAL_AMOUNT as FLOAT, and IS_PRIORITY as NUMBER(1,0)
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Snowflake native data types that match the semantics and query patterns of the data. NUMBER is appropriate for integer-like identifiers, TIMESTAMP_NTZ is suitable when timestamp values do not include time zone information, NUMBER with scale such as NUMBER(10,2) is preferred for exact financial-style amounts, and BOOLEAN should be used for logical true/false fields. In Snowflake, choosing the correct data type improves usability, reduces the need for repeated CAST operations, and avoids precision loss. This reflects Snowflake documentation and best practices around numeric, timestamp, string, and Boolean data types: use exact numeric types for precise arithmetic, select the correct timestamp variant based on time zone requirements, and use BOOLEAN for logical predicates rather than storing true/false values as text.
- A. Incorrect.
Incorrect. Using VARCHAR for all columns may simplify initial ingestion, but it is not the best design when the data has clear business meaning and query requirements. Storing timestamps, numeric amounts, and Boolean flags as strings forces repeated casting in queries, increases risk of conversion errors, and can reduce clarity and maintainability. A candidate might choose this because raw landing tables often use text types, but the scenario asks for the best table design for analyst usage.
- B. Correct.
Correct. ORDER_ID as NUMBER is appropriate for integer identifiers when numeric behavior is acceptable. ORDER_TS as TIMESTAMP_NTZ fits a timestamp value when no time zone information is present in the source string. TOTAL_AMOUNT as NUMBER(10,2) is the best choice for currency-like values because fixed-point numeric types preserve exact precision needed for aggregations. IS_PRIORITY as BOOLEAN allows direct filtering with Boolean logic without conversion. This design aligns with Snowflake best practices of using the most appropriate native data type for query and analytics needs.
- C. Incorrect.
Incorrect. ORDER_ID as FLOAT is not ideal for identifiers because floating-point types can introduce representation issues and are intended for approximate numeric values. ORDER_TS as DATE would lose the time-of-day component required by values like 2025-02-14 16:30:45. TOTAL_AMOUNT as VARCHAR would require casting before aggregation, and IS_PRIORITY as STRING would not support direct Boolean predicates efficiently. This option combines several common type mismatches.
- D. Incorrect.
Incorrect. ORDER_ID as BOOLEAN is clearly inappropriate for an order identifier. ORDER_TS as TIMESTAMP_TZ is only necessary when the source data includes or requires time zone context; the sample value does not. TOTAL_AMOUNT as FLOAT is a poor choice for financial values because approximate floating-point storage can lead to precision issues. IS_PRIORITY as NUMBER(1,0) could encode true/false values, but the source already contains TRUE/FALSE and Snowflake supports a native BOOLEAN type, which is clearer and more appropriate.