Databricks-DE-Pro Exam Questions and Answers

Add 'cloudFiles.schemaEvolutionMode': 'addCol'.

Setting schema evolution mode to 'addCol' enables the ingestion process to detect new columns in the source data and automatically add them to the target Delta table. This ensures the table structure remains synchronized with the incoming data stream, preventing the loss of new attributes arriving in source files.

Perform a full overwrite of the Delta table for every load.

Since there is no mechanism to identify changed data, performing a full overwrite ensures the target table always matches the source. This is the standard pattern for handling tables without watermark columns. You should balance the frequency of the load with the size of the table to manage compute costs.

Apply masking logic within the initial streaming ingestion transformation.

Applying masking logic during the initial ingestion transformation (e.g., using withColumn and sha2 or similar functions) ensures that PII is protected before it is ever committed to storage. This maintains data privacy from the moment the data enters the ecosystem, preventing sensitive information from ever reaching the persistent Bronze table.

Declarative pipeline management and automated dependency handling.

DLT allows you to define the pipeline in a declarative way, where the system automatically manages the creation and execution of the Directed Acyclic Graph (DAG) of dependencies. This eliminates the manual effort of coordinating complex streams, reducing operational overhead and the likelihood of human error in pipeline configuration.

Built-in data quality monitoring with Expectations.

DLT provides a native 'Expectations' framework that allows you to define quality constraints directly in your code. This enables automatic logging and alerting for data quality issues during ingestion, ensuring that bad data is handled according to business rules without needing custom, complex validation logic in your pipeline.

It stores metadata about the inferred schema and tracks evolution.

The schema location is where Auto Loader saves the inferred schema and tracks historical changes. This allows the process to maintain state regarding the data structure, ensuring that subsequent batches are processed correctly even as the source schema changes over time across multiple runs or restarts.

Tracking the 'numInputRows' metric in the streaming query progress.

This metric tells you how many records are being processed in each micro-batch. It is essential for detecting data spikes, identifying potential ingestion lags, and verifying that the volume of data flowing through the pipeline aligns with the expected source throughput, which helps in capacity planning and performance tuning.

Setting up alerts on failed expectations in DLT.

Expectations are the primary mechanism for detecting bad data. If a pipeline is running but data is being dropped or failing quality checks, you need immediate notification. Alerting on these failures ensures that data quality issues are addressed before they propagate to Silver or Gold tables, maintaining overall pipeline integrity.

Using DLT event logs to analyze pipeline execution details.

The DLT event log is a comprehensive source of truth for the health, progress, and performance of your pipelines. It contains metadata about every update, failure, and quality check result. Analyzing this data is the industry standard for troubleshooting and auditing production data pipelines in Databricks environments.

Define a column mask using SQL functions to redact data for the analyst group.

Unity Catalog column masking allows engineers to apply granular security policies directly to table columns. By defining a mask, the platform automatically redacts data based on the user's role at query time, ensuring compliance without modifying the underlying storage. This provides a clean separation between data storage and security enforcement.

It provides automated, column-level lineage tracking for SQL and Python workloads.

Unity Catalog integrates directly with the Databricks engine to track data movement, including column-level transformations. This automated capture ensures that lineage information is always up-to-date, removing the need for manual documentation or external tools that often fall out of sync with the actual data processing logic implemented in the notebooks.

It enables visibility into dependencies between datasets across different workspaces.

Because Unity Catalog is a multi-workspace governance solution, it provides a centralized view of data dependencies regardless of where the compute occurs. This cross-workspace visibility is essential for enterprise governance, allowing administrators to understand the impact of schema changes or data deletion across the entire organization's data ecosystem.

Create a SHARE object containing the tables to be shared.

The SHARE object is the container in Unity Catalog that bundles the datasets intended for distribution. Without creating this object, there is no mechanism to group the tables or views for the recipient, making it impossible to manage the scope of data being exposed to the external parties.

Configure a RECIPIENT object representing the external partner.

