DP-750: Delta Lake Table Operations Explained and with Real Exam Questions

When preparing for DP-750: Microsoft Certified: Azure Databricks Data Engineer Associate, you need to understand common Delta Lake and Spark table operations.

This article focuses on operations that appear frequently in DP-750 questions:

• INSERT INTO
• INSERT OVERWRITE
• table constraints
• NOT NULL
• CHECK
• INTERSECT
• append writes
• PySpark null filtering
• dropna
• fillna

The related questions in your DP-750 question bank are Q40, Q41, Q42, Q45, Q49–52, and Q53.

---

1. Why table operations matter in DP-750

Many DP-750 questions are not about advanced Spark optimization. They test whether you understand the basic behavior of table operations.

For example:

INSERT INTO table2
SELECT * FROM table1;

This preserves existing rows and adds new rows.

But:

INSERT OVERWRITE table2
SELECT * FROM table1;

replaces existing data.

Similarly, in PySpark:

df.dropna(subset=["order_amount"])

removes rows where order_amount is null, while:

df.fillna(0, subset=["order_amount"])

does not remove rows. It replaces null values with 0.

These details are simple, but they are very common exam traps.

---

2. INSERT INTO

INSERT INTO inserts new rows into a table. Azure Databricks documentation describes INSERT as a statement that inserts new rows into a table, using either value expressions or the result of a query.

Example:

INSERT INTO sales.schema1.table2
SELECT * FROM sales.schema1.table1;

This means:

Take rows from table1 and append them to table2.

It does not delete existing rows from table2.

Use INSERT INTO when the requirement says:

• preserve existing data
• add new rows
• append new records
• load current-year data into historical table
• daily file contains new records only
• minimize processing effort

For DP-750:

> Preserve existing data + add new rows = INSERT INTO

---

3. INSERT OVERWRITE

INSERT OVERWRITE replaces existing data in the target table or partition.

This is different from INSERT INTO.

Example:

INSERT OVERWRITE sales.schema1.table2
SELECT * FROM sales.schema1.table1;

This means:

Replace the existing data in table2 with the query result.

In DP-750, if the question says:

Preserve any existing data

then INSERT OVERWRITE is usually wrong.

Use INSERT OVERWRITE only when the requirement is to replace existing data.

---

4. CREATE TABLE AS SELECT vs CREATE OR REPLACE TABLE AS SELECT

These commands are also common traps.

CREATE TABLE sales.schema1.table2 AS
SELECT * FROM sales.schema1.table1;

creates a new table from query results.

CREATE OR REPLACE TABLE sales.schema1.table2 AS
SELECT * FROM sales.schema1.table1;

creates a table or replaces the existing table.

For DP-750:

|Requirement|Correct pattern| |---|---| |Add rows to an existing table|INSERT INTO| |Replace existing table definition and data|CREATE OR REPLACE TABLE AS SELECT| |Create a new table from query result|CREATE TABLE AS SELECT| |Preserve existing data|Avoid INSERT OVERWRITE and CREATE OR REPLACE|

If the target table already exists and you need to preserve existing data, INSERT INTO is the safest answer.

---

5. Table constraints

Azure Databricks supports table constraints for data quality. Microsoft documentation says constraints can define and enforce data quality rules, including NOT NULL and CHECK constraints. NOT NULL means values in specific columns cannot be null, and CHECK means a specified Boolean expression must be true for each row.

Common examples:

transaction_id STRING NOT NULL

CHECK (amount > 0)

Use constraints when the requirement says:

• table-level data quality enforcement
• invalid records must be rejected during writes
• a column must never be null
• a numeric value must be greater than zero
• enforce the rule when data is written

For DP-750:

> Reject invalid records at table write time = table constraints

---

6. NOT NULL constraint

A NOT NULL constraint prevents a column from containing null values.

Example:

CREATE TABLE Sales (
    transaction_id STRING NOT NULL,
    transaction_date DATE,
    amount DECIMAL(10,2)
);

If a write tries to insert a row where transaction_id is null, the write fails.

For DP-750:

transaction_id must never be null

means:

Add a NOT NULL constraint to transaction_id

---

7. CHECK constraint

A CHECK constraint requires a Boolean expression to be true.

Example:

ALTER TABLE Sales
ADD CONSTRAINT valid_amount CHECK (amount > 0);

If a write tries to insert a row where amount <= 0, the write fails.

For DP-750:

amount must be greater than 0

means:

Add a CHECK constraint to amount

Do not use a view or a WHERE clause if the requirement is to reject invalid records during table writes. A view only changes what users see when querying. It does not enforce the rule at write time.

---

8. INTERSECT

