Pass the Databricks Databricks Certification Databricks-Certified-Data-Engineer-Associate Questions and answers with CertsForce

The correct command to grant the necessary permission on the entire database to the new team is to use the GRANT USAGE command. The GRANT USAGE command grants the principal the ability to access the securable object, such as a database, schema, or table. In this case, the securable object is the database customers, and the principal is the group team. By granting usage on the database, the team will be able to see what tables already exist in the database. Option E is the only option that uses the correct syntax and the correct privilege type for this scenario. Option A uses the wrong privilege type (VIEW) and the wrong securable object (CATALOG). Option B uses the wrong privilege type (CREATE), which would allow the team to create new tables in the database, but not necessarily see the existing ones. Option C uses the wrong securable object (CATALOG) and the wrong principal (customers). Option D uses the wrong securable object (team) and the wrong principal (customers). References: GRANT, Privilege types, Securable objects, Principals

The query performing a streaming hop from raw data to a Bronze table is identified by using the Spark streaming read capability and then writing to a Bronze table. Let ' s analyze the options:

Option A: Utilizes .writeStream but performs a complete aggregation which is more characteristic of a roll-up into a summarized table rather than a hop into a Bronze table.

Option B: Also uses .writeStream but calculates an average, which again does not typically represent the raw to Bronze transformation, which usually involves minimal transformations.

Option C: This uses a basic .write with .mode( " append " ) which is not a streaming operation, and hence not suitable for real-time streaming data transformation to a Bronze table.

Option D: It employs spark.readStream.load() to ingest raw data as a stream and then writes it out with .writeStream, which is a typical pattern for streaming data into a Bronze table where raw data is captured in real-time and minimal transformation is applied. This approach aligns with the concept of a Bronze table in a modern data architecture, where raw data is ingested continuously and stored in a more accessible format.

DESCRIBE HISTORY my_table returns the Delta table’s operation history. Its result includes information such as the table version, operation timestamp, user identity when available, operation name, parameters, notebook or job details, and associated metrics. The engineer can filter the returned timestamps to analyze operations performed during the last seven days. SHOW TBLPROPERTIES displays configured table properties rather than transactional history. DESCRIBE DETAIL returns the current table metadata and storage characteristics, not a chronological audit of changes. Directly querying the _delta_log path is unnecessary, depends on file-level implementation details, and the displayed path is not the supported SQL statement for this audit requirement. Therefore, option C provides the supported and structured record of past Delta table operations.

================

The correct command to create a new table that contains all records from two tables without duplicate records is to use the UNION operator. The UNION operator combines the results of two queries and removes any duplicate rows. The INNER JOIN, OUTER JOIN, and MERGE operators do not remove duplicate rows, and the INTERSECT operator only returns the rows that are common to both tables. Therefore, option B is the only correct answer. References: Databricks SQL Reference - UNION, Databricks SQL Reference - JOIN, Databricks SQL Reference - MERGE, [Databricks SQL Reference - INTERSECT]

Delta Lake provides schema enforcement on write, preventing incompatible data from being silently added to a table. When schema evolution is explicitly enabled through the appropriate write or ingestion configuration, new optional columns can be added without dropping and recreating the existing table. Delta Lake also maintains a transaction log containing committed table versions, enabling table-history inspection and time-travel queries for debugging or auditing, subject to retention settings. A plain Parquet table does not provide Delta’s transactional history or equivalent write enforcement. Recreating a Delta table whenever columns change is unnecessary and can disrupt its history. Auto Loader can detect evolving source schemas, but an external table with schema inference alone does not provide the complete set of required Delta capabilities. Therefore, option D is correct.

================

Option D provides a practical balance between the 15-minute latency objective and compute cost. A scheduled Lakeflow Job running every 12 minutes leaves a small margin for startup and processing while avoiding an always-running stream. Trigger.AvailableNow processes all records available when the query starts in one or more incremental micro-batches and then terminates, allowing the associated compute to be released after the backlog is consumed. Running the job every minute would introduce unnecessary job-start and storage-check overhead. A continuous trigger would keep compute active even when little or no data is arriving. Triggering a separate run for every individual file can also create excessive runs and operational overhead. Therefore, the scheduled AvailableNow strategy in option D supports bounded latency while minimizing idle compute consumption.

================

Structured Streaming provides native Kafka integration and processes incoming records in repeated micro-batches, making it suitable for the required sub-minute clickstream latency. For the customer data, an incremental Lakeflow Connect ingestion pattern tracks source progress and processes only new or changed records during subsequent executions. This avoids repeatedly transferring and processing unchanged Snowflake rows. Option B performs a complete extraction every four hours, so the later MERGE does not eliminate unnecessary source reading and network transfer. Option C relies on a time filter that may miss late changes, updates without a suitable timestamp, or records affected by clock boundaries. Option D explicitly performs full refreshes and therefore violates the requirement to prevent reprocessing unchanged customer data. Consequently, option A is the only approach that satisfies both the streaming-latency and incremental-ingestion requirements.

================

Databricks intentionally stops existing streaming reads and writes after an external Delta table is converted to a Unity Catalog managed table. This protects data consistency because the table’s managed storage location and access path have changed. The documented recovery action is to restart the stream with the same configuration. The existing checkpoint can then resume processing from the last committed offset, and supported path-based access is redirected to the converted managed table. REFRESH TABLE invalidates cached metadata but does not repair a stopped Structured Streaming query or modify its checkpoint. Deleting the checkpoint would discard progress information and could cause unnecessary reprocessing or duplicate output. Additional permissions are not the indicated solution when the failure is specifically caused by managed-table conversion. Therefore, option A is correct.

================

The expected behavior when a batch of data containing data that violates the expectation is processed is that the job will fail. This is because the expectation clause has the ON VIOLATION FAIL UPDATE option, which means that if any record in the batch does not meet the expectation, the entire batch will be rejected and the job will fail. This option is useful for enforcing strict data quality rules and preventing invalid data from entering the target dataset.

Option A is not correct, as the ON VIOLATION FAIL UPDATE option does not drop the records that violate the expectation, but fails the entire batch. To drop the records that violate the expectation and record them as invalid in the event log, the ON VIOLATION DROP RECORD option should be used.

Option C is not correct, as the ON VIOLATION FAIL UPDATE option does not drop the records that violate the expectation, but fails the entire batch. To drop the records that violate the expectation and load them into a quarantine table, the ON VIOLATION QUARANTINE RECORD option should be used.

Option D is not correct, as the ON VIOLATION FAIL UPDATE option does not add the records that violate the expectation, but fails the entire batch. To add the records that violate the expectation and record them as invalid in the event log, the ON VIOLATION LOG RECORD option should be used.

Option E is not correct, as the ON VIOLATION FAIL UPDATE option does not add the records that violate the expectation, but fails the entire batch. To add the records that violate the expectation and flag them as invalid in a field added to the target dataset, the ON VIOLATION FLAG RECORD option should be used.

Delta Live Tables Expectations

[Databricks Data Engineer Professional Exam Guide]