MotherDuck is now a supported data store in Meltano and it’s built for more than convenience. Under the hood, it inherits a sync mode that turns hours-long loads into ones that finish while you’re still on your coffee break, so you’re not just skipping custom scripts, you’re skipping the wait too.With BATCH mode support carried over from Meltano’s DuckDB loading, it’s a genuinely fast way to get data into MotherDuck at scale.
MotherDuck: Serverless DuckDB for Fast, No-Ops Analytics
MotherDuck is a cloud data warehouse built on DuckDB, designed for fast, serverless analytics, whether you’re querying in SQL or asking questions in natural language. It’s built to serve everything from production applications to internal reporting, without the infrastructure overhead of managing your own warehouse.
Get a Pipeline Loading Into MotherDuck in Four Steps
Getting a pipeline loading into MotherDuck takes just a few settings and since this works through Meltano’s standard extract-load pattern, it’s not limited to one source. All 600+ of Meltano’s connectors can feed into MotherDuck once it’s set up.
- Create the target database in MotherDuck first. Meltano won’t create it for you. The database your connection string points to (for example, my_database in md:my_database) needs to already exist in your MotherDuck account before you run a pipeline.
- Add your MotherDuck connection string. This tells Meltano which database to load into.
- Add your MotherDuck token. This authenticates the connection to your account.
- Run your pipeline. Everything else, like batch size, schema, and metadata columns, has sensible defaults you can adjust as needed.
RECORD vs. BATCH: The Sync Mode That Changes Everything
Meltano’s DuckDB-based loading, the same foundation MotherDuck support is built on, handles Singer’s two sync modes very differently:
- RECORD mode (the default) sends data one row message at a time, similar to most custom implementations.
- BATCH mode writes rows to files and loads them in bulk instead.
A benchmark loading 5 million rows from two sources into both local DuckDB and cloud MotherDuck showed:
| Source | Destination | Sync Mode | Rows/sec | Improvement |
|---|---|---|---|---|
| MySQL | DuckDB (local) | RECORD | 32,786.0 | — |
| MySQL | DuckDB (local) | BATCH | 167,392.0 | ~411% |
| MySQL | MotherDuck (cloud) | RECORD | 17,118.0 | — |
| MySQL | MotherDuck (cloud) | BATCH | 147,541.0 | ~762% |
| PostgreSQL | DuckDB (local) | RECORD | 7,150.0 | — |
| PostgreSQL | DuckDB (local) | BATCH | 294,824.0 | ~4,023% |
| PostgreSQL | MotherDuck (cloud) | RECORD | 11,958.0 | — |
| PostgreSQL | MotherDuck (cloud) | BATCH | 147,146.0 | ~1,130% |
BATCH mode delivers a dramatic gain across every source and destination combination here, but the size of that gain depends on where the data is headed. Loading into local DuckDB, PostgreSQL sees an especially large jump under BATCH mode, reaching close to 295,000 rows/sec. Loading into MotherDuck, both sources land in a similar range under BATCH mode, around 147,000 rows/sec, since the destination itself becomes the shared bottleneck once the network is part of the equation.
The practical takeaway: BATCH mode is worth enabling everywhere, whether your destination is a local DuckDB file or MotherDuck in the cloud and the exact gain will depend on both your source and your destination.
Nine Settings That Put You in Control of Your MotherDuck Loads
Beyond the connection basics, you get control over exactly how data lands:
- Control your load size (Batch Size Rows): decide how many rows get pushed into MotherDuck in one go.
- Decide when to flush (Flush All Streams): flush every stream together once a batch fills up, instead of handling each one separately.
- Pick where data lands (Default Target Schema): set the schema new tables land in by default, so you’re not sorting it out after the fact.
- Track your data’s origin (Add Metadata Columns): tag loaded rows with ingestion metadata, so you always know when and how a row arrived.
- Choose how deletes behave (Hard Delete): decide whether deleted source rows are actually removed from MotherDuck or just marked as deleted.
- Handle nested data your way (Data Flattening Max Level): control how deep nested fields get unpacked into flat columns.
- Enforce data integrity (Primary Key Required): require a primary key on the output table so duplicate or orphaned rows don’t slip through.
- Catch bad records early (Validate Records): validate incoming records before they’re loaded, rather than finding out after the fact.
- Manage your staging space (Temporary Directory): choose where temporary files live while a load is in progress.
Most teams can leave these at their defaults and only revisit them once a pipeline is running and they know what they need to tune.
Ready to Load Your First Pipeline Into MotherDuck?
If MotherDuck is already part of your stack, it’s ready to use in Meltano today.Get started with Meltano and point your next pipeline at your MotherDuck database.