The RECIPIENT object identifies the external entity allowed to access the shared data. This object is critical for the authentication process, as it generates the unique credentials required by the recipient to establish a secure connection and retrieve the data, ensuring that only authenticated parties can consume the shared information.

Execute a GRANT SHARE command to link the SHARE and RECIPIENT objects.

The grant command is the final step in establishing the relationship between the data bundle and the recipient. It effectively authorizes the external party to access the contents of the share, allowing them to start querying the data through the Delta Sharing protocol with the provided authentication token.

Customer-managed keys (CMK) for managed storage.

Customer-managed keys provide a mechanism to encrypt data at rest using keys managed by the customer. This is the industry-standard approach for ensuring data confidentiality in a multi-tenant cloud environment, providing an additional layer of security and auditability that is essential for enterprise compliance and robust data governance.

Use Delta Sharing with an open sharing provider.

Delta Sharing is the industry-standard, secure protocol for data sharing. It supports both Databricks and non-Databricks recipients, allowing for seamless integration. Because it works at the protocol level, it avoids the security risks of copying data and ensures that the provider retains full control over the shared assets.

External locations require a storage credential to function.

A storage credential acts as a bridge between Unity Catalog and the cloud storage provider. Without it, Unity Catalog would have no way to authenticate and access the files in the storage account on behalf of the user, making it impossible to manage external tables securely within the metastore.

Access to external locations can be granted to users using GRANT statements.

Unity Catalog allows administrators to use the GRANT command to authorize specific users or groups to read or write to an external location. This declarative security model is key to modern governance, as it makes access management programmatic and auditable, aligning with the overall goal of centralized control and security.

expectations_violated_records

This metric exposes the number of records that failed a quality expectation constraint during the execution of a DLT pipeline. Tracking this count allows engineers to identify data drift or schema issues, enabling proactive remediation and ensuring that final tables meet established business quality standards.

When the 'duration' column value in the query output is strictly greater than 3600.

The 'op' field is set to '>', and the column is 'duration' with a threshold of '3600'. Therefore, any value exceeding 3600 returned by the query result set will satisfy the condition and transition the alert to the 'Triggered' state.

System tables (Audit logs)

System tables store audit records for all user and system activities in the Databricks environment. They are the authoritative source for monitoring access patterns, ensuring compliance with security requirements, and identifying potential anomalies or security incidents across the entire Databricks workspace ecosystem.

Cluster CPU utilization

High CPU utilization is a direct indicator of compute saturation. When worker nodes sustain high CPU loads, processing throughput drops, leading to job latency. Monitoring this metric allows for the implementation of auto-scaling policies to add nodes, effectively balancing the workload across a larger compute resource pool.

Use the 'Notifications' setting in the DLT pipeline configuration.

DLT pipelines include a dedicated notifications section in the settings. By configuring a destination—such as a webhook URL—engineers can receive automated notifications for pipeline events, including successful completions or failures, ensuring seamless integration with communication tools like Slack or Microsoft Teams.

Driver and Executor logs.

Driver and executor logs contain the stack traces and error messages that specifically indicate memory exhaustion. Reviewing these logs allows the engineer to determine whether the error occurred during a shuffle operation, a broadcast join, or while loading a large data partition into memory.

Spark UI Executor memory metrics.

The Spark UI provides real-time visualization of memory usage across all executors. Monitoring how memory is consumed during specific stages helps pinpoint if a memory-intensive transformation or a skew issue is the root cause of the crashes, enabling targeted code optimizations or configuration adjustments.

Configure the downstream task to use the 'depends_on' attribute to reference the upstream task ID.

The 'depends_on' attribute explicitly defines the task DAG structure within Databricks Workflows. By creating this dependency, the scheduler guarantees that the downstream task will only execute if the upstream task succeeds, preventing erroneous runs when input data is missing or corrupted due to preceding failures.

Enable the 'repair and rerun' feature to target only the failed tasks in the pipeline.

The repair and rerun feature allows engineers to execute only the failed tasks and their downstream dependents. This preserves the existing successful outputs of the workflow and ensures that the system only attempts to process data that was previously blocked by the specific task failure event.

