SnowPro Advanced: Data Engineer (DEA-C02) — Questions 76–150

Snowflake automatically detects the number of files in the specified path and distributes them across available nodes in the virtual warehouse. This parallel processing capability is fundamental to Snowflake's architecture, enabling rapid ingestion of large datasets without the need for complex, manual partitioning logic on the part of the engineer.

UNDROP TABLE restores the table with its original properties, including the DATA_RETENTION_TIME_IN_DAYS setting. The retention period is not changed by the drop or undrop operation. The restored table will continue to have a 5-day Time Travel retention, allowing further recovery if needed. This is the correct behavior.

With DATA_RETENTION_TIME_IN_DAYS set to 1, only the most recent 24 hours are queryable through Time Travel. A 40-hour-old version is outside that window, and Fail-safe cannot be queried by users at all. Because retention cannot be extended retroactively, the historical version is effectively unrecoverable without Snowflake Support intervention in a genuine disaster case.

In Snowflake Enterprise Edition, permanent tables support a maximum Time Travel retention of 90 days, configurable via DATA_RETENTION_TIME_IN_DAYS. This allows recovery of historical data for up to 90 days using AT or BEFORE clauses. This setting is ideal for critical tables requiring long recovery windows, but it increases storage costs because historical versions are retained.

The `PIVOT` clause is the native Snowflake operator for rotating data rows into columns. It is highly optimized and significantly more readable and maintainable than manual aggregation methods. It allows for flexible reporting by easily transforming datasets to meet the specific requirements of various business intelligence tools.

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.

ACCOUNT_USAGE contains views that track every query, access event, and configuration change. This data is essential for governance audits, as it provides a transparent and immutable history of what happened in the account, allowing for detailed investigation and reporting required by modern data compliance standards.

Transient tables support up to 1 day of Time Travel on Enterprise Edition, which satisfies the 24-hour recovery need, and they do not incur Fail-safe storage. This makes them well suited for high-volume, short-lived data such as raw clickstream events. The absence of Fail-safe directly reduces storage cost, matching the team's objective.

Snowflake's Search Optimization Service can be configured to index specific fields within a VARIANT column. This allows the engine to quickly identify which micro-partitions contain the specific nested value, avoiding a full scan of the semi-structured data and providing high-performance lookups on very large datasets.

Data Classification automatically scans tables and identifies sensitive columns, applying system tags. After reviewing the results, the engineer can create masking policies and attach them to the tagged columns. This sequence leverages automation and ensures policies are applied where needed.

LATERAL FLATTEN is designed to expand a VARIANT array into multiple rows, one per element. Because it is a lateral join, it can reference the VARIANT column from the source table and preserve all other columns, making it ideal for this scenario.

MAX_FILE_SIZE caps the size of each output file using a byte value, and 67108864 bytes equals 64 MB. SINGLE = FALSE allows Snowflake to produce multiple files across the available parallelism, which suits the consumer's need for many small files. This combination directly meets both stated requirements for the unload.

Clustering physically orders rows by the chosen column so that a selective filter can skip micro-partitions whose value ranges fall outside the predicate. When the profile shows most partitions being scanned for a selective filter, clustering on that filter column is the direct way to raise the pruning ratio and cut the scanned data volume.

A Row Access Policy is a schema-level object that can be added to a table to filter rows based on conditions evaluated at query time. It can reference a mapping table like USER_DEPARTMENT to dynamically determine the user's department and restrict rows. This meets the requirement of dynamic filtering without altering the table structure. It is the correct feature for row-level security in Snowflake.

A transient table with a 1-day retention still supports Time Travel within that window, so the engineer can query the pre-truncation version using AT (TIMESTAMP => ...) and re-insert the rows. This is the reliable in-place recovery method. Note the retention window must not have elapsed, and the TIMESTAMP must fall inside the retention period.

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.

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.

Because cloning creates a metadata-only copy, it is nearly instantaneous and consumes no additional storage until data is modified. This makes it the most efficient way to test complex data transformations against production-like data without the cost and time associated with traditional physical data duplication.

By storing the pre-computed output of a query, materialized views save compute resources for repetitive, complex queries. This reduces the need to run the underlying logic every time the view is accessed, significantly improving read performance and reducing the overall credit consumption for heavy analytical workloads.

