Databricks-DE-Pro Practice Test 20 — 15 Questions

Question 1

Which design pattern is best suited for handling late-arriving data in a medallion architecture?

Accepted Answer

Update the Silver layer using a MERGE operation that matches on event time.. Late-arriving data is common in streaming and batch pipelines. Using a watermarking strategy combined with a merge or upsert operation allows the system to process incoming data while maintaining the integrity of historical windows. By allowing late records to update existing states, the system remains accurate despite delays from the source, which is critical for time-sensitive financial or operational reporting where accuracy is paramount.

Answer

Discard all late-arriving records to ensure a clean source-of-truth.

Answer

Append late-arriving data to the Bronze layer but ignore it in all downstream layers.

Answer

Create a separate 'late_data' table and join it to the main table every month.

Question 2

A data engineer is configuring a Databricks workspace with Unity Catalog. The security team wants to ensure that all data access is logged for compliance auditing. The engineer enables audit logs and configures delivery to a cloud storage location. Which Unity Catalog object should the engineer use to query audit logs for data access events?

Accepted Answer

The system table 'system.access.audit' in Unity Catalog.. Unity Catalog system tables include 'system.access.audit', which records audit events such as data access. This table is automatically populated and can be queried for compliance. Other options refer to non-existent or unrelated objects. The system table is the correct and standard way to access audit logs within Unity Catalog.

Answer

The 'event_log' table in the workspace's Hive metastore.

Answer

The 'audit_logs' Delta table in the 'default' database.

Answer

The 'information_schema.audit_logs' view in Unity Catalog.

Question 3

A logistics company wants to analyze shipment delays. The fact table `fact_shipments` has a `delay_minutes` measure. The team needs to slice delays by the reason for delay, which can be one of several predefined categories. Which dimension modeling approach is most suitable?

Accepted Answer

Create a dimension table `dim_delay_reason` with a surrogate key and reference it from the fact table.. A dedicated dimension table for delay reasons provides a clean, efficient way to slice shipment delays. It supports consistent categorization and easy addition of new reasons. Storing as a string in the fact table or snowflaking adds performance and maintenance overhead. A junk dimension would obscure the ability to analyze by delay reason alone.

Answer

Create a snowflake schema by normalizing delay reasons into multiple tables.

Answer

Use a junk dimension to combine delay reason with other low-cardinality attributes.

Answer

Store the delay reason as a string column directly in the fact table.

Question 4

When refining data in a Medallion architecture, why is it recommended to perform schema enforcement as early as possible in the Bronze layer?

Accepted Answer

It prevents malformed data from polluting downstream layers and makes debugging easier.. Early enforcement catches errors at the ingestion point, preventing corrupt data from propagating into Silver or Gold layers. This practice keeps the 'upstream' data quality high, reducing the need for costly, complex fixes in downstream tables. It ensures that the Medallion architecture remains clean and reliable, minimizing the time spent debugging issues that could have been resolved at the very first step of the data pipeline.

Answer

It eliminates the need for any further schema validation in the Silver or Gold layers.

Answer

It ensures that the storage cost of the Bronze layer is kept at a minimum.

Answer

It automatically converts all incoming file formats into the optimal Delta format.

Question 5

Which of the following describes the behavior of the 'OPTIMIZE' command in Databricks?

Accepted Answer

It rewrites data files into a more efficient layout for reads.. The 'OPTIMIZE' command performs file compaction, which consolidates small files into larger, optimally sized files. This improves scan performance significantly by reducing the metadata overhead of opening and reading many tiny files. It also supports Z-Ordering, which co-locates related data in the same files, further enhancing the efficiency of data skipping and improving query speed for common filter predicates.

Answer

It recomputes statistics to improve query planning.

Answer

It clears the transaction log to reduce storage costs.

Answer

It converts non-Delta tables to Delta format.

Question 6

In the medallion architecture, which layer is primarily responsible for applying business logic and historical aggregations?

Accepted Answer

