Reverse ETL is the concept of writing data back into business applications, using the data warehouse as a source. This approach allows to use curated clean data from the data warehouse to keep data in business applications in sync. For example you could sync customers and invoices from your ERP to your accounting software (using the data warehouse as in-between).

Click here to watch a 7 minute how-to video on Reverse ETL in Peliqan

The entire flow consists of 2 phases:

ETL (or ELT): ERP customer & invoicing data to the data warehouse
Reverse ETL: customer & invoicing data from the data warehouse to the accounting software

Phase 1 is done by adding a connection in Peliqan to the source system. This will automatically set up an ELT pipeline and sync the data into the data warehouse.

Phase 2 is done via the Peliqan Reverse ETL app. Install the app in your Peliqan account, configure the Reverse ETL flow and set a schedule on the app for ongoing syncs. All the steps of phase 2 are described below.

☝

You can also implement Custom Data Syncs (Custom Reverse ETL flows) in Peliqan using low-code Python scripts, based on one of the provided templates. For more information visit:

Custom data syncs

Install the Reverse ETL app

In Peliqan, go to the Build section (rocket icon in left).

Find the Reverse ETL app and click on Install Template.

Bookmark the app to your Peliqan homepage.

Open the app on your Peliqan homepage.

Add syncs

In the app, in the left pane, add a sync for each object type, for example a sync for “Companies” and a sync for “Contacts”.

You can change the ordering of Syncs under “Manage sync”. Make sure to sync parent records first, before syncing child records. For example Contacts are linked to a parent Company, so Companies need to sync first.

Sync settings

For each sync, choose the sync mode (e.g. “Insert & Update records”) and enable incremental sync, if the source data has a timestamp. Peliqan pipelines always a add a timestamp _sdc_batched_at so this is the default timestamp source column to use.

Add source

Select a source table. See below on how to write a “source query” in order to transform data and map it to the target model. When you have created a source query, make sure to select this as the source table.

Add target

Select a target. You have to configure a Target API Object, this will be used to perform writeback to the target (using Peliqan’s writeback functions in the selected Connector).

Generic Target Object

If you cannot find the Target Object that you need, you can select “Object”, if it is available in the target connector that you selected. Example:

This is a generic writeback, that can be used for multiple object types. When doing so, you will have to set the object name in the Field Mapping tab. To do so, click on the Pencil icon on the left, and enter the name of the object manually, for example “organizations” in the screenshot below (in order to insert/update organizations in the target Pipedrive):

Configure field mapping

Map fields from the source to the target.

Do not use “id” fields in the target (column on the right). Those fields are often FK relations in the target, use the “Relations (FK)” tab instead.

Link tables

The Reverse ETL app creates a link table under schema link_tables for each sync. The link table keeps track of source ids and target ids. Example of a link table:

source_id	target_id	status	First sync	Last sync
100	567	Success	…	…
200	890	Success	…	…

Configure relations (foreign keys, parent relations)

If you have an FK (foreign key) in the source, which is a relation to a parent object, you probably need to map that to another FK field in the target. You cannot map FK fields directly because the parent object will have another id in the source and in the target.

Here is an example where first sync Companies, then we sync Contacts. Each Contact (child) is linked to a Company (Parent):

In the above example, you can see that

Source Company id=100 is synced to target Partner id=789.
Source Contact id=500 is synced to target Contact id=321.
You cannot map source company_id directly to target partner_id.

Instead the source FK (500) must be translated to the target FK (789) when the Contact is synced. This is depicted as “Step 3” in the drawing above, in green.

The Reverse ETL app will perform this type of lookup when you configure the FK’s for source and target under “Relations”:

The lookup to find the FK for the tarfget, is done in the link table of the parent object (green line in the above drawing).

Test the Reverse ETL sync

You can test the sync under “Data” by selecting one source record and performing a manual sync. If there are errors, adjust the field mapping and test again.

View the status of a sync

View the status of each sync under “Status”. This shows the link table which contains all source ids that have been synced, the timetamps and errors if any.

Set a schedule

Once the syncs are working fine, you can add a schedule to the app. Go to the Build section in Peliqan (rocket icon in left pane), open the app and set a schedule. Make sure to use incremental syncs and configure a daily or hourly sync.

Writing a source query (source view)

You can write a source query (view) to prepare data for mapping to the target. Write the query first, and next use it as the source table in the Reverse ETL app.

Examples of transformations that are typically handled in a source query:

Transform date formats from the source format to the target format.
Casting of data types from the source to the target data type
Combining data from multiple source tables into a single source query
Mapping of values from source to target
Adding a columns with a static value for fields that need to have a static value in the target

Below is an example where data from a source table contacts is mapped to a target table people.

Example source table contacts:

id    firstname       lastname      country_code     _sdc_batched_at
1     Bob             Davis         FR               20205-01-01 00:00:00
2     Anne            Crome         UK               20205-01-01 00:00:00
3     ...

Example target table people (id not shown, will be set automatically on insert):

fullname        country          category
Bob Davis       France           VIP
Anne Crome      United Kingdom   VIP

Example query sync_contacts_source to use as source table in the Reverse ETL app:

SELECT
	  id,                                              -- make sure to include id
	  firstname || ' ' || lastname as fullname,        -- concatenate
    CASE                                             -- value mapping
    	WHEN country_code = 'FR' THEN 'France'
    	WHEN country_code = 'UK' THEN 'United Kingdom'
    END AS country,
    "VIP" AS contact_category,                       -- static value
    _sdc_batched_at                                  -- make sure to include _sdc_batched_at
FROM contacts

Nested data

Nested data can be created in a source query and mapped to a JSON field in the target. An example is invoice lines for invoices.

☝

Note: you can often also sync invoice lines (and other child objects) as a separate Object Type. Sync the invoices first, next sync the invoice lines with the invoice_id as a FK relation.

Below is an example source query for sales invoices from Odoo, which creates a JSON array with invoice line objects in one column. This column can be mapped to a field “invoice lines” in the target (e.g. Exact Online). Make sure to use the correct keys in the invoice line objects, as needed by the target.

SELECT
  i.id,
  i.ref,
  i.date,
  i.partner_id,
  i.amount_total,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'Quantity', l.quantity,
      'Name', l.name
    )
  ) AS invoice_lines_json
FROM odoo.account_move AS i
LEFT JOIN odoo.account_move_line AS l
  ON i.id = l.move_id
WHERE
  i.move_type = 'out_invoice' AND line_ids <> '[]'
GROUP BY
  i.id,
  i.ref,
  i.date,
  i.partner_id,
  i.amount_total,

Example result (some columns omitted):

id	date	invoice_lines_json
1	2025-01-01	[ { “Quantity”: 4, “Name”: “Shoes” }, { “Quantity”: 1, “Name”: “Socks” } ]
2	2025-01-02	[ { “Quantity”: 3, “Name”: “Shoes” } ]