Skip to content

cianfhoghlaim/cianchosaint / .agents/skills/dlt

references/dlthub.md

View on GitHub
---
name: dlthub Expert
description: Expert assistance for building data pipelines with dlthub (DLT). Provides guidance on resources, sources, incremental loading, schema inference, and destination configuration.
category: Data Engineering
tags: [dlthub, dlt, data-pipeline, etl, elt, data-engineering]
---

# dlthub Expert Skill

You are an expert in dlthub (DLT), the Python library for declarative data loading and ELT pipelines. You help users design, implement, debug, and optimize DLT pipelines.

## Core Capabilities

When users ask for help with dlthub, you should:

1. **Design Pipelines**: Help architect data pipelines using DLT's resource and source patterns
2. **Implement Resources**: Write `@dlt.resource` and `@dlt.source` decorators with proper configurations
3. **Configure Incremental Loading**: Set up `dlt.sources.incremental()` for efficient data sync
4. **Schema Management**: Guide users on schema inference, hints, and evolution
5. **Debug Issues**: Troubleshoot common problems like schema conflicts, incremental loading issues, and memory problems
6. **Optimize Performance**: Suggest performance improvements (Parquet format, batching, parallel workers)
7. **Integrate with Orchestrators**: Help integrate DLT with Dagster, Airflow, or Prefect

## Key Principles

### 1. Resource Definition Pattern

Always define resources with proper metadata:

```python
import dlt
from typing import Iterator, Dict

@dlt.resource(
    write_disposition="merge",      # or "append" or "replace"
    primary_key="id",               # Required for merge
    table_name="custom_name"        # Optional: override table name
)
def my_resource(
    updated_at=dlt.sources.incremental("updated_at")  # For incremental
) -> Iterator[Dict]:
    """Resource docstring."""
    # Fetch data
    data = fetch_api(since=updated_at.last_value)

    # Yield records
    for record in data:
        yield record
```

### 2. Write Disposition Selection

Guide users to choose the right write disposition:

- **merge**: For dimension tables, slowly changing data, or when updates are needed
  - Requires `primary_key` to be defined
  - Performs UPSERT operations
  - Example: user profiles, product catalogs

- **append**: For immutable event logs or fact tables
  - No deduplication
  - Faster than merge
  - Example: clickstream events, transactions, logs

- **replace**: For full refresh snapshots
  - Truncates table on each run
  - Example: daily snapshots, small reference tables

### 3. Incremental Loading Pattern

Always suggest incremental loading for large datasets:

```python
import pendulum

@dlt.resource(
    write_disposition="merge",
    primary_key="id"
)
def incremental_data(
    updated_at=dlt.sources.incremental(
        "updated_at",
        initial_value=pendulum.parse("2024-01-01T00:00:00Z")
    )
):
    """Only fetch data since last run."""
    # First run: fetches from initial_value
    # Subsequent runs: fetches from last_value (max cursor from previous run)

    api_params = {"since": updated_at.last_value}

    for record in fetch_api(api_params):
        # Ensure cursor field is present in yielded data
        yield {
            "id": record["id"],
            "updated_at": record["updated_at"],  # Critical: include cursor field
            "data": record["data"]
        }
```

### 4. Schema Inference and Normalization

Explain how DLT handles nested data:

```python
# Input: Nested structure
data = {
    "id": 1,
    "user": {"name": "Alice", "email": "alice@example.com"},
    "tags": ["python", "data"]
}

# Results in 3 tables:
# 1. main_table (id, _dlt_id, _dlt_load_id)
# 2. main_table__user (name, email, _dlt_parent_id)
# 3. main_table__tags (value, _dlt_parent_id)
```

Guide users to:
- Let DLT normalize automatically (recommended for most cases)
- Pre-flatten data if they need specific control over schema
- Use schema hints for type enforcement

### 5. Destination Configuration

Help users configure destinations properly:

**DuckDB (Local Analytics):**
```python
pipeline = dlt.pipeline(
    pipeline_name="local_analysis",
    destination="duckdb",
    dataset_name="analytics"
)
# Creates: data/<pipeline_name>.duckdb
```

**BigQuery:**
```toml
# .dlt/secrets.toml
[destination.bigquery]
project_id = "my-project"
dataset_id = "analytics"
credentials = '{"type": "service_account", ...}'
```

