SnowPro Advanced: Data Engineer (DEA-C02) — Questions 1–75

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.

Snowflake supports unloading from secure views, and the policies (masking, row access) defined in the view are enforced during the unload. This allows sensitive data to be masked in the output files. The unload operation executes the view's query, so the policies are applied. This is the correct and secure approach for the scenario.

Wrapping multiple DML operations in a BEGIN/COMMIT block guarantees atomicity. If any statement fails, the entire transaction can be rolled back, ensuring that either all target tables are updated correctly or none are, maintaining the integrity of the data across the whole schema.

Enterprise Edition raises the maximum Time Travel retention for permanent tables from one day to ninety days, enabling the 30-day window. Without Enterprise Edition, a 30-day retention setting would not be permitted, so verifying the edition is a prerequisite for meeting the requirement.

Zero-copy cloning creates a new table that initially references the same micro-partitions as the source, so no data is physically duplicated at creation. This satisfies the requirement of no additional storage up front. Storage diverges only when either table is modified, at which point new micro-partitions are written and the clone stops sharing those changed partitions.

UNPIVOT is a built-in Snowflake clause that rotates columns into rows. It is optimized for this operation and can handle millions of rows efficiently. By specifying the columns to unpivot and the output column names, the engineer can transform the wide table into a long format in a single statement.

When UPDATE operations rewrite micro-partitions, Snowflake writes new versions and retains the prior versions for the duration of the Time Travel retention period. With a 60-day window and repeated full-table updates, many historical versions accumulate, driving up Time Travel storage even though the current row count is unchanged.

Workload isolation is critical in Snowflake. By dedicating one warehouse to ETL and another to BI reporting, you prevent contention. This allows you to optimize each warehouse for its specific workload pattern, ensuring consistent performance for BI users regardless of how heavy the ETL jobs are.

For transient tables, the DATA_RETENTION_TIME_IN_DAYS parameter is capped at 1. Snowflake returns an error when you attempt to set a higher value. To get 30 days of Time Travel, the object must be a permanent table in an Enterprise Edition (or higher) account. The transient table type is the constraint here, not the account edition.

FLATTEN with LATERAL is designed to explode nested arrays in VARIANT data, producing one row per element. The colon notation accesses nested fields, and casting ensures the correct data type. This approach is efficient and standard for transforming semi-structured data in Snowflake.

The Result Cache is the only feature that can provide near-instantaneous results for identical queries by skipping all compute processing. It remains valid for 24 hours as long as the underlying data remains unchanged, making it perfect for repeated dashboard queries or frequent analytical tasks.

The SKIP_HEADER option specifies the number of header rows to skip at the beginning of each file. Setting it to 1 will skip the first row, which is the header. This is the correct way to handle CSV files with a single header row, ensuring that column names are not loaded as data.

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.

Data Classification samples a limited number of rows to infer the semantic category. If the sample does not contain a sufficient number of email addresses (e.g., due to low frequency or sampling randomness), the column may not be classified as EMAIL. This is a common reason for missed classifications. The engineer can adjust the sampling or manually tag the column. This option correctly identifies the sampling limitation as the likely cause.

Controlling MAX_FILE_SIZE splits the unload into many files, which increases parallel write throughput and keeps individual objects manageable. Writing into a structured prefix lets downstream readers prune by folder rather than scanning the whole export. Together these options satisfy both the throughput and the organization goals without requiring post-processing of the unloaded data.

Zero-copy cloning simply points to the existing micro-partitions of the source table. Because no data is moved, the operation is nearly instantaneous and free. Only when the data in the clone is modified (e.g., through a transformation) are new micro-partitions created, incurring incremental storage costs.

Dynamic Tables are designed for declarative pipelines. You define the transformation query, and Snowflake automates the materialization and incremental updates. This eliminates the need for manual scheduling or monitoring of complex task chains, greatly simplifying the data engineering workflow for continuous transformation tasks.

