OpenMedallion

Declarative medallion pipelines in pure open-source Python — local first, cloud portable, fast by default.

OpenMedallion is an opinionated open-source library for building Bronze → Silver → Gold data warehouse and lakehouse pipelines using dlt, Polars, and Hamilton — without depending on expensive enterprise platforms or proprietary tooling.

Why OpenMedallion?

Modern open-source data tools are individually excellent — but combining them into a production-ready medallion architecture is still fragmented.

You already have great tools for ingestion, transformation, loading, orchestration, and validation. But you still have to stitch everything together yourself — writing glue code, defining project structure, creating naming conventions, managing layer boundaries, and maintaining all of it over time.

OpenMedallion exists to reduce that friction.

Quickstart

pip install openmedallion

medallion init my_project       # scaffold: YAML configs + UDF stubs + kestra_flow.yml
medallion run my_project        # Bronze → Silver → Gold in one command
medallion run my_project --layer silver   # re-run a single layer

Key Features

• Declarative YAML config — define pipeline layers without writing boilerplate
• Incremental loads — append and merge modes via dlt cursor columns and primary keys
• Rich silver transforms — 12 built-in declarative transform types (rename, cast, drop, fillna, clip, normalize, deduplicate, filter_rows, map_values, allowed_values, coerce_bool, udf) — no Python needed for common operations
• Expressive gold aggregations — 11 aggregation functions (count, sum, mean, min, max, median, std, var, first, last, count_distinct) plus having, sort, and limit post-aggregation controls
• Composable UDFs — drop Python functions into udf/silver/ or udf/gold/; no new framework to learn
• Local first — run the full pipeline against Parquet files with zero cloud credentials
• Cloud portable — swap filesystem for S3 in one line; logic stays unchanged
• Source agnostic — any dlt source: SQL databases, REST APIs, filesystems, and more
• Fast by default — Polars for all transforms; no pandas bottlenecks
• Natural-language queries — ask questions about your data in plain English; works with Ollama (local), OpenRouter, OpenAI, or any OpenAI-compatible endpoint

How It Works

OpenMedallion wires three best-in-class open-source tools under a unified declarative config:

YAML config
    │
    ▼
Hamilton DAG           ← orchestrates which layer runs and in what order
    │
    ├── Bronze  (dlt)     ← ingests raw data from any source into Parquet
    ├── Silver  (Polars)  ← typed UDF transforms: rename, cast, filter, enrich
    └── Gold    (Polars)  ← YAML-declared group-by aggregations + window metrics

Installation

pip install openmedallion

Optional extras:

pip install "openmedallion[s3]"        # S3 support via s3fs + boto3
pip install "openmedallion[oracle]"    # Oracle DB support via oracledb
pip install "openmedallion[profile]"   # Data profiling reports via ydata-profiling
pip install "openmedallion[explore]"   # Interactive exploration reports via pygwalker
pip install "openmedallion[cerebrum]"  # Natural-language queries: DuckDB engine + neuron FastAPI server
pip install "openmedallion[cortex]"    # Cortex Dash chat UI (requires [cerebrum])

Requires Python 3.11+

Project Structure

medallion init my_project generates a complete, ready-to-run project:

my_project/
├── main.yaml                    # pipeline name + layer includes + paths
├── backend/
│   ├── bronze.yaml              # source connection + incremental config
│   ├── silver.yaml              # table transforms (rename, cast, filter, UDFs)
│   ├── gold.yaml                # aggregations (group_by + metrics + window fns)
│   └── udf/
│       ├── silver/              # Python UDFs called from silver.yaml
│       └── gold/                # Python UDFs called from gold.yaml
├── frontend/                    # dashboard files (Tableau, Power BI, etc.)
├── data/                        # gitignored pipeline outputs
├── summary/                     # analysis write-ups
├── kestra_flow.yml              # Kestra orchestration flow — mount via docker-compose.yml
└── README.md                    # pre-filled project documentation template

Configuration

main.yaml — declare your layers and data paths:

pipeline:
  name: customer_warehouse

includes:
  bronze: bronze.yaml
  silver: silver.yaml
  gold:   gold.yaml

paths:
  bronze: "./data/bronze"
  silver: "./data/silver"
  gold:   "./data/gold"
  export: "./data/export"

silver.yaml — declarative transforms with optional UDFs:

bronze_to_silver:
  tables:
    - source_file: ORDERS.parquet
      output_file: orders.parquet
      transforms:
        - type: rename
          columns:
            ORDER_ID:    order_id
            CUSTOMER_ID: customer_id
        - type: cast
          columns:
            order_id: Int64
            amount:   Float64
        - type: clip                          # clamp numeric or date columns
          columns:
            amount: {min: 0}
            order_date: {min: "2020-01-01", max: "2030-12-31"}
        - type: allowed_values               # null out unexpected categories
          columns:
            status: [pending, shipped, delivered, cancelled]
        - type: normalize                    # strip whitespace + lowercase
          columns:
            region: strip_lower
        - type: fillna                       # fill remaining nulls
          columns:
            region: "unknown"
        - type: coerce_bool                  # "yes"/"1"/"true" → true, etc.
          columns: [is_priority]
        - type: deduplicate                  # remove exact duplicates
          subset: [order_id]
        - type: udf
          file: udf/silver/enrich.py
          function: flag_large_orders
          args:
            threshold: 500.0

