MERGE INTO target_table
USING source_table
ON match_condition
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2
WHEN NOT MATCHED THEN
    INSERT (column1, column2) VALUES (value1, value2)
[WHEN NOT MATCHED BY SOURCE THEN
    DELETE];  -- Snowflake only

-- Update existing customers and insert new ones
MERGE INTO customers target
USING customer_updates source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
    UPDATE SET
        target.name = source.name,
        target.email = source.email,
        target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, created_at)
    VALUES (source.customer_id, source.name, source.email, CURRENT_TIMESTAMP)

-- Update only if source is newer, insert new records
MERGE INTO product_inventory target
USING product_updates source
ON target.product_id = source.product_id
WHEN MATCHED AND source.last_updated > target.last_updated THEN
    UPDATE SET
        target.quantity = source.quantity,
        target.price = source.price,
        target.last_updated = source.last_updated
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, quantity, price, last_updated)
    VALUES (source.product_id, source.product_name, source.quantity, source.price, source.last_updated)

-- Update customer summary from orders
MERGE INTO customer_summary target
USING (
    SELECT 
        customer_id,
        COUNT(*) as total_orders,
        SUM(total) as lifetime_value,
        AVG(total) as avg_order_value,
        MAX(order_date) as last_order_date
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
) source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
    UPDATE SET
        target.total_orders = target.total_orders + source.total_orders,
        target.lifetime_value = target.lifetime_value + source.lifetime_value,
        target.last_order_date = source.last_order_date,
        target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (customer_id, total_orders, lifetime_value, last_order_date, created_at)
    VALUES (source.customer_id, source.total_orders, source.lifetime_value, source.last_order_date, CURRENT_TIMESTAMP)

-- Sync product catalog - update, insert, and delete discontinued products
MERGE INTO product_catalog target
USING external_product_feed source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET
        target.product_name = source.product_name,
        target.price = source.price,
        target.status = 'Active',
        target.last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price, status, created_at)
    VALUES (source.product_id, source.product_name, source.price, 'Active', CURRENT_TIMESTAMP)
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET
        target.status = 'Discontinued',
        target.discontinued_at = CURRENT_TIMESTAMP

-- Two-step process for BigQuery/Databricks
-- Step 1: Merge
MERGE INTO product_catalog target
USING external_product_feed source
ON target.product_id = source.product_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

-- Step 2: Mark discontinued products
UPDATE product_catalog
SET status = 'Discontinued',
    discontinued_at = CURRENT_TIMESTAMP
WHERE product_id NOT IN (SELECT product_id FROM external_product_feed)
  AND status = 'Active'

-- SCD Type 2: Keep history of changes
MERGE INTO customer_dimension target
USING customer_updates source
ON target.customer_id = source.customer_id AND target.is_current = TRUE
WHEN MATCHED AND (
    target.name != source.name OR
    target.email != source.email OR
    target.address != source.address
) THEN
    UPDATE SET
        target.is_current = FALSE,
        target.end_date = CURRENT_DATE,
        target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, address, is_current, start_date, end_date, created_at)
    VALUES (source.customer_id, source.name, source.email, source.address, TRUE, CURRENT_DATE, NULL, CURRENT_TIMESTAMP);

-- Insert new versions of changed records
INSERT INTO customer_dimension (customer_id, name, email, address, is_current, start_date, end_date, created_at)
SELECT 
    source.customer_id,
    source.name,
    source.email,
    source.address,
    TRUE as is_current,
    CURRENT_DATE as start_date,
    NULL as end_date,
    CURRENT_TIMESTAMP as created_at
FROM customer_updates source
INNER JOIN customer_dimension target
    ON target.customer_id = source.customer_id 
    AND target.is_current = FALSE
    AND target.end_date = CURRENT_DATE

MERGE INTO sales_targets target
USING (
    SELECT 
        employee_id,
        EXTRACT(YEAR FROM sale_date) as year,
        EXTRACT(QUARTER FROM sale_date) as quarter,
        SUM(amount) as actual_sales
    FROM sales
    GROUP BY employee_id, EXTRACT(YEAR FROM sale_date), EXTRACT(QUARTER FROM sale_date)
) actual
ON target.employee_id = actual.employee_id
   AND target.year = actual.year
   AND target.quarter = actual.quarter
WHEN MATCHED THEN
    UPDATE SET
        target.actual_sales = actual.actual_sales,
        target.variance = actual.actual_sales - target.target_sales,
        target.variance_pct = (actual.actual_sales - target.target_sales) * 100.0 / target.target_sales,
        target.status = CASE
            WHEN actual.actual_sales >= target.target_sales THEN 'Met'
            WHEN actual.actual_sales >= target.target_sales * 0.9 THEN 'Near'
            ELSE 'Below'
        END,
        target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (employee_id, year, quarter, actual_sales, target_sales, variance, status, created_at)
    VALUES (actual.employee_id, actual.year, actual.quarter, actual.actual_sales, 0, actual.actual_sales, 'No Target', CURRENT_TIMESTAMP)