Snowflake DEA-C01 Online Practice Questions and Exam Preparation

Snowflake DEA-C01 Online Questions & Answers

• Question 11:

  What is a characteristic of the use of external tokenization?

  A. Secure data sharing can be used with external tokenization
  B. External tokenization cannot be used with database replication
  C. Pre-loading of unmasked data is supported with external tokenization
  D. External tokenization allows (he preservation of analytical values after de-identification
  D. External tokenization allows (he preservation of analytical values after de-identification

  ---

  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.

• Question 12:

  A Data Engineer wants to create a new development database (DEV) as a clone of the permanent production database (PROD) There is a requirement to disable Fail-safe for all tables.

  Which command will meet these requirements?

  A. CREATE DATABASE DEV CLONE PROD FAIL_SAFE=FALSE;
  B. CREATE DATABASE DEV CLONE PROD;
  C. CREATE TRANSIENT DATABASE DEV CLONE RPOD
  D. CREATE DATABASE DEV CLOSE PROD DATA_RETENTION_TIME_IN_DAYS =0L
  C. CREATE TRANSIENT DATABASE DEV CLONE RPOD

  ---

  Explanation

  This option will meet the requirements of creating a new development database (DEV) as a clone of the permanent production database (PROD) and disabling Fail-safe for all tables. By using the CREATE TRANSIENT DATABASE command, the Data Engineer can create a transient database that does not have Fail-safe enabled by default. Fail-safe is a feature in Snowflake that provides additional protection against data loss by retaining historical data for seven days beyond the time travel retention period. Transient databases do not have Fail-safe enabled, which means that they do not incur additional storage costs for historical data beyond their time travel retention period. By using the CLONE option, the Data Engineer can create an exact copy of the PROD database, including its schemas, tables, views, and other objects.

• Question 13:

  A Data Engineer wants to check the status of a pipe named my_pipe. The pipe is inside a database named test and a schema named Extract (case-sensitive).

  Which querywill provide the status of the pipe?

  A. SELECT FROM SYSTEM$PIPE_STATUS (''test.'extract'.my_pipe"i:
  B. SELECT FROM SYSTEM$PIPE_STATUS (,test.,,Extracr,,.ny_pipe, i I
  C. SELE2T * FROM SYSTEM$PIPE_STATUS < ' test. "Extract", my_pipe');
  D. SELECT * FROM SYSTEM$PIPE_STATUS ("test. 'extract' .my_pipe"};
  C. SELE2T * FROM SYSTEM$PIPE_STATUS < ' test. "Extract", my_pipe');

  ---

  Explanation

  The query that will provide the status of the pipe is SELECT * FROM SYSTEM$PIPE_STATUS(`test."Extract".my_pipe');. The SYSTEM$PIPE_STATUS function returns information about a pipe, such as its name, status, last received message timestamp, etc. The function takes one argument: the pipe name in a qualified form. The pipe name should include the database name, the schema name, and the pipe name, separated by dots. If any of these names are case-sensitive identifiers, they should be enclosed in double quotes. In this case, the schema name Extract is case-sensitive and should be quoted. The other options are incorrect because they do not follow the correct syntax for the pipe name argument. Option A and B use single quotes instead of double quotes for case-sensitive identifiers. Option D uses double quotes instead of single quotes for non-case-sensitive identifiers.

• Question 14:

  A company is using Snowpipe to bring in millions of rows every day of Change Data Capture (CDC) into a Snowflake staging table on a real-time basis The CDC needs to get processedand combined with other data in Snowflake and land in a final table as part of the full data pipeline.

  How can a Data engineer MOST efficiently process the incoming CDC on an ongoing basis?

  A. Create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data.
  B. Transform the data during the data load with Snowpipe by modifying the related copy into statement to include transformation steps such as case statements andJOIN'S.
  C. Schedule a task that dynamically retrieves the last time the task was run from information_schema-rask_hiSwOry and use that timestamp to process the delta of the new rows since the last time the task was run.
  D. Use a create ok replace table as statement that references the staging table and includes all the transformation SQL. Use a task to run the full create or replace table as statement on a scheduled basis
  A. Create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data.

  ---

  Explanation

  The most efficient way to process the incoming CDC on an ongoing basis is to create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data. A stream is a Snowflake object that records changes made to a table, such as inserts, updates, or deletes. A stream can be queried like a table and can provide information about what rows have changed since the last time the stream was consumed. A task is a Snowflake object that can execute SQL statements on a schedule without requiring a warehouse. A task can be configured to run only when certain conditions are met, such as when a stream has data or when another task has completed successfully. By creating a stream on the staging table and scheduling a task that transforms data from the stream, the Data Engineer can ensure that only new or modified rows are processed and that no unnecessary computations are performed.

• Question 15:

  A new customer table is created by a data pipeline in a Snowflake schema where MANAGED ACCESSenabled.

  .... Can gran access to the CUSTOMER table? (Select THREE.)

  A. The role that owns the schema
  B. The role that owns the database
  C. The role that owns the customer table
  D. The SYSADMIN role
  E. The SECURITYADMIN role
  F. The USERADMIN role with the manage grants privilege
  A. The role that owns the schema
  B. The role that owns the database
  E. The SECURITYADMIN role

  ---

  Explanation

  The roles that can grant access to the CUSTOMER table are the role that owns the schema, the role that owns the database, and the SECURITYADMIN role. These roles have the ownership or the manage grants privilege on the schema or the database level, which allows them to grant access to any object within them. The other options are incorrect because they do not have the necessary privilege to grant access to the CUSTOMER table. Option C is incorrect because the role that owns the customer table cannot grant access to itself or to other roles. Option D is incorrect because the SYSADMIN role does not have the manage grants privilege by default and cannot grant access to objects that it does not own. Option F is incorrect because the USERADMIN role with the manage grants privilege can only grant access to users and roles, not to tables.

• Question 16:

  Which stages support external tables?

  A. Internal stages only; within a single Snowflake account
  B. internal stages only from any Snowflake account in the organization
  C. External stages only from any region, and any cloud provider
  D. External stages only, only on the same region and cloud provider as the Snowflake account
  C. External stages only from any region, and any cloud provider

  ---

  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.

• Question 17:

  A Data Engineer is working on a continuous data pipeline which receives data from Amazon Kinesis Firehose and loads the data into a staging table which will later be used in the data transformation process The average file size is 300-500 MB.

  The Engineer needs to ensure that Snowpipe is performant while minimizing costs.

  How can this be achieved?

  A. Increase the size of the virtual warehouse used by Snowpipe.
  B. Split the files before loading them andset the SIZE_LIMIT option to 250 MB.
  C. Change the file compression size and increase the frequency of the Snowpipe loads
  D. Decrease the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose
  B. Split the files before loading them andset the SIZE_LIMIT option to 250 MB.

  ---

  Explanation

  This option is the best way to ensure that Snowpipe is performant while minimizing costs. By splitting the files before loading them, the Data Engineer can reduce the size of each file and increase the parallelism of loading. By

  setting the SIZE_LIMIT option to 250 MB, the Data Engineer can specify the maximum file size that can be loaded bySnowpipe, which can prevent performance degradation or errors due to large files. The other options are not optimal

  because:

  Increasing the size of the virtual warehouse used by Snowpipe will increase the performance but also increase the costs, as larger warehouses consume more credits per hour.

  Changing the file compression size and increasing the frequency of the Snowpipe loads will not have much impact on performance or costs, as Snowpipe already supports various compression formats and automatically loads files as soon as

  they are detected in the stage.

  Decreasing the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose will not affect Snowpipe performance or costs, as Snowpipe does not depend on Kinesis Firehose buffer size but rather on its own

  SIZE_LIMIT option.

• Question 18:

  A Data Engineer needs to ingest invoice data in PDF format into Snowflake so that the data can be queried and used in a forecasting solution.

  ..... recommended way to ingest this data?

  A. Use Snowpipe to ingest the files that land in an external stage into a Snowflake table
  B. Use a COPY INTO command to ingest the PDF files in an external stage into a Snowflake table with a VARIANT column.
  C. Create an external table on the PDF files that are stored in a stage and parse the data nto structured data
  D. Create a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data
  D. Create a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data

  ---

  Explanation

  The recommended way to ingest invoice data in PDF format into Snowflake is to create a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data. This option allows for more flexibility and control over how the PDF data is extracted and transformed. The other options are not suitable for ingesting PDF data into Snowflake. Option A and B are incorrect because Snowpipe and COPY INTO commands can only ingest files that are in supported file formats, such as CSV, JSON, XML, etc. PDF files are not supported by Snowflake and will cause errors or unexpected results. Option C is incorrect because external tables can only query files that are in supported file formats as well. PDF files cannot be parsed by external tables and will cause errors or unexpected results.

• Question 19:

  What kind of Snowflake integration is required when defining an external function in Snowflake?

  A. API integration
  B. HTTP integration
  C. Notification integration
  D. Security integration
  A. API integration

  ---

  Explanation

  An API integration is required when defining an external function in Snowflake. An API integration is a Snowflake object that defines how Snowflake communicates with an externalservice via HTTPS requests and responses. An API integration specifies parameters such as URL, authentication method, encryption settings, request headers, and timeout values. An API integration is used to create an external function object that invokes the external service from within SQL queries.

• Question 20:

  Database XYZ has the data_retention_time_in_days parameter set to 7 days and table xyz.public.ABC has the data_retention_time_in_daysset to 10 days.

  A Developer accidentally dropped the database containing this single table 8 days ago and just discovered the mistake.

  How can the table be recovered?

  A. undrop database xyz;
  B. create -able abc_restore as select * from xyz.public.abc at {offset => -60*60*24*8};
  C. create table abc_restore clone xyz.public.abc at (offset => -30G*24*3);
  D. Create a Snowflake Support case lo restore the database and tab e from "a i-safe
  A. undrop database xyz;

  ---

  Explanation

  The table can be recovered by using the undrop database xyz; command. This command will restore the database that was dropped within the last 14 days, along with all its schemas and tables, including the customer table. The data_retention_time_in_days parameter does not affect this command, as it only applies to time travel queries that reference historical data versions of tables or databases. The other options are not valid ways to recover the table. Option B is incorrect because creating a table as select * from xyz.public.ABC at {offset => -6060248} will not work, as this query will try to access a historical data version of the ABC table that does not exist anymore after dropping the database. Option C is incorrect because creating a table clone xyz.public.ABC at {offset => -360024*3} will not work, as this query will try to clone a historical data version of the ABC table that does not exist anymore after dropping the database. Option D is incorrect because creating a Snowflake Support case to restore the database and table from fail-safe will not work, as fail-safe is only available for disaster recovery scenarios and cannot be accessed by customers.