Certified Data Engineer Professional Table Practice Questions

A junior data engineer is migrating a workload from a relational database system to the Databricks Lakehouse. The source system uses a star schema, leveraging foreign key constrains and multi-table inserts to validate records on write. Which consideration will impact the decisions made by the engineer while migrating this workload?

• AA. All Delta Lake transactions are ACID compliance against a single table, and Databricks does not enforce foreign key constraints.
• BB. Databricks only allows foreign key constraints on hashed identifiers, which avoid collisions in highly-parallel writes.
• CC. Foreign keys must reference a primary key field; multi-table inserts must leverage Delta Lake's upsert functionality.
• DD. Committing to multiple tables simultaneously requires taking out multiple table locks and can lead to a state of deadlock.

The data governance team has instituted a requirement that all tables containing Personal Identifiable Information (PH) must be clearly annotated. This includes adding column comments, table comments, and setting the custom table property "contains_pii" = true. The following SQL DDL statement is executed to create a new table: Which command allows manual confirmation that these three requirements have been met?

• AA. DESCRIBE EXTENDED dev.pii test
• BB. DESCRIBE DETAIL dev.pii test
• CC. SHOW TBLPROPERTIES dev.pii test
• DD. DESCRIBE HISTORY dev.pii test

An upstream system is emitting change data capture (CDC) logs that are being written to a cloud object storage directory. Each record in the log indicates the change type (insert, update, or delete) and the values for each field after the change. The source table has a primary key identified by the field pk_id. For auditing purposes, the data governance team wishes to maintain a full record of all values that have ever been valid in the source system. For analytical purposes, only the most recent value for each record needs to be recorded. The Databricks job to ingest these records occurs once per hour, but each individual record may have changed multiple times over the course of an hour. Which solution meets these requirements?

• AA. Create a separate history table for each pk_id resolve the current state of the table by running a union all filtering the history tables for the most recent state.
• BB. Use merge into to insert, update, or delete the most recent entry for each pk_id into abronze table, then propagate all changes throughout the system.
• CC. Iterate through an ordered set of changes to the table, applying each in turn; rely on Delta Lake's versioning ability to create an audit log.
• DD. Use Delta Lake's change data feed to automatically process CDC data from an external system, propagating all changes to all dependent tables in the

The view updates represents an incremental batch of all newly ingested data to be inserted or updated in the customers table. The following logic is used to process these records. Which statement describes this implementation?

• AA. The customers table is implemented as a Type 3 table; old values are maintained as a new column alongside the current value.
• BB. The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted.
• CC. The customers table is implemented as a Type 0 table; all writes are append only with no changes to existing values.
• DD. The customers table is implemented as a Type 1 table; old values are overwritten by new values and no history is maintained.