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.

Identifying & Resolving Record Rejections

From time to time, Stitch may run into problems when attempting to load data into your data warehouse. When data is deemed incompatible by the data warehouse, the record will be "rejected" and Stitch will log it in a rejected records log.

In this article, we’ll cover:

Reasons for Record Rejection

Reasons for rejection will depend on the type of data warehouse you’re using, as each has its own data requirements and restrictions. For a detailed rollup of how each destination handles data - including what situations will result in rejected records -  refer to the Expected Data Loading Behavior doc for the destination you’re using:

There are some common causes for rejection, however:

  • Column names contain data type suffixes (ex: __bigint), which are reserved by Stitch
  • Table and/or column names contain _sdc or _rjm prefixes, which are reserved by Stitch
  • Timestamp data that falls outside the range supported by the data warehouse
  • Integer data that falls outside the range supported by the data warehouse

Rejected Records Log

In every integration schema created by Stitch is a table called _sdc_rejected which acts as a log for rejected records.

This table contains information about why a record was rejected, the date of the rejection, and the table in the integration schema that the data was destined for:

Column Name Description
_sdc_rejected_at The date of the load error
table_name The table the record was destined for
reason The reason for the rejection
record The raw Transit data of the record


Here’s an example of what an _sdc_rejected table might look like:

_sdc_rejected_at table_name reason record
2016-11-29 10:50:55.816 user Column names from client should not include reserved datatype suffix on name __bigint "{""id"":1,""name__bigint"":""Paul""}"
2016-11-29 11:00:34.816 order Field collision on id "{""id"":1,""ID"":""Paul""}"

Why were these records rejected? Here are the details:

  1. The record in the first row was rejected because the column name contains a data type suffix: name__bigint. Stitch reserves these suffixes for column names, so any column name containing a data type suffix will be rejected.
  2. The record in the second row was rejected because there is another column named id.

Resolving Record Rejection Issues

In some cases, you may be able to pinpoint and resolve the root cause of the rejection. Here’s an example:

  1. You have a Postgres database integration that’s connected to a Redshift data warehouse.
  2. You’re syncing a users table that contains two fields that differ only by case: id and Id.
  3. As Redshift is case-insensitive, this will cause a collision and result in Redshift rejecting both records.
  4. You can re-name the columns (such as customer_id and first_order_id) in the source database and re-sync the data.

Note that it may not be possible to resolve every rejected record issue. While you may be able to resolve the issue in a database integration, the majority of SaaS integrations don’t provide users with the ability to define and control the structure of their data.

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!