DEA-C02 Flashcards — Free SnowPro Advanced: Data Engineer Study Cards

The QUALIFY clause

Filtering on window functions requires a mechanism that executes after the window calculations are performed. The QUALIFY clause allows engineers to filter results directly in the SELECT statement without nesting logic inside a subquery or a Common Table Expression. This significantly improves query readability and can lead to internal optimizations by the Snowflake query optimizer during the execution phase.

Warehouse provisioning time.

When a warehouse is in a suspended state, the first query submitted triggers a 'cold start' as Snowflake provisions compute resources. This process involves allocating virtual nodes, which takes a few seconds, leading to latency. This is a common occurrence in environments using auto-suspend to save costs. Understanding this behavior is vital for performance tuning, as engineers often confuse this infrastructure provisioning time with actual query execution performance issues.

The table is transient, which limits retention to one day.

The exhibit shows the table is a TRANSIENT table. Snowflake limits the retention period for transient tables to a maximum of one day. Because the table was created with the TRANSIENT keyword, it does not support long-term Time Travel, and the DATA_RETENTION_TIME_IN_DAYS parameter cannot be set to a value higher than 1. To support 30 days, the table must be recreated as a permanent table.

Apply a masking policy to the columns and grant the APPLY MASKING POLICY privilege.

Dynamic Data Masking allows policies to be applied to columns based on the user's role without duplicating data. By leveraging masking policies, you maintain a single source of truth while ensuring sensitive information is protected at query runtime. This method is highly scalable as a single policy can be assigned to multiple columns across different tables, simplifying maintenance and ensuring consistent security posture across the enterprise.

Load the files into a table with a VARIANT column to store the semi-structured data.

Using a single Snowpipe with a variant column is the most scalable pattern for schema drift or varied structures. By loading raw data into a VARIANT column, the engineer avoids the overhead of managing multiple pipe definitions. This allows downstream transformations using SQL views or dynamic tables to parse the specific keys as needed, ensuring the ingestion process remains decoupled from schema changes while maintaining a low-latency data pipeline architecture.

It identifies the underlying tables that provided the data, even if the user queried a view.

The ACCESS_HISTORY view is a powerful tool for tracking data movement and consumption. The 'base_objects_accessed' field specifically records the underlying source tables (the 'base' objects) that provided the data, even if the user queried a view or a series of nested views. This is vital for accurate compliance reporting and understanding the ultimate source of truth.

Using Snowpipe for serverless continuous ingestion.

Snowpipe is highly efficient for continuous ingestion because it only charges for the actual compute used during the load. For small, frequent batches, it avoids the cost of keeping a warehouse running 24/7. This cost-effectiveness makes it the ideal choice for real-time ingestion patterns, ensuring that the organization pays only for the resources consumed during data movement, rather than paying for idle compute cycles in a traditional, scheduled warehouse setup.

Transient Tables

Transient tables are designed specifically for data that needs to persist beyond a single session but does not require the 7-day Fail-safe protection offered by permanent tables. By excluding Fail-safe, Snowflake reduces the storage footprint and associated costs, making them ideal for intermediate ETL stages where data can be easily re-created if lost.

It allows costs to be associated with specific business projects.

Object Tagging allows organizations to assign costs to specific business units, projects, or applications by labeling the tables and schemas they use. This is critical for internal showback/chargeback models. By tracking resource usage at the tagged object level, finance teams can accurately map Snowflake costs to specific departments, encouraging better resource management and accountability across the organization's data footprint.

17 days, including 10 days of Time Travel and 7 days of Fail-safe.

For permanent tables, the data protection lifecycle consists of the Time Travel period followed by the Fail-safe period. After the table is dropped, it remains in Time Travel for 10 days (allowing UNDROP). Then, it automatically moves into Fail-safe for another 7 days. Once both periods expire (17 days total), the data is purged.

A Vectorized Python UDF

Snowflake's Vectorized Python UDFs allow for high-performance processing by passing batches of rows as Pandas DataFrames or Series. This reduces the overhead associated with calling the function for every individual row. For data transformations involving heavy computational logic or libraries like NumPy, vectorized UDFs are significantly more efficient than standard scalar UDFs which process data row-by-row.

