SnowPro Advanced: Data Engineer practice questions

1. Question 1 of 25

   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

   Correct answer: B

   How others answered

   • B100%

2. Question 2 of 25

   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

   Correct answer: C

   How others answered

   • C92%
   • B8%

3. Question 3 of 25

   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

   Correct answer: D, E, F

   How others answered

   • DEF73%
   • BDE27%

   Only 73% agreed on this answer — treat it as contested rather than settled.

4. Question 4 of 25

   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

   Correct answer: B, D, E

   How others answered

   • BDE100%

5. Question 5 of 25

   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

   Correct answer: B

   How others answered

   • B100%

6. Question 6 of 25

   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

   Correct answer: D

   How others answered

   • D100%

7. Question 7 of 25

   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

   Correct answer: C, D, E

   How others answered

   • CDE100%

8. Question 8 of 25

   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

   Correct answer: C

   How others answered

   • C90%
   • D10%

9. Question 9 of 25

   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

   Correct answer: B

   How others answered

   • B100%

10. Question 10 of 25

    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

    Correct answer: B, C, F

    How others answered

    • BCF100%

11. Question 11 of 25

    Select 3

    A new CUSTOMER table is created by a data pipeline in a Snowflake schema where MANAGED ACCESS is enabled. Which roles can grant access to the CUSTOMER table? (Choose three.)

    • AThe role that owns the schema
    • BThe role that owns the database
    • CThe role that owns the CUSTOMER table
    • DThe SYSADMIN role
    • EThe SECURITYADMIN role
    • FThe USERADMIN role with the MANAGE GRANTS privilege

    Show answer

    Correct answer: A, E, F

    How others answered

    • AEF100%

12. Question 12 of 25

    What is the purpose of the BUILD_STAGE_FILE_URL function in Snowflake?

    • AIt generates an encrypted URL for accessing a file in a stage.
    • BIt generates a staged URL for accessing a file in a stage.
    • CIt generates a permanent URL for accessing files in a stage.
    • DIt generates a temporary URL for accessing a file in a stage.

    Show answer

    Correct answer: C

    How others answered

    • C100%

13. Question 13 of 25

    The JSON below is stored in a VARIANT column named V in a table named jCustRaw:

    

    Which query will return one row per team member (stored in the teamMembers array) along with all of the attributes of each team member?

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

    Show answer

    Correct answer: B

    How others answered

    • B100%

14. Question 14 of 25

    Select 2

    A company has an extensive script in Scala that transforms data by leveraging DataFrames. A Data Engineer needs to move these transformations to Snowpark. What characteristics of data transformations in Snowpark should be considered to meet this requirement? (Choose two.)

    • AIt is possible to join multiple tables using DataFrames.
    • BSnowpark operations are executed lazily on the server.
    • CUser-Defined Functions (UDFs) are not pushed down to Snowflake.
    • DSnowpark requires a separate cluster outside of Snowflake for computations.
    • EColumns in different DataFrames with the same name should be referred to with squared brackets.

    Show answer

    Correct answer: A, B

    How others answered

    • AB100%

15. Question 15 of 25

    The following is returned from SYSTEM$CLUSTERING_INFORMATION() for a table named ORDERS with a DATE column named O_ORDERDATE:

    

    What does the total_constant_partition_count value indicate about this table?

    • AThe table is clustered very well on O_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering.
    • BThe table is not clustered well on O_ORDERDATE, as there are 493 micro-partitions where the range of values in that column overlap with every other micro-partition in the table.
    • CThe data in O_ORDERDATE does not change very often, as there are 493 micro-partitions containing rows where that column has not been modified since the row was created.
    • DThe data in O_ORDERDATE has a very low cardinality, as there are 493 micro-partitions where there is only a single distinct value in that column for all rows in the micro-partition.

    Show answer

    Correct answer: A

    How others answered

    • A100%

16. Question 16 of 25

    A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated. The query conditions vary by topic according to what data each Analyst needs. Responsiveness of the dashboard queries is a top priority, and the data cache should be preserved. How should the Data Engineer configure the compute resources to support this dashboard?

    • AAssign queries to a multi-cluster virtual warehouse with economy auto-scaling. Allow the system to automatically start and stop clusters according to demand.
    • BAssign all queries to a multi-cluster virtual warehouse set to maximized mode. Monitor to determine the smallest suitable number of clusters.
    • CCreate a virtual warehouse for every 250 Analysts. Monitor to determine how many of these virtual warehouses are being utilized at capacity.
    • DCreate a size XL virtual warehouse to support all the dashboard queries. Monitor query runtimes to determine whether the virtual warehouse should be resized.

    Show answer

    Correct answer: B

    How others answered

    • B100%

