DP-750 prepare and process data explained
Prepare and process data is the largest DP-750 domain, at 30–35%. It covers four jobs: designing how data is modelled in Unity Catalog, ingesting it, cleansing and transforming it, and enforcing quality. Expect many short scenarios where one detail (batch or streaming, history or current state, upsert or append) decides the answer.
Design and implement data modeling
Formats, loading and tables
Choose a table format from Parquet, Delta, CSV, JSON or Iceberg. For lakehouse tables Delta is the default because it adds transactions, schema enforcement, time travel and MERGE. CSV and JSON are source formats you ingest from, not formats you usually keep. Choose batch loading for periodic, bounded data and streaming when data arrives continuously and latency matters. Choose managed tables unless files must live somewhere you control.
Partitioning and clustering
| Technique | Use when |
|---|---|
| Partitioning | Low-cardinality column used in most filters, and large partitions |
| Liquid clustering | Query patterns change, high-cardinality filters, or you want to avoid the partition-versus-skew trade-off |
| Z-ordering | Legacy approach to co-locate data inside files on an existing table |
Over-partitioning on a high-cardinality column creates many small files and slows queries. The exam steers you towards liquid clustering for new tables with evolving access patterns.
History and granularity
A slowly changing dimension type 1 overwrites old values, type 2 keeps a row per version with validity dates and a current flag, and type 3 keeps a limited previous-value column. A temporal (history) table records every change over time, which supports auditing and point-in-time reporting. Granularity is the level of one row, such as one order line versus one order, and it must match the questions the table will answer.
Ingest data
| Route | Typical use |
|---|---|
| Lakeflow Connect | Managed ingestion from supported enterprise sources, batch or streaming |
| Notebooks | Custom logic, APIs and unusual formats |
| CTAS, CREATE OR REPLACE TABLE | Create or fully replace a table from a query |
| COPY INTO | Idempotent loading of new files from a folder, SQL only |
| CDC feed | Applying inserts, updates and deletes from a source |
| Structured Streaming | Continuous processing with checkpoints |
| Event Hubs | Streaming source for event data, read via its Kafka-compatible endpoint |
| Lakeflow Spark Declarative Pipelines with Auto Loader | Incremental file ingestion with schema handling |
Azure Data Factory is also a valid ingestion tool in the outline: pick it when you need to orchestrate copies from many Azure and external sources. Auto Loader scales better than COPY INTO when millions of files arrive, and it keeps state of what has been processed.
Cleanse, transform and load
- Profile data with summary statistics and distributions before deciding what to fix.
- Data types: choose precise types, such as DECIMAL for money and TIMESTAMP for times, instead of strings.
- Duplicates, missing and null values: drop duplicates on a key, fill with defaults or filter, depending on the business rule.
- Set and join operators: join, union, intersect and except do different things; INTERSECT keeps rows present in both, EXCEPT keeps rows only in the first.
- Reshaping: denormalise for reads, pivot rows to columns, unpivot columns to rows.
- Loading: append adds rows, insert overwrite replaces, and MERGE upserts by matching on a key, which is the answer for CDC and SCD type 1 and 2 patterns.
Data quality constraints
Delta checks the schema on write, so a mismatched write fails instead of silently corrupting the table; that is schema enforcement. Schema drift is the source adding or changing columns, which you handle by enabling schema evolution deliberately or capturing unexpected data in a rescued data column. Constraints include NOT NULL and CHECK. In Lakeflow Spark Declarative Pipelines, expectations attach a rule with an action: record violations only, drop the rows, or fail the update.
Sample questions
Question 1. A customer dimension must show an address as it was when each past order was placed, while still identifying the current address. Which design fits?
- A. SCD type 2 with validity dates and a current flag
- B. SCD type 1 that overwrites the address
- C. Append a daily snapshot without any date columns
- D. Truncate and reload the table each day
Show answer
Answer: A
A slowly changing dimension type 2 keeps one row per version with start and end dates and a current flag, so history and current state are both available. Type 1 overwrites and loses the old address. Appending the latest snapshot without validity columns cannot tell which row applied at order time. Truncating the table daily removes history.
Want more questions like this? Full DP-750 practice tests →
Question 2. A CDC feed delivers inserts, updates and deletes keyed on customer_id for a Delta target table. Which operation applies the changes in one statement?
- A. Append the changes to the target
- B. CREATE OR REPLACE TABLE from the feed
- C. COPY INTO the target
- D. MERGE INTO the target on customer_id
Show answer
Answer: D
MERGE INTO matches source rows to target rows on a key and can insert, update or delete in one atomic statement. Append only adds rows and would duplicate updated customers. CREATE OR REPLACE TABLE rebuilds the whole table and is wasteful. COPY INTO loads new files but does not match keys for updates and deletes.
Want more questions like this? Full DP-750 practice tests →
Question 3. A new table is filtered by customer_id, which has millions of distinct values, and analysts later start filtering by order_date as well. The team does not want to redesign partitions again. What should you choose?
- A. Partition by customer_id
- B. Partition by customer_id and order_date
- C. Liquid clustering on customer_id, later adding order_date
- D. Store the data as CSV with no layout
Show answer
Answer: C
Liquid clustering handles high-cardinality columns and lets you change clustering keys without rewriting the layout design. Partitioning by customer_id would create millions of tiny partitions. Z-ordering is an older approach that needs OPTIMIZE and is not the flexible option. A single unpartitioned CSV table gives no data skipping.
Want more questions like this? Full DP-750 practice tests →
What to practise
Load the same folder three ways: COPY INTO, a streaming read with Auto Loader, and a notebook batch read, and compare what happens on the second run. Write a MERGE from a change feed with inserts, updates and deletes. Build a SCD type 2 table by hand. Create a pipeline with expectations using each violation action. Then continue with the pipelines guide.