Replicating a database view is almost the same as replicating a database table. In this guide, we’ll cover the database integrations that support views and the additional steps required to replicate a database view.
Database views can be replicated from the following database integrations only:
Setting a view to replicate
To replicate a database view, follow these steps:
Step 1: Verify the database user's permissions
To replicate the view, the Stitch database user must have the appropriate level of 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:
Step 2: Select the view in Stitch
Note: Parent objects - like the database containing the view - must also be set to replicate. If they aren’t, set them to replicate before continuing.
- Click into the database integration from the Stitch Dashboard page.
- Navigate to the view you want to replicate.
- Click the icon next to the Sync Status column to set the view to replicate.
- Once a view has been set to replicate, the View Settings page will display.
Step 3: Define the view's Primary Key
Next, you’ll define the Primary Key setting for the view. There are two options:
- No Primary Key: The view will be replicated using Append-Only Replication. 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: 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-replication of the view. This is to ensure that there aren’t any gaps in the data.
Step 4: Define the view's Replication Method
The last step is to define the view’s Replication Method. Replication Methods tell Stitch how to extract data from a source table, which will directly impact your overall row usage.
The following Replication Methods are available for use with database views:
Key-based Incremental Replication: This method will only replicate new and updated data, based on the Replication Key column you define. A Replication Key is required to use this method. Refer to the Replication Key documentation for more info.
Full Table Replication: This method will replicate the view in full during every replication job.
Step 5: Save the view's settings
Once you’ve defined the view’s Primary Key(s) and Replication Method, click the Save Settings button.
During the next replication job, Stitch will replicate the view according to the settings you defined.