dlt (data load tool) for Data Engineers: Schema Inference, Incremental Loads & Load Modes

dlt data load tool is the open-source Python library that turns the messiest, most repetitive half of every pipeline — pulling data out of an API or database and landing it, correctly typed, in a warehouse — into a few lines of ordinary Python. It is not a platform, not a UI, not a service you log into. You pip install dlt, write a generator that yields dictionaries, and call pipeline.run(...). dlt infers the schema, unnests your nested JSON into child tables, tracks how far it got so the next run is incremental, and writes with the load mode you asked for — full refresh, append, or upsert.

That is a genuinely different shape from the two options data engineers reached for before it: a managed connector platform (Fivetran, Airbyte) that you configure but cannot easily embed, or a hand-rolled requests + INSERT script that works until the API adds a column and the load silently corrupts. This guide walks through the four ideas an interviewer will actually probe — the resource / source / pipeline object model, automatic schema inference and evolution, incremental loading with cursor state, and the replace / append / merge write dispositions — and pairs each with a Solution-Tail interview answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the ETL practice library →, rehearse the load-shape decisions on the data-transformation practice set →, and harden your retry logic on the idempotency practice set →.

---

On this page

• Why dlt changes Python ingestion in 2026
• Sources, resources & the pipeline object
• Schema inference & evolution
• Incremental loading with cursor state
• Write dispositions — replace, append, merge
• Cheat sheet — dlt recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why dlt changes Python ingestion in 2026

dlt is a library you embed, not a platform you operate — that one fact decides where it fits

The one-sentence invariant: dlt runs inside your own Python process, so ingestion becomes a dependency you import rather than a service you provision. Everything that makes dlt attractive to a data engineering team follows from that. There is no connector cluster to run, no separate control plane, no vendor row-based bill; a dlt pipeline is just code that runs anywhere Python runs — a Lambda, an Airflow task, a GitHub Action, your laptop.

The EL split — what dlt does and deliberately does not do.

• Extract. dlt gives you helpers for the common source shapes — REST APIs (with pagination and auth), SQL databases, filesystems, cloud storage — but any Python generator that yields dicts is a valid source.
• Load. dlt owns the hard part: inferring types, creating and evolving tables, staging files, and writing to the destination (DuckDB, BigQuery, Snowflake, Redshift, Postgres, Athena, and more) atomically.
• Transform is out of scope on purpose. dlt is the EL in ELT. Business logic — joins, dimensional models, metrics — belongs downstream in dbt or SQL, against the clean, typed tables dlt produced. Keeping transform out is why dlt stays small.

Where dlt sits against the alternatives.

• vs a hand-rolled script. A requests loop plus INSERT statements has no schema management, no incremental state, no retries, and no idempotency. The first schema drift or partial failure corrupts the table. dlt handles all four for free.
• vs Fivetran / Airbyte. Managed connectors are excellent when a prebuilt connector exists and you want zero code. dlt wins when you need a connector that does not exist, when you want ingestion versioned in your repo, or when you cannot send data through a third-party plane for compliance reasons.
• vs Singer / Meltano. dlt covers the same tap/target ground but as one Python library with typed schemas and native destinations, instead of a spec plus a runner plus separate tap and target processes.

What interviewers listen for.

• Do you say "dlt is a library, not a platform" in the first sentence? — senior signal.
• Do you place dlt as "the EL, with transform left to dbt" unprompted? — required framing.
• Do you reach for dlt when the answer is "there is no Fivetran connector for this source" rather than as a Fivetran replacement for everything? — senior signal.
• Do you mention schema evolution and incremental state as built-ins, not things you write yourself? — the whole point.

Worked example — five lines that replace a hundred

Detailed explanation. The canonical dlt "hello world" loads a list of Python dictionaries into a local DuckDB warehouse. It looks trivial, and that is the point: the same five lines that load a toy list scale unchanged to a paginated REST API, because dlt only ever sees a stream of dictionaries. The library infers the schema, creates the table, and writes the rows — no CREATE TABLE, no type mapping, no connection boilerplate that you maintain.

