Replication for database views is supported for the following database integrations:
While the steps for syncing a database view are almost the same as those for syncing a table, there are some slight differences. To sync a database view, you'll need to:
- Verify the Stitch database user's permissions
- Set the view to sync
- Define the view's Primary Key setting
- Define the view's Replication Method
Verifying the Stitch Database User's Permissions
To replicate the view, the Stitch database user must have the correct permissions to access it.
If you don't see the view you want in the Stitch app, it may be because the Stitch user has insufficient permissions. Verify that the Stitch user's permissions and, if necessary, grant any that are missing.
For a refresher on the permissions Stitch needs, refer to the articles linked below:
Setting the View to Sync
Note that any top-level items - like the database containing the view - must also be set to sync. If they aren't, set them to sync before continuing.
- Click into the database integration from the Stitch Dashboard page.
- Navigate to the view you want to sync.
- Click the icon next to the Status column to set the view to sync.
- Once a view has been set to sync, the View Settings page will display.
Defining the View's Primary Key Setting
Next, you'll define the Primary Key setting for the view. There are two options:
- No Primary Key: If selected, the view will be replicated in an append-only fashion. This means that existing rows will not be updated, and any new rows will be appended to the end of the table.
- Custom Primary Key: If selected, the field or fields you define will be used as the view's Primary Keys. To add additional fields, click the Add button.
Note that you can change the Primary Key setting - including adding or removing fields to the Custom Primary Key - at any time, but doing so will require a full re-sync of the view. This is to ensure that there aren't any gaps in the data.
Defining the View's Replication Method
The last step is to define the Replication Method for the view:
- Incremental Replication: If selected, Stitch will only replicate new and/or updated data (based on the Replication Key you define) during every replication attempt. When using Incremental Replication, keep in mind that:
- Replication Keys are required to use Incremental Replication. When Stitch replicates your data, it will store the last recorded maximum value in the Replication Key column and compare it against the data source - not what's in your data warehouse - to identify new/updated data. Any row with a Replication Key value greater than or equal to the stored value is where Stitch will begin the next replication attempt.
- If no Primary Key is selected for the view, data will be added to the table in an append-only fashion.
- Full Table Replication: If selected, Stitch will replicate the entire contents of the view on every replication attempt. As this can increase latency and quickly use up your row limit, it's the most inefficient way to use Stitch.
After you've finished defining the view's settings, click Update Settings.