How to Load API Data into DuckDB with dlt

📘 Introduction

In this hands-on tutorial, you will learn how to load data from a REST API into DuckDB using dlt. This is a great first local data pipeline because you do not need a cloud warehouse, a complex setup, or production credentials.

We will use dlt to fetch data from an API, create a DuckDB database file, and inspect the loaded table locally.

💡 What are we implementing?

We are implementing the extract and load part of a data pipeline.

The flow looks like this:

API → dlt → DuckDB

dlt handles the loading process. DuckDB stores the data locally in a .duckdb file.

For this example, we use the public JSONPlaceholder API. It provides demo data that is useful for learning and does not require authentication.

✅ Prerequisites

☑️ Python installed
☑️ Basic Python knowledge
☑️ A terminal or code editor
☑️ Internet access for the public API request

⚙️ 1️⃣ Create a project folder

Create a new folder for the example:

mkdir dlt_api_duckdb_demo
cd dlt_api_duckdb_demo

Create and activate a virtual environment:

python -m venv .venv
source .venv/bin/activate

On Windows, activate it with:

.venv\Scripts\activate

Your folder structure now looks like this:

dlt_api_duckdb_demo/
└── .venv/

📦 2️⃣ Install dlt and DuckDB

Install the required packages:

pip install "dlt[duckdb]" duckdb

The dlt[duckdb] extra installs the DuckDB destination support for dlt. The duckdb package lets us inspect the database from Python or the DuckDB CLI.

🌐 3️⃣ Understand the API source

Before we write the pipeline, let’s quickly look at the data source.

We use the JSONPlaceholder API, a free public API for testing and tutorials. It provides fake JSON data such as posts, users, comments, albums, and todos.

For this tutorial, we use the posts endpoint:

https://jsonplaceholder.typicode.com/posts

Each record contains fields such as:

• userId
• id
• title
• body

This is useful for learning because the API does not require authentication or an API key.