DE-PRO practice questions 141–150

1. Question 141 · 202 in bank

   A data team's Structured Streaming job is configured to calculate running aggregates for item sales to update a downstream marketing dashboard. The marketing team has introduced a new promotion, and they would like to add a new field to track the number of times this promotion code is used for each item. A junior data engineer suggests updating the existing query as follows. Note that proposed changes are in bold. Original query:

   

   Proposed query:

   

   Proposed query: .start(“/item_agg”) Which step must also be completed to put the proposed query into production?

   • ASpecify a new checkpointLocation
   • BIncrease the shuffle partitions to account for additional aggregates
   • CRun REFRESH TABLE delta.'/item_agg'
   • DRegister the data in the "/item_agg" directory to the Hive metastore
   • ERemove .option(‘mergeSchema’, ‘true’) from the streaming write

   Show answer

   Answer: A

   Explanation

   Below is the proposed query: df.groupBy("item") .agg(count("item").alias("total_count"), mean("sale_price").alias("avg_price"), count("promo_code = 'NEW MEMBER'") .alias("new member_promo")) writeStream .outputMode("complete") .option('mergeSchema', 'true') .option("checkpointLocation", "/item_agg/ checkpoint") .start("/item_agg") Answer A. When updating the schema of a streaming job by adding new fields (like the new_member_promo field), it’s important to use a new checkpoint location. This is because the existing checkpoint location is tied to the old schema, and adding a new field could lead to schema mismatch issues.

   Source: Adapted from community discussion — AlejandroU

   Next question

2. Question 142 · 202 in bank

   Which statement describes the correct use of pyspark.sql.functions.broadcast?

   • AIt marks a column as having low enough cardinality to properly map distinct values to available partitions, allowing a broadcast join.
   • BIt marks a column as small enough to store in memory on all executors, allowing a broadcast join.
   • CIt caches a copy of the indicated table on attached storage volumes for all active clusters within a Databricks workspace.
   • DIt marks a DataFrame as small enough to store in memory on all executors, allowing a broadcast join.
   • EIt caches a copy of the indicated table on all nodes in the cluster for use in all future queries during the cluster lifetime.

   Show answer

   Answer: D

   Explanation

   The broadcast function in PySpark is used in the context of joins. When you mark a DataFrame with broadcast, Spark tries to send this DataFrame to all worker nodes so that it can be joined with another DataFrame without shuffling the larger DataFrame across the nodes. This is particularly beneficial when the DataFrame is small enough to fit into the memory of each node. It helps to optimize the join process by reducing the amount of data that needs to be shuffled across the cluster, which can be a very expensive operation in terms of computation and time.

   Source: Adapted from community discussion — aragorn_brego

   Next question

3. Question 143 · 202 in bank

   The data engineering team maintains the following code:

   

   Assuming that this code produces logically correct results and the data in the source table has been de-duplicated and validated, which statement describes what will occur when this code is executed?

   • AThe silver_customer_sales table will be overwritten by aggregated values calculated from all records in the gold_customer_lifetime_sales_summary table as a batch job.
   • BA batch job will update the gold_customer_lifetime_sales_summary table, replacing only those rows that have different values than the current version of the table, using customer_id as the primary key.
   • CThe gold_customer_lifetime_sales_summary table will be overwritten by aggregated values calculated from all records in the silver_customer_sales table as a batch job.
   • DAn incremental job will leverage running information in the state store to update aggregate values in the gold_customer_lifetime_sales_summary table.
   • EAn incremental job will detect if new rows have been written to the silver_customer_sales table; if new rows are detected, all aggregates will be recalculated and used to overwrite the gold_customer_lifetime_sales_summary table.

   Show answer

   Answer: C

   Explanation

   The code is performing a batch aggregation operation on the "silver_customer_sales" table grouped by "customer_id". It calculates the first and last transaction dates, the average sales, the total number of distinct orders, and the lifetime value of sales for each customer. The .mode("overwrite") operation specifies that the output table "gold_customer_lifetime_sales_summary" should be overwritten with the result of this aggregation. This means that every time this code runs, it will replace the existing "gold_customer_lifetime_sales_summary" table with a new version that reflects the current state of the "silver_customer_sales" table.

   Source: Adapted from community discussion — aragorn_brego

   Next question

