Datics AI
← All work

Ephemera

Ephemera's network analytics sync redesigned to read only new rows, every ten minutes

Ephemera's analytics job for the XMTP network reloaded and compared its full message history on every run. Working in Ephemera's own Google Cloud project, we redesigned it to fetch only rows newer than what Dune already holds, in batches that also carry the first sync and any catch up.

Data & AnalyticsNetwork analytics data pipelineModernized a live product

Design
Incremental, batched sync every 10 minutes
Data path
Snowflake to Dune, unchanged tools on both ends
Runs in
Ephemera's own Google Cloud project
Status
Approved, then reported working by the client

The challenge

A scheduled job loaded the full XMTP message dataset into memory on every run and diffed it against the last snapshot, so memory use and run time grew with the data.

What we did

We proposed an incremental query with an out-of-core fallback, then designed a batched flow that starts from the latest timestamp already in Dune and loops until nothing is left.

The outcome

Ephemera approved a sync that reads only new rows every ten minutes, with first sync and recovery on the same batched path, and later told us it was working great.

The starting point

Ephemera is the company behind XMTP, an open messaging network used by web3 apps and wallets. A Cloud Run job in its Google Cloud project moved network message data from its Snowflake warehouse into Dune, where the network's analytics live.

What had to change

Every run downloaded the last snapshot, queried the full dataset, loaded it into memory and diffed it to find new wallet addresses. Memory use and run time grew with the data, run after run.

What could not break

  • Snowflake stays the source and Dune stays the analytics destination
  • The job keeps running in the client's own Google Cloud project
  • Each upload stays under Dune's CSV upload cap of about 200 MB
  • A failed run must be able to catch up on the next one
  • The client approves the design before the running job changes

The calls we made

We treated it as a question of what the job really needs to read each time, not as a reason to rebuild the pipeline.

  1. 01

    Read only what is new

    The message data carries a timestamp that only grows, so the job can ask Snowflake for rows newer than the last one synced and skip the history entirely. We recommended this as the primary approach.

    Ruled out

    Keeping the full reload and in-memory diff, which costs more memory and time on every run as the network grows.

  2. 02

    Start from what Dune already holds

    In the approved flow, each run first asks Dune for the latest timestamp it holds, so the next query begins exactly where the destination ends.

    Ruled out

    A last run timestamp kept in a separate Snowflake metadata table, as in our first proposal.

  3. 03

    Keep an out-of-core fallback

    Where a full comparison is still needed, Dask or Vaex can diff the data against the stored snapshot without loading it all into memory.

    Ruled out

    Pandas for full comparisons, which holds the whole dataset in memory at once.

  4. 04

    Batch every run, even when one batch is enough

    The client noted the first sync needs batching because of Dune's upload cap. We kept batching for every run, since pipelines fail for unforeseen reasons and a catch up should use the same path as a normal run.

    Ruled out

    Batching only the first sync.

How the system fits together

triggerlatest timestamp?batch of newerrowsrowsupload batch, confirmfallback diffonlyCloud Run scheduledjobStarts the sync every tenminutesETL controllerFetch, upload, confirm,loop, logSnowflakeXMTP message data, thesourceGoogle CloudStorageSnapshots for theout-of-core fallbackDuneNetwork analytics, CSVupload API
Client's existing systemBuilt by DaticsData storeOutside serviceThe approved flow inside Ephemera's own Google Cloud project: the controller loops through batches until no newer rows are left, then logs completion.

What we built

Before and after, side by side

We mapped the old flow (snapshot, full query, in-memory diff, upload) against the new one so the client could see exactly which steps disappear.

Before the redesign, every run downloaded the last snapshot, queried every timestamp and wallet address, and diffed it all in memory.
02

Incremental, batched sync design

A scheduled job triggers an ETL controller that reads the latest timestamp in Dune, queries Snowflake for newer rows in batches, uploads and confirms each batch, and loops until nothing is left.

03

Written proposal with two paths

A proposal that recommends the timestamp based incremental query and keeps an out-of-core comparison for any case where a full diff is still required.

How it works

Trigger

A Cloud Run scheduled job starts the ETL every ten minutes.

Find the high water mark

The controller asks Dune for the latest timestamp it already holds.

Fetch a batch

Snowflake returns the next batch of rows newer than that timestamp.

Upload and confirm

Each batch goes to Dune and is confirmed before the next one is fetched.

Finish

When no batches are left, the run logs completion and ends.

What changed

Ephemera approved a design that replaces a growing full reload with a sync that reads only new rows, and later told us it was working great. The same batched path handles normal runs, the first sync and recovery after a failed run, without changing the warehouse, the storage or the analytics tool.

Only rows newer than what Dune holds
Each run reads
Every ten minutes
Schedule
“It's working great”
Saul CarlinEphemera (XMTP)Ephemera

Under the hood

Datics role
Modernized a live product
Source and destination
Snowflake as the source, Dune as the analytics destination through its CSV upload API, with snapshots in Google Cloud Storage.
Scheduling
A Cloud Run scheduled job in the client's Google Cloud project runs the sync every ten minutes.
Upload limit
Batches keep every upload under Dune's cap of about 200 MB, so large catch ups do not fail on size.

Data and retrieval

Snowflake

Apps and APIs

Python

Also in the stack

DuneGoogle Cloud RunGoogle Cloud StoragePandasDask or Vaex

How it shipped

  1. Access to the running job

    Ephemera shared its Google Cloud project, the running Cloud Run job and a shared Slack channel.

  2. Proposal

    We mapped the existing flow and proposed two ways to cut memory and run time, recommending the incremental query.

  3. Design and approval

    A sequence diagram and a flowchart of the batched flow went to the client in Slack, and the client approved it.

Inside the client's own cloud

We worked in Ephemera's own Google Cloud project and on its existing Cloud Run job, not in a separate environment of ours.

Decisions in their Slack

Questions and designs went through a shared Slack channel in Ephemera's workspace, where its team reviewed them.

Design approved before code

We shared a sequence diagram and a flowchart, and the client approved the flow before anything changed in the running job.

What this unlocked

The same pattern fits any scheduled sync into a third party analytics tool: start from what the destination already holds, batch around its limits, and let recovery run the normal path.

Related work

Have a product with the same kind of constraints?

Tell us what already exists and what you want it to do. We will tell you what it takes.

Book a 30-minute Product Review