Redshift output
Load rows into an Amazon Redshift table (append, replace, or upsert). Best for small and medium loads. The load is one transaction by default, so a load that fails leaves the table as it was.
- Category
- Output
- Connection
Amazon Redshift
What it does
Loads the incoming rows into an Amazon Redshift table through a saved Connection. Appends, replaces the contents, or upserts on key columns. The table is auto-created from the incoming columns if it doesn't exist. All or nothing is on by default: the whole load is ONE transaction, so a mid-run failure leaves the table exactly as it was.
Inputs and outputs
| Inputs | One. |
| Outputs | None. This node ends a pipeline. |
When to use it
- Land a transform's results in a warehouse table
- Refresh a reporting table each run (truncate)
- Keep a table in sync re-runnably, without duplicates (upsert)
Examples
Load, then merge and tidy up
Post-SQL: DELETE FROM orders USING stg_orders s WHERE orders.id = s.id · INSERT INTO orders SELECT * FROM stg_orders · VACUUM orders · ANALYZE orders
Four statements, in order. VACUUM and ANALYZE work here because the statements run outside the load's transaction; Redshift refuses both inside one.
Append
Table: public.events, Mode: append
Full refresh
Mode: truncate
Empties the table first, then loads, all inside the one transaction while All or nothing is on.
A load too long to hold open
All or nothing: off
Each batch commits as it is written (an upsert batch in its own short transaction); truncate empties with a real TRUNCATE first. A failure leaves the batches written so far.
Upsert
Mode: upsert, Key columns: id
Rows with a matching id are replaced; new ones are added.
Tips
- Each pre- and post-SQL statement has its own ⤢, the SQL Studio, with the connection's tables and columns beside the statement. It has no Run button: a Test run executes none of these statements.
- Pre-SQL and post-SQL run on this same connection, around the load. Pre-SQL runs and commits BEFORE the load starts; post-SQL runs only AFTER it has finished successfully, so a failed load never fires the merge.
- One statement per row, run in order. If one fails the run stops there, and the message names which statement it was and what the database said.
- A Test run (Run to here) executes none of them, so testing stays safe on a pipeline whose pre-SQL truncates a table.
- VACUUM and ANALYZE belong in post-SQL rather than a second pipeline: the statements run outside the load's transaction, which is the only place Redshift accepts them.
- Best for SMALL and MEDIUM loads. Rows go in with INSERT, and Redshift is a warehouse: past roughly 100k rows, write the data to S3 as Parquet (S3 File output) and load it with Redshift's COPY command instead. The run log says so when a load gets that big.
- With All or nothing on, truncate mode empties with DELETE, not TRUNCATE, precisely so a failed load rolls back instead of leaving the table empty.
- Text columns are created as VARCHAR(65535), Redshift's widest, not TEXT, which Redshift silently caps at 256 characters.
- Dates, times and decimals load natively. Decimal precision is capped at 38 digits, Redshift's maximum, so a wider incoming decimal is narrowed to fit rather than rejected.
- Upsert is a delete-then-insert of the matching keys. A key that appears more than once in one load keeps its last row. A row whose key is null matches nothing and is simply added.