SnowPro Advanced: Data Engineer (DEA-C02) — Questions 151–225

An operator whose output rows massively exceed its input rows indicates an unintended row explosion, usually from a fan-out join or an incorrect grouping key. Because the explosion happens before the aggregation, the downstream operator must process every duplicated row, so fixing the cardinality growth is the only action that addresses the actual bottleneck.

The Economy policy only starts a new cluster if it estimates there is enough work to keep it busy for at least six minutes. This delay is intended to prevent the frequent starting and stopping of clusters to save credits, but it directly results in longer queue times for users.

Clustering the table on the filtered column co-locates similar values in the same micro-partitions, so the optimizer can prune micro-partitions that cannot contain matching rows. This directly reduces bytes scanned by the TableScan, which is the reported bottleneck. Because the table is large and the filter is highly selective, clustering yields the greatest reduction in I/O and improves performance without resizing the warehouse.

Clustering keys physically sort data by the specified columns, which aligns with the PARTITION BY clause of window functions. This reduces the need for repartitioning during query execution, as data is already co-located. For large tables with frequent window function queries, clustering can significantly improve performance by minimizing data movement.

Applying masking policies to the base table ensures that even if the underlying data is accessed via a view, the masking rules are still enforced. Using a secure view provides the added benefit of hiding the underlying schema metadata, making this the most secure approach for external data sharing.

Cloned tables share micro-partitions with the source, and Snowflake bills each distinct micro-partition once regardless of how many tables reference it. The 500 GB of new loads and 100 GB of updated rows create partitions unique to the clone, which are billed. The unchanged 5 TB remains shared and is charged once across both tables, so the incremental storage attributable to the clone reflects only the changed data.

UNDROP restores the table object along with the retention configuration it had at the time of the drop. Since FACT_ORDERS had a 14-day retention period when it was dropped, the restored table retains that same 14-day setting, and the remaining Time Travel window continues from the original drop timestamp.

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.

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.

PARSE_JSON is the specific function built to transform a string containing JSON into a hierarchical VARIANT object. This enables the use of Snowflake's native path-based querying, which is essential for transforming complex, nested data into relational structures during the pipeline process.

LATERAL FLATTEN is designed to explode semi-structured arrays into multiple rows, preserving the parent row's context. Applied to event_data:items, it yields one row per element, making product_id and quantity accessible via VALUE:product_id and VALUE:quantity. This directly satisfies the requirement to produce one row per product per event while repeating the event timestamp and user ID.

When FLATTEN is applied to an empty array, it generates zero output rows. With a LATERAL join, the outer row is eliminated because there are no matching rows from the flattened side. This is standard SQL behavior for lateral joins with empty sets. The engineer must be aware that empty arrays cause data loss unless handled with an outer join or a default value.

ON_ERROR = 'CONTINUE' allows the load to proceed past rows that fail validation, skipping them while loading valid rows. Snowflake records the rejected rows and their error details, which can be retrieved from the COPY command's result or from the load history. This matches the requirement to continue loading and capture rejects for analysis.

When a table is not clustered on the filter column, the micro-partition metadata may show wide ranges for that column, causing the optimizer to scan many partitions to find matching rows. Without clustering, data is organized by insertion order, which often leads to overlapping values and poor pruning. This directly explains the high number of partitions read.

A Cartesian join warning indicates that the join is producing a Cartesian product, typically because the join condition is missing, always true, or uses incorrect columns. The engineer should verify that the join predicate correctly matches the date_key in the fact table to the primary key in the date dimension. Fixing the join condition eliminates the Cartesian product, reducing the result set to the intended matches and dramatically improving performance. This is the direct solution to the warning.

Temporary tables exist only within a session and are dropped automatically when the session ends. They have minimal Time Travel and no Fail-safe coverage, so they do not incur Fail-safe storage costs. For session-scoped scratch data, temporary tables provide the lowest protection and lowest storage overhead, matching the engineer's cost-reduction goal.

COPY INTO parallelizes work across the files in a stage, so a single monolithic file forces one thread to process the entire load. Splitting the data into many smaller compressed files lets the warehouse distribute file processing across nodes, dramatically increasing throughput. The 100-250 MB compressed target size is the recommended range that balances parallelism against per-file overhead, making this the most direct fix for the described bottleneck.

This mode is used for 'dry runs'. It scans the staged files and reports any issues (like delimiter problems or type mismatches) that would cause the load to fail. This helps prevent corrupted or partial loads and is a best practice when setting up new data movement tasks.

