DEA-C01 Actual Questions Answers Pass With Real DEA-C01 Exam Dumps [Q29-Q53]

DEA-C01 Actual Questions Answers Pass With Real DEA-C01 Exam Dumps

DEA-C01 Dumps Prepare Your Exam With 67 Questions

NEW QUESTION # 29
Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table and either consume the change data or skip the current run if no change data exists. Which System Function can be used by Data engineer to verify whether a stream contains changed data for a table?

• A. SYSTEM$STREAM_CDC_DATA
• B. SYSTEM$STREAM_DELTA_DATA
• C. SYSTEM$STREAM_HAS_CHANGE_DATA
• D. SYSTEM$STREAM_HAS_DATA

Answer: D

Explanation:
Explanation
SYSTEM$STREAM_HAS_DATA
Indicates whether a specified stream contains change data capture (CDC) records.

NEW QUESTION # 30
What is a characteristic of the operations of streams in Snowflake?

• A. When a stream is used to update a target table the offset is advanced to the current time.
• B. Whenever a stream is queried, the offset is automatically advanced.
• C. Querying a stream returns all change records and table rows from the current offset to the current time.
• D. Each committed and uncommitted transaction on the source table automatically puts a change record in the stream.

Answer: C

Explanation:
Explanation
A stream is a Snowflake object that records the history of changes made to a table. A stream has an offset, which is a point in time that marks the beginning of the change records to be returned by the stream. Querying a stream returns all change records and table rows from the current offset to the current time. The offset is not automatically advanced by querying the stream, but it can be manually advanced by using the ALTER STREAM command. When a stream is used to update a target table, the offset is advanced to the current time only if the ON UPDATE clause is specified in the stream definition. Each committed transaction on the source table automatically puts a change record in the stream, but uncommitted transactions do not.

NEW QUESTION # 31
When using the CURRENT_ROLE and CURRENT_USER functions with secure UDFs that will be shared with Snowflake accounts, Snowflake returns a NULL value for these functions?

• A. FALSE
• B. TRUE

Answer: B

Explanation:
Explanation
When using the CURRENT_ROLE and CURRENT_USER functions with secure UDFs that will be shared with Snowflake accounts, Snowflake returns a NULL value for these functions. The rea-son is that the owner of the data being shared does not typically control the users or roles in the ac-count with which the UDF is being shared.

NEW QUESTION # 32
Mark the Correct Statements:
Statement 1. Snowflake's zero-copy cloning feature provides a convenient way to quickly take a "snapshot" of any table, schema, or database.
Statement 2. Data Engineer can use zero-copy cloning feature for creating instant backups that do not incur any additional costs (until changes are made to the cloned object).

• A. Statement 2
• B. Statement 1 & 2 are correct.
• C. Statement 1
• D. Both are False.

Answer: D

Explanation:
Explanation
Snowflake's zero-copy cloning feature provides a convenient way to quickly take a "snapshot" of any table, schema, or database and create a derived copy of that object which initially shares the underlying storage. This can be extremely useful for creating instant backups that do not incur any additional costs (until changes are made to the cloned object).
For example, when a clone is created of a table, the clone utilizes no data storage because it shares all the existing micro-partitions of the original table at the time it was cloned; however, rows can then be added, deleted, or updated in the clone independently from the original table. Each change to the clone results in new micro-partitions that are owned exclusively by the clone and are protect-ed through CDP.

NEW QUESTION # 33
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed. Which option he can choose to remove these files either during data loading or afterwards?

• A. Script can be used during data loading & post data loading with DELETE command.
• B. Files no longer needed, can be removed using the PURGE=TRUE command.
• C. He can choose to remove stage files during data loading (using the COPY INTO <table> command).
• D. Files no longer needed, can be removed using the REMOVE command.

Answer: B,C

Explanation:
Explanation
Managing Data Files
Staged files can be deleted from a Snowflake stage (user stage, table stage, or named stage) using the following methods:
Files that were loaded successfully can be deleted from the stage during a load by specifying the PURGE copy option in the COPY INTO <table> command.
After the load completes, use the REMOVE command to remove the files in the stage.
Removing files ensures they aren't inadvertently loaded again. It also improves load performance, because it reduces the number of files that COPY commands must scan to verify whether existing files in a stage were loaded already.