The Gold layer is designed for applying business logic and historical aggregations.. The Gold layer is the final stage of the medallion architecture, where data is prepared for consumption by BI tools and data science applications. It involves cleaning, transforming, and aggregating data according to business rules. This layer represents the 'source of truth' for analytical reporting, as it is structured to support specific business use cases rather than representing the raw or cleaned granular data found in earlier layers.

Answer

The Bronze layer acts as the primary layer for historical aggregation and business logic.

Answer

The Silver layer is designed for applying complex business logic and final aggregations.

Answer

The Raw layer stores all business logic results for future auditing.

Question 7

A financial services firm stores market data in an external location registered in Unity Catalog as `s3://firm-market-data/`. The security team requires that only a specific IAM role, assumed by a Unity Catalog storage credential, can read the bucket, and that no Databricks user can bypass Unity Catalog to read the data directly with their own cloud credentials. The Data Engineer must configure the storage credential. Which configuration achieves this?

Accepted Answer

Create a storage credential that assumes an IAM role whose trust policy allows only the Unity Catalog metastore's IAM role, and grant the credential to the external location.. The secure pattern is a storage credential that assumes a customer IAM role whose trust policy names only the Unity Catalog metastore's IAM role. That makes the metastore the sole path to the bucket, so users cannot use their own credentials to read it, and Unity Catalog enforces privileges at query time. Static keys, broad workspace trust, and instance profiles all create alternate access paths or long-lived secrets.

Answer

Create a storage credential using an access key and secret key with full S3 permissions, and attach it to the external location.

Answer

Create a storage credential backed by an instance profile attached to the cluster's EC2 instances, and grant it to the external location.

Answer

Create a storage credential that assumes an IAM role trusted by every Databricks workspace in the account, and grant it to the external location.

Question 8

A data engineer is configuring a Unity Catalog storage credential to access an AWS S3 bucket. The organization's security policy requires that Databricks assumes an IAM role, and that no long-lived AWS access keys are stored in Databricks. The engineer has created an IAM role with a trust policy and an external ID. Which action must the engineer take to complete the storage credential configuration in Unity Catalog?

Accepted Answer

Specify the IAM role ARN and the external ID in the storage credential, and ensure the role's trust policy allows the Databricks AWS account to assume it.. For Unity Catalog to access S3 without long-lived AWS keys, a storage credential must reference an IAM role that Databricks assumes. The role's trust policy must permit the Databricks AWS account to assume it, and an external ID can be used to prevent the confused deputy problem. This design centralizes credential management and avoids storing static keys.

Answer

Provide the AWS access key ID and secret access key of an IAM user that has permissions to the S3 bucket.

Answer

Attach an instance profile to the cluster and grant the cluster's IAM role access to the S3 bucket, then create the storage credential without an IAM role.

Answer

Configure a service principal in Databricks, assign it the S3 bucket policy, and use its client ID and secret as the storage credential.

Question 9

A Data Engineer is setting up Lakehouse Federation for a PostgreSQL database. Which TWO steps are required to ensure that users can securely query the data using Unity Catalog?

Accepted Answer

Create a connection object with appropriate credentials in Unity Catalog.. To set up Lakehouse Federation, you must first establish a connection object that stores credentials securely in Unity Catalog. Then, you must create a foreign catalog that maps the remote database to a local Unity Catalog structure. These two steps enable Unity Catalog to manage permissions and translate queries for the federated source without exposing raw credentials to end users.

Answer

Ingest all PostgreSQL data into a S3 bucket first.

Answer

Install a custom JDBC driver on every user's local machine.

Answer

Configure a VPC peering connection to the external database.

Question 10

Which THREE actions are required to properly implement a secure data sharing strategy using Delta Sharing?

Accepted Answer

Create a SHARE object containing the tables to be shared.. Delta Sharing provides a secure way to share data without replication. Implementing it requires creating a sharing object, defining the recipients, and granting access to specific tables. This ensures that only authorized entities can access the data, maintaining strict governance. By abstracting the data access from the storage location, organizations can share data securely with external partners while retaining full control over who has access and when that access is revoked.

Answer

