DEV Community

Matteo
Matteo

Posted on

Audit trails for small pandas pipelines: quality scores, column lineage and metric names

Most analytics pipelines I have seen answer "what is the number?" and cannot answer the next question: "where did it come from, and how much should I trust it?"

Heavy tools exist for that (dbt, Great Expectations, OpenLineage). But for a small project, a CSV on a laptop or a portfolio-sized dataset, they can be more machinery than the problem needs. This post walks through how I handled three of those concerns in Quaero, a small pandas-based pipeline I built: data quality scoring, column-level lineage and semantic metric naming. All the code below is copied from the repository, and all the output comes from running it on the bundled sample file. I also point out where each approach is weak, because I found some of those weak spots while writing this.

Quaero interface preview

The setup

Quaero takes a dataset and a question, and writes cleaned tables ("marts") plus a set of JSON files describing what happened. Running the bundled sample (5 rows, 4 columns) is one command (Python 3.11+, from the repository root):

git clone https://github.com/matteocavo/Quaero.git && cd Quaero
python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt

python -m app.main sample_data/release_impact_sample.csv \
  --source-name release_impact \
  --question "Which release years generate the strongest average streams?" \
  --project-root .
Enter fullscreen mode Exit fullscreen mode

Next to the marts, the run writes dq_score_*.json, lineage.json, cleaning_log_*.json and metrics_definitions.json. The idea is that the metadata is a first-class output, not a log you hope someone reads.

1. Data quality: a small, explainable score

Each column gets a score from three dimensions, with weights written down in the module header rather than hidden in a config:

_COMPLETENESS_WEIGHT = 0.40
_UNIQUENESS_WEIGHT = 0.20
_FORMAT_WEIGHT = 0.40

def _completeness(series: pd.Series) -> float:
    if len(series) == 0:
        return 100.0
    return round(float(series.notna().mean() * 100), 1)
Enter fullscreen mode Exit fullscreen mode

Outliers are flagged but deliberately do not change the score:

def _outlier_flag(series: pd.Series) -> bool:
    if not is_numeric_dtype(series):
        return False
    non_null = series.dropna()
    if len(non_null) < 4:
        return False
    mean = float(non_null.mean())
    std = float(non_null.std())
    if std == 0:
        return False
    outliers = ((non_null - mean).abs() > _OUTLIER_SIGMA * std).sum()
    return float(outliers / len(non_null)) > _OUTLIER_THRESHOLD
Enter fullscreen mode Exit fullscreen mode

On the sample file the output is:

overall_score: 96.0
release_year 100.0 | provider 92.0 | catalog 92.0 | streams 100.0
Enter fullscreen mode Exit fullscreen mode

Why is a clean categorical column at 92? Because provider has 3 distinct values in 5 rows, so the uniqueness dimension is 60 and the weighted score is 0.4·100 + 0.2·60 + 0.4·100 = 92. That is a real design flaw worth admitting: uniqueness is the right signal for an ID column and the wrong one for a category. A better version would only apply it where the column is expected to be a key. The value of keeping the formula this small is that you can see the flaw by reading ten lines, and fix it.

The format score is also an honest proxy: numeric columns get 100, and text columns lose points in proportion to non-string values mixed in. It does not validate formats such as emails or dates.

2. Cleaning decisions as data

Every conservative cast is recorded as a structured decision instead of a log line:

def _decision(step, action, columns, rows_affected, details):
    entry = {"step": step, "action": action, "details": details}
    if columns is not None:
        entry["columns"] = columns
    if rows_affected is not None:
        entry["rows_affected"] = rows_affected
    return entry
Enter fullscreen mode Exit fullscreen mode

A text column is only cast to numeric when enough values convert (NUMERIC_CONVERSION_THRESHOLD), and the decision stores the ratio and how many values failed. The sample file was already clean, so its cleaning_log contains zero decisions, which is itself useful information: nothing was changed silently.

Because decisions are structured, the lineage step can consume them. That is the next part.

3. Column-level lineage without a lineage platform

pipelines/lineage.py builds a graph with three layers (raw, staging, mart) and four edge types (normalized, type_cast, aggregated, passthrough). The edge type for the staging layer comes from the cleaning decisions:

raw_origin = raw_normalized.get(col)
if raw_origin:
    edge_type = (
        "type_cast"
        if col in cast_decisions
        else ("normalized" if raw_origin != col else "passthrough")
    )
Enter fullscreen mode Exit fullscreen mode

For the sample run this gives 46 nodes and 28 edges (the sample only exercises passthrough and aggregated; the other two types appear when columns are renamed or cast), for example:

raw::streams      -> staging::streams                       passthrough
staging::streams  -> mart::mart_summary_stats::avg_streams  aggregated
staging::streams  -> mart::mart_summary_stats::p95_streams  aggregated
Enter fullscreen mode Exit fullscreen mode

