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 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 JinFollow 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