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

# Lightning sync engine

> The Lightning sync engine runs change data capture inside your data warehouse, so syncs on large models are faster and more reliable.

> [!NOTE]
>
> The Lightning sync engine was previously known as warehouse planning. Both
> terms describe the same feature.

## Before you begin

* A [supported source](#supported-sources) and write access to your data warehouse.

## How the Lightning sync engine works

Hightouch identifies the incremental changes in your data model so that syncs send only the necessary updates to your destinations. For every sync, Hightouch computes what rows to send by pulling all rows within your data model and comparing them with the last sync. This process is called **change data capture** (CDC) or **diffing.** You can find more details in the [change data capture overview](https://docs.hightouch.com/syncs/cdc.md).

By default, the change data capture computation and file storage happens on Hightouch managed infrastructure. This can be slow for large models that take a long time to query.

With the **Lightning sync engine**, Hightouch computes change data capture **directly in your warehouse**. It stores previously synced data into a special schema managed by Hightouch. Customers with extensive datasets requiring high performance syncs typically use the Lightning sync engine.

![Basic sync architecture diagram](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/get-started/basic-sync-engine.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=50e56da1e904b93a63285f72fa692115)

![Lightning sync engine architecture diagram](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/get-started/lightning-sync-engine.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=e58adf346739b6d70fda353fd44476be)

> [!TIP]
>
> Hightouch recommends using the Lightning sync engine for [supported
> sources](#supported-sources) when syncing more than 100 thousand rows of data.

### Engine comparison

The **Lightning sync engine** requires granting write access to your data warehouse, which makes its setup more involved than the Basic sync engine. However, it is more performant and reliable than the Basic engine. This makes it the ideal choice to guarantee faster syncs, especially with large data models. It also unlocks features that are not available with the Basic engine, including [Journeys](https://docs.hightouch.com/customer-studio/journeys.md), [Sampling](https://docs.hightouch.com/customer-studio/sampling.md), [Warehouse Sync Logs](https://docs.hightouch.com/syncs/warehouse-sync-logs.md), [Audience Snapshots](https://docs.hightouch.com/customer-studio/audience-snapshots.md), [Match Booster](https://docs.hightouch.com/match-booster/overview.md), and [Identity Resolution](https://hightouch.com/platform/identity-resolution).

| Criteria | Basic sync engine | Lightning sync engine |
| - | - | - |
| Performance | Slower | Quicker (up to 100 times faster) |
| Ideal for large data models (over 100 thousand rows) | No | Yes |
| Reliability | Normal | High |
| Resilience to sync interruptions | Normal | High |
| Extra features | None | [Journeys](https://docs.hightouch.com/customer-studio/journeys.md), [Sampling](https://docs.hightouch.com/customer-studio/sampling.md), [Warehouse Sync Logs](https://docs.hightouch.com/syncs/warehouse-sync-logs.md), [Audience Snapshots](https://docs.hightouch.com/customer-studio/audience-snapshots.md), [Match Booster](https://docs.hightouch.com/match-booster/overview.md), [Identity Resolution](https://hightouch.com/platform/identity-resolution), experiment [holdout group logs](https://docs.hightouch.com/customer-studio/experiments.md) |
| Ease of setup | Simpler | More involved |
| Location of change data capture | Hightouch infrastructure | Warehouse schemas managed by Hightouch |
| Required warehouse permissions | Read-only | Read and write |
| Ability to switch | You can move to the Lightning engine at any time | You can't move to the Basic engine once Lightning is configured |

### When the Lightning sync engine is required

The Basic sync engine supports standard audience syncs, but several Hightouch features require the Lightning sync engine. Enable the Lightning sync engine if you plan to use any of the following:

* [Journeys](https://docs.hightouch.com/customer-studio/journeys.md)
* [Sampling](https://docs.hightouch.com/customer-studio/sampling.md)
* [Warehouse Sync Logs](https://docs.hightouch.com/syncs/warehouse-sync-logs.md)
* [Audience Snapshots](https://docs.hightouch.com/customer-studio/audience-snapshots.md)
* Experiment [holdout group logs](https://docs.hightouch.com/customer-studio/experiments.md)
* [Match Booster](https://docs.hightouch.com/match-booster/overview.md)
* [Identity Resolution](https://hightouch.com/platform/identity-resolution)

If you are setting up Customer Studio for anything beyond basic audience syncs, Hightouch recommends enabling the Lightning sync engine during source setup so you don't need to revisit your sync engine choice later.

## Supported sources

The Lightning sync engine works with the following sources:

* [Amazon Athena](https://docs.hightouch.com/sources/amazon-athena.md)
* [Amazon Redshift](https://docs.hightouch.com/sources/amazon-redshift.md)
* [Azure Synapse](https://docs.hightouch.com/sources/synapse.md)
* [ClickHouse](https://docs.hightouch.com/sources/clickhouse.md)
* [Databricks](https://docs.hightouch.com/sources/databricks.md)
* [Google BigQuery](https://docs.hightouch.com/sources/google-bigquery.md)
* [Greenplum Database](https://docs.hightouch.com/sources/greenplum.md)
* [Lakebase](https://docs.hightouch.com/sources/lakebase.md)
* [Microsoft Fabric](https://docs.hightouch.com/sources/fabric.md)
* [PostgreSQL](https://docs.hightouch.com/sources/postgresql.md)
* [Snowflake](https://docs.hightouch.com/sources/snowflake.md)
* [Snowflake Iceberg](https://docs.hightouch.com/sources/snowflake-iceberg.md)
* [Trino](https://docs.hightouch.com/sources/trino.md)

## Enable the Lightning sync engine

When first setting up one of the [supported sources](#supported-sources), Hightouch prompts you to **Choose your sync engine**. Select **Lightning sync engine**, marked **Recommended**.

![The Choose your sync engine step with Lightning and Basic options](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/syncs/lightning-sync-engine/choose-sync-engine.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=8756f7c3c826fe754d9e4218cf70fee2)

You can set up the Lightning sync engine at this point or continue with the Basic sync engine and [upgrade to the Lightning engine](#migrate-from-basic-sync-engine) later. Once you've configured the Lightning engine, you can't switch back to the Basic engine.

Selecting the Lightning sync engine surfaces a SQL snippet that provisions a dedicated user and role and creates the [warehouse schemas](#warehouse-schemas) Hightouch needs. You must:

1. Grant Hightouch write permission to your data warehouse.
2. Create the warehouse schemas for Hightouch to use.

Run the snippet from the source's in-app instructions, editing the placeholders for your warehouse first.

![The permissions snippet that provisions Hightouch's warehouse user, role, and schemas](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/syncs/lightning-sync-engine/sync-engine-permissions.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=096dd8bef4c020204187f9feb1267685)

### Warehouse schemas

Hightouch requires you to create two schemas:

* `hightouch_planner`: the internal schema that powers the Lightning sync engine. Hightouch manages it, and you don't query it directly.
* `hightouch_audit`: the public schema that holds [Warehouse Sync Logs](https://docs.hightouch.com/syncs/warehouse-sync-logs.md) and other tables you can query.

To rename these or store them in a different database, expand **Override schema names and locations** during setup.

> [!NOTE]
>
> Storing the Lightning schema in a different database or project can limit some
> sources to SQL models. See your [source's documentation](#supported-sources)
> for details.

![Overriding the internal and public schema names during setup](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/syncs/lightning-sync-engine/override-schema-names.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=6cbb83faf6ef8104241349edcb6209c0)

Whenever a sync that uses the Lightning sync engine runs, Hightouch creates two new tables in the `hightouch_planner` schema: one to log the model's query results (the `_plan` table) and one to log rows rejected during the sync (the `_rejections` table).
Hightouch uses these tables to perform [change data capture](#how-the-lightning-sync-engine-works) and plan which rows to sync.

Hightouch only keeps the two most recent pairs of tables to compute the diff for the current sync.
In other words, Hightouch [doesn't maintain historical records](https://docs.hightouch.com/syncs/cdc.md#faq) of these tables, and any given sync never has more than the two most recent pairs of tables.
Because these tables and their names change with every sync run, Hightouch requires permission to write to the entire `hightouch_planner` schema rather than specific tables.

> [!NOTE]
>
> You can learn more about the `hightouch_audit` schema on the [Warehouse Sync
> Logs](https://docs.hightouch.com/syncs/warehouse-sync-logs.md#schema) page.

#### Table removal

Removing tables from the `hightouch_planner` and `hightouch_audit` schemas isn't recommended:

* `hightouch_planner`: these tables are required for [change data capture](#how-the-lightning-sync-engine-works) to work correctly; if you delete these tables, you need to trigger a [full resync](https://docs.hightouch.com/syncs/resync-reset-clear.md#full-resync) to fix your syncs.
* `hightouch_audit`: removing tables from the `hightouch_audit` schema would mean deleting the [Warehouse Sync Logs](https://docs.hightouch.com/syncs/warehouse-sync-logs.md) related to a sync's prior runs, which are useful for historical analysis.

If your [source](#supported-sources) is located in the European Union, you might want to avoid having PII older than 30 days in your data warehouse, in accordance with the [GDPR](https://en.wikipedia.org/wiki/General_Data_Protection_Regulation).
In this case, we recommend running **all** syncs in your workspace at least once every 15 days.
This ensures that Hightouch refreshes the tables in the `hightouch_planner` schema and the **Snapshot** table in the [`hightouch_audit` schema](https://docs.hightouch.com/syncs/warehouse-sync-logs#schema), removing all older PII before it reaches the 30-day threshold.

> [!WARNING]
>
> Historical data is never automatically cleared out from the **Changelog**
> table in the `hightouch_audit` schema. If required, you need to manually
> remove older PII from this table.

> [!NOTE]
>
> The **Sync runs** table in the `hightouch_audit` schema doesn't contain PII.

## Migrate from Basic sync engine

You can migrate to the Lightning sync engine by going to the **Configuration** tab of any previously setup [supported source](#supported-sources).

1. From the source's **Configuration** tab, select **Lightning sync engine** under **Choose your sync engine** .
2. Follow the source-specific in-app instructions to grant Hightouch write permissions to your warehouse and create the schemas for Hightouch to use.
3. Click **Save changes**.
4. Test your connection to ensure setup is correct.

![Migrating an existing source to the Lightning sync engine from its Configuration tab](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/workspace-management/permissions/lightning-engine-migration.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=4c60724d0e894cd254aac3730fa0b714)

If you enable the Lightning sync engine on an existing source, Hightouch migrates any syncs on the Basic engine to the Lightning sync engine on the next run. Any previously synced data won't be unnecessarily resynced since the existing [change data capture](#how-the-lightning-sync-engine-works) data is copied over to your warehouse schemas. In most cases you don't need to trigger [full resyncs](https://docs.hightouch.com/syncs/resync-reset-clear.md#full-resync) or [reset change data capture (CDC)](https://docs.hightouch.com/syncs/resync-reset-clear.md#reset-cdc), unless you [encounter errors](#migration-tips).

> [!NOTE]
>
> The first run after migrating to the Lightning sync engine can take longer
> than usual, as it uses a special migration sync engine to create the necessary
> [schemas](#warehouse-schemas) in your warehouse.

### Migration tips

Follow these tips when performing the migration:

* The Lightning sync engine requires your [primary key to be unique](#primary-key-is-not-unique) for every model using the source. If you're unsure if your primary key is unique, check before migrating.
* If you have a large number of syncs to migrate, please
  [reach out](#pylon-chat) so that we can help you migrate incrementally.
* If you hit any errors, you can often fix them with a [full resync](https://docs.hightouch.com/syncs/resync-reset-clear.md#full-resync). Ensure your sync meets the [full resync prerequisites](https://docs.hightouch.com/syncs/resync-reset-clear.md#before-you-begin) before doing so. Otherwise, you can [reset change data capture (CDC)](https://docs.hightouch.com/syncs/resync-reset-clear.md#reset-cdc).

## Common errors

### Primary key is not unique

The Lightning sync engine requires that every row in your model has a unique value for its [primary key](https://docs.hightouch.com/models/creating-models.md#unique-primary-key-requirement). This is because the primary key is used to uniquely identify each row, and for example, whether it failed previously.

Models with duplicate primary keys can be deduplicated in SQL with the `ROW_NUMBER()` function.

For example:

```sql theme={null}
WITH your_data_model AS (
  // copy/paste your model here
)

WITH your_data_model_with_rank AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY your_primary_key) as rank FROM your_data_model
)

SELECT * FROM your_data_model_with_rank WHERE rank=1
```

The preceding example de-duplicates your model by *partitioning* on the primary key. It chooses an arbitrary record from your duplicates. You can use `ORDER BY` or `WHERE` to filter and select which data from the duplicates you want to keep.