The table exists only in the temporary session catalog of a different notebook.

Temporary views or tables created in interactive notebook sessions are not persisted in the shared Hive Metastore and are scoped to the session. Since the job runs in a separate, isolated environment, it cannot access objects defined in the transient memory of a different interactive session.

The runner does not have the DATABRICKS_HOST and DATABRICKS_TOKEN environment variables set.

The Databricks CLI looks for these specific environment variables to authenticate with the workspace. If they are absent, the CLI cannot establish a connection, leading to an error indicating that it cannot reach or find the workspace. This is the most common cause of failures in automated pipelines.

Use the Spark UI to identify tasks using 'collect()' or 'toPandas()' on large datasets.

The Spark UI allows you to inspect the execution plan and identify operations that move data from worker nodes to the driver node. Using 'collect()' on large datasets is a classic cause of driver out-of-memory errors. Identifying these bottlenecks allows you to replace them with distributed data writing.

Implement structured logging within the application code to track variable states.

Structured logging provides granular insight into the execution path and variable values, which are otherwise unavailable once a job fails. By writing these logs to a persistent sink, engineers can reconstruct the state of the application at the exact moment of failure, significantly reducing the time required for investigation.

Modularize code into libraries (wheels) to enable easier unit testing and local debugging.

Moving logic into modular wheels allows developers to write unit tests and debug code in a local IDE before deployment. This isolates business logic from the Databricks environment, enabling faster iteration and higher code quality, which prevents many bugs from ever reaching the production environment in the first place.

Configure alerts on job failures to send notifications to a team Slack or email channel.

Alerting is essential for immediate awareness of failures. By integrating Databricks job monitoring with communication platforms, the team is notified immediately when a job fails, ensuring that the investigation process starts as soon as possible, thereby minimizing the impact of the failure on data availability and business processes.

By clicking the 'Logs' tab within the specific task run details in the Jobs UI.

The Jobs UI provides a 'Logs' tab that captures stdout, stderr, and log4j outputs for every task execution. This is the official, supported way to view execution logs within the Databricks workspace, allowing engineers to quickly debug failures without leaving the browser or using complex CLI commands.

Apply a column mask using a SQL function in Unity Catalog.

Column masking in Unity Catalog allows administrators to define functions that dynamically redact or obscure data based on the user's role. This provides a unified, policy-driven approach to data security that is applied at query time, ensuring compliance without the complexity of managing numerous views or physical tables.

Use groups instead of individual users for access grants.

Granting permissions to groups rather than individual users simplifies access management and reduces the risk of human error. When a new user joins a team, they automatically inherit the correct permissions by being added to the relevant group, ensuring consistent security posture across the entire data platform.

Use Service Principals for automated CI/CD job execution.

Service principals are the ideal identity for automated processes and CI/CD pipelines. They provide a secure, non-interactive way to manage data access without relying on individual user credentials, which expire and pose security risks. This ensures that pipelines maintain consistent access even when team members change.

To control the lifecycle of the encryption keys used for storage.

The primary purpose of CMK is to allow the organization to manage the lifecycle of encryption keys. This includes rotation policies and the ability to immediately revoke access to the storage account, ensuring that the cloud provider cannot access the data without the customer's active cooperation.

IP Access Lists.

IP Access Lists are the specific Databricks feature designed to restrict access based on source IP. By defining a set of allowed CIDR ranges, administrators can ensure that users can only interact with the Databricks environment from authorized network locations, satisfying critical security and compliance requirements for enterprise clients.

It provides long-lived programmatic access to the API.

PATs provide a mechanism for scripts to authenticate with Databricks without interactive logins. Because they are long-lived, if they are hardcoded in scripts or exposed in logs, they provide an attacker with persistent access to the user's workspace, creating a significant security vulnerability if not rotated regularly.

It captures table-to-table data dependencies.

