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

Questions 6–10

1. 6application · medium

   A data engineer is optimizing a transformation that joins a large fact table with a small dimension table. The query currently takes 10 minutes. The engineer notices that the fact table is not clustered on the join key. Which optimization would most likely improve the query performance?

   • ACreate a materialized view on the dimension table
   • BAdd a cluster key on the fact table's join column
   • CUse the RESULT_SCAN function to cache the result
   • DUse a larger warehouse
   Reveal Solution Discussion Select an answer first
2. 7application · medium

   A team runs a nightly ELT pipeline that loads data into Snowflake and then runs a series of transformations. The pipeline currently uses a Medium warehouse with auto-suspend set to 5 minutes. The transformations are CPU-intensive and the warehouse is often idle between steps. The team wants to reduce cost without impacting the pipeline's completion time. Which configuration change would best achieve this?

   • AIncrease warehouse size to Large
   • BReduce auto-suspend to 1 minute
   • CUse a smaller warehouse and increase auto-suspend to 10 minutes
   • DEnable multi-cluster with auto-scaling
   Reveal Solution Discussion Select an answer first
4. 8application · medium

   A data engineer is designing a pipeline that loads a large CSV file from an external stage into a Snowflake table. The file is 5 GB and contains millions of rows. The load currently takes 20 minutes on an X-Small warehouse. The engineer wants to reduce the load time without changing the warehouse size. Which approach would be most effective?

   • ASplit the file into multiple smaller files (e.g., 100-250 MB each)
   • BUse the VALIDATION_MODE option to pre-validate the data
   • CEnable automatic clustering on the target table
   • DUse the COPY INTO command with the FILE_FORMAT option to specify CSV parsing
   Reveal Solution Discussion Select an answer first
5. 9foundation · easy

   A data engineer wants to improve the performance of a Snowflake pipeline that loads large CSV files from an external stage into a table. Which technique is most directly aimed at improving load performance through parallelization?

   • AIncreasing the timeout for the COPY command.
   • BSplitting the files into multiple smaller files and using a larger warehouse.
   • CUsing a single large file to reduce the number of COPY statements.
   • DDisabling automatic clustering on the target table.
   Reveal Solution Discussion Select an answer first
6. 10foundation · easy

   A data engineer notices that a pipeline's queries are frequently timing out. Which warehouse configuration parameter is most directly responsible for controlling how long a query can run before being aborted?

   • ASTATEMENT_TIMEOUT_IN_SECONDS
   • BAUTO_SUSPEND
   • CWAREHOUSE_SIZE
   • DMAX_CONCURRENCY_LEVEL
   Reveal Solution Discussion Select an answer first