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).

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.

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). You also have to select a Target table. The app will NOT write to this table, however the table is used to discover the available fields in the target.

Configure field mapping

Map fields from the source to the target.

Do not map to “id” fields in the target. Those fields are often FK relations in the target, use the “Relations” tab instead.

Configure 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.

Example:

In the above example, you can see that

• Source company id=1 is synced to target organization id=ABC.
• Source contact id=5 is synced to target person id=DEF.
• You cannot map source company_id directly to target organization_id.

Instead the Reverse ETL app will perform a lookup for the Relation. When the contact is synced, the Reverse ETL app will lookup the target id of the company. The lookup is done in a link table.

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.

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 _sdc_batched_at
FROM contacts