Snowflake DEA-C01 Online Practice Questions and Exam Preparation

Snowflake DEA-C01 Online Questions & Answers

• Question 41:

  A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe.

  Which recommendations apply to this scenario? (Select THREE)

  A. Load large files (1 GB or larger)
  B. Ensure that data files are 100-250 MB (or larger) in size compressed
  C. Load a single huge array containing multiple records into a single table row
  D. Verify each value of each unique element stores a single native data type (string or number)
  E. Extract semi-structured data elements containing null values into relational columns before loading
  F. Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute
  B. Ensure that data files are 100-250 MB (or larger) in size compressed
  D. Verify each value of each unique element stores a single native data type (string or number)
  F. Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute

  ---

  Explanation

  The recommendations that apply to this scenario are:

  Ensure that data files are 100-250 MB (or larger) in size compressed: This recommendation will improve Snowpipe performance by reducing the number of files that need to be loaded and increasing the parallelism of loading. Smallerfiles can

  cause performance degradation or errors due to excessive metadata operations or network latency.

  Verify each value of each unique element stores a single native data type (string or number): This recommendation will improve Snowpipe performance by avoiding data type conversions or errors when loading JSON data into variant

  columns. Snowflake supports two native data types for JSON elements: string and number. If an element has mixed data types across different files or records, such as string and boolean, Snowflake will either convert them to string or raise

  an error, depending on the FILE_FORMAT option.

  Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute: This recommendation will minimize Snowpipe costs by reducing the number of notifications that need to be sent to

  Snowpipe for auto-ingestion. Snowpipe charges for notifications based on the number of files per notification and the frequency of notifications. By creating smaller files and staging them at a lower frequency, fewer notifications will be needed.

• Question 42:

  A large table with 200 columns contains two years of historical data. When queried. the table is filtered on a single day Below is the Query Profile:

  

  Using a size 2XL virtual warehouse, this query look over an hour to complete What will improve the query performance the MOST?

  A. increase the size of the virtual warehouse.
  B. Increase the number of clusters in the virtual warehouse
  C. Implement the search optimization service on the table
  D. Add a date column as a cluster key on the table
  D. Add a date column as a cluster key on the table

  ---

  Explanation

  Adding a date column as a cluster key on the table will improve the query performance by reducing the number of micro-partitions that need to be scanned. Since the table is filtered on a single day, clustering by date will make the query more selective and efficient.

• Question 43:

  What is a characteristic of the operations of streams in Snowflake?

  A. Whenever a stream is queried, the offset is automatically advanced.
  B. When a stream is used to update a target table the offset is advanced to the current time.
  C. Querying a stream returns all change records and table rows from the current offset to the current time.
  D. Each committed and uncommitted transaction on the source table automatically puts a change record in the stream.
  C. Querying a stream returns all change records and table rows from the current offset to the current time.

  ---

  Explanation

  A stream is a Snowflake object that records the history of changes made to a table. A stream has an offset, which is a point in time that marks the beginning of the change records to be returned by the stream. Querying a stream returns all change records and table rows from the current offset to the current time. The offset is not automatically advanced by querying the stream, but it can be manually advanced by using the ALTER STREAM command. When a stream is used to update a target table, the offset is advanced to the current time only if the ON UPDATE clause is specified in the stream definition. Each committed transaction on the source table automatically puts a change record in the stream, but uncommitted transactions do not.

• Question 44:

  Which output is provided by both theSYSTEM$CLUSTERING_DEPTHfunction and theSYSTEM$CLUSTERING_INFORMATIONfunction?

  A. average_depth
  B. notes
  C. average_overlaps
  D. total_partition_count
  A. average_depth

  ---

  Explanation

  The output that is provided by both the SYSTEM$CLUSTERING_DEPTH function and the SYSTEM$CLUSTERING_INFORMATION function is average_depth. This output indicates the average number of micro-partitions that contain data for a given column value or combinationof column values. The other outputs are not common to both functions. The notes output is only provided by the SYSTEM$CLUSTERING_INFORMATION function and it contains additional information or recommendations about the clustering status of the table. The average_overlaps output is only provided by the SYSTEM$CLUSTERING_DEPTH function and it indicates the average number of micro-partitions that overlap with other micro-partitions for a given column value or combination of column values. The total_partition_count output is only provided by the SYSTEM$CLUSTERING_INFORMATION function and it indicates the total number of micro-partitions in the table.

