+1 (726) 227-3027
ETL Advisors

Loading Amazon Redshift from Talend: S3 Staging, COPY, and MERGE That Reruns Cleanly

Redshift is still the warehouse behind a lot of the Talend estates we inherit. The most common performance problem we see is the same each time: a job that pushes rows through a tRedshiftOutput component one batch at a time. It works on 50,000 rows and falls over on 50 million. This tutorial covers the pattern we use instead: stage in S3, COPY in parallel, then MERGE from a staging table.

Why not insert directly

Redshift is a columnar, massively parallel system. It is built to ingest many files at once across slices. Single-row or small-batch INSERT statements go through the leader node and ignore that parallelism. COPY from S3 does not.

The job layout

A reliable load job has five steps:

  1. Extract and transform in Talend as usual (tMap, tNormalize, and so on).
  2. Write files to S3 with tFileOutputDelimited to a local temp directory, then tS3Put. Gzip the files and split them. Aim for a number of files that is a multiple of the slice count in your cluster, each roughly 1 MB to 1 GB compressed.
  3. Create or truncate a staging table with tRedshiftRow.
  4. COPY into staging using tRedshiftBulkExec, or tRedshiftRow with your own COPY statement if you need options the component does not expose.
  5. MERGE into the target and commit.

The tRedshiftOutputBulkExec component combines steps 2 and 4. We still prefer the split version on large loads because it lets you retry the COPY without re-extracting.

Credentials: use an IAM role

Avoid access keys in context variables. Attach an IAM role to the cluster with read access to the staging prefix, and reference it in the COPY statement:

COPY stg.orders
FROM 's3://my-bucket/etl/orders/2026-01-15/'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-copy'
FORMAT AS CSV
GZIP
IGNOREHEADER 1
TIMEFORMAT 'auto'
TRUNCATECOLUMNS
COMPUPDATE OFF
STATUPDATE OFF;

Put the run date or a run ID in the S3 prefix. That makes each load self-contained and easy to replay. Turn COMPUPDATE and STATUPDATE off for staging tables you rebuild every run, and run ANALYZE on the target only when needed.

Merging from staging

Redshift now supports a MERGE statement, which replaces the older delete-then-insert workaround for most jobs:

MERGE INTO core.orders
USING stg.orders AS s
ON core.orders.order_id = s.order_id
WHEN MATCHED AND s.updated_at > core.orders.updated_at THEN
  UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, updated_at)
  VALUES (s.order_id, s.status, s.amount, s.updated_at);

Check that your cluster version supports MERGE before relying on it. If it does not, use the classic pattern in one transaction: delete matching keys from the target using the staging table, insert everything from staging, commit.

Two details matter here:

  • Deduplicate staging first. If staging holds two rows for the same key, the merge result is not what you want. Keep the latest per key with ROW_NUMBER() in the staging query.
  • The updated_at guard makes late or replayed files harmless. An older record cannot overwrite a newer one.

Making reruns safe

  • Keep staging, COPY, and MERGE in one job so the same run ID flows through all three.
  • Turn off auto-commit on the connection, share the connection with tRedshiftConnection, and commit with tRedshiftCommit only after the merge succeeds. Use tRedshiftRollback in an On Component Error path.
  • Log the row count from COPY and from the merge. Compare them to the extract count and fail the job on a mismatch.
  • When COPY fails, read STL_LOAD_ERRORS (or SYS_LOAD_ERROR_DETAIL) to find the file, line, and column. Do not guess.

Table design notes

  • Pick a distribution key that matches your most common join, or use DISTSTYLE AUTO and let Redshift decide.
  • Use a sort key on the column you filter by most, usually a date.
  • Skip manual VACUUM on tables that load by append. Automatic table optimization covers most cases. Large delete-heavy merges are the exception.

Common failure modes

  • Timestamps that do not parse. Set an explicit TIMEFORMAT and normalize formats in Talend before writing the file.
  • Embedded newlines or quotes in text. Use CSV format with proper quoting rather than a naive delimiter.
  • Varchar overflow. TRUNCATECOLUMNS hides data loss. Prefer widening the column, or at least log the truncation.
  • One giant file. It loads on a single slice. Split it.
  • Role permissions. A COPY that fails with access denied is almost always a missing s3:GetObject or s3:ListBucket on the prefix.

When to consider something else

If your team is already moving toward Snowflake, Databricks, or BigQuery, the same stage-then-merge idea applies. See our posts on loading those platforms. If you are unsure whether to optimize the Redshift path or migrate it, we can help you assess the estate. Contact ETL Advisors to talk it through.