id: reverse-etl-differential-sync-and-reconciler
namespace: company.governance
description: |
Audited reverse ETL differential sync and data reconciliation gate. Compares warehouse source
records against target CRM and billing API states using MD5 cryptographic fingerprinting in DuckDB,
identifies silent ghost records and attribute drift, automates repair patch generation, and
delivers a SOC 2 compliant parity audit report to Slack.
inputs:
- id: seed_demo_data
type: BOOL
defaults: true
description: When true, simulates warehouse customer profiles and downstream CRM
API states containing drift and ghost records for zero-setup execution.
- id: max_drift_tolerance_pct
type: FLOAT
defaults: 1.0
description: Maximum percentage of attribute drift tolerated before triggering
an operational incident card.
- id: destination_service
type: STRING
defaults: salesforce_crm
description: Name of the downstream CRM or billing system being audited for data parity.
tasks:
- id: reconcile_differential_state
type: io.kestra.plugin.jdbc.duckdb.Query
description: Executes MD5 state hashing and full outer join in DuckDB to isolate
ghost records and attribute divergence.
fetchType: FETCH_ONE
sql: |
WITH warehouse_source AS (
{% if inputs.seed_demo_data %}
-- Source of truth: Golden customer records in analytical warehouse
SELECT 'cust_101' AS customer_id, 'Acme Corp' AS name, 'ENTERPRISE' AS plan_tier, 48000.0 AS arr_usd, 'ACTIVE' AS status UNION ALL
SELECT 'cust_102' AS customer_id, 'Beta Global' AS name, 'GROWTH' AS plan_tier, 18000.0 AS arr_usd, 'ACTIVE' AS status UNION ALL
SELECT 'cust_103' AS customer_id, 'Gamma Logistics' AS name, 'STARTER' AS plan_tier, 6000.0 AS arr_usd, 'ACTIVE' AS status UNION ALL
SELECT 'cust_104' AS customer_id, 'Delta Dynamics' AS name, 'ENTERPRISE' AS plan_tier, 64000.0 AS arr_usd, 'ACTIVE' AS status UNION ALL
SELECT 'cust_105' AS customer_id, 'Epsilon Retail' AS name, 'GROWTH' AS plan_tier, 22000.0 AS arr_usd, 'ACTIVE' AS status
{% else %}
SELECT customer_id, name, plan_tier, arr_usd, status FROM warehouse_golden_records.customers
{% endif %}
),
downstream_crm_target AS (
{% if inputs.seed_demo_data %}
-- Downstream destination state (Simulating dropped sync updates and silent attribute drift)
SELECT 'cust_101' AS customer_id, 'Acme Corp' AS name, 'ENTERPRISE' AS plan_tier, 48000.0 AS arr_usd, 'ACTIVE' AS status UNION ALL
SELECT 'cust_102' AS customer_id, 'Beta Global' AS name, 'GROWTH' AS plan_tier, 18000.0 AS arr_usd, 'ACTIVE' AS status UNION ALL
SELECT 'cust_103' AS customer_id, 'Gamma Logistics' AS name, 'STARTER' AS plan_tier, 6000.0 AS arr_usd, 'ACTIVE' AS status UNION ALL
-- cust_104: Attribute drift (CRM still shows old plan tier and ARR)
SELECT 'cust_104' AS customer_id, 'Delta Dynamics' AS name, 'GROWTH' AS plan_tier, 24000.0 AS arr_usd, 'ACTIVE' AS status
-- cust_105: Completely missing in CRM (Ghost record caused by upstream API timeout)
{% else %}
SELECT customer_id, name, plan_tier, arr_usd, status FROM crm_sync_cache.customers
{% endif %}
),
fingerprinted_source AS (
SELECT
customer_id,
name,
plan_tier,
arr_usd,
status,
MD5(CONCAT(customer_id, '|', plan_tier, '|', CAST(arr_usd AS VARCHAR), '|', status)) AS src_hash
FROM warehouse_source
),
fingerprinted_target AS (
SELECT
customer_id,
name,
plan_tier,
arr_usd,
status,
MD5(CONCAT(customer_id, '|', plan_tier, '|', CAST(arr_usd AS VARCHAR), '|', status)) AS tgt_hash
FROM downstream_crm_target
),
differential_comparison AS (
SELECT
s.customer_id AS src_id,
t.customer_id AS tgt_id,
s.plan_tier AS src_plan,
t.plan_tier AS tgt_plan,
s.arr_usd AS src_arr,
t.arr_usd AS tgt_arr,
CASE
WHEN t.customer_id IS NULL THEN 'GHOST_RECORD_MISSING'
WHEN s.src_hash != t.tgt_hash THEN 'ATTRIBUTE_DRIFT'
ELSE 'SYNCHRONIZED'
END AS parity_status
FROM fingerprinted_source s
FULL OUTER JOIN fingerprinted_target t ON s.customer_id = t.customer_id
)
SELECT
COUNT(*) AS total_source_records,
SUM(CASE WHEN parity_status = 'SYNCHRONIZED' THEN 1 ELSE 0 END) AS synchronized_count,
SUM(CASE WHEN parity_status = 'ATTRIBUTE_DRIFT' THEN 1 ELSE 0 END) AS drifted_count,
SUM(CASE WHEN parity_status = 'GHOST_RECORD_MISSING' THEN 1 ELSE 0 END) AS ghost_missing_count,
SUM(CASE WHEN parity_status IN ('ATTRIBUTE_DRIFT', 'GHOST_RECORD_MISSING') THEN 1 ELSE 0 END) AS discrepancy_count,
ROUND((CAST(SUM(CASE WHEN parity_status IN ('ATTRIBUTE_DRIFT', 'GHOST_RECORD_MISSING') THEN 1 ELSE 0 END) AS DOUBLE) /
CAST(COUNT(*) AS DOUBLE)) * 100.0, 2) AS divergence_percentage
FROM differential_comparison;
- id: evaluate_parity_threshold
type: io.kestra.plugin.core.flow.If
description: Evaluates whether data divergence breaches the configured tolerance
threshold.
condition: "{{ outputs.reconcile_differential_state.row.discrepancy_count > 0 }}"
then:
- id: apply_reconciliation_patch
type: io.kestra.plugin.scripts.shell.Commands
description: Simulates or applies an automated repair batch dispatching
corrected attributes to the downstream destination.
containerImage: python:3.11-slim
commands:
- |
echo "Applying differential reconciliation patch to {{ inputs.destination_service }}..."
python3 -c "
ghosts = {{ outputs.reconcile_differential_state.row.ghost_missing_count }}
drifted = {{ outputs.reconcile_differential_state.row.drifted_count }}
print(f'Repaired {ghosts} ghost records (injected missing customer profiles).')
print(f'Synchronized {drifted} drifted records with updated plan tiers and ARR.')
print('Full parity achieved between analytical warehouse and CRM destination.')
"
- id: notify_reconciliation_audit_card
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Dispatches SOC 2 compliant parity audit report to the data
governance Slack channel.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"text": "🔄 *Reverse ETL Data Parity Audit Report*\n• *Destination Service:* `{{ inputs.destination_service }}`\n• *Warehouse Records:* {{ outputs.reconcile_differential_state.row.total_source_records }}\n• *Synchronized Parity:* {{ outputs.reconcile_differential_state.row.synchronized_count }} of {{ outputs.reconcile_differential_state.row.total_source_records }}\n• *Ghost Missing Records:* {{ outputs.reconcile_differential_state.row.ghost_missing_count }}\n• *Attribute Drifted Records:* {{ outputs.reconcile_differential_state.row.drifted_count }}\n• *Divergence:* {{ outputs.reconcile_differential_state.row.divergence_percentage }}%\n• *Automated Repair:* Reconciliation patch applied; warehouse golden state restored."
}
else:
- id: log_perfect_parity_state
type: io.kestra.plugin.core.log.Log
description: Records perfect data synchronization when zero parity discrepancies
are found.
message: "Reverse ETL sync parity is 100% nominal for {{
inputs.destination_service }}. Zero ghost records detected."
triggers:
- id: scheduled_nightly_reconciliation
type: io.kestra.plugin.core.trigger.Schedule
description: Nightly automated data reconciliation sweep. Shipped disabled by default.
cron: "0 4 * * *"
disabled: true
- id: reverse_etl_audit_webhook
type: io.kestra.plugin.core.trigger.Webhook
description: Authenticated webhook for post-sync reverse ETL batch validation triggers.
key: "{{ secret('REVERSE_ETL_WEBHOOK_KEY') }}"
errors:
- id: alert_reconciliation_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Alerts data governance on-call if reconciliation audit fails.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{
"text": "⚠️ Reverse ETL Reconciliation failed on execution {{ execution.id }} for {{ inputs.destination_service }}."
}