Unity Catalog lineage maps dependencies between tables, including views and downstream transformations. This is essential for compliance, as it allows engineers to see the flow of sensitive data through the ETL pipeline, making it easier to ensure that masking policies are applied to all downstream, derived datasets.

A sharing credential file

The sharing credential file is the fundamental mechanism for Delta Sharing. It contains the short-lived access token and the endpoint URL required by the external client to authenticate and authorize requests. This file acts as the bridge between the provider's data and the recipient's consumption tool, ensuring secure access.

The SQL Warehouse pushes down query predicates to the external database.

A key benefit of Lakehouse Federation is query pushdown. The SQL Warehouse optimizes the execution plan by sending filters, aggregations, and joins to the external database engine. This reduces data movement across the network and leverages the external database's compute power, significantly improving query performance for federated sources.

Create a connection object with appropriate credentials in Unity Catalog.

Creating a connection object is the first step. It encapsulates the connection details (URL, driver, etc.) and credentials (e.g., username/password or secret) in a secure manner. This object is stored in Unity Catalog, allowing administrators to manage access centrally and providing compute clusters with necessary information to access.

Create a foreign catalog that references the PostgreSQL connection.

A foreign catalog acts as the entry point for the external data within Unity Catalog. It links the connection object to the specific remote database, allowing users to navigate and query the external schema as if it were part of the local Unity Catalog metadata hierarchy for seamless access.

Delta Sharing allows recipients outside of the Databricks ecosystem to access data.

Delta Sharing is an open-source protocol that decouples the data provider from the recipient's environment. This enables organizations to share data securely with partners who may be using different cloud providers or query engines, as long as they can consume the open Delta Sharing protocol via a credential file.

It provides a single security model for all data sources.

Unity Catalog abstracts the security differences between various data sources. Whether you are querying a PostgreSQL database, a Snowflake instance, or internal Delta tables, you use the same Unity Catalog permission syntax. This dramatically reduces the administrative overhead and potential for configuration errors across a complex multi-source data landscape.

Updates are automatically visible to the recipient.

Because Delta Sharing queries the live Delta table, the recipient always sees the most recent committed state of the data. This provides a seamless, real-time data sharing experience, eliminating the manual overhead of exporting files or synchronizing data snapshots between the provider and the recipient organizations.

Salt the skewed column and perform the join.

Salting distributes the rows associated with the skewed key across multiple partitions by appending a random integer. This forces the join operation to process these rows in parallel across different executors, effectively eliminating the bottleneck caused by the skewed distribution of the customer_id column during the shuffle phase.

Apply Dynamic Views with functions like 'mask_hash' or 'case'.

Dynamic Views allow the definition of access policies based on the session user's role or identity. By using functions such as masking or conditional logic, you can obscure PII for unauthorized users while keeping the underlying data intact for administrative roles or authorized analytical processes as required.

Columns with high cardinality are generally better candidates.

High-cardinality columns allow for more effective data clustering. By grouping similar values together, the engine can skip large chunks of data that do not meet the filter criteria. This is the primary mechanism by which Z-Ordering improves query performance compared to basic partitioning schemes.

Z-Ordering should be applied to columns frequently used in WHERE clauses.

The primary benefit of Z-Ordering is to accelerate range queries and point lookups. Columns used in WHERE clauses are the most common candidates because the engine can use the Z-Index to quickly prune files, resulting in significantly fewer files being scanned during the execution of the query.

Z-Ordering significantly improves performance for joins on the indexed column.

When join keys are Z-Ordered, the physical layout of the data may align with the join distribution, potentially reducing the amount of data shuffled across the network. This can lead to faster join performance, especially when joining two large tables that have been optimized with similar clustering strategies.

Define the function inside the same notebook but outside the 'dlt.table' function.

Defining the function in the same module ensures it is accessible during the execution of the DLT pipeline. The decorator will register the function as part of the materialization process, allowing DLT to call it while processing data, provided that any necessary external libraries are also installed.

Using a source system that supports replaying data (e.g., Kafka or Delta).