Snowflake's data classification service uses a set of predefined system tags, such as 'SNOWFLAKE.CORE.EMAIL' or 'SNOWFLAKE.CORE.PHONE', to identify and label columns. These tags help categorize data automatically, allowing administrators to apply governance policies based on these labels rather than manually tagging every column in the database.

PARSE_JSON interprets a string as a JSON document and returns a VARIANT. It is specifically designed to convert JSON-formatted strings into Snowflake's semi-structured VARIANT type, allowing access to nested fields using colon notation. This is the correct function for this transformation.

COPY INTO with a stage target unloads table or query results to that stage, and specifying TYPE = PARQUET writes columnar files. Using the @my_stage/path/ syntax directs output to the named internal stage, which the other team can then access according to their granted privileges.

By applying tags to databases or schemas, organizations can monitor usage and associate costs with specific projects or departments. This visibility is essential for cost management, as it allows leadership to identify high-cost areas and optimize resource consumption based on actual business value and departmental activity.

Access History captures column-level access information for queries, including which columns were read and written. It is designed for auditing and compliance, and the data is retained for 365 days. This meets the requirement of tracking column access and retaining for one year. Therefore, Access History is the correct feature.

With DATA_RETENTION_TIME_IN_DAYS set to 0, Snowflake retains no historical versions for the table, so queries using AT or BEFORE clauses return no prior state. Because the batch job truncated the table, the only recovery path would have been Time Travel, which was effectively disabled by the zero-day retention setting.

Remote spilling happens when intermediate results exceed local memory and are written to remote storage. Increasing the warehouse size allocates more memory per node, allowing the aggregation to process larger partitions in memory and reducing the need to spill remotely. This directly addresses the memory constraint without altering the query. While other options might have indirect benefits, scaling up the warehouse is the most straightforward way to mitigate remote spilling caused by memory-intensive aggregations.

The Result Cache stores the output of previous queries. When a subsequent query matches the original exactly, Snowflake retrieves the results from the cache instead of running the query again. This avoids the need to spin up or utilize a warehouse, resulting in zero cost for that specific operation.

With DATA_RETENTION_TIME_IN_DAYS set to 1, the table is recoverable via Time Travel for only 1 day after being dropped. After 2 days, the Time Travel period has expired, and the table has moved to Fail-safe, which does not support UNDROP. Thus, the UNDROP fails because the table is no longer in Time Travel.

Transient tables are capped at a maximum Time Travel retention of 1 day regardless of account edition. Setting 14 days exceeds that ceiling, so the statement is rejected. The 1-day value is the only valid nonzero retention for transient objects, which is why the attempt to extend it fails in this scenario.

Snowpark lets the engineer write transformations in Python, which can be unit tested with standard frameworks before deployment. The code can be stored in a Git repository and executed as a stored procedure or in a Snowpark session. This aligns with the requirement for version control and testability, unlike pure SQL scripts. It also handles semi-structured data natively through DataFrame APIs.

If the COPY INTO statement includes a transformation such as $1:column::NUMBER or TO_NUMBER($1), the string is converted to a number, which drops leading zeros. The resulting numeric value is then implicitly cast back to VARCHAR for the target column, but the zeros are already lost. Removing or changing the transformation preserves the original string.

The optimizer relies on statistics to estimate cardinality and choose join strategies. If statistics are stale or missing, it may incorrectly estimate the broadcasted table as small and choose a broadcast join. Refreshing statistics on the join columns gives the optimizer accurate row counts, allowing it to select a more appropriate join strategy, such as a hash join with a smaller build side, reducing spilling.

CSV is a natively supported format for unloading data from Snowflake. It is widely compatible with most analytical and spreadsheet applications, making it a standard choice for exporting data to external systems that require simple, tabular data structures for their own processing or analysis requirements.

A secure view hides its definition and the underlying base table from consumers, and sharing the view rather than the table restricts partners to exactly the rows the view exposes. This is the standard pattern for row-level curation in a share and prevents consumers from querying the base table directly.

When new data is inserted or existing data is updated in a way that creates overlapping ranges in the clustering key, the clustering service must work harder. If the rate of these changes is high, the service consumes many credits while struggling to keep the table well-organized.

This table-level property is essential for allowing DML operations like COPY to automatically perform DDL changes. Without this setting, even if the COPY command identifies new columns, it will fail or ignore them because it lacks the authorization to alter the underlying table schema during the data loading process.

Search optimization service is designed to accelerate selective point lookups and queries that filter on high-cardinality columns and return a small number of rows. It builds a search access path that allows the optimizer to quickly locate matching rows without scanning all micro-partitions. This directly addresses the slow dashboard query pattern.

