Direct SQL Access to External Data Sources

This chapter contains three different direct access examples.

Copy code
❯ duckdb
v1.1.1 af39bd0dcf
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D SELECT stargazers_count
    FROM read_json('https://api.github.com/repos/duckdb/duckdb');
┌──────────────────┐
│ stargazers_count │
│      int64       │
├──────────────────┤
│            23620 │
└──────────────────┘

Data Lake Solutions

A very popular use case is data lake solutions with DuckDB; here, we look at two different ways of doing so.

Framework Integration with Ibis and PRQL

There are exciting extensions to DuckDB which extensively integrate the data ecosystem. For example:

• Ibis: The portable Python dataframe library
• PRQL with the DuckDK Extension: A modern language for transforming data.

These allow writing transformations in Ibis or PRQL and executing them with DuckDB or any other compute supported by the framework. The advantage here is that you have generalist, declarative language to define transformations but can still use DuckDB's power for the execution part. Besides that, you could also easily switch to Druid, ClickHouse, or others with these libraries.

For example, Gil Forsyth mentions processing 1.1 billion rows of PyPI package data using DuckDB through Ibis in about 38 seconds on a laptop, using only about 1GB of RAM and 20 logical cores. Gil notes that even on slower laptops, the query would still be completed successfully due to the low memory usage, which takes longer to execute.

Modern Data Stack Implementation

For example, having an end-to-end data stack based on open-source, also called MDS in a box, is a perfect use-case where DuckDB can be used as light-weight compute, either as a single data store or as computing SQL's on dbt, for example, or querying within the BI tools your S3 or other sources—everything running on a single laptop.

There are great examples of these concepts, including the inception article by Jacob on Modern Data Stack in a Box with DuckDB, where he uses Meltano, dbt, and Apache Superset besides DuckDB. This runs at mdsinabox.com.

Another exciting one is by the  New South Wales Department of Education, which features DuckDB, Dagster, dbt, dlt, and Evidence to power their new data portal. Or David's local-first open data platform, where he uses DuckDB to provide a serverless data platform called Datadex. David runs the stack in production with MotherDuck and GitHub Actions.

Local-First Development and Testing

This is a vast real-time production use case. However, it's hard to find examples as it's done in the background. Testing locally, 100 times a day, before going into production can save much money and speed up testing cycles. Spinning up a cluster, running the deployment scrips, and publishing large docker images is unnecessary.

On Reddit, someone stated that for dbt users with BigQuery warehouses, DuckDB enables efficient local testing by isolating BigQuery-specific code into ephemeral models and mocking them in dbt. This approach keeps warehouse-specific code thin and untested while enabling comprehensive testing of core business logic locally. Some developers also use SQLGlot with DuckDB to test BigQuery SQL locally—so-called unit-testing without warehouse dependencies.

Someone else said they're using it in combination with Ibis and Snowflake. For tests, they patch the Snowflake connection with a connection to a local DuckDB test database. This works quite well, although you won’t catch all errors. Or quickly query Snowflake tables locally without the Warehouse, with universql.

Another use case is data diffs, which quickly compare datasets using SQL queries. DuckDB has one of the fastest Parquet/CSV readers, integration with Postgres and others through plugins, and easy-to-work-with CSV, all of which make It super developer-friendly and suited for fast iterative development and testing.

Composable Python Query Relations

An intriguing feature or use case I found with Python integration is its relational API. When writing queries in Python, DuckDB returns a "relation" object (an abstract representation of the query) rather than immediately materializing the total result. This relation can be stored in a variable and used in subsequent queries, making SQL more composable. The query planner optimizes the final composite relation, allowing for better performance.

Ned mentions, "You get this lazy representation of your query that will until you ask it fully to materialize or just peek at the first bit, it won't evaluate anything." Viewing in a notebook shows only the first 10,000 records by default for preview purposes.

Large-Scale Configuration Management

Reading large config files on the fly is another small but powerful use case. Chris used a C# desktop app that collects large amounts of configuration and performance data. They load it into DuckDB and then send it to their cloud processing engine for report generation. They are impressed with it and are looking at other use cases.

Matthew does similar with handling large result sets and querying everything from files to iceberg tables at Coginiti.

Multi-User Performance Management

GoodData's comprehensive evaluation of DuckDB and MotherDuck for production use offers another compelling example of DuckDB's enterprise readiness. Through rigorous testing of over 700 analytics-focused test cases developed over 15 years, GoodData found MotherDuck outperforming Snowflake and PostgreSQL in performance tests, particularly for analytical workloads with relatively small data volumes and parallel query execution.

However, GoodData's testing also revealed essential considerations for production deployment, including non-blocking limitations with ISO date arithmetic, a lack of query cancellation, and one blocker with downloading runtime extensions every time, some of which are resolved by now. Despite limitations, they concluded that MotherDuck is production-ready for analytics use cases, particularly praising its efficiency in handling concurrent users and analytical queries.

Enterprise ETL Optimization: FinQore (formerly SaaSWorks) Case Study

At FinQore (formerly SaaSWorks), implementing DuckDB transformed their data pipeline performance from eight hours to just eight minutes, with the potential for further optimization to seconds. Their production system processes complex financial data from multiple source systems, a task that traditionally required manual Excel (btw Excels Never Dies) reconciliation even for sophisticated businesses. They are replacing their Postgres datasets with DuckDB, particularly for front-end operations, due to DuckDB's fast performance for analytical workloads.

