DEA-C02 Practice Questions
143 real DEA-C02 exam questions with expert-verified answers and explanations. Page 1 of 3.
- Question #1Performance Optimization
A Data Engineer is investigating a query that is taking a long time to return. The Query Profile shows the following: What step should the Engineer take to increase the query perfo...
Query performanceVirtual warehousesPerformance tuningWarehouse sizing - Question #2Data Transformation
How can the following relational data be transformed into semi-structured data using the LEAST amount of operational overhead?
Data TransformationSemi-structured DataVARIANT Data TypeOBJECT_CONSTRUCT function - Question #3Performance Optimization
A Data Engineer executes a complex query and wants to make use of Snowflake's query results caching capabilities to reuse the results. Which conditions must be met? (Choose three.)
Snowflake CachingQuery PerformanceResult CacheMicro-partitions - Question #4Data Movement
A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe. Which recommendations apply to this scenario? (Choose three.)
SnowpipeSemi-structured DataData Loading Best PracticesPerformance Optimization - Question #5Performance Optimization
Given the table SALES which has a clustering key of column CLOSED_DATE, which table function will return the average clustering depth for the SALES_REPRESENTATIVE column for the No...
Snowflake ClusteringClustering DepthSystem FunctionsPerformance Optimization - Question #6Performance Optimization
A large table with 200 columns contains two years of historical data. When queried, the table is filtered on a single day. Below is the Query Profile: Using a size 2XL virtual ware...
Snowflake PerformanceTable ClusteringMicro-partition PruningQuery Optimization - Question #7Data Movement
A Data Engineer is working on a Snowflake deployment in AWS eu-west-1 (Ireland). The Engineer is planning to load data from staged files into target tables using the COPY INTO comm...
COPY INTOExternal StagesInternal StagesCloud Storage - Question #8Security and Governance
A Data Engineer wants to create a new development database (DEV) as a clone of the permanent production database (PROD). There is a requirement to disable Fail-safe for all tables....
Database cloningTransient objectsFail-safeDatabase creation - Question #9Snowflake Scripting and Tasks
Which query will show a list of the 20 most recent executions of a specified task, MYTASK, that have been scheduled within the last hour that have ended or are still running? A. B....
Snowflake TasksInformation SchemaSQL QueryingTask Monitoring - Question #10Data Transformation
Which methods can be used to create a DataFrame object in Snowpark? (Choose three.)
SnowparkDataFrame APIData IngestionSQL Integration - Question #11Security and Governance
A new CUSTOMER table is created by a data pipeline in a Snowflake schema where MANAGED ACCESS is enabled. Which roles can grant access to the CUSTOMER table? (Choose three.)
Snowflake RBACManaged Access SchemasPrivilege ManagementSystem Roles - Question #12Data Movement
What is the purpose of the BUILD_STAGE_FILE_URL function in Snowflake?
Snowflake StagesFile URLsData Access - Question #13Data Transformation
The JSON below is stored in a VARIANT column named V in a table named jCustRaw: Which query will return one row per team member (stored in the teamMembers array) along with all of...
JSON queryingSemi-structured dataLATERAL FLATTENSnowflake SQL - Question #14Data Transformation
A company has an extensive script in Scala that transforms data by leveraging DataFrames. A Data Engineer needs to move these transformations to Snowpark. What characteristics of d...
SnowparkDataFramesData TransformationLazy Execution - Question #15Performance Optimization
The following is returned from SYSTEM$CLUSTERING_INFORMATION() for a table named ORDERS with a DATE column named O_ORDERDATE: What does the total_constant_partition_count value ind...
ClusteringMicro-partitionsPerformance OptimizationSYSTEM$CLUSTERING_INFORMATION - Question #16Performance Optimization
A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated. The query conditions...
Multi-cluster Virtual WarehousesWarehouse Maximized ModePerformance TuningConcurrency Management - Question #17Performance Optimization
A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours. How long will Snowflake use the persisted query results from the r...
Result CacheQuery PerformanceCaching - Question #18Snowflake Scripting and Tasks
A Data Engineer ran a stored procedure containing various transactions. During the execution, the session abruptly disconnected, preventing one transaction from committing or rolli...
Transaction ManagementSession ManagementSystem FunctionsResource Locking - Question #19Snowflake Scripting and Tasks
A database contains a table and a stored procedure defined as: The log_table is initially empty and a Data Engineer issues the following command: CALL insert_log(NULL::VARCHAR); No...
Snowflake ScriptingStored ProceduresConditional LogicNULL Handling - Question #20Data Transformation
When would a Data Engineer use TABLE with the FLATTEN function instead of the LATERAL FLATTEN combination?
Snowflake FLATTENSemi-structured dataLateral vs Table functionJSON and Array processing - Question #21Performance Optimization
Which output is provided by both the SYSTEM$CLUSTERING_DEPTH function and the SYSTEM$CLUSTERING_INFORMATION function?
ClusteringSystem FunctionsPerformance MonitoringData Optimization - Question #22Data Transformation
A Data Engineer needs to ingest invoice data in PDF format into Snowflake so that the data can be queried and used in a forecasting solution. What is the recommended way to ingest...
User-Defined Functions (UDFs)PDF ParsingData IngestionUnstructured Data Processing - Question #23Data Movement
A table is loaded using Snowpipe and truncated afterwards. Later, a Data Engineer finds that the table needs to be reloaded, but the metadata of the pipe will not allow the same fi...
SnowpipeData LoadingPipe ManagementMetadata Management - Question #24Data Movement
A stream called TRANSACTIONS_STM is created on top of a TRANSACTIONS table in a continuous pipeline running in Snowflake. After a couple of months, the TRANSACTIONS table is rename...
Snowflake StreamsStream BehaviorTable RenamingChange Data Capture - Question #25Performance Optimization
A Data Engineer is evaluating the performance of a query in a development environment. Based on the Query Profile, what are some performance tuning options the Engineer can use? (C...
Query PerformanceVirtual WarehousesSQL Optimization - Question #26Data Transformation
Which methods will trigger an action that will evaluate a DataFrame? (Choose two.)
Snowpark DataFramesLazy EvaluationActions - Question #27Data Movement
Which Snowflake objects does the Snowflake Kafka connector use? (Choose three.)
Kafka ConnectorSnowpipeInternal StagesData Ingestion - Question #28Data Transformation
A Data Engineer has created table t1 with one column c1 with datatype VARIANT: create or replace table t1 (c1 variant); The Engineer has loaded the following JSON data set, which h...
JSON queryingVARIANT data typeSemi-structured data processingData type casting - Question #29Security and Governance
What is a characteristic of the use of external tokenization?
External TokenizationData De-identificationAnalytical PreservationData Security - Question #30Data Movement
A Data Engineer is implementing a near real-time ingestion pipeline to load data into Snowflake using the Snowflake Kafka connector. There will be three Kafka topics created. Which...
Snowflake Kafka ConnectorReal-time ingestionSnowpipeAutomatic object creation - Question #31Performance Optimization
The following chart represents the performance of a virtual warehouse over time: A Data Engineer notices that the warehouse is queueing queries. The warehouse is size X-Small, the...
Virtual WarehouseMulti-cluster scalingQuery concurrencyPerformance tuning - Question #32Security and Governance
A secure function returns data coming through an inbound share. What will happen if a Data Engineer tries to assign USAGE privileges on this function to an outbound share?
Data SharingSecure FunctionsRe-sharing RestrictionsAccess Control - Question #33Data Transformation
Which functions will compute a 'fingerprint' over an entire table, query result, or window to quickly detect changes to table contents or query results? (Choose two.)
HashingData FingerprintingChange DetectionSQL Functions - Question #34Data Movement
Which stages support external tables?
External TablesExternal StagesCloud StorageData Access - Question #35Data Movement
A Data Engineer wants to check the status of a pipe named my_pipe. The pipe is inside a database named test and a schema named Extract (case-sensitive). Which query will provide th...
SnowpipeSystem FunctionsObject IdentifiersCase Sensitivity - Question #36Data Movement
Company A and Company B both have Snowflake accounts. Company A's account is hosted on a different cloud provider and region than Company B's account. Companies A and B are not in...
Data SharingCross-Cloud SharingDatabase ReplicationSnowflake Accounts - Question #37Data Movement
A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure: The engineer is using the following COPY INTO statement...
Data LoadingCOPY INTOFile FormatsCSV Parsing - Question #38Performance Optimization
A Data Engineer is working on a continuous data pipeline which receives data from Amazon Kinesis Firehose and loads the data into a staging table which will later be used in the da...
SnowpipeKinesis FirehosePerformance TuningCost Optimization - Question #39Security and Governance
Within a Snowflake account. permissions have been defined with custom roles and role hierarchies. To set up column-level masking using a role in the hierarchy of the current user,...
Column-level maskingRole hierarchiesAccess controlContext functions - Question #40Security and Governance
Assuming a Data Engineer has all appropriate privileges and context, which statements would be used to assess whether the User-Defined Function (UDF), MYDATABASE.SALES.REVENUE_BY_R...
User-Defined Functions (UDFs)Information SchemaSecurityMetadata Querying - Question #41Security and Governance
A Data Engineer has written a stored procedure that will run with caller's rights. The Engineer has granted ROLEA the right to use this stored procedure. What is a characteristic o...
Stored ProceduresCaller's RightsAccess ControlSnowflake Security - Question #42Snowflake Scripting and Tasks
What is a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake?
JavaScript Stored ProceduresBinding VariablesSupported Data TypesSnowflake Scripting - Question #43Performance Optimization
Which use case would be BEST suited for the search optimization service?
Search Optimization ServiceQuery OptimizationPerformance TuningSelective Filters - Question #44Data Movement
What is a characteristic of the operations of streams in Snowflake?
Snowflake StreamsChange Data Capture (CDC)Stream OffsetData Synchronization - Question #45Data Movement
At what isolation level are Snowflake streams?
Snowflake StreamsIsolation LevelRepeatable ReadChange Data Capture - Question #46Security and Governance
What kind of Snowflake integration is required when defining an external function in Snowflake?
External FunctionsAPI IntegrationSecurityCloud Integrations - Question #47Performance Optimization
A Data Engineer is writing a Python script using the Snowflake Connector for Python. The Engineer will use the snowflake.connector.connect function to connect to Snowflake. The req...
Snowflake Connector for PythonConnection ParametersPerformance TuningConnection Validation - Question #48Security and Governance
A Data Engineer wants to centralize grant management to maximize security. A user needs OWNERSHIP on a table in a new schema. However, this user should not have the ability to make...
Managed Access SchemasGrant ManagementSecurityOwnership Privileges - Question #49Data Movement
A CSV file, around 1 TB in size, is generated daily on an on-premise server. A corresponding table, internal stage, and file format have already been created in Snowflake to facili...
Data LoadingInternal StagesSnowSQLAutomation - Question #50Snowflake Scripting and Tasks
What are characteristics of Snowpark Python packages? (Choose three.)
Snowpark PythonPackage ManagementAnaconda IntegrationMetadata Inspection