100% Real DEA-C01 dumps - Brilliant DEA-C01 Exam Questions PDF
DEA-C01 Exam PDF [2025] Tests Free Updated Today with Correct 132 Questions
Snowflake DEA-C01 Exam Syllabus Topics:
| Topic | Details |
|---|---|
| Topic 1 |
|
| Topic 2 |
|
| Topic 3 |
|
| Topic 4 |
|
| Topic 5 |
|
NEW QUESTION # 11
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: B
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 # 12
When would a Data engineer use table with the flatten function instead of the lateral flatten combination?
- A. Whenthe LATERALFLATTENcombination requires no other source m the from clause to refer to
- B. When table withFLATTENis acting like a sub-query executed for each returned row
- C. WhenTABLE with FLATTENrequires no additional source m the from clause to refer to
- D. When TABLE with FLATTENrequires another source in the from clause to refer to
Answer: D
Explanation:
Explanation
The TABLE function with the FLATTEN function is used to flatten semi-structured data, such as JSON or XML, into a relational format. The TABLE function returns a table expression that can be used in the FROM clause of a query. The TABLE function with the FLATTEN function requires another source in the FROM clause to refer to, such as a table, view, or subquery that contains the semi-structured data. For example:
SELECT t.value:city::string AS city, f.value AS population FROM cities t, TABLE(FLATTEN(input => t.value:population)) f; In this example, the TABLE function with the FLATTEN function refers to the cities table in the FROM clause, which contains JSON data in a variant column named value. The FLATTEN function flattens the population array within each JSON object and returns a table expression with two columns: key and value.
The query then selects the city and population values from the table expression.
NEW QUESTION # 13
Melissa, Senior Data Engineer, looking out to optimize query performance for one of the Critical Control Dashboard, she found that most of the searches by the users on the control dashboards are based on Equality search on all the underlying columns mostly. Which Best techniques she should consider here?
- A. Melissa can create Indexes & Hints on the searchable columns to speed up Equality search.
- B. She can go for clustering on underlying tables which can speedup Equality searches.
- C. A materialized view speeds both equality searches and range searches.
- D. The search optimization service would best fit here as it can be applied to all underlying columns & speeds up equality searches.
(Correct)
Answer: D
Explanation:
Explanation
Clustering a table can speed any of the following, as long as they are on the clustering key:
Range searches.
Equality searches.
However, a table can be clustered on only a single key (which can contain one or more columns or expressions).
The search optimization service speeds equality searches. However, this applies to all the columns of supported types in a table that has search optimization enabled. This is what required here& best fit for purpose.
A materialized view speeds both equality searches and range searches, as well as some sort opera-tions, but only for the subset of rows and columns included in the materialized view.
NEW QUESTION # 14
At what isolation level are Snowflake streams?
- A. Read uncommitted
- B. Repeatable read
- C. Snapshot
- D. Read committed
Answer: B
Explanation:
Explanation
The isolation level of Snowflake streams is repeatable read, which means that each transaction sees a consistent snapshot of data that does not change during its execution. Streams use time travel internally to provide this isolation level and ensure that queries on streams return consistent results regardless of concurrent transactions on their source tables.
NEW QUESTION # 15
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. 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.
- C. Regular views do not cache data, and therefore cannot improve performance by cach-ing.
- D. As with non-materialized views, a materialized view automatically inherits the privileges of its base table.
- 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: D
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 # 16
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_DATA
- B. SYSTEM$STREAM_HAS_CHANGE_DATA
- C. SYSTEM$STREAM_DELTA_DATA
- D. SYSTEM$STREAM_CDC_DATA
Answer: A
Explanation:
Explanation
SYSTEM$STREAM_HAS_DATA
Indicates whether a specified stream contains change data capture (CDC) records.
NEW QUESTION # 17
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. 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.
- B. He needs to query history views to get the queryID as best practices.
- C. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
- D. 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)
Answer: D
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 # 18
In Which Data Modelling Technique, Data Engineer generally refer the terms Hubs & Satellites?
- A. Snowflake Schema
- B. Data Hub
- 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 # 19
What is a characteristic of the use of external tokenization?
- A. External tokenization cannot be used with database replication
- B. Pre-loading of unmasked data is supported with external tokenization
- C. Secure data sharing can be used with external tokenization
- D. External tokenization allows (he preservation of analytical values after de-identification
Answer: D
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 # 20
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 # 21
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 multiple audience values (i.e. Audience or Audience Restriction Fields) in the SAML 2.0 assertion from the identity provider to Snowflake.
- C. Snowflake supports using SSO with organizations, and you can use the corresponding URL in the SAML2 security integration.
- D. 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.
Answer: A,B,C,D
NEW QUESTION # 22
When created, a stream logically takes an initial snapshot of every row in the source object and the contents of a stream change as DML statements execute on the source table.
A Data Engineer, Sophie Created a view that queries the table and returns the CURRENT_USER and CURRENT_TIMESTAMP values for the query transaction. A Stream has been created on views to capture CDC.
Tony, another user inserted the data e.g.
insert into <table> values (1),(2),(3);
Emily, another user also inserted the data e.g.
insert into <table> values (4),(5),(6);
What will happened when Different user queries the same stream after 1 hour?
- A. All the 6 records would be shown with METADATA$ACTION as 'INSERT' out of which 3 records would be displayed with username 'Tony' & rest 3 records would be displayed with username 'Emily'.
- B. All the Six records would be displayed with User 'Sohpie' Who is the owner of the View.
- C. All the Six Records would be displayed with CURRENT_USER & CUR-RENT_TIMESTAMP while querying Streams.
- D. User would be displayed with the one who queried during the session, but Recorded timestamp would be of past 1 hour i.e. actual records insertion time.
Answer: C
Explanation:
Explanation
When User queries the stream, the stream returns the username for the user. The stream also returns the current timestamp for the query transaction in each row, NOT the timestamp when each row was inserted.
NEW QUESTION # 23
A database contains a table and a stored procedure defined as.
No other operations are affecting the log_table.
What will be the outcome of the procedure call?
- A. The Iog_table contains one record and the stored procedure returned 1 as a return value
- B. The Iog_table contains zero records and the stored procedure returned 1 as a return value
- C. The log_table contains one record and the stored procedure returned NULL as a return value
- D. The Iog_table contains zero records and the stored procedure returned NULL as a return value
Answer: A
Explanation:
Explanation
The stored procedure is defined with a FLOAT return type and a JavaScript language. The body of the stored procedure contains a SQL statement that inserts a row into the log_table with a value of '1' for col1. The body also contains a return statement that returns 1 as a float value. When the stored procedure is called with any VARCHAR parameter, it will execute successfully and insert one record into the log_table and return 1 as a return value. The other options are not correct because:
The log_table will not be empty after the stored procedure call, as it will contain one record inserted by the SQL statement.
The stored procedure will not return NULL as a return value, as it has an explicit return statement that returns 1.
NEW QUESTION # 24
Can Masking policies be applied to virtual columns?
- A. FALSE
- B. TRUE
Answer: A
NEW QUESTION # 25
A healthcare company uses Amazon Kinesis Data Streams to stream real-time health data from wearable devices, hospital equipment, and patient records.
A data engineer needs to find a solution to process the streaming data. The data engineer needs to store the data in an Amazon Redshift Serverless warehouse. The solution must support near real-time analytics of the streaming data and the previous day's data.
Which solution will meet these requirements with the LEAST operational overhead?
- A. Use the Amazon Aurora zero-ETL integration with Amazon Redshift.
- B. Load data into Amazon Kinesis Data Firehose. Load the data into Amazon Redshift.
- C. Use the streaming ingestion feature of Amazon Redshift.
- D. Load the data into Amazon S3. Use the COPY command to load the data into Amazon Redshift.
Answer: C
Explanation:
https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-streaming-ingestion.html Use the Streaming Ingestion Feature of Amazon Redshift: Amazon Redshift recently introduced streaming data ingestion, allowing Redshift to consume data directly from Kinesis Data Streams in near real-time. This feature simplifies the architecture by eliminating the need for intermediate steps or services, and it is specifically designed to support near real-time analytics. The operational overhead is minimal since the feature is integrated within Redshift.
NEW QUESTION # 26
You can execute zero, one, or more transactions inside a stored procedure?
- A. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 27
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 automatically handles leading spaces by trimming implicitly & removes the quotation marks enclosing each field.
- B. 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'); - C. 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) - D. field_optionally_enclosed_by option along with TRIM_IF function in COPY INTO statement can be used to handle this scenario successfully.
Answer: B
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 # 28
A company is migrating its database servers from Amazon EC2 instances that run Microsoft SQL Server to Amazon RDS for Microsoft SQL Server DB instances. The company's analytics team must export large data elements every day until the migration is complete. The data elements are the result of SQL joins across multiple tables. The data must be in Apache Parquet format. The analytics team must store the data in Amazon S3.
Which solution will meet these requirements in the MOST operationally efficient way?
- A. Create an AWS Lambda function that queries the EC2 instance-based databases by using Java Database Connectivity (JDBC). Configure the Lambda function to retrieve the required data, transform the data into Parquet format, and transfer the data into an S3 bucket. Use Amazon EventBridge to schedule the Lambda function to run every day.
- B. Schedule SQL Server Agent to run a daily SQL query that selects the desired data elements from the EC2 instance-based SQL Server databases. Configure the query to direct the output .csv objects to an S3 bucket. Create an S3 event that invokes an AWS Lambda function to transform the output format from .csv to Parquet.
- C. Create a view in the EC2 instance-based SQL Server databases that contains the required data elements. Create an AWS Glue job that selects the data directly from the view and transfers the data in Parquet format to an S3 bucket. Schedule the AWS Glue job to run every day.
- D. Use a SQL query to create a view in the EC2 instance-based SQL Server databases that contains the required data elements. Create and run an AWS Glue crawler to read the view.
Create an AWS Glue job that retrieves the data and transfers the data in Parquet format to an S3 bucket. Schedule the AWS Glue job to run every day.
Answer: C
NEW QUESTION # 29
Data Engineer is using existing pipe that automates data loads using event notifications, later he figured out the needs to modify pipe properties. For the same, He decided to recreate the pipe as best practice. He followed the below steps for the same.
1. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
2. Recreate the pipe (using CREATE OR REPLACE PIPE).
3. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?
- A. Pause the pipe (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = true) Pre & Post recreation & Resume after recreation (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = false).
- B. Force the pipe to resume (using SYSTEM$PIPE_FORCE_RESUME).
- C. Terminate the existing pipe (using ALTER PIPE ... SET PIPE_EXECUTION_TERMINATE = true) before recreation.
- D. CREATE OR REPLACE PIPE command will recreate the PIPE successfully.
Answer: A
Explanation:
Explanation
Recreating a pipe (using a CREATE OR REPLACE PIPE statement) is necessary to modify most pipe properties.
Recreating Pipes for Automated Data Loads
When recreating a pipe that automates data loads using event notifications, it's recommended that Data Engineer complete the following steps:
1. Pause the pipe (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = true).
2. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is PAUSED.
3. Recreate the pipe (using CREATE OR REPLACE PIPE).
4. Pause the pipe again.
5. Review the configuration steps for your cloud messaging service to ensure the settings are still accurate.
6. Query the SYSTEM$PIPE_STATUS function again and verify that the pipe execution state is RUNNING.
NEW QUESTION # 30
Streams cannot be created to query change data on which of the following objects? [Select All that Apply]
- A. Directory tables
- B. Query Log Tables
- C. Standard tables, including shared tables.
- D. Views, including secure views
- E. External tables
Answer: B
Explanation:
Explanation
Streams supports all the listed objects except Query Log tables.
NEW QUESTION # 31
A media company uses software as a service (SaaS) applications to gather data by using third- party tools. The company needs to store the data in an Amazon S3 bucket. The company will use Amazon Redshift to perform analytics based on the data.
Which AWS service or feature will meet these requirements with the LEAST operational overhead?
- A. Amazon Kinesis
- B. Amazon Managed Streaming for Apache Kafka (Amazon MSK)
- C. AWS Glue Data Catalog
- D. Amazon AppFlow
Answer: D
Explanation:
https://d1.awsstatic.com/solutions/guidance/architecture-diagrams/integrating-third-party-saas- data-using-amazon-appflow.pdf Amazon AppFlow is a fully managed integration service that enables you to securely transfer data between Software as a Service (SaaS) applications like Salesforce, Marketo, Slack, and ServiceNow, and AWS services like Amazon S3 and Amazon Redshift, in just a few clicks. It can store the raw data pulled from SaaS applications in Amazon S3, and integrates with AWS Glue Data Catalog to catalog and store metadata.
NEW QUESTION # 32
The COPY command supports several options for loading data files from a stage i.e.
I. By path
II. Specifying a list of specific files to load.
III. Using pattern matching to identify specific files by pattern.
IV. Organize files into logical paths that reflect a scheduling pattern.
Of the aforesaid options for identifying/specifying data files to load from a stage, which option in general is the fastest & best considerate?
- A. I
- B. IV
- C. II
- D. III
Answer: C
Explanation:
Explanation
Of the above options for identifying/specifying data files to load from a stage, providing a discrete list of files is generally the fastest; however, the FILES parameter supports a maximum of 1,000 files, meaning a COPY command executed with the FILES parameter can only load up to 1,000 files.
For example:
copy into load1 from @%load1/Snow1/ files=('mydata1.csv', 'mydata2.csv', 'mydata3.csv')
NEW QUESTION # 33
......
Verified & Correct DEA-C01 Practice Test Reliable Source May 04, 2025 Updated: https://www.realexamfree.com/DEA-C01-real-exam-dumps.html
Snowflake DEA-C01 Exam Preparation Guide and PDF Download: https://drive.google.com/open?id=17fUFhIFoExXUlJoxlx5onNTeNNiH1jR6
