DE-ASSOC practice questions 71–80

1. Question 71 · 170 in bank

   A data analyst has a series of queries in a SQL program. The data analyst wants this program to run every day. They only want the final query in the program to run on Sundays. They ask for help from the data engineering team to complete this task. Which of the following approaches could be used by the data engineering team to complete this task?

   • AThey could submit a feature request with Databricks to add this functionality.
   • BThey could wrap the queries using PySpark and use Python’s control flow system to determine when to run the final query.
   • CThey could only run the entire program on Sundays.
   • DThey could automatically restrict access to the source table in the final query so that it is only accessible on Sundays.
   • EThey could redesign the data model to separate the data used in the final query into a new table.

   Show answer

   Answer: B

   Explanation

   B The answer is B. Option A: Submitting a feature request with Databricks to add this functionality is not a feasible solution because it would require Databricks to implement new functionality. Option C: Only running the entire program on Sundays would be inconvenient for the data analyst because they would have to remember to run the program on Sundays. Option D: Automatically restricting access to the source table in the final query so that it is only accessible on Sundays would be difficult to implement and would not be a reliable solution. Option E: Redesigning the data model to separate the data used in the final query into a new table would be a major undertaking and would not be a feasible solution for this specific problem. Therefore, the only feasible solution to the problem is to wrap the queries using PySpark and use Python's control flow system to determine when to run the final query. python code

   Source: Adapted from community discussion — Atnafu

   Next question

2. Question 72 · 170 in bank

   A data engineer runs a statement every day to copy the previous day’s sales into the table transactions. Each day’s sales are in their own file in the location "/transactions/raw". Today, the data engineer runs the following command to complete this task:

   

   After running the command today, the data engineer notices that the number of records in table transactions has not changed. Which of the following describes why the statement might not have copied any new records into the table?

   • AThe format of the files to be copied were not included with the FORMAT_OPTIONS keyword.
   • BThe names of the files to be copied were not included with the FILES keyword.
   • CThe previous day’s file has already been copied into the table.
   • DThe PARQUET file format does not support COPY INTO.
   • EThe COPY INTO statement requires the table to be refreshed to view the copied rows.

   Show answer

   Answer: C

   Explanation

   C. The previous day’s file has already been copied into the table. The COPY INTO statement is generally used to copy data from files or a location into a table. If the data engineer runs this statement daily to copy the previous day’s sales into the "transactions" table and the number of records hasn't changed after today's execution, it's possible that the data from today's file might not have differed from the data already present in the table. If the files in the "/transactions/raw" location are expected to contain distinct data for each day and the number of records in the table remains the same, it implies that the data engineer might have already copied today's data previously, or today's data was identical to the data already present in the table. Options A, B, D, and E don't accurately explain why the statement might not have copied new records into the table based on the provided scenario.

   Source: Adapted from community discussion — Garyn

   References

   • Databricks documentation
   • Databricks documentation

   Next question

3. Question 73 · 170 in bank

   Which of the following describes a scenario in which a data team will want to utilize cluster pools?

   • AAn automated report needs to be refreshed as quickly as possible.
   • BAn automated report needs to be made reproducible.
   • CAn automated report needs to be tested to identify errors.
   • DAn automated report needs to be version-controlled across multiple collaborators.
   • EAn automated report needs to be runnable by all stakeholders.

   Show answer

   Answer: A

   Explanation

   Using cluster pools reduces the cluster startup time. So in this case, the reports can be refreshed quickly and not having to wait long for the cluster to start

   Source: Adapted from community discussion — Data_4ever

   References

   • Databricks documentation

   Next question

4. Question 74 · 170 in bank

   A data engineer needs to create a table in Databricks using data from their organization’s existing SQLite database. They run the following command:

   

   Which of the following lines of code fills in the above blank to successfully complete the task?

   • Aorg.apache.spark.sql.jdbc
   • Bautoloader
   • CDELTA
   • Dsqlite
   • Eorg.apache.spark.sql.sqlite

   Show answer

   Answer: A

   Explanation

   Nobody mentioned this, but the big hint in this question is the url, which has a "jdbc:" url prefix. Hence, a JDBC type driver is required here.

   Source: Adapted from community discussion — 7082935

   References

   • Databricks documentation

   Next question

5. Question 75 · 170 in bank

   A new data engineering team has been assigned to work on a project. The team will need access to database customers in order to see what tables already exist. The team has its own group team. Which command can be used to grant the necessary permission on the entire database to the new team?

   • AGRANT VIEW ON CATALOG customers TO team;
   • BGRANT CREATE ON DATABASE customers TO team;
   • CGRANT USAGE ON CATALOG team TO customers;
   • DGRANT USAGE ON DATABASE customers TO team;

   Show answer

   Answer: D

   Next question

6. Question 76 · 170 in bank

   A data engineer is attempting to drop a Spark SQL table my_table. The data engineer wants to delete all table metadata and data. They run the following command: DROP TABLE IF EXISTS my_table - While the object no longer appears when they run SHOW TABLES, the data files still exist. Which of the following describes why the data files still exist and the metadata files were deleted?

   • AThe table’s data was larger than 10 GB
   • BThe table’s data was smaller than 10 GB
   • CThe table was external
   • DThe table did not have a location
   • EThe table was managed

   Show answer

   Answer: C

   Explanation

   C. The table was external The reason why the data files still exist while the metadata files were deleted is because the table was external. When a table is external in Spark SQL (or in other database systems), it means that the table metadata (such as schema information and table structure) is managed externally, and Spark SQL assumes that the data is managed and maintained outside of the system. Therefore, when you execute a DROP TABLE statement for an external table, it removes only the table metadata from the catalog, leaving the data files intact. On the other hand, for managed tables (option E), Spark SQL manages both the metadata and the data files. When you drop a managed table, it deletes both the metadata and the associated data files, resulting in a complete removal of the table.

   Source: Adapted from community discussion — vctrhugo

   Next question

