Amazon Redshift is fully managed, cloud-based data warehouse. Redshift exhibits far better performance for large-scale analytical queries than do traditional, row-based relational databases like MySQL or PostgreSQL. You can learn more about transactional vs. analytic databases in our Data Strategy Guide.
While Redshift is based on Postgres and therefore shares many similarities, there are some key differences.
Unlike Postgres which has a limit of 63 characters for names, Redshift supports lengthier table and column names:
Stitch uses Primary Keys in the replication process as the key indicator of rows to add and/or update.
Redshift has a relatively cosmetic implementation of Primary Keys, meaning their usage isn't enforced in any way. Stitch uses table comments to store Primary Key information for use in the replication process.
Redshift is case insensitive, meaning that tables and columns that differ only in case will result in collision errors. For example: columns named
Id will result in a collision.
Redshift doesn't natively support nested record replication and storage. To compensate for this, Stitch will de-nest nested records and break them into subtables for easier querying.
Click here for more info on how Stitch handles nested structures in Redshift.
When timestamp data arrives in Redshift, Redshift will convert those timestamps into UTC and display the data type as
TIMESTAMP WITHOUT TIME ZONE.
Views are incredibly useful tools for filtering out unnecessary data from database tables. While you're free to build views on top of the integration tables created by Stitch, you may occasionally encounter "dependency" errors.
This issue is typically a result of how Stitch handles altered table structures and views with dependencies. Stitch won't automatically drop views with dependencies, as we don't want to affect your work without your say-so.
Click here for more info on how Stitch handles views with dependencies.
Because data can come from a variety of integrations and all those integrations may structure or handle data differently, Stitch will likely encounter numerous scenarios when replicating and loading your data. It's important to familiarize yourself with how certain scenarios will be handled so you can understand what's happening or how to diagnose an issue.
Click here for more info on what those scenarios are and how Stitch handles them.