You Probably Don't Need Spark to Move Data Between RDS and Your Lake

Spark by default

Ask a data team how they move tables from a relational database into their data lake, and there is a good chance the answer includes Spark. Glue jobs, EMR, Databricks: the tool changes, but the reasoning is usually the same: it’s data engineering, so it’s Spark.

Spark is a great tool for a specific problem: data that does not fit on one machine. But very few teams have that problem. Most companies run on a handful of application databases with tables of thousands to a few hundred million rows. That is not big data. It is data that a single, fairly small machine can move.

When you use Spark for it anyway, you pay for things you don’t need:

• A cluster, even if one node would do.
• Startup time, often longer than reading the table itself.
• Configuration and operations: executors, partitions, memory settings, and the knowledge to tune them.

I know because we’ve been through all three: our extractions started on Spark, moved to pandas, and now run on DuckDB.

Where the frontier really is

When I think about moving a table out of a database, I think in three zones:

Many teams start in zone 1 with pandas, and it works. Then a table grows, a task gets OOM-killed, and the next step feels obvious: “pandas doesn’t scale, let’s use Spark.” The jump goes straight from zone 1 to zone 3.

Zone 2 is painful with pandas because everything has to fit in memory at once. So you start working around it:

• Batches. Read the table in chunks (WHERE updated_at > x ORDER BY ... LIMIT n), which needs a sortable key per table and a batch size tuned per source. Each batch is another query, often on a column without an index, so a table read in 20 batches is scanned about 20 times.
• Bigger tasks for the big tables, and a separate job for the rest.
• Safety nets: a cap on the number of batches so a run can’t go forever, and a “safe mode” for backfills.

Each workaround is reasonable on its own. Together, they make a simple job complicated, and that complexity is what makes Spark look attractive.

DuckDB changes where the line is. It runs inside your Python process and does not need the whole table in memory: when it runs out, it spills to disk and keeps going. A table bigger than RAM is read in one pass, with no batches. If you haven’t used it, I wrote an intro in DuckDB and Beyond.

That’s 300M rows on a machine smaller than most laptops. The line isn’t infinitely far (one machine eventually runs out of disk, more on that later), but most teams are nowhere near it.

The idea: let the engine move the data

Our old pipeline was the obvious one: read a batch with pandas.read_sql, write it to the lake, repeat. It works, but every row makes a long trip: from the database driver to Python objects, to a DataFrame, to Arrow, to Parquet. Most of that is Python doing work that a database engine does much better.

DuckDB can skip the whole trip because it can attach both ends:

• The source database. An attached database looks like one more schema in DuckDB: you can SELECT from its tables, or send it a query to run on the server.
• The lake. The Iceberg extension attaches an AWS Glue catalog, and DuckDB can create and write Iceberg tables in it.

With both ends attached, moving a table is a SQL statement. The rows go from the source to S3 inside DuckDB’s engine, and Python becomes an orchestrator: it builds the SQL, runs it and logs.

These are the extensions we use, all of them installed with a single INSTALL:

Connects to	Extension	Docs
PostgreSQL	postgres (core)	PostgreSQL extension
MySQL	mysql (core)	MySQL extension
SQL Server	mssql (community)	mssql extension
Glue / Iceberg	iceberg (core)	Iceberg catalogs

In its simplest form, the whole idea fits in a few lines:

INSTALL postgres; LOAD postgres;
INSTALL iceberg; LOAD iceberg;

-- The source, read-only
ATTACH 'host=... dbname=app user=... password=...' AS src (TYPE postgres, READ_ONLY);

-- The lake: a Glue catalog, addressed by AWS account id
ATTACH '<aws account id>' AS lake (TYPE iceberg, ENDPOINT_TYPE 'glue');

-- Move a table
INSERT INTO lake.bronze.users
SELECT * FROM src.public.users;

No DataFrame, no batches, no driver loop. The real version has a few more steps (staging, merges, types that need a cast), which I’ll show in the next section.

I already hinted at this in the “acting as a data mover” part of DuckDB and Beyond. This time it’s not a toy example: it’s what runs our extractions every night.

How it works

The snippet above is the idea. The real thing has a few more steps, but not many more: the whole engine is about 1,300 lines of Python, adapters for three databases included. For each table, it does three things:

def extract_one(source, table, query):
    """Moves one table to the lake with DuckDB. Returns the number of rows written."""
    with DuckDBEngine(source) as engine:  # 1. attach the source
        engine.connect_lake()             #    ...and the Glue catalog
        engine.stage(table, query)        # 2. read the source once, into a temp table
        return engine.write(table)        # 3. create, append, merge or overwrite

All the snippets in this section are simplified versions of our code.

CREATE OR REPLACE SECRET aws_credentials (
    TYPE s3, KEY_ID '...', SECRET '...', SESSION_TOKEN '...', REGION '...'
);

ATTACH '<aws account id>' AS lake
    (TYPE iceberg, ENDPOINT_TYPE 'glue', DEFAULT_SCHEMA 'default');

CREATE OR REPLACE TEMP TABLE staged AS
SELECT
    "id",
    "name",
    upper(CAST("external_id" AS VARCHAR)) AS "external_id",  -- a GUID
    ...
FROM postgres_query('src', ?)  -- the ? is the query that runs on the source

SELECT
    [id],
    CAST([name] AS NVARCHAR(MAX)) AS [name],
    CAST([created_at] AS DATETIME2(3)) AS [created_at],
    LOWER(CONVERT(VARCHAR(MAX), CAST([org_path] AS VARBINARY(MAX)), 2)) AS [org_path]
FROM (SELECT * FROM dbo.employees WHERE updated_at > '2026-10-25') AS source_query

if not table_exists:
    mode = "create"     # CREATE TABLE ... AS SELECT * FROM staged
elif table.full_refresh:
    mode = "overwrite"  # DELETE + INSERT, in one transaction
elif table.merge_cols:
    mode = "merge"      # MERGE INTO ... ON the merge keys
else:
    mode = "append"     # INSERT INTO ... BY NAME

logger.info(f"Replacing every row of {table} (delete and insert in one transaction)")
con.execute("BEGIN")
try:
    con.execute(f"DELETE FROM {table}")
    con.execute(f"INSERT INTO {table} BY NAME SELECT * FROM staged")
    con.execute("COMMIT")
except BaseException as e:
    logger.error(f"Replacing the rows of {table} failed ({e}), rolling back: the old rows stay")
    con.execute("ROLLBACK")
    raise

class PostgresSource(Source):
    def attach_queries(self, alias):
        return [
            "INSTALL postgres",
            "LOAD postgres",
            f"ATTACH '{self.conninfo}' AS {alias} (TYPE postgres, READ_ONLY)",
        ]

    def scan(self, alias):
        return f"postgres_query('{alias}', ?)"

What we got

We moved three kinds of jobs from pandas to DuckDB.

In more detail:

Compute is vCPU-hours: how long each task ran, times its size. The numbers come from real runs on the real sources; the details of each migration, and how each number was measured, are in the appendix.

The nightly export is the least impressive line, and that’s on purpose. It is incremental (it only reads the rows that changed), so most of its time is spent waiting for MySQL to answer, and no engine makes a source database answer faster. Even there, we went from 11 jobs to 1 and used a fraction of the compute.

The trade-off: you write the write logic

With pandas, writing to the lake was one call to awswrangler.athena.to_iceberg. You give it a DataFrame, a mode, some merge_cols and schema_evolution=True, and it figures out the rest.

DuckDB writes Iceberg natively, but at a lower level: it gives you SQL statements, not a “write this table” function. Everything awswrangler decided for us, we now write ourselves. That’s about 400 lines of Python, and this is what they do:

Most of these are a few lines each. Two of them are worth showing.

The merge. DuckDB’s QUALIFY makes the deduplication a one-liner, and then the merge is plain SQL:

-- One row per key: the latest version wins
CREATE OR REPLACE TEMP TABLE staged AS
SELECT * FROM staged
WHERE id IS NOT NULL
QUALIFY row_number() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1;

MERGE INTO lake.raw.employees AS t
USING staged AS s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET
    name = s.name,
    updated_at = s.updated_at,
    ...,
    _extracted_at = s._extracted_at,
    _num_writes = t._num_writes + 1,          -- how many times this row was written
    _first_exported_at = t._first_exported_at  -- kept from the first time it was seen
WHEN NOT MATCHED THEN INSERT BY NAME;      -- new rows: _num_writes = 1, _first_exported_at = now

Owning the merge has a nice side effect: we decide what happens to every column, not only the source ones. With awswrangler, an update replaced the whole row. Now each row also carries some metadata about its own history:

• _extracted_at: when the row was last extracted.
• _first_exported_at: when it first reached the lake, kept across updates.
• _num_writes: how many times it has been written. A row that changes every night stands out immediately.

It’s two lines in the MERGE, and it answers questions like “when did this record first appear?” without digging through Iceberg snapshots.

The commit retry. Glue’s Iceberg endpoint fails a commit every now and then, and retrying a write is not always safe: a 502 can mean the commit landed anyway, and appending the same rows twice duplicates them. So before retrying a create or an append, we check whether the rows are already there:

for attempt in range(1, COMMIT_TRIES + 1):
    try:
        write_rows()
        return
    except duckdb.Error as error:
        if not is_transient(error) or attempt == COMMIT_TRIES:
            raise
        logger.warning(f"Committing to {table} failed ({attempt=}): {error}")
        if mode in ("create", "append") and rows_landed(table):  # by this run's extraction timestamp
            logger.warning("The rows are in the table already, not writing again")
            return
        time.sleep(WAIT_SECONDS * 2 ** (attempt - 1))

The wait doubles after every attempt (5 s, 10 s, 20 s…): an exponential backoff, so a struggling Glue gets more breathing room each time. A merge or an overwrite gives the same table when run twice, so those are simply retried.

We wrote the loop by hand because of the “did it land?” check in the middle. If you only need the retries, backoff gives you the same in a decorator:

@backoff.on_exception(backoff.expo, duckdb.Error, max_tries=5, giveup=lambda e: not is_transient(e))
def write_rows():
    ...

What we learned

Running many tables in parallel on one machine raises three questions: how many at once, how much memory each one gets, and how much disk. These are the lessons we learned answering them.

MEMORY_FRACTION = 0.7  # of the container, shared by every DuckDB alive in the process
ENGINES = []           # the DuckDBs alive right now
ENGINES_LOCK = threading.Lock()


def share_memory():
    """Gives each live engine its part of the budget. Call it when one starts or ends."""
    limit_mb = get_container_memory_mb()
    mb_per_weight = limit_mb * MEMORY_FRACTION / sum(e.weight for e in ENGINES)
    for engine in ENGINES:
        share_mb = int(mb_per_weight * engine.weight)  # weight is 2 on a retry, 3 on the next
        # A separate cursor, because the engine's connection is busy running its query
        engine.control.execute(f"SET memory_limit = '{share_mb}MiB'")

When you do need Spark

This post is not “Spark is bad”. Spark is the right tool for problems that don’t fit on one machine. The point is that most extractions are not that problem.

And I’m not saying it from theory: two of the jobs in this post started on Spark.

• The SQL Server extraction ran on Spark before pandas. It took around 40 minutes, slightly slower than DuckDB’s 30, and cost far more.
• The MySQL export was even worse: one cluster per database, so 11 clusters every night, to move data that now fits on a single 8 GB task.

With that said, these are the signs I’d look for before reaching for a cluster:

• A single table doesn’t fit on one machine’s disk. That’s the edge we found in the previous section, and it’s a real one. Reading the table in batches (time windows, say) can push it further, but then you’re back to the kind of complexity DuckDB removed in the first place.
• The work is more than moving rows. Big joins across large datasets, or heavy aggregations that need to shuffle data between machines, are what Spark was built for.
• You already run Spark well. If the platform, the team and the knowledge are already there, the extra cost of using it for extractions is much smaller.

And before any of those, remember that one machine goes a long way. A bigger task, more disk, or reading the biggest tables in windows are all cheaper steps than a cluster.

Conclusion

Spark has become the default answer to “how do we move this data?”, and for most teams it’s the wrong one. Not because Spark is bad, but because their tables aren’t big enough to need it.

pandas is great until a table stops fitting in memory, and then it gets complicated fast. DuckDB fills the gap between the two: it reads a table bigger than memory in one pass, attaches the source and the lake directly, and lets Python do what it’s good at, orchestrating.

For us, that meant one job instead of many, 70% to 95% less compute, and full extractions several times faster, on machines smaller than a laptop. The price was owning a few hundred lines of write logic, and learning where one machine stops.

If you try it, or if you’ve hit a limit I haven’t, I’d love to hear about it in the comments.

Appendix: the migrations in detail

The numbers behind the What we got table, for those who want them.