Local spilling occurs when the join operation's intermediate results exceed the memory available on a warehouse node. Increasing the warehouse size allocates more memory per node, allowing the join to process larger datasets in memory and reducing or eliminating local spilling. This directly addresses the memory constraint without altering the query. Other options like filtering or clustering might reduce data volume but do not directly increase the memory available to the join operation.

Extracting to cloud storage and then using COPY INTO with a storage integration is the standard Snowflake-native pattern when agents cannot be installed on the source. The storage integration avoids embedding long-lived cloud credentials in SQL, and COPY INTO provides parallel, resumable loading with load metadata tracking. It also keeps the Oracle host limited to outbound file transfers, which matches the stated network constraint.

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.

Scaling up to a larger warehouse increases the available RAM and local SSD space for each compute node. This allows the join operation to be processed entirely in memory or reduces spillage to local disk, completely avoiding the highly latent remote disk spillage that is currently degrading the query performance.

High average overlaps and a depth of 8.4 indicate that the values for C1 and C2 are scattered across many different micro-partitions. This overlap prevents efficient partition pruning during query execution, as the warehouse must open multiple partitions to find all relevant records for a specific key range.

Once a dropped table passes its Time Travel retention period, it enters Fail-safe for an additional 7 days. During Fail-safe, the table cannot be restored by the user with UNDROP; only Snowflake Support can recover it, and that recovery is not guaranteed. Since 20 days exceeds the 14-day retention, UNDROP is not possible.

Snowpipe loads files automatically as they arrive by executing a COPY INTO statement that can include transformations, column mappings, and error handling. It uses event notifications plus a queue with serverless compute, so no warehouse management is required. This matches the need for automatic, near real-time ingestion with minimal administrative overhead.

Database Replication is the native Snowflake solution for moving data between regions and accounts. It is highly automated, supports incremental updates, and ensures data consistency. It also provides the foundation for disaster recovery, making it the most robust choice for cross-region data movement requirements.

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.

When a file contains a single top-level array of objects, STRIP_OUTER_ARRAY removes that outer array and loads each element as a separate row. This is essential for making the nested records queryable as individual rows rather than one giant array value.

Snowpark's DataFrame API is designed for lazy evaluation, meaning transformations are not executed until an action is triggered. The operations are pushed down to Snowflake's engine for execution, leveraging its scalability. This is a core feature that enables efficient processing of large datasets without pulling data to the client.

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.

Data Classification scans tables and views to identify PII and other sensitive data. When enabled on a table, it automatically tags columns and can send notifications via email or integration when new sensitive columns are detected, ensuring ongoing protection.

Secure views provide a layer of obfuscation where the view definition and internal query structure are hidden from all users except those with specific ownership privileges. This prevents users from reverse-engineering the logic used to create the view, which is a key requirement for secure multi-tenant or external data sharing.

Clustering keys align the physical data storage with the query filter patterns. This allows Snowflake to skip micro-partitions that do not contain the data matching the filter criteria. This reduces I/O significantly, which is the primary bottleneck for massive table scans, leading to immediate performance improvements.

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.

When AUTO_INGEST is set to TRUE, Snowpipe uses event notifications from the cloud storage service (e.g., S3, Azure Blob Storage, GCS) to detect new files. These notifications are sent to a queue that Snowflake monitors, triggering the pipe to load the new files. This enables near real-time ingestion.

LAG is designed to access a previous row within a window. Partitioning by customer_id and ordering by sale_date ensures the previous sale for each customer is correctly identified, and Snowflake's optimizer can leverage clustering or partitioning to reduce data movement, making it efficient for large tables.

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.

A larger warehouse provides more memory per node and more nodes, allowing the aggregation to process higher-cardinality groups without exceeding memory limits. This directly reduces local spilling because the hash table can fit in memory. Scaling up is a standard response when the Query Profile shows local disk spilling in an aggregation step and the query logic cannot be changed.

Query History captures all queries executed, including the user, role, and SQL text. It can be used to attribute data usage to specific users and roles. While it does not directly record column-level access, it provides a comprehensive audit trail of query activity. Combined with other features, it helps meet the requirement for auditable access.

