Optimize Data Pipelines DEA-C02 Practice Questions (Page 3)

Questions 11–15

1. 11application · medium

   A data team uses Snowflake's TASK and STREAM objects to build a continuous data pipeline. They notice that the pipeline sometimes falls behind, and they want to proactively detect when tasks are running longer than expected. Which monitoring approach would best help them detect this issue?

   • AQuery TASK_HISTORY to check the SCHEDULED_TIME and COMPLETED_TIME for each run
   • BSet up a resource monitor on the warehouse
   • CEnable email notifications on the task
   • DUse the QUERY_HISTORY view to look for queries with high bytes scanned
   Reveal Solution Discussion Select an answer first
2. 12foundation · easy

   A data engineer is troubleshooting a Snowflake pipeline that has become slower over time. The pipeline loads data from external storage into staging tables, then runs transformations. Which symptom is a classic indicator of queuing as a bottleneck?

   • AQueries show high CPU utilization on the warehouse nodes.
   • BQueries spend significant time in the 'Queued' state before starting to execute.
   • CThe pipeline's external stage has high network latency when listing files.
   • DThe COPY INTO command reports a high number of rows rejected due to data quality issues.
   Reveal Solution Discussion Select an answer first
4. 13foundation · easy

   A Snowflake pipeline is experiencing queuing during peak load times, but the warehouse is idle during off-peak hours. Which configuration change would best balance throughput and cost?

   • AEnable auto-suspend and auto-resume on the warehouse.
   • BIncrease the statement timeout to allow queries to wait longer.
   • CDisable auto-resume to prevent unexpected queries from starting the warehouse.
   • DSet the warehouse to always run at the maximum size.
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   A Snowflake pipeline performs a large transformation that joins two fact tables. The query runs slowly, and the execution plan shows that one table is being scanned multiple times. Which bottleneck does this symptom most directly indicate?

   • AInefficient transformation logic
   • BNetwork latency between the warehouse and storage
   • CInsufficient warehouse size
   • DResource contention from other workloads
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   Which partitioning strategy is most effective for improving query performance in Snowflake?

   • AStoring data in external tables partitioned by date.
   • BCreating separate tables for each partition value.
   • CUsing a large number of micro-partitions regardless of query patterns.
   • DDefining a clustering key on frequently filtered columns.
   Reveal Solution Discussion Select an answer first