Exactly-once requires the ability to re-read the stream from a specific offset if a failure occurs. Kafka and Delta provide the necessary offset management to ensure that data can be re-processed accurately after a system interruption, which is the foundational requirement for guaranteeing consistent results in streaming pipelines.

Writing to the sink using an idempotent operation.

Idempotency ensures that multiple attempts to write the same data result in the same final state in the destination. This is crucial for handling retries that occur during a failure; without idempotency, a retry could lead to duplicate rows, violating the exactly-once processing guarantee required by many downstream systems.

Maintaining a checkpoint directory in a reliable storage location.

The checkpoint directory stores the state and metadata of the streaming query. If the query fails, it uses this information to recover from the exact point of failure. This mechanism is mandatory for maintaining the state across failures and ensuring that progress tracking remains consistent throughout the pipeline lifecycle.

Use the 'partitionBy' option in the write command.

The 'partitionBy' command instructs Spark to organize the output into a directory structure based on the values in the specified columns. This allows downstream queries to use partition pruning to only scan the necessary folders, significantly reducing data read and increasing query efficiency for the region data.

Add a CHECK constraint to the table using the ALTER TABLE ADD CONSTRAINT command.

The ALTER TABLE ADD CONSTRAINT command natively integrates with Delta Lake's transaction log to enforce validation at the moment of ingestion. It is highly performant and ensures that no transaction containing a null value in the specified column will be committed, effectively preventing data quality issues at the source.

The transaction fails entirely, and no changes are committed to the Delta table.

Constraints in Delta Lake are hard requirements. If an operation violates these rules, the commit fails, and the transaction is aborted. This guarantees that the table remains in a consistent state and prevents invalid data from entering the storage layer, which is crucial for maintaining reliable audit trails and reports.

Use the 'to_timestamp' function with an array of acceptable formats to parse the column.

The 'to_timestamp' function in Spark SQL accepts a format string or an array of formats. This allows the engine to attempt parsing against multiple patterns, which is the most robust and performant way to handle format inconsistencies without writing complex, slow-running row-based logic or custom UDFs.

Use the dropDuplicates() method with watermark settings to manage state.

Using dropDuplicates on a streaming DataFrame, combined with a watermark, allows Spark to manage the state of seen records efficiently. The watermark specifies the time limit for which duplicates are tracked, ensuring the state doesn't grow indefinitely, which is essential for long-running streaming pipelines consuming data with late arrivals.

Enforce strict schema validation on incoming Bronze files.

Schema enforcement at the Silver layer is critical to ensure data consistency. By validating that columns match expected types and structures, you prevent downstream failures in analytical queries and reporting tools, which often lack the robustness to handle unexpected schema changes or malformed data types automatically.

Perform deduplication to ensure unique records based on business keys.

Data quality at the Silver level requires uniqueness. Deduplication ensures that metrics are calculated based on accurate counts and that analytical models are not biased by repeated data points. This is foundational for providing reliable business insights and avoiding costly mistakes in decision-making based on inflated or incorrect datasets.

Standardize data formats (e.g., timestamps, currency codes) across all source systems.

Standardization is crucial for cross-system analysis. By normalizing formats like date/time and currency in the Silver layer, you enable joining and comparing data from disparate sources, which is the primary purpose of the Silver layer: creating a unified, trustworthy dataset that serves as the foundation for downstream analytical workloads.

Hash the PII columns using a salted, non-reversible cryptographic hash function.

Hashing with a salt provides a secure, non-reversible way to mask PII while maintaining referential integrity. This allows analysts to group data by the hash key without seeing the actual sensitive values. It is a highly recommended practice for balancing data utility with strict regulatory and privacy requirements.

The queries lack filters on 'region' or 'date', preventing effective partition pruning.

Because the table is partitioned by region and date, failing to include these in the WHERE clause forces the engine to scan every partition. Z-Ordering only clusters data within individual partitions. If the engine doesn't prune the partitions first, the Z-Ordering benefits are largely ignored during the scan.

The Gold layer is designed for applying business logic and historical aggregations.

