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:
customers: Contains customer details.
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

About the author
I am a Tech Enthusiast having 13+ years of experience in 𝐈𝐓 as a 𝐂𝐨𝐧𝐬𝐮𝐥𝐭𝐚𝐧𝐭, 𝐂𝐨𝐫𝐩𝐨𝐫𝐚𝐭𝐞 𝐓𝐫𝐚𝐢𝐧𝐞𝐫, 𝐌𝐞𝐧𝐭𝐨𝐫, with 12+ years in training and mentoring in 𝐒𝐨𝐟𝐭𝐰𝐚𝐫𝐞 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐓𝐞𝐬𝐭 𝐀𝐮𝐭𝐨𝐦𝐚𝐭𝐢𝐨𝐧 𝐚𝐧𝐝 𝐃𝐚𝐭𝐚 𝐒𝐜𝐢𝐞𝐧𝐜𝐞. I have 𝒕𝒓𝒂𝒊𝒏𝒆𝒅 𝒎𝒐𝒓𝒆 𝒕𝒉𝒂𝒏 10,000+ 𝑰𝑻 𝑷𝒓𝒐𝒇𝒆𝒔𝒔𝒊𝒐𝒏𝒂𝒍𝒔 and 𝒄𝒐𝒏𝒅𝒖𝒄𝒕𝒆𝒅 𝒎𝒐𝒓𝒆 𝒕𝒉𝒂𝒏 500+ 𝒕𝒓𝒂𝒊𝒏𝒊𝒏𝒈 𝒔𝒆𝒔𝒔𝒊𝒐𝒏𝒔 in the areas of 𝐒𝐨𝐟𝐭𝐰𝐚𝐫𝐞 𝐃𝐞𝐯𝐞𝐥𝐨𝐩𝐦𝐞𝐧𝐭, 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠, 𝐂𝐥𝐨𝐮𝐝, 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐢𝐬, 𝐃𝐚𝐭𝐚 𝐕𝐢𝐬𝐮𝐚𝐥𝐢𝐳𝐚𝐭𝐢𝐨𝐧𝐬, 𝐀𝐫𝐭𝐢𝐟𝐢𝐜𝐢𝐚𝐥 𝐈𝐧𝐭𝐞𝐥𝐥𝐢𝐠𝐞𝐧𝐜𝐞 𝐚𝐧𝐝 𝐌𝐚𝐜𝐡𝐢𝐧𝐞 𝐋𝐞𝐚𝐫𝐧𝐢𝐧𝐠. I am interested in 𝐰𝐫𝐢𝐭𝐢𝐧𝐠 𝐛𝐥𝐨𝐠𝐬, 𝐬𝐡𝐚𝐫𝐢𝐧𝐠 𝐭𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐤𝐧𝐨𝐰𝐥𝐞𝐝𝐠𝐞, 𝐬𝐨𝐥𝐯𝐢𝐧𝐠 𝐭𝐞𝐜𝐡𝐧𝐢𝐜𝐚𝐥 𝐢𝐬𝐬𝐮𝐞𝐬, 𝐫𝐞𝐚𝐝𝐢𝐧𝐠 𝐚𝐧𝐝 𝐥𝐞𝐚𝐫𝐧𝐢𝐧𝐠 new subjects.
Comments
Join the discussionNo comments yet. Be the first to comment.


