DEA-C01 Exam Dumps, DEA-C01 Practice Test Questions
PDF (New 2024) Actual Snowflake DEA-C01 Exam Questions
Snowflake DEA-C01 Exam Syllabus Topics:
| Topic | Details |
|---|---|
| Topic 1 |
|
| Topic 2 |
|
| Topic 3 |
|
| Topic 4 |
|
| Topic 5 |
|
NEW QUESTION # 19
Assuming that the session parameter USE_CACHED_RESULT is set to false, what are characteristics of Snowflake virtual warehouses in terms of the use of Snowpark?
- A. Creating a DataFrame from a table will start a virtual warehouse
- B. Transforming a DataFrame with methods like replace () will start a virtual warehouse -
- C. Calling a Snowpark stored procedure to query the database with session, call () will start a virtual warehouse
- D. Creating a DataFrame from a staged file with the read () method will start a virtual warehouse
Answer: A
Explanation:
Explanation
Creating a DataFrame from a table will start a virtual warehouse because it requires reading data from Snowflake. The other options will not start a virtual warehouse because they either operate on local data or use an existing session to query Snowflake.
NEW QUESTION # 20
How Data Engineer can do Monitoring of Files which are Staged Internally during Continuous data pipelines loading process? [Select all that apply]
- A. She can use the DATA_VALIDATE function to validate the data files She have loaded and can retrieve any errors encountered during the load.
- B. She can Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.
- C. Snowflake retains historical data for COPY INTO commands executed within the pre-vious 14 days.
- D. She Can Monitor the files using Metadata maintained by Snowflake i.e. file-name,last_modified date etc.
- E. She can use the DATA_LOAD_HISTORY Information Schema view to retrieve the history of data loaded into tables using the COPY INTO command.
Answer: B,C,D
Explanation:
Explanation
Monitoring Files Staged Internally
Snowflake maintains detailed metadata for each file uploaded into internal stage (for users, tables, and stages), including:
File name
File size (compressed, if compression was specified during upload)
LAST_MODIFIED date, i.e. the timestamp when the data file was initially staged or when it was last modified, whichever is later In addition, Snowflake retains historical data for COPY INTO commands executed within the pre-vious 14 days. The metadata can be used to monitor and manage the loading process, including de-leting files after upload completes:
Use the LIST command to view the status of data files that have been staged.
Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.
Use the VALIDATE function to validate the data files you've loaded and retrieve any errors en-countered during the load.
Use the LOAD_HISTORY Information Schema view to retrieve the history of data loaded into tables using the COPY INTO command.
NEW QUESTION # 21
Within a Snowflake account permissions have been defined with custom roles and role hierarchies.
To set up column-level masking using a role in the hierarchy of the current user, what command would be used?
- A. CORRECT_ROLE
- B. IS_GRANTED_TO_INVOKER_ROLE
- C. IKVOKER_ROLE
- D. IS_RCLE_IN_SESSION
Answer: D
Explanation:
Explanation
The IS_ROLE_IN_SESSION function is used to set up column-level masking using a role in the hierarchy of the current user. Column-level masking is a feature in Snowflake that allows users to apply dynamic data masking policies to specific columns based on the roles of the users who access them. The IS_ROLE_IN_SESSION function takes a role name as an argument and returns true if the role is in the current user's session, or false otherwise. The function can be used in a masking policy expression to determine whether to mask or unmask a column value based on the role of the user. For example:
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string -> CASE WHEN IS_ROLE_IN_SESSION('HR') THEN val ELSE REGEXP_REPLACE(val, '(.).(.@.)', '\1****\2') END; In this example, the IS_ROLE_IN_SESSION function is used to create a masking policy for an email column.
The masking policy returns the original email value if the user has the HR role in their session, or returns a masked email value with asterisks if not.
NEW QUESTION # 22
A data engineer is using Amazon Athena to analyze sales data that is in Amazon S3. The data engineer writes a query to retrieve sales amounts for 2023 for several products from a table named sales_data. However, the query does not return results for all of the products that are in the sales_data table. The data engineer needs to troubleshoot the query to resolve the issue.
The data engineer's original query is as follows:
SELECT product_name, sum(sales_amount)
FROM sales_data
WHERE year = 2023
GROUP BY product_name
How should the data engineer modify the Athena query to meet these requirements?
- A. Replace sum(sales_amount) with count(*) for the aggregation.
- B. Change WHERE year = 2023 to WHERE extract(year FROM sales_data) = 2023.
- C. Add HAVING sum(sales_amount) > 0 after the GROUP BY clause.
- D. Remove the GROUP BY clause.
Answer: C
Explanation:
https://docs.aws.amazon.com/athena/latest/ug/select.html
NEW QUESTION # 23
Snowflake does not treat the inner transaction as nested; instead, the inner transaction is a separate transaction.
What is term used to call these Transaction?
- A. Inner Transaction
- B. Enclosed Transaction
- C. Nested Scope Transaction
- D. Atomic Transaction
- E. Scoped transactions
Answer: E
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 temporary 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 staged URL for accessing a file in a stage.
Answer: D
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
A company wants to migrate an application and an on-premises Apache Kafka server to AWS.
The application processes incremental updates that an on-premises Oracle database sends to the Kafka server. The company wants to use the replatform migration strategy instead of the refactor strategy.
Which solution will meet these requirements with the LEAST management overhead?
- A. Amazon Kinesis Data Firehose
- B. Amazon Managed Streaming for Apache Kafka (Amazon MSK) provisioned cluster
- C. Amazon Kinesis Data Streams
- D. Amazon Managed Streaming for Apache Kafka (Amazon MSK) Serverless
Answer: D
NEW QUESTION # 26
A media company wants to improve a system that recommends media content to customer based on user behavior and preferences. To improve the recommendation system, the company needs to incorporate insights from third-party datasets into the company's existing analytics platform.
The company wants to minimize the effort and time required to incorporate third-party datasets.
Which solution will meet these requirements with the LEAST operational overhead?
- A. Use Amazon Kinesis Data Streams to access and integrate third-party datasets from Amazon Elastic Container Registry (Amazon ECR).
- B. Use API calls to access and integrate third-party datasets from AWS Data Exchange.
- C. Use Amazon Kinesis Data Streams to access and integrate third-party datasets from AWS CodeCommit repositories.
- D. Use API calls to access and integrate third-party datasets from AWS DataSync.
Answer: D
Explanation:
Data Exchange is the AWS official third-party datasets repository:
https://aws.amazon.com/data-exchange
NEW QUESTION # 27
If using a JavaScript UDF in a masking policy, Data Engineer needs to ensure the data type of the column, UDF, and masking policy match irrespective of case-sensitivity?
- A. TRUE
- B. FALSE
Answer: B
Explanation:
Explanation
Please note JavaScript is case sensitive but if we are using a JavaScript UDF in a masking policy, ensure the data type of the column, UDF, and masking policy match.
NEW QUESTION # 28
Select the incorrect statements regarding Clustering depth?
- A. Clustering depth can be used for determining whether a large table would benefit from explicitly defining a clustering key.
- B. The clustering depth for a populated table measures the average depth (1 or greater) of the overlapping micro-partitions for specified columns in a table. The smaller the aver-age depth, the better clustered the table is with regards to the specified columns.
- C. A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 1.
(Correct) - D. It helps Monitoring the clustering "health" of a large table, particularly over time as DML is performed on the table.
Answer: C
Explanation:
Explanation
A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 0.
NEW QUESTION # 29
A Data Engineer is investigating a query that is taking a long time to return The Query Profile shows the following:
What step should the Engineer take to increase the query performance?
- A. Change the order of the joins and start with smaller tables first
- B. Rewrite the query using Common Table Expressions (CTEs)
- C. Add additional virtual warehouses.
- D. increasethe size of the virtual warehouse.
Answer: D
Explanation:
Explanation
The step that the Engineer should take to increase the query performance is to increase the size of the virtual warehouse. The Query Profile shows that most of the time was spent on local disk IO, which indicates that the query was reading a lot of data from disk rather than from cache. This could be due to a large amount of data being scanned or a low cache hit ratio. Increasing the size of the virtual warehouse will increase the amount of memory and cache available for the query, which could reduce the disk IO time and improve the query performance. The other options are not likely to increase the query performance significantly. Option A, adding additional virtual warehouses, will not help unless they are used in a multi-cluster warehouse configuration or for concurrent queries. Option C, rewriting the query using Common Table Expressions (CTEs), will not affect the amount of data scanned or cached by the query. Option D, changing the order of the joins and starting with smaller tables first, will not reduce the disk IO time unless it also reduces the amount of data scanned or cached by the query.
NEW QUESTION # 30
Which are false statements about Star Schema?
- A. The star schema separates business process data into facts, which hold the measurable, quantitative data about a business, and dimensions which are descriptive attributes re-lated to fact data.
- B. The star schema is an important special case of the snowflake schema and is more effec-tive for handling simpler queries.
- C. Star schema is more flexible in terms of analytical needs compared to Data Vault Mod-elling.
- D. Star schemas are denormalized.
Answer: C
NEW QUESTION # 31
A company has used an Amazon Redshift table that is named Orders for 6 months. The company performs weekly updates and deletes on the table. The table has an interleaved sort key on a column that contains AWS Regions.
The company wants to reclaim disk space so that the company will not run out of storage space.
The company also wants to analyze the sort key column.
Which Amazon Redshift command will meet these requirements?
- A. VACUUM REINDEX Orders
- B. VACUUM FULL Orders
- C. VACUUM DELETE ONLY Orders
- D. VACUUM SORT ONLY Orders
Answer: B
NEW QUESTION # 32
Which of the below concepts/functions helps while implementing advanced Column-level Security?
- A. INVOKER_ROLE
- B. Role Hierarchy
- C. CURRENT_CLIENT
- D. CURRENT_ROLE
Answer: A,B,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 # 33
A company stores details about transactions in an Amazon S3 bucket. The company wants to log all writes to the S3 bucket into another S3 bucket that is in the same AWS Region.
Which solution will meet this requirement with the LEAST operational effort?
- A. Create a trail of management events in AWS CloudTraiL. Configure the trail to receive data from the transactions S3 bucket. Specify an empty prefix and write-only events. Specify the logs S3 bucket as the destination bucket.
- B. Configure an S3 Event Notifications rule for all activities on the transactions S3 bucket to invoke an AWS Lambda function. Program the Lambda function to write the event to Amazon Kinesis Data Firehose. Configure Kinesis Data Firehose to write the event to the logs S3 bucket.
- C. Create a trail of data events in AWS CloudTraiL. Configure the trail to receive data from the transactions S3 bucket. Specify an empty prefix and write-only events. Specify the logs S3 bucket as the destination bucket.
- D. Configure an S3 Event Notifications rule for all activities on the transactions S3 bucket to invoke an AWS Lambda function. Program the Lambda function to write the events to the logs S3 bucket.
Answer: C
Explanation:
https://docs.aws.amazon.com/AmazonS3/latest/userguide/logging-with-S3.html
NEW QUESTION # 34
A retail company stores transactions, store locations, and customer information tables in four reserved ra3.4xlarge Amazon Redshift cluster nodes. All three tables use even table distribution.
The company updates the store location table only once or twice every few years.
A data engineer notices that Redshift queues are slowing down because the whole store location table is constantly being broadcast to all four compute nodes for most queries. The data engineer wants to speed up the query performance by minimizing the broadcasting of the store location table.
Which solution will meet these requirements in the MOST cost-effective way?
- A. Upgrade the Redshift reserved node to a larger instance size in the same instance family.
- B. Add a join column named store_id into the sort key for all the tables.
- C. Change the distribution style of the store location table to KEY distribution based on the column that has the highest dimension.
- D. Change the distribution style of the store location table from EVEN distribution to ALL distribution.
Answer: D
Explanation:
Changing the distribution style of the store location table to ALL distribution (A) is the most cost- effective solution. It directly addresses the issue of broadcasting by ensuring the entire table is available on each node, significantly improving join performance without incurring substantial additional costs.
NEW QUESTION # 35
Company DEF has a strict security policy that mandates that all data at rest in Amazon S3 must be encrypted. They want to ensure that the encryption keys are managed by AWS, but they also want the flexibility to change the encryption keys when required.
Which of the following encryption methods best meets Company DEF's requirements?
- A. Server-Side Encryption with Customer-Provided Keys (SSE-C).
- B. Server-Side Encryption with Amazon S3 Managed Keys (SSE-S3).
- C. Client-Side Encryption with a client-side master key.
- D. Server-Side Encryption with AWS Key Management Service (SSE-KMS).
Answer: D
NEW QUESTION # 36
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_CDC_DATA
- B. SYSTEM$STREAM_DELTA_DATA
- C. SYSTEM$STREAM_HAS_CHANGE_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 # 37
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. CALL $CLUSTERINS_INFORMATION('Invoice');
- C. CALL SYSTEM$CLUSTERING_INFORMATION ('Invoice');
- D. SELECT $CLUSTERXNG_INFQRMATION ('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 # 38
Given the table sales which has a clustering key of column CLOSED_DATE which table function will return the average clustering depth for the SALES_REPRESENTATIVEcolumn for the North American region?
- A.
- B.
- C.
- D.
Answer: A
Explanation:
Explanation
The table function SYSTEM$CLUSTERING_DEPTH returns the average clustering depth for a specified column or set of columns in a table. The function takes two arguments: the table name and the column name(s). In this case, the table name is sales and the column name is SALES_REPRESENTATIVE. The function also supports a WHERE clause to filter the rows for which the clustering depth is calculated. In this case, the WHERE clause is REGION = 'North America'. Therefore, the function call in Option B will return the desired result.
NEW QUESTION # 39
What is a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake?
- A. Only JavaScript variables of type number, string and sf Date can be bound
- B. Users are restricted from binding JavaScript variables because they create SQL injection attack vulnerabilities
- C. All types of JavaScript variables can be bound
- D. All Snowflake first-class objects can be bound
Answer: A
Explanation:
Explanation
A characteristic of the use of binding variables in JavaScript stored procedures in Snowflake is that only JavaScript variables of type number, string and sf Date can be bound. Binding variables are a way to pass values from JavaScript variables to SQL statements within a stored procedure. Binding variables can improve the security and performance of the stored procedure by preventing SQL injection attacks and reducing the parsing overhead. However, not all types of JavaScript variables can be bound. Only the primitive types number and string, and the Snowflake-specific type sf Date, can be bound. The other options are incorrect because they do not describe a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake. Option A is incorrect because authenticator is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option B is incorrect because arrow_number_to_decimal is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option D is incorrect because users are not restricted from binding JavaScript variables, but encouraged to do so.
NEW QUESTION # 40
You as Data engineer might want to consider disabling auto-suspend for a warehouse if?
- A. You have a heavy, steady workload for the warehouse.
- B. You have a low, fluctuating workload for the warehouse.
- C. You require the warehouse to be available with delay.
- D. You require the warehouse to be available with no delay or lag time.
Answer: A,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 # 41
......
Updated Dec-2024 Pass DEA-C01 Exam - Real Practice Test Questions: https://www.examdiscuss.com/Snowflake/exam/DEA-C01/
Dumps Moneyack Guarantee - DEA-C01 Dumps UpTo 90% Off: https://drive.google.com/open?id=17Q-mQckn3SDUF8TtFr3cMpbTBmQNuc9R