NEW QUESTION # 34
Mark the incorrect statement in case Data engineer using the COPY INTO <table> command to load data from files into Snowflake tables?

• A. For Data loading of files with semi-structured file formats (JSON, Avro, etc.), the only supported character set is UTF-16.
• B. For Local environment, Files are first copied ("staged") to an internal (Snowflake) stage, then loaded into a table.
• C. For loading data from all semi-structured supported file formats (JSON, Avro, etc.), as well as unloading data, UTF-8 is the only supported character set.
• D. UTF-32 & UTF-16 both encoding character sets supported for loading data from de-limited files (CSV, TSV, etc.)

Answer: A

Explanation:
Explanation
For Data Loading of delimited files (CSV, TSV, etc.), the default character set is UTF-8. To use any other characters sets, you must explicitly specify the encoding to use for loading.
For semi-structured file formats (JSON, Avro, etc.), the only supported character set is UTF-8.
Rest of the statements are correct.

NEW QUESTION # 35
Data Engineer looking out for quick tool for understanding the mechanics of queries & need to know more about the performance or behaviour of a particular query.
He should go to which feature of snowflake which can help him to spot typical mistakes in SQL query expressions to identify potential performance bottlenecks and improvement opportunities?

• A. Query Optimizer
• B. Performance Metadata table
• C. Query Designer
• D. Query Profile

Answer: D

Explanation:
Explanation
Query Profile, available through the classic web interface, provides execution details for a query. For the selected query, it provides a graphical representation of the main components of the pro-cessing plan for the query, with statistics for each component, along with details and statistics for the overall query.
Query Profile is a powerful tool for understanding the mechanics of queries. It can be used whenev-er you want or need to know more about the performance or behavior of a particular query. It is de-signed to help you spot typical mistakes in SQL query expressions to identify potential performance bottlenecks and improvement opportunities.

NEW QUESTION # 36
Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]

• A. ALTR
• B. BIG Squid
• C. Baffle
• D. Dataiku
• E. Zepl

Answer: A,C

Explanation:
Explanation
Security and governance tools ensure sensitive data maintained by an organization is protected from inappropriate access and tampering, as well as helping organizations to achieve and maintain regula-tory compliance. These tools are often used in conjunction with observability solutions/services to provide organizations with visibility into the status, quality, and integrity of their data, including identifying potential issues.
Together, these tools support a wide range of operations, including risk assessment, intrusion detec-tion/monitoring/notification, data masking, data cataloging, data health/quality checks, issue identi-fication/troubleshooting/resolution, and more.
ALTR & Baffle are correct options here.

NEW QUESTION # 37
Pascal, a Data Engineer, have requirement to retrieve the 10 most recent executions of a specified task (completed, still running, or scheduled in the future) scheduled within the last hour, which of the following is the correct SQL Code ?

• A. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 10,
  5.task_name=>'MYTASK'));
• B. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 10,
  5.task_name=>'MYTASK') WHERE query_id IS NOT NULL);
• C. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 10,query_id IS NOT NULL
  5.task_name=>'MYTASK'));
• D. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 11,
  5.task_name=>'MYTASK') WHERE query_id IS NOT NULL);

Answer: A

Explanation:
Explanation
To retrieve only tasks that are completed or still running, filter the query using WHERE query_id IS NOT NULL.

NEW QUESTION # 38
As part of Table Designing, Data Engineer added a timestamp column that inserts the current timestamp as the default value as records are loaded into a table. The intent is to capture the time when eachrecord was loaded into the table; however, the timestamps are earlier than the LOAD_TIME column values returned by COPY_HISTORY view (Account Usage). What could be reason of this issue?

• A. It might be possible that Cloud Provider hosted on Snowflake belongs to region having server time zone lagging Cluster time zone of warehouse where queries get processed & committed.
• B. CURRENT_TIMESTAMP values might be different due to query gets executed in warehouse located in different region.
• C. LOAD_TIME column values returned by COPY_HISTORY view (Account Usage) gives the same time as returned by CURRENT_TIMESTAMP.
• D. The reason is, CURRENT_TIMESTAMP is evaluated when the load operation is com-piled in cloud services rather than when the record is inserted into the table (i.e. when the transaction for the load operation is committed).

