Scenario practice questions

Which action is recommended to resolve a scenario where a Databricks Job is failing due to excessive metadata operations on a Delta table with millions of files?

Trap 1: Increase the driver node instance size.

While a larger driver node might provide more memory to handle the large file list, this is a 'band-aid' solution. It does not address the underlying issue of excessive metadata operations. The root cause is the sheer number of files, which must be reduced to maintain a healthy table state.

Trap 2: Disable the Delta transaction log.

The Delta transaction log is the core component that provides ACID guarantees and snapshot isolation in Delta Lake. Disabling it is impossible and would result in a loss of the very features that make Delta tables reliable. The metadata overhead is a trade-off for these critical consistency features.

Trap 3: Add more worker nodes to the cluster.

Adding worker nodes increases the compute capacity for processing data, but it does not improve the performance of metadata operations which occur primarily on the driver node. The bottleneck is the interaction between the driver and the storage layer, which is not resolved by adding more parallel processing workers.

A
Increase the driver node instance size.
Why it fails: While a larger driver node might provide more memory to handle the large file list, this is a 'band-aid' solution. It does not address the underlying issue of excessive metadata operations. The root cause is the sheer number of files, which must be reduced to maintain a healthy table state.
B
Run OPTIMIZE to consolidate files.
Running OPTIMIZE reduces the number of files by merging small files into larger ones. This directly reduces the number of entries in the Delta log and the number of metadata calls required to resolve the table state, significantly improving the performance of subsequent queries and avoiding the metadata bottleneck.
C
Disable the Delta transaction log.
Why it fails: The Delta transaction log is the core component that provides ACID guarantees and snapshot isolation in Delta Lake. Disabling it is impossible and would result in a loss of the very features that make Delta tables reliable. The metadata overhead is a trade-off for these critical consistency features.
D
Add more worker nodes to the cluster.
Why it fails: Adding worker nodes increases the compute capacity for processing data, but it does not improve the performance of metadata operations which occur primarily on the driver node. The bottleneck is the interaction between the driver and the storage layer, which is not resolved by adding more parallel processing workers.

Question 2mediummultiple choice

A data engineer needs to ingest data from a legacy SQL Server database into a Delta Lake bronze table. The ingestion must be performant and support parallel reads from the source table. What is the best practice for configuring the JDBC connection in this scenario?

Trap 1: Use a single executor to ensure data consistency during the read.

Using a single executor for a large JDBC read creates a significant bottleneck, as all data must pass through a single network connection. This approach does not scale and fails to take advantage of the distributed nature of the Databricks platform, leading to very long ingestion times for large tables.

Trap 2: Rely on the default JDBC settings for automatic parallelization.

By default, a JDBC connection in Spark uses only one partition and one task to read the entire dataset. There is no automatic parallelization based on the source table's structure. Therefore, the engineer must explicitly provide the partitioning parameters to enable parallel reads and avoid a single-threaded ingestion process.

Trap 3: Use the COPY INTO command to read directly from the JDBC source.

The COPY INTO command is designed specifically for ingesting data from cloud object storage locations like S3 or ADLS. It does not support direct ingestion from JDBC sources. To read from a relational database, the engineer must use the Spark JDBC reader or a specialized connector provided by Databricks.

A
Use a single executor to ensure data consistency during the read.
Why it fails: Using a single executor for a large JDBC read creates a significant bottleneck, as all data must pass through a single network connection. This approach does not scale and fails to take advantage of the distributed nature of the Databricks platform, leading to very long ingestion times for large tables.
B
Configure 'partitionColumn', 'lowerBound', 'upperBound', and 'numPartitions'.
These four parameters allow Spark to split the JDBC query into multiple smaller queries that can be executed in parallel. By defining the range and number of partitions, the data engineer enables multiple workers to fetch data at the same time, which is critical for high-performance data ingestion from external databases.
C
Rely on the default JDBC settings for automatic parallelization.
Why it fails: By default, a JDBC connection in Spark uses only one partition and one task to read the entire dataset. There is no automatic parallelization based on the source table's structure. Therefore, the engineer must explicitly provide the partitioning parameters to enable parallel reads and avoid a single-threaded ingestion process.
D
Use the COPY INTO command to read directly from the JDBC source.
Why it fails: The COPY INTO command is designed specifically for ingesting data from cloud object storage locations like S3 or ADLS. It does not support direct ingestion from JDBC sources. To read from a relational database, the engineer must use the Spark JDBC reader or a specialized connector provided by Databricks.

