• INSERT OVERWRITE replaces the existing data in the table but preserves historical versions of the data.

• Unlike CREATE OR REPLACE TABLE (CRAS), which modifies the schema, INSERT OVERWRITE generally only replaces the data.

Dataset Example

Let's assume we have two tables:

1. customers: Contains customer details.

2. sales: Stores sales transactions.

Step 1: Creating the Customers and Sales Tables

CREATE TABLE customers (
    customer_id INT,
    name STRING,
    city STRING
) USING DELTA;

CREATE TABLE sales (
    sales_id INT,
    customer_id INT,
    amount DECIMAL(10,2),
    sale_date DATE
) USING DELTA;

Step 2: Inserting Initial Data

INSERT INTO customers VALUES (1, 'Alice', 'New York');
INSERT INTO customers VALUES (2, 'Bob', 'Los Angeles');
INSERT INTO customers VALUES (3, 'Charlie', 'Chicago');

INSERT INTO sales VALUES (101, 1, 200.50, '2024-01-10');
INSERT INTO sales VALUES (102, 2, 350.75, '2024-01-15');
INSERT INTO sales VALUES (103, 3, 500.00, '2024-01-20');

Step 3: Using INSERT OVERWRITE

INSERT OVERWRITE customer_sales
SELECT c.customer_id, c.name, s.sales_id, s.amount, s.sale_date
FROM customers c
INNER JOIN sales s ON s.customer_id = c.customer_id;

• The first time this query runs, it creates the customer_sales table with the latest sales data.

• The second time it runs, it overwrites the existing data while maintaining historical versions

Time Travel in Delta Lake

Since Delta Lake stores all historical changes, you can retrieve old versions of the table.

Step 4: Querying Previous Versions

To see all historical changes:

DESCRIBE HISTORY customer_sales;

To retrieve previous versions:

-- Fetch data from version 1 (initial data)
SELECT * FROM customer_sales VERSION AS OF 1;

-- Fetch data from version 2 (latest overwritten data)
SELECT * FROM customer_sales VERSION AS OF 2;

Key Differences: INSERT OVERWRITE vs. CREATE OR REPLACE TABLE