Data operations¶

Data operations (data_op()) are non-DDL statements that mutate data or trigger maintenance. They are not declared in the model: they are ad-hoc operations.

Additional model examples¶

Batch partition cleanup¶

# Freeze, back up, then drop old partitions
from dbwarden.databases.clickhouse import data_op

# Freeze for backup
for m in ["2023-01", "2023-02", "2023-03"]:
    data_op(name=f"freeze_{m}", forward=f"ALTER TABLE events FREEZE PARTITION '{m}'")

# After backup verified, drop
for m in ["2023-01", "2023-02", "2023-03"]:
    data_op(name=f"drop_{m}", forward=f"ALTER TABLE events DROP PARTITION '{m}'")

Conditional mutation with setting override¶

# Large mutation with timeout
data_op(
    name="archive_old_events",
    forward="""
    ALTER TABLE events
        UPDATE status = 'archived'
        WHERE event_date < '2020-01-01'
        SETTINGS mutations_sync = 2
""",
)

OPTIMIZE with deduplicate¶

# Force full merge and deduplication on all parts
data_op(name="optimize_deduplicate", forward="OPTIMIZE TABLE events FINAL DEDUPLICATE")

# With column-specific deduplication
data_op(name="optimize_deduplicate_by", forward="OPTIMIZE TABLE events FINAL DEDUPLICATE BY id, event_date")

Multi-step migration with data ops¶

def migrate_events():
    # 1. Create new table via migrate
    # 2. Backfill from old partition
    data_op(
        name="replace_partition_from_v1",
        forward="""
        ALTER TABLE events_v2
            REPLACE PARTITION '2024-01'
            FROM events_v1
    """,
    )
    # 3. Drop old partition
    data_op(name="drop_v1_partition", forward="ALTER TABLE events_v1 DROP PARTITION '2024-01'")
    # 4. Verify
    data_op(name="optimize_v2", forward="OPTIMIZE TABLE events_v2 FINAL")

Partition operations¶

from dbwarden.databases.clickhouse import data_op

# Attach a detached partition
data_op(name="attach_partition", forward="ALTER TABLE events ATTACH PARTITION '2024-01'")

# Replace one partition with another
data_op(name="replace_partition", forward="ALTER TABLE events REPLACE PARTITION '2024-02' FROM staging_events")

# Drop a partition
data_op(name="drop_partition", forward="ALTER TABLE events DROP PARTITION '2024-01'")

# Clear column in partition
data_op(name="clear_column", forward="ALTER TABLE events CLEAR COLUMN payload IN PARTITION '2024-01'")

# Freeze partition for backup
data_op(name="freeze_partition", forward="ALTER TABLE events FREEZE PARTITION '2024-01'")

# Unfreeze
data_op(name="unfreeze_partition", forward="ALTER TABLE events UNFREEZE PARTITION '2024-01'")

Mutations¶

# DELETE
data_op(name="delete_old_events", forward="ALTER TABLE events DELETE WHERE event_date < '2023-01-01'")

# UPDATE
data_op(name="redact_payload", forward="ALTER TABLE events UPDATE payload = 'redacted' WHERE id = 123")

Synchronicity

Mutations are async by default. Use SETTINGS mutations_sync = 2 to wait for all replicas, or mutations_sync = 1 to wait for the current server only. See ALTER semantics for the full distinction between mutations_sync and alter_sync. Do not use value 3 on ReplicatedMergeTree; it silently does nothing (known bug).

OPTIMIZE¶

# Merge parts
data_op(name="optimize_final", forward="OPTIMIZE TABLE events FINAL")

# With partition
data_op(name="optimize_partition", forward="OPTIMIZE TABLE events PARTITION '2024-01' FINAL")

# Deduplicate
data_op(name="optimize_deduplicate", forward="OPTIMIZE TABLE events FINAL DEDUPLICATE")

POPULATE¶

# Populate a materialized view
data_op(name="populate_mv", forward="ALTER TABLE mv_name POPULATE")

This is a data-op rather than a DDL property because it is a write concern, not structural. See Materialized views.

The data_ops module also provides a populate(materialized_view_spec) helper (exported as data_ops_populate) that derives the populate statement from a materialized or aggregating view spec and returns a DataOp whose forward SQL is INSERT INTO <target> <select>. It requires a target table and a select query; pass rollback= to make it reversible. A plugin or authoring tool can pass the descriptor in an explicit apply_data_op operation to ChDataOpHandler.emit(). When requires_confirmation is set, the emitter comments out the forward SQL for manual review instead of opening an interactive prompt. Deduplication is by migration history, not by the descriptor's name.

Secret rotation¶

Named collection secrets are rotated through ClickHouse's secret store:

# Refresh credentials from secret store
data_op(
    name="rotate_kafka_secret",
    forward="ALTER NAMED COLLECTION kafka_prod UPDATE sasl_password = SECRET 'new_secret_id'",
)

Safety¶

Operation	Safety	Notes
ATTACH PARTITION	INFO	Cheap metadata operation
REPLACE PARTITION	WARN	Overwrites target
DROP PARTITION	WARN	Data loss within a partition
CLEAR COLUMN	WARN	Data cleared for partition
DELETE mutation	WARN	Async, causes part rewrites
UPDATE mutation	WARN	Async, causes part rewrites
OPTIMIZE FINAL	INFO	Heavy IO
POPULATE	INFO	Inserts current data
Secret rotation	INFO

Rollback behavior¶

Data operations are not reversible by dbwarden: they are ad-hoc mutations. Plan accordingly: test on staging, back up partitions before DROP or REPLACE.