Row access policies are designed to filter rows based on conditions such as the current role. By defining a policy that returns TRUE only when the role is COMPLIANCE_OFFICER or when ACCOUNT_STATUS is not 'DELINQUENT', the engineer enforces exactly the required row-level security. This is the standard Snowflake mechanism for row-level filtering.

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.

LATERAL FLATTEN is designed to explode arrays into multiple rows. Using it with the input parameter pointing to the items array will produce one row per element in the array. This allows each line item to be represented as a separate row, and the parent order ID can be included via the correlation. This is the standard and most efficient way to flatten arrays in Snowflake.

Search optimization service is specifically designed to accelerate highly selective point lookups and equality predicates on columns, even when the table is not clustered on those columns. It maintains a search access path that allows the optimizer to find matching micro-partitions quickly. For a UUID equality filter returning a single row, this is the intended use case and provides significant latency improvement.

When you reduce the retention period, historical data older than the new period becomes inaccessible and is eventually purged. However, storage costs may not decrease immediately because the data must first be purged from storage, which can take time. Additionally, Fail-safe still applies for 7 days after the retention period, so storage costs for Fail-safe may persist. This is the correct behavior.

TO_CHAR converts a date or timestamp to a string using the specified format. Using 'YYYY-MM' returns the year and month in the desired format. This is the standard function for formatting dates in Snowflake and is the simplest way to achieve the required transformation.

The performance of a Row Access Policy depends heavily on how well Snowflake can prune data. If the mapping table is large or poorly structured, the policy evaluation can become a bottleneck. Keeping mapping tables lean and indexed via clustering ensures that the join logic does not force unnecessary data scanning.

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.

Snowflake Data Classification includes built-in semantic categories such as SSN that automatically identify and tag columns containing Social Security numbers. By applying classification to the entire PROD database, the steward can scan all schemas and tables without manual effort. The system assigns tags like SNOWFLAKE.CORE.SSN to matching columns, enabling automated governance.

This is the core architecture of Snowpipe auto-ingest. By integrating with S3 Event Notifications, Snowflake receives an asynchronous signal the moment a file is written. This allows the serverless compute resources to spin up only when there is work to do, providing a highly scalable and cost-effective ingestion path.

Because the table name is already in use by the new table, the UNDROP operation will error out. This prevents the unintentional destruction of the new table. The user is required to intervene—either by renaming the new table or dropping it—before the older, dropped version can be successfully brought back.

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.

Most time-series data is loaded as it is generated, meaning rows with similar dates are grouped into the same micro-partitions. Snowflake’s metadata tracks the min/max values of every column in each partition, so a range filter on a naturally ordered column can effectively prune most of the table.

The FLATTEN function is the standard table function used to explode arrays or objects into separate rows. When applied with a LATERAL join, it maintains the relationship between the root object and the nested collection, providing the necessary tabular structure for further SQL-based data manipulation.

By only processing the rows that have changed (inserts, updates, or deletes), change tracking enables high-performance incremental transformations. This avoids the need to process the entire source table repeatedly, which would otherwise lead to excessive compute costs and increased latency in the data pipeline.

This expression uses TRY_TO_NUMBER on the VARIANT value, which attempts to convert it to a number regardless of whether it is stored as a number or a string. If the conversion fails (e.g., missing key or non-numeric string), it returns NULL, and COALESCE replaces it with 0. This directly handles both numeric and string types and missing keys, making it the correct and efficient solution.

A materialized view stores the pre-computed aggregation, so the recurring dashboard query can read a much smaller, pre-aggregated result set instead of scanning the full fact table. Snowflake automatically maintains the materialized view as base data changes, and the optimizer can rewrite queries to use it, dramatically reducing scan time for this repetitive aggregation pattern.

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.

The ACCOUNT_USAGE.TAG_REFERENCES view contains a comprehensive record of all tag associations across the entire Snowflake account, including those in different databases. This view is the standard for account-wide governance and auditing, allowing for a single query to return all objects tagged with specific compliance-related labels.

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.

Snowpipe maintains a load history for each pipe, recording which files have been processed. When new files are detected, it checks the history and skips any file that has already been loaded. This prevents reprocessing of old files, even if they are in existing partitions. This is the default and correct behavior, requiring no additional configuration.

