Templates in development

Written for Databricks, Snowflake and Fabric and tested on DuckDB. None has run on a real platform yet, and none is released.

Reverse ETL

Reverse ETL: safe to re-run.

Push curated gold data into business applications and operational databases. Send only what changed, with an idempotency key, so a retry never creates duplicates.

Template in development
Model and serve
Databricks first
Platform notesSQL sketch
ShareLinkedInXRedditBluesky

Shape card

10 / 10

  1. Source
  2. Bronze
  3. Silver
  4. Gold (used)
  5. Apps (used)
Group
Model and serve
Works across
Gold to Apps
Reads
Gold tables
Writes
Business applications and databases
Slots
8 typed slots
Tested by
Golden tests on DuckDB
Platforms
Databricks first, then Snowflake and Microsoft Fabric

Overview

What Reverse ETL does.

Push curated gold data back into business applications and operational databases, safe to re-run.

What it does

Reverse ETL reads rows from a gold table and writes them to a destination, such as a business application's API or an operational database. It sends only what changed since the last push and uses an idempotency key, so a retry never creates a second copy of a record.

Only the fields you allow are written, so values that the application owns are never overwritten. Batch size and rate limit are slots, and deletes are not pushed unless you ask for them.

Use it when

  • Sending scores, segments or account health to a sales, support or marketing tool.
  • Feeding an application database with computed values.
  • Keeping a lookup table in an operational system in step with a curated source.

Use another shape when

Slots

Inputs, outputs and slots.

What the Reverse ETL template reads, what it writes and the typed slots it asks you for. The agent fills most slots from your catalog and your request.

In

Inputs

  • A gold table with a stable key for every record
  • The destination connection and the field mapping
  • The record of what was pushed last time

Reverse ETL

  1. Source
  2. Bronze
  3. Silver
  4. Gold (used)
  5. Apps (used)

Out

Outputs

  • Created or updated records in the destination, and only changed ones
  • A record of what was pushed, with a hash of each payload
  • Failed records with their reasons, safe to retry

Template slots

8 slots · names and types may change before release

source_table
table
The gold table to read.
destination
string
The destination connection: a business application or a database.
key_columns
columns
The columns that identify a record in both systems.
field_mapping
expressions
Which gold column maps to which destination field.
writable_fields
columns
The only fields Emolysis may write. Everything else is left alone.
batch_size
integer
How many records go in one request.
rate_limit
integer
The most requests per minute the destination allows.
push_deletes
boolean
Whether a record removed from the gold table is deleted in the destination.

Slot types are identifier, table, column, columns, expression, expressions, string, integer, boolean, duration and enum. What is a template slot.

Testing

Tested before it ships.

Every template comes with golden tests that run on DuckDB on your machine before anything reaches your platform.

How a run goes

The golden test pushes to a stand-in destination on your machine that records everything it receives, so a repeated push is visible.

  1. 01Fixtures go in: small tables with the hard cases for the shape.
  2. 02The template runs on DuckDB on your machine, with no cluster and no warehouse compute.
  3. 03The result is compared with the expected output, row for row.
  4. 04Green can deploy. Red sends the agent back to the template.
What is a golden testGolden tests in the docs

What the Reverse ETL test checks

  1. Case 01

    Push the same batch twice. The second push changes nothing.

  2. Case 02

    Change one row and push. Only that record is sent.

  3. Case 03

    The destination rejects a batch, as a rate limit would. Retrying sends no duplicates.

  4. Case 04

    A field outside the allowed list is never sent.

  5. Case 05

    A row removed from the gold table sends no delete unless the slot says so.

Each template is also judged per platform: yellow when it passes on DuckDB, green when it also passes on the real platform. Nothing is released, so no template has a green mark yet.

Platforms

Written for the platform you run.

The same shape has a different correct form on each platform. This is what the Reverse ETL template uses on each one, and the platform facts that decide it.

Databricks

In development

The Databricks template uses a scheduled job that reads the gold Delta table and can use Change Data Feed to find the changed rows.

  • Change Data Feed on the gold table lists the rows that changed since the last version pushed, the cheapest way to send only changes.
  • Without it, the template compares a hash of each row's payload with the hash stored at the last push.
  • Destination credentials stay on Databricks, in a Unity Catalog HTTP connection someone in your workspace sets up, never in a pipeline's files; the job needs USE CONNECTION on it.
  • A job that allows only one active run keeps two pushes from overlapping.
Emolysis for Databricks

Snowflake

Planned

The Snowflake template uses a task with a stream on the gold table. Outbound calls need your account's external network access set up.

  • A stream on the gold table yields the changed rows for the task to push.
  • Calling an external API from Snowflake needs a network rule, a secret and an external access integration, which an administrator creates. The template lists them in its notes.
  • Alternatively the push can run outside Snowflake and only read the stream's output.
  • Batch the rows so the number of calls stays under the destination's rate limit.
Emolysis for Snowflake

