scherlok

<!-- mcp-name: io.github.rbmuller/scherlok -->
<div align="center">

<img src="https://img.shields.io/badge/python-3.10+-blue?logo=python&logoColor=white" alt="Python 3.10+">
<img src="https://img.shields.io/pypi/v/scherlok?color=green" alt="PyPI">
<a href="https://pepy.tech/project/scherlok"><img src="https://img.shields.io/pepy/dt/scherlok?color=blue&label=downloads" alt="PyPI downloads"></a>
<img src="https://img.shields.io/badge/license-MIT-blue" alt="MIT License">
<a href="https://github.com/rbmuller/scherlok/actions/workflows/ci.yml"><img src="https://github.com/rbmuller/scherlok/actions/workflows/ci.yml/badge.svg" alt="CI"></a>
<a href="https://glama.ai/mcp/servers/rbmuller/scherlok"><img src="https://glama.ai/mcp/servers/rbmuller/scherlok/badges/score.svg" alt="Glama score"></a>
<a href="https://registry.modelcontextprotocol.io/v0.1/servers?search=io.github.rbmuller/scherlok"><img src="https://img.shields.io/badge/MCP%20Registry-io.github.rbmuller%2Fscherlok-success?logo=anthropic" alt="MCP Registry"></a>
<a href="https://rbmuller.github.io/scherlok/"><img src="https://img.shields.io/badge/docs-rbmuller.github.io%2Fscherlok-blue?logo=materialformkdocs&logoColor=white" alt="Documentation"></a>

<br><br>

<img src="assets/scherlok-logo.png" alt="Scherlok" width="120">

<h1>Scherlok</h1>

<p><strong>Zero-config anomaly detection for your database tables.</strong><br>
No YAML, no rules, no thresholds. Scherlok learns what "normal" looks like, then tells you when something changes.</p>

</div>

```bash
pip install scherlok
scherlok ci postgres://user:pass@host/db   # profiles on the first run, detects anomalies on every run after
```

No database handy? The demo seeds one, learns it, breaks it, and catches it, in about a second:

```bash
uvx --from "scherlok[duckdb]" scherlok demo
```

<div align="center">

<img src="examples/demo.svg" alt="Scherlok Demo" width="700">

</div>

Works with **PostgreSQL, BigQuery, Snowflake, MySQL, DuckDB** and **dbt**. Alerts go to Slack, Discord, Teams, email, or your CI exit code.

---

## The Problem

Every data team has the same nightmare:

> A source API silently changes from **dollars to cents**. Revenue dashboards show wrong numbers for **3 weeks** before anyone notices.
>
> A column starts returning **NULLs**. A table stops updating. Row counts drop **40% on a Tuesday**. Nobody knows until the CEO asks why the report looks weird.

Current tools (Great Expectations, Soda, dbt tests) require you to **define what "correct" looks like** before you can detect what's wrong. Hundreds of rules. Dozens of YAML files. And you still miss things — because you can't write rules for problems you haven't imagined yet.

## What It Catches

| Anomaly | What Happened | Severity |
|---------|---------------|----------|
| **Volume drop** | Row count dropped 40% overnight | CRITICAL |
| **Volume spike** | 3x more rows than normal | WARNING |
| **Freshness alert** | Table hasn't updated in 12h (normally every 2h) | CRITICAL |
| **Schema drift** | Column removed or type changed | CRITICAL |
| **NULL surge** | NULL rate jumped from 2% to 45% | WARNING |
| **Distribution shift** | Column mean shifted 3+ standard deviations (Shewhart-style control limit) | INFO, WARNING above 5σ |
| **Cardinality explosion** | Status column went from 5 values to 500 | CRITICAL |

Every anomaly is auto-scored: **INFO**, **WARNING**, or **CRITICAL**. No thresholds to configure.

## How It Works

Scherlok takes the opposite approach of rule-based tools: **learn first, then detect.**

```bash
scherlok connect postgres://user:pass@host/db   # connect once
scherlok investigate                              # learn your data
scherlok watch                                    # detect anomalies
```

Three commands. Five minutes. Done. (`scherlok ci <url>` runs all three in one step for pipelines.)

After five valid profiles, Scherlok learns per-metric variability from the
latest 30 profiles using robust historical baselines for volume, numeric mean
shifts, NULL rates, and distinct counts. During cold start or when history is
not usable, it keeps the conservative fixed defaults.

### 1. `investigate` — Learn the patterns

```bash
$ scherlok investigate

  Profiling 12 tables...
  ✓ users         — 45,231 rows, 8 columns
  ✓ orders        — 1,203,847 rows, 15 columns
  ✓ products      — 892 rows, 12 columns
  ...
  Done. Profiles saved.
```

Scherlok profiles every table: row counts, column types, NULL rates, value distributions, freshness cadence, cardinality. Stores everything locally in SQLite.

### 2. `watch` — Detect anomalies

```bash
$ scherlok watch

  Checking 12 tables against learned profiles...

  🔴 CRITICAL  orders    volume_drop     Row count dropped 52% (1,203,847 → 578,412)
  🟡 WARNING   users     null_increase   Column "email": NULL rate 2.1% → 18.7%
  🔵 INFO      products  distribution    Column "price": mean shifted 3.2σ

  3 anomalies detected. Exit code: 1
```

### 3. Alert — Slack, CI/CD, or both

