When choosing between using a dimensional model and a flattened dataset for BI requirements in Snowflake, what considerations impact the final decision? (Select all that apply)

Question

Options

• AQuery performance expectations
• BUser access control requirements
• CQuery execution plan constraints
• DData denormalization and redundancy needs

• A
  81% (21)
• B
  12% (3)
• C
  8% (2)

Explanation

Choosing between a dimensional model and a flattened dataset is fundamentally a data design decision driven by how data will be queried and structured. Query performance expectations (A) are critical because dimensional models (star/snowflake schemas) excel at complex analytical joins, while flattened datasets minimize joins for faster, simpler queries - the expected query patterns directly shape the right choice. Data denormalization and redundancy needs (D) are equally central because flattened datasets intentionally repeat data to eliminate joins, whereas dimensional models keep facts and dimensions separated, so your tolerance for redundancy determines which approach fits.

B is incorrect because user access control - roles, row-level security, column masking - is managed through Snowflake's governance layer (RBAC, Dynamic Data Masking) and applies equally to either model type; it doesn't drive the modeling decision itself. C is incorrect because Snowflake's query optimizer automatically generates execution plans regardless of whether you use a dimensional or flat structure - you don't design schemas around execution plan constraints.

Memory tip: Think "PAD" - Performance and Denormalization are the two physical design factors. If the question mentions access control or execution plans, those are governance/engine concerns, not modeling choices.

No community discussion yet for this question.