4. Question 144 · 202 in bank

   The data architect has mandated that all tables in the Lakehouse should be configured as external (also known as "unmanaged") Delta Lake tables. Which approach will ensure that this requirement is met?

   • AWhen a database is being created, make sure that the LOCATION keyword is used.
   • BWhen configuring an external data warehouse for all table storage, leverage Databricks for all ELT.
   • CWhen data is saved to a table, make sure that a full file path is specified alongside the Delta format.
   • DWhen tables are created, make sure that the EXTERNAL keyword is used in the CREATE TABLE statement.
   • EWhen the workspace is being configured, make sure that external cloud object storage has been mounted.

   Show answer

   Answer: C

   Explanation

   In Delta Lake, an external (or unmanaged) table is a table created outside of the data lake but is still accessible from the data lake. The data for external tables is stored in a location specified by the user, not in the default directory of the data lake. When you save data to an external table, you need to specify the full file path where the data will be stored. This makes the table “external” because the data itself is not managed by Delta Lake, only the metadata is. This is why specifying a full file path alongside the Delta format when saving data to a table will ensure that the table is configured as an external Delta Lake table.

   Source: Adapted from community discussion — vctrhugo

   Next question

5. Question 145 · 202 in bank

   An upstream source writes Parquet data as hourly batches to directories named with the current date. A nightly batch job runs the following code to ingest all data from the previous day as indicated by the date variable:

   

   Assume that the fields customer_id and order_id serve as a composite key to uniquely identify each order. If the upstream system is known to occasionally produce duplicate entries for a single order hours apart, which statement is correct?

   • AEach write to the orders table will only contain unique records, and only those records without duplicates in the target table will be written.
   • BEach write to the orders table will only contain unique records, but newly written records may have duplicates already present in the target table.
   • CEach write to the orders table will only contain unique records; if existing records with the same key are present in the target table, these records will be overwritten.
   • DEach write to the orders table will only contain unique records; if existing records with the same key are present in the target table, the operation will fail.
   • EEach write to the orders table will run deduplication over the union of new and existing records, ensuring no duplicate records are present.

   Show answer

   Answer: B

   Explanation

   B. Each write to the orders table will only contain unique records, but newly written records may have duplicates already present in the target table. Explanation: In the provided code, the .dropDuplicates(["customer_id","order_id"]) operation is performed on the data loaded from the Parquet files. This operation ensures that only unique records, based on the composite key of "customer_id" and "order_id," are retained in the DataFrame before writing to the "orders" table. However, this operation does not consider duplicates that may already exist in the "orders" table. It only filters duplicates from the current batch of data. If there are duplicates in the "orders" table from previous batches, they will remain in the table. So, newly written records will not have duplicates within the batch being written, but duplicates from previous batches may still exist in the target table.

   Source: Adapted from community discussion — Eertyy

   Next question

6. Question 146 · 202 in bank

   A CHECK constraint has been successfully added to the Delta table named activity_details using the following logic:

   

   A batch job is attempting to insert new records to the table, including a record where latitude = 45.50 and longitude = 212.67. Which statement describes the outcome of this batch insert?

   • AThe write will fail when the violating record is reached; any records previously processed will be recorded to the target table.
   • BThe write will fail completely because of the constraint violation and no records will be inserted into the target table.
   • CThe write will insert all records except those that violate the table constraints; the violating records will be recorded to a quarantine table.
   • DThe write will include all records in the target table; any violations will be indicated in the boolean column named valid_coordinates.
   • EThe write will insert all records except those that violate the table constraints; the violating records will be reported in a warning log.

   Show answer

   Answer: B

   Explanation

   In systems that support atomic transactions, such as Delta Lake, when a batch operation encounters a record that violates a CHECK constraint, the entire operation fails, and no records are inserted, including those that do not violate the constraint. This is to ensure the atomicity of the transaction, meaning that either all the changes are committed, or none are, maintaining data integrity. The record with a longitude of 212.67 violates the constraint because longitude values must be between -180 and 180 degrees.

   Source: Adapted from community discussion — aragorn_brego

   Next question

