Skip to content

Merge DataFrame

The @sql.merge_dataframe decorator and DataFrameMergeOperator merge a pandas DataFrame into a table with upsert logic.

Operator Usage

import pandas as pd
from airsql.operators import DataFrameMergeOperator
from airsql import Table

df = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'email': ['[email protected]', '[email protected]', '[email protected]'],
})

merge_task = DataFrameMergeOperator(
    task_id='merge_users',
    dataframe=df,
    output_table=Table('postgres_conn', 'public.users'),
    conflict_columns=['id'],
    update_columns=['name', 'email'],
    pre_truncate=False,
)

merge_task.execute(context)

Parameters

Parameter Type Required Description
task_id str Yes Unique task identifier
dataframe pd.DataFrame Yes Pandas DataFrame to merge
output_table Table Yes Destination table reference
conflict_columns List[str] Yes Columns for conflict detection
update_columns List[str] No Columns to update (default: all non-conflict)
timestamp_column str No Column to populate with timestamp
pre_truncate bool No If True, truncate before merge
dry_run_flag bool No If True, simulate without writing
**kwargs Any No Additional operator kwargs

Decorator Usage

Function Returns DataFrame

from airsql import sql, Table
import pandas as pd


@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'public.users'), conflict_columns=['id']
)
def sync_users():
    return pd.DataFrame({
        'id': [1, 2, 3],
        'name': ['Alice', 'Bob', 'Charlie'],
        'email': ['[email protected]', '[email protected]', '[email protected]'],
    })


merge_task = sync_users()

Direct DataFrame Merging

df = pd.DataFrame({'id': [1, 2], 'value': [10, 20]})


@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'staging.data'),
    conflict_columns=['id'],
    dataframe=df,
)
def merge_existing_data():
    pass

Examples

Single Column Conflict

@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'inventory.products'), conflict_columns=['sku']
)
def update_inventory():
    return pd.DataFrame({
        'sku': ['SKU001', 'SKU002', 'SKU003'],
        'name': ['Product A', 'Product B', 'Product C'],
        'quantity': [100, 50, 75],
        'price': [19.99, 29.99, 39.99],
    })

Composite Key Conflict

@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'analytics.daily_metrics'),
    conflict_columns=['metric_date', 'metric_name'],
    update_columns=['metric_value'],
)
def update_daily_metrics():
    return pd.DataFrame({
        'metric_date': ['2024-01-01', '2024-01-01'],
        'metric_name': ['users', 'revenue'],
        'metric_value': [1000, 50000],
    })

Selective Column Update

Only update specific columns on conflict:

@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'public.users'),
    conflict_columns=['user_id'],
    update_columns=['name', 'last_login'],
)
def update_user_profiles():
    return pd.DataFrame({
        'user_id': [1, 2],
        'name': ['Updated Alice', 'Updated Bob'],
        'email': ['[email protected]', '[email protected]'],
        'last_login': [pd.Timestamp.now(), pd.Timestamp.now()],
        'created_at': [pd.Timestamp('2023-01-01'), pd.Timestamp('2023-01-02')],
    })

Pre-Truncate Before Merge

@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'reporting.state'),
    conflict_columns=['entity_id'],
    pre_truncate=True,
)
def rebuild_state():
    return pd.DataFrame({
        'entity_id': [1, 2, 3],
        'state': ['active', 'inactive', 'pending'],
        'updated_at': [pd.Timestamp.now()] * 3,
    })

With Timestamp Column

@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'audit.change_log'),
    conflict_columns=['change_id'],
    timestamp_column='loaded_at',
)
def load_changes():
    return pd.DataFrame({
        'change_id': [101, 102],
        'entity': ['user', 'order'],
        'action': ['create', 'update'],
    })

Dry Run Mode

@sql.merge_dataframe(
    output_table=Table('postgres_conn', 'test.data'),
    conflict_columns=['id'],
    dry_run=True,
)
def test_merge():
    return pd.DataFrame({'id': [1, 2], 'value': [10, 20]})

How It Works

  1. AirSQL prepares the DataFrame and applies automatic timestamp columns if requested.
  2. Rows are merged into the target with backend-specific upsert semantics.
  3. If timestamp_column is specified, it is populated with the current timestamp.

For PostgreSQL targets, AirSQL converts pandas, NumPy, and PyArrow scalar values to native Python values before executing INSERT ... ON CONFLICT. Existing rows are only updated when selected values are different, using an IS DISTINCT FROM guard to avoid no-op physical updates.

Generated SQL (PostgreSQL Example)

INSERT INTO users (id, name, email, last_login)
VALUES (?, ?, ?, ?)
ON CONFLICT (id) 
DO UPDATE SET 
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    last_login = EXCLUDED.last_login
WHERE (users.name, users.email, users.last_login)
IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.email, EXCLUDED.last_login)

Update Columns Behavior

update_columns Behavior
None (default) Update all columns except conflict columns
['col1', 'col2'] Update only specified columns
[] Insert only (skip updates)

Temporary Tables

temp_table = Table('postgres_conn', 'temp.staging', temporary=True)


@sql.merge_dataframe(output_table=temp_table, conflict_columns=['id'])
def process_batch():
    return pd.DataFrame({'id': [1, 2, 3]})