100% Free DEA-C01 Exam Dumps Use Real SnowPro Advanced Dumps With 132 Questions!
Pass Your DEA-C01 Exam Easily With 100% Exam Passing Guarantee [2023]
NEW QUESTION # 61
Data Engineer is performing below steps in sequence while working on Stream s1 created on table t1.
Step 1: Begin transaction.
Step 2: Query stream s1 on table t1.
Step 3: Update rows in table t1.
Step 4: Query stream s1.
Step 5: Commit transaction.
Step 6: Begin transaction.
Step 7: Query stream s1.
Mark the Incorrect Operational statements:
- A. For Step 5, If the stream was consumed in DML statements within the transaction, the stream position advances to the transaction start time.
- B. if Transaction 2 had begun before Transaction 1 was committed, queries to the stream would have returned a snapshot of the stream from the position of the stream to the be-ginning time of Transaction 2 and would not see any changes committed by Transac-tion 1.
- C. For Step 7, Results do include table changes committed by Transaction 1.
- D. For Step 4, Returns the CDC data records by streams with updated rows happened in the Step 3 because Streams works in Repeated committed mode in which statements see any changes made by previous statements executed within the same transaction, even though those changes are not yet committed.
- E. For Step 2, The stream returns the change data capture records between the current position to the Transaction 1 start time. If the stream is used in a DML statement, the stream is then locked to avoid changes by concurrent transactions.
Answer: D
Explanation:
Explanation
Streams support repeatable read isolation. In repeatable read mode, multiple SQL statements within a transaction see the same set of records in a stream. This differs from the read committed mode supported for tables, in which statements see any changes made by previous statements executed within the same transaction, even though those changes are not yet committed.
The delta records returned by streams in a transaction is the range from the current position of the stream until the transaction start time. The stream position advances to the transaction start time if the transaction commits; otherwise, it stays at the same position.
Within Transaction 1, all queries to stream s1 see the same set of records. DML changes to table t1 are recorded to the stream only when the transaction is committed.
In Transaction 2, queries to the stream see the changes recorded to the table in Transaction 1. Note that if Transaction 2 had begun before Transaction 1 was committed, queries to the stream would have returned a snapshot of the stream from the position of the stream to the beginning time of Transaction 2 and would not see any changes committed by Transaction 1.
NEW QUESTION # 62
Streams record the differences between two offsets. If a row is added and then updated in the cur-rent offset, what will be the value of METADATA$ISUPDATE Columns in this scenario?
- A. UPDATE
- B. FALSE
- C. TRUE
- D. INSERT
Answer: B
Explanation:
Explanation
Stream Columns
A stream stores an offset for the source object and not any actual table columns or data. When que-ried, a stream accesses and returns the historic data in the same shape as the source object (i.e. the same column names and ordering) with the following additional columns:
METADATA$ACTION
Indicates the DML operation (INSERT, DELETE) recorded.
METADATA$ISUPDATE
Indicates whether the operation was part of an UPDATE statement. Updates to rows in the source object are represented as a pair of DELETE and INSERT records in the stream with a metadata column METADATA$ISUPDATE values set to TRUE.
METADATA$ROW_ID
Specifies the unique and immutable ID for the row, which can be used to track changes to specific rows over time.
Note that streams record the differences between two offsets. If a row is added and then updated in the current offset, the delta change is a new row. The METADATA$ISUPDATE row records a FALSE value.
NEW QUESTION # 63
Which of the following statements is/are incorrect regarding Fail-safe data recovery?
- A. If downtime and the time required to reload lost data are factors, permanent tables, even with their added Fail-safe costs, may offer a better overall solution than transient tables.
- B. Historical data in transient tables can be recovered by Snowflake due to Operation fail-ure after the Time Travel retention period ends using Fail-safe.
- C. Data stored in temporary tables is not recoverable after the table is dropped as they do not have fail-safe.
- D. Short-lived tables (i.e. <1 day), such as ETL work tables, can be defined as transient to eliminate Fail-safe costs.
- E. Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.
Answer: B
NEW QUESTION # 64
Which of the below concepts/functions helps while implementing advanced Column-level Security?
- A. CURRENT_CLIENT
- B. CURRENT_ROLE
- C. Role Hierarchy
- D. INVOKER_ROLE
Answer: B,C,D
Explanation:
Explanation
Column-level Security supports using Context Functions in the conditions of the masking policy body to enforce whether a user has authorization to see data. To determine whether a user can see data in a given SQL statement, it is helpful to consider:
Masking policy conditions using CURRENT_ROLE target the role in use for the current session.
Masking policy conditions using INVOKER_ROLE target the executing role in a SQL statement.
Role hierarchy
Determine if a specified role in a masking policy condition (e.g. ANALYST custom role) is a lower privilege role in the CURRENT_ROLE or INVOKER_ROLE role hierarchy. If so, then the role returned by the CURRENT_ROLE or INVOKER_ROLE functions inherits the privileges of the specified role.
NEW QUESTION # 65
Select the correct usage statements with regards to SQL UDF?
- A. The body of a UDF cannot contain DDL statements or any DML statement other than SELECT.
- B. Scalar functions (UDFs) have a limit of 500 input arguments.
- C. You can include only one query expression.
- D. When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.
- E. All of above are correct.
Answer: E
NEW QUESTION # 66
Select the Incorrect statement about External Functions in SnowFlake?
- A. An external function does not contain its own code; instead, the external function calls code that is stored and executed outside Snowflake.
- B. Inside Snowflake, the external function is stored as a API Integration object.
- C. Inside Snowflake, the external function is stored as a database object that contains in-formation that Snowflake uses to call the remote service.
- D. An external function is a type of UDF.
Answer: B
NEW QUESTION # 67
Steven created the task, what additional privileges required by Steven on the task so that he can suspend or resume the tasks?
- A. Steven must have SUSPEND privilege on the task so that he can suspend or resume the task.
- B. Steven needs to have Global Managed RESUME privilege by TASK administrator.
- C. Steven is already owner of the task; he can execute the task & suspend/resume the task without any additional privileges.
- D. In addition to the task owner, a Steven Role must have OPERATE privilege on the task so that he can suspend or resume the task.
Answer: D
Explanation:
Explanation
In addition to the task ownership privilege, a role that has the OPERATE privilege on the task can suspend or resume the task.
NEW QUESTION # 68
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 not clustered.
- B. Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use IN.
- C. The table is frequently queried on columns other than the primary cluster key.
- 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 # 69
What are Common Query Problems a Data Engineer can identified using Query Profiler?
- A. Inefficient Pruning
- B. Queries Too Large to Fit in Memory
- C. Ineffective Data Sharing
- D. "Exploding" Joins i.e Joins resulting due to a "Cartesian product"
Answer: A,B,D
Explanation:
Explanation
"Exploding" Joins
One of the common mistakes SQL users make is joining tables without providing a join condition (resulting in a "Cartesian product"), or providing a condition where records from one table match multiple records from another table. For such queries, the Join operator produces significantly (often by orders of magnitude) more tuples than it consumes.
This can be observed by looking at the number of records produced by a Join operator in the profile interface, and typically is also reflected in Join operator consuming a lot of time.
Queries Too Large to Fit in Memory
For some operations (e.g. duplicate elimination for a huge data set), the amount of memory available for the compute resources used to execute the operation might not be sufficient to hold intermediate results. As a result, the query processing engine will start spilling the data to local disk. If the local disk space is not sufficient, the spilled data is then saved to remote disks.
This spilling can have a profound effect on query performance (especially if remote disk is used for spilling).
Spilling statistics can be checked in Query Profile Interface.
Inefficient Pruning
Snowflake collects rich statistics on data allowing it not to read unnecessary parts of a table based on the query filters. However, for this to have an effect, the data storage order needs to be correlat-ed with the query filter attributes.
The efficiency of pruning can be observed by comparing Partitions scanned and Partitions total sta-tistics in the TableScan operators. If the former is a small fraction of the latter, pruning is efficient. If not, the pruning did not have an effect.
Of course, pruning can only help for queries that actually filter out a significant amount of data. If the pruning statistics do not show data reduction, but there is a Filter operator above TableScan which filters out a number of records, this might signal that a different data organization might be beneficial for this query.
NEW QUESTION # 70
Find out the odd one out:
- A. 1. Bulk Data Load: Load history Stored in the metadata of the target table for 365 days.
2. SnowPipe: Load history Stored in the metadata of the pipe for 64 days. - B. 1. Bulk Data Load: Requires a user-specified warehouse to execute COPY statements.
2. SnowPipe: Uses Snowflake-supplied compute resources. - C. 1. Bulk Data Load: Loads are always performed in a single transaction.
2. SnowPipe: Loads are combined or split into a single or multiple transactions based on the number and size of the rows in each data file. - D. 1. Bulk Data Load: Billed for the amount of time each virtual warehouse is active.
2. SnowPipe: Billed according to the compute resources used in the Snowpipe ware-house while loading the files.
Answer: A
Explanation:
Explanation
Bulk data load
Load History Stored in the metadata of the target table for 64 days. Available upon completion of the COPY statement as the statement output.
Snowpipe
Load History Stored in the metadata of the pipe for 14 days. Must be requested from Snowflake via a REST endpoint, SQL table function, or ACCOUNT_USAGE view.
Rest are correct statements.
NEW QUESTION # 71
Mohan, Data engineer works with ALTUSO Company, wants to programmatically check the status of the query. He needs query id to identify each query executed by Snowflake & using Snowflake Connector for Python to execute a query, how he will be able to meet this requirements. Select the best options you will suggest?
- A. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
- B. He can access the query ID through the sfqid attribute in the Cursor object.
1.cur = con.cursor()
2.cur.execute("SELECT * FROM snowtable")
3.print(cur.sfqid) - C. When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.
- D. He needs to query history views to get the queryID as best practices.
Answer: B
Explanation:
Explanation
Retrieving the Snowflake Query ID
A query ID identifies each query executed by Snowflake. When you use the Snowflake Connector for Python to execute a query, you can access the query ID through the sfqid attribute in the Cursor object:
1.# Retrieving a Snowflake Query ID
2.cur = con.cursor()
3.cur.execute("SELECT * FROM testtable")
4.print(cur.sfqid)
NEW QUESTION # 72
Streams cannot be created to query change data on which of the following objects? [Select All that Apply]
- A. Standard tables, including shared tables.
- B. Directory tables
- C. External tables
- D. Views, including secure views
- E. Query Log Tables
Answer: E
Explanation:
Explanation
Streams supports all the listed objects except Query Log tables.
NEW QUESTION # 73
Data Engineer Loading File named snowdata.tsv in the /datadir directory from his local machine to Snowflake stage and try to prefix the file with a folder named tablestage, please mark the correct command which helps him to load the files data into snowflake internal Table stage?
- A. put file:///datadir/snowdata.tsv @%tablestage;
- B. put file://c:\datadir\snowdata.tsv @%tablestage;
- C. put file://c:\datadir\snowdata.tsv @tablestage;
- D. put file://c:\datadir\snowdata.tsv @~/tablestage;
Answer: B
Explanation:
Explanation
Execute PUT to upload (stage) local data files into an internal stage.
@% character combination identifies a table stage.
NEW QUESTION # 74
Mark the incorrect statement when Data Engineer implement Automating Continuous Data Loading Using Cloud Messaging?
- A. 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.
- B. Automated Snowpipe uses event notifications to determine when new files arrive in monitored cloud storage and are ready to load.
- C. Triggering automated Snowpipe data loads using S3 event messages is supported by Snowflake accounts hosted on Cloud Platform like AWS, GCP or AZURE.
- D. Notifications identify the cloud storage event and include a list of the file names. They do not include the actual data in the files.
Answer: C
Explanation:
Explanation
Triggering automated Snowpipe data loads using S3 event messages is supported by Snowflake ac-counts hosted on Amazon Web Services (AWS) only.
Rest is correct statements.
NEW QUESTION # 75
You as Data engineer might want to consider disabling auto-suspend for a warehouse if?
- A. You have a low, fluctuating workload for the warehouse.
- B. You require the warehouse to be available with delay.
- C. You have a heavy, steady workload for the warehouse.
- D. You require the warehouse to be available with no delay or lag time.
Answer: C,D
Explanation:
Explanation
Automating Warehouse Suspension
Data Engineer might want to consider disabling auto-suspend for a warehouse if:
He/She have a heavy, steady workload for the warehouse.
He/She require the warehouse to be available with no delay or lag time. Warehouse provisioning is generally very fast (e.g. 1 or 2 seconds); however, depending on the size of the warehouse and the availability of compute resources to provision, it can take longer.
If he/she chose to disable auto-suspend, He/she must carefully consider the costs associated with running a warehouse continually, even when the warehouse is not processing queries. The costs can be significant, especially for larger warehouses (X-Large, 2X-Large, etc.).
To disable auto-suspend, Engineer must explicitly select Never in the web interface, or specify 0 or NULL in SQL.
NEW QUESTION # 76
......
Study resources for the Valid DEA-C01 Braindumps: https://www.testkingit.com/Snowflake/latest-DEA-C01-exam-dumps.html
DEA-C01 Dumps are Available for Instant Access: https://drive.google.com/open?id=1CXkPQcWqJhORm6cZ5d82MsTV-A0_mafI