7. Question 147 · 202 in bank

   All records from an Apache Kafka producer are being ingested into a single Delta Lake table with the following schema: key BINARY, value BINARY, topic STRING, partition LONG, offset LONG, timestamp LONG There are 5 unique topics being ingested. Only the "registration" topic contains Personal Identifiable Information (PII). The company wishes to restrict access to PII. The company also wishes to only retain records containing PII in this table for 14 days after initial ingestion. However, for non-PII information, it would like to retain these records indefinitely. Which of the following solutions meets the requirements?

   • AAll data should be deleted biweekly; Delta Lake's time travel functionality should be leveraged to maintain a history of non-PII information.
   • BData should be partitioned by the registration field, allowing ACLs and delete statements to be set for the PII directory.
   • CBecause the value field is stored as binary data, this information is not considered PII and no special precautions should be taken.
   • DSeparate object storage containers should be specified based on the partition field, allowing isolation at the storage level.
   • EData should be partitioned by the topic field, allowing ACLs and delete statements to leverage partition boundaries.

   Show answer

   Answer: E

   Explanation

   I think answer E is correct, as by default partitionning by a column will create a separate folder for each subset data linked to the partition

   Source: Adapted from community discussion — mouad_attaqi

   References

   • Databricks documentation

   Next question

8. Question 148 · 202 in bank

   The data architect has decided that once data has been ingested from external sources into the Databricks Lakehouse, table access controls will be leveraged to manage permissions for all production tables and views. The following logic was executed to grant privileges for interactive queries on a production database to the core engineering group. GRANT USAGE ON DATABASE prod TO eng; GRANT SELECT ON DATABASE prod TO eng; Assuming these are the only privileges that have been granted to the eng group and that these users are not workspace administrators, which statement describes their privileges?

   • AGroup members have full permissions on the prod database and can also assign permissions to other users or groups.
   • BGroup members are able to list all tables in the prod database but are not able to see the results of any queries on those tables.
   • CGroup members are able to query and modify all tables and views in the prod database, but cannot create new tables or views.
   • DGroup members are able to query all tables and views in the prod database, but cannot create or edit anything in the database.
   • EGroup members are able to create, query, and modify all tables and views in the prod database, but cannot define custom functions.

   Show answer

   Answer: D

   Explanation

   The GRANT USAGE ON DATABASE statement gives the eng group the ability to access the prod database. This means they can enter the database context and list the tables. The GRANT SELECT ON DATABASE statement additionally grants them permission to perform SELECT queries on all existing tables and views within the prod database. However, these privileges do not include creating new tables or views, modifying existing tables, or assigning permissions to other users or groups.

   Source: Adapted from community discussion — aragorn_brego

   Next question

9. Question 149 · 202 in bank

   Which of the following technologies can be used to identify key areas of text when parsing Spark Driver log4j output?

   • ARegex
   • BJulia
   • Cpyspsark.ml.feature
   • DScala Datasets
   • EC++

   Show answer

   Answer: A

   Explanation

   Regular expressions (regex) can be used to identify and extract patterns from text data, which makes them very useful for parsing log files like the Spark Driver's log4j output. By defining specific regex patterns, you can search for error messages, timestamps, specific log levels, or any other text that follows a particular format within the log files.

   Source: Adapted from community discussion — aragorn_brego

   Next question

10. Question 150 · 202 in bank

    You are testing a collection of mathematical functions, one of which calculates the area under a curve as described by another function. assert(myIntegrate(lambda x: x*x, 0, 3) [0] == 9) Which kind of test would the above line exemplify?

    • AUnit
    • BManual
    • CFunctional
    • DIntegration
    • EEnd-to-end

    Show answer

    Answer: A

    Next question

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

DE-PRO practice, question 141. 202 questions available.