INTERSECT returns rows that appear in both query results. Azure Databricks documentation describes INTERSECT as one of the supported set operators used to combine query results. Both subqueries must have the same number of columns and compatible types.

Example:

SELECT Column1
FROM Table1
INTERSECT
SELECT Column2
FROM Table2;

This returns values that appear in both result sets.

Now look at this pattern:

SELECT Column1
FROM Table1
GROUP BY Column1
HAVING COUNT(*) > 1

INTERSECT

SELECT Column2
FROM Table2
GROUP BY Column2
HAVING COUNT(*) > 1;

The first query returns values that appear more than once in Table1.

The second query returns values that appear more than once in Table2.

INTERSECT returns only values that appear in both results.

So the final result is:

Values that appear more than once in both tables.

---

9. PySpark null filtering

In PySpark, null handling is a common exam trap.

To keep only rows where a column is not null, use:

df.filter(df.order_amount.isNotNull())

Spark documentation says Column.isNotNull() returns true if the current expression is not null.

You can also use:

df.dropna(subset=["order_amount"])

Spark documentation says DataFrame.dropna() returns a new DataFrame omitting rows with null or NaN values.

For DP-750:

> Exclude rows where order_amount is null = isNotNull() or dropna(subset=[...])

---

10. fillna does not remove rows

fillna replaces null values. It does not remove rows.

Example:

df.fillna(0, subset=["order_amount"])

If a row has:

order_amount = null

then after fillna, it becomes:

order_amount = 0

Spark documentation says DataFrame.fillna() returns a new DataFrame where null values are filled with a new value.

For DP-750:

> Requirement: exclude rows where order_amount is null > fillna(0) = wrong

Because replacing null with 0 is not the same as excluding the row.

---

11. Why df.order_amount != None is wrong

This is another common Spark trap.

In normal Python, you might write:

x != None

But Spark DataFrame columns are not normal Python values. They are distributed column expressions. Null comparison in Spark SQL follows SQL null semantics, and comparisons with null do not behave like normal Boolean comparisons.

So this expression is not the correct exam answer:

df.filter(df.order_amount != None)

Use:

df.filter(df.order_amount.isNotNull())

or:

df.dropna(subset=["order_amount"])

For DP-750:

> Null filtering in Spark = use isNotNull() or dropna()

---

12. Writing DataFrames to Delta

A common PySpark write pattern is:

df.write \
    .format("delta") \
    .mode("append") \
    .save(path)

This means:

|Code|Meaning| |---|---| |.format("delta")|Write data in Delta format| |.mode("append")|Add rows to existing data| |.save(path)|Save to the target path|

For DP-750, if the source is CSV and the target is an existing Delta table or Delta path, the pattern is:

spark.read.format("csv")
df.write.format("delta").mode("append")

Use append when the requirement says:

• each new CSV file must be added
• existing Delta table must be preserved
• new rows only
• no updates to existing rows

---

13. Append vs overwrite

This is the same idea as INSERT INTO vs INSERT OVERWRITE.

|SQL|PySpark write mode|Meaning| |---|---|---| |INSERT INTO|.mode("append")|Add new rows| |INSERT OVERWRITE|.mode("overwrite")|Replace existing data|

For DP-750:

Preserve existing data = append
Replace existing data = overwrite

---

14. DP-750 decision table for table operations

|Scenario in the question|Best answer pattern| |---|---| |Preserve existing data and add rows|INSERT INTO| |Daily file contains new records only|INSERT INTO or append mode| |Replace existing data|INSERT OVERWRITE or overwrite mode| |Create a new table from query result|CREATE TABLE AS SELECT| |Recreate existing table from query result|CREATE OR REPLACE TABLE AS SELECT| |transaction_id must never be null|NOT NULL constraint| |amount must be greater than 0|CHECK constraint| |Invalid records must be rejected on write|Table constraints| |Values appear more than once in both tables|GROUP BY + HAVING COUNT(*) > 1 + INTERSECT| |Exclude rows where column is null|isNotNull() or dropna(subset=[...])| |Replace nulls with zero|fillna(0)| |Add new CSV data to Delta path|.format("delta").mode("append")|

---

Real Exam Questions

Question 40

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a managed Delta table named Sales. Sales stores transaction data and contains the following columns:

• transaction_id, string
• transaction_date, date
• amount, decimal

You need to implement the following data quality requirements by using table-level data quality enforcement:

• amount must be greater than 0.
• transaction_id must never be null.
• Invalid records must be rejected when data is written to the Sales table.

What should you do?

A. Use a SELECT statement with WHERE conditions to validate the data before querying.

B. Create a view that filters out rows where transaction_id is null or amount is less than or equal to 0.

C. Add a NOT NULL constraint to transaction_id and a CHECK constraint to amount. ✅ Correct Answer