When filtering on a low-cardinality column with only 5 distinct values, the query will typically return a large percentage of the table (e.g., 20% per value). Snowflake's micro-partition pruning is ineffective because most partitions contain all statuses. Thus, a full table scan is unavoidable and is the expected behavior. Other optimizations like clustering or search optimization do not help. The engineer should accept that the scan is necessary unless the query can be rewritten to filter on a more selective column.

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.

Scheduled zero-copy clones provide independent copies that reference shared micro-partitions initially, costing little until data diverges. If the source is modified or dropped, a clone can serve as a recovery point. This complements Time Travel by giving the team a self-service restoration option that does not depend on Snowflake Support, supporting the stated goals.

Even with the small table as the build side, the probe side or other concurrent operations may exceed memory, causing remote spilling. Increasing warehouse size adds memory per node, which can eliminate the spill by allowing the join to process more data in memory. This is a direct way to address memory pressure without changing the query.

Manual refresh working while auto-ingest fails points squarely at the event notification path. Snowflake exposes a notification channel for the pipe, and the S3 bucket must be configured to send object-created events to it. If that wiring is absent or points to the wrong channel, files arrive in the bucket but Snowflake is never told, which matches the observed behavior exactly.

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.

Raising DATA_RETENTION_TIME_IN_DAYS extends the window for future changes. Historical versions already purged under the previous 1-day setting are not restored. This accurately reflects how the retention parameter applies going forward without retroactively recovering expired data, which is the correct behavior for SALES_ARCHIVE.

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.

Defining tasks with an 'AFTER' clause creates a DAG, allowing Snowflake to manage the execution order and dependencies automatically. This is the recommended approach for orchestrating complex transformation pipelines within the database, providing native support for success/failure triggers and clear visibility into the entire workflow.

Increasing the warehouse size provides more memory and compute resources per node, which can reduce or eliminate local disk spilling during aggregation. Spilling occurs when the aggregation's working set exceeds available memory. A larger warehouse has more memory, allowing the aggregation to be processed in-memory. This directly addresses the spilling issue and is a straightforward optimization.

The 'base_objects_accessed' column performs a recursive look-through of views to identify the original source tables. This ensures that even if sensitive data is hidden behind multiple layers of views, the security administrator can still track exactly which underlying physical tables were accessed by the user.

In Snowflake Standard Edition, the maximum Time Travel retention for permanent tables is 1 day. Setting DATA_RETENTION_TIME_IN_DAYS to 1 enables recovery of the table if it is dropped within that 1-day period. This is the minimum setting that provides recovery capability while keeping storage costs low. It meets the requirement to recover the table and minimizes storage overhead.

Row access policies and masking policies operate independently and both are enforced. The row access policy filters the result set to rows where REGION matches CURRENT_REGION(), while the masking policy on BALANCE transforms the value to NULL for roles outside FINANCE. The analyst therefore sees only their region's rows, and every BALANCE in those rows is NULL. This is the expected combined behavior when both policy types are attached.

Clustering the table on these two columns will physically organize the micro-partitions. This enables excellent partition pruning, ensuring that the system only reads the partitions containing data for the specified region and date range, significantly reducing the I/O cost and improving the query execution speed.

DataFrame.where() is an alias for filter() in Snowpark. After joining with DataFrame.join(), calling where() applies the filter condition to the joined DataFrame. This is functionally equivalent to using filter() and is a valid method for applying conditions in a Snowpark transformation pipeline.

Dividing data into smaller files allows the virtual warehouse to utilize all available compute cores for parallel ingestion. Using a larger warehouse provides more nodes and threads, which significantly decreases the overall time required to load large datasets by distributing the I/O and processing load across the entire cluster.

PrivateLink enables a private connection between the customer's cloud network and Snowflake by using the cloud provider's backbone network. This avoids traversing the public internet, satisfying security and compliance requirements for sensitive data movement while providing stable, high-bandwidth connectivity for large-scale data ingestion and egress tasks.

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.

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.