A separate module, pipelines/lineage_html.py, renders lineage.json as an HTML view with a Mermaid diagram. The mart layer, though, is where I need to be honest. Aggregated columns are linked back to a staging column with a name heuristic: the first staging column whose name appears inside the mart column name. The code says so in a comment, and it breaks in a predictable way. I tested it with a staging table that has both year and release_year. This is the behaviour before the fix:

st = pd.DataFrame({"year": [1, 2], "release_year": [2020, 2021], "streams": [5, 6]})
mart = pd.DataFrame({"avg_release_year": [2020.5]})
# edge to the mart column, before the fix:
# [{'from': 'staging::year', 'to': 'mart::m::avg_release_year', 'type': 'aggregated'}]
Enter fullscreen mode Exit fullscreen mode

avg_release_year is attributed to year, not release_year. The root cause is the combination of a substring test and a break on the first hit, so the result depended on column order. Name-based lineage is cheap and right most of the time, but it is a guess, and a lineage graph that guesses silently is worse than none.

The fix I made is deliberately small. The metric names already follow a machine-readable convention (next section), so the lineage step now asks that convention first and only falls back to the substring match, choosing the longest contained name:

def _resolve_source_column(mart_column, staging_columns):
    parsed = parse_semantic_metric_column(mart_column)
    if parsed is not None:
        source_column = parsed["source_column"]
        if source_column is None:  # e.g. record_count: counts rows, has no source column
            return None
        if source_column in staging_columns:
            return source_column
    candidates = [c for c in staging_columns if c in mart_column]
    return max(candidates, key=len) if candidates else None
Enter fullscreen mode Exit fullscreen mode

Seven regression tests cover ambiguous names: column order, a shorter name that really is the source, the fallback, no match, and record_count. A review of the fix caught that last case: record_count has no source column, yet the substring fallback could still link it to a staging column called record or count, so the helper now returns no source for it. Four of the seven tests fail on the code before the fix. A heuristic is still involved for names outside the convention, so the honest long-term answer is for each mart builder to declare its source columns explicitly.

4. Semantic metric names that can be parsed back

The last piece is naming. Metrics follow a <aggregation prefix>_<source column> convention, defined in one table:

AGGREGATION_PREFIXES = {
    "average": "avg", "sum": "total", "count": "count", "median": "median",
    "min": "min", "max": "max", "std": "std",
    "p05": "p05", "p25": "p25", "p75": "p75", "p95": "p95",
}

def build_semantic_metric_name(source_column: str, aggregation: str) -> str:
    if source_column == "record_count":
        return "record_count"
    prefix = AGGREGATION_PREFIXES.get(aggregation)
    if prefix is None:
        raise ValueError(f"Unsupported semantic aggregation: {aggregation}")
    return f"{prefix}_{source_column}"
Enter fullscreen mode Exit fullscreen mode

The point is that the convention is reversible. parse_semantic_metric_column("avg_streams") returns the source column, the aggregation and a description, which feed metrics_definitions.json:

{
  "metric_name": "catalog_summary_avg_streams",
  "source_mart": "mart_catalog_summary",
  "source_column": "avg_streams",
  "source_metric_column": "streams",
  "formula_description": "Average of `streams` in `mart_catalog_summary`.",
  "default_aggregation": "average",
  "unit": null
}
Enter fullscreen mode Exit fullscreen mode

Note "unit": null. Units are inferred from column names with a short list of rules, and when none matches the function returns None instead of guessing. For a metric catalogue I would rather have an explicit "unknown" than a confident wrong unit.

What this is not

  • It is not a replacement for dbt or Great Expectations. There is no test runner, no scheduling and no expectation suites.
  • The quality score is a summary for orientation, not a pass/fail gate.
  • The lineage is column-level, and for names outside the metric naming convention it is still a heuristic at the mart layer.
  • The public demo is browser-only and runs on bundled illustrative data; the Python pipeline runs locally.

What I took from building it

  1. Write the metadata as files, not logs. JSON next to the data is diffable, testable and readable by a BI tool.
  2. Keep scoring formulas short enough to argue with. I found the categorical-uniqueness flaw only because the whole formula fit on a screen.
  3. Make "unknown" a valid value. unit: null and outlier_flag: false for short columns are honest defaults.
  4. Test your own lineage with an adversarial table. It took five lines to find the year / release_year bug, and a few more to pin it with tests.

A question for you

For small pipelines, where do you draw the line between "write your own metadata" and "adopt a lineage/quality tool"? And for mart-level lineage specifically, would you declare sources explicitly in each mart builder or parse the SQL/dataframe operations?

The code is at https://github.com/matteocavo/Quaero (MIT). Issues and corrections are welcome.

Top comments (0)