ARA-C01 Question 337
Single answer3.3 Determine the appropriate data transformation solution to meet business needs.A retail company ingests point-of-sale transactions into Snowflake every few minutes using Snowpipe. Analysts need a curated SALES_FACT table that applies data quality rules, standardizes product codes, and joins to several dimensions before dashboards refresh every 15 minutes. The source schema changes occasionally, and the transformation logic is maintained by a small data engineering team that wants version control, testability, and clear dependency management across multiple SQL transformation steps. Which solution is the MOST appropriate to meet these business needs?
- A
Create a chain of dynamic tables that incrementally transforms the raw transaction data into the curated SALES_FACT table with an appropriate target lag.
- B
Implement the transformation logic as a set of SQL models in dbt, orchestrated externally on a 15-minute schedule, with tests and version control managing dependencies.
- C
Use a materialized view on the raw transaction table to handle the joins, code standardization, and data quality filtering for the curated SALES_FACT output.
- D
Run all transformation logic inside a single task that executes one large SQL statement every 15 minutes, because tasks provide built-in source schema evolution handling.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use dbt models orchestrated on a 15-minute schedule. The scenario is not asking only how to refresh transformed data; it is asking for the most appropriate transformation solution given business and team requirements: maintainable SQL transformations, version control, testing, and dependency management. dbt is commonly used with Snowflake for exactly this purpose. Snowflake tasks are useful for scheduling, and dynamic tables can simplify some continuously maintained transformations, but neither provides the same development workflow and testing framework as dbt. Materialized views are optimized for specific performance and maintenance use cases, not for full-featured transformation pipelines with complex business logic. Snowflake best practices generally align transformation tool choice with operational needs: use dbt for modular ELT development and testing, tasks for scheduling/orchestration, and dynamic tables when continuous incremental table maintenance is the primary requirement.
- A. Incorrect.
This is a plausible option because dynamic tables can simplify pipeline maintenance and support incremental transformation with target lag. However, the scenario emphasizes a small team that specifically wants strong version control, testability, and clear dependency management across multiple SQL transformation steps. Those are core strengths of dbt. In addition, dynamic tables do not replace the software development lifecycle capabilities of dbt, such as model testing, modular project structure, and integrated dependency definitions in code repositories. Dynamic tables may be part of an architecture, but they are not the most appropriate primary solution for the stated business and team requirements.
- B. Correct.
This is correct. dbt is well suited for multi-step SQL transformations in Snowflake when the organization needs modular modeling, dependency management, version control integration, and built-in data tests. Scheduling dbt runs every 15 minutes through an orchestrator aligns with the dashboard refresh requirement. dbt models can transform Snowpipe-landed raw data into curated fact tables while supporting maintainability for a small engineering team. This matches the requirement to choose an appropriate transformation solution based on business needs rather than just raw platform capability.
- C. Incorrect.
This is incorrect. Materialized views in Snowflake are designed for specific query acceleration and limited automatic maintenance scenarios, but they are not a good fit for complex ELT pipelines involving multiple joins to dimensions, broad transformation logic, standardization rules, and richer data quality processing. Materialized views also have restrictions that make them unsuitable as a general replacement for curated warehouse transformation layers. A candidate might choose this option because materialized views are automatically maintained, but that convenience does not make them the right transformation framework here.
- D. Incorrect.
This is incorrect. Tasks can schedule SQL execution, but a single large SQL statement is difficult to maintain, test, and evolve, especially when the source schema changes and multiple transformation steps are involved. Tasks also do not provide built-in schema evolution handling for transformation logic. Schema evolution is associated with data loading capabilities in certain contexts, not with task orchestration. This option reflects a common misconception that tasks themselves are a comprehensive transformation framework rather than a scheduling and orchestration mechanism.