The BEFORE clause with a STATEMENT identifier reads the table as it existed immediately before the specified statement executed, which is exactly the pre-delete snapshot. Selecting from that snapshot and inserting the missing rows re-establishes the deleted data while preserving rows written after the delete. This works because the table's 30-day retention window still covers the twenty-minute-old delete, and the query ID can be retrieved from the Query History view.

The SINGLE = TRUE option in a COPY INTO <location> command directs Snowflake to unload the data into a single file, rather than multiple files. This is useful when the target system expects a single file. However, it may impact performance for very large datasets because the unload is not parallelized.

The GRANTS_TO_USERS view in the ACCOUNT_USAGE schema contains a record of all role grants to users, including the role name, grantee name, and grant date. Querying this view with a filter on the ROLE column for 'SYSADMIN' will return the required audit information. It is the correct and efficient source for this data.

A many-to-many relationship on join keys causes each row in the first table to match multiple rows in the second, leading to an explosion of output rows. Identifying this allows the engineer to decide if the data should be aggregated before the join to ensure a one-to-many relationship.

Duplicate loads in Snowpipe often occur when the same file triggers multiple events or when the load history is not correctly tracked. Snowflake maintains a load history for each pipe for 14 days; if a file is loaded within that period, it is skipped. However, if events are duplicated (e.g., due to at-least-once delivery from Pub/Sub or S3 event notifications), the pipe may attempt to load the same file again. Ensuring the notification integration is configured to minimize duplicates and that load history is not cleared can prevent this. Additionally, using the COPY_HISTORY function to monitor can help identify issues.

The MERGE command allows for conditional logic based on match status, which is ideal for deduplication and incremental updates. By defining specific matching criteria, it ensures that target table data remains accurate without creating duplicate rows, which is a common requirement in ETL/ELT pipelines.

Local spilling happens when an operation exceeds the memory allocated to a warehouse node. Increasing the warehouse size adds more compute resources and memory per node, which can accommodate larger intermediate results and reduce or eliminate local spilling. This directly addresses the memory constraint without altering the query logic. Other options, such as changing join order or disabling broadcast, may not resolve the underlying memory issue if the data volume per node remains high.

FLATTEN is a table function that expands a VARIANT array into one row per element, exposing the value in a VALUE column. It is the standard Snowflake construct for unnesting arrays and is exactly what is needed to transform the `tags` array into rows for downstream aggregation.

Snowpipe maintains a load history keyed on the file name and path. When a file with an identical name and path is seen again, it is treated as already loaded and ignored, even if its contents changed. Regenerating files with the same names in the same stage location will therefore not trigger a reload without manual intervention.

Each file processed by the COPY command involves metadata overhead and network handshakes. With 10,000 tiny files, the warehouse spends more time managing the file list and opening connections than actually moving data, leading to very poor utilization of the compute resources and longer load times.

The 'PATTERN' parameter acts as a filter for files in the stage. If the pattern is overly restrictive or incorrect, Snowflake may successfully read the stage but find zero files that match, resulting in a successful command execution that loads no data. This is a common configuration oversight in pipeline development.

Because the table scan dominates the query profile, the query is likely reading more data than necessary. Implementing clustering keys or using partition pruning can reduce the volume of data scanned, directly targeting the primary cause of the high execution time shown in the profile.

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.

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.

Frequent single-row or tiny INSERT statements create many small micro-partitions, which inflates metadata overhead and forces the optimizer to scan numerous fragments. Accumulating rows and loading them in larger batches produces fewer, better-sized micro-partitions, directly reducing the fragmentation that the profile is revealing as the cause of the slowdown.

Snowflake zero-copy cloning creates a new table that shares the same micro-partitions as the source without copying data. Storage is only charged for new micro-partitions created when the source or clone is modified after cloning. This makes the initial storage impact effectively zero, which is the defining benefit of the CLONE keyword for large tables.

VALIDATION_MODE = RETURN_ERRORS parses the staged files and returns the rejected rows with their error messages without loading anything into the target table. It is exactly the dry-run mechanism for inspecting bad rows and confirming data quality before a real load. Because nothing is committed, it also avoids polluting load metadata or creating duplicates, which fits the pre-production validation goal.

Clustering keys cause related rows to be co-located in the same micro-partitions, so equality predicates on the clustering column allow the optimizer to skip partitions that cannot contain matches. The other column lacks that physical ordering, so the scan must read all partitions. This explains the difference in performance between the two equality filters and is the most likely cause.

