The data engineer team is configuring environment for development testing, and production before beginning migration on a new data pipeline. The team requires extensive testing on both the code and data resulting from code execution, and the team want to develop and test against similar production data as possible. A junior data engineer suggests that production data can be mounted to the development testing environments, allowing pre production code to execute against production dat a. Because all users have Admin privileges in the development environment, the junior data engineer has offered to configure permissions and mount this data for the team. Which statement captures best practices for this situation?
Correct Answer: C
The best practice in such scenarios is to ensure that production data is handled securely and with proper access controls. By granting only read access to production data in development and testing environments, it mitigates the risk of unintended data modification. Additionally, maintaining isolated databases for different environments helps to avoid accidental impacts on production data and systems. Reference: Databricks best practices for securing data: https://docs.databricks.com/security/index.html
To reduce storage and compute costs, the data engineering team has been tasked with curating a series of aggregate tables leveraged by business intelligence dashboards, customer-facing applications, production machine learning models, and ad hoc analytical queries. The data engineering team has been made aware of new requirements from a customer-facing application, which is the only downstream workload they manage entirely. As a result, an aggregate table used by numerous teams across the organization will need to have a number of fields renamed, and additional fields will also be added. Which of the solutions addresses the situation while minimally interrupting other teams in the organization without increasing the number of tables that need to be managed?
Correct Answer: B
This is the correct answer because it addresses the situation while minimally interrupting other teams in the organization without increasing the number of tables that need to be managed. The situation is that an aggregate table used by numerous teams across the organization will need to have a number of fields renamed, and additional fields will also be added, due to new requirements from a customer-facing application. By configuring a new table with all the requisite fields and new names and using this as the source for the customer-facing application, the data engineering team can meet the new requirements without affecting other teams that rely on the existing table schema and name. By creating a view that maintains the original data schema and table name by aliasing select fields from the new table, the data engineering team can also avoid duplicating data or creating additional tables that need to be managed. Verified Reference: [Databricks Certified Data Engineer Professional], under "Lakehouse" section; Databricks Documentation, under "CREATE VIEW" section.
The data engineer is using Spark's MEMORY_ONLY storage level. Which indicators should the data engineer look for in the spark UI's Storage tab to signal that a cached table is not performing optimally?
Correct Answer: C
In the Spark UI's Storage tab, an indicator that a cached table is not performing optimally would be the presence of the _disk annotation in the RDD Block Name. This annotation indicates that some partitions of the cached data have been spilled to disk because there wasn't enough memory to hold them. This is suboptimal because accessing data from disk is much slower than from memory. The goal of caching is to keep data in memory for fast access, and a spill to disk means that this goal is not fully achieved.
A Delta Lake table representing metadata about content posts from users has the following schema: user_id LONG post_text STRING post_id STRING longitude FLOAT latitude FLOAT post_time TIMESTAMP date DATE Based on the above schema, which column is a good candidate for partitioning the Delta Table?
Correct Answer: A
Partitioning a Delta Lake table is a strategy used to improve query performance by dividing the table into distinct segments based on the values of a specific column. This approach allows queries to scan only the relevant partitions, thereby reducing the amount of data read and enhancing performance. Considerations for Choosing a Partition Column: Cardinality: Columns with high cardinality (i.e., a large number of unique values) are generally poor choices for partitioning. High cardinality can lead to a large number of small partitions, which can degrade performance. Query Patterns: The partition column should align with common query filters. If queries frequently filter data based on a particular column, partitioning by that column can be beneficial. Partition Size: Each partition should ideally contain at least 1 GB of data. This ensures that partitions are neither too small (leading to too many partitions) nor too large (negating the benefits of partitioning). Evaluation of Columns: date: Cardinality: Typically low, especially if data spans over days, months, or years. Query Patterns: Many analytical queries filter data based on date ranges. Partition Size: Likely to meet the 1 GB threshold per partition, depending on data volume. user_id: Cardinality: High, as each user has a unique ID. Query Patterns: While some queries might filter by user_id, the high cardinality makes it unsuitable for partitioning. Partition Size: Partitions could be too small, leading to inefficiencies. post_id: Cardinality: Extremely high, with each post having a unique ID. Query Patterns: Unlikely to be used for filtering large datasets. Partition Size: Each partition would be very small, resulting in a large number of partitions. post_time: Cardinality: High, especially if it includes exact timestamps. Query Patterns: Queries might filter by time, but the high cardinality poses challenges. Partition Size: Similar to user_id, partitions could be too small. Conclusion: Given the considerations, the date column is the most suitable candidate for partitioning. It has low cardinality, aligns with common query patterns, and is likely to result in appropriately sized partitions. Reference: Delta Lake Best Practices Partitioning in Delta Lake
A facilities-monitoring team is building a near-real-time PowerBI dashboard off the Delta table device_readings: Columns: device_id (STRING, unique sensor ID) event_ts (TIMESTAMP, ingestion timestamp UTC) temperature_c (DOUBLE, temperature in °C) Requirement: For each sensor, generate one row per non-overlapping 5-minute interval, offset by 2 minutes (e.g., 00:02-00:07, 00:07-00:12, ...). Each row must include interval start, interval end, and average temperature in that slice. Downstream BI tools (e.g., Power BI) must use the interval timestamps to plot time-series bars. Options:
Correct Answer: A
The correct way to satisfy non-overlapping windows with an offset in Databricks SQL is to use the window function with three parameters: window duration, slide duration, and start offset. In option A, the function call: window(event_ts, '5 minutes', '2 minutes', '5 minutes') creates 5-minute windows that slide every 5 minutes, with a 2-minute offset, which exactly matches the requirement (intervals like 00:02-00:07, 00:07-00:12, ...). Option B is incorrect because it uses a windowed aggregation with RANGE, which produces overlapping sliding averages, not discrete non-overlapping buckets. Option C manually constructs bucket boundaries with date_trunc and offsets, but this is brittle and less efficient than the built-in window function. Option D incorrectly passes four parameters to window but with the wrong ordering (5 minutes, 5 minutes, 2 minutes). This creates a sliding window every 5 minutes with overlap, rather than true non-overlapping shifted windows. Reference (Databricks SQL Windowing Functions): Databricks documentation specifies that: window(time_col, windowDuration, slideDuration, startTime) produces tumbling or sliding windows. When slideDuration = windowDuration, it produces non-overlapping tumbling windows. The startTime argument allows for offset windows, which is why '2 minutes' ensures alignment at 00:02, 00:07, etc. Thus, A is the only correct solution as it directly implements non-overlapping, offset-based tumbling windows.
