DEA-C02 Practice Test 15 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a storage integration using an Azure AD service principal and Private Link.. Using Azure Private Link with a Snowflake storage integration is the recommended method for secure, private data movement. This architecture ensures that traffic between Azure and Snowflake stays on the private network, bypassing the public internet and meeting strict compliance requirements for enterprise security. This question highlights the importance of cloud networking fundamentals in the context of data engineering and secure cloud-to-cloud integrations.

Answer

Configure an Azure Service Bus to relay the data to Snowflake.

Answer

Use a public URL for the stage but enable IP whitelisting.

Answer

Use an external stage with a SAS token that has a long expiration time.

Question 2

A 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?

Accepted Answer

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.

Answer

Define multiple pipes pointing to the same stage with different pattern matching.

Answer

Use a schema-on-read approach by configuring the pipe to cast columns explicitly.

Answer

Configure the pipe to use an external table instead of loading data into an internal table.

Question 3

A data engineer is tasked with migrating a 10TB historical dataset from an on-premise HDFS cluster to Snowflake. The data is currently stored in compressed CSV files. What is the most efficient strategy to ensure optimal performance during the initial bulk load into a Snowflake table?

Accepted Answer

Split the CSV files into sizes of 100-250 MB and use the COPY INTO command with a Large or X-Large warehouse.. For massive bulk loads, Snowflake recommends leveraging the COPY INTO command with a properly sized virtual warehouse to maximize parallel processing. Pre-splitting files into the 100-250MB range allows multiple threads across the warehouse nodes to ingest data concurrently. This approach avoids the overhead of serverless compute and provides more control over the ingestion window and resource utilization compared to continuous loading methods.

Answer

Upload the files to an internal stage and use Snowpipe with auto-ingest enabled to process the backlog.

Answer

Execute a single COPY INTO command targeting the entire directory using an X-Small warehouse to minimize credit consumption.

Answer

Use the Snowflake Web Interface (Classic UI) to upload the files directly into the target table in 50MB chunks.

Question 4

Which function is best suited for converting a string representation of a JSON object into a VARIANT type within a transformation query?

Accepted Answer

PARSE_JSON(). The PARSE_JSON function is the standard Snowflake tool for casting string data into the VARIANT format. Once in the VARIANT format, the data can be queried using path notation (e.g., col:field), enabling powerful transformations on nested or semi-structured data. This is a foundational operation in data ingestion pipelines where raw data often arrives as strings in CSV or Parquet files.

Answer

TO_VARIANT()

Answer

TRY_CAST(data AS VARIANT)

Answer

TO_JSON()

Question 5

An engineer needs to ensure that a transformation pipeline handles 'late-arriving' data in a streaming context. Which feature is most effective?

Accepted Answer

Using Dynamic Tables with a configured lag target.. Snowflake's Dynamic Tables allow for declarative data transformation pipelines that automatically handle data state, including updates and late arrivals. By defining the lag, the engine manages the re-processing required to integrate new data into the final result set. This eliminates the need for manual handling of late data, which is historically a significant pain point in traditional ETL pipeline design.

Answer

Using a manual stored procedure to scan for missing data based on timestamps.

Answer

Implementing a complex Python task to buffer all data in a queue before processing.

Answer

Overwriting the entire target table with every execution to ensure data integrity.

Question 6

Which of the following scenarios is most appropriate for using a Materialized View to optimize performance?

Accepted Answer

Frequently run queries involving complex joins and aggregations on static data.. Materialized Views in Snowflake are best suited for queries that are frequently executed, computationally expensive, and rely on stable, non-volatile data. By pre-computing the result set and storing it, Snowflake avoids the overhead of repeated aggregation or complex joins. This is essential for dashboarding or reporting where the same transformation is run repeatedly, saving both compute credits and time while ensuring the underlying data remains consistent with the base table.

Answer

Queries that are run only once a month.

Answer

Queries that access data from an external stage.

Answer

Queries that filter on non-deterministic functions.

Question 7

Which Snowflake feature is best suited for transforming data that is already loaded and requires periodic, complex SQL transformations without managing manual scheduling or streams?

Accepted Answer

Dynamic Tables. Dynamic Tables provide a declarative approach to data transformation. Users define the query that represents the transformation, and Snowflake manages the refresh frequency, dependencies, and incremental updates automatically. This reduces the administrative burden compared to managing manual Tasks and Streams. It is the modern standard for building ELT pipelines where the user describes the desired state, and the system ensures the target reflects the source over time.

Answer

Materialized Views

Answer

Stored Procedures

Answer

External Tables

Question 8

A Data Engineer needs to transform semi-structured JSON data loaded into a VARIANT column named 'raw_data'. The goal is to flatten the 'items' array into individual rows while preserving the 'order_id' from the root level. Which function is the most efficient choice for this transformation?

Accepted Answer