Microsoft Fabric

Planned

The Fabric template uses a pipeline copy activity into a database, or a notebook for APIs.

  • A Data Pipeline copy activity can write to supported operational databases with an upsert on a key, which is idempotent by design.
  • A notebook calls REST APIs in batches, and can read the destination's token from a key vault at run time.
  • A pipeline's retry settings handle transient failures, and idempotency is what makes those retries safe.
  • The gold table is a lakehouse or warehouse table, so the source side of the push needs no copy.
Emolysis for Microsoft Fabric

Whatever the platform, the template is tested on DuckDB first. The platform notes describe each product in general terms, so check the details against the version you run.

Mistakes

Mistakes it prevents.

The failures that most often hit a pipeline of this shape in production. Each one is a case in the template's golden test or a rule in its design.

  1. Duplicate records after a retry

    A request that timed out may have succeeded, and the retry creates the record again. Each write carries an idempotency key, so the destination can recognize a repeat.

  2. Overwriting fields that the destination application owns

    A push that writes every column replaces what a salesperson typed into the CRM. The template writes only the fields on the allowed list.

  3. Ignoring rate limits

    A burst of requests is rejected, and a naive retry makes it worse. Batch size and rate limit are slots, and the test rejects a batch on purpose.

  4. Deletes pushed by accident

    A table that is rebuilt empty for a moment looks like a mass delete. Deletes are off unless a slot turns them on.

  5. Personal data sent to a tool that should not receive it

    Every mapped field leaves your platform. The explicit field mapping and allowed list make that list visible before the first push.

SQL sketch

The idea in SQL.

Select only what changed since the last push

Illustrative sketch in generic SQL. The call to the destination happens in the runner, not in SQL. State is recorded only after the destination confirms a batch.

Illustrative sketch · SQL
-- 1. Choose what to send: records whose payload differs from what was pushed last time.
WITH payload AS (
  SELECT
    CAST(a.account_id AS VARCHAR)                                AS external_id,
    a.health_score,
    a.segment,
    MD5(CAST(a.health_score AS VARCHAR) || '|' || a.segment)     AS payload_hash
  FROM gold.account_health AS a
)
SELECT
  p.external_id,
  p.health_score,
  p.segment,
  p.payload_hash,
  MD5(p.external_id || '|' || p.payload_hash) AS idempotency_key
FROM payload AS p
LEFT JOIN ops.reverse_etl_state AS s
  ON s.external_id = p.external_id AND s.destination = 'crm'
WHERE s.payload_hash IS DISTINCT FROM p.payload_hash;

-- 2. After the destination confirms a batch, record what was pushed (one row shown).
MERGE INTO ops.reverse_etl_state AS s
USING (SELECT 'crm' AS destination, '1042' AS external_id, 'b7f3c1' AS payload_hash) AS sent
  ON s.destination = sent.destination AND s.external_id = sent.external_id
WHEN MATCHED THEN UPDATE SET payload_hash = sent.payload_hash, pushed_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (destination, external_id, payload_hash, pushed_at)
  VALUES (sent.destination, sent.external_id, sent.payload_hash, CURRENT_TIMESTAMP);

Related

Shapes that work alongside it.

Real pipelines are several shapes in a row. These are the ones that most often sit next to this one.

09

Model and serve

Aggregation / gold

The source of a push: curated gold tables at a declared grain.

Explore Aggregation / gold
03

Sync and history

CDC merge

The opposite direction: apply changes from an application into the lakehouse.

Explore CDC merge
08

Model and serve

Dedupe / conform

To give the data a stable key and clean values before it leaves.

Explore Dedupe / conform

Glossary

Keep reading

FAQ

Questions about Reverse ETL.

What is reverse ETL?

Reverse ETL moves curated data from your data platform back into the business applications and operational databases that people and systems use, such as a CRM, a support tool or an application database. It is the reverse of loading data into the platform, which is why the gold layer is its source.

How does reverse ETL avoid duplicates when a request is retried?

Each record is written with a key that identifies it, and the destination either upserts on that key or recognizes the idempotency key of the request. The template also records a hash of what it last pushed for each record, so an unchanged record is not sent again. The golden test pushes the same batch twice and checks that the second push changes nothing.

What if the destination has a rate limit?

Batch size and rate limit are slots in the template, so the push stays under the limit and spreads a large first load over time. A request the destination rejects is safe to retry, because every write is idempotent.

Which data should not be sent to a business application?

Send only the fields the destination needs. Everything in the field mapping leaves your platform, so personal data and sensitive values deserve a deliberate decision. The template writes only the fields on the allowed list, which makes the list visible and reviewable before the first push.

In development · Not released

Test first, then deploy.

The Reverse ETL template is written for Databricks, Snowflake and Microsoft Fabric, and its golden tests pass on DuckDB. Nothing is released yet. Early access is by invitation, for teams that want to help shape it.

Apply for early accessAll ten shapes