Q01 - Question
You need a dataset that lists customers who never placed an order. The result must contain only columns from the customer table. You use the customer and orders tables, which share a customer_id column. Which join should you use?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. LEFT JOIN from customer to orders
- B. LEFT ANTI JOIN from customer to orders
- C. LEFT SEMI JOIN from customer to orders
- D. INNER JOIN from customer to orders
B is correct.
Explanation: An anti join returns left table rows that have no match in the right table. It does not include columns from the right table. This meets the requirement for customers with no orders and customer-only columns.
A is incorrect: A left join returns all left table rows, including customers that have orders. It adds NULLs for the right table only when no match exists.
C is incorrect: A semi join returns left table rows that have matches in the right table. This returns customers who placed orders, which is the opposite of the requirement.
D is incorrect: An inner join returns only matching rows from both tables. It excludes customers without orders.
Q02 - Question
You create a Unity Catalog table for invoices. The invoice_amount column stores currency values that require exact precision. The event_time column records when invoices were posted by users in several time zones, and the values must be ordered consistently across time zones. Which column types should you use?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. invoice_amount DECIMAL(10,2) and event_time TIMESTAMP
- B. invoice_amount DOUBLE and event_time TIMESTAMP
- C. invoice_amount DECIMAL(10,2) and event_time TIMESTAMP_NTZ
- D. invoice_amount DOUBLE and event_time TIMESTAMP_NTZ
A is correct.
Explanation: DECIMAL(p,s) provides exact precision for financial data. TIMESTAMP converts values to UTC for storage and applies the session timezone when values are read. This behavior gives consistent ordering across time zones.
B is incorrect: DOUBLE uses binary representation and can introduce rounding errors with base-10 values such as currency.
C is incorrect: TIMESTAMP_NTZ does not apply timezone conversion. Use it when a time must remain as entered, not when you track events across time zones.
D is incorrect: DOUBLE can introduce rounding errors with currency, and TIMESTAMP_NTZ does not apply timezone conversion.
Q03 - Question
A data engineer must ingest a MySQL table into Unity Catalog on a schedule. CDC and binlog access are not available on the source database. The table has an updated_at timestamp column that always increases when a row is inserted or changed. The team does not want to deploy an ingestion gateway or staging storage. Which Lakeflow Connect approach should the engineer use?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. Use a database connector that uses CDC and an ingestion gateway.
- B. Use a query-based connector and configure updated_at as the cursor column.
- C. Use a SaaS connector that extracts data through the source API.
- D. Use COPY INTO to load the table from a Unity Catalog volume.
B is correct.
Explanation: Query-based connectors query the source database directly on a schedule and do not require CDC configuration. They use a cursor column, which is a monotonically increasing timestamp or integer column, to find new or updated rows since the last run. No gateway or staging storage is required, so updated_at meets the stated constraints.
A is incorrect: Database connectors that use CDC create an ingestion gateway that extracts changes from the source binlog. CDC and binlog access are not available in this scenario.
C is incorrect: SaaS connectors are for enterprise SaaS applications such as Salesforce and Workday. They extract data through the source API and do not apply to a MySQL table.
D is incorrect: COPY INTO loads files from a storage location into an existing Delta table. It does not read rows from a source database.
Q04 - Question
You manage all tables in a Unity Catalog schema. You need automated freshness and completeness checks across every table without configuring each table. You also need distribution, null percentage, and drift metrics for one critical table. Which approach meets both requirements?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. Configure data profiling individually on every table in the schema.
- B. Enable anomaly detection at the schema level only.
- C. Enable anomaly detection at the schema level and configure data profiling on the critical table.
- D. Run ANALYZE TABLE with COMPUTE STATISTICS FOR ALL COLUMNS on every table.
C is correct.
Explanation: Anomaly detection is enabled once at the schema level. It evaluates freshness and completeness for all tables in the schema without per-table configuration. Data profiling is configured per table. It tracks distributions, null percentages, and drift metrics, so you use it for the critical table.
A is incorrect: Data profiling is configured individually for each table, so this approach requires per-table configuration for every table in the schema.
B is incorrect: Anomaly detection evaluates freshness and completeness. It does not provide the per-table distribution, null percentage, and drift metrics that data profiling provides.
D is incorrect: ANALYZE TABLE collects statistics such as row counts, null counts, and distinct counts. It does not create the drift metrics table that data profiling creates.
Q05 - Question
A Lakeflow pipeline loads regional order data into the orders_us streaming table. It uses two explicit flows, orders_west_flow and orders_east_flow, and both flows have been running for several weeks. A team member proposes renaming orders_west_flow to match a new naming standard. What is the documented effect of renaming the flow?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. The renamed flow keeps its existing checkpoint, because flow names do not affect checkpoints.
- B. The pipeline drops the orders_us table and rebuilds it from both sources.
- C. The orders_east_flow flow also restarts from the beginning, because both flows share one checkpoint.
- D. The renamed flow does not keep its checkpoint and starts processing from the beginning as a new flow.
D is correct.
Explanation: Flow names identify streaming checkpoints. If you rename a flow, the checkpoint does not carry over, and the renamed flow starts processing from the beginning as a new flow. Plan for this behavior before you rename a flow that has been running.
A is incorrect: Flow names are used to identify checkpoints, so a rename does affect checkpoint continuity.
B is incorrect: The documented effect of a rename is on the checkpoint of the renamed flow. It does not describe a table drop or a rebuild from both sources.
C is incorrect: Each flow is identified by its own name, and orders_east_flow keeps its name. The documented effect applies to the renamed flow.
Q06 - Question
A CDC feed for an employees table sends partial update records that contain only the changed columns. All other columns arrive as null. The destination table is a streaming table that must keep historical versions of each employee row for changes to columns such as name and department. A change to last_login must update the existing row in place and must not create a new history version. Which TWO configurations should the data engineer use?
Domain: Prepare and process data (30–35%) Type: Multi-select
- A. Set the flow to STORED AS SCD TYPE 1.
- B. Use IGNORE NULL UPDATES (SQL) or ignore_null_updates (Python) in the AUTO CDC flow.
- C. Add last_login to the COLUMNS * EXCEPT list of the flow.
- D. Use STORED AS SCD TYPE 2 with TRACK HISTORY ON * EXCEPT (last_login).
B and D are correct.
Explanation: With partial update records, ignoring null updates leaves existing column values intact instead of overwriting them with nulls. SCD Type 2 preserves historical versions, and TRACK HISTORY ON * EXCEPT (last_login) updates the existing record in place for last_login changes. Changes to other columns, such as name or department, create new historical versions.
A is incorrect: SCD Type 1 keeps only the current version of each record, so it does not preserve history for name and department.
C is incorrect: The COLUMNS * EXCEPT clause excludes columns from the destination table, such as the operation and sequence columns. Excluding last_login would remove it from the table instead of limiting history tracking.
Q07 - Question
A change data capture feed contains updated customer records and new customer records. You must load the feed into the customers table in Unity Catalog. Existing customers must be updated and new customers must be inserted. The feed can contain more than one row for the same customer_id. Which two actions should you take?
Domain: Prepare and process data (30–35%) Type: Multi select
- A. Deduplicate the source data on customer_id before the load.
- B. Use INSERT INTO to append the feed to the customers table.
- C. Use MERGE INTO with a match on customer_id, update matched rows, and insert unmatched rows.
- D. Use INSERT OVERWRITE to replace the contents of the customers table with the feed.
A and C are correct.
Explanation: MERGE INTO matches source rows to target rows. It updates existing records and inserts new ones in a single statement. MERGE requires a unique match between source and target rows. If multiple source rows match the same target row, the operation fails, so you deduplicate the source first.
B is incorrect: INSERT INTO appends rows and does not update existing records. It also does not check for duplicates, so changed customers would be added as additional rows.
D is incorrect: INSERT OVERWRITE truncates the target table or partition before it inserts rows. It replaces existing data instead of synchronizing changes.
Q08 - Question
A table named daily_metrics is rebuilt every night from a 30-day query over a transactions table. Downstream users already have granted privileges on the table, and the table has row filters and column masks. The nightly job must replace all rows and keep the privileges, row filters, and column masks. Which SQL approach should the data engineer use?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. Run CREATE TABLE IF NOT EXISTS ... AS SELECT each night.
- B. Run COPY INTO to load the query results each night.
- C. Run CREATE OR REPLACE TABLE ... AS SELECT each night.
- D. Drop the table, and then run CREATE TABLE ... AS SELECT each night.
C is correct.
Explanation: CREATE OR REPLACE TABLE fully replaces the table contents. It preserves table history, granted privileges, and any row filters or column masks. Downstream users continue to access the table without reconfiguration.
A is incorrect: The IF NOT EXISTS clause skips execution entirely when the table already exists. The table is not refreshed after the first run.
B is incorrect: COPY INTO appends files to an existing table and skips files that were already loaded. It does not replace the table contents, and its source is files rather than a query.
D is incorrect: Dropping and recreating the table is not the documented way to keep metadata and permissions. CREATE OR REPLACE is documented to preserve them.
Q09 - Question
A streaming job reads from Azure Event Hubs through the Kafka-compatible endpoint. The job parses JSON by casting the value column to a string. The team changes the source to the native Event Hubs connector by setting the format to eventhubs and using eventhubs.connectionString. The parsing step now fails because the value column is missing. What should the data engineer change in the parsing logic?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. Cast the body column to a string, because the native connector stores the event payload in body.
- B. Keep casting the value column, and set startingOffsets to earliest.
- C. Keep casting the value column, and set kafka.sasl.mechanism to PLAIN on the eventhubs format.
- D. Keep casting the value column, and add trigger(availableNow=True) to the write stream.
A is correct.
Explanation: The native Event Hubs connector returns a different schema than the Kafka-compatible interface. The event payload is in the body column instead of value. Use CAST(body AS STRING) before parsing the JSON content.
B is incorrect: The startingOffsets option controls where a new Kafka query begins reading. It does not change the columns that the source returns.
C is incorrect: SASL settings configure authentication for the Kafka-compatible interface. They do not rename columns, and the native connector uses its own connection string option.
D is incorrect: A trigger controls when the stream processes data. It does not change the source schema, so the value column is still missing.
Q10 - Question
The sales.customers table contains multiple rows for some customer_id values. The rows differ in name and updated_at. You must keep only the row with the latest updated_at for each customer_id by using SQL. Which approach should you use?
Domain: Prepare and process data (30–35%) Type: Single choice
- A. Run dropDuplicates on customer_id in PySpark.
- B. Run distinct() on the full DataFrame.
- C. Use GROUP BY customer_id with HAVING COUNT(*) > 1.
- D. Use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) with QUALIFY to keep row number 1.
D is correct.
Explanation: ROW_NUMBER() with QUALIFY lets you control which duplicate to keep. Partitioning by customer_id and ordering by updated_at in descending order keeps only the most recently updated record for each customer.
A is incorrect: dropDuplicates keeps only the first occurrence. It does not let you choose the row with the latest updated_at.
B is incorrect: distinct() removes duplicates across all columns. Rows that differ in name or updated_at are not identical, so they remain.
C is incorrect: GROUP BY with HAVING identifies values that appear more than once. It does not remove duplicate rows.