ACCESS_HISTORY is specifically designed for auditing data access at the column level. It records the relationships between users, the queries they run, and the tables or columns those queries accessed. This is the primary tool for governance teams to generate compliance reports and prove data security.

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.

COPY INTO supports column-level transformations in the SELECT clause of the statement. Applying TO_TIMESTAMP($1:event_time::STRING) converts the ISO-8601 string to a TIMESTAMP_NTZ as the data is loaded, eliminating a separate post-load step. This is the intended mechanism for inline type conversion during ingestion of semi-structured files.

ASOF JOIN is designed for time-series lookups where the fact timestamp must fall on or after a dimension timestamp. By matching on the dimension key and using MATCH_CONDITION, it efficiently finds the most recent valid dimension row without scanning all validity windows, which is exactly the SCD lookup pattern needed here.

The total lifespan of the data after a drop is the sum of its configured Time Travel (10 days) and the system-defined Fail-safe (7 days). Only after this cumulative 17-day window does Snowflake release the storage and permanently delete the micro-partitions from the underlying cloud storage service.

A permanent table in Enterprise Edition supports up to 90 days of Time Travel, so 60 is valid. Time Travel and Fail-safe are sequential windows: after the Time Travel period expires, Snowflake retains the data for an additional 7 days of Fail-safe, which is only accessible through Snowflake Support. The total recoverability is therefore 60 days of self-service Time Travel followed by 7 days of Fail-safe.

Query History in the ACCOUNT_USAGE schema captures detailed information about every query executed, including the query text, the user who ran it, and the execution time. This is the standard Snowflake feature for auditing query activity and meets the requirement to log queries against the sensitive table.

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.

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.

Tag-based masking policies are enforced based on the tag assignment and the masking policy's conditions, independent of the user's privileges on the tag itself. The analyst lacks USAGE on the tag, so they cannot see the tag metadata, but the masking policy still applies to the column when queried. Thus the SSN appears masked.

A masking policy is a schema-level object that can be attached to a column and evaluates at query time. It can inspect the user's role and conditionally return a masked value, such as showing only the last four digits, while storing the original data unchanged. This directly satisfies the requirement for dynamic, role-based transformation without altering the underlying table data.

Parquet is a columnar format that applies per-column encodings such as dictionary and run-length encoding, which greatly shrink columns with many repeated values, and SNAPPY adds block compression on top. This yields the smallest transfer volume while remaining readable by common analytics and ML tools.

Snowpipe with AUTO_INGEST uses an S3 event notification delivered to an SQS queue that Snowflake manages, and the pipe's COPY statement loads each new file as it lands. This provides the low-latency, event-driven ingestion required for hundreds of small files per minute without scheduling batch jobs.

Transient tables have a default and maximum Time Travel retention of 1 day. Because 30 hours exceeds that 24-hour window, the historical data is no longer available for Time Travel. The engineer must design the reload process to retain data longer, or use a permanent table with a longer DATA_RETENTION_TIME_IN_DAYS setting.

Fail-safe is a Snowflake feature that provides a 7-day window after the Time Travel retention period ends. During this period, data can only be recovered by Snowflake Support. It is not user-accessible via SQL. This applies to permanent tables that have Time Travel enabled. Transient and temporary tables do not have Fail-safe. This statement correctly describes the duration and access method for Fail-safe recovery.

A masking policy is applied to a column and can conditionally mask its values based on the user's role. By creating a masking policy that returns the actual salary for FINANCE_ANALYST and a masked value for others, the engineer can meet the requirement. This is the standard Snowflake feature for column-level security and dynamic data masking.

Queued Overload Time represents the amount of time a query spent waiting for warehouse resources because the warehouse was already at maximum capacity. A high value here is a direct indication that the warehouse is under-provisioned for the current level of concurrency and needs to scale out or up.

A task executes with the permissions of its owner. If the owner role does not have the necessary INSERT, UPDATE, or DELETE permissions on the target table, the MERGE operation will fail during execution. This is a standard security constraint enforced by Snowflake.

Snowflake follows a 'bottom-up' precedence rule for object tagging. A tag explicitly applied to a lower-level object, such as a table, will always take precedence over the same tag inherited from a higher-level container like a schema or database, allowing for specific overrides of general governance policies.

Snowpark supports UDFs that can be written in Python, Java, or Scala and executed within Snowflake. This allows custom transformation logic to be applied to data at scale, which is essential for complex feature engineering and data cleaning tasks that go beyond built-in SQL functions.

