DEA-C02 Data Transformation Practice Question
Exhibit
ALTER TABLE sales_data ADD SEARCH OPTIMIZATION ON EQUALITY(customer_email);
Refer to the exhibit. A data engineer applies search optimization to a table to speed up point lookups. How does this transformation impact the storage and maintenance costs for the table?
⚠ Common exam trap
Many candidates incorrectly believe that the search optimization service only incurs standard table storage costs without utilizing background serverless compute resources for ongoing maintenance.
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
✓
It increases storage costs and uses serverless compute for maintenance.
The Search Optimization Service is a background process that creates a persistent search access path (index-like structure). It significantly improves performance for point lookups but comes at a cost. The service consumes both storage for the search access path and serverless compute for the background maintenance as data in the base table is modified over time.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
It has no impact on storage as it uses the existing metadata of the micro-partitions.
Why it's wrong here
Search optimization creates a separate, dedicated data structure to track the location of values. This structure requires additional storage space beyond the standard micro-partition metadata. For large tables with many unique values, the storage overhead can be substantial and must be factored into the total cost of ownership.
- ✓
It increases storage costs and uses serverless compute for maintenance.
Why this is correct
When search optimization is enabled, Snowflake builds a search access path. Maintaining this path as the table is updated requires serverless compute resources, which are billed to the account. Additionally, the search access path itself occupies storage, adding to the monthly storage bill for that specific database object.
- ✗
It only increases compute costs during the initial build phase, with no ongoing costs.
Why it's wrong here
The Search Optimization Service is dynamic. Every time DML operations (INSERT, UPDATE, DELETE) are performed on the base table, the search access path must be updated to remain accurate. This ongoing maintenance is handled by Snowflake in the background and results in recurring serverless compute charges whenever the data changes.
- ✗
It reduces storage costs by compressing the base table micro-partitions more efficiently.
Why it's wrong here
Search optimization does not modify the compression or structure of the base table's micro-partitions. Its purpose is to provide an auxiliary path for finding data, not to optimize the underlying storage of the table itself. In fact, it always increases the total storage footprint because of the auxiliary data generated.
Quick reference
Cloud Service Model Comparison
| Model | You Manage | Provider Manages | Examples |
|---|---|---|---|
| IaaS | OS, runtime, apps, data | Hardware, hypervisor, networking | EC2, Azure VMs, GCP Compute Engine |
| PaaS | Apps and data | OS, runtime, middleware, hardware | Elastic Beanstalk, Azure App Service |
| SaaS | Data and settings only | Everything else | Microsoft 365, Salesforce, Workday |
| FaaS / Serverless | Function code only | Infra, scaling, runtime | Lambda, Azure Functions, Cloud Run |
| CaaS | Containers and apps | Kubernetes, OS, hardware | EKS, AKS, GKE |
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.