```bash
# Slack
scherlok watch --webhook https://hooks.slack.com/services/...

# Discord
scherlok watch --webhook https://discord.com/api/webhooks/...

# Microsoft Teams
scherlok watch --webhook https://outlook.office.com/webhook/...

# Any endpoint (generic JSON payload)
scherlok watch --webhook https://my-api.com/alerts

# CI/CD gate (fails pipeline on CRITICAL)
scherlok watch --exit-code --fail-on critical
```

Auto-detects Slack, Discord, and Teams from the URL and formats the payload accordingly. Any other URL receives a generic JSON payload.

## CI/CD Integration

Use Scherlok as a data quality gate. The `ci` command does it in one line:

```yaml
# GitHub Actions
- name: Data quality check
  run: |
    pip install scherlok
    scherlok config --store s3://my-bucket/scherlok/profiles.db
    scherlok ci ${{ secrets.DATABASE_URL }} \
      --webhook ${{ secrets.SLACK_WEBHOOK }} \
      --fail-on critical
```

If Scherlok detects a critical anomaly, the pipeline fails. Bad data never reaches production.

## Works with dbt

Already running dbt? Scherlok complements `dbt test` with **automatic** anomaly detection — no rules to write.

```bash
pip install scherlok[dbt]

# After `dbt run`, point Scherlok at your project
scherlok dbt --project-dir ./my_dbt_project
```

Scherlok reads `target/manifest.json`, discovers every materialized model (`table`, `incremental`, `view`), auto-resolves the connection from your `profiles.yml`, and profiles each model:

```
Investigating 4 dbt models in ./my_dbt_project (postgres)
  ✓ stg_customers                  (12,345 rows)
  ✓ stg_orders                     (98,765 rows)
  ✗ fct_orders                     CRITICAL: Row count dropped 42% (98,765 → 57,283)
  ✓ dim_customers_inc              (12,300 rows)

Summary: 4 profiled, 1 anomalies (1 critical, 0 warning)
```

Use it as a CI gate after `dbt run`:

```yaml
- run: dbt run --target prod
- run: scherlok dbt --project-dir . --target prod --fail-on critical
```

Or collapse both steps into one with the wrapper:

```yaml
- run: scherlok dbt-run-and-watch --project-dir . --target prod --fail-on critical
```

The wrapper runs `dbt run` by default and uses the successful model nodes recorded in
`target/run_results.json`, so partial runs profile only what dbt actually built. Use
`--build` to run `dbt build`; successful models are still profiled when a test failure
causes downstream models to be skipped on dbt's handled failure path (exit 1), while
the wrapper preserves `dbt build`'s exit code. Unhandled failures fail fast without
reading the artifact.

Both `dbt` and `dbt-run-and-watch` accept `--output json` for CI parsers — a single JSON document on stdout, nothing else.

**Supported adapters:** `postgres`, `bigquery`, `snowflake`, `mysql`, `duckdb`. For others, pass `--connection-string` explicitly.

📖 Full docs: [dbt integration guide →](src/scherlok/dbt/README.md)

## dbt Package — native tests

Prefer staying inside dbt? Install Scherlok as a dbt package for native data quality tests — no Python CLI needed.

```yaml
# packages.yml
packages:
  - git: https://github.com/rbmuller/scherlok.git
    revision: v1.0.1
```

Once the dbt Package Hub listing lands ([dbt-labs/hubcap#456](https://github.com/dbt-labs/hubcap/pull/456)), this becomes `package: rbmuller/scherlok` with `version: [">=1.0.0", "<2.0.0"]`.

```yaml
# schema.yml
models:
  - name: fct_orders
    tests:
      - scherlok.volume_anomaly:
          sensitivity: 3.0
      - scherlok.row_count_between:
          min_value: 100
    columns:
      - name: email
        tests:
          - scherlok.not_null_proportion:
              max_rate: 0.01
      - name: updated_at
        tests:
          - scherlok.recency:
              days: 2
```

**Tier 1 — Instant (no setup):** `not_null_proportion`, `row_count_between`, `recency`, `unique_proportion`

**Tier 2 — Auto-learning (Shewhart control limits):** `volume_anomaly`, `null_anomaly` — require the `scherlok_metrics` model to build baseline history.

📖 Full docs: [dbt package README →](models/_models.yml)

## HTML dashboard

![scherlok dashboard](assets/dashboard-screenshot.png)

```bash
scherlok dashboard --out report.html
```

One self-contained HTML file (~28 KB): KPIs, per-table incidents grouped with first-seen timestamps, `+`/`−`/`~` schema-drift diff, sparklines, and full anomaly history. Auto dark/light theme via `prefers-color-scheme`.

📖 Full docs: [dashboard guide →](src/scherlok/dashboard/README.md)

## Use it from an AI agent (MCP)

Let Claude Code / Claude Desktop run data-quality checks directly:

```bash
pip install scherlok   # scherlok-mcp ships built-in since v0.7.0
```

```json
{
  "mcpServers": {
    "scherlok": {
      "command": "scherlok-mcp",
      "env": { "SCHERLOK_CONNECTION": "postgresql://user:pass@host/db" }
    }
  }
}
```

The agent gets `list_tables`, `investigate`, `watch`, `status`, `history`, and `check` as tools. Credentials are resolved server-side (never passed by the model)