Achive your Success with Latest Snowflake DEA-C01 Exam [Jan 21, 2024]
The DEA-C01 Exam Test For Brief Preparation
NEW QUESTION # 23
David, a Lead Data engineer with XYZ company looking out to improve query performance & oth-er benefits while working with Tables, Regular Views, MVs and Cached Results.
Which one of the following does not shows key similarities and differences between tables, regular views, cached query results, and materialized views while choosing any of them by David?
- A. Cached Query Results: Used only if data has not changed and if query only uses de-terministic functions (e.g. not CURRENT_DATE).
- B. As with non-materialized views, a materialized view automatically inherits the privileges of its base table.
- C. Regular views do not cache data, and therefore cannot improve performance by cach-ing.
- D. Materialized views are faster than tables because of their "cache" (i.e. the query results for the view); in addition, if data has changed, they can use their "cache" for data that hasn't changed and use the base table for any data that has changed.
- E. Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.
Answer: B
Explanation:
Explanation
Materialized Views, like other database objects (tables, views, UDFs, etc.), are owned by a role and have privileges that can be granted to other roles.
You can grant the following privileges on a materialized view:
SELECT
As with non-materialized views, a materialized view does not automatically inherit the privileges of its base table. You should explicitly grant privileges on the materialized view to the roles that should use that view.
As with non-materialized views, a user who wishes to access a materialized view needs privileges only on the view, not on the underlying object(s) that the view references.
Rest is correct.
NEW QUESTION # 24
A new customer table is created by a data pipeline in a Snowflake schema where MANAGED ACCESSenabled.
.... Can gran access to the CUSTOMER table? (Select THREE.)
- A. The role that owns the schema
- B. The SYSADMIN role
- C. The USERADMIN role with the manage grants privilege
- D. The role that owns the customer table
- E. The role that owns the database
- F. The SECURITYADMIN role
Answer: A,E,F
Explanation:
Explanation
The roles that can grant access to the CUSTOMER table are the role that owns the schema, the role that owns the database, and the SECURITYADMIN role. These roles have the ownership or the manage grants privilege on the schema or the database level, which allows them to grant access to any object within them. The other options are incorrect because they do not have the necessary privilege to grant access to the CUSTOMER table. Option C is incorrect because the role that owns the customer table cannot grant access to itself or to other roles. Option D is incorrect because the SYSADMIN role does not have the manage grants privilege by default and cannot grant access to objects that it does not own. Option F is incorrect because the USERADMIN role with the manage grants privilege can only grant access to users and roles, not to tables.
NEW QUESTION # 25
Which connector creates the RECORD_CONTENT and RECORD_METADATA columns in the existing Snowflake table while connecting to Snowflake?
- A. Kafka Connector
- B. Spark Connector
- C. Python Connector
- D. Node.js connector
Answer: A
Explanation:
Explanation
Apache Kafka software uses a publish and subscribe model to write and read streams of records, similar to a message queue or enterprise messaging system. Kafka allows processes to read and write messages asynchronously. A subscriber does not need to be connected directly to a publisher; a pub-lisher can queue a message in Kafka for the subscriber to receive later.
An application publishes messages to a topic, and an application subscribes to a topic to receive those messages. Kafka can process, as well as transmit, messages; however, that is outside the scope of this document. Topics can be divided into partitions to increase scalability.
Kafka Connect is a framework for connecting Kafka with external systems, including databases. A Kafka Connect cluster is a separate cluster from the Kafka cluster. The Kafka Connect cluster sup-ports running and scaling out connectors (components that support reading and/or writing between external systems).
The Kafka connector is designed to run in a Kafka Connect cluster to read data from Kafka topics and write the data into Snowflake tables.
Every Snowflake table loaded by the Kafka connector has a schema consisting of two VARIANT columns:
RECORD_CONTENT. This contains the Kafka message.
RECORD_METADATA. This contains metadata about the message, for example, the topic from which the message was read.
NEW QUESTION # 26
Robert, A Data Engineer, found that Pipe become stale as it was paused for longer than the limited retention period for event messages received for the pipe (14 days by default) & also the previous pipe owner transfers the ownership of this pipe to Robert role while the pipe was paused. How Robert in this case, Resume this stale pipe?
- A. PIPE needs to recreate in this scenario, as pipe already past 14 days of period & stale.
- B. He can apply System function SYSTEM$PIPE_STALE_RESUME with ALTER PIPE statement.
- C. Robert can use SYSTEM$PIPE_FORCE_RESUME function to resume this stale pipe.
- D. select sys-tem$pipe_force_resume('mydb.myschema.stalepipe','staleness_check_override, ownership_transfer_check_override');
- E. ALTER PIPES ... RESUME statement will resume the pipe.
Answer: D
Explanation:
Explanation
When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.
To resume a stale pipe, a qualified role must call the SYSTEM$PIPE_FORCE_RESUME function and input the STALENESS_CHECK_OVERRIDE argument. This argument indicates an under-standing that the role is resuming a stale pipe.
For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema:
SELECT SYS-TEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override'); While the stale pipe was paused, if ownership of the pipe was transferred to another role, then re-suming the pipe requires the additional OWNERSHIP_TRANSFER_CHECK_OVERRIDE argu-ment. For example, resume the stale stalepipe2 pipe in the mydb.myschema database and schema, which transferred to a new role:
SELECT SYS-TEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override, own-ership_transfer_check_override');
NEW QUESTION # 27
You can execute zero, one, or more transactions inside a stored procedure?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 28
The Snowpipe API provides REST endpoints for fetching load reports. One of the Endpoint named insertReport helps to retrieves a report of files submitted via insertFiles end point whose contents were recently ingested into a table. A success response (200) contains information about files that have recently been added to the table. Response Looks like below:
1.{
2."pipe": "SNOWTESTDB.SFTESTSCHEMA.SFpipe",
3."completeResult": true,
4."nextBeginMark": "1_16",
5."files": [
6.{
7."path": "data4859992083898.csv",
8."stageLocation": "s3://mybucket/",
9."fileSize": 89,
10."timeReceived": "2022-01-31T04:47:41.453Z",
11."lastInsertTime": "2022-01-31T04:48:28.575Z",
12."rowsInserted": 1,
13."rowsParsed": 1,
14."errorsSeen": 0,
15."errorLimit": 1,
16."complete": true,
17."status": "????"
18.}
19.]
20.}
Which one is the correct value of status string data in the Response Body?
- A. LOAD_SUCCESS
- B. SUCCESS
- C. LOADED
- D. LOADED_SUCCESS
Answer: A
Explanation:
Explanation
Permissible Load status for the file:
LOAD_IN_PROGRESS: Part of the file has been loaded into the table, but the load process has not completed yet.
LOADED: The entire file has been loaded successfully into the table.
LOAD_FAILED: The file load failed.
PARTIALLY_LOADED: Some rows from this file were loaded successfully, but others were not loaded due to errors. Processing of this file is completed.
Please not the different Response Codes available with their meaning.
200 - Success. Report returned.
400 - Failure. Invalid request due to an invalid format, or limit exceeded.
404 - Failure. pipeName not recognized.
This error code can also be returned if the role used when calling the endpoint does not have suffi-cient privileges. For more information, see Granting Access Privileges.
429 - Failure. Request rate limit exceeded.
500 - Failure. Internal error occurred.
As you could understand from the questions, there is 200 Success response returned, Status in the response body would be LOADED.
NEW QUESTION # 29
For SQL UDFs, The invoker of the function need not have access to the objects referenced in the function definition, but only needs the privilege to use the function?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 30
In Which Data Modelling Technique, Data Engineer generally refer the terms Hubs & Satellites?
- A. Data Hub
- B. Snowflake Schema
- C. Data Vault
- D. Star Schema
Answer: C
Explanation:
Explanation
In Data Vault modelling, Hubs are entities of interest to the business.
They contain just a distinct list of business keys and metadata about when each key was first loaded and from where.
In Data Vault modelling, Satellites connect to Hubs or Links. They are Point in Time: so we can ask and answer the question, "what did we know when?" Satellites contain data about their parent Hub or Link and metadata about when the data was load-ed, from where, and a business effectivity date.
NEW QUESTION # 31
What is a characteristic of the use of external tokenization?
- A. Secure data sharing can be used with external tokenization
- B. External tokenization allows (he preservation of analytical values after de-identification
- C. External tokenization cannot be used with database replication
- D. Pre-loading of unmasked data is supported with external tokenization
Answer: B
Explanation:
Explanation
External tokenization is a feature in Snowflake that allows users to replace sensitive data values with tokens that are generated and managed by an external service. External tokenization allows the preservation of analytical values after de-identification, such as preserving the format, length, or range of the original values.
This way, users can perform analytics on the tokenized data without compromising the security or privacy of the sensitive data.
NEW QUESTION # 32
If external software i.e. TIBCO, exports Data fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, How Snowflake handle it? [Select 2]
- A. Snowflake reads the leading space rather than the opening quotation character as the beginning of the field and the quotation characters are interpreted as string data.
(Correct) - B. field_optionally_enclosed_by option along with TRIM_IF function in COPY INTO statement can be used to handle this scenario successfully.
- C. COPY command trims the leading space and removes the quotation marks enclosing each field
1.copy into SFtable
2.from @%SFtable
3.file_format = (type = csv trim_space=true field_optionally_enclosed_by = '0x22'); - D. Snowflake automatically handles leading spaces by trimming implicitly & removes the quotation marks enclosing each field.
Answer: C
Explanation:
Explanation
If your external software exports fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, Snowflake reads the leading space rather than the open-ing quotation character as the beginning of the field. The quotation characters are interpreted as string data.
Use the TRIM_SPACE file format option to remove undesirable spaces during the data load.
NEW QUESTION # 33
A Data Engineer would like to define a file structure for loading and unloading data Where can the file structure be defined? (Select THREE)
- A. pipe object
- B. INSERT command
- C. stage object
- D. FILE FORMAT Object
- E. MERGE command
- F. copy command
Answer: C,D,F
Explanation:
Explanation
The places where the file format can be defined are copy command, file format object, and stage object. These places allow specifying or referencing a file format that defines how data files are parsed and loaded into or unloaded from Snowflake tables. A file format can include various options, such as field delimiter, field enclosure, compression type, date format, etc. The other options are not places where the file format can be defined. Option B is incorrect because MERGE command is a SQL command that can merge data from one table into another based on a join condition, but it does not involve loading or unloading data files. Option D is incorrect because pipe object is a Snowflake object that can load data from an external stage into a Snowflake table using COPY statements, but it does not define or reference a file format. Option F is incorrect because INSERT command is a SQL command that can insert data into a Snowflake table from literal values or subqueries, but it does not involve loading or unloading data files.
NEW QUESTION # 34
UDTFs also called a table function, returns zero, one, or multiple rows for each input row?
- A. NO
- B. YES
Answer: B
Explanation:
Explanation
UDFs may be scalar or tabular.
A scalar function returns one output row for each input row. The returned row consists of a single column/value.
A tabular function, also called a table function, returns zero, one, or multiple rows for each input row. A tabular UDF is defined by specifying a return clause that contains the TABLE keyword and specifies the names and data types of the columns in the table results. Tabular UDFs are often called UDTFs (user-defined table functions) or table UDFs.
NEW QUESTION # 35
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. TRUE
- B. FALSE
Answer: A
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 # 36
Which two Account usage views can be used for auditing Dynamic data masking purpose?
- A. MASKING POLICIES
- B. DYNAMIC POLICY_REFERENCES
- C. POLICY_REFERENCES
- D. DYNAMIC MASKING POLICIES
Answer: A,C
NEW QUESTION # 37
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. 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.
- C. Create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data.
- D. 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
Answer: C
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 # 38
Which are supported Programming Languages for Creating UDTFs?
- A. Java
- B. Python
- C. Perl
- D. Javascript
- E. Node.javascript
Answer: A,B,D
NEW QUESTION # 39
......
Revolutionary Guide To Exam Snowflake Dumps: https://www.free4dump.com/DEA-C01-braindumps-torrent.html
Pass DEA-C01 Exam Latest Practice Questions: https://drive.google.com/open?id=16iqrHXvGsEIyEPeeSGCBD4w1K64fM4li