DEA-C02 Performance Optimization Practice Question
Which TWO of the following are considered 'anti-patterns' for performance in Snowflake?
⚠ Common exam trap
Candidates often confuse anti-patterns with regular scaling best practices, assuming that using large compute resources or dynamic filtering always hurts performance, leading them to misidentify basic tuning options as harmful choices.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Using one large warehouse for all user workloads.
Using a single massive warehouse for all workloads leads to poor resource isolation and contention, as simple queries compete with heavy ones. Additionally, using SELECT * in production code is an anti-pattern because it retrieves unnecessary columns, forcing the system to read more data blocks than required. Both practices degrade performance and increase costs by wasting I/O and compute resources on operations that could be avoided.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Using one large warehouse for all user workloads.
Why this is correct
Consolidating all workloads into one warehouse prevents resource isolation. A single complex query can monopolize the warehouse, causing delays for other users. Separating workloads into different warehouses based on priority or type is a best practice to ensure predictable performance and effective resource management.
- ✗
Specifying column names instead of SELECT *.
Why it's wrong here
Explicitly selecting only the required columns is a performance best practice, not an anti-pattern. It reduces the amount of data read from storage and transferred over the network, leading to faster execution times and lower costs. This is the recommended approach for writing production queries.
- ✗
Utilizing multi-cluster warehouses for concurrency.
Why it's wrong here
Using multi-cluster warehouses is a recommended architectural pattern for scaling compute to meet fluctuating demand. It is the standard way to handle high concurrency without queueing. Describing this as an anti-pattern is incorrect, as it is a core feature designed for high-performance enterprise workloads.
- ✓
Using SELECT * in production application code.
Why this is correct
Selecting all columns is inefficient because it often retrieves data that is not needed for the business logic. This wastes I/O, increases memory pressure, and creates unnecessary network traffic. It also makes the application fragile, as changes to the table schema can break existing code unexpectedly.
- ✗
Implementing materialized views for performance.
Why it's wrong here
Materialized views are a legitimate and powerful optimization tool in Snowflake. When used correctly for expensive, frequently executed queries, they significantly improve read performance. Labeling them as an anti-pattern is incorrect, as they are a key feature specifically provided to solve performance challenges.
About these practice questions
Courseiva writes every DEA-C02 question from scratch — 229 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This DEA-C02 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C02 exam.