Loading Data into Hotdata with dlt

dlt is an open-source Python library for building data pipelines. It takes care of pulling data from a source, figuring out the schema, and loading it in batches. Hotdata is now listed as one of dlt's community destinations. With hotdata-dlt-destination, any dlt source can load straight into a Hotdata instant database, ready to query as soon as the pipeline finishes.

How it works

The destination creates an instant database for you on the first run and can keep loading into it after that. Each batch is uploaded as Parquet and added to the database. Everything you'd expect from dlt carries over, including nested child tables, dlt's internal columns (_dlt_id and _dlt_load_id), schema versioning, and load tracking.

Pipeline state lives in the instant database too, so incremental sources pick up where they left off on the next run. You can use the append, replace, or merge write modes, and merge upserts rows by primary_key.

To read the data back, you use dlt's usual pipeline.dataset() interface and get pandas, Arrow, SQL, or ibis. The queries run on Hotdata's servers using Apache DataFusion, results come back as Arrow, and you don't need to write any Hotdata-specific code. When you write SQL, you can use PostgreSQL, DuckDB, or Snowflake syntax.

It also works in the other direction. hotdata_table() and hotdata_query() turn a Hotdata table or SQL query into a dlt resource, so you can move data out of Hotdata into any other dlt destination.

Loading and querying data

First, install the package and set your API key:

pip install hotdata-dlt-destination
export HOTDATA_API_KEY="your_api_key"

Then define a resource and run the pipeline:

import dlt
from hotdata_dlt_destination import hotdata

@dlt.resource(name="customers", write_disposition="append")
def customers():
    yield [
        {"id": 1, "name": "Alice", "amount": 99.99},
        {"id": 2, "name": "Bob", "amount": 49.50},
    ]

pipeline = dlt.pipeline(
    pipeline_name="my_pipeline",
    destination=hotdata(
        workspace_id="your_workspace_id",
        database_name="sales",
        declared_tables=["customers"],
    ),
)

pipeline.run(customers())

The first run creates an instant database and prints its id. Pass that id as database_id= on later runs to keep loading into the same database; otherwise, each run creates a new one.

You can read the data back through the same pipeline:

ds = pipeline.dataset()

ds.table("customers").df()
ds("SELECT name, sum(amount) AS spend FROM customers GROUP BY name").df()

Try it out

A good first step is to take a dlt source you already use, like sql_database against Postgres, and point it at Hotdata instead. Run it, then query the result.

For more, see the dlt guide for configuration, incremental loading, and partition and sort keys, and the repository for demo pipelines.