DEA-C02 practice questions 1–10

1. Question 1 · 126 in bank

   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?

   • AAdd additional virtual warehouses.
   • BIncrease the size of the virtual warehouse.
   • CRewrite the query using Common Table Expressions (CTEs).
   • DChange the order of the joins and start with smaller tables first.

   Show answer

   Answer: B

   Next question

2. Question 2 · 126 in bank

   How can the following relational data be transformed into semi-structured data using the LEAST amount of operational overhead?

   

   • AUse the TO_JSON function.
   • BUse the PARSE_JSON function to produce a VARIANT value.
   • CUse the OBJECT_CONSTRUCT function to return a Snowflake object.
   • DUse the TO_VARIANT function to convert each of the relational columns to VARIANT.

   Show answer

   Answer: C

   Next question

3. Question 3 · 126 in bank

   Select 3

   A Data Engineer executes a complex query and wants to make use of Snowflake’s query results caching capabilities to reuse the results. Which conditions must be met? (Choose three.)

   • AThe results must be reused within 72 hours.
   • BThe query must be executed using the same virtual warehouse.
   • CThe USED_CACHED_RESULT parameter must be included in the query.
   • DThe table structure contributing to the query result cannot have changed.
   • EThe new query must have the same syntax as the previously executed query.
   • FThe micro-partitions cannot have changed due to changes to other data in the table.

   Show answer

   Answer: DEF

   Next question

4. Question 4 · 126 in bank

   Select 3

   A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe. Which recommendations apply to this scenario? (Choose three.)

   • ALoad large files (1 GB or larger).
   • BEnsure that data files are 100-250 MB (or larger) in size, compressed.
   • CLoad a single huge array containing multiple records into a single table row.
   • DVerify each value of each unique element stores a single native data type (string or number).
   • EExtract semi-structured data elements containing null values into relational columns before loading.
   • FCreate data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute.

   Show answer

   Answer: BDE

   Next question

5. Question 5 · 126 in bank

   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_REPRESENTATIVE column for the North American region?

   • Aselect system$clustering_information('Sales', 'sales_representative', 'region = ''North America''');
   • Bselect system$clustering_depth('Sales', 'sales_representative', 'region = ''North America''');
   • Cselect system$clustering_depth('Sales', 'sales_representative') where region = 'North America';
   • Dselect system$clustering_information('Sales', 'sales_representative') where region = 'North America’;

   Show answer

   Answer: B

   Next question

6. Question 6 · 126 in bank

   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 took over an hour to complete. What will improve the query performance the MOST?

   • AIncrease the size of the virtual warehouse.
   • BIncrease the number of clusters in the virtual warehouse.
   • CImplement the search optimization service on the table.
   • DAdd a date column as a cluster key on the table.

   Show answer

   Answer: D

   Next question

7. Question 7 · 126 in bank

   Select 3

   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? (Choose three.)

   • AInternal stage on GCP us-central1 (Iowa)
   • BInternal stage on AWS eu-central-1 (Frankfurt)
   • CExternal stage on GCP us-central1 (Iowa)
   • DExternal stage in an Amazon S3 bucket on AWS eu-west-1 (Ireland)
   • EExternal stage in an Amazon S3 bucket on AWS eu-central-1 (Frankfurt)
   • FSSD attached to an Amazon EC2 instance on AWS eu-west-1 (Ireland)

   Show answer

   Answer: CDE

   Next question

8. Question 8 · 126 in bank

   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?

   • ACREATE DATABASE DEV - CLONE PROD - FAIL_SAFE = FALSE;
   • BCREATE DATABASE DEV - CLONE PROD;
   • CCREATE TRANSIENT DATABASE DEV - CLONE PROD;
   • DCREATE DATABASE DEV - CLONE PROD - DATA_RETENTION_TIME_IN DAYS = 0;

   Show answer

   Answer: C

   Next question

9. Question 9 · 126 in bank

   Which query will show a list of the 20 most recent executions of a specified task, MYTASK, that have been scheduled within the last hour that have ended or are still running?

   • A//IMG//
   • B//IMG//
   • C//IMG//
   • D//IMG//

   Show answer

   Answer: B

   Next question

10. Question 10 · 126 in bank

    Select 3

    Which methods can be used to create a DataFrame object in Snowpark? (Choose three.)

    • Asession.jdbc_connection()
    • Bsession.read.json()
    • Csession.table()
    • DDataFrame.write()
    • Esession.builder()
    • Fsession.sql()

    Show answer

    Answer: BCF

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DEA-C02 practice, question 1. 126 questions available.