Grant the recipient access to the underlying S3 bucket directly.

Answer

Copy the data to a public-facing S3 bucket for easier access.

Question 11

Which THREE of the following are benefits of using Delta Lake over standard Parquet files in Databricks?

Accepted Answer

ACID transactions. Delta Lake provides a robust layer of reliability and performance features on top of Parquet. ACID transactions ensure data consistency during concurrent writes. Schema enforcement prevents data corruption by rejecting non-conforming writes, while time travel allows for auditing and disaster recovery. These capabilities are fundamental to modern Lakehouse architectures, as they bridge the gap between reliable data warehousing and the flexibility of cloud object storage.

Answer

Automatic file compaction to remove Parquet footer bloat

Answer

Automatic conversion from CSV to Parquet on read

Question 12

A data engineer is using Databricks Auto Loader to ingest CSV files into a Delta table. The engineer notices that some files have a different delimiter (semicolon instead of comma). Which option should be used to handle this variation?

Accepted Answer

Preprocess the files to standardize the delimiter before ingestion.. The correct approach is to preprocess the files to standardize the delimiter before ingestion. Auto Loader does not support per-file delimiter detection; it requires a consistent format. By converting all files to a common delimiter, you ensure that Auto Loader parses them correctly and ingests data without errors. This is a common preprocessing step in data pipelines.

Answer

Set the cloudFiles.schemaEvolutionMode to 'addNewColumns' to handle delimiter changes.

Answer

Use the cloudFiles.format option with a custom delimiter per file.

Answer

Set the delimiter option to ';' for all files.

Question 13

A retail company wants to analyze sales by product, store, and date. The data team is designing the Gold layer and needs to choose between a star schema and a snowflake schema. Which factor most strongly favors a star schema in a Databricks Lakehouse?

Accepted Answer

Star schemas simplify queries and improve performance by minimizing joins.. Star schemas minimize joins by denormalizing dimensions, which simplifies queries and boosts performance. In Databricks, this aligns well with Delta Lake and Photon optimizations. Snowflake schemas normalize dimensions, increasing joins and complexity. Storage and referential integrity are not primary advantages of star schemas.

Answer

Star schemas reduce data redundancy by normalizing dimension tables.

Answer

Star schemas enforce referential integrity through foreign key constraints.

Answer

Star schemas require less storage space than snowflake schemas.

Question 14

A data engineer at a healthcare company needs to share a Delta table containing patient records with an external research partner. The partner must only see aggregated statistics, not individual patient rows. The engineer wants to enforce this at the data sharing layer without creating a separate physical copy of the table. Which approach should the engineer take?

Accepted Answer

Create a view that aggregates the data and share the view through Delta Sharing.. Delta Sharing allows sharing views that can perform aggregation and filtering, enabling fine-grained control over what data is exposed. Sharing an aggregated view ensures the partner sees only summary statistics, not raw patient records. This approach avoids data duplication and maintains centralized governance. The other options either do not support the required granularity or are not designed for external sharing.

Answer

Create a Delta Share and add the table with a partition filter that excludes sensitive columns.

Answer

Export the aggregated data to a CSV file and provide it to the partner via a secure file transfer.

Answer

Use Lakehouse Federation to create a foreign catalog pointing to the partner's database and grant SELECT on the aggregated columns.

Question 15

A data engineer is tuning a Spark job and decides to use 'Z-Ordering' on a Delta table. Which THREE of the following are valid considerations when selecting columns for Z-Ordering?

Accepted Answer

Columns with high cardinality are generally better candidates.. Z-Ordering improves data skipping by clustering related information in the same set of files. Choosing the right columns is crucial; high-cardinality columns with frequent filter predicates are ideal. Excessive Z-Ordering can lead to high maintenance costs and metadata overhead. Mastering this technique is essential for performance tuning in Databricks, as it drastically reduces the amount of data read during query execution, directly impacting both latency and cloud infrastructure costs.

Answer

You should Z-Order on every column to maximize performance.

Answer

Z-Ordering is most effective on columns that are never filtered.