dlt's Auto-Schema Pipeline: How Python Data Loaders Infer Structure Without Predefined Models

Agent-driven data pipelines break when upstream APIs change field types, add nested objects, or return inconsistent structures. Traditional ETL requires manual schema definitions, which means every API evolution triggers a pull request, a migration script, and downtime. dlt is an open-source Python library that infers schemas on the fly, writes to SQL stores, vector databases, DuckDB, and data lakes, and handles schema drift without predefined models.

The library shipped with 222 points on Show HN and 54 comments. It targets the exact problem agents face: ingesting messy, unstructured data from scrapers, APIs, and webhooks without brittle DDL.

How Schema Inference Works

dlt inspects the first batch of data, infers column types, nesting depth, and relationships, then creates tables dynamically. When the next batch arrives with new fields or changed types, dlt evolves the schema in place.

Core mechanics:

• Type detection: Scans Python dicts, lists, and nested objects to map to SQL types (VARCHAR, BIGINT, JSONB, TIMESTAMP).
• Normalization: Flattens nested JSON into relational tables or preserves structure as JSONB depending on destination.
• Schema versioning: Tracks schema changes across loads, allowing rollback or merge strategies.
• Incremental updates: Supports append, merge, and replace modes with automatic deduplication keys.

When a source adds a new field mid-pipeline, dlt adds the column to the destination table. When a field changes from integer to string, dlt widens the type or creates a new version depending on configuration.

Handling Schema Drift

Schema drift is the failure mode. An API returns user_id as an integer for six months, then switches to UUID strings. dlt detects the conflict and offers three strategies:

The default is evolve. For agent pipelines scraping third-party APIs, this prevents crashes. For financial or compliance data, freeze mode forces human review.

Conflict resolution example:

import dlt pipeline = dlt.pipeline( pipeline_name="api_scraper", destination="duckdb", dataset_name="raw_data" ) @dlt.resource(write_disposition="merge", primary_key="id") def fetch_users(): # API returns inconsistent user_id types  yield {"id": 123, "name": "Alice"} yield {"id": "uuid-456", "name": "Bob"} pipeline.run(fetch_users()) 

dlt detects the type mismatch, widens id to VARCHAR, and continues. The alternative is a failed load and a pager alert.

Multi-Destination Writes and Backpressure

dlt writes to multiple destinations simultaneously: Postgres, Snowflake, BigQuery, Pinecone, Weaviate, S3, DuckDB. Each destination has different schema constraints (Postgres supports JSONB, BigQuery prefers nested RECORD types, vector stores need embeddings).

Backpressure handling:

• Buffering: dlt batches rows in memory, flushes when buffer size or time threshold is hit.
• Retries: Exponential backoff for transient failures (network timeouts, rate limits).
• Partial success: If one destination fails, dlt continues writing to others and logs the error.

When writing to a vector store and a SQL warehouse, dlt normalizes data differently for each. The SQL destination gets flattened tables. The vector store gets embeddings and metadata as JSONB.

Failure recovery:

• State tracking: dlt persists load state (last processed ID, cursor position) to the destination or a separate state backend.
• Idempotency: Merge mode uses primary keys to deduplicate retries.
• Dead letter queue: Failed rows land in a separate table for manual inspection.

If a pipeline crashes mid-load, restarting resumes from the last committed state. No duplicate rows, no data loss.

Orchestration Integration

dlt runs inside Airflow, Prefect, Dagster, or as a standalone script. It does not replace orchestrators. It replaces the glue code that extracts, normalizes, and loads data.

Airflow DAG example:

from airflow.decorators import dag, task from datetime import datetime import dlt @dag(schedule_interval="@hourly", start_date=datetime(2026, 11, 1)) def scrape_and_load(): @task def run_pipeline(): pipeline = dlt.pipeline( pipeline_name="api_scraper", destination="postgres", dataset_name="raw" ) @dlt.resource def api_data(): # Fetch from external API  return requests.get("https://api.example.com/data").json() pipeline.run(api_data()) run_pipeline() scrape_and_load() 

The orchestrator handles scheduling, retries, and alerting. dlt handles schema inference, normalization, and writes.

Security Boundaries

dlt does not enforce authentication or authorization. It assumes the Python process has credentials for source APIs and destination databases. Secrets management is external (environment variables, Vault, cloud secret managers).

Risk surface:

• Credential leakage: If dlt logs raw data, API keys or PII may appear in logs.
• SQL injection: dlt generates DDL dynamically. Malicious field names could inject SQL if not sanitized.
• Data exfiltration: A compromised source could inject malicious payloads into destination tables.

Mitigations:

• Run dlt in isolated environments (containers, VPCs).
• Use least-privilege database credentials.
• Enable audit logging on destinations.
• Validate source data before loading (custom transformations, schema contracts).

Observability

dlt emits structured logs, metrics, and traces. It integrates with OpenTelemetry for distributed tracing.

Key metrics:

• Rows extracted, normalized, loaded per run.
• Schema evolution events (new columns, type changes).
• Load duration, retry counts, failure rates.
• Destination-specific metrics (query latency, storage size).

Logs include:

• Source API response times.
• Normalization warnings (type coercion, null handling).
• Destination write errors.

For agent pipelines, observability answers: Did the schema change? Did the load succeed? How long did it take?

When to Use dlt

Good fit:

• Agent pipelines scraping third-party APIs with unstable schemas.
• Prototyping data products where schema is unknown upfront.
• Incremental loads from SaaS tools (Stripe, GitHub, Salesforce).
• Loading into DuckDB for local analytics or testing.

Bad fit:

• High-throughput streaming (Kafka, Kinesis). dlt is batch-oriented.
• Strict schema enforcement. If schema changes are bugs, not features, use a schema registry.
• Real-time analytics. dlt adds latency for normalization and buffering.
• Compliance-heavy workloads where schema evolution must be audited and approved.

Technical Verdict

Use dlt when upstream data sources are messy, schemas are unknown, and manual DDL is a bottleneck. It eliminates the glue code between extraction and loading, handles schema drift gracefully, and supports enough destinations to avoid vendor lock-in.

Avoid it when schema stability is a requirement, real-time latency matters, or you need fine-grained access control inside the pipeline. dlt is a data loader, not a streaming platform or governance layer.

For agent-driven automation, dlt solves the schema problem. Agents scrape, poll, and ingest data without knowing the structure upfront. dlt infers it, writes it, and adapts when it changes.

Source Links

• dlt Colab Notebook
• Hacker News Discussion