7. Question 77 · 170 in bank

   A Delta Live Table pipeline includes two datasets defined using STREAMING LIVE TABLE. Three datasets are defined against Delta Lake table sources using LIVE TABLE. The table is configured to run in Production mode using the Continuous Pipeline Mode. Assuming previously unprocessed data exists and all definitions are valid, what is the expected outcome after clicking Start to update the pipeline?

   • AAll datasets will be updated at set intervals until the pipeline is shut down. The compute resources will persist to allow for additional testing.
   • BAll datasets will be updated once and the pipeline will persist without any processing. The compute resources will persist but go unused.
   • CAll datasets will be updated at set intervals until the pipeline is shut down. The compute resources will be deployed for the update and terminated when the pipeline is stopped.
   • DAll datasets will be updated once and the pipeline will shut down. The compute resources will be terminated.
   • EAll datasets will be updated once and the pipeline will shut down. The compute resources will persist to allow for additional testing.

   Show answer

   Answer: C

   Explanation

   C. All datasets will be updated at set intervals until the pipeline is shut down. The compute resources will be deployed for the update and terminated when the pipeline is stopped. Explanation: Continuous Pipeline Mode in Production mode implies that the pipeline continuously processes incoming data updates at set intervals, ensuring the datasets are kept up-to-date as new data arrives. Since the pipeline is set to Continuous Pipeline Mode, it will keep running and updating the datasets until it is manually shut down. The compute resources are allocated dynamically to process and update the datasets as needed, and they will be terminated when the pipeline is stopped or shut down. This mode allows for real-time or near-real-time updates to the datasets from the streaming/live tables, ensuring that the data remains current and reflects the changes occurring in the data sources.

   Source: Adapted from community discussion — Garyn

   References

   • Databricks documentation

   Next question

8. Question 78 · 170 in bank

   In order for Structured Streaming to reliably track the exact progress of the processing so that it can handle any kind of failure by restarting and/or reprocessing, which of the following two approaches is used by Spark to record the offset range of the data being processed in each trigger?

   • ACheckpointing and Write-ahead Logs
   • BStructured Streaming cannot record the offset range of the data being processed in each trigger.
   • CReplayable Sources and Idempotent Sinks
   • DWrite-ahead Logs and Idempotent Sinks
   • ECheckpointing and Idempotent Sinks

   Show answer

   Answer: A

   Explanation

   Why the correct answer is E. Checkpointing and Idempotent Sinks: Checkpointing: Spark Structured Streaming uses checkpointing to track the state of the data being processed. Checkpoints allow the system to restart processing from where it left off in case of failure, ensuring reliability. Idempotent Sinks: Idempotent sinks ensure that reprocessing the same data multiple times (in case of a failure or restart) doesn’t lead to duplicate results. The sink can handle repeated writes of the same data without issues. Why A. Checkpointing and Write-ahead Logs is incorrect: Spark Structured Streaming does not use Write-ahead Logs (WAL) for tracking offsets or ensuring fault tolerance. While WALs are used in some systems for durability, Spark Structured Streaming relies on checkpointing and the concept of idempotent operations to ensure consistency and fault tolerance.

   Source: Adapted from community discussion — Colje

   References

   • Microsoft Learn

   Next question

9. Question 79 · 170 in bank

   Which of the following describes the relationship between Gold tables and Silver tables?

   • AGold tables are more likely to contain aggregations than Silver tables.
   • BGold tables are more likely to contain valuable data than Silver tables.
   • CGold tables are more likely to contain a less refined view of data than Silver tables.
   • DGold tables are more likely to contain more data than Silver tables.
   • EGold tables are more likely to contain truthful data than Silver tables.

   Show answer

   Answer: A

   Explanation

   A. Gold tables are more likely to contain aggregations than Silver tables. In some data processing pipelines, especially those following a typical "Bronze-Silver-Gold" data lakehouse architecture, Silver tables are often considered a more refined version of the raw or Bronze data. Silver tables may include data cleansing, schema enforcement, and some initial transformations. Gold tables, on the other hand, typically represent a stage where data is further enriched, aggregated, and processed to provide valuable insights for analytical purposes. This could indeed involve more aggregations compared to Silver tables.

   Source: Adapted from community discussion — vctrhugo

   Next question

10. Question 80 · 170 in bank

    Which of the following is hosted completely in the control plane of the classic Databricks architecture?

    • AWorker node
    • BJDBC data source
    • CDatabricks web application
    • DDatabricks Filesystem
    • EDriver node

    Show answer

    Answer: C

    Explanation

    C. Databricks web application In the classic Databricks architecture, the control plane includes components like the Databricks web application, the Databricks REST API, and the Databricks Workspace. These components are responsible for managing and controlling the Databricks environment, including cluster provisioning, notebook management, access control, and job scheduling. The other options, such as worker nodes, JDBC data sources, Databricks Filesystem (DBFS), and driver nodes, are typically part of the data plane or the execution environment, which is separate from the control plane. Worker nodes are responsible for executing tasks and computations, JDBC data sources are used to connect to external databases, DBFS is a distributed file system for data storage, and driver nodes are responsible for coordinating the execution of Spark jobs.

    Source: Adapted from community discussion — vctrhugo

    References

    • Databricks documentation
    • Microsoft Learn

    Next question

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

DE-ASSOC practice, question 71. 170 questions available.