Snowpipe auto-ingest for GCS relies on Google Cloud Pub/Sub to deliver event notifications. The bucket must have a Pub/Sub topic and a subscription that pushes messages to the Snowflake-provided notification URL. Without a functioning subscription, Snowflake receives no events, and files are never queued—matching the silent behavior described. This is the correct component to verify.

When an identical query is re-run and the underlying tables have not changed, Snowflake returns the persisted result from the query result cache without recomputing it. That is why repeat executions are near-instant while the first execution after the cache expires or data changes must run fully, matching the observed morning pattern.

Data Classification scans table columns and applies system tags like SNOWFLAKE.CORE.SSN based on semantic and pattern matching. By creating a custom classification profile that includes SSN detection, the team can automatically tag all relevant columns account-wide. This satisfies the requirement for automatic detection and tagging without manual intervention.

STRIP_OUTER_ARRAY removes the outer brackets of a top-level JSON array so that each element is treated as a separate row during loading. This is exactly the behavior needed when files contain an array of objects and each object should map to a row. It is a property of the JSON file format used by COPY INTO.

Snowflake uses AES-256 encryption for all data at rest, and this service is enabled by default for all accounts. This transparent process ensures that data is always protected within the database without requiring any user-managed configuration or maintenance, simplifying security administration for data engineers and system architects.

Tags allow for the classification of data assets, enabling administrators to identify sensitive information and apply policies programmatically. This is a fundamental component of data governance, as it provides a structured way to manage and protect data across large, complex schemas without requiring manual column-level configuration.

Transient tables never have Fail-safe coverage, and setting DATA_RETENTION_TIME_IN_DAYS to 0 removes Time Travel as well. The table remains available for normal queries, but no historical data can be recovered. This matches the engineer's cost-saving goal without affecting current data access, making it the correct outcome for STAGE_EVENTS.

Zero-copy cloning creates a new table that shares the same underlying micro-partitions as the source, so no data is physically duplicated at creation time. Storage is only added when one side diverges and new micro-partitions are written. This makes cloning near-instant and cost-efficient until changes occur.

AUTO_INGEST = TRUE enables Snowpipe to automatically ingest new files as they arrive in the external stage, based on event notifications from the cloud provider. For Google Cloud Storage, you configure a notification integration that uses Google Cloud Pub/Sub to send event messages to Snowflake. This setup provides near-real-time ingestion with minimal latency and no manual intervention, making it the correct choice.

This is the most effective way to optimize ingestion. By reducing the total number of files, you decrease the overhead associated with listing and opening files in cloud storage. Larger files allow Snowflake to utilize its parallel processing capabilities more effectively, leading to much faster and more cost-efficient data movement.

Snowflake evaluates Row Access Policies first to determine which rows a user can access, then applies Masking policies to the columns within those allowed rows. This sequential order is essential for maintaining strict security boundaries, as it prevents users from performing operations on rows they are not authorized to see.

Every time Automatic Clustering or an UPDATE statement modifies data, new micro-partitions are written. With 90 days of Time Travel, every single version of those partitions created over the last three months is preserved. In a high-activity environment, this historical data can easily dwarf the size of the active data.

Access History provides a detailed audit of which columns and tables were accessed by a query. This is essential for governance, as it allows administrators to identify if unauthorized roles are attempting to access sensitive data, providing the foundation for automated security alerting and compliance reporting.

Snowflake's hierarchical parameter model ensures that objects inherit settings from their parent containers unless explicitly overridden. By updating the schema, any table within it that has not been specifically configured with its own retention period will now follow the schema's new 30-day Time Travel policy.

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.

ELT allows you to load raw data quickly and perform transformations using Snowflake’s engine, which is highly optimized for parallel execution. This avoids costly data movement and allows you to scale compute resources on-demand to handle even the most intensive transformation workloads.

Workload separation isolates small queries from large ones, preventing large queries from monopolizing resources and causing queueing for small queries. By dedicating a warehouse to small, frequent queries, they can run with minimal queueing, while the other warehouse handles heavy workloads. This is a best practice for concurrency management and can be cost-effective if each warehouse is sized appropriately and auto-suspend is enabled.

The RETENTION_TIME of '1' indicates a 1-day Time Travel window. During this period, the metadata for the dropped table is preserved, allowing the UNDROP TABLE command to succeed. This is the only recovery mechanism available for transient tables before the data is permanently deleted.