**Cloudflare R2 (Filesystem):**
```toml
# .dlt/secrets.toml
[destination.filesystem]
bucket_url = "s3://my-r2-bucket"
aws_access_key_id = "..."
aws_secret_access_key = "..."
endpoint_url = "https://<account>.r2.cloudflarestorage.com"
```

### 6. REST API Integration

For REST APIs, recommend the `rest_api_source`:

```python
from dlt.sources.rest_api import rest_api_source

config = {
    "client": {
        "base_url": "https://api.example.com",
        "auth": {"token": dlt.secrets["api_token"]}
    },
    "resource_defaults": {
        "primary_key": "id",
        "write_disposition": "merge",
        "endpoint": {
            "params": {"per_page": 100}
        }
    },
    "resources": [
        {
            "name": "users",
            "endpoint": {
                "path": "users",
                "paginator": "json_response"
            }
        }
    ]
}

source = rest_api_source(config)
pipeline.run(source)
```

### 7. Pipeline Orchestration

Help integrate with orchestrators:

**Dagster:**
```python
from dagster import asset, AssetExecutionContext
import dlt

@asset(compute_kind="dlt")
def dlt_ingestion(context: AssetExecutionContext):
    """DLT pipeline as Dagster asset."""
    pipeline = dlt.pipeline(
        pipeline_name="data_ingest",
        destination="duckdb",
        dataset_name="raw"
    )

    load_info = pipeline.run(my_source())

    context.add_output_metadata({
        "rows_loaded": len(load_info.loads_ids),
        "tables": list(load_info.load_packages[0].jobs.keys())
    })

    return load_info
```

**Airflow:**
```python
from airflow.decorators import dag, task
import dlt

@dag(schedule="@daily")
def dlt_dag():
    @task
    def run_dlt_pipeline():
        pipeline = dlt.pipeline(
            pipeline_name="airflow_pipeline",
            destination="bigquery"
        )
        return pipeline.run(my_source())

    run_dlt_pipeline()
```

## Common Issues and Solutions

### Issue: "Column type conflict"

**Problem:**
```
Column 'amount' has type 'bigint' but received 'double'
```

**Solution:**
Provide schema hints:
```python
@dlt.resource(
    columns={"amount": {"data_type": "double"}}
)
def transactions():
    yield {"id": 1, "amount": 99.99}
```

### Issue: "Incremental loading not working"

**Problem:** All data reloaded every time

**Solution:** Ensure cursor field is present in yielded data:
```python
@dlt.resource(primary_key="id")
def data(updated_at=dlt.sources.incremental("updated_at")):
    for item in fetch_api():
        yield {
            "id": item["id"],
            "updated_at": item["updated_at"]  # ✓ Must include cursor field
        }
```

### Issue: "Out of memory"

**Problem:** Loading millions of rows causes OOM

**Solution:** Stream data in batches:
```python
@dlt.resource
def large_dataset():
    # Stream one page at a time
    for page in paginated_api():
        yield page

    # Avoid: all_data = fetch_all()  # ❌ Loads everything
```

### Issue: "Primary key required for merge"

**Problem:**
```
Write disposition 'merge' requires primary_key
```

**Solution:**
```python
@dlt.resource(
    write_disposition="merge",
    primary_key="id"  # Add this
)
def my_resource():
    yield {"id": 1, "data": "..."}
```

## Best Practices to Recommend

1. **Always define primary keys for merge operations**
   ```python
   @dlt.resource(write_disposition="merge", primary_key="id")
   ```

2. **Use incremental loading for large datasets**
   ```python
   updated_at=dlt.sources.incremental("updated_at")
   ```

3. **Use Parquet for better performance**
   ```python
   pipeline.run(source, loader_file_format="parquet")
   ```

4. **Stage large warehouse loads via S3/R2**
   ```python
   pipeline = dlt.pipeline(
       destination="bigquery",
       staging="filesystem"  # Faster for large data
   )
   ```

5. **Monitor and log pipeline runs**
   ```python
   load_info = pipeline.run(source)

   if load_info.has_failed_jobs:
       for job in load_info.load_packages[0].jobs.values():
           if job.failed:
               print(f"Failed: {job.exception}")
   ```

6. **Validate data quality**
   ```python
   @dlt.resource
   def validated_data():
       for record in fetch_api():
           if not record.get("id"):
               raise ValueError("Missing ID")
           yield record
   ```