Built-in silver transform types: rename · cast · drop · fillna · clip (numeric + date/datetime) · normalize (upper/lower/strip/strip_lower) · deduplicate · filter_rows (SQL expression) · map_values · allowed_values · coerce_bool · udf

gold.yaml — YAML-declared aggregations with post-aggregation controls:

silver_to_gold:
  projects:
    - name: customer_warehouse
      aggregations:
        - source_file: orders.parquet
          group_by: [customer_id]
          metrics:
            - {column: order_id, agg: count,          alias: total_orders}
            - {column: amount,   agg: sum,             alias: total_spent}
            - {column: amount,   agg: median,          alias: median_order}
            - {column: amount,   agg: std,             alias: spend_std}
            - {column: order_id, agg: count_distinct,  alias: unique_products}
          having: "total_orders > 1"         # filter after aggregation
          sort:
            columns: [total_spent]
            descending: true
          limit: 100                         # top N rows
          output_file: customer_summary.parquet

Built-in gold aggregations: count · sum · mean · min · max · median · std · var · first · last · count_distinct

Python UDFs

Business logic stays in plain Python — no custom DSL, no magic.

# udf/silver/enrich.py
import polars as pl

def flag_large_orders(df: pl.DataFrame, threshold: float = 500.0) -> pl.DataFrame:
    return df.with_columns(
        (pl.col("amount") >= threshold).alias("is_large_order")
    )

Drop the file next to your config, reference it in silver.yaml, done.

Incremental Loads

OpenMedallion supports dlt's native incremental strategies out of the box:

# bronze.yaml
source:
  type: sql_database
  dialect: sqlite
  connection_string: "sqlite:///data/mydb.db"
  tables:
    - name: orders
      incremental:
        mode: append          # cursor-based — only new rows
        cursor_column: created_at
        initial_value: "2024-01-01"
    - name: customers
      incremental:
        mode: merge           # upsert — handles updates + deletes
        primary_key: customer_id

dlt tracks cursor state automatically. Re-running bronze only pulls the delta.

Natural Language Queries

Ask questions about your pipeline data in plain English — no SQL required.

pip install "openmedallion[cerebrum]"

medallion query my_project "What are the top 5 customers by revenue?"
medallion query my_project "Show monthly trends" --model mistral
medallion query my_project "Headcount by dept and revenue by region" --decompose
medallion query my_project "Show me the good ones" --detect-ambiguity
medallion query my_project "Revenue by region for 2024-Q2" --use-templates
medallion query my_project "Which rep leads in revenue?" --user alice

This runs the full cerebrum pipeline locally: builds a schema context from your silver/gold Parquet files, generates SQL with an LLM, validates it against DuckDB, executes it, and prints the results alongside a canonical reproducible prompt.

Provider options

By default, cerebrum uses a local Ollama server (ollama serve). To use a cloud provider, set the provider and API key:

# OpenRouter (access to GPT-4o, Claude 3.5, Llama 3, Mistral, …)
MEDALLION_LLM_PROVIDER=openrouter \
MEDALLION_LLM_API_KEY=sk-or-... \
MEDALLION_LLM_MODEL=openai/gpt-4o \
medallion query my_project "Revenue by region"

# OpenAI
MEDALLION_LLM_PROVIDER=openai \
MEDALLION_LLM_API_KEY=sk-... \
MEDALLION_LLM_MODEL=gpt-4o-mini \
medallion query my_project "Revenue by region"

# Any OpenAI-compatible endpoint (LM Studio, Groq, vLLM, …)
MEDALLION_LLM_PROVIDER=lmstudio \
MEDALLION_LLM_BASE_URL=http://localhost:1234/v1 \
MEDALLION_LLM_MODEL=local-model \
medallion query my_project "Revenue by region"

Or configure once in settings.yaml:

llm:
  provider: openrouter
  model:    openai/gpt-4o
  api_key:  sk-or-...

HTTP server + chat UI

medallion ask    my_project                                   # start neuron FastAPI server on :8000
medallion ask    my_project --model mistral --port 8001       # custom model + port
medallion cortex my_project                                   # start Dash chat UI on :8050
medallion cortex my_project --neuron-url http://localhost:8001 --debug

neuron exposes /query, /feedback, /history, /session/end, /health, and /docs (Swagger UI). cortex connects to it over HTTP and provides a four-tab UI — chat (with 👍/👎 feedback on every reply), table, dashboard with CSV/Excel download, and history (your own past questions, with a "↻ Reuse" button).

Per-person chat history — zero cost, no login system

