DEA-C02 practice questions 101–110

1. Question 101 · 126 in bank

   A Data Engineer needs to optimize the performance and minimize the costs of a dynamic table pipeline in Snowflake. The Engineer wants to monitor and separate the dynamic table costs prior to transferring the table refresh processing to a shared virtual warehouse. How should the Engineer meet these requirements?

   • AUse a shared virtual warehouse to test the performance of the dynamic table refresh process and monitor the costs for all workloads.
   • BBefore moving the table, test the process on a dedicated virtual warehouse to establish a cost baseline.
   • CExecute the dynamic tables on multiple virtual warehouses simultaneously to distribute the load equally.
   • DSet up the dynamic tables directly on a production virtual warehouse to obtain real-time cost insights.

   Show answer

   Answer: B

   Next question

2. Question 102 · 126 in bank

   A company using Snowflake works with a third party that provides economic data. The third-party provider recently moved their data to Snowflake. How can the company access the third-party data in Snowflake with the LEAST amount of operational overhead?

   • AHave the data provider enable database replication so the company can create a replica of the provider's database.
   • BHave the data provider create a share object and add the company's Snowflake account to the share.
   • CHave the data provider create a listing on the Snowflake Marketplace so the company can request access to the data.
   • DHave the data provider create a new user and role specifically for the company so the company can log into the data provider's Snowflake account to access the data.

   Show answer

   Answer: B

   Next question

3. Question 103 · 126 in bank

   Select 3

   A Data Engineer needs to monitor data usage patterns, performance metrics, and query efficiency to optimize the data workloads in a Snowflake account. The Engineer needs insights into long-running queries, frequently accessed tables, and warehouse resource utilization while also tracking user activities and ensuring compliance with governance policies. Which combination of views should the Engineer use to meet these requirements? (Choose three.)

   • ASNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES view to track dependencies between objects.
   • BUse the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view to analyze query performance.
   • CUse the SNOWFLAKE.ACCOUNT_USAGE.SCHEMA_PRIVILEGES view to ensure compliance with data governance policies.
   • DUse the SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY view to analyze historical storage usage across databases.
   • EUse the SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY view to track access patterns.
   • FUse the SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY view to monitor warehouse resource consumption.

   Show answer

   Answer: BEF

   Next question

4. Question 104 · 126 in bank

   A Data Engineer is trying to improve the performance of a long-running query by clustering the base table. What information from the output of the CLUSTERING_INFORMATION system function will verify that the clustering improved query performance?

   • AThe average number of overlapping micro-partitions in the table is high.
   • BThe total number of constant micro-partitions in the table is high.
   • CThe average overlap depth of each micro-partition in the table is high.
   • DThe total number of constant micro-partitions in the table is low.

   Show answer

   Answer: B

   Next question

5. Question 105 · 126 in bank

   A tag named DEPARTMENT has two values, MARKETING and FINANCE. Which command will add a third value, ENGINEERING, to the tag?

   • AALTER TAG DEPARTMENT ADD ALLOWED_VALUES 'ENGINEERING';
   • BALTER TAG DEPARTMENT MODIFY ALLOWED_VALUES 'ENGINEERING';
   • CALTER TAG DEPARTMENT SET TAG DEPARTMENT = 'ENGINEERING';
   • DALTER TAG DEPARTMENT DROP ALLOWED VALUES 'MARKETING', 'FINANCE' ADD ALLOWED VALUES 'MARKETING', 'FINANCE', 'ENGINEERING';

   Show answer

   Answer: A

   Next question

6. Question 106 · 126 in bank

   A Data Engineer needs to monitor the quality of hr.tables.employee_dim using a Data Metric Function (DMF). Which DMF will return the percent of NULL values for the passport column in table employee_dim?

   • ASELECT SNOWFLAKE.CORE.NULL_COUNT(SELECT passport FROM hr.tables.employee_dim)/100;
   • BSELECT SNOWFLAKE.CORE.AVG(SELECT passport FROM hr.tables.employee_dim);
   • CSELECT SNOWFLAKE.CORE.BLANK_COUNT(SELECT passport FROM hr.tables.employee_dim);
   • DSELECT SNOWFLAKE.CORE.NULL_PERCENT(SELECT passport FROM hr.tables.employee_dim);

   Show answer

   Answer: D

   Next question

