DEA-C02 Practice Questions
143 real DEA-C02 exam questions with expert-verified answers and explanations. Page 3 of 3.
- Question #101Performance Optimization
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...
Snowflake Account UsagePerformance MonitoringQuery OptimizationData Usage Tracking - Question #102Performance Optimization
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...
Table ClusteringPerformance TuningSystem FunctionsMicro-partitions - Question #103Performance Optimization
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...
ClusteringMaterialized ViewsPerformance OptimizationSYSTEM$CLUSTERING_DEPTH - Question #104Performance Optimization
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...
Snowflake ClusteringMicro-partitionsPerformance OptimizationData Loading - Question #105Security and Governance
How can a Data Engineer track credit consumption related to the use of Data Metric Functions (DMFs)? Query the table function.
Data Metric Functions (DMFs)Credit ConsumptionUsage TrackingAccount Usage Views - Question #106Security and Governance
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...
Stored ProceduresSecurity ContextSession VariablesParameter Passing - Question #107Security and Governance
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...
Role-based Access ControlObject OwnershipRole ManagementSecurity Constraints - Question #108Data Movement
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...
Snowflake StreamsChange Data Capture (CDC)Data RetentionStream Staleness - Question #109Snowflake Scripting and Tasks
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...
Snowflake StreamsSnowflake TasksData PipelinesStream Staleness - Question #110Data Movement
What is the following SQL statement used for? Select * from table(validate(t1, job_id => '_last'));
VALIDATE functionData Loading ErrorsCOPY INTO Command - Question #111Snowflake Scripting and Tasks
What considerations should a Data Engineer make when leveraging the table function TASK_HISTORY? (Choose two.)
TASK_HISTORYSnowflake TasksData RetentionAccess Control - Question #112Security and Governance
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...
Time TravelData RetentionSchema ManagementObject Lifecycle - Question #113Data Movement
What metadata is maintained by Snowflake for every table during data loading when using the COPY INTO command? (Choose two.)
Data LoadingCOPY INTOMetadataLoad History - Question #114Snowflake Scripting and Tasks
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...
SQL TransactionsDDL StatementsImplicit CommitStored Procedures - Question #115Security and Governance
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...
Time TravelData RetentionSchema PropertiesTable Properties - Question #116Data Movement
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...
CloningTime TravelExternal TablesError Diagnosis - Question #117Data Transformation
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,...
Time TravelZero-copy cloningData quality testingCost optimization - Question #118Security and Governance
Use of what Snowflake Continuous Data Protection (CDP) feature will consume compute credits?
Continuous Data ProtectionFail-safeCompute CreditsData Recovery - Question #119Security and Governance
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...
Account IsolationEnvironment SegregationZero-Copy Cloning PreventionGovernance - Question #120Security and Governance
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...
Snowflake System RolesAccess ControlPermissionsDatabase Management - Question #121Security and Governance
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...
Role HierarchyCustom RolesBuilt-in RolesSecurity Best Practices - Question #122Security and Governance
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...
Snowflake Data SharingExternal FunctionsSharing LimitationsSecure Views - Question #123Security and Governance
Which command will retrieve results for all objects of a row access policy db1.policies.objectslist at the database level?
Row Access PoliciesPOLICY_REFERENCESInformation SchemaData Governance - Question #124Security and Governance
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...
Snowflake authenticationKey-pair authenticationAutomated workflowsSecurity best practices - Question #125Security and Governance
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...
AuthenticationFederated IdentitySSO - Question #126Security and Governance
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...
Data GovernanceTagsData MaskingAccess Control - Question #127Security and Governance
A tag named DEPARTMENT has two values, MARKETING and FINANCE. Which command will add a third value, ENGINEERING, to the tag?
Snowflake TagsTag Allowed ValuesALTER TAGData Governance - Question #128Security and Governance
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...
Tag-based Masking PoliciesData MaskingColumn-level SecuritySnowflake Tags - Question #129Security and Governance
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...
Projection PolicyData GovernanceColumn RestrictionsSecurity - Question #130Security and Governance
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...
Masking PoliciesData SecurityPII Protection - Question #131Security and Governance
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.
Security AuditingAccess TrackingQuery HistoryAccount Usage Views - Question #132Data Movement
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?
CREATE TABLEDDLTable StructureSchema Replication - Question #133Data Transformation
The following command is run: FLATTEN select * from table(flatten(input => parse_json('{"first_name":"joe", "last_name": "smith" "location": {"state":"co", }}'), recursive => true...
JSONFLATTEN functionSemi-structured data - Question #134Data Transformation
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...
JSONSQL FunctionsData TransformationAPI Data Preparation - Question #135Security and Governance
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...
User-Defined Functions (UDFs)Dynamic Data MaskingLogging & Event TablesScalar SQL UDF behavior - Question #136Data Transformation
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...
User-Defined Functions (UDFs)Function OverloadingData TypesSQL Compilation Errors - Question #137Data Transformation
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...
JSON ParsingSemi-structured DataData LoadingDimension Modeling - Question #138Data Transformation
How are overlapping columns handled when joining DataFrames in Snowpark?
Snowpark DataFramesJoin operationsColumn ambiguityError handling - Question #139Security and Governance
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...
Data Metric Functions (DMF)Data QualityNULL handlingSnowflake Core Functions - Question #140Data Transformation
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_...
Data Metric Functions (DMFs)SQL FunctionsSnowflake SQL SyntaxError Handling - Question #141Data Transformation
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.)
Semi-structured dataVARIANT data typeJSON functionsData extraction - Question #142Data Transformation
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....
SnowparkUser-Defined Functions (UDFs)ScalaMachine Learning Model Migration - Question #143Data Transformation
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...
Snowpark Python APIDataFrame TransformationsAggregationFiltering