mech.app

Posted on Oct 15 Originally published at mech.app

MovingLake's Bidirectional Connectors: How Real-Time ETL Agents Skip the Data Warehouse

#systemdesign #data #api #architecture

Most reverse ETL tools assume you already have a data warehouse. You extract from production systems, load into Snowflake or BigQuery, transform in dbt, then push enriched records back to your CRM. MovingLake skips the warehouse entirely and pipes data straight from APIs to destination systems in both directions. For agentic workflows that need to read from third-party APIs and write decisions back to production databases without manual orchestration, this architecture exposes new failure modes and design trade-offs.

What MovingLake Does

MovingLake provides bidirectional data connectors that handle both traditional ETL (API to Snowflake) and reverse ETL (warehouse or API directly to CRM) in real time. The core claim is that you can move data between systems without staging it in a central warehouse first.

Key capabilities:

Real-time sync between APIs, databases, and SaaS tools
Bidirectional writes: read from Salesforce, write back to Salesforce
Schema mapping without a staging layer
Conflict resolution when upstream and downstream records diverge

This matters for agent-driven workflows because agents often need to:

Pull fresh data from multiple sources (Stripe, HubSpot, internal APIs)
Make decisions based on that data
Write results back to the same systems without human approval

Traditional ETL pipelines batch these steps hourly or daily. MovingLake targets sub-minute latency.

Architecture: No Warehouse, No Problem?

The standard reverse ETL flow looks like this:

Production DB → Fivetran → Snowflake → dbt → Census → CRM

MovingLake collapses it:

Production DB ↔ MovingLake ↔ CRM

How it works under the hood:

Connectors are bidirectional adapters for each source/destination (Salesforce, PostgreSQL, Stripe, etc.)
Schema mapper translates field names and types between systems without a staging schema
Sync engine polls or listens for changes (webhooks, CDC, or API polling depending on the source)
Conflict resolver decides what happens when a record changes in both systems between syncs

No intermediate warehouse means no dbt transformations, no SQL-based business logic layer, and no single source of truth. You trade centralized data modeling for lower latency and fewer moving parts.

Real-Time Sync: What It Actually Means

"Real-time" in ETL contexts usually means one of three things:

Mechanism	Latency	Agent Suitability	Failure Mode
Webhooks	Sub-second	Best for event-driven agents	Missed events if endpoint is down
Change Data Capture (CDC)	1-10 seconds	Good for database sources	Requires database-level permissions
API Polling	30-300 seconds	Acceptable for batch-like agents	Rate limits, stale data between polls

MovingLake likely uses a mix depending on the connector. Salesforce supports webhooks via Platform Events. PostgreSQL supports CDC via logical replication. Stripe requires polling unless you configure webhook endpoints.

For an agent that needs to update a CRM record after processing a payment, webhook-based sync means the agent sees the payment event within seconds. Polling-based sync means the agent might wait up to five minutes, which breaks real-time decision loops.

Conflict Resolution: Who Wins When Both Sides Write?

Bidirectional sync introduces a classic distributed systems problem: what happens when the same record changes in both systems between sync cycles?

Example scenario:

Agent reads contact.status = "lead" from HubSpot at 10:00:00
Agent decides to update contact.status = "qualified" and writes to HubSpot at 10:00:05
Meanwhile, a human user changed contact.status = "unqualified" in HubSpot at 10:00:03
Sync engine detects both changes at 10:00:10

Common resolution strategies:

Last-write-wins (LWW): Timestamp-based, simple but loses data
Source-of-truth priority: HubSpot always wins, or the agent always wins
Merge rules: Combine changes if fields don't overlap
Manual review queue: Flag conflicts for human resolution

MovingLake does not publicly document its conflict resolution strategy, but the architecture suggests it defaults to last-write-wins with optional custom merge rules. For agent workflows, this means you need to:

Implement optimistic locking in your agent (read version, write with version check)
Accept that some writes will be silently overwritten
Build retry logic that re-reads before re-writing

Schema Mapping Without a Staging Layer

Traditional ETL uses the warehouse as a schema normalization layer. You load raw JSON from Stripe into a staging table, then transform it into a clean payments table with standardized column names.

Without a warehouse, MovingLake has to map schemas on the fly:

# Conceptual example of schema mapping config
mapping = {
    "source": "stripe.charges",
    "destination": "salesforce.opportunities",
    "fields": {
        "amount": "salesforce.amount__c / 100",  # Stripe uses cents
        "customer.email": "salesforce.contact_email__c",
        "created": "salesforce.created_date__c"
    },
    "filters": {
        "status": "succeeded"  # Only sync successful charges
    }
}

Challenges this introduces:

Type coercion: Stripe's amount is an integer (cents), Salesforce expects a decimal (dollars)
Nested fields: Stripe returns customer.email, Salesforce uses flat contact_email__c
Conditional logic: You can't run SQL transformations, so filtering happens in the connector layer

For agents, this means your schema mapping config becomes part of your infrastructure-as-code. If the agent needs to enrich CRM records with payment data, you need to version and test the mapping rules just like you would a dbt model.

Observability and Failure Modes

Bidirectional sync without a warehouse removes the audit trail you get from warehouse logs. When a sync fails, you need to answer:

Did the read fail or the write fail?
Which records were skipped due to schema validation errors?
Did the conflict resolver drop any updates?

What to instrument:

Sync latency per connector: How long between source change and destination write?
Conflict rate: How often do both sides change the same record?
Schema validation errors: Which fields failed type coercion?
Retry queue depth: How many writes are waiting for the destination API to recover?

MovingLake likely provides a dashboard for these metrics, but you should also emit them to your own observability stack (Datadog, Prometheus, etc.) so you can correlate sync failures with agent behavior.

When Agents Need Bidirectional Sync

This architecture makes sense when:

Agent latency matters more than data consistency: You need sub-minute sync, and you can tolerate occasional conflicts
Your transformations are simple: Field renaming, type coercion, and basic filtering, not complex joins or aggregations
You trust the agent to handle stale reads: The agent re-reads before writing, or accepts that its view might be slightly out of date

It does not make sense when:

You need a single source of truth: Multiple agents writing to the same CRM records will conflict
Your transformations are complex: If you need dbt-style SQL models, you need a warehouse
Compliance requires audit logs: Warehouse-based ETL gives you immutable logs of every transformation

Technical Verdict

MovingLake's bidirectional connectors are infrastructure for low-latency agent workflows that need to read from APIs and write back to production systems without manual orchestration. The architecture trades centralized data modeling for speed and simplicity.

Use it when:

Your agent needs to sync data between SaaS tools in under a minute
Your transformations fit into field mapping and basic filters
You can implement optimistic locking or accept last-write-wins conflicts

Avoid it when:

You need complex SQL transformations (use dbt + a warehouse)
Multiple agents or humans write to the same records (conflicts will escalate)
Compliance requires immutable audit logs of every data transformation

The hardest part is not the sync latency. It is reasoning about conflict resolution and schema drift when your agent's view of the world diverges from the source system's view. If you can keep your mappings simple and your agents idempotent, bidirectional sync removes a lot of orchestration overhead. If you cannot, you will spend more time debugging conflicts than you saved by skipping the warehouse.

DEV Community