Question 3mediummultiple choice

A data engineer needs to join two massive datasets. One dataset is very small (10MB), and the other is very large (1TB). To ensure the join operation is performed as efficiently as possible, which join strategy should be enforced?

Trap 1: Enforce a Sort-Merge Join.

Sort-Merge join requires shuffling both large datasets across the network to be sorted and joined on the same worker nodes. This is extremely expensive for massive datasets and is typically used when both sides of the join are too large to fit in memory.

Trap 2: Enforce a Cartesian Product Join.

Cartesian Product joins create a result set where every row of the first table is paired with every row of the second. This leads to an explosion of data, often causing out-of-memory errors and extremely slow execution times for any non-trivial dataset size.

Trap 3: Enforce a Shuffle Hash Join.

Shuffle Hash Join involves shuffling both datasets by the join key. This is unnecessary when one table is small enough to be broadcast. Shuffling massive amounts of data is slow and inefficient compared to broadcasting a small, static lookup table.

A
Enforce a Sort-Merge Join.
Why it fails: Sort-Merge join requires shuffling both large datasets across the network to be sorted and joined on the same worker nodes. This is extremely expensive for massive datasets and is typically used when both sides of the join are too large to fit in memory.
B
Enforce a Broadcast Hash Join.
Broadcast Hash Join sends the small table to all executor nodes, allowing the large table to be joined locally without a shuffle. This minimizes network traffic and compute overhead, making it the most efficient choice for joining a small table with a large one.
C
Enforce a Cartesian Product Join.
Why it fails: Cartesian Product joins create a result set where every row of the first table is paired with every row of the second. This leads to an explosion of data, often causing out-of-memory errors and extremely slow execution times for any non-trivial dataset size.
D
Enforce a Shuffle Hash Join.
Why it fails: Shuffle Hash Join involves shuffling both datasets by the join key. This is unnecessary when one table is small enough to be broadcast. Shuffling massive amounts of data is slow and inefficient compared to broadcasting a small, static lookup table.

Question 4hardmultiple choice

A data engineer is using Databricks Auto Loader to ingest JSON files from an Azure Data Lake Storage Gen2 container into a Delta table. The engineer notices that the ingestion is slow and wants to optimize file discovery. The directory contains millions of files, and new files are added frequently. Which Auto Loader option should be used to improve file discovery performance?

Trap 1: Set cloudFiles.format to 'json'.

Specifying the file format is necessary for parsing but does not affect file discovery performance. It tells Auto Loader how to read the files, not how to find them. The slow ingestion is due to listing overhead, so this option does not solve the problem.

Trap 2: Set cloudFiles.maxFilesPerTrigger to a high value.

Increasing maxFilesPerTrigger controls how many files are processed per micro-batch, but it does not improve file discovery speed. It may actually increase per-batch processing time. The bottleneck is listing files, not processing them. Therefore, this option does not address the slow ingestion caused by file discovery.

Trap 3: Enable cloudFiles.schemaEvolutionMode to 'rescue'.

This option controls how schema evolution is handled, specifically rescuing data that doesn't match the schema. It has no impact on file discovery performance. The issue is slow listing, not schema handling. Thus, this option is irrelevant to the scenario.