Every question — from cortex or medallion query — is logged to <project>/chat_history/<username>.jsonl, a personal audit trail (never fed back into the LLM prompt). username is a plain display name, not real auth — cortex asks for one once (saved in the browser); the CLI defaults to your OS username or --user NAME.

A 👍/👎 click sets that turn's accepted field in place (three-state — true/false only from an explicit click, null if you never rate it; never inferred from behavior, since a guessed signal risks reinforcing a wrong answer). Closing a session — an "End Session" button, 5 minutes idle, or closing the tab — rolls the session's rated turns into examples/harvested.jsonl/failures.jsonl, the same files medallion examples harvest/review already work with. Zero cost throughout: Ollama and the local chromadb embedding function used for few-shot ranking are both free — no paid API touches this loop unless you explicitly configure one for LLM_PROVIDER itself.

Improving accuracy — metadata, relationships, and examples (RAG)

For larger schemas, curate what the LLM sees instead of dumping every table:

medallion metadata generate      my_project   # LLM-draft table/column descriptions
medallion metadata generate      my_project --profile   # + ydata-profiling dtype/stats/accepted_values
medallion metadata approve       my_project   # human review, one table at a time
medallion metadata refresh       my_project   # detect schema drift, re-draft affected tables
medallion metadata refresh       my_project --check   # CI gate: exit 1 if schema drifted

medallion relationships generate my_project   # detect joins — no LLM call
medallion relationships approve  my_project
medallion relationships erd      my_project   # Mermaid ER diagram → relationships_erd.md

medallion examples generate      my_project --count 15   # LLM-draft Q→SQL pairs
medallion examples approve       my_project
medallion examples approve       my_project --template   # promote a verified example to a deterministic template
medallion examples harvest       my_project   # promote cortex 👍 candidates into synthetic.jsonl
medallion examples review        my_project   # list cortex 👎 failures for triage
medallion examples eval          my_project   # regression check: did curation help or hurt?
medallion examples eval          my_project --use-templates   # + compare template-routed results

Once metadata.yaml has status: approved tables and examples/synthetic.jsonl has verified: true examples, medallion query/ask pick them up automatically — no extra flag needed. Three things happen behind the scenes:

• Dynamic few-shot retrieval — the most relevant verified examples are ranked in as prompt context, instead of a fixed static list.
• Confidence-gated schema pruning — only the top-k most relevant approved tables are shown to the LLM. If similarity to the best match falls below a confidence threshold, it falls back to a raw-schema search over every silver table (any status, no metadata.yaml required) rather than trusting a weak match.
• Template-routed queries (opt-in, --use-templates) — a high-confidence match to a curated templated: true example skips SQL generation entirely; the LLM only fills the template's declared parameters, never authors query logic. Closes a correctness gap the other two mitigations can't: syntactically-valid-but-semantically-wrong SQL for known, frequently-asked questions.

See docs/guides/rag-accuracy.md for the full design, and examples/sales_intelligence_demo/ for a runnable, offline (show_rag_workflow.py) walkthrough.

Scheduling with Kestra

medallion init generates a kestra_flow.yml inside every new project — a ready-to-use Kestra flow that orchestrates bronze → silver → gold with per-task observability and retry support.

1. Start a local Kestra server

# from the repo root — requires Docker
make kestra-up
# UI available at http://localhost:8080

2. Register a project flow

Add one volume mount to the kestra service in docker-compose.yml:

- ./my_project/kestra_flow.yml:/app/flows/my_project.yml

Kestra picks up the file automatically on the next make kestra-up — no copying needed.

3. Trigger a run

From the UI at http://localhost:8080, or via the API:

curl -X POST \
  http://localhost:8080/api/v1/executions/openmedallion.projects/my_project

4. Enable scheduled refresh

Uncomment the triggers: block in kestra_flow.yml:

triggers:
  - id: daily_refresh
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "0 6 * * *"   # every day at 06:00 UTC

Restart with make kestra-up and Kestra picks up the change immediately.

Kestra vs GitHub Actions

Recommended split: GitHub Actions for CI + publish; Kestra for pipeline scheduling.

Examples

Five self-contained examples — no cloud credentials required. See examples/ for a side-by-side comparison.

When to Use OpenMedallion

A great fit if you:

• Want a standard medallion project layout without inventing one from scratch
• Prefer YAML-first config with Python escape hatches for complex logic
• Need local-first development that can scale to S3 with minimal changes
• Want full ownership of your code and infrastructure
• Are building on a tight budget without enterprise platform procurement

Not a fit if you need:

• A full enterprise data platform (Databricks, Snowflake, BigQuery)
• A no-code or drag-and-drop ETL tool
• A universal framework for every possible pipeline architecture

Tradeoffs

Roadmap

Contributing

Contributions are welcome. Good areas to contribute:

• Bug fixes and edge-case handling
• Documentation improvements and example additions
• Tests and coverage
• New pipeline templates
• New source or destination adapters
• CLI enhancements

If you are interested in open-source data architecture, your help is appreciated.

License

MIT — free to use, modify, and distribute.

If OpenMedallion looks useful, consider starring the repo — it helps others find it.