DuckDB: The Swiss Army Knife For Data Engineers
Replace all your hacky pandas scripts, ETL workarounds and more with the simplest and most amazing tool out there.
Hi there! Alejandro here 😊
Subscribe if you like to read about technical data & AI learnings, deep dives!
Enjoy the reading and let me know in the comments what you think about it 👨🏻💻
📝 TL;DR
DuckDB replaces pandas, Spark, and Airflow for 80% of use cases.
Query S3, databases, and APIs directly with SQL.
No infrastructure, no data movement, no complexity. DuckLake brings lakehouse features without Iceberg overhead. Stop over-engineering and start shipping.
Key Takeaways:
Query anything (S3, Postgres, APIs) without downloading
Run ETL pipelines with plain SQL, zero infrastructure
Process datasets larger than RAM without memory errors
Deploy anywhere: scripts, web apps, Lambda functions
DuckLake = lakehouse simplicity without Delta/Iceberg complexity
I don’t why exactly, but analytics roles have been conditioned to believe that the more complexity we have, the cooler our data stack will be.
Pandas for analysis, Spark for big workflows, Airflow for orchestration, a data warehouse for storage, and about 47 YAML files to make it all work together.
Think about the typical data workflows:
S3 > ETL with Pandas(Clean, JOIN, etc) > save output
Or maybe you’re fancy and you:
S3 Streaming > ETL with Spark Jobs > save output
With all the hype around modern data stacks, the same pattern comes knocking our doors over and over, as if ALL companies need a lakehouse architecture with 15 microservices to analyze a couple CSV files.
As I like to say, most of the time you need too much, what if I told you you just need one thing?
🦆 Enter DuckDB: One Tool, ♾️ Use Cases
DuckDB is an in-memory analytical database.
That’s a fancy way of saying it runs inside your application without any separate server or setup.
You can query anything, anywhere, without moving data around.
And before we dive into the use cases, here are some quick wins you get immediately:
Query 50GB files on your 8GB laptop without memory errors because DuckDB streams through data intelligently
Prototype your entire dbt project locally without all the warehouse connection overhead and cost considerations.
Deploy analytics anywhere because DuckDB databases are single files you can ship in CLI tools, web apps, or AWS Lambda functions
Run simple pipelines without Airflow because a Python script with DuckDB in a cron job handles 80% of orchestration needs
If you want some context on performance and portability before deep diving, you can take a look at:
📊 #1 ETL Without the Infrastructure Tax
Remember that S3 to pandas to S3 workflow? Here’s the DuckDB version:
No downloading. No memory errors. No Airflow DAG with 15 tasks.
Just SQL that reads from S3, processes in parallel, and writes back.
DuckDB handles the streaming, the parallelization, and the memory management automatically.
What data migration between systems?
No intermediary tools needed. No writing to local disk first. Just point A to point B.
Even for Change Data Capture (CDC) this could be so powerful!
Recommended Reads:
🔗 #2 Cross-Database Joins Without Moving Data
For example, do you need to join your production PostgreSQL database with that CSV a stakeholder sent you?
Normally you’d export from Postgres, load into pandas, merge, and hope nothing breaks.
But in DuckDB?
Recommended Reads:
🚀 #3 Great Flexibility: APIs, Google Sheets, Vector Stores and More
Think about APIs.
Usually you’d write a script with the requests library, parse JSON, handle pagination, write to CSV, then analyze.
Here’s what that looks like with DuckDB:
Another amazing thing where DuckDB gets wild: it has extensions for almost every data source you can think of.
Want to query a Google Sheet directly? There’s an extension for that:
Need semantic search on your documents? The VSS extension gives you vector similarity search:
At the end of the day, DuckDB’s extension ecosystem means you can connect to almost any data source without writing custom connectors or ETL scripts.
Recommended reads:
🏠 #4 Working with Data Lakes & Lakehouses Without the Overhead
You know those partitioned parquet folders that look like data/year=2024/month=11/day=08/*.parquet?
DuckDB treats them like a proper data warehouse with zero setup:
DuckDB only reads the partitions you need. It’s doing what Spark does, on your laptop, with readable SQL.
But here’s where it gets interesting: The DuckDB team recently introduced DuckLake, an open table format that gives you lakehouse capabilities without the complexity of Apache Iceberg or Delta Lake.
For lakehouses, you need to understand manifests, snapshots, transaction logs, and compatibility matrices.
DuckLake stores metadata in a SQL database (e.g. PostgreSQL).
Once again, they turned high complexity and overhead into a SQL friendly setup.
You get:
ACID transactions for reliable data operations
Time travel to query historical versions of your data
Schema evolution without breaking existing queries
Concurrent access with multiple DuckDB instances
All with SQL compatibility:
I love the concept of a local lakehouse that handles your analytical workloads without forcing you to become a distributed systems expert.
I barely understand these concepts and my experience comes from reading and experimenting, since I always worked in the warehouse side of things.
In the past, even if I concluded a lakehouse was a good option for a data platform setup, I would have discarded it because of maintenance complexity, but now there’s a different outlook on how complex this could be.
🔧 #5 Data Quality Checks Without Complex Frameworks
Data quality tools are great, but sometimes you just need to validate data quickly. DuckDB makes this dead simple:
Just hook these into Github Actions and you have a CI/CD pipeline in place right away.
🤔 When DuckDB Might Not Be the Answer
Here’s when you should consider alternatives to DuckDB:
You actually need distributed computing: Processing petabytes across hundreds of nodes where scaling is key? Snowflake or BigQuery might be the wat to go.
You need real-time OLTP: DuckDB is for analytics (OLAP), not high-frequency transactions (OLTP) you can get with systems such as PostgreSQL.
You need a central data warehouse with strict governance: For teams sharing data with access controls and audit logs, a proper warehouse makes sense.
At the end of the day, the best tool is the simplest one that solves your problem.
DuckDB just happens to be simple and solves a lot of problems.
🎓 How to Get Started
Pick one thing you’re doing today that involves downloading data, processing it, and uploading it somewhere else.
Rewrite it with DuckDB.
pip install duckdbOnce you see how much simpler it is, you’ll start finding more places to use it.
Recommended Reads:
If you enjoyed the content, hit the like ❤️ button, share, comment, repost, and all those nice things people do when like stuff these days. Glad to know you made it to this part!
Hi, I am Alejandro Aboy. I am currently working as a Data Engineer. I started in digital marketing at 19. I gained experience in website tracking, advertising, and analytics. I also founded my agency. In 2021, I found my passion for data engineering. So, I shifted my career focus, despite lacking a CS degree. I’m now pursuing this path, leveraging my diverse experience and willingness to learn.
Hey, great read as always. Your point on over-engineering really resonates. How does DuckLake achieve its lakehouse simplicity compared to Delta/Iceberg?
Spot-on about the overengineering trap. The DuckLake part really stands out, turningIceberg complexity into SQL-friendly metadata storage is a game changer. We've dealt with similiar setups where the lakehouse overhead killed the value prop. The idea of querying S3 directly without all the infrastructure dance is super practical, especially for teams that don't need petabyte-scale but still want modern features.