How do I set up incremental loads from SaaS apps to Redshift?

To set up incremental loads from SaaS apps to Redshift, the main idea is:

1. Extract only changed records from the SaaS app
2. Land them in S3 or a staging area
3. Load into Redshift staging tables
4. Merge/upsert into final tables

The best approach depends on the SaaS source and whether it supports:

• CDC / change tokens / updated_at filtering
• Bulk exports
• APIs with pagination
• Webhooks
• Connector tools like Fivetran, Airbyte, Stitch, Matillion, dbt + custom jobs

---

Common patterns

1) Timestamp-based incremental loads

If the SaaS app has fields like:

• updated_at
• modified_date
• last_changed

You can:

• store the last successful load watermark
• query only rows where updated_at > last_watermark
• load those rows into Redshift

Best for: simple APIs and tables with reliable update timestamps.

Risk: missed updates if timestamps are inconsistent or if deletes aren’t exposed.

---

2) CDC / event-based incremental loads

Some SaaS tools provide:

• change events
• audit logs
• “since cursor” APIs
• sync tokens

You track a cursor or event ID and continue from there.

Best for: platforms with strong incremental APIs.

---

3) Full extract + dedupe/merge

If the API doesn’t support incremental extraction well:

• pull full snapshots periodically
• compare/dedupe in Redshift
• merge into final tables

Best for: small datasets or poor APIs.

Downside: more expensive and slower.

---

Recommended architecture

Option A: Managed ELT tool

Use a connector tool that already handles incremental syncs:

• Fivetran
• Airbyte
• Stitch
• Matillion
• Hevo

Flow: SaaS → connector → S3/staging → Redshift

This is the easiest and most reliable if budget allows.

---

Option B: Build your own pipeline

Typical flow:

SaaS API → ingestion job → S3 → Redshift staging table → MERGE into target

Components:

• Scheduler: Airflow, Step Functions, cron, Dagster
• Extractor: Python, Lambda, ECS job, Spark
• State store: DynamoDB, SSM Parameter Store, RDS, or a metadata table in Redshift
• Landing zone: S3
• Warehouse load: Redshift COPY
• Upsert logic: MERGE or delete+insert pattern

---

Step-by-step setup

1) Identify the incremental key

Check the SaaS object schema for:

• updated_at
• created_at
• record version
• change cursor
• deleted records endpoint

If none exists, you may need:

• periodic snapshots
• webhook-based tracking
• audit log extraction

---

2) Store a watermark

Keep track of the last extracted point:

• max updated_at
• last cursor token
• last event sequence

Example metadata table:

create table etl_watermarks (
  source_name varchar(100),
  object_name varchar(100),
  last_watermark varchar(200),
  last_run_at timestamp,
  primary key (source_name, object_name)
);

---

3) Extract incrementally

Query the source using the saved watermark.

Example logic:

• WHERE updated_at > :last_watermark
• page through results
• save raw JSON/CSV to S3

Make sure to:

• handle retries
• respect API rate limits
• use idempotent writes

---

4) Load into Redshift staging

Load the extracted file into a staging table using COPY.

Example:

copy staging_orders
from 's3://my-bucket/saas/orders/2026-10-08/'
iam_role 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
json 'auto'
region 'us-east-1';

Use a staging table that mirrors the source shape.

---

5) Merge into final tables

Use Redshift MERGE if available in your cluster version, or emulate with delete + insert.

MERGE example:

merge into orders t
using staging_orders s
on t.order_id = s.order_id
when matched then update set
  customer_id = s.customer_id,
  amount = s.amount,
  updated_at = s.updated_at
when not matched then insert (
  order_id, customer_id, amount, updated_at
) values (
  s.order_id, s.customer_id, s.amount, s.updated_at
);

Delete + insert pattern:

begin;

delete from orders
using staging_orders s
where orders.order_id = s.order_id;

insert into orders
select * from staging_orders;

end;

---

6) Update watermark only after success

Only advance the watermark after:

• extraction succeeded
• load succeeded
• merge succeeded

This avoids data loss.

Example:

• source returned rows up to 2026-10-08T10:00:00Z
• after successful merge, store that as the new watermark

---

Handling deletes

Deletes are often the tricky part.

If source exposes tombstones/deleted records:

Load them and apply delete logic in Redshift.

If not:

Use one of these:

• periodic full refresh
• compare snapshots
• soft-delete logic if supported
• activity logs / audit events

---

Important design tips

Make loads idempotent

If a job reruns, it should not create duplicates.

Keep raw data

Store raw payloads in S3 for reprocessing and debugging.

Use staging tables

Never load directly into production fact tables.

Watch data types

SaaS APIs often return:

• strings for timestamps
• nested JSON
• nullable fields
• inconsistent enums

Normalize before final merge.

Plan for late-arriving updates

Use a small overlap window, for example:

• query updated_at >= last_watermark - 5 minutes

This reduces risk of missing records due to clock skew or delayed writes.

---

Minimal implementation pattern

1. Read last watermark from metadata table
2. Call SaaS API with incremental filter
3. Write results to S3
4. COPY into Redshift staging table
5. MERGE into target table
6. Save new watermark

---

If you want the fastest path

If your goal is simply “get incremental SaaS data into Redshift with minimal engineering,” use:

• Fivetran or Airbyte
• Redshift as the destination
• S3 only if you want extra control or raw backups

---

If you want, I can give you:

1. a reference architecture diagram,
2. a Python + Redshift example, or
3. a dbt/airflow incremental pattern for SaaS-to-Redshift loads.