A
Enable cloudFiles.useIncrementalListing.
This option enables incremental listing, which uses the cloud storage's native file listing capabilities to discover only new files since the last run. It significantly improves performance when dealing with large numbers of files because it avoids full directory scans. This is the recommended approach for optimizing file discovery in Auto Loader, especially for directories with millions of files.
B
Set cloudFiles.format to 'json'.
Why it fails: Specifying the file format is necessary for parsing but does not affect file discovery performance. It tells Auto Loader how to read the files, not how to find them. The slow ingestion is due to listing overhead, so this option does not solve the problem.
C
Set cloudFiles.maxFilesPerTrigger to a high value.
Why it fails: Increasing maxFilesPerTrigger controls how many files are processed per micro-batch, but it does not improve file discovery speed. It may actually increase per-batch processing time. The bottleneck is listing files, not processing them. Therefore, this option does not address the slow ingestion caused by file discovery.
D
Enable cloudFiles.schemaEvolutionMode to 'rescue'.
Why it fails: This option controls how schema evolution is handled, specifically rescuing data that doesn't match the schema. It has no impact on file discovery performance. The issue is slow listing, not schema handling. Thus, this option is irrelevant to the scenario.

Question 5mediummulti select

A data engineer is using Databricks Asset Bundles to deploy a data pipeline that includes a job and a notebook. The engineer wants to ensure that the deployment is idempotent and can be rolled back if needed. Which TWO of the following statements accurately describe the benefits of using Databricks Asset Bundles for this scenario? (Choose two.)

Trap 1: Databricks Asset Bundles require the use of Unity Catalog for all…

While Databricks Asset Bundles can integrate with Unity Catalog, they do not require it. Bundles can be used in workspaces without Unity Catalog enabled. The requirement depends on the resources being deployed; for example, if you deploy a pipeline that references Unity Catalog tables, then Unity Catalog is needed, but it is not a blanket requirement for all bundles.

Trap 2: Databricks Asset Bundles automatically synchronize data between…

Databricks Asset Bundles are focused on deploying code and configuration artifacts, such as jobs and notebooks, not on synchronizing data. Data synchronization is a separate concern and typically handled by ETL processes or data pipelines. Bundles do not manage data movement between environments.

Trap 3: Databricks Asset Bundles automatically create a new cluster for…

Databricks Asset Bundles do not automatically create clusters for each deployment. Cluster configurations are defined within the bundle and are created or reused based on the job's cluster settings. The bundle deployment process does not enforce isolation through new clusters; it simply applies the defined resources to the target workspace.

A
Databricks Asset Bundles allow you to define the entire pipeline as code, enabling version control and reproducible deployments.
Databricks Asset Bundles enable infrastructure-as-code by allowing you to define jobs, notebooks, and other resources in YAML files. This facilitates version control, code review, and reproducible deployments across environments. The bundle can be committed to a Git repository, ensuring that the pipeline definition is tracked and can be recreated consistently.
B
Databricks Asset Bundles require the use of Unity Catalog for all deployments.
Why it fails: While Databricks Asset Bundles can integrate with Unity Catalog, they do not require it. Bundles can be used in workspaces without Unity Catalog enabled. The requirement depends on the resources being deployed; for example, if you deploy a pipeline that references Unity Catalog tables, then Unity Catalog is needed, but it is not a blanket requirement for all bundles.
C
Databricks Asset Bundles automatically synchronize data between different environments during deployment.
Why it fails: Databricks Asset Bundles are focused on deploying code and configuration artifacts, such as jobs and notebooks, not on synchronizing data. Data synchronization is a separate concern and typically handled by ETL processes or data pipelines. Bundles do not manage data movement between environments.
D
Databricks Asset Bundles automatically create a new cluster for each deployment to ensure isolation.
Why it fails: Databricks Asset Bundles do not automatically create clusters for each deployment. Cluster configurations are defined within the bundle and are created or reused based on the job's cluster settings. The bundle deployment process does not enforce isolation through new clusters; it simply applies the defined resources to the target workspace.
E
Databricks Asset Bundles support rollback by allowing you to deploy a previous version of the bundle from version control.
Because bundles are defined as code and stored in version control, you can roll back to a previous version by checking out an earlier commit and redeploying. This provides a straightforward rollback mechanism, ensuring that you can revert to a known good state if a deployment introduces issues.

Question 6mediummultiple choice

A data engineer runs a nightly Databricks job that reads a large Delta table and writes aggregated results to another Delta table. The cluster logs show many small files in the source table, and the job runtime has increased steadily over weeks. The engineer wants to reduce the number of files without rewriting the entire table. Which command should be used?