Question. Load two order records into a warehouse table orders and show what dlt creates without any DDL from you.

Input.

order_id	amount	customer
1	42.50	ada
2	17.00	linus

Code.

import dlt

data = [
    {"order_id": 1, "amount": 42.50, "customer": "ada"},
    {"order_id": 2, "amount": 17.00, "customer": "linus"},
]

pipeline = dlt.pipeline(
    pipeline_name="shop",
    destination="duckdb",
    dataset_name="raw",
)

load_info = pipeline.run(data, table_name="orders")
print(load_info)

Step-by-step explanation. dlt.pipeline(...) declares where data goes (the duckdb destination) and into which schema (dataset_name="raw"); it does not touch the database yet. pipeline.run(data, table_name="orders") triggers three internal stages — extract (iterate the list to disk as typed JSONL), normalize (infer that order_id is a bigint, amount a double, customer a text, and lay out the table), and load (create raw.orders if absent, then write the rows in one atomic load package). Because dlt saw values, it never needed a schema from you.

Output.

dlt created	value
schema	raw
table	orders (columns: order_id bigint, amount double, customer text)
system columns	_dlt_id, _dlt_load_id added per row
load status	2 rows, 1 load package, completed

Rule of thumb. If your source can be expressed as "a Python function that yields dicts," dlt can load it — the toy example and the production API differ only in the generator, never in the pipeline.

---

2. Sources, resources & the pipeline object

@dlt.resource, @dlt.source, and dlt.pipeline are the entire mental model — learn these three and the rest is configuration

dlt has exactly three objects you compose, and an interviewer who asks "walk me through dlt's model" wants these three in order. Get the vocabulary crisp and the whole library snaps into focus.

The three objects.

• Resource — one table's worth of rows. A @dlt.resource decorates a generator function; each dict it yields becomes a row, and the resource name becomes the table name. A resource is the unit that carries write-disposition, primary-key, and incremental settings.
• Source — a group of related resources. A @dlt.source is a function that returns one or more resources, usually because they share auth or a base URL (an API, a database). Running a source loads all its resources into the same dataset.
• Pipeline — destination + dataset + state. dlt.pipeline(...) binds a pipeline_name (the identity that owns state), a destination, and a dataset_name. It is the thing you .run().

The three stages every run() executes.

• Extract. dlt consumes your generators and writes the raw items to local storage as a load package. Extraction is decoupled from loading so a slow API never holds a warehouse transaction open.
• Normalize. dlt infers or reads the schema, flattens nested structures into parent/child tables, and rewrites the data into the destination's preferred file format (Parquet / JSONL / CSV).
• Load. dlt creates or migrates tables and copies the staged files into the destination atomically, recording the outcome in _dlt_loads and updating _dlt_pipeline_state.

Why the resource is the powerful unit.

• A resource can yield in a loop, so it streams — memory stays flat even for millions of rows.
• A resource can depend on another via @dlt.transformer, so you can fan a list of IDs into per-ID detail calls.
• Per-resource write_disposition, primary_key, and incremental mean one source can mix a full-refresh dimension and an append-only event table in the same run.

Worked example — a source with two resources

Detailed explanation. Real sources bundle several tables. Here a tiny "shop" source exposes two resources — customers (a small dimension) and orders (a growing fact) — from one function. Running the source loads both tables into one dataset in a single pipeline run, and each resource can carry its own load settings.

Question. Define a shop source that yields a customers resource and an orders resource, then load both with one pipeline.run(...).

Input. Two customers and three orders returned by the source functions.

Code.

import dlt

@dlt.resource(write_disposition="replace")
def customers():
    yield {"customer_id": 1, "name": "ada"}
    yield {"customer_id": 2, "name": "linus"}