Answer: D

Explanation:
Explanation
The reason timestamps are earlier than the LOAD_TIME column values which is returned by COPY_HISTORY view (Account Usage) is that CURRENT_TIMESTAMP is evaluated when the load operation is compiled in cloud services rather than when the record is inserted into the table (i.e. when the transaction for the load operation is committed).

NEW QUESTION # 39
Partition columns optimize query performance by pruning out the data files that do not need to be scanned (i.e.
partitioning the external table). Which pseudocolumn of External table evaluate as an expression that parses the path and/or filename information.

• A. METADATA$ROW_NUMBER
• B. METADATA$FILEPATH
• C. METADATA$COLUMNNAME
• D. METADATA$FILENAME

Answer: D

Explanation:
Explanation
METADATA$FILENAME
A pseudocolumn that identifies the name of each staged data file included in the external table, in-cluding its path in the stage.
An external table creator defines partition columns in a new external table as expressions that parse the path and/or filename information stored in the METADATA$FILENAME pseudocolumn. A partition consists of all data files that match the path and/or filename in the expression for the parti-tion column.

NEW QUESTION # 40
A company is using Snowpipe to bring in millions of rows every day of Change Data Capture (CDC) into a Snowflake staging table on a real-time basis The CDC needs to get processedand combined with other data in Snowflake and land in a final table as part of the full data pipeline.
How can a Data engineer MOST efficiently process the incoming CDC on an ongoing basis?

• A. Transform the data during the data load with Snowpipe by modifying the related copy into statement to include transformation steps such as case statements andJOIN'S.
• B. Use a create ok replace table as statement that references the staging table and includes all the transformation SQL. Use a task to run the full create or replace table as statement on a scheduled basis
• C. Schedule a task that dynamically retrieves the last time the task was run from information_schema-rask_hiSwOry and use that timestamp to process the delta of the new rows since the last time the task was run.
• D. Create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data.

Answer: D

Explanation:
Explanation
The most efficient way to process the incoming CDC on an ongoing basis is to create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data. A stream is a Snowflake object that records changes made to a table, such as inserts, updates, or deletes. A stream can be queried like a table and can provide information about what rows have changed since the last time the stream was consumed. A task is a Snowflake object that can execute SQL statements on a schedule without requiring a warehouse. A task can be configured to run only when certain conditions are met, such as when a stream has data or when another task has completed successfully. By creating a stream on the staging table and scheduling a task that transforms data from the stream, the Data Engineer can ensure that only new or modified rows are processed and that no unnecessary computations are performed.

NEW QUESTION # 41
Charles, A Lead Data engineer, with ACCOUNTADMIN role wants to configure the time travel for one of the Schema's object. He setup the MIN_DATA_RETENTION_TIME_IN_DAYS pa-rameter with Value 79 at account level but he figured out that DA-TA_RETENTION_TIME_IN_DAYS is already set with value 81 at account level. What would be the effective minimum data retention period for an object?

• A. 0
• B. 1
• C. 2
• D. There is no such MIN_DATA_RETENTION_TIME_IN_DAYS parameter

Answer: C

Explanation:
Explanation
A user with the ACCOUNTADMIN role can also set the MIN_DATA_RETENTION_TIME_IN_DAYS at the account level. This parameter setting enforc-es a minimum data retention period for databases, schemas, and tables. Setting MIN_DATA_RETENTION_TIME_IN_DAYS does not alter or replace the DA-TA_RETENTION_TIME_IN_DAYS parameter value. It may, however, change the effective data retention period for objects. When MIN_DATA_RETENTION_TIME_IN_DAYS is set at the ac-count level, the data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).

NEW QUESTION # 42
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 vary by tope according to what data each Analyst needs Responsiveness of the dashboard queries is a top priority, and the data cache should be preserved.
How should the Data Engineer configure the compute resources to support this dashboard?

• A. Assign queries to a multi-cluster virtual warehouse with economy auto-scaling Allow the system to automatically start and stop clusters according to demand.
• B. Create a size XL virtual warehouse to support all the dashboard queries Monitor query runtimes to determine whether the virtual warehouse should be resized.
• C. Assign all queries to a multi-cluster virtual warehouse set to maximized mode Monitor to determine the smallest suitable number of clusters.
• D. Create a virtual warehouse for every 250 Analysts Monitor to determine how many of these virtual warehouses are being utilized at capacity.

Answer: C

Explanation:
Explanation
This option is the best way to configure the compute resources to support this dashboard. By assigning all queries to a multi-cluster virtual warehouse set to maximized mode, the Data Engineer can ensure that there is enough compute capacity to handle thousands of concurrent queries from different analysts. A multi-cluster virtual warehouse can scale up or down by adding or removing clusters based on the load. A maximized scaling policy ensures that there is always at least one cluster running and that new clusters are added as soon as possible whenneeded. By monitoring the utilization and performance of the virtual warehouse, the Data Engineer can determine the smallest suitable number of clusters that can meet the responsiveness requirement and minimize costs.

NEW QUESTION # 43
A Data Engineer is implementing a near real-time ingestionpipeline to toad data into Snowflake using the Snowflake Kafka connector. There will be three Kafka topics created.
......snowflake objects are created automatically when the Kafka connector starts? (Select THREE)

• A. Pipes
• B. External stages
• C. Materialized views
• D. internal stages
• E. Tasks
• F. Tables

Answer: A,D,F

Explanation:
Explanation
The Snowflake objects that are created automatically when the Kafka connector starts are tables, pipes, and internal stages. The Kafka connector will create one table, one pipe, and oneinternal stage for each Kafka topic that is configured in the connector properties. The table will store the data from the Kafka topic, the pipe will load the data from the stage to the table using COPY statements, and the internal stage will store the files that are produced by the Kafka connector using PUT commands. The other options are not Snowflake objects that are created automatically when the Kafka connector starts. Option B, tasks, are objects that can execute SQL statements on a schedule without requiring a warehouse. Option E, external stages, are objects that can reference locations outside of Snowflake, such as cloud storage services. Option F, materialized views, are objects that can store the precomputed results of a query and refresh them periodically.

NEW QUESTION # 44
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 files to be loaded again.
How can this issue be solved using the LEAST amount of operational overhead?

• A. Set the FORCE=TRUE option in the Snowpipe COPY INTO command
• B. Wait until the metadata expires and then reload the file using Snowpipe
• C. Recreate the pipe by using the create or replace pipe command
• D. Modify the file by adding a blank row to the bottom and re-stage the file

Answer: A

Explanation:
Explanation
The FORCE=TRUE option in the Snowpipe COPY INTO command allows Snowpipe to load files that have already been loaded before, regardless of the metadata. This is the easiest way to reload the same files without modifying them or recreating the pipe.

NEW QUESTION # 45
Which two Account usage views can be used for auditing Dynamic data masking purpose?

• A. DYNAMIC POLICY_REFERENCES
• B. DYNAMIC MASKING POLICIES
• C. POLICY_REFERENCES
• D. MASKING POLICIES

Answer: C,D

NEW QUESTION # 46
Data engineer designed the data pipelines using Snowpipe to load data files into Snowflake tables, what will happen in case few files with same name but modified data are queued for reloading?

• A. eTAG is changed for Files even they are having same name, so data will be duplicated in SnowFlake tables.
• B. Snowpipe uses file loading metadata associated with each table object, so no metadata available to prevent duplication.
• C. Data will be reloaded as files are modified & its associated metadata also changed. But Snowflake handle implicitly deduplication.
• D. Snowpipe uses file loading metadata associated with each pipe object to prevent reload-ing the same files (and duplicating data) in a table.

Answer: D

Explanation:
Explanation
Snowflake uses file loading metadata to prevent reloading the same files (and duplicating data) in a table.
Snowpipe prevents loading files with the same name even if they were later modified (i.e. have a different eTag).
The file loading metadata is associated with the pipe object rather than the table. As a result:
Staged files with the same name as files that were already loaded are ignored, even if they have been modified, e.g. if new rows were added or errors in the file were corrected.
Truncating the table using the TRUNCATE TABLE command does not delete the Snowpipe file loading metadata.

NEW QUESTION # 47
Select the incorrect statement while working with warehouses?

