Redshift - Unload

On this Page

Snap Type:



This Snap unloads the result of a query to one or more files on Amazon S3, using Amazon S3 server-side encryption (SSE)/server-side encryption with KMS/client-side encryption with CMK. The target Redshift table is not modified. 

ETL Transformations & Data Flow

        "entries": [
                "url": "s3:///mybucket/unload_folder/test0000_part_00"
                "url": "s3:///mybucket/unload_folder/test0001_part_00"
                "url": "s3:///mybucket/unload_folder/test0002_part_00"
                "url": "s3:///mybucket/unload_folder/test0003_part_00"
        "status": "success",
        "unloadQuery": "UNLOAD ('SELECT * FROM') TO 's3://mybucket/unload_folder/test' 
CREDENTIALS 'aws_access_key_id=;aws_secret_access_key=' MANIFEST ESCAPE ALLOWOVERWRITE PARALLEL"

Upon the successful preview, the expected output data is as follows:

        "entries": [
                "url": "s3:///<bucket>/<folder>/<prefix>000n_part_00"
        "status": "preview",
        "unloadQuery": "UNLOAD ('SELECT * FROM') TO 's3://mybucket/unload_folder/test' 
CREDENTIALS 'aws_access_key_id=;aws_secret_access_key=' MANIFEST ESCAPE ALLOWOVERWRITE PARALLEL"

The Snap behaves the same on a Groundplex as it does in a Cloudplex.

Expected upstream Snaps: Any Snap with a document output view

Expected downstream Snaps: Any Snap with a document input view, such as JSON Formatter, Mapper, and so on. The CSV Formatter Snap cannot be connected directly to this Snap since the output document map data is not flat.

This Snap provides a front end to the Amazon Redshift Unload/Copy Utility. The Snap allows data to be efficiently moved from one Redshift instance to an optionally encrypted S3 bucket. The COPY snap moves data from an optionally encrypted S3 bucket into a second Redshift instance. Alternately the data may be downloaded to another system using the S3 READ Snap.

By default the data will be written to a separate CSV-encoded compressed file per Redshift 'slice'. It is possible to override this behavior but the resulting file must be smaller than 6.2 GB.

The default CSV delimiter is a carat ('^'), not a comma. (Note: elsewhere I've seen the default delimiter listed as a pipe ('|') character.)

Input & Output

Input: Key-value map data to evaluate expression properties of the Snap.

Input Schema Provided: No

Output: This snap produces one document. The fields are:

  • entries - a list of S3 URLs.
  • status - "success" or "unsuccess"
  • unloadQuery - the actual SQL command executed.

Output Schema Provided: No

Preview Supported: Yes

Passthrough Supported: No

Output Examples:

    • Upon the successful execution, the expected output data is as follows:

  • The Redshift account should contain S3 Access-key ID, S3 Secret key, S3 Bucket and S3 Folder.
  • The Redshift account security settings should allow access from the IP Address of the cloudplex or groundplex.
  • The Amazon S3 bucket where Amazon Redshift will write the output files must reside in the same region as your cluster.
  • The Amazon S3 bucket where Amazon Redshift should allow write access from the IP address of the cloudplex or groundplex.
Limitations and Known Issues:

Works in Ultra Task Pipelines.


Account & Access

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. This Snap requires a Redshift Account with S3 properties. See Configuring Redshift Accounts for information on setting up this type of account. 



This Snap has at most one input view. 


This Snap has at most one document output view.
The output document contains map data:

  • a "status" field with "success" or "preview", 
  • a "unloadQuery" field with the query sent to Redshift, and
  • an "entries" field with a list of S3 URL's written by the Redshift UNLOAD operation.

This Snap has at most one document error view and produces zero or more documents in the view.

The error view would contain error, reason, resolution and stack trace.


The preview on this Snap will not execute the Redshift UNLOAD operation. Connect a JSON Formatter and a File Writer Snaps to the error view and then execute the pipeline. If there is any error, you will be able to preview the output file in the File Writer Snap for the error information.



Required The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Select query


Defines a SELECT query. The results of the query are unloaded. In most cases, it is worthwhile to unload data in sorted order by specifying an ORDER BY clause in the query; this approach will save the time required to sort the data when it is reloaded.


Default value: SELECT * FROM 

S3 file prefix

The prefix of AWS S3 file names which are used by Redshift to write data. The Snap uses S3 Bucket and S3 Folder in the RedShift Account to format the full S3 path. File names created by RedShift are in the format:  s3://<bucket>/<folder>/<s3-file-prefix><slice-number>_part_<file-number>, where <s3-file-prefix> is the value of this property. The Amazon S3 bucket where Amazon Redshift will write the output files must reside in the same region as your cluster.

Example: test

Default value: [None]


Required. Single ASCII character that is used to separate fields in the output file. DELIMITER will be ignored if FIXEDWIDTH is specified. If the data contains the delimiter character, you will need to specify the ESCAPE option to escape the delimiter, or use ADDQUOTES to enclose the data in double quotes. Alternatively, specify a delimiter that is not contained in the data.

Example: a pipe character ( | ), a comma ( , ), or a tab ( \t )

Default value: a pipe character ( | )

Fixed width

Specifies FIXEDWIDTH spec., in which Redshift unloads the data to a file where each column width is a fixed length, rather than separated by a delimiter. The FIXEDWIDTH spec is a string that specifies the number of columns and the width of the columns. DELIMITER is ignored if FIXEDWIDTH is specified. Because FIXEDWIDTH does not truncate data, the specification for each column in the UNLOAD query needs to be at least as long as the length of the longest entry for that column. The format for fixedwidth_spec is: 'colID1:colWidth1,colID2:colWidth2, ...'

Example: colID1:36,colID2:36,colID3:256

Default value: [None]

Null as

Specifies a string that represents a null value in unload files. If this option is used, all output files contain the specified string in place of any null values found in the selected data. If this option is not specified, null values are unloaded as: 
Zero-length strings for delimited output 
Whitespace strings for fixed-width output
 If a null string is specified for a fixed-width unload and the width of an output column is less than the width of the null string, the following behavior occurs:
 An empty field is output for non-character columns
 An error is reported for character columns
Example: null

Default value: [None]

Anaplan server may ignore this setting and try to auto-detect.  See Anaplan for details.


If selected, for CHAR and VARCHAR columns in delimited unload files, an escape character (\) is placed before every occurrence of the following characters:
 Linefeed: \n
 Carriage return: \r
 The delimiter character specified for the unloaded data.
 The escape character: \
 A quote character: " or ' (if both ESCAPE and ADDQUOTES are selected).
We strongly recommend that you always select this property unless you are certain that your data does not contain any delimiters or other characters that might need to be escaped.

 Important:  If you loaded your data using a COPY with the ESCAPE option, you must also specify the ESCAPE option with your UNLOAD command to generate the reciprocal output file. Similarly, if you UNLOAD using the ESCAPE option, you will need to use ESCAPE when you COPY the same data.

Default value: Selected

Add quotes

If checked, RedShift places quotation marks around each unloaded data field, so that Amazon Redshift can unload data values that contain the delimiter itself.

Default value: Not selected

Allow overwrite

By default, UNLOAD fails if it finds files that it would possibly overwrite. If specified, UNLOAD will overwrite existing files, including the manifest file.

Default value: Not selected


Creates a manifest file that explicitly lists the data files that are created by the UNLOAD process. The manifest is a text file in JSON format that lists the URL of each file that was written to Amazon S3. The manifest file is written to the same Amazon S3 path prefix as the unload files in the format <s3_path_prefix>manifest. For example, if the unload file S3 path prefix is 's3://mybucket/myfolder/venue_', the manifest file location will be 's3://mybucket/myfolder/venue_manifest'. Since this Snap needs to read the content of the manifest file for the output document data, it always includes MANIFEST option in the query. After reading the content of the manifest file, the Snap deletes the manifest file if this property is un-selected.

Default value: Not selected


By default, UNLOAD writes data in parallel to multiple files, according to the number of slices in the cluster. If unchecked, UNLOAD writes to one or more data files serially, sorted absolutely according to the ORDER BY clause, if one is used. The maximum size for a data file is 6.2 GB. Therefore, if the unload data is larger than 6.2 GB, UNLOAD will create more than one file. The UNLOAD command is designed to use parallel processing. We recommend leaving this property selected for most cases.

Default value:  Selected


Unloads data to one or more gzip-compressed file per slice. Each resulting file is appended with a .gz extension.

Default value: Not selected

Client-side Encryption

Specifies the Amazon S3 client-side encryption type for the output files on Amazon S3. UNLOAD by default creates encrypted files using Amazon S3 server-side encryption with AWS-managed encryption keys (SSE). UNLOAD does not support Amazon S3 server-side encryption with a customer-supplied key (SSE-C). To unload to encrypted gzip-compressed files, check the GZIP property. If selected, provide the customer master key in the Master symmetric key property.

Default value: Not selected

IAM role

Check this property if the bulk load/unload has to be done using IAM role. If checked, ensure the properties (AWS account ID, role name and region name) are provided in the account.

Default value:  [None] 

KMS Encryption type

Specifies the type of KMS S3 encryption to be used on the data. The available encryption options are:

  • None - Files do not get encrypted using KMS encryption
  • Server-Side KMS Encryption If selected, the output files on Amazon S3 are encrypted using this encryption with Amazon S3 generated KMS key. 

Default value: None

If both the KMS and Client-side encryption types are selected, the Snap gives precedence to the SSE,  and displays an error prompting the user to select either of the options only.

Master symmetric key

Conditional. This property applies only when the Client-side Encryption property is selected. This is the customer master key for the data to be encrypted client side. For more information about this please refer to Customer Master Keys and Using Client Side Encryption.

Default value:  [None] 

KMS key

Specifies the AWS Key Management Service (KMS) key ID or ARN to be used for the S3 encryption. This is only required if the KMS Encryption type property is configured to use the encryption with KMS. For more information about the KMS key refer to AWS KMS Overview and Using Server Side Encryption

Default value:  [None] 

Snap Execution

Select one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.


Transferring data from one Redshift instance to another

The Redshift Unload and Redshift Copy Snaps can be used to transfer data from one Redshift instance to a second.

Sample Unload snap settings

The first mapper extracts the URL elements

The splitter breaks the string array of URLs into a series of documents.

The second mapper strips the leading "s3:///" from the URLs.

The S3 Upsert Snap uses an expression property for the filename.

The output of the Upsert Snap shows the results.

The key points are that

  1. The S3 file list must include the 'slice' information, e.g., '0000_part_00'. There will be one file per slice.
  2. The Unload Snap will always write to the S3 bucket associated with the Redshift account. The Upsert Snap's S3 file list must include the S3 bucket name.
  3. The destination table must be created beforehand. There is no auto-creation mode.
  4. The null substitution is not recognized by the upsert. It must be updated manually. This could cause problems with uniqueness constraints.

Exported pipeline is available in the Downloads section below.

This Snap will leave the data file on the S3 instance.


Important steps to successfully reuse Pipelines

  1. Download and import the Pipeline into SnapLogic.
  2. Configure Snap accounts as applicable.
  3. Provide Pipeline parameters as applicable.

  File Modified

File Redshift Unload - Example.slp

Apr 20, 2018 by Mohammed Iqbal

Snap Pack History

 Click to view/expand
Release Snap Pack VersionDateType  Updates
4.29 Patch429patches16908 Latest
  • Enhanced the Redshift accounts with the following:

    • Expression enabler to pass values from Pipeline parameters.

    • Support for Security Token for S3 bucket external staging.

  • Fixed an issue with Redshift - Execute Snap where the Snap failed when the query contained comments with single or double quotes in it. Now the Pipeline executes without any error if the query contains a comment.

4.29 Patch



Fixed an issue with Redshift Account and Redshift SSL Account where the Redshift Snaps failed when the S3 Secret key or S3 Access-key ID contained special characters, such as +.





Upgraded with the latest SnapLogic Platform release.

4.28main14627 StableUpdated the label for Delete Condition to Delete Condition (Truncates Table if empty) in the Redshift Delete Snap.
4.27 Patch427patches12999 LatestFixed an issue with the Redshift Bulk Load Snap, where the temporary files in S3 were not deleted for aborted or interrupted Pipelines.
4.27 Patch427patches12999 Latest
4.27main12833 Stable

Enhanced the Redshift - Execute Snap to invoke stored procedures.

4.26main11181 StableUpgraded with the latest SnapLogic Platform release.
4.25 Patch425patches11008 Latest

Updated the AWS SDK from version 1.11.688 to 1.11.1010 in the Redshift Snap Pack and added a custom SnapLogic User Agent header value.

StableUpgraded with the latest SnapLogic Platform release.

Fixed an issue with the Redshift Bulk Load Snap that fails while displaying a Failed to commit transaction error.

StableUpgraded with the latest SnapLogic Platform release.
4.21 Patch421patches6144 Latest

Fixed the following issues with DB Snaps:

  • The connection thread waits indefinitely causing the subsequent connection requests to become unresponsive.
  • Connection leaks occur during Pipeline execution.
4.21 PatchMULTIPLE8841 Latest

Fixed the connection issue in Database Snaps by detecting and closing open connections after the Snap execution ends. 



StableUpgraded with the latest SnapLogic Platform release.
4.20 Patch db/redshift8774

Fixed the Redshift - Execute Snap that hangs if the SQL statement field contains only a comment ("-- comment"). 

StableUpgraded with the latest SnapLogic Platform release.
4.19 Patch db/redshift8410 Latest

Fixed an issue with the Redshift - Update Snap wherein the Snap is unable to perform operations when:

  • An expression is used in the Update condition property.
  • Input data contain the character '?'.
StableUpgraded with the latest SnapLogic Platform release.
4.18 Patch db/redshift8043 Latest

Enhanced the Snap Pack to support AWS SDK 1.11.634 to fix the NullPointerException issue in the AWS SDK. This issue occurred in AWS-related Snaps that had HTTP or HTTPS proxy configured without a username and/or password. 

4.18 PatchMULTIPLE7884 Latest

Fixed an issue with the PostgreSQL grammar to better handle the single quote characters.

4.18 PatchMULTIPLE7778 Latest

Updated the AWS SDK library version to default to Signature Version 4 Signing process for API requests across all regions.

StableUpgraded with the latest SnapLogic Platform release.
4.17 Patchdb/redshift7433 Latest

Fixed an issue with the Redshift Bulk Load Snap wherein the Snap fails to copy the entire data from source to the Redshift table without any statements being aborted.


Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.

  • Fixed an issue with the Redshift Execute Snap wherein the Snap would send the input document to the output view even if the Pass through field is not selected in the Snap configuration. With this fix, the Snap sends the input document to the output view, under the key original, only if you select the Pass through field.
  • Added the Snap Execution field to all Standard-mode Snaps. In some Snaps, this field replaces the existing Execute during preview checkbox.
4.16 Patch db/redshift6821 Latest

Fixed an issue with the Lookup Snap passing data simultaneously to output and error views when some values contained spaces at the end.

StableUpgraded with the latest SnapLogic Platform release.
4.15 Patch db/redshift6286 Latest

Fixed an issue with the Bulk Upsert Snap wherein there was no output for any input schema.

4.15 Patch db/redshift6334 Latest

Replaced Max idle time and Idle connection test period properties with Max life time and Idle Timeout properties, respectively, in the Account configuration. The new properties fix the connection release issues that were occurring due to default/restricted DB Account settings.

StableUpgraded with the latest SnapLogic Platform release.
4.14 Patch db/redshift5786 Latest

Fixed an issue wherein the Redshift Upload snap logged the access and secret keys without encryption in the error logs. The keys are now masked.

4.14 Patch db/redshift5667 Latest
  • Added "Validate input data" property in the Redshift Bulk Load Snap to enable users to troubleshoot input data schema.
  • Enhanced a check to identify whether the Provided Query in the Redshift Execute Snap is of read or write type.
StableUpgraded with the latest SnapLogic Platform release.
4.13 Patch db/redshift/5303 Latest

Added a new property "Validate input data" in the Redshift Bulk Load Snap to help users troubleshoot the input data schema.

4.13 Patch db/redshift5186 Latest

Fixed the Bulk Load and Unload Snaps wherein the KMS encryption type property is failing with validation error.




Added KMS encryption support to these Snaps: Redshift Unload, Redshift Bulk Load, Redshift Bulk Upsert, and Redshift S3 Upsert.

4.12 Patch db/redshift5027 Latest

Fixed an issue wherein the Redshift Snaps timeout and fail to retrieve a database connection.

4.12 Patch

MULTIPLE4967 Latest

Provided an interim fix for an issue with the Redshift accounts by re-registering the driver for each account validation. The final fix is being shipped in a separate build.

4.12 Patch

MULTIPLE4744 Latest

Added support for Redshift grammar to recognize window functions as being part of the query statement.



StableUpgraded with the latest SnapLogic Platform release.
4.11 Patch db/redshift4589 Latest

Fixed an issue when creating a Redshift table via the second/metadata input view for the Redshift Bulk Load Snap.


Added SSL support to the Configuring Redshift Accounts.

4.10 Patch db/redshift4115 Latest

The Upsert or BulkUpdate/BulkLoad shall not execute and produce output when no inputView has been provided.

4.10 Patchredshift3936 Latest

Addressed an issue in Redshift Execute with a Select that hangs after extracting 13 million in the morning or 30 million in the evening 




Added Auto commit property to the Select and Execute Snaps at the Snap level to support overriding of the Auto commit property at the Account level.

4.9.0 Patch

redshift3229 Latest

Addressed an issue in Redshift Multiple Execute where INSERT INTO SELECT statement generated a 'transaction, commit and rollback statements are not supported' exception.

4.9.0 Patch

redshift3073 Latest

Fixed an issue regarding connection not closed after login failure; Expose autocommit for "Select into" statement in PostgreSQL Execute Snap and Redshift Execute Snap

  • Updated the Bulk Load, Bulk Upsert and S3 Upsert Snaps with the properties Vacuum type & Vacuum threshold (%) (replaced the original Vacuum property).

  • Update the S3 Upsert Snap with the properties, IAM role and Server-side encryption to support data upsert across two VPCs.

  • Added support for the Redshift driver under the account setting for JDBC jars.

4.8.0 Patchredshift2852 Latest
  • Addressed an issue with Redshift Insert failing with 'casts smallint as varchar'

  • Addressed an issue with Redshift Bulk Upsert fails to drop temp table

4.8.0 Patchredshift2799 Latest
  • Addressed an issue with Redshift Snaps with the default driver failing with could not load JDBC driver for url file.

  • Added the properties, JDBC Driver Class, JDBC jars and JDBC Url to enable the users to upload the Redshift JDBC drivers that can override the default driver.
4.8.0 Patchredshift2758 Latest

Potential fix for JDBC deadlock issue.

4.8.0 Patch

redshift2713 Latest

Fixed Redshift Snap Pack rendering dates that are one hour off from the date returned by database query for non-UTC Snaplexes

4.8.0 Patch

redshift2697 Latest

Addresses an issue where some changes made in the platform patch MRC294 to improve performance caused Snaps in the listed Snap Packs to fail.



  • Redshift MultiExecute Snap introduced in this release.

  • Redshift Account: Info tab added to accounts.

  • Database accounts now invalidate connection pools if account properties are modified and login attempts fail.

  • Info tab added to accounts.
  • Database accounts now invalidate connection pools if account properties are modified and login attempts fail.
4.7.0 Patchredshift2434 Latest

Replaced newSingleThreadExecutor() with a fixed thread pool.

4.7.0 Patch

redshift2387 Latest

Addressed an issue in Redshift Bulk Load Snap where Load Empty String was setting not working after release.

4.7.0 Patch

redshift2223 Latest

Auto-commit is turned off automatically for SELECT

4.7.0 Patch




Fixed an issue for database Select Snaps regarding Limit rows not supporting an empty string from a pipeline parameter.



  • Updated the Redshift Snap Account Settings with the IAM properties that include AWS account ID , IAM role name, and Region name.

  • Redshift Bulk Load Snap updated with the properties IAM Role & Server-side encryption.

  • Redshift Bulk Upsert Snap updated with the properties Load empty stringsIAM Role & Server-side encryption.

  • Updated the Redshift Upsert Snap with Load empty strings property.

  • Updated the Redshift Unload Snap with the property IAM role.

  • Redshift Execute Snap enhanced to fully support SQL statements with/without expressions & SQL bind variables.

  • Resolved an issue in Redshift Execute Snap that caused errors when executing a command Select current_schemas(true).

  • Resolved an issues in Redshift Execute Snap that caused errors when a Select * from <table_name> into statement was executed.

  • Enhanced error reporting in Redshift Bulk Load Snap to provided appropriate resolution messages.



  • Redshift S3 Upsert Snap introduced in this release.

  • Resolved an issue that occurred while inserting mismatched data type values in Redshift Insert Snap.



  • Resolved an issue in Redshift Bulk Upsert Snap that occurred when purging temp tables.

  • Resolved an issue in Redshift Upload/Upsert Snap that occurred when using IAM credentials in an EC2 instance with an S3 bucket.

4.4.1NA Latest

Resolved an issue with numeric precision when trying to use create table if not present in Redshift Insert Snap.

4.4NA StableUpgraded with the latest SnapLogic Platform release.
4.3.2NA Stable
  • Redshift Select Where clause property now has expression support.

  • Redshift Update Update condition property now has expression support.

  • Resolved an issue with Redshift Select Table metadata being empty if the casing is different from the suggested one for table name

4.3NA Stable
  • Table List Snap: A new option, Compute table graph, now lets you determine whether or not to generate dependents data into the output.

  • Redshift Unload Snap Parallel property now explicitly adds 'PARALLEL [OFF|FALSE]' to the UNLOAD query.

4.2NA Latest
  • Resolved an issue where Redshift SCD2 Snap historized the current row when no Cause-historization fields had changed.

  • Ignore empty result added to Execute and Select Snaps. The option will not any document to the output view for select statements.

  • Resolved an issue with Redshift Select Snap returning a Date object for DATE column data type instead of a LocalDate object.

  • Resolved an issue in RedShift SCD2 failing to close database cursor connection.

  • Resolved an issue with Redshift Lookup Snap not handling values with spaces in the prefix.

  • Updated driver not distributed with the Redshift Snap Pack.

  • Output fields table property added to Select Snap.

  • Resolved an issue with Redshift - Bulk Loader incorrectly writing to wrong location on S3 and disable data compression not working

  • Resolved an issue in Execute and Select Snaps where the output document was the same as the input document if the query produces no data. When there is no result from the SELECT query, the input document will be passed through to the output view as a value to the 'original' key. The new property Pass through with true default.

  • Redshift Account: Enhanced error messaging

  • Redshift SCD2: Bug fixes with compound keys

  • RedShift Lookup: Bug fixes on lookup failures; Pass-though on no lookup match property added to allow you to pass the input document through to the output view when there is no lookup matching.