Authentic Best resources for DEA-C01 Test Engine Practice Exam
[2024] DEA-C01 PDF Questions - Perfect Prospect To Go With PracticeDump Practice Exam
NEW QUESTION # 32
Which Scenario Data engineer decide Materialized views are not useful. Select All that apply.
- A. Query results contain results that require significant processing.
- B. The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
- C. The view's base table change frequently.
- D. Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).
Answer: C
Explanation:
Explanation
A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is pre-computed, querying a material-ized view is faster than executing a query against the base table of the view. This performance dif-ferencecan be significant when a query is run frequently or is sufficiently complex. As a result, ma-terialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets.
Materialized views require Enterprise Edition.
Materialized views are designed to improve query performance for workloads composed of com-mon, repeated query patterns. However, materializing intermediate results incurs additional costs. As such, before creating any materialized views, you should consider whether the costs are offset by the savings from re-using these results frequently enough.
Materialized views are particularly useful when:
Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).
Query results contain results that require significant processing, including:
1. Analysis of semi-structured data.
2. Aggregates that take a long time to calculate.
The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
The view's base table does not change frequently.
NEW QUESTION # 33
A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours.
---will Snowflake use the persisted query results from the result cache provided that the underlying data has not changed^
- A. 12 hours
- B. 31 days
- C. 24 hours
- D. 14 days
Answer: D
Explanation:
Explanation
Snowflake uses the result cache to store the results of queries that have been executed recently. The result cache is maintained at the account level and is shared across all sessions and users. The result cache is invalidated when any changes are made to the tables or views referenced by the query. Snowflake also has a retention policy for the result cache, which determines how long the results are kept in the cache before they are purged. The default retention period for the result cache is 24 hours, but it can be changed at the account, user, or session level. However, there is a maximum retention period of 14 days for the result cache, which cannot be exceeded. Therefore, if the underlying data has not changed, Snowflake will use the persisted query results from the result cache for up to 14 days.
NEW QUESTION # 34
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. Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.
- B. Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.
- C. 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.
- D. When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.
Answer: C
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 # 35
A Data Engineer needs to know the details regarding the micro-partition layout for a table named invoice using a built-in function.
Which query will provide this information?
- A. SELECT SYSTEM$CLUSTERING_INTFORMATICII ('Invoice' ) ;
- B. SELECT $CLUSTERXNG_INFQRMATION ('Invoice')'
- C. CALL $CLUSTERINS_INFORMATION('Invoice');
- D. CALL SYSTEM$CLUSTERING_INFORMATION ('Invoice');
Answer: A
Explanation:
Explanation
The query that will provide information about the micro-partition layout for a table named invoice using a built-in function is SELECT SYSTEM$CLUSTERING_INFORMATION('Invoice');. The SYSTEM$CLUSTERING_INFORMATION function returns information about the clustering status of a table, such as the clustering key, the clustering depth, the clustering ratio, the partition count, etc. The function takes one argument: the table name in a qualified or unqualified form. In this case, the table name is Invoice and it is unqualified, which means that it will use the current database and schema as the context. The other options are incorrect because they do not use a valid built-in function for providing information about the micro-partition layout for a table. Option B is incorrect because it uses $CLUSTERING_INFORMATION instead of SYSTEM$CLUSTERING_INFORMATION, which is not a valid function name. Option C is incorrect because it uses CALL instead of SELECT, which is not a valid way to invoke a table function.
Option D is incorrect because it uses CALL instead of SELECT and $CLUSTERING_INFORMATION instead of SYSTEM$CLUSTERING_INFORMATION, which are both invalid.
NEW QUESTION # 36
How can the following relational data be transformed into semi-structured data using the LEAST amount of operational overhead?
- A. Use the PAESE_JSON function to produce a variant value
- B. Use the OBJECT_CONSTRUCT function to return a Snowflake object
- C. Use the to_json function
- D. Use the TO_VARIANT function to convert each of the relational columns to VARIANT.
Answer: B
Explanation:
Explanation
This option is the best way to transform relational data into semi-structured data using the least amount of operational overhead. The OBJECT_CONSTRUCT function takes a variable number of key-value pairs as arguments and returns a Snowflake object, which is a variant type that can store JSON data. The function can be used to convert each row of relational data into a JSON object with the column names as keys and the column values as values.
NEW QUESTION # 37
Harry using Snowflake Enterprise Edition & decided to scale in/out the Cluster in automatic mode. He needs to configure some warehouses as multi cluster mode and some among them in Standard mode as per needs.
If Harry is using Snowflake Enterprise Edition (or a higher edition), all his warehouses should be configured as multi-cluster warehouses only.
- A. FALSE
- B. TRUE
Answer: B
Explanation:
Explanation
If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.
NEW QUESTION # 38
To advance the offset of a stream to the current table version without consuming the change data in a DML operation, which of the following operations can be done by Data Engineer? [Select 2]
- A. Delete the offset using STREAM properties SYSTEM$RESET_OFFSET( <stream_id> )
- B. A stream advances the offset only when it is used in a DML transaction, so none of the options works without consuming the change data of table.
- C. Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).
- D. using the CREATE OR REPLACE STREAM syntax, Recreate the STREAM
Answer: C,D
Explanation:
Explanation
When created, a stream logically takes an initial snapshot of every row in the source object (e.g. ta-ble, external table, or the underlying tables for a view) by initializing a point in time (called an off-set) as the current transactional version of the object. The change tracking system utilized by the stream then records information about the DML changes after this snapshot was taken. Change rec-ords provide thestate of a row before and after the change. Change information mirrors the column structure of the tracked source object and includes additional metadata columns that describe each change event.
Note that a stream itself does not contain any table data. A stream only stores an offset for the source object and returns CDC records by leveraging the versioning history for the source object.
A new table version is created whenever a transaction that includes one or more DML statements is committed to the table.
In the transaction history for a table, a stream offset is located between two table versions. Query-ing a stream returns the changes caused by transactions committed after the offset and at or before the current time.
Multiple queries can independently consume the same change data from a stream without changing the offset.
A stream advances the offset only when it is used in a DML transaction. This behavior applies to both explicit and autocommit transactions. (By default, when a DML statement is execut-ed, an autocommit transaction is implicitly started and the transaction is committed at the comple-tion of the statement. This behavior is controlled with the AUTOCOMMIT parameter.) Querying a stream alone does not advance its offset, even within an explicit transaction; the stream contents must be consumed in a DML statement.
To advance the offset of a stream to the current table version without consuming the change data in a DML operation, complete either of the following actions:
Recreate the stream (using the CREATE OR REPLACE STREAM syntax).
Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).
NEW QUESTION # 39
Elon, a Data Engineer, needs to Split Semi-structured Elements from the Source files and load them as an array into Separate Columns.
Source File:
1.+----------------------------------------------------------------------+
2.| $1 |
3.|----------------------------------------------------------------------|
4.| {"mac_address": {"host1": "197.128.1.1","host2": "197.168.0.1"}}, |
5.| {"mac_address": {"host1": "197.168.2.1","host2": "197.168.3.1"}} |
6.+----------------------------------------------------------------------+ Output: Splitting the Machine Address as below.
1.COL1 | COL2 |
2.|----------+----------|
3.| [ | [ |
4.| "197", | "197", |
5.| "128", | "168", |
6.| "1", | "0", |
7.| "1" | "1" |
8.| ] | ] |
9.| [ | [ |
10.| "197", | "197", |
11.| "168", | "168", |
12.| "2", | "3", |
13.| "1" | "1" |
14.| ] | ]
Which SnowFlake Function can Elon use to transform this semi structured data in the output for-mat?
- A. CONVERT_TO_ARRAY
- B. NEST
- C. SPLIT
- D. GROUP_BY_CONNECT
Answer: C
NEW QUESTION # 40
Snowpipe loads data from files as soon as they are available in a stage. Automated data loads lever-age event notifications for cloud storage to inform Snowpipe of the arrival of new data files to load. Which Cloud hosted platform provides cross cloud support for automated data loading via Snow-pipe?
- A. GCP
- B. AZURE
- C. AWS
- D. None of the Above currently provide cross cloud support for Snowpipe.
Answer: C
Explanation:
Explanation
Cross-cloud support only available to accounts hosted on Amazon Web Services currently.
NEW QUESTION # 41
As Data Engineer, you have been asked to access data held in AWS Glacier Deep Archive storage class for Historical Data Analysis, which one is the correct statement to recommend?
- A. Loading data from AWS cloud storage services is supported regardless of the cloud platform that hosts your Snowflake account.
- B. We can simply access AWS Glacier Deep Archive storage External Stage data using PUT command.
- C. Data can be accessed from External stage using AWS Private link in this case.
- D. You cannot access data held in archival cloud storage classes that requires restoration before it can be retrieved.
- E. Upload (i.e. stage) files to your cloud storage account using the tools provided by the cloud storage service.
Answer: D
Explanation:
Explanation
External stage
References data files stored in a location outside of Snowflake. Currently, the following cloud stor-age services are supported:
Amazon S3 buckets
Google Cloud Storage buckets
Microsoft Azure containers
The storage location can be either private/protected or public.
You cannot access data held in archival cloud storage classes that requires restoration before it can be retrieved. These archival storage classes include, for example, the Amazon S3 Glacier Flexible Retrieval or Glacier Deep Archive storage class, or Microsoft Azure Archive Storage.
NEW QUESTION # 42
Which UDF programming language is not supported with Snowflake Secure Data Sharing feature?
- A. JAVA
- B. JAVASCRIPT
- C. PYTHON
- D. SQL
Answer: B
NEW QUESTION # 43
Which two Account usage views can be used for auditing Dynamic data masking purpose?
- A. POLICY_REFERENCES
- B. MASKING POLICIES
- C. DYNAMIC MASKING POLICIES
- D. DYNAMIC POLICY_REFERENCES
Answer: A,B
NEW QUESTION # 44
John, Data Engineer, do have technical requirements to refresh the External tables Metadata period-ically or in auto mode, which approach John can take to meet this technical specification?
- A. Snowflake implicitly take care this Infrastructure needs, as underlying warehouse layer internally manage the refresh. No action needed from John.
- B. External table cannot be scheduled via Snowflake Tasks, 3rd party tools/scripts needs to be used provided by External cloud storage provider.
- C. He can create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.
- D. John can use AUTO_REFRESH parameter if the underlying External Cloud host sup-ports this for External tables.
Answer: C,D
Explanation:
Explanation
Both Option A & B are correct.
For Refreshing External Table Metadata on a Auto Mode, John can use the AUTO_REFRESH pa-rameter properties of External table.When an external table is created, the AUTO_REFRESH pa-rameter is set to TRUE by default.
When an external table is created, the AUTO_REFRESH parameter is set to TRUE by default.
Snowflake recommend that you accept this default value for external tables that reference data files in either Amazon S3 or Microsoft Azure stages.
However, the automatic refresh option is not available currently for external tables that reference Google Cloud Storage stages.
For these external tables, manually refreshing the metadata on a schedule can be useful.
The following example refreshes the metadata for an external table named snowdb.snowschema.snow_ext_table (using ALTER EXTERNAL TABLE ... REFRESH) on a schedule.
-- Create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.
1.CREATE TASK snow_ext_table_refresh_task
2.WAREHOUSE=mywh
3.SCHEDULE='5 minutes'
4.AS
5.ALTER EXTERNAL TABLE snowmydb.snowmyschema.snow_ext_table REFRESH;
NEW QUESTION # 45
Search optimization works best to improve the performance of a query when the following condi-tions are true:[Select All that apply]
- A. The table is frequently queried on columns other than the primary cluster key.
- B. The table is not clustered.
- C. Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use IN.
- D. Search Query uses Sort Operations.
Answer: A,B,C
Explanation:
Explanation
Materialized Views works best for search query performance in case of Sort Operations. For Rest of the points Search optimization works best to improve query performance.
NEW QUESTION # 46
Which callback function is required within a JavaScript User-Defined Function (UDF) for it to execute successfully?
- A. initialize ()
- B. processRow ()
- C. handler
- D. finalize ()
Answer: B
Explanation:
Explanation
The processRow () callback function is required within a JavaScript UDF for it to execute successfully. This function defines how each row of input data is processed and what output is returned. The other callback functions are optional and can be used for initialization, finalization, or error handling.
NEW QUESTION # 47
In one of your created Schema, you have been required to create Internal Stages, what are the In-correct considerations you can noticed from the below options? [Select All that Apply]
- A. A table stage is available for each table created in Snowflake.
- B. A named internal stage type can store files that are staged and managed by one or more users and loaded into one or more tables.
- C. Table stage type is designed to store files that are staged and managed by one or more users but only loaded into a single table.
- D. User stages can be altered or dropped just like Table Stage.
Answer: D
Explanation:
Explanation
A stage specifies where data files are stored (i.e. "staged") so that the data in the files can be loaded into a table.
Types of Internal Stages
User Stages
Table Stages
Named Stages
By default, each user and table in Snowflake is automatically allocated an internal stage for staging data files to be loaded. In addition, you can create named internal stages.
File staging information is required during both steps in the data loading process:
You must specify an internal stage in the PUT command when uploading files to Snowflake.
You must specify the same stage in the COPY INTO <table> command when loading data into a table from the staged files.
Consider the best type of stage for specific data files. Each option provides benefits and potential drawbacks.
User Stages
Each user has a Snowflake stage allocated to them by default for storing files. This stage is a con-venient option if your files will only be accessed by a single user, but need to be copied into multi-ple tables.
User stages have the following characteristics and limitations:
User stages are referenced using @~; e.g. use LIST @~ to list the files in a user stage.
Unlike named stages, user stages cannot be altered or dropped.
User stages do not support setting file format options. Instead, you must specify file format and copy options as part of the COPY INTO <table> command.
This option is not appropriate if:
Multiple users require access to the files.
The current user does not have INSERT privileges on the tables the data will be loaded into.
Table Stages
Each table has a Snowflake stage allocated to it by default for storing files. This stage is a conven-ient option if your files need to be accessible to multiple users and only need to be copied into a sin-gle table.
Table stages have the following characteristics and limitations:
Table stages have the same name as the table; e.g. a table named mytable has a stage referenced as
@%mytable.
Unlike named stages, table stages cannot be altered or dropped.
Table stages do not support transforming data while loading it (i.e. using a query as the source for the COPY command).
Note that a table stage is not a separate database object; rather, it is an implicit stage tied to the table itself. A table stage has no grantable privileges of its own. To stage files to a table stage, list the files, query them on the stage, or drop them, you must be the table owner (have the role with the OWNERSHIP privilege on the table).
This option is not appropriate if you need to copy the data in the files into multiple tables.
Named Stages
Named stages are database objects that provide the greatest degree of flexibility for data loading:
Users with the appropriate privileges on the stage can load data into any table.
Because the stage is a database object, the security/access rules that apply to all objects apply. The privileges to use a stage can be granted or revoked from roles. In addition, ownership of the stage can be transferred to another role.
If you plan to stage data files that will be loaded only by you, or will be loaded only into a single table, then you may prefer to simply use either your user stage or the stage for the table into which you will be loading data.
Named stages are optional but recommended when you plan regular data loads that could involve multiple users and/or tables.
NEW QUESTION # 48
To troubleshoot data load failure in one of your Copy Statement, Data Engineer have Executed a COPY statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS with reference to the set of files he had attempted to load. Which below function can facilitate analysis of the problematic records on top of the Results produced? [Select 2]
- A. Rejected_record
- B. RESULT_SCAN
- C. LAST_QUERY_ID
- D. LOAD_ERROR
Answer: B,C
Explanation:
Explanation
LAST_QUERY_ID() Function
Returns the ID of a specified query in the current session. If no query is specified, the most recently executed query is returned.
RESULT_SCAN() Function
Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table.
The following example validates a set of files (SFfile.csv.gz) that contain errors. To facilitate analy-sis of the errors, a COPY INTO <location> statement then unloads the problematic records into a text file so they could be analyzed and fixed in the original data files. The statement queries the RESULT_SCAN table.
1.#copy into Snowtable
2.from @SFstage/SFfile.csv.gz
3.validation_mode=return_all_errors;
4.#set qid=last_query_id();
5.#copy into @SFstage/errors/load_errors.txt from (select rejected_record from ta-ble(result_scan($qid))); Note: Other options are not valid functions.
NEW QUESTION # 49
Snowflake supports using key pair authentication for enhanced authentication security as an alterna-tive to basic authentication (i.e. username and password). Select the list of SnowFlake Clients sup-port the same?
[Select All that Apply]
- A. Node.js
- B. SnowFlake Connector for Spark
- C. SnowCD
- D. Go Driver
- E. SnowSQL
Answer: A,B,D,E
NEW QUESTION # 50
......
Best updated resource for DEA-C01 Online Practice Exam: https://torrentpdf.practicedump.com/DEA-C01-exam-questions.html