Singlestore to Amazon Redshift: How to Move Your Data

Moving SingleStore into Amazon Redshift is an unusual request on its face, because SingleStore already runs analytical queries quickly. The reasons people do it are cost and company: memory-optimised storage is expensive to fill with history, and your other sources are not in SingleStore.

Two things shape this build. The connector has no change data capture, which matters because SingleStore usually holds data that changes constantly, and Redshift cannot be loaded without a staging bucket.

SingleStore to Amazon Redshift at a glance:

Why move data from SingleStore to Amazon Redshift?

Two situations account for most of these pipelines, and neither is that Redshift queries faster.

The first is tiering by cost. SingleStore earns its price by keeping working data in memory, which is exactly the wrong economics for three years of history nobody queries weekly. Moving the cold tail to a warehouse lets the cluster stay sized for what it is good at.

The second is joining inside an AWS estate. If your warehouse, your cost reports and your other sources already live there, bringing operational data across puts everything in reach of one set of queries rather than two systems that never meet.

What do you need before you start?

The cursor question decides whether the result is trustworthy, and the bucket is a prerequisite you cannot skip:

A dedicated read-only user. With access to every table you intend to replicate. Creating one for the pipeline rather than reusing an application account means its queries are identifiable when somebody asks what is consuming the cluster. The SingleStore source documentation lists the requirements.

A cursor column per table. There is no change data capture, so incremental sync depends on a column that only increases and is set on every write. An updated timestamp maintained by your application is the usual answer, and tables without one are limited to full refresh.

An S3 staging bucket and IAM permissions. The Redshift destination writes data to S3 as files with a manifest, then issues a COPY command, which is Redshift's own recommended approach. You need put, get, delete and list on the bucket, in the same region as the cluster.

Network access and Redshift entities. The connector supports SSL and SSH tunnel connections, which covers most SingleStore deployments. On the warehouse side you need a database, a schema and a user able to write data and manage staging operations.

Note also that an SSH tunnel to Redshift covers the SQL connection only. Staging traffic reaches S3 over HTTPS regardless, so a network design assuming everything passes through a bastion is wrong.

How do you build a SingleStore to Amazon Redshift pipeline in Airbyte?

Step 1: Find your cursors and decide about deletes

Go through the tables you intend to replicate and identify a suitable cursor column on each. At the same time, ask whether rows are ever deleted or hard-updated in those tables, because the answer determines how much work the section below creates for you.

Step 2: Configure the SingleStore source

Click Sources in the left navigation, then New Source, and select SingleStore, following adding a source. Supply the host, port, database and read-only credentials, then configure SSL mode or an SSH tunnel according to how your cluster is reachable.

Step 3: Configure the Redshift destination

Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster host, database, schema and credentials, along with your S3 bucket and IAM key. Staging files are removed after a successful COPY by default, and can be retained if you need them for debugging.

Step 4: Nominate cursors and set your keys

Click Connections, then New connection, select your tables, nominate a cursor field for each and choose an incremental sync mode. Schedule it when the cluster is least busy, and set sort and distribution keys on the resulting tables, since those choices affect query performance more than anything in the pipeline.

Split your heaviest tables into their own connection so they do not hold up everything else, and so you can give them a different schedule if they turn out to need one.

Why does the missing CDC matter here specifically?

Because of what SingleStore is usually doing. The connector reads with a cursor rather than from a log, so a query filtered on a timestamp cannot observe an absence, and deletions never reach Redshift. That is true of many sources and it bites harder on this one.

A warehouse-to-warehouse pipeline usually reads tables that transformation jobs rebuild wholesale, where deletion barely applies. SingleStore is typically holding live operational state behind an application, where rows are updated and removed constantly, so the gap between source and destination is real rather than theoretical.

Two remedies, and the first is much better. Ask your application team to soft-delete, marking rows with a flag and an updated timestamp rather than removing them, which makes deletions visible to a cursor. Where that is not possible, schedule a periodic full refresh on those specific tables and accept the load it costs.

Why does this pipeline have two busy ends?

Because both systems are usually doing something else. SingleStore is serving an application that needed fast reads and writes, and Redshift has a finite number of concurrency slots shared with your analysts, so a large sync competes at both ends of the same pipeline.

That is unusual, and it makes the initial load worth treating as its own exercise. Run it during a window that is genuinely quiet for both, table by table if the volumes warrant it, rather than discovering the effect on your application's response times and your analysts' queries at the same moment.

Afterwards the ongoing syncs are far lighter, provided your cursors are doing their job. On the Redshift side, consider giving the pipeline its own workload management queue with a modest allocation so it cannot crowd out interactive work even when a table falls back to a full read.

Frequently asked questions

Does the SingleStore source support change data capture?

No. Incremental sync is cursor-based and append only, so deletions are not captured and updates are only seen when they advance the cursor column.

Do I need an S3 bucket to load into Redshift?

Yes. The destination stages data in S3 and loads it with COPY, which is Redshift's recommended approach. Put the bucket in the same region as your cluster.

How do I get deletions into Redshift?

Ask your application team to soft-delete with a flag and an updated timestamp, which a cursor can see. Otherwise schedule a periodic full refresh on the affected tables.

Why is my sync slowing down other queries?

It competes at both ends, with your application on the SingleStore side and with analysts on the Redshift side. Schedule off-peak, and consider a dedicated workload management queue.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. The only code you might want is a soft-delete change in your application, which is a schema decision rather than pipeline work.

Get your SingleStore data into Amazon Redshift

Find reliable cursor columns first and settle the deletion question with your application team, because soft deletes are far cheaper than repeated full refreshes. Provision the staging bucket in your cluster's region, run the initial load when both ends are quiet, and set sort and distribution keys deliberately.

Airbyte's connector catalog includes 700+ pre-built connectors, so operational data can sit beside the commercial sources that explain it. For the same source in another warehouse, see SingleStore to Snowflake, and for a similar relational source, MySQL to Amazon Redshift.

Start syncing now →