Trap 1: ANALYZE TABLE sales COMPUTE STATISTICS

ANALYZE TABLE collects statistics used by the query optimizer to improve join ordering and filter estimation. It does not combine small files or change the physical layout of the data. While helpful for query planning, it will not reduce the number of files in the Delta table, so it fails to address the root cause of the slowdown.

Trap 2: ALTER TABLE sales SET TBLPROPERTIES…

This table property enables optimized writes for future write operations, which can reduce the number of small files produced going forward. However, it does not compact the existing small files already present in the table. The engineer needs to address the current file layout, so this setting alone will not fix the existing performance problem.

Trap 3: VACUUM sales RETAIN 0 HOURS

VACUUM removes old, unreferenced files that are no longer part of the Delta transaction log. It does not merge small files into larger ones; it only deletes obsolete data files. Running VACUUM with zero retention is also dangerous because it can break time travel and concurrent readers. It does not solve the small-file read performance issue.

A
ANALYZE TABLE sales COMPUTE STATISTICS
Why it fails: ANALYZE TABLE collects statistics used by the query optimizer to improve join ordering and filter estimation. It does not combine small files or change the physical layout of the data. While helpful for query planning, it will not reduce the number of files in the Delta table, so it fails to address the root cause of the slowdown.
B
OPTIMIZE sales
OPTIMIZE compacts small files into larger ones (bin-packing) and can be run on the entire table or with a WHERE clause on a subset. It does not require rewriting the whole table and is the standard Delta Lake maintenance command to address the small-file problem, directly improving read performance for the nightly aggregation job.
C
ALTER TABLE sales SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true')
Why it fails: This table property enables optimized writes for future write operations, which can reduce the number of small files produced going forward. However, it does not compact the existing small files already present in the table. The engineer needs to address the current file layout, so this setting alone will not fix the existing performance problem.
D
VACUUM sales RETAIN 0 HOURS
Why it fails: VACUUM removes old, unreferenced files that are no longer part of the Delta transaction log. It does not merge small files into larger ones; it only deletes obsolete data files. Running VACUUM with zero retention is also dangerous because it can break time travel and concurrent readers. It does not solve the small-file read performance issue.

Question 7mediummulti select

Which TWO of the following scenarios are valid use cases for utilizing Delta Lake's Change Data Feed (CDF)?

Trap 1: Performing full table overwrites in a batch job.

Full overwrites replace the entire dataset, rendering incremental change tracking irrelevant. CDF is designed specifically for tracking granular updates to existing data, not for scenarios where the entire table is refreshed or recreated from scratch during every pipeline execution.

Trap 2: Compressing large files into smaller partitions.

File compaction and partitioning are storage optimization tasks handled by the OPTIMIZE command. CDF is a data tracking feature, not a storage management feature, and does not perform file reorganization or partition manipulation to improve storage layout.

Trap 3: Querying the table history using the VERSION AS OF syntax.

VERSION AS OF is part of Time Travel, which accesses previous table snapshots. While related, it is not the same as Change Data Feed. Time Travel looks at the state of the table at a point in time, whereas CDF logs specific mutations.

A
Auditing data changes for compliance reporting.
CDF provides a detailed log of all changes, making it ideal for compliance and audit logs. It records exactly what was changed and when, allowing engineers to reconstruct the state of a row at any point in time without complex manual tracking.
B
Performing full table overwrites in a batch job.
Why it fails: Full overwrites replace the entire dataset, rendering incremental change tracking irrelevant. CDF is designed specifically for tracking granular updates to existing data, not for scenarios where the entire table is refreshed or recreated from scratch during every pipeline execution.
C
Incremental propagation of changes to downstream systems.
Propagating changes incrementally to downstream sinks like search indexes or data warehouses is a primary use case. CDF enables pipelines to read only the delta, significantly reducing compute costs and processing time compared to full table scans.
D
Compressing large files into smaller partitions.
Why it fails: File compaction and partitioning are storage optimization tasks handled by the OPTIMIZE command. CDF is a data tracking feature, not a storage management feature, and does not perform file reorganization or partition manipulation to improve storage layout.
E
Querying the table history using the VERSION AS OF syntax.
Why it fails: VERSION AS OF is part of Time Travel, which accesses previous table snapshots. While related, it is not the same as Change Data Feed. Time Travel looks at the state of the table at a point in time, whereas CDF logs specific mutations.

