Delta Table Tutorial in Azure Synapse: Create, Insert, Update, Delete & Merge with Spark SQL | Azure Synapse Analytics Tutorial

Delta Table Tutorial in Azure Synapse: Create, Insert, Update, Delete & Merge with Spark SQL

📘 Overview

Delta Lake is a powerful open-source storage layer that brings ACID transactions to Apache Spark. In Azure Synapse, Delta Tables enable you to perform CREATE, INSERT, UPDATE, DELETE, and MERGE operations directly within your Spark SQL workflows.

🛠️ 1. Create a Delta Table

%%spark
CREATE TABLE lake.delta_customers (
  id INT,
  name STRING,
  country STRING
)
USING DELTA
LOCATION 'abfss://data@yourstorageaccount.dfs.core.windows.net/delta/customers'

➕ 2. Insert Records

%%spark
INSERT INTO lake.delta_customers VALUES
  (1, 'Alice', 'USA'),
  (2, 'Bob', 'Canada');

✏️ 3. Update Records

%%spark
UPDATE lake.delta_customers
SET country = 'United States'
WHERE name = 'Alice';

❌ 4. Delete Records

%%spark
DELETE FROM lake.delta_customers
WHERE id = 2;

🔁 5. Merge (Upsert) Records

%%spark
MERGE INTO lake.delta_customers AS target
USING (
  SELECT 2 AS id, 'Robert' AS name, 'UK' AS country
) AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

🔍 6. Query the Delta Table

%%sql
SELECT * FROM lake.delta_customers;

📌 Why Use Delta in Synapse?

• ACID Transactions
• Time Travel and Versioning
• Efficient Upserts (MERGE)
• Seamless integration with Spark SQL

📈 Use Cases

• Change Data Capture (CDC) pipelines
• Slowly Changing Dimensions (SCD)
• Transactional lakehouse architectures

📺 Watch the Full Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.