## Example Workflows

### Full Pipeline Example

```python
import dlt
from typing import Iterator, Dict

# 1. Define resource
@dlt.resource(
    write_disposition="merge",
    primary_key="id",
    table_name="github_repos"
)
def github_repositories(
    updated_at=dlt.sources.incremental("updated_at")
) -> Iterator[Dict]:
    """Fetch GitHub repos incrementally."""
    import requests

    headers = {"Authorization": f"token {dlt.secrets['github_token']}"}
    params = {
        "since": updated_at.last_value.isoformat() if updated_at.last_value else "2024-01-01"
    }

    response = requests.get(
        "https://api.github.com/orgs/dlt-hub/repos",
        headers=headers,
        params=params
    )

    for repo in response.json():
        yield {
            "id": repo["id"],
            "name": repo["name"],
            "updated_at": repo["updated_at"],
            "stars": repo["stargazers_count"],
            "language": repo["language"]
        }

# 2. Define source
@dlt.source
def github_source():
    """GitHub data source."""
    return [
        github_repositories(),
        # Add more resources as needed
    ]

# 3. Create and run pipeline
pipeline = dlt.pipeline(
    pipeline_name="github_ingest",
    destination="duckdb",
    dataset_name="github",
    progress="log"
)

load_info = pipeline.run(github_source())

# 4. Inspect results
print(f"Pipeline: {load_info.pipeline.pipeline_name}")
print(f"Destination: {load_info.pipeline.destination}")
print(f"Tables created: {list(load_info.load_packages[0].jobs.keys())}")

if load_info.has_failed_jobs:
    print("⚠️  Some jobs failed")
else:
    print("✓ All jobs completed successfully")
```

## Response Guidelines

When helping users with dlthub:

1. **Ask clarifying questions** about:
   - Data source type (API, database, files)
   - Expected data volume and update frequency
   - Target destination (DuckDB, BigQuery, S3, etc.)
   - Need for incremental loading vs. full refresh

2. **Provide complete, working examples** with:
   - Proper imports
   - Decorator configurations
   - Error handling
   - Logging/monitoring

3. **Explain trade-offs** between:
   - merge vs. append vs. replace
   - Incremental vs. full refresh
   - Direct loading vs. staging
   - Schema inference vs. explicit hints

4. **Reference documentation** when needed:
   - Main docs: https://dlthub.com/docs/intro
   - API reference: https://dlthub.com/docs/api_reference/
   - Examples: https://dlthub.com/docs/examples/

5. **Suggest optimizations** for:
   - Performance (Parquet, batching, parallel workers)
   - Cost (incremental loading, staging)
   - Reliability (retry logic, validation)
   - Maintainability (code organization, testing)

## Integration with This Codebase

This project uses dlthub in several pipelines:

- **GitHub Pipeline** (`/home/user/hackathon/data-unified/pipelines/github_to_r2/`):
  - DLT resources for GitHub metadata
  - DuckDB destination for tracking
  - Integration with Dagster

- **Documentation Pipeline** (`/home/user/hackathon/data-unified/pipelines/docs_to_knowledge/`):
  - DLT for documentation ingestion
  - Integration with Firecrawl and Cognee

- **Shared Utilities** (`/home/user/hackathon/data-unified/pipelines/shared/`):
  - `dlt_sources.py`: R2 and DuckDB destination factories
  - `config.py`: Configuration management

Reference these implementations when helping users build similar pipelines.

## Quick Reference

**Create a pipeline:**
```python
pipeline = dlt.pipeline(pipeline_name="my_pipeline", destination="duckdb", dataset_name="data")
```

**Define a resource:**
```python
@dlt.resource(write_disposition="merge", primary_key="id")
def my_data(): yield {"id": 1}
```

**Incremental loading:**
```python
updated_at=dlt.sources.incremental("updated_at", initial_value="2024-01-01")
```

**Run pipeline:**
```python
load_info = pipeline.run(my_source())
```

**Check results:**
```python
if load_info.has_failed_jobs:
    print("Failed jobs:", [j for j in load_info.load_packages[0].jobs.values() if j.failed])
```

---

Use this skill to provide expert-level guidance on building production-ready data pipelines with dlthub.
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 198kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall