Panoply is a fully managed data warehouse service that will spin up an Amazon Redshift instance in just a few clicks. With Panoply, you can use your favorite analysis, SQL, and visualization tools just like you would if you were creating a Redshift data warehouse on your own.

This guide serves as a reference for version 2 of Stitch’s Panoply destination.


Details and features

Stitch features

High-level details about Stitch’s implementation of Panoply, such as supported connection methods, availability on Stitch plans, etc.

Release status

Released

Stitch plan availability

All Stitch plans

Supported versions

Not applicable

Connect API availability Unsupported

This version of the Panoply destination is not currently available in Stitch’s Connect API.

SSH connections Unsupported

Stitch does not support using SSH tunnels to connect to Panoply destinations.

SSL connections Supported

Stitch will attempt to use SSL to connect by default. No additional configuration is needed.

VPN connections Unsupported

Virtual Private Network (VPN) connections may be implemented as part of an Enterprise plan. Contact Stitch Sales for more info.

Default loading behavior

Upsert
Note: Append-Only loading will be used if all conditions for Upsert are not met. Learn more.

Nested structure support

Unsupported
Nested data structures will be flattened into relational objects. Learn more.

Destination details

Details about the destination, including object names, table and column limits, reserved keywords, etc.

Note: Exceeding the limits noted below will result in loading errors or rejected data.

Maximum record size

4MB

Table name length

127 characters

Column name length

115 characters

Maximum table size

None

Maximum tables per database

100,000

Case sensitivity

Insensitive

Reserved keywords

Refer to the Reserved keywords documentation.

Pricing

Pricing varies from plan to plan, but every Panoply plan includes:

  • Unlimited queries
  • Unlimited user accounts
  • Automatic maintenance, vacuuming, and backups

Panoply charges based on the amount of data stored and offers several plan options for your needs. Refer to their pricing page for more information.


Replication

Replication process overview

A Stitch replication job consists of three stages:

Step 1: Data extraction

Stitch requests and extracts data from a data source. Refer to the System overview guide for a more detailed explanation of the Extraction phase.

Step 2: Preparation

During this phase, the extracted data is buffered in Stitch’s durable, highly available internal data pipeline and readied for loading. Refer to the System overview guide for a more detailed explanation of the Preparation phase.

Step 3: Loading

Stitch loads the data into Panoply.

Loading behavior

By default, Stitch will use Upsert loading when loading data into Panoply.

If the conditions for Upsert loading aren’t met, data will be loaded using Append-Only loading.

Refer to the Understanding loading behavior guide for more info and examples.

Primary Keys

Stitch requires Primary Keys to de-dupe incrementally replicated data. To ensure Primary Key data is available, Stitch creates a primary_keys table comment. The comment is an array of strings that contain the names of the Primary Key columns for the table.

For example: A table comment for a table with a single Primary Key:

'{"primary_keys":["id"]}'

And a table comment for a table with a composite Primary Key:

'{"primary_keys":["event_id","created_at"]}'

Note: Removing or incorrectly altering Primary Key table comments can lead to replication issues.

Incompatible sources

The Panoply destination has reported incompatibilities with some of Stitch's integrations. Refer to the table below for more info.

Integration Version Level Reason
MongoDB ANY

Flattening nested JSON structures may result in tables with columns that exceed Panoply’s 1,600 column limit. Learn more.

MongoDB Atlas v1

Flattening nested JSON structures may result in tables with columns that exceed Panoply’s 1,600 column limit. Learn more.

See all destination and integration incompatibilities.


Transformations

System tables and columns

Stitch will create the following tables in each integration’s dataset:

Additionally, Stitch will insert system columns (prepended with _sdc) into each table.

Data typing

Stitch converts data types only where needed to ensure the data is accepted by Panoply. In the table below are the data types Stitch supports for Panoply destinations, and the Stitch types they map to.

  • Stitch type: The Stitch data type the source type was mapped to. During the Extraction and Preparing phases, Stitch identifies the data type in the source and then maps it to a common Stitch data type.
  • Destination type: The destination-compatible data type the Stitch type maps to. This is the data type Stitch will use to store data in Panoply.
  • Notes: Details about the data type and/or its allowed values in the destination, if available. If a range is available, values that exceed the noted range will be rejected by Panoply.
Stitch type Destination type Notes
BIGINT BIGINT
  • Range : -9223372036854775808 to 9223372036854775807

BOOLEAN BOOLEAN
DATE TIMESTAMP
  • Description: Stored in UTC as TIMESTAMP WITHOUT TIMEZONE

  • Range : 4713 BC to 294276 AD

DOUBLE DOUBLE
FLOAT DOUBLE
INTEGER INTEGER
  • Range : -9223372036854775808 to 9223372036854775807

NUMBER DECIMAL
  • Description: Precision must be between 1 and 38; scale must be between 0 and the precision value

STRING STRING
  • Description: Values that exceed in size will not be rejected, but truncated to Panoply’s maximum width

  • Range : 65535 bytes (64K -1)

JSON structures

Panoply destinations don’t have native support for nested data structures. To ensure nested data can be loaded, Stitch will flatten objects and arrays into columns and subtables, respectively. For more info and examples, refer to the Handling nested data structures guide.

Column names

Column names in Panoply:

Stitch will perform the following transformations to ensure column names adhere to the rules imposed by Panoply:

Transformation Source column Destination column
Convert uppercase and mixed case to lowercase CuStOmErId or CUSTOMERID customerid
Remove special characters customer#id or !CuStOMerID customerid and customerid
Remove non-letter leading characters 4customerid or _customerid customerid

Timezones

Panoply will store the value as TIMESTAMP WITHOUT TIMEZONE. In Panoply, this data is stored without timezone information and expressed as UTC.


Compare destinations

Not sure if Panoply is the data warehouse for you? Check out the Choosing a Stitch Destination guide to compare each of Stitch’s destination offerings.


Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.