FLATTEN. The FLATTEN function is specifically designed to transform semi-structured data into a relational format by producing a lateral view of array elements. By using it in a LATERAL join, the engineer can correlate the parent 'order_id' with each exploded array element effectively. This is a critical pattern in Snowflake for normalizing JSON structures before downstream analytics, ensuring that hierarchical data becomes queryable by standard SQL BI tools.

Answer

JSON_EXTRACT_PATH_TEXT

Answer

OBJECT_CONSTRUCT

Answer

ARRAY_TO_STRING

Question 9

A 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?

Accepted Answer

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.

Answer

The query result cache is full.

Answer

The warehouse size is too small.

Answer

The metadata cache is invalidated.

Question 10

A query is slow because it is scanning a massive table. The filters are on columns that are not currently clustered. What is the most immediate step to improve performance?

Accepted Answer

Add a clustering key on the filter columns.. When dealing with massive table scans, the most immediate improvement comes from reducing the volume of data read. If the filter columns aren't clustered, adding a clustering key is the most direct way to inform the engine which partitions can be skipped. This is a foundational performance technique in Snowflake, allowing the database to prune partitions based on the filter criteria, thereby reducing I/O and accelerating query execution without needing to change existing query SQL code.

Answer

Use the SEARCH OPTIMIZATION service.

Answer

Rewrite the query to use an INNER JOIN.

Answer

Scale down the warehouse to a smaller size.

Question 11

A company is using a Multi-cluster Warehouse (MCW) with a 'Standard' scaling policy. Users report that during peak morning hours, query queuing occurs briefly before a new cluster starts. What is the effect of changing the scaling policy to 'Economy' in this scenario?

Accepted Answer

Queuing will increase because the warehouse waits longer to start a new cluster.. The Economy scaling policy prioritizes credit savings over immediate performance by waiting until there is enough consistent load to keep a new cluster busy for six minutes. In this scenario, switching to Economy would likely increase queuing times because the system becomes more conservative about spinning up additional compute resources compared to the Standard policy.

Answer

Queuing will decrease because the Economy policy optimizes cluster distribution.

Answer

Performance will remain identical but the total credit consumption will decrease.

Answer

The warehouse will automatically scale up to a larger T-shirt size.

Question 12

Refer to the exhibit. The 'sales' table is very large and not clustered. Which action will provide the most significant performance improvement for this query?

Accepted Answer

Define a clustering key on (region, sale_date).. Since the query filters by 'region' and 'sale_date', performance is bottlenecked by the need to scan large amounts of irrelevant data. By clustering the table on 'region' and 'sale_date', Snowflake will organize the micro-partitions so that they are logically grouped by these columns. This allows the query engine to ignore huge swaths of data, drastically reducing the I/O requirement and accelerating the scan portion of the query.

Answer

Enable the Search Optimization Service on the sales table.

Answer

Increase the warehouse size.

Answer

Materialize the query result in a view.

Question 13

Which of the following describes the primary purpose of Snowflake's Fail-safe storage?

Accepted Answer

To provide a 7-day recovery period for catastrophic system failures.. Fail-safe is a critical component of Snowflake's data protection architecture, providing an immutable 7-day period that starts after the Time Travel window expires. It is intended for emergency recovery following system-level failures or catastrophic data loss. It is not designed for user-driven point-in-time recovery, which is the function of Time Travel, making Fail-safe a final, non-configurable safety mechanism for data durability.

Answer

To allow users to query data state from up to 90 days ago.

Answer

To facilitate zero-copy cloning of large production databases.

Answer

To enable cross-region replication for high availability.

Question 14

Refer to the exhibit. A data engineer runs this query to investigate clustering costs. The output shows high credit consumption but the 'Clustering Depth' of the table remains high. What is the most likely cause of this behavior?

Accepted Answer

The table is being continuously updated with data that overlaps existing ranges.. High credit consumption combined with high clustering depth usually indicates that the table is being frequently updated or appended with data that is significantly 'out of order' relative to the clustering key. This causes the Automatic Clustering service to continuously work to re-sort the data, but the constant influx of unsorted data prevents the depth from improving.

Answer

The clustering key is defined on a column with a 'Date' data type.

Answer

The warehouse used for clustering is too small for the table size.

Answer

The Search Optimization Service is conflicting with the clustering service.

Question 15

Which object allows a data engineer to assign a security policy based on a user's geographical location attribute?

Accepted Answer

Row Access Policy.. Row Access Policies are the correct mechanism here. By using a policy that evaluates the current user's session context—specifically looking at attributes like their IP address or a custom 'location' attribute—the policy can filter out rows that the user is not authorized to see based on residency or regional compliance regulations like GDPR, ensuring data sovereignty is upheld throughout the organization.

Answer

Masking Policy.

Answer

Secure View.

Answer

Tag-based masking.