@dlt.resource(write_disposition="append")
def orders():
    yield {"order_id": 10, "customer_id": 1, "amount": 42.5}
    yield {"order_id": 11, "customer_id": 2, "amount": 17.0}
    yield {"order_id": 12, "customer_id": 1, "amount": 9.9}

@dlt.source
def shop():
    return [customers, orders]

pipeline = dlt.pipeline(pipeline_name="shop", destination="duckdb", dataset_name="raw")
info = pipeline.run(shop())

Step-by-step explanation. Each @dlt.resource becomes a table named after the function. customers carries write_disposition="replace" so it is rebuilt every run (correct for a small dimension), while orders carries append so history accumulates. @dlt.source groups them; returning [customers, orders] tells dlt to load both. pipeline.run(shop()) extracts, normalizes, and loads both resources into the raw dataset in one atomic package.

Output.

table	rows after run	disposition
raw.customers	2	replace
raw.orders	3	append

Rule of thumb. One @dlt.source = one logical system; one @dlt.resource = one table. Put shared auth and base URLs on the source, put load behaviour on each resource.

dlt interview question on the source/resource model

Question. An interviewer gives you a paginated REST endpoint /tickets that returns {"data": [...], "next": "<url|null>"} and asks you to load every page into a tickets table using dlt, streaming so memory stays flat. Show the resource.

Solution Using a generator resource with pagination

Code.

import dlt
import requests

@dlt.resource(name="tickets", write_disposition="append")
def tickets(base_url="https://api.example.com/tickets"):
    url = base_url
    while url:
        resp = requests.get(url, timeout=30).json()
        yield from resp["data"]          # stream each page's rows
        url = resp.get("next")           # follow pagination

pipeline = dlt.pipeline(pipeline_name="support", destination="duckdb", dataset_name="raw")
info = pipeline.run(tickets())

Step-by-step trace.

step	url followed	rows yielded	held in memory
1	/tickets (page 1)	500	one page
2	/tickets?cursor=p2	500	one page
3	/tickets?cursor=p3	320	one page
4	next = null → stop	—	—

1. The resource is a generator: yield from resp["data"] emits a page's rows and then suspends, so dlt pulls one page at a time.
2. After yielding a page, the loop reads resp["next"] and continues until the API returns null.
3. dlt's extract stage writes each yielded batch to the load package on disk, so peak memory is one page, not the whole result set.
4. write_disposition="append" means every run adds the newly-fetched tickets to the existing table.

Output:

table	total rows loaded	peak rows in memory
raw.tickets	1320	~500 (one page)

Why this works — concept by concept:

• Generator streaming — because the resource yields instead of returning a list, dlt controls back-pressure and never materializes the full dataset; a 10-million-row export uses the same memory as a 500-row one.
• Pagination as a loop — following resp["next"] inside the generator keeps paging logic in one place; dlt neither knows nor cares how many pages there are.
• Append disposition — new pages are added, not overwritten, which is the correct default for an event-like source before you add incremental filtering.
• Decoupled extract/load — dlt stages extracted pages to disk first, so a slow API cannot hold a warehouse write transaction open.
• Cost — time is O(rows) network-bound; memory is O(page size), independent of total rows.

ETL
Topic — etl
ETL extract-and-load pipeline problems

Practice →

Pipelines Topic — pipelines Pipeline-design and streaming-generator problems

Practice →

---

3. Schema inference & evolution

dlt reads your data and writes the DDL — inference plus contracts is what replaces a hand-maintained schema

The feature that sells dlt to a data engineer is that you never write CREATE TABLE or ALTER TABLE again for ingested data. dlt inspects the values flowing through, infers a column type for each field, unnests nested structures into child tables, and — when a new column appears next week — evolves the schema according to a contract you set. This is the difference between a script that breaks on drift and a pipeline that adapts.

How inference works.

