SQL, Python, and R for Actuaries

Modern reserving work increasingly happens in code rather than spreadsheets, both because triangles built from raw transactions are auditable and reproducible, and because packages like chainladder (Python) and ChainLadder (R) implement stochastic methods that are impractical to hand-roll reliably in a workbook. This article walks through building a triangle in SQL, then fitting Mack chain-ladder in Python and R.

Building a triangle from transactions with SQL window functions

Raw claims systems store one row per transaction (payment, reserve change), not a pre-aggregated triangle. The first step is aggregating transactions into incremental paid loss by accident year and development period, then cumulating with a window function. Given a table claim_transactions(claim_id, accident_year, transaction_date, paid_amount), and a reference valuation_date:

WITH dev AS (
  SELECT
    accident_year,
    FLOOR(
      DATEDIFF(day, DATE(accident_year || '-01-01'), transaction_date) / 365.25
    ) AS dev_year,
    paid_amount
  FROM claim_transactions
  WHERE transaction_date <= '2023-12-31'
),
incr AS (
  SELECT
    accident_year,
    dev_year,
    SUM(paid_amount) AS incremental_paid
  FROM dev
  GROUP BY accident_year, dev_year
),
cum AS (
  SELECT
    accident_year,
    dev_year,
    SUM(incremental_paid) OVER (
      PARTITION BY accident_year
      ORDER BY dev_year
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_paid
  FROM incr
)
SELECT accident_year, dev_year, cumulative_paid
FROM cum
ORDER BY accident_year, dev_year;

The window function SUM(...) OVER (PARTITION BY accident_year ORDER BY dev_year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is the running-total idiom that turns incremental amounts into the cumulative $C_{i,k}$ values a triangle needs, without a self-join or procedural loop. Pivoting dev_year into columns (accident year down the rows, development period across) is typically done downstream in the reporting or BI layer, or with a database-specific PIVOT clause.

Age-to-age factors and the chain ladder

Once cumulative losses $C_{i,k}$ are available, the volume-weighted age-to-age factor is

$f_k = \frac{\sum_i C_{i,k+1}}{\sum_i C_{i,k}},$

summed over accident years $i$ with both $C_{i,k}$ and $C_{i,k+1}$ observed, and the cumulative development factor to ultimate is

$CDF_k = \prod_{j=k}^{n-1} f_j, \qquad \hat U_i = C_{i,k} \times CDF_k.$

Python: pandas + chainladder with Mack

The chainladder package wraps this in a stochastic framework that also returns Mack standard errors:

import pandas as pd
import chainladder as cl

# df has columns: accident_year, dev_year, cumulative_paid
triangle = cl.Triangle(
    data=df,
    origin="accident_year",
    development="dev_year",
    columns="cumulative_paid",
    cumulative=True,
)

mack = cl.MackChainladder().fit(triangle)

ultimate = mack.ultimate_
reserve = mack.ibnr_
std_error = mack.total_mack_std_err_

print(ultimate)
print(reserve)
print(std_error)

mack.ultimate_ returns the projected ultimate loss by accident year, mack.ibnr_ the implied reserve, and mack.total_mack_std_err_ the Mack standard error of the total reserve estimate, from which a CV and a normal-approximation range can be derived exactly as in the reserving article on ASOP 43.

R: the ChainLadder package

The R ecosystem's ChainLadder package predates chainladder-python and remains the reference implementation many practitioners validate against:

library(ChainLadder)

# tri is a triangle object built with as.triangle() from a long-format data frame
tri <- as.triangle(
  df,
  origin = "accident_year",
  dev = "dev_year",
  value = "cumulative_paid"
)

mack_fit <- MackChainLadder(tri, est.sigma = "Mack")

summary(mack_fit)
mack_fit$FullTriangle
mack_fit$Mack.S.E

summary(mack_fit) prints ultimate, reserve, and both the Mack standard error and the CV by accident year and in total, matching the theoretical formula

$\mathrm{se}(\hat R_i)^2 = \hat U_i^2 \sum_{k} \frac{\hat\sigma_k^2}{f_k^2}\left(\frac{1}{C_{i,k}} + \frac{1}{\sum_j C_{j,k}}\right).$

Cross-checking that Python's chainladder and R's ChainLadder produce the same age-to-age factors and total reserve (they should, to rounding, on identical input data and default settings) is a useful sanity check before relying on either in production.

Reproducibility practices

• Pin package versions (requirements.txt / renv.lock) — chain-ladder point estimates are stable across versions, but default tail-factor and bootstrap behavior can change.
• Separate data extraction (SQL) from modeling (Python/R) into distinct, version-controlled scripts, so a triangle can be regenerated from the same query months later.
• Parameterize the valuation date rather than hardcoding it, so re-running for a new quarter does not require editing logic.
• Store the raw extract alongside the aggregated triangle, so a reconciliation (see the ASOP 23 article) can be re-run without re-querying the source system.
• Use notebooks for exploration, scripts for production — a notebook that must be run top-to-bottom in order to reproduce results is a common, avoidable source of silent errors.
• Log package/library versions and random seeds for any bootstrap or simulation-based reserve range, since these are not exactly reproducible without a fixed seed.

Pitfalls

• Building the triangle's development-period bucketing inconsistently between the SQL layer and the modeling layer (e.g., 12-month vs. 365.25-day periods), which silently shifts every age-to-age factor.
• Feeding a triangle with a partial, still-accruing latest diagonal into a Mack model without adjusting for known reporting lag.
• Treating a chainladder-python or ChainLadder R output as ground truth without validating a hand-calculated volume-weighted factor for at least one development period.

Exam relevance

Loss development mechanics underlie CAS Exam 5, the Mack model and its standard error formula are core to CAS Exam 7, and data/tooling practices are part of the professionalism material on CAS Exam 6.

Further reading

• chainladder-python documentation (chainladder-python.readthedocs.io)
• Gesmann, M. et al., R ChainLadder package documentation and vignettes
• Friedland, J., Estimating Unpaid Claims Using Basic Techniques (CAS)