TRY_TO_DATE attempts to convert the string to a date and returns NULL if the conversion fails, rather than raising an error. This is ideal for handling invalid date strings gracefully. It also returns NULL for NULL inputs. The function is designed for exactly this scenario, where data quality is uncertain and you want to avoid query 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.

On Enterprise Edition, permanent tables support DATA_RETENTION_TIME_IN_DAYS up to 90, so 30 is valid and provides the required 30-day Time Travel window for recovery from accidental deletions or modifications. After Time Travel expires, the table also benefits from the 7-day Fail-safe period, adding further protection. This configuration directly satisfies the stated requirement.

Clustering on join keys allows Snowflake to perform partition pruning during the join. If both tables in a join are clustered on their respective join keys, the optimizer can significantly reduce the amount of data read and processed, as it only needs to compare partitions with overlapping key ranges.

Snowpipe auto-ingest uses cloud messaging services like SQS to detect new files immediately upon arrival. This serverless feature charges based on the actual compute used for ingestion, making it ideal for frequent, small file arrivals where low latency is critical and managing a dedicated virtual warehouse would be expensive and inefficient.

Snowflake's native object tagging allows custom tags to be created with CREATE TAG, applied to columns, tables, and other objects, and governed through the APPLY TAG privilege on the tag. By granting APPLY TAG only to TAG_ADMIN, the engineer ensures only that role can assign or change the tag on objects. This directly satisfies both the categorization and the access-control requirements using a single governance feature.

Snowflake Data Classification automatically scans tables and views, identifies sensitive data using native and custom classifiers, and assigns system tags such as SNOWFLAKE.CORE.PRIVACY_CATEGORY. This directly addresses the need to discover and tag personal data across many databases with minimal manual effort. The system tags can then be used to drive masking policies, making this the correct approach.

A pipe with AUTO_INGEST = TRUE relies on the cloud provider's event notifications to trigger COPY INTO when new files land on the stage, so ingestion happens automatically with no external scheduler required. The storage integration supplies the credentials and the event notification ARN is tied to the pipe, which matches the requirement of loading frequent event files with minimal orchestration overhead on the Snowflake side.

Tag-based masking policies allow you to associate a masking policy with a tag. When the tag is applied to a column, the masking policy is automatically enforced. This ensures consistent protection across all tables that use the tag, and new columns tagged later are automatically covered. It meets the requirement for consistent masking based on a tag.

Standard Edition supports a maximum Time Travel retention of 1 day for permanent tables. The default is 1 day, and it can be set to 0 to disable Time Travel. Values above 1 day, such as 90, require Enterprise Edition or higher. This is why a 90-day setting is invalid on Standard Edition and must be corrected or the account upgraded.

Centralizing file format settings allows for consistent data ingestion behavior across all pipelines. If a delimiter or format requirement changes, updating the single object applies the change globally, which prevents bugs and inconsistencies that would occur if each COPY command had its own, potentially divergent, file format parameter definitions.

Snowflake allows masking policies to be attached to system tags, including those created by Data Classification. This enables automatic masking of columns that are classified as sensitive, such as those tagged with SEMANTIC_CATEGORY = 'PII'. The masking policy is then enforced whenever the tag is present on a column.

If the policy references the mapping table without fully qualifying it (e.g., using just TENANT_MAPPING instead of DB.SCHEMA.TENANT_MAPPING), the resolution depends on the session's current database and schema. Users with different session contexts might resolve the table incorrectly, leading to no matching rows. This is a common pitfall. Fully qualifying objects in policies ensures consistent behavior regardless of session settings, which is why this is the most likely cause.

Zero-copy cloning creates a new database that shares the same micro-partitions as the source. Initially, no additional storage is consumed. When data in the clone is modified, new micro-partitions are created for the changes, and storage is charged for those new partitions. The original micro-partitions remain shared until they are no longer referenced by either database. This makes the clone instantly available with minimal storage overhead.

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.

Transient tables support Time Travel with a maximum retention of 1 day. Setting DATA_RETENTION_TIME_IN_DAYS to 1 enables recovery within 24 hours and, because transient tables have no Fail-safe period, no additional storage costs are incurred after the retention period expires. This directly meets the requirement.

Increasing the warehouse size provides more memory per node. Since a single query can only execute within the memory limits of the nodes assigned to it, a larger warehouse provides the necessary resources to hold intermediate join states in RAM, effectively eliminating the need to spill to remote storage.

Set-based transformations leverage Snowflake's query optimizer to execute complex logic in parallel across the cluster. This is the foundation of efficient ELT, as it pushes the transformation logic down to the data rather than moving data to the logic, maximizing performance and minimizing latency.