Skip to main content

dltHub (dlt)

dltHub builds dlt, an open-source Python library that loads data from various, often messy data sources into well-structured, live datasets. It offers a lightweight interface for extracting data from REST APIs, SQL databases, cloud storage, Python data structures, and many more, with MotherDuck as a built-in destination.

How it works with MotherDuck​

dlt (data load tool) is the open-source library dltHub builds, and it's designed to be easy to use, flexible, and scalable:

  • dlt infers schemas and data types, normalizes the data, and handles nested data structures.
  • dlt supports a variety of popular destinations and has an interface to add custom destinations to create reverse ETL pipelines.
  • dlt runs anywhere Python runs, be it on Airflow, serverless functions, MotherDuck Flights, or any other cloud deployment of your choice.
  • dlt automates pipeline maintenance with schema evolution and schema and data contracts.

dlt uses DuckDB as its local development destination and as the engine behind dltHub's project cache, so a pipeline you develop locally against DuckDB loads into MotherDuck by switching the destination. dltHub also offers a managed platform for deploying, monitoring, and scaling dlt pipelines.

For the destination reference, see the dlt MotherDuck destination documentation.

Prerequisites​

pip install "dlt[motherduck]"

Authentication​

To authenticate with MotherDuck, you have two options:

  1. Environment variable: export your token as MOTHERDUCK_TOKEN, which the destination picks up automatically:
export MOTHERDUCK_TOKEN="<your_motherduck_token>"
  1. Local development: add the token to .dlt/secrets.toml, optionally with the target database:
[destination.motherduck.credentials]
database = "<your_database>"
password = "<your_motherduck_token>"

Minimal example​

Below is a minimal example of a pipeline that generates dummy GitHub-like data and loads it into MotherDuck:

import random
from datetime import datetime
from typing import Dict, Iterator, List, Sequence

import dlt
from dlt.sources import DltResource


@dlt.source(name="dummy_github")
def dummy_source(repos: List[str]) -> Sequence[DltResource]:
    """A source that generates dummy GitHub-like data."""
    return (dummy_repo_info(repos), dummy_languages(repos))


@dlt.resource(write_disposition="replace")
def dummy_repo_info(repos: List[str]) -> Iterator[Dict]:
    for repo in repos:
        owner, name = repo.split("/")
        yield {
            "id": random.randint(10000, 99999),
            "name": name,
            "full_name": repo,
            "owner": {"login": owner},
            "created_at": datetime.now().isoformat(),
            "stargazers_count": random.randint(0, 1000),
        }


@dlt.resource(write_disposition="replace")
def dummy_languages(repos: List[str]) -> Iterator[Dict]:
    for repo in repos:
        for language in random.sample(["Python", "Rust", "Go"], 2):
            yield {
                "repo": repo,
                "language": language,
                "bytes": random.randint(1000, 100000),
            }


def run_minimal_example():
    pipeline = dlt.pipeline(
        pipeline_name="minimal_github_pipeline",
        destination="motherduck",
        dataset_name="minimal_example",
    )

    info = pipeline.run(
        dummy_source(["example/repo1", "example/repo2"]),
        loader_file_format="parquet",
    )
    print(info)


if __name__ == "__main__":
    run_minimal_example()

dlt revolves around three core concepts:

  • Sources: Define where the data comes from.
  • Resources: Represent structured units of data within a source.
  • Pipelines: Manage the data loading process.

In the example above, dummy_source defines a source that simulates GitHub-like data, dummy_repo_info and dummy_languages are resources producing repository and language data, and the pipeline loads both into MotherDuck.

The core integration with MotherDuck is defined in the pipeline configuration:

pipeline = dlt.pipeline(
    pipeline_name="minimal_github_pipeline",
    destination="motherduck",
    dataset_name="minimal_example",
)

Setting destination="motherduck" tells dlt to load the data into MotherDuck. Passing loader_file_format="parquet" in the run call keeps the loading path on Parquet and COPY rather than falling back to row-wise insert_values, which is significantly slower against a remote database.

Start from a source connector​

Instead of hand-writing a source, scaffold one of dltHub's verified sources with MotherDuck as the destination:

dlt init salesforce motherduck
pip install -r requirements.txt

That generates a pipeline script and a .dlt/secrets.toml for both the source and MotherDuck credentials. See Salesforce for a worked example.

Run pipelines on MotherDuck compute​

dlt pipelines run as Flights, MotherDuck's scheduled Python jobs, so a pipeline can ingest on a cron without separate infrastructure. The Flight runtime injects MOTHERDUCK_TOKEN for you, which the MotherDuck destination reads automatically.

Known limitations​

  • Use the motherduck destination rather than the generic duckdb destination pointed at md:. The defaults differ, and only the MotherDuck destination is tuned for remote loading.
  • The insert_values loader format works but is much slower than Parquet for remote loads.
  • If loads hit timeouts and retries, lower the number of load workers to 3-5 with the LOAD__WORKERS environment variable.

Related content​