[Full-Version] 2024 New Preparation Guide of Snowflake DEA-C01 Exam
DEA-C01 Practice Exam - 67 Unique Questions
NEW QUESTION # 17
Jonas, a Lead Performance Engineer,identifed that some of the operation of his query which func-tionally remove the duplicates from huge data set is spilling the data to remote disk. How can he alleviate spilling to a remote disk for better query performance?
- A. Data Sharing can be helpful to improve query performance.
- B. He can Process data in smaller batches to manage workload.
- C. Jonas can recommend using a large warehouse which effectively increase the available memory/local disk space for the operations.
- D. Spilling do not have a profound effect on query performance (especially if remote disk is used for spilling).
Answer: B,C
Explanation:
Explanation
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).
To alleviate this, It is recommend that:
Using a larger warehouse (effectively increasing the available memory/local disk space for the op-eration), and/or Processing data in smaller batches.
NEW QUESTION # 18
Which Role that is dedicated to user and role management only?
- A. USERADMIN
- B. SYSADMIN
- C. ORGADMIN
- D. PUBLIC
- E. SECURITYADMIN
Answer: A
NEW QUESTION # 19
You can execute zero, one, or more transactions inside a stored procedure?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 20
Marko, a Data Engineer is using Snowpipe for data loading in micro batches for one of the Finance Data workloads. There are set of files he attempted to load into the snowflake table using Snow-pipe. While monitoring he found that there are set of files has multiple issue, He queried the COPY_HISTORY view & checked the STATUS column which indicates whether a particular set of files was loaded, partially loaded, or failed to load. But he wants to view all errors in the files along with Load status, how he can check all errors?
- A. Marko can look out for FIRST_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason why a file partially loaded or failed for all the files.
- B. He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_ERROR_MODE copy option set to RE-TURN_ALL_PIPE_ERRORS.
- C. He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS.
- D. He can check RETURN_ALL_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason and view all errors in the files.
Answer: C
Explanation:
Explanation
The STATUS column indicates whether a particular set of files was loaded, partially loaded, or failed to load.
The FIRST_ERROR_MESSAGE column provides a reason when an attempt partial-ly loaded or failed.
Note that if a set of files has multiple issues, the FIRST_ERROR_MESSAGE column only indi-cates the first error encountered. To view all errors in the files, execute a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS. The VALIDATION_MODE copy option instructs a COPY statement to validate the data to be loaded and return results based on the validation option specified. No data is loaded when this copy option is specified.
NEW QUESTION # 21
Snowflake does not provide which of following set of SQL functions to support retrieving infor-mation about tasks?
- A. SYSTEM$TASK_DEPENDENTS_ENABLE
- B. TASK_HISTORY
- C. TASK_QUERY_HISTORY
- D. SYSTEM$CURRENT_USER_TASK_NAME
- E. TASK_DEPENDENTS
Answer: E
Explanation:
Explanation
SYSTEM$CURRENT_USER_TASK_NAME
Returns the name of the task currently executing when invoked from the statement or stored proce-dure defined by the task.
SYSTEM$TASK_DEPENDENTS_ENABLE
Recursively resumes all dependent tasks tied to a specified root task.
TASK_DEPENDENTS
This table function returns the list of child tasks for a given root task in a DAG of tasks.
TASK_HISTORY
This table function can be used to query the history of task usage within a specified date range.
NEW QUESTION # 22
SYSTEM$CLUSTERING_INFORMATION functions returns clustering information, including average clustering depth, for a table based on one or more columns in the table. The function returns a JSON object containing average_overlaps name/value pairs. Does High average_overlaps indicates well organized Clustering?
- A. YES
- B. NO
Answer: B
Explanation:
Explanation
Higher the avg_overlap indicates poorly organized clustering.
NEW QUESTION # 23
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. 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.
- B. Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.
- C. Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.
- D. When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.
Answer: A
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 # 24
What is the purpose of the BUILD_FILE_URL function in Snowflake?
- A. It generates a permanent URL for accessing files in a stage.
- B. It generates a staged URL for accessing a file in a stage.
- C. It generates an encrypted URL foe accessing a file in a stage.
- D. It generates a temporary URL for accessing a file in a stage.
Answer: B
Explanation:
Explanation
The BUILD_FILE_URL function in Snowflake generates a temporary URL for accessing a file in a stage. The function takes two arguments: the stage name and the file path. The generated URL is valid for 24 hours and can be used to download or view the file contents. The other options are incorrect because they do not describe the purpose of the BUILD_FILE_URL function.
NEW QUESTION # 25
Which are supported Programming Languages for Creating UDTFs?
- A. Node.javascript
- B. Python
- C. Perl
- D. Java
- E. Javascript
Answer: B,D,E
NEW QUESTION # 26
A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Which value type it can returns?
- A. Single Value
- B. A Set of Rows
- C. Regex
- D. Scaler or Tabular depend on input SQL expression
Answer: D
NEW QUESTION # 27
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 # 28
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. 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'); - B. 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) - C. field_optionally_enclosed_by option along with TRIM_IF function in COPY INTO statement can be used to handle this scenario successfully.
- D. Snowflake automatically handles leading spaces by trimming implicitly & removes the quotation marks enclosing each field.
Answer: A
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 # 29
Changing the retention period for your account or individual objects changes the value for all lower-level objects that do not have a retention period explicitly set?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 30
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 # 31
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_HAS_CHANGE_DATA
- B. SYSTEM$STREAM_DELTA_DATA
- C. SYSTEM$STREAM_CDC_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 # 32
Which stages support external tables?
- A. External stages only, only on the same region and cloud provider as the Snowflake account
- B. internal stages only from any Snowflake account in the organization
- C. Internal stages only; within a single Snowflake account
- D. External stages only from any region, and any cloud provider
Answer: D
Explanation:
Explanation
External stages only from any region, and any cloud provider support external tables. External tables are virtual tables that can query data from files stored in external stages without loading them into Snowflake tables. External stages are references to locations outside of Snowflake, such as Amazon S3 buckets, Azure Blob Storage containers, or Google Cloud Storage buckets. External stages can be created from any region and any cloud provider, as long as they have a valid URL and credentials. The other options are incorrect because internal stages do notsupport external tables. Internal stages are locations within Snowflake that can store files for loading or unloading data. Internal stages can be user stages, table stages, or named stages.
NEW QUESTION # 33
......
Latest Questions DEA-C01 Guide to Prepare Free Practice Tests: https://protechtraining.actualtestsit.com/Snowflake/DEA-C01-exam-prep-dumps.html