Jin

Posted on Aug 5 • Originally published at blog.luca-liu.com

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

#azure #databricks #certification #tutorial

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.

Explore more

Jin

Hello there! 👋 I'm Jin, a Business Intelligence Developer with a passion for all things data. Proficient in Python, SQL, Power BI, Tableau

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

Connect with me on LinkedIn

Connect with me on X

DEV Community

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

1. Why table operations matter in DP-750

2. `INSERT INTO`

3. `INSERT OVERWRITE`

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

5. Table constraints

6. `NOT NULL` constraint

7. `CHECK` constraint

8. `INTERSECT`

9. PySpark null filtering

10. `fillna` does not remove rows

11. Why `df.order_amount != None` is wrong

12. Writing DataFrames to Delta

13. Append vs overwrite

14. DP-750 decision table for table operations

Real Exam Questions

Question 40

Question 41

Question 42

Question 45

Question 49

Question 50

Question 51

Question 52

Question 53

Key takeaways

Explore more

Jin

Top comments (0)

1. Why table operations matter in DP-750

2. INSERT INTO

3. INSERT OVERWRITE

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

5. Table constraints

6. NOT NULL constraint

7. CHECK constraint

8. INTERSECT

9. PySpark null filtering

10. fillna does not remove rows

11. Why df.order_amount != None is wrong

12. Writing DataFrames to Delta

13. Append vs overwrite

14. DP-750 decision table for table operations

Real Exam Questions

Question 40

Question 41

Question 42

Question 45

Question 49

Question 50

Question 51

Question 52

Question 53

Key takeaways

Explore more

JinFollow

2. `INSERT INTO`

3. `INSERT OVERWRITE`

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

6. `NOT NULL` constraint

7. `CHECK` constraint

8. `INTERSECT`

10. `fillna` does not remove rows

11. Why `df.order_amount != None` is wrong

Jin