Snowflake keeps a record of every file successfully loaded into a table. The COPY command checks this history by default and skips files that have already been processed. If this is bypassed (e.g., by changing file names or using FORCE=TRUE), the same data will be ingested again, resulting in duplicates.

An internal stage is storage managed by Snowflake and does not require an external cloud bucket. Files can be uploaded with the PUT command from a local filesystem or network share accessible to the client, then loaded with COPY INTO. This matches the requirement for a Snowflake-managed stage without external cloud storage configuration.

TAG_REFERENCES is the account usage view that lists all tag references, including the tag name, tag value, and the object (such as a column) to which the tag is applied. It is the correct source for auditing tag assignments across the account.

The PARTITION BY clause allows engineers to dynamically create a directory structure in the external stage based on column values. This improves the performance of downstream applications that only need to read specific subsets of data and helps maintain a clean, navigable data lake environment for long-term storage.

Initial cloning only creates new metadata entries. As long as the micro-partitions remain identical between the source and the clone, no new storage is consumed. Only when DML operations create new micro-partitions will the storage usage diverge, and Snowflake will bill for the unique data blocks.

Without clustering, micro-partitions can contain rows spanning wide date ranges, so a seven-day filter cannot prune much. Clustering on DATE co-locates similar dates into the same micro-partitions, enabling the optimizer to skip partitions outside the range. Automatic Clustering keeps this ordering as new data arrives, directly reducing bytes scanned for the daily query.

Dynamic tables allow for complex transformations involving joins, aggregations, and window functions. They automatically handle the incremental refresh logic, removing the need for manual task orchestration and stream tracking, making them the ideal choice for declarative data transformation pipelines within the Snowflake ecosystem.

Dynamic Tables are specifically designed to handle incremental changes, including updates to existing records and late-arriving data. The system automatically manages the refreshes based on the lag target, ensuring that the target table remains consistent with the source data without manual intervention.

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.

Window functions perform memory-intensive operations like sorting. If the dataset exceeds the memory capacity of the current warehouse size, spilling occurs. Scaling up to a larger warehouse increases the memory available to the individual nodes, allowing the sort operation to complete in RAM without disk involvement.

To run Data Classification, the role must have USAGE privileges on the database and schema, as well as SELECT on the table. Without USAGE, the role cannot access the objects to perform the classification. This privilege is a prerequisite and is typically granted to the role that will execute the classification process. Therefore, this action is required.

A masking policy is the native Snowflake column-level security object designed for this exact scenario. Creating it with CREATE MASKING POLICY and attaching it via ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY makes the policy evaluate on every query of the SSN column, returning the raw value only to roles listed in the policy body (such as PII_ADMIN) and a redacted value to everyone else. No application SQL changes are needed.

Transient tables persist until explicitly dropped and can be seen by multiple users and sessions, fulfilling the requirement. They support Time Travel up to one day but lack the 7-day Fail-safe period, which significantly lowers storage costs for large-scale intermediate data that does not need disaster recovery.

UNDROP relies on Time Travel to restore a dropped table, and the recovery window equals the table's retention setting. With 5 days of retention, the table was recoverable for 5 days after the drop. At 6 days, that window has closed, so UNDROP fails. This is why timely recovery action matters.

The table has a 14-day Time Travel retention. A deletion that occurred 20 days ago is outside that window, so historical data for that point is no longer available to Time Travel. The earlier successful recovery does not extend the retention period. The team would need a longer retention setting or an external backup strategy.

When one side of a join is small, broadcasting it to every node lets each node join its local fact rows without shuffling the large table. This converts a costly redistribution of the large input into a cheap replication of the small input, which is exactly the pattern the profile is hinting at as the bottleneck.

Effective partition pruning means the query engine can identify and ignore irrelevant micro-partitions. Seeing a low number of partitions scanned compared to the total available count in the table is the primary metric indicating that the filtering logic is successfully narrowing the data access scope.

Permanent tables are the only table type covered by Fail-safe. After the Time Travel retention period elapses, a 7-day Fail-safe window applies, during which Snowflake Support can assist with recovery. This matches the engineer's requirement for post-retention protection, making a permanent table the correct choice.