Reverse ETL Patterns

Push transformed warehouse data back into operational tools. Covers reverse ETL architecture, common destinations, sync strategies, data activation workflows, and the patterns that close the loop between analytics and operations.

Reverse ETL refers to the process of moving data from a destination system back into a source system, typically to update or enrich source data based on downstream transformations and analytics results. This pattern is essential when you need to persist derived values, computed metrics, or updated metadata back into the source system for consumption by other systems that do not support real-time updates.

Update Source Data From Derived Results

To update source records with values calculated in a downstream system, use a batch process that reads from a data warehouse and applies SQL UPDATE statements to the source database.

Example workflow:

  1. Export transformed data from a data warehouse (e.g., Snowflake) to a staging table
  2. Generate an UPDATE statement using the MERGE syntax
MERGE INTO source_table AS target
USING staging_table AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET
    calculated_field = source.calculated_value,
    last_updated = CURRENT_TIMESTAMP;

Failure mode: If the staging table lacks a primary key, updates may silently overwrite unrelated rows. The MERGE operation will succeed even if no rows match, producing no error and leaving data in an inconsistent state.

Sync Incremental Changes From Target Back to Source

For systems that do not support full data reconciliation, implement a pattern that identifies changed records in the target system and applies only those changes back to the source.

Use a timestamp column or version field to track updates:

-- In target system (e.g., PostgreSQL)
SELECT * FROM facts_table 
WHERE updated_at > '2023-01-01T00:00:00Z';

-- In source system (e.g., MySQL)
UPDATE source_table SET 
  metric_value = ?, 
  last_synced = NOW()
WHERE id = ?;

Failure mode: If the target system does not maintain a consistent update timestamp, changes may be missed or duplicated. The pattern assumes deterministic change tracking; if multiple processes write to the same target records, concurrent updates may overwrite each other.

Handle Upsert Operations Across Systems

When updating data in a source system based on derived values from a destination system, use an UPSERT operation that creates new records or updates existing ones.

Example using PostgreSQL's ON CONFLICT clause:

INSERT INTO source_table (id, calculated_value, last_updated)
VALUES (12345, 987.65, NOW())
ON CONFLICT (id) DO UPDATE SET
  calculated_value = EXCLUDED.calculated_value,
  last_updated = EXCLUDED.last_updated;

Failure mode: If ON CONFLICT is used without specifying a conflict target, the operation will fail with an error indicating that no unique constraint was found to determine which row conflicts. The pattern does not account for partial updates that may leave some fields unchanged.

Apply Data Changes in Batch Transactions

When multiple records need to be updated together, wrap the operations in a transaction to ensure consistency and prevent partial updates.

BEGIN;
UPDATE source_table SET metric_a = 100 WHERE category = 'A';
UPDATE source_table SET metric_b = 200 WHERE category = 'B';
COMMIT;

Failure mode: If one update fails within the transaction, all changes are rolled back. This pattern does not handle cases where individual records might fail to update while others succeed, which requires more granular error handling.

Implement Change Tracking for Reverse ETL

Use a change tracking mechanism that logs which records were modified and when, enabling incremental reverse ETL operations.

Example schema:

CREATE TABLE sync_log (
  id BIGSERIAL PRIMARY KEY,
  source_id VARCHAR(255),
  operation_type VARCHAR(10),
  sync_timestamp TIMESTAMP DEFAULT NOW()
);

Use this log to identify changes for processing:

SELECT * FROM sync_log 
WHERE sync_timestamp > '2023-01-01T00:00:00Z'
ORDER BY sync_timestamp;

Failure mode: If the sync log table is not properly indexed on sync_timestamp, queries will be slow and may cause timeouts during large sync operations. The pattern does not account for systems that do not support row-level logging.

Manage Data Consistency Between Source and Destination

To ensure that source data reflects the most recent derived values, implement a verification step that compares values in both systems.

Example comparison query:

SELECT 
  s.id,
  s.calculated_value AS source_value,
  d.calculated_value AS destination_value
