DEA-C02 Practice Questions

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 i...

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 system function will verify CLUST...

A Data Engineer is working to optimize the performance of end-users' queries about automobile shipment data for a portal web application that generates dashboards for different cli...

A table, SALES, is reloaded daily, and is used by many users to support multiple data TABLE reporting tools. Users report poor performance when querying the table. This is the clus...

How can a Data Engineer track credit consumption related to the use of Data Metric Functions (DMFs)? Query the table function.

The following stored procedure has been created to load orders for a single date into a target table: create or replace procedure uspLoadOrdersByDate () returns string language jav...

A Data Engineer runs the following sequence: use role ACCOUNTADMIN; create role ROLE1; grant role ROLE1 to role ACCOUNTADMIN; grant create database on account to role ROLE1; use ro...

A Data Engineer has created table T1 with DATA_RETENTION_TIME_IN_DAYS set to 7. There is a default stream S1 on table T1. The parameter MAX_DATA_EXTENSION_TIME_IN_DAYS is set to 10...

A Data Engineer is building a data pipeline to ingest incremental data from a data source using streams and tasks in Snowflake. A stream is created on a CUSTOMER_RAW table to track...

What is the following SQL statement used for? Select * from table(validate(t1, job_id => '_last'));

What considerations should a Data Engineer make when leveraging the table function TASK_HISTORY? (Choose two.)

A table, mytable, is created using the following command in a schema called raw: create table mytable(col1 number, col2 date) data_retention_time_in_days=90; The DATA_RETENTION_TIM...

What metadata is maintained by Snowflake for every table during data loading when using the COPY INTO command? (Choose two.)

A Data Engineer is debugging a SQL stored procedure that contains a transaction with a rollback using this code: DROP TABLE A1; CREATE OR REPLACE TABLE A1(i int); BEGIN TRANSACTION...

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...

A Data Engineer is using Time Travel to clone a table using this query: CREATE TABLE MY_TABLE_CLONE CLONE MY_TABLE AT (OFFSET => -60*30); An error is returned. What could be causin...

A Data Engineer is investigating a data quality issue that led to a system failure. Because there is no data from the time of the incident, the Engineer needs replicate the issue,...

Use of what Snowflake Continuous Data Protection (CDP) feature will consume compute credits?

To meet governance requirements, a company needs to completely isolate its production environment from its development and testing environments. The solution must prevent zero- cop...

A Data Engineer needs to be able to create new databases and objects as required for a Proof of Concept (POC). The sandbox Snowflake account where the POC is tested does not have R...

A Data Engineer has created two custom roles, MARKETING_ROLE and FINANCE_ROLE, to be granted to the users of an application. According to best practices, what action should be take...

A Data Engineer is using a Snowflake Standard edition and has an external function in schema A, a view in schema B, and then creates a SHARE and adds the view to the SHARE as follo...

Which command will retrieve results for all objects of a row access policy db1.policies.objectslist at the database level?

A Data Engineer needs to create a SQL script that will load and transform data in batch mode into Snowflake. The script should be run with SnowSQL, and should be scheduled to run a...

Which authentication method has the following characteristics? 1. User authentication is separated from access to Snowflake using an external entity 2. The external entity provides...

A Data Engineer discovered that many tables in the company database contain sensitive details about products which are not yet in production. This information is scattered across m...

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

A Data Engineer needs to create a tag-based masking policy to determine whether data should be masked based on the value of the label string assigned to column salary in table staf...

A Data Engineer created table t1 and applied a projection policy, my_proj_policy to several columns: What effect will the policy have on columns account_number and account_type? (C...

A retail store's application team needs to build a loyalty program for their customers. The customer table contains Personal Identifiable Information (PII), and the team members ha...

A Data Engineer is supporting a security audit and needs to identify all tables accessed by USER1 in the last month. What query can be used to meet this requirement? A. B. C. D.

A Data Engineer would like to create a new empty table named CURRENT_STUDENT_COPY_DATA that has the same structure as the table CURRENT_STUDENT. Which statement should be used?

The following command is run: FLATTEN select * from table(flatten(input => parse_json('{"first_name":"joe", "last_name": "smith" "location": {"state":"co", }}'), recursive => true...

A Data Engineer needs to share customer data using an API with a partner application. The API will accept the JSON payload using this structure: All information can be found in tab...

A Data Engineer has created a scalar User-Defined Function (UDF) that contains a SELECT statement based on a projection constraint column. The Engineer notices that the UDF behaves...

A Data Engineer has created two SQL User-Defined Functions (UDFs) with the same name: The following function call is made: select plus5(to_date('2024-01-25')); What will be the out...

A company has loaded the following JSON data into the table and needs assistance CAR_SALES loading the data into a dimension. VEHICLE_DIM Which query will load the data into a VEHI...

How are overlapping columns handled when joining DataFrames in Snowpark?

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...

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_...

When working with semi-structured data in a VARIANT column, which Snowflake functions will allow a user to write queries that extract values using a hierarchy name? (Choose two.)

A company is running several machine learning models that were developed in Scala and are running on-premises. A Data Engineer has been asked to design a solution using Snowflake....

A Data Engineer needs to rewrite SQL code into Snowpark code using the Snowpark Python API. Data is stored in a DataFrame, df_orders. How can this SQL query be rewritten so that it...