--- title: "Large Portfolios" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Large Portfolios} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r, include = FALSE} knitr::opts_chunk$set( collapse = TRUE, comment = "#>" ) database_packages <- c("DBI", "dbplyr", "dplyr", "duckdb") duckdb_available <- all(vapply( database_packages, requireNamespace, quietly = TRUE, FUN.VALUE = logical(1) )) ``` ```{r} library(insurancerating) ``` ## Why large portfolios require a different workflow Insurance portfolio extracts can contain millions of policy-period records. The practical limit is not imposed by R as a statistical language, but by the memory needed to hold the data and temporary objects created during a calculation. A database can perform the first reduction when the row-level portfolio does not fit comfortably in memory. For many traditional insurance pricing GLMs, the policy rows are not all needed once records with identical model covariates have been combined. A **model point** is one observed combination of explanatory variables or rating factors, together with aggregated exposure and response quantities such as claim count and claim cost. Constructing model points is therefore both a data-reduction step and a natural way to prepare a modelling dataset. Two different operations are used in this vignette: - `merge_date_ranges()` and `merge_date_ranges_db()` consolidate compatible policy periods over time; - `rating_grid()` and `rating_grid_db()` aggregate records to model points. Temporal consolidation and model-point aggregation solve different problems and can be used sequentially. A typical large-portfolio workflow is: `policy periods -> optional temporal consolidation -> derive rating factors -> model-point aggregation -> collect into R -> fit the pricing GLM`. ## Memory rather than a fixed row limit There is no generally valid maximum number of rows for R. Required memory depends on the number and type of columns. Ten million rows containing a few integer or factor columns are materially smaller than ten million rows with many character fields. Reading a file and subsequently grouping it may also require more memory than the final object because source and intermediate objects coexist temporarily. The following ranges are practical planning guidance rather than hard limits: | Portfolio size | Typical approach | |---|---| | Up to about 1 million rows | Usually straightforward in R | | About 1 to 10 million rows | Often feasible for a reasonably narrow table with sufficient memory and `data.table`-based operations | | Above about 10 million rows | Estimate memory before importing; database reduction is often preferable | | About 50 million wide portfolio rows | Usually reduce in DuckDB or the source database before collecting into R | A sample of the source file gives a more useful estimate than its row count: ```{r, eval = FALSE} bytes_per_row <- as.numeric(object.size(portfolio_sample)) / nrow(portfolio_sample) estimated_object_gb <- bytes_per_row * expected_rows / 1024^3 ``` Allow additional working memory for reading, copying, grouping and modelling. If the estimated object already occupies a substantial part of available RAM, perform the initial reduction in a database. ## From policy records to model points ### Optional temporal consolidation `merge_date_ranges()` combines connected coverage periods within the same policy or risk. This is useful when renewals, endorsements or administrative splits describe a continuous period with otherwise unchanged characteristics. ```{r} periods <- data.frame( policy_id = c("P001", "P001", "P002"), coverage = c("Fire", "Fire", "Fire"), period_start = as.Date(c("2025-01-01", "2025-07-01", "2025-01-01")), period_end = as.Date(c("2025-06-30", "2025-12-31", "2025-12-31")), earned_exposure = c(0.5, 0.5, 1) ) merge_date_ranges( periods, period_start = "period_start", period_end = "period_end", group_by = c("policy_id", "coverage"), aggregate_cols = "earned_exposure" ) ``` This step is optional. It is not required when the source periods already have the intended modelling granularity. ### Local model-point aggregation `rating_grid()` combines rows with the same observed rating-factor values and preserves additive quantities. The example below includes earned exposure, claim count, claim cost and earned premium. Reconstruction value is first rounded to units of EUR 1,000 because individual euro values are not relevant to this example tariff structure. ```{r} set.seed(2026) local_portfolio <- data.frame( policy_id = seq_len(100000), sector = sample(c("Industry", "Retail", "Services"), 100000, TRUE), region = sample(c("North", "South", "West"), 100000, TRUE), reconstruction_value = sample(seq(100000, 2000000, by = 500), 100000, TRUE), earned_exposure = runif(100000, 0.25, 1), earned_premium = runif(100000, 100, 2500) ) local_portfolio$reconstruction_value_1000 <- round(local_portfolio$reconstruction_value / 1000) * 1000 frequency <- with( local_portfolio, exp( -3 + 0.20 * (sector == "Industry") - 0.10 * (region == "North") + 0.0000001 * reconstruction_value_1000 ) ) local_portfolio$claim_count <- rpois( nrow(local_portfolio), lambda = local_portfolio$earned_exposure * frequency ) local_portfolio$claim_amount <- 0 has_claims <- local_portfolio$claim_count > 0 local_portfolio$claim_amount[has_claims] <- rgamma( sum(has_claims), shape = 2 * local_portfolio$claim_count[has_claims], scale = 3000 ) local_grid <- rating_grid( local_portfolio, group_by = c("sector", "region", "reconstruction_value_1000"), exposure = "earned_exposure", aggregate_cols = c("claim_count", "claim_amount", "earned_premium") ) data.frame( stage = c("Portfolio", "Rating grid"), rows = c(nrow(local_portfolio), nrow(local_grid)) ) head(local_grid) ``` The result has one row per observed combination of sector, region and rounded reconstruction value. Exposure, claims, loss and premium remain portfolio totals and can be used directly in pricing analyses. ## Aggregation and GLM estimation Model-point aggregation is not necessarily an approximation. For the Poisson frequency GLM used below, policy rows within a model point have identical model covariates and therefore the same linear predictor. Claim counts and earned exposure are additive, and exposure enters the model through `offset(log(earned_exposure))`. Under these conditions, summing claim counts and exposure by the complete set of model covariates preserves the coefficient estimates, apart from numerical tolerance. This statement assumes independent Poisson observations and no additional policy-level weights or model terms that vary within a model point. ```{r} grid_frequency_model <- glm( claim_count ~ sector + region + reconstruction_value_1000 + offset(log(earned_exposure)), family = poisson(link = "log"), data = local_grid ) policy_frequency_model <- glm( claim_count ~ sector + region + reconstruction_value_1000 + offset(log(earned_exposure)), family = poisson(link = "log"), data = local_portfolio ) all.equal( unname(coef(grid_frequency_model)), unname(coef(policy_frequency_model)), tolerance = 1e-8 ) ``` The comparison verifies the result for the response, covariates and exposure offset used in this example. It should not be generalised to every model family or data structure. Policy-level records remain necessary for individual predictions, record-level diagnostics, non-additive information, certain bootstrap procedures and models whose likelihood is not preserved by the selected grouping. Claim-level data also remains relevant for severity and large-loss diagnostics. ## Lazy model-point aggregation with DuckDB The database functions accept a lazy table created with `dplyr::tbl()`. They return another lazy table. Calling the function therefore constructs SQL but does not import the source portfolio. The next example is executed when the suggested database packages are available. It uses an in-memory DuckDB database, so the documentation build does not create a database file. The grouping remains lazy: `rating_grid_db()` constructs the query, and only the reduced grid is read back into R with `collect()`. ```{r duckdb-memory-workflow, eval = duckdb_available} library(DBI) library(dbplyr) library(dplyr) library(duckdb) con <- dbConnect(duckdb()) dbWriteTable( con, "portfolio", local_portfolio, overwrite = TRUE ) portfolio_db <- tbl(con, "portfolio") grid_db <- portfolio_db |> mutate( reconstruction_value_1000 = round(reconstruction_value / 1000) * 1000 ) |> rating_grid_db( group_by = c("sector", "region", "reconstruction_value_1000"), exposure = "earned_exposure", aggregate_cols = c("claim_count", "claim_amount", "earned_premium") ) # Inspect the SQL without collecting the row-level portfolio. sql_render(grid_db) database_row_counts <- data.frame( source_rows = portfolio_db |> summarise(n = n()) |> collect() |> pull(n), reduced_rows = grid_db |> summarise(n = n()) |> collect() |> pull(n) ) database_grid <- collect(grid_db) database_row_counts head(database_grid) dbDisconnect(con, shutdown = TRUE) ``` This example starts from an R object only to make the workflow reproducible inside the vignette. In production, the row-level portfolio will commonly already reside in a database or be read by DuckDB directly from Parquet files. In that case the large source table never needs to be materialised in R. For a persistent local database, supply a file path through `dbdir`. This file-backed variant is not executed during package checks: ```{r, eval = FALSE} con <- dbConnect(duckdb(), dbdir = "portfolio.duckdb") ``` DuckDB can also query files without first loading them into an R object. For example, a Parquet extract can be exposed as a database view: ```{r, eval = FALSE} library(DBI) library(dbplyr) library(dplyr) library(duckdb) con <- dbConnect(duckdb()) dbExecute(con, " CREATE VIEW portfolio AS SELECT * FROM read_parquet('portfolio/*.parquet') ") portfolio_db <- tbl(con, "portfolio") ``` ## Scaling the same workflow to 10 and 50 million rows The following DuckDB example generates ten million rows inside the database. The records are never materialised as an R data frame. The code is not executed during the vignette build because creating ten million rows on every package check would be disproportionate, but the block is complete and can be run as shown. ```{r, eval = FALSE} large_database_path <- "large_portfolio.duckdb" con <- dbConnect(duckdb(), dbdir = large_database_path) dbExecute(con, " CREATE TABLE portfolio_10m AS SELECT i AS policy_id, 'Sector ' || CAST(i % 20 AS VARCHAR) AS sector, 'Region ' || CAST(FLOOR(i / 20) % 10 AS VARCHAR) AS region, 100000 + (FLOOR(i / 200) % 100) * 1000 AS reconstruction_value, 0.5 + (i % 50) / 100.0 AS earned_exposure, CASE WHEN i % 17 = 0 THEN 1 + CAST(i % 3 AS INTEGER) ELSE 0 END AS claim_count, CASE WHEN i % 17 = 0 THEN 5000 + CAST(i % 50000 AS DOUBLE) ELSE 0 END AS claim_amount, 100 + (i % 2000) AS earned_premium FROM range(10000000) AS portfolio(i) ") portfolio_10m <- tbl(con, "portfolio_10m") grid_10m_db <- portfolio_10m |> mutate( reconstruction_value_1000 = round(reconstruction_value / 1000) * 1000 ) |> rating_grid_db( group_by = c("sector", "region", "reconstruction_value_1000"), exposure = "earned_exposure", aggregate_cols = c("claim_count", "claim_amount", "earned_premium") ) sql_render(grid_10m_db) row_comparison_10m <- data.frame( source_rows = portfolio_10m |> summarise(n = n()) |> collect() |> pull(n), reduced_rows = grid_10m_db |> summarise(n = n()) |> collect() |> pull(n) ) row_comparison_10m$reduction <- 1 - row_comparison_10m$reduced_rows / row_comparison_10m$source_rows row_comparison_10m grid_10m <- collect(grid_10m_db) ``` In this constructed portfolio, the three grouping variables define at most 20,000 combinations. Ten million source rows are therefore reduced to no more than 20,000 rating-grid rows before `collect()` is called: a reduction of 99.8%. The exact reduction in a real portfolio depends on the number of observed combinations. The same modelling variables and aggregation apply to 50 million rows. Only the range used to generate the database table changes: ```{r, eval = FALSE} dbExecute(con, " CREATE TABLE portfolio_50m AS SELECT i AS policy_id, 'Sector ' || CAST(i % 20 AS VARCHAR) AS sector, 'Region ' || CAST(FLOOR(i / 20) % 10 AS VARCHAR) AS region, 100000 + (FLOOR(i / 200) % 100) * 1000 AS reconstruction_value, 0.5 + (i % 50) / 100.0 AS earned_exposure, CASE WHEN i % 17 = 0 THEN 1 + CAST(i % 3 AS INTEGER) ELSE 0 END AS claim_count, CASE WHEN i % 17 = 0 THEN 5000 + CAST(i % 50000 AS DOUBLE) ELSE 0 END AS claim_amount, 100 + (i % 2000) AS earned_premium FROM range(50000000) AS portfolio(i) ") portfolio_50m <- tbl(con, "portfolio_50m") grid_50m_db <- portfolio_50m |> mutate( reconstruction_value_1000 = round(reconstruction_value / 1000) * 1000 ) |> rating_grid_db( group_by = c("sector", "region", "reconstruction_value_1000"), exposure = "earned_exposure", aggregate_cols = c("claim_count", "claim_amount", "earned_premium") ) row_comparison_50m <- data.frame( source_rows = portfolio_50m |> summarise(n = n()) |> collect() |> pull(n), reduced_rows = grid_50m_db |> summarise(n = n()) |> collect() |> pull(n) ) row_comparison_50m$reduction <- 1 - row_comparison_50m$reduced_rows / row_comparison_50m$source_rows row_comparison_50m grid_50m <- collect(grid_50m_db) ``` The 50 million source records again reduce to no more than 20,000 combinations in this example, a reduction of 99.96%. The large table remains in DuckDB; only the compact grid enters R. A file-backed DuckDB database can be used when the database itself should persist between sessions. ## Consolidating policy periods in DuckDB `merge_date_ranges_db()` applies the temporal gaps-and-islands calculation in DuckDB. It returns consolidated periods as a lazy query: ```{r, eval = FALSE} dbExecute(con, " CREATE TABLE portfolio_periods AS SELECT * FROM (VALUES ('P001', 'Fire', 'Industry', DATE '2025-01-01', DATE '2025-06-30', 0.5, 600.0), ('P001', 'Fire', 'Industry', DATE '2025-07-01', DATE '2025-12-31', 0.5, 650.0), ('P002', 'Fire', 'Retail', DATE '2025-01-01', DATE '2025-12-31', 1.0, 900.0) ) AS periods( policy_id, coverage, sector, period_start, period_end, earned_exposure, earned_premium ) ") periods_db <- tbl(con, "portfolio_periods") merged_periods_db <- merge_date_ranges_db( periods_db, period_start = "period_start", period_end = "period_end", group_by = c("policy_id", "coverage", "sector"), aggregate_cols = c("earned_exposure", "earned_premium"), merge_gap_days = 1 ) merged_periods <- collect(merged_periods_db) dbDisconnect(con, shutdown = TRUE) unlink(large_database_path) unlink(paste0(large_database_path, ".wal")) ``` This database variant is restricted to DuckDB because date arithmetic and window-function details differ between database systems. `rating_grid_db()` is based on standard grouped SQL and can be used with other `dbplyr` backends. ## Which reduction comes first? The two reductions answer different questions and do not always need to be combined. - Use `rating_grid_db()` directly when the source periods are already suitable for aggregation and the objective is a table of observed rating combinations. - Use `merge_date_ranges_db()` when renewals, endorsements or administrative splits first need to be consolidated into meaningful coverage periods. - When both are required, merge periods first and construct the rating grid second. A rating grid no longer contains the row-level interval structure needed to merge policy periods. The preferred sequence, where every step is relevant, is: `raw policy periods -> merge compatible periods -> derive or transform rating factors -> aggregate to model points -> collect the compact grid -> fit the model`. The temporal merge must precede model-point aggregation because start dates, end dates and adjacency between individual records are no longer represented after the grid has been constructed. Rating factors should normally be derived before constructing the grid so that every distinct model value forms its own model point. The grouping columns used for temporal merging must retain every attribute that should remain distinct. For example, include `sector` when a policy changes sector during its history. Aggregate premium or exposure only when the source amounts are additive; overlapping records that describe the same covered days should be resolved before summing. For a large workflow it can be useful to materialise an intermediate reduction inside DuckDB with `dplyr::compute()`. This avoids repeating an expensive merge while still keeping the intermediate table outside R. Call `collect()` only after checking that the reduced row count and columns fit the intended R analysis. ## Where to go next - [Getting Started](getting-started.html) uses a compact portfolio to follow the modelling workflow after data preparation. - [Pricing workflow and package building blocks](pricing-workflow-building-blocks.html) places portfolio reduction within the wider actuarial package map. - [Model validation](model-validation.html) shows how model points can support exposure-aware observed-versus-expected reviews after fitting. - The [reference index](https://mharinga.github.io/insurancerating/reference/index.html) documents the exact local and database-backed preparation functions.