DEA-C01 Questions Pass on Your First Attempt Dumps for SnowPro Advanced Certified
DEA-C01 Practice Test Pdf Exam Material
NEW QUESTION # 22
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. User stages can be altered or dropped just like Table Stage.
- D. 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.
Answer: C
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 # 23
Data Engineer wants to analyze query performance & looking out for profiling information, He went to Query/Operator Details also called Profile Overview of Query Profile Interface & searching for statistics attributes around I/O. Which of the following information he can't get from there?
- A. Percentage scanned from cache - the percentage of data scanned from the local disk cache.
- B. Bytes written - bytes written (e.g. when loading into a table).
- C. Bytes sent over the wireframe - amount of data sent over the wireframe
- D. Bytes read from result - bytes read from the result object.
- E. External bytes scanned - bytes read from an external object, e.g. a stage.
Answer: C
Explanation:
Explanation
To help you analyze query performance, Query/Operator Details panel also called Profile overview panel provides two classes of profiling information:
Execution time, broken down into categories
Detailed statistics
Apart from Option a Bytes sent over the wireframe - amount of data sent over the wireframe , Rest of the Statistics Information provided by Query/Operator details in the Query Profile Inter-face.
To Know More about the Query/Operator Details options , please refer the link:
https://docs.snowflake.com/en/user-guide/ui-query-profile#query-operator-details
NEW QUESTION # 24
When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other 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 views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions. The reason is that the owner of the data being shared does not typically control the users or roles in the account with which the view is being shared.
NEW QUESTION # 25
Select the Correct statements with regard to using Federated authentication/SSO?
- A. Snowflake supports using MFA in conjunction with SSO to provide additional levels of security.
- B. Snowflake supports SSO with Private Connectivity to the Snowflake Service for Snow-flake accounts on Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform.
- C. Snowflake supports multiple audience values (i.e. Audience or Audience Restriction Fields) in the SAML 2.0 assertion from the identity provider to Snowflake.
- D. Snowflake supports using SSO with organizations, and you can use the corresponding URL in the SAML2 security integration.
Answer: A,B,C,D
NEW QUESTION # 26
External Function is a type of UDF & can be Scaler or Tabular?
- A. FALSE
- B. TRUE
Answer: A
Explanation:
Explanation
External functions must be scalar functions. A scalar external function returns a single value for each input row.
NEW QUESTION # 27
Mark the Correct Statements:
Statement 1. Enable failover for a primary database to one or more accounts in your organization using an ALTER DATABASE ... ENABLE FAILOVER TO ACCOUNTS statement.
Statement 2. Enabling failover for a primary database can be done by Data Engineer either before or after a replica of the primary database has been created in a specified account.
- A. Both are Correct.
- B. Both are False.
- C. Statement 1
- D. Statement 2
Answer: A
NEW QUESTION # 28
Jeff, a Data Engineer, accessing elements in JSON object in its 3 data loading scripts, he unknow-ingly use the upper case while accessing the elements. e.g.
Script 1 --> fruits:apple.sweet
Script 2 --> FRUITS:apple.sweet
Script 3 --> FRUITS:Apple.Sweet
Which are the correct statements?
- A. Script 1 & Script 2 traverse path twill be treated same, but Script 2 will not.
- B. Script 1,2,3 traverse path will treat in same way.
- C. Script 2&3 traverse path will be same.
- D. Script 1 & 3 traverse path will be treated in same way.
Answer: A
Explanation:
Explanation
There are two ways to access elements in a JSON object:
Dot Notation
Bracket Notation
Regardless of which notation you use, the column name is case-insensitive but element names are case-sensitive.
NEW QUESTION # 29
Which are the Cloud Platforms that Support Calling an External Function?
- A. GCP
- B. AWS only
- C. AWS,GCP,AZURE
- D. AWS & AZURE
Answer: C
NEW QUESTION # 30
Which of the following statements is/are incorrect regarding Fail-safe data recovery?
- A. 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.
- B. Data stored in temporary tables is not recoverable after the table is dropped as they do not have fail-safe.
- C. Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by 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. 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.
Answer: A
NEW QUESTION # 31
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: C
Explanation:
Explanation
Execute PUT to upload (stage) local data files into an internal stage.
@% character combination identifies a table stage.
NEW QUESTION # 32
Data Engineer, ran the below clustering depth analysis function:
select system$clustering_depth('TPCH_CUSTOMERS', '(C1, C6)', 'C9 = 30'); on TPCH_CUSTOMERS table, will return which of the following?
- A. An error: this function does not accept lists of columns as a third parameter.
- B. An error: this function does not accept predicates ('C9 = 30') as parameter.
- C. Calculate the clustering depth for a table using the clustering key defined for the table.
- D. Calculate the clustering depth for a table using mentioned columns in the table.
Answer: D
NEW QUESTION # 33
Which are supported Programming Languages for Creating UDTFs?
- A. Python
- B. Node.javascript
- C. Java
- D. Javascript
- E. Perl
Answer: A,C,D
NEW QUESTION # 34
Which is the non-supportable JavaScript UDF data types?
- A. Double
- B. Binary
- C. String
- D. Integers
Answer: D
NEW QUESTION # 35
By default, a newly-created Custom role is not assigned to any user, nor granted to any other role?
- A. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 36
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. INSERT
- B. UPDATE
- C. FALSE
- D. TRUE
Answer: C
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 # 37
Jackie, a Data engineer advised to his data team members about one of the Role highlighting fol-lows points:
1. Avoid Using the <?> Role for Automated Scripts
2. Avoid Using the <?> Role to Create Objects
Which System defined or Custom Role She is mentioning?
- A. CUSTOM Role
- B. SYSADMIN
- C. SECURITYADMIN
- D. USERADMIN
- E. ACCOUNTADMIN
Answer: E
NEW QUESTION # 38
Michael, a Data Engineer Running a Data query to achieve Union of Data sets coming from Multi-ple data sources, later he figured out that Data processing query is taking more time than expected. He started analyzing the Query performance using query profile interface. He discovered & realized that he used UNION when the UNION ALL semantics was sufficient.
Which Extra Data Processing Operator Michael figured out while doing query profile analysis in this case which helps him to identify this performance bottlenecks?
- A. Aggregate
- B. Join
- C. Flatten
- D. UNION ALL
- E. Filter
Answer: A
Explanation:
Explanation
In SQL, it is possible to combine two sets of data with either UNION or UNION ALL constructs. The difference between them is that UNION ALL simply concatenates inputs, while UNION does the same, but also performs duplicate elimination.
A common mistake is to use UNION when the UNION ALL semantics are sufficient. These que-ries show in Query Profile as a UnionAll operator with an extra Aggregate operator on top (which performs duplicate elimination).
To Know more about Data Processing Operators, please do refer:
https://docs.snowflake.com/en/user-guide/ui-query-profile#operator-types
NEW QUESTION # 39
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. 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.
- C. For Step 7, Results do include table changes committed by Transaction 1.
- D. 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.
- E. 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.
Answer: E
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 # 40
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 suspended warehouse does not provision any new compute resources for the warehouse.
- C. Resizing a warehouse to a larger size is useful while loading and unloading significant amounts of data.
- D. Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.
Answer: D
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 # 41
......
DEA-C01 [Jul-2023] Newly Released] Exam Questions For You To Pass: https://www.actualtestsquiz.com/DEA-C01-test-torrent.html
DEA-C01 Answers DEA-C01 Free Demo Are Based On The Real Exam: https://drive.google.com/open?id=1Ch8VhgP4Le4yydOv24WpZcLOse4SzZIr
