How to Implement SCD Type 1 Overwrite in Python with SQLite

import sqlite3

# Simulate a dimension table with SCD Type 1 (overwrite)
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# Create dimension table
cursor.execute("""
    CREATE TABLE customer_dim (
        customer_id INTEGER PRIMARY KEY,
        customer_name TEXT,
        city TEXT,
        updated_at TEXT
    )
""")

# Initial load
initial_data = [
    (1, "Alice Smith", "New York", "2024-01-01"),
    (2, "Bob Johnson", "Chicago", "2024-01-01"),
    (3, "Carol White", "Boston", "2024-01-01"),
]
cursor.executemany(
    "INSERT INTO customer_dim VALUES (?, ?, ?, ?)", initial_data
)

# Incoming source data — Alice moved and Bob's name changed
incoming_data = [
    (1, "Alice Smith", "Los Angeles", "2024-06-01"),
    (2, "Robert Johnson", "Chicago", "2024-06-01"),
]

# SCD Type 1: overwrite existing rows with new values
for customer_id, name, city, updated_at in incoming_data:
    cursor.execute(
        """
        UPDATE customer_dim
        SET customer_name = ?, city = ?, updated_at = ?
        WHERE customer_id = ?
        """,
        (name, city, updated_at, customer_id)
    )

# Show result
cursor.execute("SELECT * FROM customer_dim ORDER BY customer_id")
for row in cursor.fetchall():
    print(row)

conn.close()