FROM source_table s
JOIN destination_table d ON s.id = d.id
WHERE s.calculated_value != d.calculated_value;

Failure mode: If the comparison is done without considering data types or precision differences, values that should match may appear mismatched. The pattern does not account for systems with different time zones or floating-point representation.

Process Reverse ETL in Staged Pipelines

Break reverse ETL workflows into multiple stages to allow for validation and error handling.

Stage 1: Extract from destination system

SELECT id, calculated_value FROM destination_table 
WHERE last_processed < '2023-01-01T00:00:00Z';

Stage 2: Validate and transform

-- Apply transformations or business rules before writing back
UPDATE temp_staging SET validated_value = UPPER(original_value);

Stage 3: Write to source system

UPDATE source_table SET 
  calculated_value = (SELECT validated_value FROM temp_staging WHERE temp_staging.id = source_table.id)
WHERE id IN (SELECT id FROM temp_staging);

Failure mode: If the staging table is not properly cleaned between runs, stale data may be processed. The pattern assumes that all validation logic can be expressed in SQL, which may not be true for complex business rules.

Handle Partial Failures Gracefully

Implement a retry mechanism that can reprocess failed records without reprocessing successfully completed ones.

Example approach:

-- Mark records as processing
UPDATE source_table SET status = 'processing' WHERE id IN (SELECT id FROM batch_to_process);

-- Process with error handling
BEGIN;
UPDATE source_table SET calculated_value = ? WHERE id = ?;
COMMIT;

-- Mark successful records
UPDATE source_table SET status = 'completed' WHERE id IN (SELECT id FROM batch_to_process);

Failure mode: If the process fails between marking as "processing" and completing, records may remain in an inconsistent state. The pattern does not include mechanisms for detecting and recovering from partial failures.

Schedule Reverse ETL Jobs

Use a scheduler to run reverse ETL processes at regular intervals, ensuring data stays synchronized.

Example cron job:

# Run every hour at 15 minutes past the hour
0 15 * * * /usr/local/bin/reverse-etl-script --batch-size 1000 --target-type salesforce

Failure mode: If the script does not properly handle time zone differences, jobs may run at unexpected times. The pattern assumes that job scheduling is handled by external tools rather than built-in system features.

Monitor Reverse ETL Success and Failure

Implement logging to track when reverse ETL operations succeed or fail.

Example logging approach:

INSERT INTO etl_log (
  operation_type,
  source_table,
  target_table,
  records_processed,
  status,
  error_message,
  timestamp
) VALUES (
  'reverse_etl',
  'source_table',
  'destination_table',
  1234,
  'success',
  NULL,
  NOW()
);

Failure mode: If logging is not implemented consistently, it becomes difficult to detect and resolve failures. The pattern does not include mechanisms for alerting or automatic retry of failed operations.

Handle Data Type Mismatches

When moving data between systems with different type definitions, explicitly convert types during the reverse ETL process.

Example:

-- Convert from string to numeric
UPDATE source_table SET 
  numeric_field = CAST(string_field AS DECIMAL(10,2))
WHERE string_field IS NOT NULL;

Failure mode: If conversion fails due to invalid input data, the entire batch may fail. The pattern does not include error handling for conversion failures or mechanisms to log bad records for manual review.

Manage Dependencies Between Data Sources

When a reverse ETL operation depends on data from multiple systems, ensure dependencies are resolved before processing.

Example workflow:

-- Wait for required data in dependent systems
SELECT COUNT(*) FROM dependent_table WHERE timestamp > '2023-01-01T00:00:00Z';
-- Only proceed if count > 0

Failure mode: If dependencies are not properly checked, operations may process incomplete data. The pattern does not include mechanisms for handling missing dependencies or waiting for them to be satisfied.

The key to successful reverse ETL is understanding that it is fundamentally about moving data back from a destination system to a source, with careful attention to consistency, error handling, and incremental processing. Each pattern must account for potential failures in the process and maintain data integrity across systems.

This page was rewritten on 16 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.