Tables without an explicit retention setting will inherit the new 30-day period.

Parameters in Snowflake follow a hierarchy where object-level settings override schema-level settings, which override account-level settings. If a table does not have its own specific retention period set, it inherits the new value from the schema. However, if a table was explicitly created with its own retention period, that setting remains unchanged.

It provides instant access to query results without compute costs.

The Result Cache is a managed feature that stores the output of a query for a period (typically 24 hours). If an identical query is submitted later, Snowflake returns the cached results immediately without using any compute resources. This is a critical performance and cost optimization tool, as it eliminates the need to re-process large datasets for common, repeated queries, providing near-zero latency and zero credit consumption.

Create a secure view and apply masking policies to the base table columns.

The best practice is to combine a Secure View with a Dynamic Data Masking policy. The Secure View provides a clean abstraction layer, while the Masking Policy ensures that the data itself remains protected regardless of how the view is queried. This combination allows for precise data sharing while adhering to strict compliance standards, protecting the organization from data leaks during the sharing process.

The Load History metadata maintained by the COPY command.

Snowflake's COPY command and Snowpipe both utilize 'load history' metadata to ensure that each unique file is only loaded once into a specific table. This history is maintained for 64 days. If duplicates are appearing, it is often because the files were modified (changing their checksum) or the engineer is using a method that doesn't check the history, such as using a FORCE=TRUE parameter.

Permanent table

Fail-safe is exclusive to permanent tables. It provides a non-configurable 7-day window after Time Travel expires, during which Snowflake Support can recover data. Transient, temporary, and external tables do not receive Fail-safe coverage, so only a permanent table satisfies the requirement described in the scenario.

The clone consumes no additional storage initially, and data changes in DEV_DB will consume additional storage only for the changed micro-partitions.

Zero-copy cloning creates a new database that shares the original micro-partitions without duplicating data. The clone is immediately available and consumes no additional storage at creation. Subsequent changes in either the source or the clone create new micro-partitions, and storage is billed only for those new partitions. This approach is efficient for creating development or testing environments.

It remains 5.

When a table is dropped, its metadata and parameter settings are preserved. When the UNDROP command restores the table, it retrieves the original configuration, including the previously set DATA_RETENTION_TIME_IN_DAYS value of 5. This ensures that object-level policies remain consistent after recovery, allowing the Data Engineer to maintain control over historical data retention settings without needing to reconfigure them after accidental deletions.

Ensuring the mapping table is small and uses columns that allow for effective pruning.

When Row Access Policies involve joins to mapping tables, Snowflake's optimizer must execute these checks efficiently to avoid full table scans. Using a memoizable function or ensuring the mapping table is small and clustered correctly helps the pruning process. Governance at scale requires balancing strict security logic with the underlying query performance to ensure user experience is maintained.

Implement a Row Access Policy that uses a mapping table to determine the user's department and filters rows accordingly.

Row Access Policies are designed to filter rows based on user attributes or mapping tables. They attach to tables and enforce filtering at query time, making them ideal for dynamic row-level security. Secure views can be bypassed if base table access is granted, and masking policies only affect column values, not row visibility. Thus, a Row Access Policy is the correct solution.

A masking policy applied to the CREDIT_CARD_NUMBER column.

The requirement is to dynamically mask a column based on the user's role while preserving the original data. A masking policy attached to the column evaluates at query time and can return different values depending on the role. It does not alter stored data, and it can show only the last four digits for non-privileged roles while revealing the full value for PAYMENT_ADMIN. This is the standard Snowflake method for column-level dynamic data masking.

MAX_FILE_SIZE = 67108864 and SINGLE = FALSE

MAX_FILE_SIZE sets an upper bound on each unloaded file and takes a byte value, so 64 MB must be written as 67108864. SINGLE = FALSE lets the unload split output across multiple files. Used together, they produce many files each capped at the requested size, which is what the downstream parallel consumer requires.