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.
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 .
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)
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
On the sample file the output is:
overall_score: 96.0
release_year 100.0 | provider 92.0 | catalog 92.0 | streams 100.0
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
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")
)
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
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'}]
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
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}"
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
}
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
- Write the metadata as files, not logs. JSON next to the data is diffable, testable and readable by a BI tool.
- Keep scoring formulas short enough to argue with. I found the categorical-uniqueness flaw only because the whole formula fit on a screen.
-
Make "unknown" a valid value.
unit: nullandoutlier_flag: falsefor short columns are honest defaults. -
Test your own lineage with an adversarial table. It took five lines to find the
year/release_yearbug, 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)