Snowflake · Free Practice Questions · Last reviewed May 2026
30real exam-style questions organised by domain, each with the correct answer highlighted and a plain-English explanation of why it's right — and why the others are wrong.
A data engineer needs to filter the results of a complex analytical query based on the result of a window function. The query calculates a rolling average of sales per region and should only return rows where the current sale exceeds that average. Which SQL clause is most efficient for this transformation?
The WHERE clause
The HAVING clause
The QUALIFY clause
The QUALIFY clause is specifically designed to filter the results of window functions after they have been computed. It functions similarly to how HAVING works for aggregates, providing a clean syntax to remove rows that do not meet criteria. This reduces code complexity by eliminating the need for wrapping the primary query in a subquery.
The GROUP BY clause
A data engineer is implementing a Python User-Defined Function (UDF) to perform complex string manipulation. To optimize performance for a large-scale transformation, the engineer wants to ensure the UDF processes multiple rows in a single call. Which type of UDF should be implemented?
A scalar Python UDF with a loop
A Vectorized Python UDF
Vectorized UDFs define a handler that receives a batch of input rows as a Pandas object. This allows the Python code to utilize highly optimized vectorized operations, which can be orders of magnitude faster than scalar processing. It minimizes the context switching between the SQL engine and the Python interpreter during the transformation.
A Python User-Defined Table Function (UDTF)
A JavaScript UDF using the 'async' keyword
A data engineer is converting a column of strings into integers. Some rows contain non-numeric characters that would normally cause the query to fail. Which function should be used to return a NULL value instead of an error when a conversion is impossible?
AS_INTEGER()
TO_DECIMAL()
TRY_CAST()
TRY_CAST is the safest choice for transformations where data quality is uncertain. It attempts to convert the value to the specified data type, but if the conversion fails, it returns NULL instead of raising an exception. This ensures that a few bad records do not crash an entire batch processing job.
COALESCE()
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?
It has no impact on storage as it uses the existing metadata of the micro-partitions.
It increases storage costs and uses serverless compute for maintenance.
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.
It reduces storage costs by compressing the base table micro-partitions more efficiently.
To comply with privacy regulations, a data engineer must ensure that certain columns in a table are masked for unauthorized users during transformation. Which Snowflake feature provides a way to define re-usable masking logic that is automatically applied at query time?
Row Access Policies
External Tables with encrypted files
Dynamic Data Masking Policies
Dynamic Data Masking allows for the creation of policies that use SQL logic (like CASE statements) to determine how data should appear. When assigned to a column, the policy transforms the output for unauthorized roles (e.g., replacing a social security number with 'XXX-XX-XXXX') while keeping the raw data intact.
Secure Views with hardcoded filters
When performing a MERGE operation to update a large table, which factor most significantly impacts the performance of the transformation?
The number of columns included in the SELECT list of the source query.
The clustering of the target table on the join column used in the MERGE statement.
Clustering on the join column allows the query optimizer to prune partitions that do not contain matching keys. This significantly reduces the amount of data read from storage, which is the most expensive part of a MERGE operation on large datasets.
The size of the virtual warehouse, as larger warehouses always make MERGE operations faster.
The use of an explicit transaction block around the MERGE statement.
Want more Data Transformation practice?
Practice this domainA user complains that a dashboard query is slow during peak hours. The warehouse is configured with auto-suspend and auto-resume. What is the most likely cause of the latency observed during the initial execution?
The query result cache is full.
Warehouse provisioning time.
When a warehouse is suspended, the first query triggers the provisioning of compute resources. This latency is inherent to the cloud-native architecture of Snowflake. Once the resources are active, subsequent queries will run faster because the compute is already 'warm' and ready to process incoming execution requests.
The warehouse size is too small.
The metadata cache is invalidated.
Which of the following scenarios is most appropriate for using a Materialized View to optimize performance?
Frequently run queries involving complex joins and aggregations on static data.
Materialized views are ideal for frequently executed queries that involve intensive operations like complex joins and aggregations. Because the result set is stored and updated automatically as the base table changes, subsequent queries retrieve the result directly, drastically reducing compute costs and improving response times for end users.
Queries that are run only once a month.
Queries that access data from an external stage.
Queries that filter on non-deterministic functions.
A user runs a query twice in succession with no data changes. The second query completes in near-zero time. What feature is responsible for this performance?
Warehouse Local Cache.
Metadata Cache.
Query Result Cache.
The Query Result Cache is specifically designed to store and reuse the output of previously executed queries. When a query is repeated and the data remains unchanged, Snowflake retrieves the result directly, bypassing the execution process entirely and resulting in near-instant performance for the user.
Search Optimization Service.
Which of the following is the most cost-effective way to handle massive concurrent read-only queries?
Increase the warehouse size.
Use a multi-cluster warehouse with economy scaling.
Multi-cluster warehouses scale horizontally to handle high concurrency. The 'Economy' policy specifically optimizes for cost by being more conservative about adding new clusters, which is ideal for read-only workloads where some queuing is acceptable to save credits compared to the 'Standard' policy.
Enable query pruning.
Create materialized views for every user.
A table experiences performance degradation over time due to frequent DML operations (INSERT/UPDATE/DELETE). What is the most likely cause?
The metadata cache is exceeding its capacity.
Micro-partition fragmentation.
Frequent DML operations break the physical ordering within micro-partitions. As data is changed, the original clustering becomes scattered across many partitions, reducing the effectiveness of partition pruning. This forces the engine to scan more data than necessary to satisfy the same query, leading to significant performance degradation.
The warehouse cache is becoming corrupted.
The result cache is being constantly invalidated.
Which of the following is the best practice for using the query profile to identify performance bottlenecks?
Only analyze queries that take longer than one hour.
Start by identifying the operator with the highest time contribution.
The operator with the highest time contribution is the most significant bottleneck. By focusing on this node first, you address the primary source of latency. This approach ensures that your optimization efforts have the greatest possible impact on the total query duration and overall warehouse credit consumption.
Ignore spilling metrics if the query eventually finishes.
Focus primarily on the number of partitions scanned.
Want more Performance Optimization practice?
Practice this domainRefer to the exhibit. Why can the user not change the retention time of the table 'SENSITIVE_DATA' to 30 days?
The table is transient, which limits retention to one day.
Transient tables in Snowflake are designed for temporary data and support a maximum retention period of only one day. This architectural constraint prevents users from extending the Time Travel window beyond 24 hours, regardless of the Snowflake edition or account settings, ensuring efficient storage for short-lived, transient datasets.
The user lacks the OWNERSHIP privilege on the table.
The table is currently locked by a running query.
The table has change tracking enabled.
Which of the following describes the primary purpose of Snowflake's Fail-safe storage?
To allow users to query data state from up to 90 days ago.
To provide a 7-day recovery period for catastrophic system failures.
Fail-safe acts as a disaster recovery mechanism for critical system failures. If data is permanently lost beyond the reach of Time Travel, Snowflake Support can potentially recover data from the Fail-safe window. This 7-day window is mandatory, immutable, and ensures that data remains protected against unexpected infrastructure-level incidents.
To facilitate zero-copy cloning of large production databases.
To enable cross-region replication for high availability.
Refer to the exhibit. What is the DATA_RETENTION_TIME_IN_DAYS setting for the table after the UNDROP operation?
It reverts to the database default.
It remains 5.
The UNDROP operation restores the table to its exact state, including all defined parameters like DATA_RETENTION_TIME_IN_DAYS. Since it was explicitly set to 5 before the DROP command, that configuration is preserved in the metadata and reapplied upon restoration, ensuring no changes to the intended retention policy occur.
It reverts to the account default.
It is reset to 1.
Which of the following best describes the storage impact of zero-copy cloning a large table?
It immediately doubles the storage cost of the table.
It incurs no additional storage cost at the time of cloning.
Because zero-copy cloning is a metadata-only operation, no physical data is moved or copied. The new table references the same immutable micro-partitions as the source. Initial storage consumption is effectively zero, making this a highly efficient way to manage multiple environment versions without needing redundant data storage.
It creates a physical copy that only shares unchanged data.
It requires the user to re-cluster the clone to avoid costs.
What is the result of increasing the DATA_RETENTION_TIME_IN_DAYS from 1 to 5 days on an existing table?
All data from the last 5 days becomes immediately accessible.
The table begins retaining historical data for 5 days moving forward.
Increasing the retention setting tells Snowflake to hold onto data versions for the new, longer period starting from the time the parameter is updated. This allows for a longer Time Travel window for future data states, supporting the requirement to access historical data for analysis or recovery operations.
The storage cost remains the same as it was.
The change takes effect only after the next vacuum process.
Which Snowflake edition is required to support a 90-day Time Travel retention period?
Standard Edition.
Enterprise Edition.
Enterprise Edition and higher (including Business Critical) are specifically designed to support long-term Time Travel up to 90 days. This capability allows organizations to meet rigorous compliance and historical data access requirements that are not achievable within the standard 1-day retention limit of the entry-level edition.
Any edition supports 90 days by default.
Only the Trial Edition.
Want more Storage and Data Protection practice?
Practice this domainA data engineer needs to ensure that PII data in the 'SALES' table is obscured for non-admin users while maintaining original data types for downstream analytical models. Which approach provides the most scalable governance?
Create separate secure views for every user role requiring masked access.
Apply a masking policy to the columns and grant the APPLY MASKING POLICY privilege.
Applying a masking policy directly to columns provides a centralized way to enforce data governance. By granting the APPLY MASKING POLICY privilege to a governance role, you ensure that security policies are managed by the data security team rather than the database owners, following the principle of least privilege.
Use row-level security to filter rows containing PII data for authorized users.
Physically transform the data during the ETL process and store it in a new table.
An organization wants to classify their data to identify PII. Which feature should they use to automatically tag columns containing sensitive information?
Row Access Policies.
Data Classification.
Snowflake Data Classification is designed to discover and classify sensitive data like PII. It utilizes built-in system tags to label columns, which can then be used to trigger automated security controls, ensuring that sensitive data is protected according to organizational compliance standards without requiring extensive manual effort.
Dynamic Data Masking.
Query Profile.
Which object allows a data engineer to assign a security policy based on a user's geographical location attribute?
Masking Policy.
Row Access Policy.
Row Access Policies allow for conditional logic that incorporates context functions like CURRENT_USER or session attributes. This enables the implementation of fine-grained access control where rows are only visible if the user's location satisfies specific regulatory requirements or internal business rules defined within the policy's SQL expression.
Secure View.
Tag-based masking.
A company requires that data masking policies be applied automatically whenever a column is tagged with 'PII'. How can this be achieved?
Write a stored procedure to trigger on every DDL statement.
Use tag-based masking policies.
Tag-based masking policies allow you to define a masking policy and associate it with a specific tag. When the tag is applied to a column, the masking policy is automatically applied. This streamlines governance, reduces maintenance, and ensures consistency across large, evolving datasets in the enterprise.
Use a Row Access Policy with a conditional tag check.
Manually apply the masking policy every time a table is created.
Which governance tool allows an administrator to audit who accessed a specific table and when?
QUERY_HISTORY.
ACCESS_HISTORY.
ACCESS_HISTORY provides a detailed, audit-ready log of data access. It tracks which users accessed which tables, views, and columns. This is the standard tool for governance teams to verify compliance with data privacy regulations and ensure that only authorized roles are interacting with protected data assets.
OBJECT_DEPENDENCIES.
The Security Dashboard.
Which approach is most effective for managing governance policies across a large, multi-schema data warehouse environment?
Create policies within each individual schema.
Deploy policies to a centralized database and schema.
Centralizing governance objects enables a single source of truth for security policies. This simplifies the management of privileges and makes auditing significantly easier, as all policies are located in one place. It also allows for clear separation of duties between the security team and the data engineering team.
Use a single shared role for all policy management.
Manually recreate all policies in every environment.
Want more Data Governance practice?
Practice this domainA data engineer is configuring Snowpipe to ingest files from an S3 bucket. The bucket contains files with different schemas. Which approach allows the engineer to handle these variations without creating separate pipes?
Define multiple pipes pointing to the same stage with different pattern matching.
Use a schema-on-read approach by configuring the pipe to cast columns explicitly.
Load the files into a table with a VARIANT column to store the semi-structured data.
Loading into a VARIANT column provides the most flexibility for varied schemas. Snowflake automatically parses JSON, Avro, or Parquet structures into the variant type. This allows the ingestion process to proceed regardless of column additions or removals, deferring the structural parsing to the transformation layer.
Configure the pipe to use an external table instead of loading data into an internal table.
Which feature should be used to automate the loading of files from an S3 bucket into Snowflake as soon as they are uploaded, without manual intervention?
A scheduled task using the EXECUTE TASK command.
Snowpipe using event-based notifications.
Snowpipe is purpose-built for continuous, event-driven data loading. By integrating with cloud provider event notifications, it automatically detects new files and queues them for ingestion, providing near-real-time data availability for Snowflake users without requiring manual intervention or complex infrastructure monitoring to track file arrival times.
An external function that writes directly to the table.
A recurring COPY INTO command inside a stored procedure.
A data engineer needs to load data from an Azure Blob storage container into Snowflake. The organization requires a secure connection that does not use public endpoints. What should the engineer configure?
Configure an Azure Service Bus to relay the data to Snowflake.
Create a storage integration using an Microsoft Entra ID service principal and Private Link.
Storage integrations are the secure way to access cloud storage without hardcoding credentials. When combined with Azure Private Link, they establish a dedicated, private connection that ensures data movement stays within the cloud provider's backbone, satisfying security requirements by removing reliance on public internet traffic for data transfers.
Use a public URL for the stage but enable IP whitelisting.
Use an external stage with a SAS token that has a long expiration time.
A data engineer wants to load data from an S3 bucket and perform a transformation during the load. Which method is the most appropriate for this task?
Load the raw data into a temporary table, then run a task to transform it.
Use a COPY INTO command with a subquery that includes transformations.
Transforming data directly in the COPY INTO statement via a SELECT query allows for data casting, filtering, and column reordering before the data hits the target table. This ELT approach is the most efficient pattern in Snowflake, saving compute resources by reducing the number of write operations to disk.
Create a stream on the S3 bucket to trigger a transformation procedure.
Use an external function to transform the data before it reaches the stage.
Which of the following describes the purpose of a Snowflake storage integration object?
To cache frequently accessed data to improve query performance.
To provide a secure way to access cloud storage without hardcoding credentials.
Storage integrations allow Snowflake to use a secure trust relationship (e.g., AWS role) to access cloud storage. This avoids hardcoding sensitive credentials in stage definitions, improving security posture and simplifying credential management across different environments, which is essential for maintaining compliance and minimizing the risk of unauthorized credential exposure.
To create a physical partition of data within the cloud storage bucket.
To convert data into a proprietary Snowflake format for faster loading.
A data engineer is tasked with migrating small, frequent batches of data into Snowflake. Which feature is most appropriate to keep costs low while ensuring the data is processed continuously?
A virtual warehouse running 24/7 with a scheduled task.
Using Snowpipe for serverless continuous ingestion.
Snowpipe is a serverless feature that automatically scales compute resources based on incoming file volume. It is specifically designed to handle frequent, small batches without requiring an always-on warehouse, providing the most cost-effective and operationally efficient solution for continuous, real-time data movement requirements in a Snowflake environment.
Triggering a stored procedure every minute to check the storage stage.
Using a large multi-cluster warehouse to process the batches in parallel.
Want more Data Movement practice?
Practice this domainThe DEA-C02 exam has 60–90 questions and must be completed in 120 minutes. The passing score is 700/1000.
Scenario-based questions covering exam objectives with detailed answer explanations.
The exam covers 5 domains: Data Transformation, Performance Optimization, Storage and Data Protection, Data Governance, Data Movement. Questions are weighted by domain — higher-weight domains appear more on your actual exam.
No. These are original exam-style practice questions written against the official Snowflake DEA-C02 exam objectives. They are not copied from the real exam. Courseiva focuses on genuine understanding, not memorisation of braindumps.
Courseiva tracks your accuracy per domain and routes you toward weak areas automatically. Free, no account required.