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

Questions 6–10

1. 6foundation · easy

   Which data model type is best suited for a reporting environment where business users need to query aggregated sales by product, time, and region with minimal joins?

   • AEntity-relationship model
   • BData vault model
   • CRelational model
   • DDimensional model
   Reveal Solution Discussion Select an answer first
2. 7application · medium

   A data modeler is designing a fact table for an e-commerce company. The fact table will store order line items, and the company wants to analyze sales by product, customer, and date. The product dimension has a surrogate key, and the customer dimension also has a surrogate key. Which columns should be included in the fact table to properly link to these dimensions?

   • AProduct natural key, customer natural key, and order date
   • BProduct surrogate key, customer surrogate key, and date key
   • CProduct name, customer name, and order date
   • DOnly the product surrogate key and the order date
   Reveal Solution Discussion Select an answer first
4. 8application · medium

   A financial reporting team is designing a data model for loan performance. They have a central fact table of loan payments and dimensions for borrower, branch, loan product, and date. The borrower dimension has attributes such as credit score, employment status, and region, which are currently stored directly in the borrower dimension. The team wants to reduce data redundancy and make it easier to update region information independently. Which schema design best meets this requirement?

   • ASnowflake schema with a separate region dimension linked to the borrower dimension
   • BStar schema with the borrower dimension denormalized to include all attributes
   • CData vault model with hubs, links, and satellites
   • DRelational model with fully normalized tables and no fact/dimension distinction
   Reveal Solution Discussion Select an answer first
5. 9application · medium

   A human resources team maintains an employee dimension in a data warehouse. When an employee changes their job title, the team wants to keep the old title for historical reporting but also show the current title in dashboards. They do not need to track the history of every change, only the most recent change. Which slowly changing dimension (SCD) strategy should they use?

   • AType 1: overwrite the job title in the existing row
   • BType 2: add a new row for the employee with the new title and effective dates
   • CType 3: add a separate column for the previous job title
   • DType 4: use a separate history table for all title changes
   Reveal Solution Discussion Select an answer first
6. 10foundation · easy

   In data modeling, what does the term 'grain' refer to?

   • AThe frequency of data refreshes
   • BThe physical storage format of the table
   • CThe level of detail captured by a fact table
   • DThe number of dimensions in a schema
   Reveal Solution Discussion Select an answer first