Question 8mediummulti select

A data engineer is designing a pipeline using Structured Streaming to ingest data into Delta Lake. Which THREE benefits are provided by using checkpoints in this scenario?

Trap 1: Providing the ability to recover the previous version of the table.

Recovering previous versions of a table is a feature of Delta Lake's Time Travel, which relies on the transaction log, not the streaming checkpoint. Checkpoints track the progress of the streaming query itself, whereas the Delta log tracks the history and state of the data stored within the table.

Trap 2: Automatically cleaning up old data files in the target directory.

Cleaning up old data files is handled by the VACUUM command in Delta Lake, not by the streaming checkpoint mechanism. Checkpoints are strictly for managing the state of the streaming query, while physical file management and storage optimization are part of Delta Lake's table maintenance and lifecycle operations.

A
Enabling the stream to resume from the exact point of failure.
Checkpoints record the offsets of the data that has been successfully processed. If the streaming job fails or is manually stopped, Spark uses these offsets to determine where to restart the processing. This ensures that no data is skipped and that the pipeline maintains its continuity across different runs.
B
Providing the ability to recover the previous version of the table.
Why it fails: Recovering previous versions of a table is a feature of Delta Lake's Time Travel, which relies on the transaction log, not the streaming checkpoint. Checkpoints track the progress of the streaming query itself, whereas the Delta log tracks the history and state of the data stored within the table.
C
Ensuring exactly-once processing semantics for the ingestion.
By tracking processed offsets and coordinating with the Delta Lake transaction log, checkpoints ensure that each record is processed and committed exactly once. This prevents the creation of duplicate records in the target table, which is essential for maintaining data quality and accuracy in downstream analytical or reporting applications.
D
Automatically cleaning up old data files in the target directory.
Why it fails: Cleaning up old data files is handled by the VACUUM command in Delta Lake, not by the streaming checkpoint mechanism. Checkpoints are strictly for managing the state of the streaming query, while physical file management and storage optimization are part of Delta Lake's table maintenance and lifecycle operations.
E
Storing the state of aggregations across streaming batches.
For stateful operations like aggregations or joins, checkpoints store the intermediate state of the computation. This allows Spark to maintain running totals or windowed calculations across multiple micro-batches, ensuring that the results remain accurate even if the stream is interrupted and subsequently restarted at a later time.

Question 9mediummultiple choice

Which Databricks feature should be used to securely share data with external organizations without duplicating the data?

Trap 1: Unity Catalog

While Unity Catalog manages data governance and access control internally, it is not a direct tool for sharing data across separate organizations or cloud environments. Delta Sharing is the specific protocol designed for cross-organizational data exchange while maintaining the security policies defined by the Unity Catalog service.

Trap 2: Databricks Workflows

Workflows are used for orchestrating internal data processing tasks and dependencies. They are not intended for data sharing or collaboration with external parties. Using Workflows to share data would likely involve insecure methods like moving files to a shared location, which is inefficient and violates modern data governance practices.

Trap 3: MLflow Model Registry

The MLflow Model Registry is for managing the versioning and deployment of machine learning models. It does not provide mechanisms for sharing raw data or tables with external organizations. Its scope is entirely focused on the lifecycle management of AI assets, not on secure enterprise data exchange or distribution.

