
Synthetic data from a profile
Richard Aubrey White
Source:vignettes/synthetic-data.Rmd
synthetic-data.Rmdcsdb makes synthetic data for one table in two steps. A profile
summarises the real table with aggregate SQL, under disclosure control.
A simulator then draws new rows from the profile. A
SyntheticTable holds the contract of the table and a
catalogue of fixed datasets, so that a test can name the data it
uses.
This vignette works at the level of one table. Two other vignettes build on it:
- cs9,
vignette("synthetic-data", package = "cs9"): synthetic tables, synthetic mode and switchable sources in a surveillance system. - norsyss.cs9,
vignettes/synthetic-development.Rmdin the private repositoryniphr/norsyss.cs9: one late task of a real pipeline, run on synthetic inputs alone.
Every chunk here runs on SQLite.
A table to profile
The table holds 600 made-up consultations. Each row is one visit of one person.
library(csdb)
#> csdb 2026.11.17
#> https://niphr.github.io/csdb/
library(data.table)
#>
#> Attaching package: 'data.table'
#> The following object is masked from 'package:base':
#>
#> %notin%
set.seed(1)
visits <- data.table(
id = sample(1:150, 600, replace = TRUE),
date = as.Date("2024-01-01") + sample(0:120, 600, replace = TRUE),
sex = sample(c("female", "male"), 600, replace = TRUE),
age = sample(0:95, 600, replace = TRUE),
consultations = rpois(600, 3),
municipality = sample(sprintf("m%03d", 1:40), 600, replace = TRUE)
)
visits[1:3, date := as.Date("2024-09-30")]
tab <- DBTable_v9$new(
dbconfig = list(driver = "SQLite", db = tempfile(fileext = ".sqlite")),
table_name = "visits",
field_types = c(
id = "INTEGER",
date = "DATE",
sex = "TEXT",
age = "INTEGER",
consultations = "INTEGER",
municipality = "TEXT"
),
keys = NULL,
validator_field_types = validator_field_types_blank,
validator_field_contents = validator_field_contents_blank
)
invisible(tab$create_table())
#> Creating table visits
tab$insert_data(visits)Three rows move to 2024-09-30, a week with no other visit. Disclosure control has to deal with them below.
Sensitivity levels
Each column gets one of four sensitivity levels. A column that
columns does not name is schema_only.
| Level | What the profile publishes |
|---|---|
full |
the values and their counts; above max_levels, bins for
a number and n_distinct for text |
coarsen |
a date by ISO week, a number by bins |
schema_only |
the type and the share of missing values |
exclude |
nothing beyond the field type |
profile_table() reads the query, the field types and the
keys from the table. profile_query() does the same for any
SQL query.
p <- profile_table(
tab,
columns = c(
id = "exclude",
date = "full",
sex = "full",
age = "coarsen",
consultations = "full"
),
k = 5,
max_levels = 10,
group_sizes = list(rows_per_id = "id")
)
p$columns$sex
#> $type
#> [1] "TEXT"
#>
#> $sensitivity
#> [1] "full"
#>
#> $missing_share
#> [1] 0
#>
#> $levels
#> [1] "female" "male"
#>
#> $n
#> [1] 290 310
p$columns$municipality
#> $type
#> [1] "TEXT"
#>
#> $sensitivity
#> [1] "schema_only"
#>
#> $missing_share
#> [1] 0municipality is schema_only, because
columns does not name it. The profile holds no value of
it.
Suppression by merge
The profile never writes a count below k. On an ordered
dimension, a cell below k merges with its neighbours
instead of being removed:
- Days merge into ISO weeks, then calendar months, then calendar
quarters. The column takes the finest resolution at which every cell
holds
k. - Bins of a number merge into runs of adjacent bins. A merged bin keeps the outer breaks of its run.
- Group sizes merge into ranges of row counts. No exact smallest or largest row count is published.
The three late visits are the only visits in their week, their month
and their quarter. Each of those cells holds 3, below
k = 5. So date comes out by quarter, and the
third quarter joins the second in one run:
names(p$columns$date)
#> [1] "type" "sensitivity" "missing_share" "quarters"
#> [5] "quarters_to" "n"
data.table(
from = p$columns$date$quarters,
to = p$columns$date$quarters_to,
n = p$columns$date$n
)
#> from to n
#> <Date> <Date> <int>
#> 1: 2024-01-01 2024-01-01 456
#> 2: 2024-04-01 2024-07-01 144consultations has more than max_levels
distinct values, so it is published as bins. A number column also
records nonnegative, which is TRUE when its
minimum is at least 0. The simulator then draws no value below 0.
p$columns$consultations$breaks
#> [1] -0.5 0.5 1.5 2.5 3.5 4.5 5.5 6.5 10.5
p$columns$consultations$nonnegative
#> [1] TRUE
p$group_sizes$rows_per_id
#> $column
#> [1] "id"
#>
#> $size_from
#> [1] 1 3 4 5 6 7 8
#>
#> $size_to
#> [1] 2 3 4 5 6 7
#>
#> $n
#> [1] 33 23 30 27 20 7 6An unordered cell, such as a level of a TEXT column,
cannot merge. A cell below k is removed, and the removed
counts are summed into suppressed_n when that sum is at
least k.
The profile refuses a torn read
profile_query() reads the data with several aggregate
queries, in one read snapshot where the driver gives one. It then checks
that the counts of each column, plus its missing values, add up to
n_rows. A profile that fails the check is torn, and the
function refuses it.
A concurrent write without a snapshot tears a profile. This chunk
simulates one. It mocks the internal reader of the missing counts, so
that 10 rows are inserted through the same connection just after that
read. Every later read then sees 610 rows, and n_rows is
600:
con <- tab$dbconnection$autoconnection
real_read <- csdb:::sql_missing_counts
torn <- testthat::with_mocked_bindings(
sql_missing_counts = function(con, sql, columns) {
out <- real_read(con, sql, columns)
DBI::dbExecute(con, "INSERT INTO visits SELECT * FROM visits LIMIT 10")
return(out)
},
tryCatch(
profile_query(
con,
"SELECT * FROM visits",
columns = c(sex = "full"),
field_types = c(sex = "TEXT")
),
error = function(e) conditionMessage(e)
),
.package = "csdb"
)
stopifnot(grepl("Torn profile", torn))
cat(torn, "
")
#> Torn profile: the counts of column sex sum to 610 (610 in cells and 0 missing), but n_rows is 600. The data changed while profile_query() read it.
DBI::dbGetQuery(con, "SELECT COUNT(*) AS n FROM visits")$n
#> [1] 600The function ends its transaction with a rollback, so the 10 inserted rows are gone again.
Write and read the profile
A profile is a YAML file. read_profile() gives back an
identical object.
f <- write_profile(p, tempfile(fileext = ".yaml"))
cat(readLines(f)[1:16], sep = "\n")
#> provenance:
#> format: 1
#> source: ~
#> query: |-
#> SELECT *
#> FROM `visits`
#> driver: SQLiteConnection
#> profiled_at: '2026-11-17T14:35:24Z'
#> k: 5
#> noise: ~
#> max_levels: 10
#> csdb_version: 2026.11.17
#> field_types:
#> id: INTEGER
#> date: DATE
#> sex: TEXT
identical(read_profile(f), p)
#> [1] TRUEThe provenance names the query, the driver, k,
noise, max_levels and the csdb version. It
holds no user name.
What a profile may contain
A profile of real data is a release of aggregates. It MUST have the approval of the data owner before it leaves the restricted environment. That holds for a profile in a package directory, in a test fixture and in a git repository.
Suppression keeps every published count at k or above.
It does not decide whether the set of aggregates is safe to publish.
That decision is the data owner’s. With
noise = list(epsilon = e), the profile adds Laplace noise
of scale 1 / e to each count before suppression.
Simulate and compare
simulate_from_profile() draws new rows from the
published summaries. It reads the profile only, never the profiled
rows.
sim <- simulate_from_profile(p, n = 600, seed = 1)
head(sim)
#> id date sex age consultations municipality
#> <int> <Date> <char> <int> <int> <char>
#> 1: 1 2024-03-20 female 62 3 municipality_3
#> 2: 1 2024-03-02 female 20 4 municipality_10
#> 3: 2 2024-02-17 female 30 6 municipality_3
#> 4: 3 2024-02-20 male 50 5 municipality_7
#> 5: 3 2024-01-23 male 79 1 municipality_9
#> 6: 3 2024-04-02 male 34 4 municipality_9profile_compare() checks how far the simulation is from
the real profile. Profile the simulation with the same
columns, k and max_levels
first:
s <- profile_data(
sim,
field_types = p$field_types,
columns = c(
id = "exclude",
date = "full",
sex = "full",
age = "coarsen",
consultations = "full"
),
k = 5,
max_levels = 10,
group_sizes = list(rows_per_id = "id")
)
profile_compare(p, s)
#> column property distance threshold pass
#> <char> <char> <num> <num> <lgcl>
#> 1: date missing_share 0.000000000 0.05000000 TRUE
#> 2: date quarters 0.010000000 0.08666667 TRUE
#> 3: sex missing_share 0.000000000 0.05000000 TRUE
#> 4: sex levels 0.003333333 0.09166667 TRUE
#> 5: age missing_share 0.000000000 0.05000000 TRUE
#> 6: age bins 0.053333333 0.14008333 TRUE
#> 7: consultations missing_share 0.000000000 0.05000000 TRUE
#> 8: consultations bins 0.063333333 0.10833333 TRUE
#> 9: municipality missing_share 0.000000000 0.05000000 TRUE
#> 10: id group_sizes 0.067543246 0.17045045 TRUEEach row has its own rule for the threshold:
- A table of counts, such as
levels,quarters,binsorgroup_sizes: the function drawsn_bootmultinomial tables of the synthetic size from the real shares. It merges each draw with thekofreal. The threshold is the 95th percentile of their distances, plusmargin. -
missing_share:n_bootbinomial draws ofsynth$n_rowsrows at the real share. The threshold is the 95th percentile of their distances, plusmargin. A share of"suppressed"on either side gives no row. -
n_distinct: the distance is 0 inside the range thatsynth$n_rowsuniform draws from the real values can reach. The threshold ismargin. -
dependency: the distance is 0 when both profiles agree onholds, otherwise 1. The threshold is 0. - A summary of
realthatsynthlacks, or holds in a form that does not align: distance 1, thresholdmargin.
For a table of counts, a smaller simulation gets a wider threshold.
No draw models the noise that noise adds to a profile.
A SyntheticTable
A SyntheticTable holds the contract of one table: its
field types, its keys, validator_field_contents and named
invariants. Every data.table that it returns passed
$validate().
An invariant is a function(d) or a
function(d, world). It returns TRUE, or a
message that says why d fails.
dir <- tempfile("visits-")
dir.create(dir)
write_profile(p, file.path(dir, "profile.yaml"))
ft <- p$field_types
invariants <- list(
age_nonnegative = function(d) {
if (all(d$age >= 0, na.rm = TRUE)) TRUE else "age is negative."
}
)
st <- synthetic_table_from_dir(
dir,
name = "visits",
field_types = ft,
invariants = invariants
)
print(st$random(n = 5, seed = 3))
#> id date sex age consultations municipality
#> <int> <Date> <char> <int> <int> <char>
#> 1: 1 2024-02-15 male 77 2 municipality_10
#> 2: 1 2024-02-18 female 89 3 municipality_7
#> 3: 1 2024-02-20 female 87 4 municipality_3
#> 4: 1 2024-03-19 male 33 2 municipality_5
#> 5: 2 2024-03-16 male 56 2 municipality_7$random() with seed = NULL draws a seed and
reports it in a message, so a failure can be replayed.
Named datasets
A test SHOULD name the dataset it uses. $add_dataset()
stores a recipe in datasets.yaml. A recipe holds
n, seed, overrides, the md5 of
the world as world_md5, the row count as rows,
and the md5 of the data. The name default is the generator
output at seed = 1 and n = 1000. A generator
that chooses its own size uses n = NULL there.
world <- list(sex = c("female", "male"))
st$add_dataset(
"default",
n = 1000,
seed = 1,
description = "The generator output at seed 1.",
world = world
)
st$add_dataset(
"all_female",
n = 200,
seed = 2,
description = "Every row is female.",
overrides = list(sex = "female"),
world = world
)
cat(readLines(file.path(dir, "datasets.yaml")), sep = "\n")
#> - name: default
#> kind: recipe
#> 'n': 1000
#> seed: 1
#> description: The generator output at seed 1.
#> overrides: []
#> world_md5: 33e42b7138d2f5c7ae76e6e5ff72ff42
#> rows: 1000
#> md5: 9fd7b6d59c58c5bbe02b75bb22dc347b
#> - name: all_female
#> kind: recipe
#> 'n': 200
#> seed: 2
#> description: Every row is female.
#> overrides:
#> sex: female
#> world_md5: 33e42b7138d2f5c7ae76e6e5ff72ff42
#> rows: 200
#> md5: 03a2fe75d8d608e93a7abeaa73426476A handmade dataset is a CSV file in handmade/. Each
column is read as text and converted to its field type. An empty cell is
NA.
dir.create(file.path(dir, "handmade"))
writeLines(
c(
"id,date,sex,age,consultations,municipality",
"1,2024-01-01,female,40,2,m001",
"2,2024-01-02,male,,1,m002"
),
file.path(dir, "handmade", "two_visits.csv")
)
st$list_datasets()
#> name kind n seed description
#> <char> <char> <int> <int> <char>
#> 1: default recipe 1000 1 The generator output at seed 1.
#> 2: all_female recipe 200 2 Every row is female.
#> 3: two_visits handmade NA NA <NA>
print(st$dataset("two_visits"))
#> id date sex age consultations municipality
#> <int> <Date> <char> <int> <int> <char>
#> 1: 1 2024-01-01 female 40 2 m001
#> 2: 2 2024-01-02 male NA 1 m002$dataset() regenerates a recipe. When the md5 of the
world equals world_md5, it checks the row count against
rows and the md5 against md5:
nrow(st$dataset("all_female", world = world))
#> [1] 200
st$check_hashes(world = world)
#> name pass
#> <char> <lgcl>
#> 1: default TRUE
#> 2: all_female TRUEThe hash check after a generator change
A change of the generator changes the data of every recipe. The hash check then stops, and the error names the table and the dataset. Here a new generator draws 1 to 10 consultations in place of the profile:
changed <- function(profile, n, seed, world) {
d <- simulate_from_profile(profile, n = n, seed = seed, world = world)
d[, consultations := sample(1:10, .N, replace = TRUE)]
return(d)
}
st2 <- synthetic_table_from_dir(
dir,
name = "visits",
field_types = ft,
invariants = invariants,
generator = changed
)
st2$dataset("all_female", world = world)
#> Error:
#> ! SyntheticTable visits, dataset 'all_female': the regenerated data does not match the md5 in datasets.yaml. The generator, the profile or the overrides changed.After a deliberate change, delete the recipe from
datasets.yaml and add it again with
$add_dataset(). A test that reads the dataset then sees the
new data.
For a world other than the recipe’s,
$dataset() validates the data, does not check the md5, and
says so in a message.