Gold is the analytical layer. It transforms Silver data into business-ready aggregates, KPIs, and reports. By focusing on business logic here, the engineering team ensures that the data is prepared specifically for downstream consumption, reducing the computational load on end-user tools while ensuring consistent metrics across the organization.

Run the DELETE statement and then run the VACUUM command.

The DELETE command updates the Delta log to exclude the records from future reads. Running VACUUM removes the orphaned files that contain the deleted data. This combination ensures the records are both logically and physically removed from the data lake, fulfilling the legal requirements of GDPR compliance.

Update the Silver layer using a MERGE operation that matches on event time.

The MERGE operation is ideal for late-arriving data because it can identify existing records and update them with the corrected information. By using event time as a matching key, the pipeline can ensure that even if data arrives out of order, the state of the Silver table remains accurate.

It provides a clear progression of data quality and structure.

The medallion architecture organizes data into Bronze, Silver, and Gold to represent increasing levels of refinement. This structured approach allows teams to manage data quality incrementally, ensures that business logic is applied consistently, and provides an immutable raw history that can be reprocessed whenever requirements change.

To store business-ready data for analytical use cases.

The Gold layer provides final, aggregated datasets that are ready for BI and reporting. By presenting data in this format, it masks the underlying complexity of the raw and silver data, providing a performant and understandable view of the business to end-users and non-technical stakeholders.

Migrate interactive SQL workloads to Databricks Serverless Compute to dynamically scale resources and eliminate idle billing.

Serverless SQL warehouses scale automatically with query concurrency and bill only while active, directly addressing the sporadic, unpredictable workload and idle-cost constraint. Cold starts and out-of-memory errors from concurrency spikes are absorbed by dynamic resource provisioning rather than fixed cluster sizing.

Enable Photon acceleration on clusters executing heavy relational scans and complex analytical joins.

Photon uses a vectorized execution engine written in C++ to optimize CPU cache utilization and memory bandwidth, significantly accelerating scan and join heavy workloads. Faster execution directly translates to lower compute costs on standard multi-node clusters.

Enable spark.databricks.delta.optimizeWrite.enabled and spark.databricks.delta.autoCompact.enabled.

Enabling optimized writes and Auto Compact forces Spark to shuffle data to achieve well-sized files prior to writing and automatically triggers a compaction pass when small files are detected. This directly targets the root cause of metadata bottlenecks in high-frequency streaming architectures.

Convert the workload to use a Databricks Job cluster configured with spot instances and automatic fallback to on-demand.

Job clusters are cheaper than all-purpose clusters and terminate after the run. Configuring spot instances with automatic fallback to on-demand preserves cost savings while surviving pre-emptions, directly addressing the reliability failure and the four-hour runtime.

The min_idle_instances setting maintains running virtual machines continuously, incurring persistent infrastructure costs.

min_idle_instances keeps that number of virtual machines powered on at all times, even when no clusters are running. Those idle instances bill continuously for cloud infrastructure, which is the persistent cost driver behind the unexpected charges shown in the exhibit.

OPTIMIZE table_name

OPTIMIZE triggers a compaction process that aggregates small files into larger, optimized Parquet files. This process significantly improves read performance by enabling better data skipping and reducing the overhead associated with listing and opening numerous tiny files, which is a common bottleneck in high-frequency Delta Lake write workloads.

Set the auto-stop duration to a very low value, such as 1 minute, for serverless SQL warehouses.

Serverless SQL warehouses support very fast startup times, making a 1-minute auto-stop duration feasible. This minimizes the period that the warehouse remains active while idle, ensuring that billing stops almost immediately after the last query finishes, which is highly effective for reducing costs in environments with intermittent usage.

Implement SQL query history monitoring to identify and optimize long-running or resource-intensive queries.

Identifying expensive queries through the query history allows engineers to optimize code, improve filter usage, or adjust data partitioning. By reducing the total compute time required for these heavy jobs, the warehouse consumes fewer resources, enabling shorter auto-stop timers and lowering overall operational costs for the Databricks SQL environment.