---
title: Syncing a lookup table from an external database
slug: how-tos/syncing-a-lookup-table-from-an-external-database
createdAt: 2026-10-30T09:00:00.000Z
---

*Available from version 8.3 and later.*

A lookup table can take its rows from a query on an external database. Historian runs the query on an interval and replaces the rows of the table with the result.

:::hint{type="info"}
Syncing lookup tables is experimental and off by default. To turn it on, add `lookuptablesync` to the `FEATURE_TOGGLES_ENABLE` environment variable of the Historian server (comma-separated if it already holds other names) and restart Historian.
:::

## Before you start

- Create a connection to the external database. See [Creating an external database connection](https://docs.factry.io/how-tos/wFSr-creating-an-external-database-connection).
- You need the `MANAGE_LOOKUP_TABLES` and `READ_EXTERNAL_DATABASES` privileges, and the lookup tables feature in your license.

The query runs unattended on every sync, under the account of the external database connection. Give that connection an account that can only read.

## Create a synced lookup table

Whether a table is synced is chosen when you create it. You cannot change it later.

1. Click **Lookup tables** in the main menu.
2. Click **Create lookup table**.
3. Enter a **Name** and, optionally, a **Description**.
4. Set **Source** to **External database**.
5. Under **Sync**, fill in:
   - **External database**: the connection to read from.
   - **Sync interval**: how often the rows are read again, as a duration such as `5m` or `1h`. The default is `5m`, the minimum is `1m`.
   - **Query**: the query that returns the rows.
   - **Watermark query (optional)**: lets a scheduled sync skip a run when the data has not changed (see below).
6. Click **Preview**. Historian runs the query and shows how many rows it returns. The columns of the table are taken from the query result.
7. Under **Columns**, check the type of each column, and remove the columns you do not need. The column names come from the query and cannot be changed.
8. Click **Save**. Historian runs the first sync right away.

You cannot save a new query until you have previewed it. A query that returns more than 500,000 rows is refused.

The rows of a synced table cannot be edited or imported. They only change through the sync. You can still export them.

## Skip unchanged data with a watermark query

A watermark query is a cheap query that returns one row, whose value changes whenever the data changes. Before a scheduled sync, Historian runs the watermark query. When its result is the same as at the previous sync, the sync is skipped and shows as **Unchanged**.

The watermark query must also notice deleted rows. `max(updated_at)` alone does not, so combine it with a count, for example:

```sql
SELECT count(*), max(updated_at) FROM product_specs
```

If the watermark query fails, every sync reads the rows.

## Sync now

To read the rows right away, select the table and click **Sync now** at the top of the panel. **Sync now** ignores the watermark and always reads the rows. Save or cancel your changes first.

## Check the sync status

The **Sync status** section of a synced table shows:

- **Last run**: when the last sync ran, and whether it **Synced**, was **Unchanged** or **Failed**.
- **Last synced**: when the rows were last replaced.
- **Rows**: the number of rows the last successful sync wrote.
- **Watermark**: the result of the last watermark query.
- **Error**: why the last sync failed, and how many times in a row it failed.

When a sync fails, the table keeps its rows. The table is listed under **Lookup tables whose sync failed** in the issues panel on the homepage until a sync succeeds.

## Change the query

1. Select the table and click **Edit**. Open the **Settings** tab.
2. Change the query or the external database, and click **Preview** again.
3. Click **Save**.

Saving a changed query or changed columns runs a sync right away. When the columns change, the old rows are dropped and read again for the new columns.

## When syncing is turned off

If you remove `lookuptablesync` from `FEATURE_TOGGLES_ENABLE`, synced tables stop syncing and keep their rows. Users can then edit them like any other lookup table. The sync settings are kept, and syncing resumes when you turn the feature on again.

## More information

- [Lookup tables](https://docs.factry.io/how-tos/lookup-tables)
- [Creating an external database connection](https://docs.factry.io/how-tos/wFSr-creating-an-external-database-connection)
