Explaining the extra 2%: performance attribution as an Airflow pipeline

An investment firm runs money against a yardstick. A UK equity fund gets measured against a UK equity index; a global fund against a global one. When the fund returns 11.32% over a year and its index returns 8.80%, the 2.52% gap is the whole story — it is what the manager gets paid for, what the sales deck leads with, and what the client asks about in the quarterly review.

But the headline number is not enough. The follow-up question is always where did it come from? Was it the decision to go overweight American stocks? The specific technology names picked in Europe? The cash drag? Decomposing that gap into named, quantified decisions is called performance attribution, and it is one of the most satisfying data engineering problems I know of in finance: the math is exact, the inputs are messy, and the output has to land on a portfolio manager's desk on a schedule. That last part is where Airflow comes in.

The decomposition in one table

Attribution works on grouped data. Take every position in the fund and every constituent of the index, tag each with a dimension — region, say — and roll up to four numbers per bucket: the fund's weight in that bucket, the index's weight, the fund's return within it, and the index's return within it.

From those four columns, three factors explain the gap for each bucket:

Selection — with allocation held equal, did the names inside this bucket do better than the index's names? bench_weight × (fund_return − bench_return)
Allocation — with picks held equal, was tilting toward or away from this bucket a good call? (fund_weight − bench_weight) × (bench_return − total_bench_return)
Interaction — the cross-term you get when both the tilt and the picks differ at once: (fund_weight − bench_weight) × (fund_return − bench_return)

The intuition behind each formula is "freeze one lever, wiggle the other." Selection freezes weights at the index's, so any difference is purely the picks. Allocation freezes returns at the index's — and measures them relative to the index total, because overweighting a bucket only helps if that bucket beat the average, not merely if it went up. Interaction is whatever is left when neither lever is frozen. The three columns sum, per bucket and in total, to exactly the fund-minus-index gap. Nothing is unexplained; that additivity is also your best pipeline test.

The same machinery runs on any grouping: region, country, industry, currency, asset class. And it nests — the contribution of one region can itself be decomposed across the countries inside it by re-normalising weights to that region's total. Same function, different grouping column, different denominator.

The pandas core

The calculation itself is embarrassingly small. That is worth noticing, because attribution vendors charge real money for it — the value is never the arithmetic, it is the plumbing around it.

def brinson_attribution(df: pd.DataFrame) -> pd.DataFrame:
    """df: one row per bucket with fund_w, bench_w, fund_r, bench_r."""
    total_bench_r = (df.bench_w * df.bench_r).sum()
 
    out = df.copy()
    out["selection"] = df.bench_w * (df.fund_r - df.bench_r)
    out["allocation"] = (df.fund_w - df.bench_w) * (df.bench_r - total_bench_r)
    out["interaction"] = (df.fund_w - df.bench_w) * (df.fund_r - df.bench_r)
    out["contribution"] = out[["selection", "allocation", "interaction"]].sum(axis=1)
    return out

Getting to that tidy input frame is the actual work:

def bucket_weights_and_returns(
    positions: pd.DataFrame, dimension: str
) -> pd.DataFrame:
    positions["weight"] = positions.start_value / positions.start_value.sum()
    positions["ret"] = positions.end_value / positions.start_value - 1
    grouped = positions.groupby(dimension).apply(
        lambda g: pd.Series({
            "w": g.weight.sum(),
            "r": (g.weight * g.ret).sum() / g.weight.sum(),
        })
    )
    return grouped

Run that once over the fund's holdings and once over the index constituents, join on the bucket, and you have the four-column input. The drill-down variant is the same function with weights re-normalised inside the parent bucket.

Everything hinges on two upstream facts: you need each position's value at both ends of the measurement window, and you need every position — fund and index — tagged with a region, country, sector, and currency. Those classifications come from a security master, and they are where attribution pipelines actually break. A missing sector tag doesn't error; it silently lands in an "Unclassified" bucket and the PM asks why 40 basis points of selection effect appeared from nowhere.

Why this is an orchestration problem

A one-off attribution is a notebook. What firms actually need is attribution for every fund, against its own benchmark, across four or five dimensions, every month-end (often every day), with the report waiting before the front office sits down. That shape — fan-out over funds and dimensions, hard upstream data dependencies, strict delivery deadlines — is precisely what Airflow is for.

The dependency structure falls out naturally:

A few Airflow features carry most of the weight here.

Sensors guard the inputs. Valuations depend on end-of-period prices, and benchmark constituent files arrive from index providers on their own schedule. Deferrable sensors let the DAG wait for the price feed to close and the vendor file to land without burning worker slots — and a sensor timeout at 7am is a page to the data team, hours before it would otherwise surface as a missing report.

Dynamic task mapping handles the fan-out. The fund list and the dimension list are data, not code. Mapping the attribution task over their product means adding a fund or a new cut (say, credit-rating buckets for a bond fund) changes a config table, not the DAG file:

@task
def attribution_inputs() -> list[dict]:
    funds = fetch_active_funds()          # each with its benchmark id
    dims = ["region", "country", "sector", "currency"]
    return [
        {"fund_id": f.id, "benchmark_id": f.benchmark_id, "dimension": d}
        for f in funds for d in dims
    ]
 
run_attribution = attribute_one.expand(spec=attribution_inputs())

Each mapped instance is small, independent, and individually retryable. A bad file for one fund fails one task instance, not the batch — the other forty reports still publish on time.

The additivity check is a first-class task, not an afterthought. The factor columns must sum to the fund-minus-benchmark gap to within rounding. Weights must sum to one on both sides. Every position must have landed in a named bucket. These assertions run as a gating task between compute and publish, so a security-master gap blocks the report instead of decorating it:

@task
def reconcile(result: AttributionResult):
    gap = result.fund_return - result.bench_return
    explained = result.factors.contribution.sum()
    if abs(gap - explained) > 1e-6:
        raise ValueError(f"unexplained residual: {gap - explained:.6f}")
    if result.unclassified_weight > 0:
        raise ValueError("positions missing classification tags")

Backfills are the killer feature. Attribution is recomputed constantly — a restated price, a corrected classification, a benchmark rebalance applied late. Because each run is parameterised by its logical date and reads point-in-time snapshots, airflow dags backfill regenerates any historical window with corrected inputs. In a spreadsheet-driven shop, that same restatement is a week of someone's life.

The shape that emerges

What I like about this problem is how cleanly the layers separate. The financial math is forty lines of pandas that a portfolio manager can read and audit. The data engineering is snapshots, classification joins, and point-in-time correctness. The orchestration is sensors on the inputs, mapped tasks over the fund × dimension grid, a reconciliation gate, and scheduled delivery. None of the layers leaks into the others — the attribution function doesn't know Airflow exists, and the DAG doesn't know what an interaction term is.

That separation is also the honest answer to "why not just buy this?" Vendors sell the formulas, but the formulas were never the hard part. The hard part is that your positions, your benchmark files, and your security master have to converge, validated, at 6am — and that part was always going to be your pipeline anyway.