The medallion showcase example with (Bronze → Silver → Gold) shows a data pipeline's impressive performance handling complex transformation, processing nested JSON, and transforming nearly 6 million records across 24 Parquet files in under a minute. The architecture leverages DuckDB's in-memory processing capabilities while maintaining data integrity through carefully designed partitioning schemes and atomic writes.

ELT Pipeline Integration with dlt

Extending capabilities and speed of a data pipeline initial load. Exporting and importing data from Postgres to Postgres can be non-straightforward, especially if you need to track schema changes, delta load, etc.

Therefore, using a tool made for ELT, something like dlt, is better. But what if the initial load from Postgres to Postgres must be improved? Changing the export as parquet and import to Postgres with an in-memory DuckDB speeded up the process order of magnitude. The parquet has been written but imported via ATTACH cmd in DuckDB, which imports parquet files into Postgres directly. We ended up using this in production when I was at Bedag.

Cost-Efficient Pipeline Design

Cost-efficient data pipelines have mainly three parts: optimizing data processing, storage, and workload management. According to Joseph, DuckDB excels in these areas, offering a powerful in-memory processing engine that can efficiently handle datasets up to 100+GB on a single machine. When combined with ephemeral VMs, DuckDB enables significantly reduced data processing costs.

Its ability to leverage the full power of a VM's resources and fast read and write operations through C++ extensions optimizes data pipelines' processing and storage aspects. Furthermore, DuckDB contributes to efficient workload management by simplifying the development, testing, and debugging process. Its integration with Python creates an accessible work environment, leading to faster development cycles and easier maintenance. Streamlines ETL operations, enabling organizations to process large volumes of data cost-effectively without the overhead of managing distributed systems.

Interactive Analytics Platforms

Here are examples of embedding DuckDB into dashboards, notebooks, or use cases integrated with WebAssembly.

Developer Tools

Besides dashboards and notebooks, here are examples of DuckDB integrated into our dev tools and databases.

AI Integration Solutions

Lastly, in this category, we can also embed AI with DuckDB.

With the duckdb-nsql, a 7B parameter text-to-SQL model that is lightweight and enables DucKDB SQL assistance features at lower latency, primarily focusing on analytical queries / SELECT statements. Check out how to use Ollama before discussing SQL Workbench.

Or use the integrated prompt() function within SQL, a new feature that MotherDuck's IDE provides. There are many more Retrieval-Augmented Generation (RAG use cases people mentioned, e.g. for its cosine similarity function and to store doc embeddings.

Security Platform Implementation: Okta Case Study

Okta manages a security-focused data platform to efficiently manage high-volume secure data. The focus was processing complex data logs from multiple sources (e.g., AWS CloudTrail, VPC flow logs) for security monitoring, anomaly detection, and downstream workflow triggering. DuckDB played a crucial role in optimizing data processing costs and performance.

DuckDB proved to be an ideal solution for scalable, cost-efficient embedded OLAP in security-heavy workloads. Its ability to handle high data volumes, integrate seamlessly into cloud workflows (via Lambda and S3), and reduce reliance on expensive cloud warehouses (like Snowflake) makes it a powerful tool for modern data platforms, especially in environments where dynamic data processing is critical.

This case highlights how DuckDB can effectively process and optimize security data workloads upstream of traditional data warehouses, reducing costs while maintaining high performance. Okta handles sensitive data and uses DuckDB to process sensitive security data.

In six months, their defensive cyber operations team processed 7.5 trillion records across 130 million files using thousands of concurrent DuckDB instances, handling data spikes from 1.5 TB to 50 TB per day without infrastructure changes. This approach dramatically reduced their data processing costs from $2,000/day with Snowflake while maintaining system robustness and security.

Data Governance Integration: DuckLake with Unity Catalog

Integrating the data governance unity catalog with DuckDB is a good practice for getting to an enterprise data platform and handling data more securely. Xebia documented their journey to use synergies between DuckDB and the OSS Unity Catalog.

They called it the "DuckLake". Unlike the data lakes we will discuss in the first chapter of zero-copy, this approach focuses on metadata and integrating the data stack.

DuckLake combines data governance capabilities and DuckDB's analytical power through Unity Catalog integration. The solution provides centralized metadata management and will soon support enhanced security features (with RBAC coming in Unity Catalog 0.2.0). While currently limited to read-only operations due to delta-kernel-rs dependencies, workarounds exist through dbt-duckdb and custom tooling for write operations.

The DuckLake approach offers a practical path forward for enterprises seeking to maintain control over their data assets while leveraging DuckDB's performance. The integration handles everything from schema definitions to access patterns, creating a robust foundation for secure data operations—even as the ecosystem matures with upcoming features and improvements.

On-Demand Server Deployment

Another security improvement is not only bringing the database to the app, but the whole server. With the HTTP API Server Extension, we can quickly spawn a server as part of our analytics environment when needed and shut down when finished—an HTTP OLAP server on-demand.

Other benefits include avoiding requiring Docker or a long-running process, which minimizes setup difficulty. Or it can replace a complex Spark cluster if data works for DuckDB but still uses the unique Spark dataframe API. For example, Atlan replaced their Spark with DuckDB, orchestrated with ArgoCD, and improved performance to ~2.3x faster than PySpark at the pod level.

Cloud Integration: MotherDuck's Advantages

MotherDuck wrote the paper about dual execution. Its backbone is the differential storage and its powerful UI. Let's explore them below.