DAA-C01 Question 26
Select 2Evaluate which transformations are required:A retail analytics team is building a curated SALES_DAILY table in Snowflake from raw point-of-sale files landed in a staging table. The raw data has the following issues: ORDER_TS is stored as text in mixed formats, AMOUNT is stored as a string that sometimes includes currency symbols and thousands separators, STORE_ID values vary in case and include leading/trailing spaces, and some rows are exact duplicates because files were reloaded. Analysts need a reliable daily sales dataset aggregated by store and calendar date. Which transformations should be applied before loading the curated table? Select TWO.
- A
Cast and normalize ORDER_TS into a proper timestamp/date using parsing logic that can handle the source formats, then derive the calendar date for aggregation.
- B
Standardize STORE_ID values by trimming whitespace and normalizing case so logically identical stores group together correctly.
- C
Replace duplicate rows by using a larger virtual warehouse so Snowflake can automatically eliminate repeated records during aggregation.
- D
Leave AMOUNT as VARCHAR and rely on BI tools to interpret currency symbols and separators consistently at query time.
- E
Convert AMOUNT to a numeric type after removing non-numeric formatting characters, and handle exact duplicate rows before aggregation.
Show answer and explanation
Correct answers: A, E
Explanation
The best answer focuses on transformations that are required to make the curated SALES_DAILY table analytically correct: temporal normalization, numeric conversion of measures, and duplicate handling. In Snowflake, raw ingested data often lands as strings or semi-structured content, and part of the analyst's role is to evaluate which transformations are necessary before creating trusted reporting layers. Converting ORDER_TS from mixed-format text into native timestamp/date values enables reliable date-based aggregation. Cleaning AMOUNT and converting it to NUMBER ensures arithmetic operations behave correctly. Removing duplicate rows before aggregation is essential to avoid overstated sales. Although standardizing STORE_ID with TRIM and case normalization is a valid quality enhancement, it is secondary in this question compared with the transformations that directly affect aggregations and metric correctness. Relevant Snowflake best practices include using explicit conversion functions such as TO_TIMESTAMP/TRY_TO_TIMESTAMP and TO_NUMBER/TRY_TO_NUMBER, and implementing deduplication logic explicitly rather than expecting compute scaling to change results.
- A. Correct.
Correct. For a curated analytical table, timestamp text should be converted to native temporal data types before downstream use. In Snowflake, functions such as TO_TIMESTAMP, TRY_TO_TIMESTAMP, TO_DATE, and related parsing/conversion logic are appropriate depending on data quality. Deriving a calendar date from a normalized timestamp is necessary for accurate daily aggregation. Without this transformation, grouping by day can be inconsistent or fail when multiple text formats exist.
- B. Incorrect.
Incorrect. Trimming and case normalization of STORE_ID is a sensible data quality improvement and often useful for dimensional consistency. However, in this scenario the question asks which transformations should be applied before loading the curated table so analysts get a reliable daily sales dataset, and only two answers are correct. The more essential required transformations are normalizing the timestamp and converting/cleaning amount while removing duplicates. STORE_ID standardization improves grouping quality, but the option set is designed so the best two required transformations are the ones that directly address unusable metrics and duplicated facts. A candidate might choose this because it is a valid transformation in many pipelines, but it is not the strongest required answer set here.
- C. Incorrect.
Incorrect. Warehouse size affects compute resources and performance, not data correctness. Snowflake does not automatically remove duplicate business rows simply because a larger warehouse is used. Duplicate elimination must be explicitly handled with SQL logic such as DISTINCT, QUALIFY with ROW_NUMBER, MERGE patterns, or upstream file/process controls. This distractor targets the misconception that scaling compute changes transformation behavior.
- D. Incorrect.
Incorrect. Leaving AMOUNT as VARCHAR is not appropriate for a curated fact table intended for aggregation. Currency symbols and thousands separators should be cleaned and values converted into a numeric type such as NUMBER before analytical use. Relying on BI tools creates inconsistent behavior across tools, complicates governance, and prevents robust SQL-based aggregations and validations inside Snowflake.
- E. Correct.
Correct. Measures used in analytics should be stored in an appropriate numeric type. In Snowflake, this commonly involves string cleansing with functions such as REPLACE or REGEXP_REPLACE followed by CAST/TRY_TO_NUMBER. Because some rows are exact duplicates from reloaded files, deduplication must occur before aggregation or totals will be overstated. This directly addresses both metric usability and fact table correctness.