A
Unity Catalog
Why it fails: While Unity Catalog manages data governance and access control internally, it is not a direct tool for sharing data across separate organizations or cloud environments. Delta Sharing is the specific protocol designed for cross-organizational data exchange while maintaining the security policies defined by the Unity Catalog service.
B
Delta Sharing
Delta Sharing is the industry-standard, open protocol that enables secure, scalable data sharing without data duplication. It allows the recipient to access data directly from the provider's cloud storage, ensuring that the shared data remains up-to-date and consistent, which is ideal for external collaboration and B2B analytics.
C
Databricks Workflows
Why it fails: Workflows are used for orchestrating internal data processing tasks and dependencies. They are not intended for data sharing or collaboration with external parties. Using Workflows to share data would likely involve insecure methods like moving files to a shared location, which is inefficient and violates modern data governance practices.
D
MLflow Model Registry
Why it fails: The MLflow Model Registry is for managing the versioning and deployment of machine learning models. It does not provide mechanisms for sharing raw data or tables with external organizations. Its scope is entirely focused on the lifecycle management of AI assets, not on secure enterprise data exchange or distribution.

Question 10hardmultiple choice

A data engineer is working with a Delta table that contains a column 'timestamp' of type timestamp. The table is partitioned by date. The engineer needs to run a query that filters on a specific date range and also on a high-cardinality column 'user_id'. The query is performing poorly. Which optimization technique should the engineer apply to improve query performance?

Trap 1: Convert the Delta table to Parquet format to leverage predicate…

Delta Lake already supports predicate pushdown and data skipping, often more efficiently than plain Parquet due to its transaction log and statistics. Converting to Parquet would lose Delta's advanced features like ACID transactions, time travel, and Z-Ordering. Predicate pushdown is not exclusive to Parquet; Delta Lake also provides it. Thus, this change would not improve performance and would sacrifice important capabilities.

Trap 2: Enable Delta Lake caching by running CACHE TABLE on the Delta table.

Caching the entire Delta table in memory can improve performance for repeated queries, but it is not a targeted optimization for filtering on high-cardinality columns. It consumes significant memory and may not be feasible for large tables. Moreover, caching does not help with data skipping or predicate pushdown, which are more effective for selective queries. This approach is typically used for frequently accessed tables, not for optimizing a specific query pattern.

Trap 3: Repartition the Delta table by the 'timestamp' column.

Repartitioning by 'timestamp' would reorganize the data but is unlikely to improve filtering on 'user_id'. Repartitioning is a physical layout change that can help with parallelism but does not provide data skipping benefits for high-cardinality columns. Since the table is already partitioned by date, further repartitioning by timestamp may lead to many small files and degrade performance. This is not the appropriate optimization for the given query pattern.

A
Use Z-ORDER BY on the 'user_id' column when writing the Delta table.
Z-ORDER BY is a technique that colocates related data in the same set of files, improving data skipping for queries that filter on the Z-ordered columns. By Z-Ordering on 'user_id', the engineer can reduce the amount of data scanned when filtering on that high-cardinality column. This is especially effective when combined with partitioning on date, as it further optimizes within each partition. It is a recommended best practice for Delta Lake performance tuning.
B
Convert the Delta table to Parquet format to leverage predicate pushdown.
Why it fails: Delta Lake already supports predicate pushdown and data skipping, often more efficiently than plain Parquet due to its transaction log and statistics. Converting to Parquet would lose Delta's advanced features like ACID transactions, time travel, and Z-Ordering. Predicate pushdown is not exclusive to Parquet; Delta Lake also provides it. Thus, this change would not improve performance and would sacrifice important capabilities.
C
Enable Delta Lake caching by running CACHE TABLE on the Delta table.
Why it fails: Caching the entire Delta table in memory can improve performance for repeated queries, but it is not a targeted optimization for filtering on high-cardinality columns. It consumes significant memory and may not be feasible for large tables. Moreover, caching does not help with data skipping or predicate pushdown, which are more effective for selective queries. This approach is typically used for frequently accessed tables, not for optimizing a specific query pattern.
D
Repartition the Delta table by the 'timestamp' column.
Why it fails: Repartitioning by 'timestamp' would reorganize the data but is unlikely to improve filtering on 'user_id'. Repartitioning is a physical layout change that can help with parallelism but does not provide data skipping benefits for high-cardinality columns. Since the table is already partitioned by date, further repartitioning by timestamp may lead to many small files and degrade performance. This is not the appropriate optimization for the given query pattern.