> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sync.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Reverse ETL

Traditional ETL (extract, transform, load) processes copy data from source systems to a centralized data store for analysis and reporting. *Reverse ETL* does the opposite--it moves data from a central repository back to your operational systems. Reverse ETL enables you to use results from data analytics to improve your operational processes, enhance customer experiences, or facilitate decision making.

The basic process for reverse ETL is as follows:

* Extract data **from a central data warehouse or data lake** where that data has been aggregated and transformed for analytical purposes.
* Transform the extracted data to make it suitable for consumption by your systems. Transformations can include reformatting, filtering, or enhancing your data.
* Load the transformed data back into operational databases, applications, or other systems where you can use it for various purposes (for example, updating records or triggering workflows).

In CData Sync, reverse ETL capability is supported for the following sources:

* [Amazon Redshift](https://docs.sync.cdata.com/26.4/en/connections/sources/amazon-redshift-source.md)
* [Google BigQuery](https://docs.sync.cdata.com/26.4/en/connections/sources/google-bigquery-source.md)
* [Microsoft SQL Server](https://docs.sync.cdata.com/26.4/en/connections/sources/sql-source.md)
* [MySQL](https://docs.sync.cdata.com/26.4/en/connections/sources/mysql-source.md)
* [Oracle](https://docs.sync.cdata.com/26.4/en/connections/sources/oracle-source.md)
* [PostgreSQL](https://docs.sync.cdata.com/26.4/en/connections/sources/postgresql-source.md)
* [Snowflake](https://docs.sync.cdata.com/26.4/en/connections/sources/snowflake-source.md)

> [!NOTE]
>
> Source databases that you can use for reverse ETL capability are restricted to those for which the **Delta Snapshot** replication type is implemented (that is, source databases that accept the SQL EXCEPT statement).

In Sync, this capability is supported for the following destinations only. (**Reverse ETL** is the only replication type available for these destinations).

* [HubSpot](https://docs.sync.cdata.com/26.4/en/connections/destinations/hubspot-destination.md)
* [Kintone](https://docs.sync.cdata.com/26.4/en/connections/destinations/kintone-destination.md)
* [Microsoft Dynamics 365](https://docs.sync.cdata.com/26.4/en/connections/destinations/microsoft-dynamics-365-destination.md)
* [Sage Intacct](https://docs.sync.cdata.com/26.4/en/connections/destinations/sage-intacct-destination.md)
* [Salesforce](https://docs.sync.cdata.com/26.4/en/connections/destinations/salesforce-destination.md)
* [Veeva Vault](https://docs.sync.cdata.com/26.4/en/connections/destinations/veeva-vault-destination.md)
* [Zoho CRM](https://docs.sync.cdata.com/26.4/en/connections/destinations/zoho-crm-destination.md)

## Understanding the Reverse ETL Process

Reverse ETL works by querying your data warehouse for changes and writing the results of that query to your destination. When you run the reverse ETL job for the first time, the application creates an initial snapshot of the source table in your data warehouse and loads the results into your destination. During subsequent runs, CData Sync creates a new snapshot and compares it against the previous snapshot to capture the changes. Then, those changes are loaded into the destination.

Using the **Delta Snapshot** feature, Sync maintains an error table for logging rows that do not insert or update correctly, and it enables you to see individual errors at the row level. This feature also cleans up any failed rows from the previous snapshot.

With reverse ETL, you can choose from four modes to transfer your data:

* **Insert:** Select this mode to insert new records into your source. For **Insert** mode, your source table must have a unique primary key. Each time you take a new snapshot, that key enables CData Sync to ensure that you are inserting new identifiers (Ids).

* **Upsert:** Select this mode both to update **existing** records and to insert new records into your source. For **Upsert**, you must define a key that matches a record from your source table to your destination table.

  When you run a job in **Upsert** mode, you can also configure **Deletion Behavior** settings by navigating to **Jobs > *JobName* > Advanced > Edit Replicate Options > Deletion Behavior**. These settings control how Sync handles records that existed in the previous snapshot but are missing from the latest snapshot.

  * **Skip Deleted (default):** No changes are made to the destination.
  * **Soft Delete:** Sync updates the destination record by setting to NULL all fields that are not primary keys.
  * **Hard Delete:** Sync permanently removes the record from the destination.

  Sync retries failed delete operations on subsequent runs so that your destination remains consistent with the source snapshot.

* **Update:** Select this mode to update **existing** records in your source. **Update** mode uses the Salesforce Id or a Microsoft Dynamics 365 alternate key to match records in your source and destination tables. You must select a source column to map to the Salesforce Id or the Dynamics 365 alternate key.

* **Delete:** Select this mode to remove records from your destination. When you run a reverse ETL job using **Delete** mode, Sync compares the latest snapshot with the previous snapshot and deletes any destination records that appear in the snapshot results.

## Creating a Reverse ETL Job

To create a reverse ETL job, you need to define job parameters and then map required and optional columns as well as any keys that are necessary, as follows:

1. Select **Jobs > Add Job > Add New Job**. This step opens the **Add Job** dialog.

2. Enter a job name.

3. Select your destination (**Salesforce** or **Microsoft Dynamics 365**) first. Then select your source, as shown in this example:

   ![Add Job dialog for reverse ETL](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_creating-a-reverse-etl-job_1.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=b0281da7e49316ad7d572bcf04a37b67)

   After you enter a source and a destination, the **Add Job** dialog expands to include a **Type** and a **Transfer Mode** section, as shown above. The **Type** section indicates that your job is a reverse ETL job, and the **Transfer Mode** section enables you to select the type of data transfer that you want to perform. For details about the transfer modes, see [Understanding the Reverse ETL Process](#understanding-the-reverse-etl-process).

4. Click **Next**.

5. Select your schema, a source table, and a destination table in the **Configure Tables** dialog.

   ![Configure Tables dialog](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_creating-a-reverse-etl-job_2.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=f4d7beb09a27dbec351c61fcfe9bbe5b)

6. Click **Next**. 

7. Configure your columns and keys in the **Configure Mapping** dialog, as follows:
   1. Choose your key (for **Upsert** and **Update** modes only).
   2. Map any required columns.
   3. Add any additional columns that you want by clicking **Add Column** at the bottom of the dialog.

8. Click **Add Job** to create your new job. After the job is created, Sync displays your table mappings on the **Column Mapping** tab, as shown below:

   ![Column Mapping tab after job is created](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_creating-a-reverse-etl-job_3.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=9fe05fdfa331f39dc044d12e1a8b8e1c)

After you add your job, you can manage it as described in the next section.

## Managing Your Job

Once you add your reverse ETL job, you can run and manage it like any other job in Sync from the **Jobs** page. When you click your job in the application, Sync opens your job page with the standard tabs (**Overview**, **Column Mapping**, **Job History**, **Events**, and **Advanced**). The following sections explain how to manage settings and mappings for your reverse ETL job on the **Overview**, **Column Mapping**, and **Advanced** tabs.

### Job Overview

The **Overview** tab for a job displays a summary of your settings, including the job type and transfer mode. In the example below, the job type is **Reverse ETL** and the transfer mode is **InsertMode**.

![Overview tab for a reverse ETL job](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_job-overview.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=a408960feb86a3fb3d3e33537c68a58d)

To edit your settings, click the **Edit** button. The only change that you can make in the **Edit Settings** dialog is to select a different verbosity level for your log file.

### Mapping Columns

[As explained earlier](#columnmap), Sync automatically opens the **Column Mapping** tab when you add a new job. This page shows you the mappings between your source and the Salesforce destination for both the required and optional columns. In addition, the page shows the mapping for keys for **Insert** mode.

![Column and table mapping](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_mapping-columns.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=7d7f8d5f932838ba5283a30c65756f78)

You can map your columns manually, as explained earlier in [step 7](#columnmap2) of "Creating a Reverse ETL Job." You can also use the Sync application's auto-mapping feature, which is especially useful when you have a large number of column fields.

After you create a job, you can edit both the column mappings and your source and destination tables. The following sections explain how to automap columns and how to edit column mappings and the tables.

#### Auto-Mapping Columns

When you create a job and map columns in the **Configure Mapping** dialog, you can see that the **Auto Map** button is disabled. That button is enabled after you select the key columns that you want to use for both the source and destination, as shown below:

![Configure Mapping dialog with Auto Map button enabled](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_automapping-columns_1.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=c698e8f590c5edc1eb90a83960aabba3)

In the example above, the dialog shows only two mappings in the **Required** section, which are easy enough to map manually. However, manual column mapping can be time-consuming when you have large numbers of columns. In such cases, using the **Auto Map** feature speeds up the process by quickly mapping the source and destination columns for you. Auto mapping can also reduce human errors that might occur from mismatching any columns.

> [!NOTE]
>
> For tables that do not have a key or required column, the **Auto Map** button is disabled. You must add a column manually to enable the button.

To use the auto-map feature:

1. Click the **Auto Map** button (after you select the key columns). When you click the button, Sync scans all unmapped source columns (both required and optional) and matches them automatically to destination columns with the same name, regardless of letter casing.

   ![Configure Mapping dialog after auto-mapping](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_automapping-columns_2.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=28b16fa42b912177f6fb9ad7b4ae1f77)

2. Click **Add Job** to create your new job. After the job is created, Sync displays your table mappings on the **Column Mapping** tab.

#### Editing Column Mappings and Tables

To edit your mappings:

1. Select **Edit > Edit Mapping** on the **Column Mapping** tab.

   ![Edit Mapping option](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_editing-column-mappings-and-tables_1.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=5aaa1ef45d939a2fd8318a5989495bf2)

2. Add new columns or change any columns and keys that you want.

3. Click **Save** to save your changes and return to the **Column Mapping** page.

To edit your source or destination table:

1. Select **Edit > Edit Tables**. This selection takes you to the **Edit Tables** dialog.

   ![Edit Tables dialog](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_editing-column-mappings-and-tables_2.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=abd97bf71b76b96d72bce7b2f11c57b9)

2. Update the source or destination table. For example, select **User** for both the destination and source. Click **Next**.

3. Rebuild the column mapping for your job in the **Edit Mapping** dialog.

4. Click **Save** to save your changes and return to the **Column Mapping** page.

### Advanced Options

The **Advanced** tab contains advanced replication options for your job. Currently, for reverse ETL jobs, the tab contains one option, **Reset Strategy**. Sync uses primary keys to identify when rows in a table are added, modified, or deleted. For reverse ETL jobs, the **Reset Strategy** replication option defines how you should manage situations where the key is changed in the query.

![Reset Strategy option on the Advanced tab](https://mintcdn.com/cdata-sync/FaXQgwCWYQjGfVUG/26.4/images/Reverse-ETL_advanced-options.png?fit=max\&auto=format\&n=FaXQgwCWYQjGfVUG\&q=85\&s=02e6633f9efff6f509aa43dbb1182752)

This option provides two settings:

* **Full Reset:** When you use this setting, Sync replaces the snapshot table with a full replication of the source table, including previously replicated rows. Avoid using this option with **Insert** mode because it might create duplicate entries.
* **Sparse Reset:** With this setting, Sync updates the snapshot table to match schema changes without altering existing rows or duplicating entries. As such, **Sparse Reset** is the preferred option for use with **Insert** mode.

You can also enter a comma-separated list of replication options in the **Additional Options** text box below the **Reset Strategy** drop-down list.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.