7. Question 107 · 126 in bank

   A Data Engineer is planning to run a set of Data Metric Functions (DMFs) against a table using this query: select snowflake.core.max( select c_name from snowflake_sample_data.tpch_sf1.customer ) ; When the query is run, an error is returned. What is causing the error?

   • ADMFs can only be defined on a table; they cannot be executed as a SQL statement.
   • Bsnowflake_sample_data.tpch_sf1.customer is in a data share, which is not a valid source for DMFs.
   • CThe SNOWFLAKE.CORE.MAX() function cannot accept VARCHAR arguments.
   • DThe SNOWFLAKE.CORE.MAX() function is not a valid Snowflake DMF.

   Show answer

   Answer: D

   Next question

8. Question 108 · 126 in bank

   Select 2

   A Data Engineer is trying to clone a very large table from a source environment that has set DATA_RETENTION_TIME_IN_DAYS = 0. During the cloning operation, Data Manipulation Language (DML) transactions are executed on the source table, and the user receives the error "Data is not available." How can this error be prevented, while ensuring successful cloning of the table? (Choose two.)

   • AIncrease the warehouse size to speed up the cloning process and avoid DML conflicts.
   • BSet the DATA_RETENTION_TIME_IN_DAYS = 1 for the table in the source environment before starting the cloning process, and revert it to = 0 after the clone is completed.
   • CAvoid all DML transactions on the source table until the cloning operation is complete.
   • DDisable Time Travel on the cloned table to reduce storage costs once the cloning is complete.
   • EUse a CREATE TABLE AS SELECT STATEMENT to create a new table, instead of creating a clone.

   Show answer

   Answer: BC

   Next question

9. Question 109 · 126 in bank

   A table was created under MY_SCHEMA: CREATE TABLE my_schema.orders ( order_id INT, order_item_nr INT ) DATA_RETENTION_TIME_IN_DAYS = 5; The DATA_RETENTION_TIME_IN_DAYS value on the schema is reset to = 10. Then a new table is created under MY_SCHEMA: CREATE TABLE my_schema.final( final_order_id INT, final_order_item_nr INT); What will happen if the Time Travel DATA_RETENTION_TIME_IN_DAYS value at the schema level is changed from = 10 to = 20?

   • AThe DATA_RETENTION_TIME_IN_DAYS cannot be set at the schema level, so the command will fail.
   • BThe DATA_RETENTION_TIME_IN_DAYS on the orders table will not change, but on the final table it will change to = 20.
   • CThe DATA_RETENTION_TIME_IN_DAYS will be set at = 20 for both the orders table and the final table.
   • DThe DATA_RETENTION_TIME_IN_DAYS will remain the same for both the orders table and the final table.

   Show answer

   Answer: B

   Next question

10. Question 110 · 126 in bank

    A company has deployed a data pipeline that streams customer transaction data from an on-premises PostgreSQL database to Snowflake for real-time analytics. A Data Engineer needs to configure a connector that will continuously and reliably transfer data to Snowflake. Which connector configuration will meet these requirements, while providing MINIMAL latency, and OPTIMAL performance?

    • AConfigure the Snowflake Connector for Kafka to continuously stream data from the PostgreSQL database into Snowflake by configuring PostgreSQL as a Kafka producer.
    • BDeploy the Snowflake JDBC driver to connect the PostgreSQL database directly to Snowflake, setting up a continuous query on the PostgreSQL database to push data to Snowflake.
    • CInstall the Snowflake Connector for Python on the PostgreSQL server to stream data directly to Snowflake, using Python scripts to manage the data transfer.
    • DConfigure the Snowflake Connector for Kafka on a Kafka Connect cluster to stream data from the PostgreSQL database to Kafka topics, which will be ingested using the Kafka connector.

    Show answer

    Answer: D

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DEA-C02 practice, question 101. 126 questions available.