• Type from value. dlt maps Python/JSON values to warehouse types: int → bigint, float → double, str → text, bool → boolean, ISO strings that parse as dates → timestamp, dict/list → nested handling. The inferred schema is written to schemas/ so it is versioned and inspectable.
• Nested data becomes child tables. A record with a nested list (e.g. an order with items: [...]) is split: the parent row lands in orders, and each item lands in a child table orders__items, linked by _dlt_parent_id → _dlt_id. This is automatic unnesting — no manual flattening.
• System columns. Every row gets a deterministic _dlt_id; every load gets a _dlt_load_id. Child rows also get _dlt_parent_id and _dlt_list_idx so you can reconstruct order.

Schema contracts — governing evolution.

• evolve (default). New columns and new tables are added automatically. Best for exploratory or trusted sources.
• freeze. New columns raise an error. Best for a locked contract where drift must fail the run, not silently widen the table.
• discard_row / discard_value. Drop the offending row or just the unexpected value. Useful when you want the known columns to keep flowing while quarantining surprises.
• Granularity. Contracts apply at three levels — tables (new tables), columns (new columns), data_type (type changes) — and you can set each independently.

Column hints and overrides.

• You can pin a type, mark a primary_key, or flag a column as a merge_key with @dlt.resource(columns={...}) when inference is not enough.
• Precision matters for money: hint decimal rather than letting a float through, or you will chase rounding drift later.

Worked example — nested JSON becomes parent and child tables

Detailed explanation. The single most useful inference behaviour is automatic unnesting. Give dlt one order that contains a list of line items and it produces two tables you can join, without you writing a flatten step. This is what makes semi-structured API payloads land as clean relational tables.

Question. Load one order that contains a nested items list and show the two tables dlt produces.

Input.

