COF-C03 Question 58
Single answerTypesA retail company is loading point-of-sale data into Snowflake. One source column contains numeric-looking identifiers such as 001245, 000078, and 123450. Business users use these values as product codes and require the original formatting to be preserved exactly, including leading zeros. The data engineering team also wants to prevent accidental arithmetic on this field. Which Snowflake data type is the MOST appropriate for this column?
- A
NUMBER(10,0)
- B
VARCHAR
- C
FLOAT
- D
VARIANT
Show answer and explanation
Correct answer: B
Explanation
The best answer is VARCHAR because the column represents an identifier, not a quantity for calculation. In Snowflake, numeric types such as NUMBER store the numeric value itself, not display formatting, so leading zeros are not preserved. FLOAT is also unsuitable because it is an approximate numeric type. VARIANT is intended for semi-structured data such as JSON, Avro, ORC, Parquet, or XML and is not a best-practice choice when the data type is known in advance. Snowflake documentation and data modeling best practices emphasize choosing string types for code values, identifiers, and other fields where textual representation must remain unchanged.
- A. Incorrect.
Incorrect. NUMBER(10,0) stores values as integers without formatting semantics. Although it can store the numeric value, leading zeros are not preserved as part of the stored value. For example,
001245and1245would be treated the same numerically. This is a common mistake when a field looks numeric but is actually an identifier. - B. Correct.
Correct. VARCHAR is the best choice for identifiers that may contain leading zeros and are not intended for arithmetic. It preserves the exact string representation provided by the source, including formatting such as leading zeros. This aligns with best practice for business keys, codes, ZIP/postal codes, and other values that may appear numeric but should be treated as text.
- C. Incorrect.
Incorrect. FLOAT is designed for approximate numeric values and is even less appropriate than NUMBER for identifier data. It does not preserve formatting such as leading zeros and can introduce precision issues for large values. Choosing FLOAT here reflects a misunderstanding between numeric measurement data and code-like identifiers.
- D. Incorrect.
Incorrect. VARIANT can store semi-structured data, including strings and numbers, but it is not the most appropriate choice for a standard relational column with a known, consistent type. Using VARIANT for a simple product code would add unnecessary complexity and reduce clarity compared with defining the column directly as VARCHAR.