• A. Compute resources waiting to shut down are considered to be in "quiesce" mode.
• B. Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.
• C. Resizing a suspended warehouse does not provision any new compute resources for the warehouse.
• D. Resizing a warehouse to a larger size is useful while loading and unloading significant amounts of data.

Answer: B

Explanation:
Explanation
Resizing a warehouse doesn't have any impact on statements that are currently being executed by the warehouse. When resizing to a larger size, the new compute resources, once fully provisioned, are used only to execute statements that are already in the warehouse queue, as well as all future statements submitted to the warehouse.

NEW QUESTION # 48
Let us say you have List of 50 Source files, which needs to be loaded into Snowflake internal stage. All these Source system files are already Brotli-compressed files. Which statement is correct with respect to Compression of Staged Files?

• A. Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.
• B. Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.
• C. Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.
• D. When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.

Answer: B

Explanation:
Explanation
Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.
To Know more about Compression of Staged Files, please refer the link:
https://docs.snowflake.com/en/user-guide/intro-summary-loading.html#compression-of-staged-files

NEW QUESTION # 49
The following CREATE DATABASE command creates a clone of a database snowmy_db i.e.
Create database pods_db clone snowmy_db
before (statement => '7e5d0cb9-005e-94e6-b058-k8f5b37c5725');
What are possible reason of failing cloning operation for this database?

• A. CREATE DATABASE query fails due to compilation error as it do not support state-ment keyword.
• B. SQL Compilation error: "Incorrect Syntax 'before' while creating database"
• C. Time Travel Statement query time is beyond the retention time of few current child (e.g., a table) of the Database entity.
• D. Time Travel Statement query time is at or before the point in time when the object was created.

Answer: C,D

NEW QUESTION # 50
Select the correct usage statements with regards to SQL UDF?

• A. Scalar functions (UDFs) have a limit of 500 input arguments.
• B. The body of a UDF cannot contain DDL statements or any DML statement other than SELECT.
• C. You can include only one query expression.
• D. All of above are correct.
• E. When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.

Answer: D

NEW QUESTION # 51
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove par-titions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?

• A. FALSE
• B. TRUE

Answer: B

Explanation:
Explanation
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
1.CREATE EXTERNAL TABLE
2.<table_name>
3.( <part_col_name> <col_type> AS <part_expr> )
4.[ , ... ]
5.[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
6.PARTITION_TYPE = USER_SPECIFIED
Included the required PARTITION_TYPE = USER_SPECIFIED parameter.

NEW QUESTION # 52
Which system role is recommended for a custom role hierarchy to be ultimately assigned to?

• A. ACCOUNTADMIN
• B. SYSTEMADMIN
• C. USERADMIN
• D. SECURITYADMIN

Answer: D

Explanation:
Explanation
The system role that is recommended for a custom role hierarchy to be ultimately assigned to is SECURITYADMIN. This role has the manage grants privilege on all objects in an account, which allows it to grant access privileges to other roles or revoke them as needed. This role can also create or modify custom roles and assign them to users or other roles. By assigning custom roles to SECURITYADMIN, the role hierarchy can be managed centrally and securely. The other options are not recommended system roles for a custom role hierarchy to be ultimately assigned to. Option A is incorrect because ACCOUNTADMIN is the most powerful role in an account, which has full access to all objects and operations. Assigning custom roles to ACCOUNTADMIN can pose a security risk and should be avoided. Option C is incorrect because SYSTEMADMIN is a role that has full access to all objects in the public schema of the account, but not to other schemas or databases. Assigning custom roles to SYSTEMADMIN can limit the scope and flexibility of the role hierarchy. Option D is incorrect because USERADMIN is a role that can manage users and roles in an account, but not grant access privileges to other objects. Assigning custom roles to USERADMIN can prevent the role hierarchy from controlling access to data and resources.

NEW QUESTION # 53
......

New DEA-C01 Dumps - Real Snowflake Exam Questions: https://www.test4engine.com/DEA-C01_exam-latest-braindumps.html

Dependable DEA-C01 Exam Dumps to Become Snowflake Certified: https://drive.google.com/open?id=1Ab8GsBHHmKpji7aZGDu9trWIDrMjfdtn