{"order_id": 10, "customer": "ada",
 "items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}]}

Code.

import dlt

order = {
    "order_id": 10,
    "customer": "ada",
    "items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}],
}

pipeline = dlt.pipeline(pipeline_name="shop", destination="duckdb", dataset_name="raw")
pipeline.run([order], table_name="orders")

Step-by-step explanation. dlt infers order_id bigint, customer text for the parent. Seeing items is a list of dicts, it creates a child table orders__items with columns sku text, qty bigint, plus link columns. Each item row stores _dlt_parent_id equal to the parent's _dlt_id, and _dlt_list_idx preserving list order. You join parent to child on those columns.

Output.

table	columns	rows
raw.orders	order_id, customer, _dlt_id, _dlt_load_id	1
raw.orders__items	sku, qty, _dlt_parent_id, _dlt_list_idx, _dlt_id	2

Rule of thumb. Nested arrays always become child tables; if you would have written a flatten step by hand, dlt already did it and gave you the join keys.

dlt interview question on schema evolution

Question. Your source added a discount field this week and marketing wants it captured, but the finance load must never silently change shape. How do you configure dlt so the marketing table evolves but the finance table fails on any new column?

Solution Using per-resource schema contracts

Code.

import dlt

@dlt.resource(name="events", schema_contract={"columns": "evolve"})
def marketing_events(rows):
    yield from rows                     # new `discount` column added automatically

@dlt.resource(name="ledger", schema_contract={"columns": "freeze"})
def finance_ledger(rows):
    yield from rows                     # new column raises, load fails loudly

pipeline = dlt.pipeline(pipeline_name="shop", destination="duckdb", dataset_name="raw")
pipeline.run([marketing_events(new_rows), finance_ledger(ledger_rows)])

Step-by-step trace.

resource	contract	new discount column	result
events	columns: evolve	present	column added, rows loaded
ledger	columns: freeze	present	SchemaFrozenException, load aborts
ledger	columns: freeze	absent	loads normally

1. Each resource carries its own schema_contract, so one run can mix policies.
2. evolve on events lets dlt ALTER TABLE to add discount and continue.
3. freeze on ledger makes any unexpected column raise before a single row is written, so finance shape can only change by an explicit code review that updates the contract.
4. Because contracts are evaluated per-resource in normalize, the marketing success and finance failure are independent.

Output:

table	outcome	schema after run
raw.events	loaded	widened with discount
raw.ledger	aborted	unchanged (drift blocked)

Why this works — concept by concept:

• Schema contract — a declarative policy on how the inferred schema may change; it turns "silent drift" into an explicit choice per table.
• evolve vs freeze — evolve optimizes for capturing everything; freeze optimizes for a stable contract where drift is a failure, not a feature.
• Per-resource scope — because the policy lives on the resource, a permissive marketing table and a locked finance table coexist in one pipeline.
• Fail-loud over corrupt-quiet — freeze aborting the load is the safe default for regulated data: a failed run is recoverable, a silently reshaped ledger is not.
• Cost — inference and contract checks are O(columns) per record, negligible against network and load I/O.

ETL
Topic — data-transformation
Schema-shaping and nested-data problems

Practice →

Quality Topic — data-quality Data-contract and validation problems

Practice →

---

4. Incremental loading with cursor state

dlt remembers where it stopped — a cursor field plus persisted state is what makes the second run cheap

A full reload every night is fine for a 10-row dimension and ruinous for a 500-million-row event table. Incremental loading means each run pulls only rows newer than the last run, and the thing that makes it reliable in dlt is that the "last run" marker lives in durable pipeline state inside the destination, not in a variable you hope survives a crash.

The mechanism.

• Cursor field. You wrap a column — usually updated_at or a monotonically increasing id — with dlt.sources.incremental("updated_at"). dlt tracks the maximum value it has seen.
• Persisted state. After each successful load, dlt writes the new high-water mark to _dlt_pipeline_state in the destination. The next run reads it back, so incrementality is resumable across process restarts and machines.
• Boundary filtering. On the next run, dlt applies WHERE updated_at > last_value (server-side when the source supports it, client-side otherwise), so only fresh rows flow.
• Deduplication. Rows exactly at the boundary can arrive twice; dlt de-duplicates on the cursor plus primary key so a row updated at exactly last_value is not double-counted.

The knobs that matter.

• initial_value. Where to start on the very first run (e.g. only load the last 90 days).
• last_value_func. Usually max, but min for backfilling oldest-first.
• end_value. Bound a backfill window so you can replay a specific range deterministically.
• lag / allow_external. Re-scan a small trailing window to catch late-arriving updates that landed just behind the watermark.

Failure modes interviewers probe.

• Wall-clock cursors are dangerous — if the source stamps updated_at from an app clock with skew, rows can land below a watermark and be missed; prefer a DB-assigned commit timestamp or an increasing id.
• No deletes. A cursor is blind to hard deletes (the row simply stops appearing). If you need deletes, you need CDC or soft-delete flags, not a high-water mark.

Worked example — an incremental resource on updated_at

Detailed explanation. The everyday incremental pattern is a resource whose function accepts a dlt.sources.incremental cursor with a default column and initial value. dlt injects the current last_value so your extract query can filter, and updates it after the load.

Question. Write an orders resource that loads only rows with updated_at newer than the last run, starting from 2026-01-01 on the first run.

Input. Source rows across two runs (watermark starts empty).

Code.

import dlt
from datetime import datetime

@dlt.resource(name="orders", write_disposition="append")
def orders(updated=dlt.sources.incremental("updated_at", initial_value="2026-01-01")):
    # `updated.last_value` is the high-water mark dlt restored from state
    for row in fetch_orders(since=updated.last_value):
        yield row

pipeline = dlt.pipeline(pipeline_name="shop", destination="duckdb", dataset_name="raw")
pipeline.run(orders())

Step-by-step explanation. On run 1, state is empty, so updated.last_value is the initial_value 2026-01-01; fetch_orders(since=...) returns everything after that date, and dlt records the maximum updated_at it saw. On run 2, dlt restores that maximum from _dlt_pipeline_state, so fetch_orders only returns rows updated since — the cheap path. Rows sitting exactly on the boundary are de-duplicated.

Output.

run	last_value at start	rows loaded	new last_value
1	2026-01-01	4	2026-03-02 10:15
2	2026-03-02 10:15	1	2026-03-05 09:00

Rule of thumb. Pick a cursor the source controls and only increases — a commit timestamp or an auto-increment id — never a value a client can backdate.

dlt interview question on incremental correctness

Question. Two runs of an incremental pipeline both load a row with updated_at = 12:30 (one row updated exactly at the previous watermark). How does dlt avoid loading it twice, and what must you provide for that to work?

Solution Using boundary deduplication on the primary key

Code.

import dlt

@dlt.resource(
    name="orders",
    write_disposition="append",
    primary_key="order_id",              # identity for boundary dedup
)
def orders(updated=dlt.sources.incremental("updated_at")):
    for row in fetch_orders(since=updated.last_value):
        yield row

Step-by-step trace.

run	fetched rows (order_id @ updated_at)	at boundary?	loaded
1	7 @ 12:00, 8 @ 12:30	—	7, 8
2	8 @ 12:30, 9 @ 12:45	8 is at last_value	9 only

1. dlt filters updated_at >= last_value (inclusive) so it cannot miss rows that share the boundary timestamp.
2. Because the filter is inclusive, row 8 @ 12:30 is fetched again on run 2.
3. dlt keeps a set of primary_key values seen exactly at the boundary from the previous run and drops re-seen ones, so 8 is discarded while 9 loads.
4. Without a primary_key (or a unique cursor), dlt cannot tell a genuine duplicate from a distinct row, so you would either double-load or risk missing boundary rows.

Output:

table	rows after run 2	duplicates
raw.orders	3 (ids 7, 8, 9)	0

Why this works — concept by concept:

• Inclusive boundary — filtering >= last_value guarantees no row is skipped when many share the exact watermark timestamp; the cost is re-fetching the boundary rows.
• Primary key dedup — a stable identity lets dlt discard the re-fetched boundary rows, turning "inclusive and safe" into "inclusive, safe, and exactly-once."
• Persisted state — the watermark and boundary-key set live in the destination, so correctness survives crashes and re-runs.
• Idempotent runs — running twice with no new data loads nothing new; this is the property that makes retries safe.
• Cost — extra work is O(boundary rows), typically tiny, in exchange for exactly-once at the watermark.

Idempotency
Topic — idempotency
Idempotent incremental-load problems

Practice →

ETL Topic — etl Incremental extract-load problems

Practice →

---

5. Write dispositions — replace, append, merge

One argument decides how rows hit the table — write_disposition is replace, append, or merge, and picking wrong is a data-quality bug

Every resource writes with one of three dispositions, and the choice is the single most consequential correctness decision in an ingestion pipeline. Say it in one breath: replace wipes and reloads, append only adds, merge upserts on a key.

The three modes.

• replace — full refresh. dlt loads into a staging table and atomically swaps it in, so readers never see a half-empty table. Correct for small dimensions and any source you can afford to re-pull whole.
• append — immutable log. Every run adds its rows; nothing is updated or deleted. Correct for event streams and any append-only fact where re-loading history would be wasteful or wrong.
• merge — upsert. With a primary_key, dlt updates rows that exist and inserts rows that do not. This is the disposition for mutable dimensions and for incremental facts that can be restated.

Merge, in detail (the one they drill).

• primary_key. The identity dlt matches on to decide update-vs-insert.
• merge_key. When there is no single primary key, match on a composite or business key.
• SCD2. Set write_disposition={"disposition": "merge", "strategy": "scd2"} and dlt maintains _dlt_valid_from / _dlt_valid_to validity columns, giving you slowly-changing-dimension history without hand-written MERGE SQL.
• dedup_sort. When several updates for the same key arrive in one batch, dedup_sort picks the winner (e.g. latest updated_at) so the merge is deterministic.

Choosing a disposition.

• Small, fully re-pullable, mutable → replace.
• Append-only events, immutable history → append.
• Mutable rows keyed by an id, or incremental + restatable → merge.
• Need full history of changes, not just current state → merge + scd2.

Worked example — the same rows under replace vs append vs merge

Detailed explanation. The clearest way to internalise dispositions is to load the same second batch three ways and watch the destination diverge. Batch 1 seeds two rows; batch 2 updates one and adds one.

Question. Given batch 1 = {1: A}, {2: B} already loaded, and batch 2 = {2: B2}, {3: C}, what is in orders after loading batch 2 under replace, append, and merge (primary_key id)?

Input.

batch	rows (id: value)
1 (seed)	1: A, 2: B
2	2: B2, 3: C

Code.

import dlt

@dlt.resource(write_disposition="merge", primary_key="id")
def orders(rows):
    yield from rows

pipeline = dlt.pipeline(pipeline_name="shop", destination="duckdb", dataset_name="raw")
pipeline.run(orders(batch2))         # swap the disposition to compare

Step-by-step explanation. Under replace, batch 2 becomes the entire table — batch 1 is gone. Under append, batch 2's rows are added beneath batch 1, so id 2 now appears twice (old B and new B2). Under merge on id, row 2 is updated in place to B2 and row 3 is inserted, while row 1 is untouched.

Output.

disposition	rows in orders after batch 2
replace	2: B2, 3: C
append	1: A, 2: B, 2: B2, 3: C
merge	1: A, 2: B2, 3: C

Rule of thumb. If the same real-world entity can appear in more than one batch, append will duplicate it — reach for merge with a primary_key unless the table is a true event log.

dlt interview question on choosing a disposition

Question. You ingest a customers table incrementally; rows change (email updates, tier changes) and you must keep only the current state, deduped by customer_id, with the latest update winning when a batch contains two versions of the same customer. Which disposition and settings, and why?

Solution Using merge with primary_key and dedup_sort

Code.

import dlt

@dlt.resource(
    name="customers",
    write_disposition="merge",
    primary_key="customer_id",
    columns={"updated_at": {"dedup_sort": "desc"}},   # latest wins within a batch
)
def customers(updated=dlt.sources.incremental("updated_at")):
    for row in fetch_customers(since=updated.last_value):
        yield row

pipeline = dlt.pipeline(pipeline_name="crm", destination="duckdb", dataset_name="raw")
pipeline.run(customers())

Step-by-step trace.

batch rows (customer_id, updated_at, email)	dedup within batch	merge action
(5, 09:00, a@x) , (5, 11:00, b@x)	keep 11:00 (desc)	update id 5 → b@x
(6, 10:00, c@x)	single	insert id 6

1. merge + primary_key="customer_id" makes the load an upsert: existing customers update, new ones insert.
2. Two rows for customer 5 arrive in one batch; dedup_sort: desc on updated_at keeps the newest (11:00 → b@x) and drops the stale one before the merge.
3. The incremental cursor ensures only changed customers were fetched, so the merge set is small.
4. The result is exactly one row per customer_id, always reflecting the latest update — current-state semantics with no duplicates.

Output:

table	rows	invariant
raw.customers	one per customer_id	latest updated_at wins

Why this works — concept by concept:

• Merge upsert — matching on primary_key collapses insert/update into one declarative disposition, so you never write MERGE SQL by hand.
• dedup_sort — resolves intra-batch conflicts deterministically; without it, two versions of one key in a batch make the winner order-dependent.
• Incremental + merge — the cursor keeps the fetch small and the merge keeps the table current; together they give cheap, correct, restatable loads.
• Current-state guarantee — one row per key after every run is the property downstream dbt models and BI tools depend on.
• Cost — merge is O(changed rows) against the target index on primary_key, far cheaper than replacing the whole dimension nightly.

ETL
Topic — data-transformation
Upsert, merge and SCD problems

Practice →

Pipelines Topic — pipelines Load-mode and pipeline-design problems

Practice →

---

Cheat sheet — dlt recipes

Minimal pipeline.

import dlt
pipe = dlt.pipeline(pipeline_name="p", destination="duckdb", dataset_name="raw")
pipe.run([{"id": 1}], table_name="t")

REST API source with pagination.

@dlt.resource(name="tickets", write_disposition="append")
def tickets(url="https://api.example.com/tickets"):
    while url:
        r = requests.get(url, timeout=30).json()
        yield from r["data"]
        url = r.get("next")

SQL database source (built-in helper).

from dlt.sources.sql_database import sql_database
source = sql_database().with_resources("orders", "customers")
dlt.pipeline("db", destination="bigquery", dataset_name="raw").run(source)

Incremental cursor.

@dlt.resource
def orders(updated=dlt.sources.incremental("updated_at", initial_value="2026-01-01")):
    yield from fetch(since=updated.last_value)

Merge / upsert.

@dlt.resource(write_disposition="merge", primary_key="id")
def dim(rows):
    yield from rows

Lock the schema (fail on drift).

@dlt.resource(schema_contract={"columns": "freeze", "tables": "freeze"})
def ledger(rows):
    yield from rows

Disposition picker.

Situation	Disposition
Small, re-pullable, mutable dimension	replace
Append-only events / immutable facts	append
Mutable rows keyed by an id	merge + primary_key
Full change history required	merge + scd2

---

Frequently asked questions

What is dlt (data load tool)?

dlt is an open-source Python library for building EL (extract-load) pipelines. You pip install dlt, write a generator that yields dictionaries, and call pipeline.run(...); dlt infers the schema, unnests nested data into child tables, tracks incremental state, and loads the rows into a destination such as DuckDB, BigQuery, Snowflake, Redshift, or Postgres. It is a library you embed in your own code, not a hosted platform.

How is dlt different from Airbyte or Fivetran?

Fivetran and Airbyte are connector platforms you configure and run as a service; dlt is a library you import into your own Python process. Use managed connectors when a prebuilt connector exists and you want zero code and zero infrastructure ownership. Use dlt when the connector you need does not exist, when you want ingestion code versioned in your repo, or when compliance forbids routing data through a third-party plane. dlt also gives you first-class schema inference, contracts, and incremental state in plain Python.

How does dlt infer a schema?

dlt inspects the values in the rows you yield and maps them to warehouse types (int → bigint, float → double, str → text, date-like strings → timestamp, and so on). Nested dictionaries and lists are automatically unnested into child tables linked by _dlt_id and _dlt_parent_id. The inferred schema is written to a versioned file, and a schema contract (evolve, freeze, or discard) controls what happens when new columns or tables appear.

What are dlt write dispositions?

A write disposition tells dlt how new rows hit the destination table. replace performs a full refresh via an atomic swap, append adds rows immutably, and merge upserts rows on a primary_key (with an scd2 strategy available for full change history). You set it per-resource, so one pipeline can mix a replace dimension and an append event table in the same run.

How does incremental loading work in dlt?

You wrap a monotonically increasing column with dlt.sources.incremental("updated_at"). dlt records the maximum value it has loaded in durable pipeline state inside the destination, and on the next run filters the source to rows past that high-water mark. It de-duplicates rows sitting exactly on the boundary using the primary_key, so re-runs are idempotent and no boundary rows are missed or double-loaded.

Does dlt do transformations?

Only lightweight, row-level shaping (renames, type hints, pseudonymization) during normalization. Business transformations — joins, dimensional models, metrics — are deliberately out of scope; dlt is the EL and you run the T downstream in dbt or SQL against the clean, typed tables dlt produced. Keeping transform out of dlt is what keeps the library small and composable.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every dlt idea above, from the streaming generator resource to the incremental cursor and the merge-on-primary-key upsert, maps to a hands-on practice room where you build the load against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you make this ingestion idempotent?" holds up under a senior interviewer's depth probes.

Practice ETL problems now →
Pipeline-design drills →