# Understanding INSERT OVERWRITE and Time Travel in Delta Lake

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

```pgsql
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**

```pgsql
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

```sql
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:

```sql
DESCRIBE HISTORY customer_sales;
```

To retrieve previous versions:

```sql
-- 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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1739511279885/4b384870-5dd2-4a18-8baf-76219068866a.png align="center")