Design and Use Data Models SNOWPRO-ADVANCED-DATA-ANALYST Practice Questions (Page 1)

Questions 1–5

1. 1expert · hard

   A financial services company maintains a customer dimension in its data warehouse. Regulatory requirements mandate that the company must be able to report the customer's address at any point in time for the past five years. The company also needs to minimize storage costs and query complexity. The address changes infrequently, but when it does change, the company must retain the full history. Which approach best balances these requirements?

   • AUse Type 1 SCD and overwrite the address
   • BUse Type 3 SCD with a previous address column
   • CUse a separate history table with all changes
   • DUse Type 2 SCD with effective dates and a current flag
   Reveal Solution Discussion Select an answer first
2. 2application · medium

   A data analyst is implementing a dimensional model in Snowflake. They have created fact and dimension tables, but they want to ensure that queries against the fact table are optimized for performance. The fact table is large, and queries often filter on the order date. Which Snowflake feature should they use to improve query performance?

   • AAdd a primary key constraint to the fact table
   • BCreate a view that filters the fact table
   • CUse a materialized view for the fact table
   • DDefine a clustering key on the order date column
   Reveal Solution Discussion Select an answer first
4. 3application · medium

   A retail analytics team maintains a customer dimension table in Snowflake. When a customer changes their address, the team needs to preserve the historical address for past orders while also tracking the current address for new orders. They also need to be able to report on which address was associated with each order at the time the order was placed. Which approach should they use?

   • AAdd a new column to the customer dimension for the current address and keep the original address column unchanged
   • BOverwrite the address in the customer dimension and update all historical fact rows to reflect the new address
   • CStore the address only in the fact table and remove it from the customer dimension
   • DUse a Type 2 slowly changing dimension: add a new row for the customer with the new address and effective dates, and join fact rows using the surrogate key
   Reveal Solution Discussion Select an answer first
5. 4foundation · easy

   Which schema design normalizes dimension tables into multiple related tables, reducing redundancy but requiring more joins for queries?

   • AData vault schema
   • BSnowflake schema
   • CStar schema
   • DGalaxy schema
   Reveal Solution Discussion Select an answer first
6. 5application · medium

   A data analyst is creating a dimensional model in Snowflake. They have a fact table named FACT_SALES and a dimension table named DIM_PRODUCT. The analyst wants to create a view that joins these tables and exposes only the product name, category, and total sales amount, so that other analysts can query it without writing joins. Which Snowflake object should they create?

   • AA secure view that restricts access to certain rows
   • BA temporary table that is populated with the joined data
   • CA standard view that joins FACT_SALES and DIM_PRODUCT
   • DA materialized view that pre-aggregates the data
   Reveal Solution Discussion Select an answer first