Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Postgres: Data Replication & Table Structural Changes

Important!
Please note that our Postgres destination is currently in open beta. The information in this article is subject to change.

When new data is detected and ready to be loaded into your data warehouse, Stitch does a few things depending on the replication method being used.

Replication Staging

Each table inside the integration schemas in your data warehouse contain a few columns prepended with _sdc:

  • _sdc_batched_at
  • _sdc_received_at
  • _sdc_sequence
  • _sdc_table_version

Stitch uses these columns to replicate your data. They're only used by us, so don't worry too much about the values in them.

Note that if any columns in your data source are prepended with _rjm or _sdc, Stitch will NOT replicate them to your data warehouse. Stitch reserves all columns with this naming convention for internal use.

Full Table Replication

During the initial replication of a table using Full Table replication, you might see what looks like an “incomplete” table. This is because Stitch is still processing and replicating your data. Once the replication is complete, the table will be marked with a version 1 stamp.

During the next replication attempt, a new version of the table will be created. Each batch is marked with a version stamp as Stitch picks it up. At this point:

  1. All data from the existing table, or prior version, is deleted.
  2. All data from the new version is copied into the new table in one transaction.

Incremental Replication

If using Incremental Replication, new and updated data will be appended to the same table version.

The only time Stitch will create a new version for an incrementally replicated table is if the underlying structure has changed. In this case, an ALTER command is issued to change the table’s structure.

Handling Structural Changes

When the data can’t be loaded losslessly into a realized table, Stitch will modify the structure of the table. This can happen for a few reasons:

  1. New columns are added to the table.
  2. Columns are removed from the table.
  3. Multiple data types exist in a single column. In this case, the column will be "split."

What happens when data can’t fit into a table?

You’ll experience some missing data on your end. To troubleshoot, we recommend taking a look at some of the more common causes for dropped (or missing) data:

  • A Primary Key for the table is missing.
  • The table name is longer than Postgres’s 63 character limit.
  • The column name is longer than Postgres’s 59 character limit.
  • Stitch's permissions to your data warehouse are revoked.
  • You have column names with __string, __bigint, etc. These are reserved names for Stitch.

If you’re stumped or need some help pinpointing the issue, please reach out to support.

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!