17. Question 17 of 25

    A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours. How long will Snowflake use the persisted query results from the result cache, provided that the underlying data has not changed?

    • A12 hours
    • B24 hours
    • C14 days
    • D31 days

    Show answer

    Correct answer: D

    How others answered

    • D100%

18. Question 18 of 25

    A Data Engineer ran a stored procedure containing various transactions. During the execution, the session abruptly disconnected, preventing one transaction from committing or rolling back. The transaction was left in a detached state and created a lock on resources. What step must the Engineer take to immediately run a new transaction?

    • ACall the system function SYSTEM$ABORT_TRANSACTION.
    • BCall the system function SYSTEM$CANCEL_TRANSACTION.
    • CSet the LOCK_TIMEOUT to FALSE in the stored procedure.
    • DSet the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

    Show answer

    Correct answer: A

    How others answered

    • A100%

19. Question 19 of 25

    A database contains a table and a stored procedure defined as:

    

    The log_table is initially empty and a Data Engineer issues the following command: CALL insert_log(NULL::VARCHAR); No other operations are affecting the log_table. What will be the outcome of the procedure call?

    • AThe log_table contains zero records and the stored procedure returned 1 as a return value.
    • BThe log_table contains one record and the stored procedure returned 1 as a return value.
    • CThe log_table contains one record and the stored procedure returned NULL as a return value.
    • DThe log_table contains zero records and the stored procedure returned NULL as a return value.

    Show answer

    Correct answer: D

    How others answered

    • D100%

20. Question 20 of 25

    When would a Data Engineer use TABLE with the FLATTEN function instead of the LATERAL FLATTEN combination?

    • AWhen TABLE with FLATTEN requires another source in the FROM clause to refer to.
    • BWhen TABLE with FLATTEN requires no additional source in the FROM clause to refer to.
    • CWhen the LATERAL FLATTEN combination requires no other source in the FROM clause to refer to.
    • DWhen TABLE with FLATTEN is acting like a sub-query executed for each returned row.

    Show answer

    Correct answer: B

    How others answered

    • B67%
    • D33%

    Only 67% agreed on this answer — treat it as contested rather than settled.

21. Question 21 of 25

    Which output is provided by both the SYSTEM$CLUSTERING_DEPTH function and the SYSTEM$CLUSTERING_INFORMATION function?

    • Aaverage_depth
    • Bnotes
    • Caverage_overlaps
    • Dtotal_partition_count

    Show answer

    Correct answer: A

    How others answered

    • A100%

22. Question 22 of 25

    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. What is the recommended way to ingest this data?

    • AUse Snowpipe to ingest the files that land in an external stage into a Snowflake table.
    • BUse a COPY INTO command to ingest the PDF files in an external stage into a Snowflake table with a VARIANT column.
    • CCreate an external table on the PDF files that are stored in a stage and parse the data into structured data.
    • DCreate a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data.

    Show answer

    Correct answer: D

    How others answered

    • D100%

23. Question 23 of 25

    A table is loaded using Snowpipe and truncated afterwards. Later, a Data Engineer finds that the table needs to be reloaded, but the metadata of the pipe will not allow the same files to be loaded again. How can this issue be solved using the LEAST amount of operational overhead?

    • AWait until the metadata expires and then reload the file using Snowpipe.
    • BModify the file by adding a blank row to the bottom and re-stage the file.
    • CSet the FORCE=TRUE option in the Snowpipe COPY INTO command.
    • DRecreate the pipe by using the CREATE OR REPLACE PIPE command.

    Show answer

    Correct answer: D

    How others answered

    • D75%
    • C25%

24. Question 24 of 25

    A stream called TRANSACTIONS_STM is created on top of a TRANSACTIONS table in a continuous pipeline running in Snowflake. After a couple of months, the TRANSACTIONS table is renamed TRANSACTIONS_RAW to comply with new naming standards. What will happen to the TRANSACTIONS_STM object?

    • ATRANSACTIONS_STM will keep working as expected.
    • BTRANSACTIONS_STM will be stale and will need to be re-created.
    • CTRANSACTIONS_STM will be automatically renamed TRANSACTIONS_RAW_STM.
    • DReading from the TRANSACTIONS_STM stream will succeed for some time after the expected STALE_TIME.

    Show answer

    Correct answer: A

    How others answered

    • A100%

25. Question 25 of 25

    Select 2

    A Data Engineer is evaluating the performance of a query in a development environment.

    

    

    Based on the Query Profile, what are some performance tuning options the Engineer can use? (Choose two.)

    • AAdd a LIMIT to the ORDER BY if possible
    • BUse a multi-cluster virtual warehouse with the scaling policy set to standard
    • CMove the query to a larger virtual warehouse
    • DCreate indexes to ensure sorted access to data
    • EIncrease the MAX_CLUSTER_COUNT

    Show answer

    Correct answer: A, C

    How others answered

    • AC100%

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

DEA-C02 practice, question 1 of 25