D. Configure row-level security, RLS, where transaction_id is null or amount is less than or equal to 0.

---

Question 41

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains two Delta tables named Table1 and Table2 of the same data type.

Table1 contains a column named Column1. Table2 contains a column named Column2.

You run the following query.

SELECT Column1
FROM Table1
GROUP BY Column1
HAVING COUNT(*) > 1

INTERSECT

SELECT Column2
FROM Table2
GROUP BY Column2
HAVING COUNT(*) > 1;

What occurs when you run the query?

A. Values appear in both tables more than once. ✅ Correct Answer

B. Values appear in either table more than once.

C. Values appear in Table2 but NOT Table1.

D. Values appear in Table1 more than once.

---

Question 42

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains two managed Delta tables named sales.schema1.table1 and sales.schema1.table2.

sales.schema1.table1 contains sales data from the current year. sales.schema1.table2 contains historical data.

You need to load all the rows from sales.schema1.table1 into sales.schema1.table2.

The solution must preserve any existing data in sales.schema1.table2 and minimize processing effort.

Which command should you run?

A. INSERT OVERWRITE sales.schema1.table2 SELECT * FROM sales.schema1.table1;

B. CREATE TABLE sales.schema1.table2 AS SELECT * FROM sales.schema1.table1;

C. INSERT INTO sales.schema1.table2 SELECT * FROM sales.schema1.table1; ✅ Correct Answer

D. CREATE OR REPLACE TABLE sales.schema1.table2 AS SELECT * FROM sales.schema1.table1;

---

Question 45

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a Delta table named Sales_orders.

Sales_orders stores historical sales data.

You receive a daily CSV file daily that contains new sales records only. The file does NOT contain updates to existing rows.

You need to load the daily data into Sales_orders. The solution must meet the following requirements:

• Preserve the existing data.

• Add only the new records.

• Minimize processing effort.

Which command should include in the loading strategy?

A. UPDATE

B. INSERT OVERWRITE

C. INSERT INTO ✅ Correct Answer

---

Question 49

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a Delta table named Orders.

You load the Orders table into an Apache Spark DataFrame named df.

You need to create a DataFrame that excludes rows where the order amount is null.

Solution: You run the following expression.

df.fillna(0, subset=["order_amount"])

Does this meet the goal?

A. Yes

B. No ✅ Correct Answer

---

Question 50

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a Delta table named Orders.

You load the Orders table into an Apache Spark DataFrame named df.

You need to create a DataFrame that excludes rows where the order amount is null.

Solution: You run the following expression.

df.filter(df.order_amount.isNotNull())

Does this meet the goal?

A. Yes ✅ Correct Answer

B. No

---

Question 51

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a Delta table named Orders.

You load the Orders table into an Apache Spark DataFrame named df.

You need to create a DataFrame that excludes rows where the order amount is null.

Solution: You run the following expression.

df.dropna(subset=["order_amount"])

Does this meet the goal?

A. Yes ✅ Correct Answer

B. No

---

Question 52

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a Delta table named Orders.

You load the Orders table into an Apache Spark DataFrame named df.

You need to create a DataFrame that excludes rows where the order amount is null.

Solution: You run the following expression.

df.filter(df.order_amount != None)

Does this meet the goal?

A. Yes

B. No ✅ Correct Answer

---

Question 53

You have an Azure Databricks workspace.

You have an Azure Data Lake Storage Gen2 account named account1.

You need to use a Databricks notebook to read CSV files from account1. The data from each new CSV file must be added to an existing Delta table named customer.

How should you complete the PySpark code segment?

basePath = "abfss://data@storage1.dfs.core.windows.net/"

df = (
    spark.read
        .format("csv")
        .option("header", "true")
        .load(basePath)
)

df.write
    .format("delta")
    .mode("append")
    .save(basePath + "delta/customers")

✅ Correct Answer

---

Key takeaways

For DP-750, Delta Lake table operation questions usually test basic but important behavior.

Remember these patterns:

• INSERT INTO adds rows and preserves existing data.

• INSERT OVERWRITE replaces existing data.

• .mode("append") adds data.

• .mode("overwrite") replaces data.

• NOT NULL prevents null values in a column.

• CHECK enforces a Boolean condition such as amount > 0.

• Views and WHERE clauses do not enforce table-level write rules.

• INTERSECT returns values that appear in both query results.

• fillna() replaces nulls; it does not remove rows.

• isNotNull() keeps only non-null rows.

• dropna(subset=[...]) removes rows with nulls in the specified column.

• df.order_amount != None is not the correct Spark null-filtering pattern.

If you understand the difference between append vs overwrite, query filtering vs write enforcement, and null replacement vs null exclusion, these DP-750 table operation questions become much easier.