• Question 45:

  A Data Engineer is working on a Snowflake deployment in AWS eu-west-1 (Ireland). The Engineer is planning to load data from staged files into target tables using the copy into command

  Which sources are valid? (Select THREE)

  A. Internal stage on GCP us-central1 (Iowa)
  B. Internal stage on AWS eu-central-1 (Frankfurt)
  C. External stage on GCP us-central1 (Iowa)
  D. External stage in an Amazon S3 bucket on AWS eu-west-1 (Ireland)
  E. External stage in an Amazon S3 bucket on AWS eu-central 1 (Frankfurt)
  F. SSO attached to an Amazon EC2 instance on AWS eu-west-1 (Ireland)
  C. External stage on GCP us-central1 (Iowa)
  D. External stage in an Amazon S3 bucket on AWS eu-west-1 (Ireland)
  E. External stage in an Amazon S3 bucket on AWS eu-central 1 (Frankfurt)

  ---

  Explanation

  The valid sources for loading data from staged files into target tables using the copy into command are:

  External stage on GCP us-central1 (Iowa): This is a valid source because Snowflake supports cross-cloud data loading from external stages on different cloud platforms and regions than the Snowflake deployment. External stage in an

  Amazon S3 bucket on AWS eu-west-1 (Ireland): This is a valid source because Snowflake supports data loading from external stages on the same cloud platform and region as the Snowflake deployment. External stage in an Amazon S3

  bucket on AWS eu-central 1 (Frankfurt): This is a valid source because Snowflake supports cross-region data loading from external stages on different regions than the Snowflake deployment within the same cloud platform. The invalid

  sources are:

  Internal stage on GCP us-central1 (Iowa): This is an invalid source because internal stages are always located on the same cloud platform and region as the Snowflake deployment. Therefore, an internal stage on GCP us-central1 (Iowa)

  cannot be used for a Snowflake deployment on AWS eu-west-1 (Ireland). Internal stage on AWS eu-central-1 (Frankfurt): This is an invalid source because internal stages are always located on the same region as the Snowflake deployment.

  Therefore, an internal stage on AWS eu-central-1 (Frankfurt) cannot be used for a Snowflake deployment on AWS eu-west-1 (Ireland). SSO attached to an Amazon EC2 instance on AWS eu-west-1 (Ireland): This is an invalid source because

  SSO stands for Single Sign-On, which is a security integration feature in Snowflake, not a data staging option.

• Question 46:

  The following chart represents the performance of a virtual warehouse over time:

  

  A DataEngineer notices that the warehouse is queueing queries The warehouse is size X- Smallthe minimum and maximum cluster counts are set to 1 the scaling policy is set to i and auto-suspend is set to 10 minutes.

  How canthe performance be improved?

  A. Change the cluster settings
  B. Increase the size of the warehouse
  C. Change the scaling policy to economy
  D. Change auto-suspend to a longer time frame
  B. Increase the size of the warehouse

  ---

  Explanation

  The performance can be improved by increasing the size of the warehouse. The chart shows that the warehouse is queueing queries, which means that there are more queries than the warehouse can handle at its current size. Increasing the size of the warehouse will increase its processing power and concurrency limit, which could reduce the queueing time and improve the performance. The other options are not likely to improve the performance significantly. Option A, changing the cluster settings, will not help unless the minimum and maximum cluster countsare increased to allow for multi-cluster scaling. Option C, changing the scaling policy to economy, will not help because it will reduce the responsiveness of the warehouse to scale up or down based on demand. Option D, changing auto-suspend to a longer time frame, will not help because it will only affect how long the warehouse stays idle before suspending itself.

• Question 47:

  A Data Engineer wants to centralize grant management to maximize security. A user needs ownership on a table m a new schema However, this user should not have the ability to make grant decisions

  What is the correct way to do this?

  A. Grant ownership to the user on the table
  B. Revoke grant decisions from the user on the table
  C. Revoke grant decisions from the user on the schema.
  D. Add the with managed access parameter on the schema
  D. Add the with managed access parameter on the schema

  ---

  Explanation

  The with managed access parameter on the schema enables the schema owner to control the grant and revoke privileges on the objects within the schema. This way, the user who owns the table cannot make grant decisions, but only the schema owner can. This is the best way to centralize grant management and maximize security.

• Question 48:

  A Data Engineer defines the following masking policy:

  

  ....

  must be applied to the full_name column in the customer table:

  

  Which query will apply the masking policy on the full_name column?

  A. ALTER TABLE customer MODIFY COLUMN full_name Set MASKING POLICY name_policy;
  B. ALTER TABLE customer MODIFY COLUMN full_nam ADD MASKING POLICY name_poiicy;
  C. ALTER TABLE customer MODIFY COLUMN first_nane SET MASKING POLICY name_policy; lasT_name SET MASKING POLICY name_pclicy;
  D. ALTER TABLE customer MODIFY COLUMN first_name ADD MASKING POLICY name_policy,
  A. ALTER TABLE customer MODIFY COLUMN full_name Set MASKING POLICY name_policy;

  ---

  Explanation

  The query that will apply the masking policy on the full_name column is ALTER TABLE customer MODIFY COLUMN full_name SET MASKING POLICY name_policy;. This query will modify the full_name column and associate it with the name_policy masking policy, which will mask the first and last names of the customers with asterisks. The other options are incorrect because they do not follow the correct syntax for applying a masking policy on a column. Option B is incorrect because it uses ADD instead of SET, which is not a valid keyword for modifying a column. Option C is incorrect because it tries to apply the masking policy on two columns, first_name and last_name, which are not part of the table structure. Option D is incorrect because it uses commas instead of dots to separate the database, schema, and table names

• Question 49:

  Which callback function is required within a JavaScript User-Defined Function (UDF) for it to execute successfully?

  A. initialize ()
  B. processRow ()
  C. handler
  D. finalize ()
  B. processRow ()

  ---

  Explanation

  The processRow () callback function is required within a JavaScript UDF for it to execute successfully. This function defines how each row of input data is processed and what output is returned. The other callback functions are optional and can be used for initialization, finalization, or error handling.

• Question 50:

  A Data Engineer has created table t1 with datatype VARIANT:

  create or replace table t1 (cl variant);

  The Engineer has loaded the following JSON data set. which has information about 4 laptop models into the table:

  The Engineer now wants to query that data set so that results are shown as normal structured data. The result should be 4 rows and 4 columns without the double quotes surrounding the data elements in the JSON data.

  The result should be similar to the use case where the data was selected from a normal relational table z2 where t2 has string data type columns model__id. model, manufacturer, and =iccisi_r.an=. and is queried with the SQL clause select *

  from t2;

  Which select command will produce the correct results?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

  ---

  Explanation