Synthetic data¶

A synthetic data source serves generated rows that look like the rows of another data source. Use it to give developers and tests realistic data without real values.

Sluice reads the source once, builds a profile of each column, and writes generated rows to a snapshot file. Clients query the snapshot like any other catalog.

Example¶

Goal: let developers query customers and orders without access to the real rows.

The shop data source holds 400 customers and 2000 orders. shopsynth builds 20% of each table from the profile of shop:

apiVersion: sluice.bino.bi/v1alpha1
kind: DataSource
metadata:
  name: shopsynth
spec:
  type: synthetic
  synthetic:
    source: shop
    seed: secret://env/SHOP_SYNTH_SEED
    privacy:
      minGroupSize: 5
    tables:
      - name: main.orders
        size: { ratio: 0.2 }
        columns:
          id: { generator: sequence }
          customer_id:
            generator: foreignKey
            args: { table: main.customers, column: id }
          product: { generator: categorical }
          amount:
            generator: correlated
            args: { with: [quantity], decimals: 2 }
          quantity: { generator: correlated }
          placed_on:
            generator: dateDistribution
            args: { by: [product] }
          shipped_on:
            generator: dateOffset
            args: { from: placed_on }
      - name: main.customers
        size: { ratio: 0.2 }
        privacy:
          rejectExactMatches: [first_name, last_name]
        columns:
          id: { generator: sequence }
          first_name:
            generator: fake
            args: { kind: first_name, keepInitial: true }
          last_name:
            generator: fake
            args: { kind: last_name, keepInitial: true }
          email:
            generator: template
            args: { template: "{first_name}.{last_name}@example.org", lower: true }

• customer_id takes the keys of the synthetic customers. Each customer gets about as many orders as in the source.
• amount and quantity keep their rank correlation. See Correlated columns.
• placed_on follows the dates of the same product in the source.
• shipped_on is placed_on plus a gap like the source gaps.
• email is built from the synthetic names of the same row.
• No synthetic customer has the first and last name of a source customer.

The seed is a secret reference to at least 16 bytes. A DataClassification tags email as pii. A tagged column needs a column entry or a rule, so the template entry is required:

apiVersion: sluice.bino.bi/v1beta1
kind: DataClassification
metadata:
  name: shop-tags
spec:
  rules:
    - resources:
        catalogs: ["shop"]
        tables: ["customers"]
        columns: ["email"]
      tags: ["pii"]

The server config names the folder of the snapshots:

synthetic:
  dir: "./data/synthetic"
  maxRatio: 0.2

Build the snapshot, then start the server:

sluice synth build shopsynth --config server.yaml
sluice synth build shopnoisy --config server.yaml

An access policy allows the group developers to read shopsynth. Another policy denies shop. A developer counts the orders per product:

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_dana.world" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT product, count(*) AS orders FROM shopsynth.main.orders GROUP BY product ORDER BY product"}'

{
  "query_id": "<query-id>",
  "columns": ["product", "orders"],
  "rows": [
    ["coffee", 180],
    ["mug", 63],
    ["tea", 157]
  ],
  "row_count": 3,
  "truncated": false
}

The same query on shop is denied:

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_dana.world" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT product, count(*) AS orders FROM shop.main.orders GROUP BY product ORDER BY product"}'

{
  "code": "ACL_DENIED",
  "message": "access denied",
  "query_id": "<query-id>",
  "policy": "deny-shop"
}

What happened: the build profiled shop.main.customers and shop.main.orders and wrote 80 customers and 400 orders. The product gift card has 2 source rows, below minGroupSize, so it never appears. The shares of the other products stay close to the source.

Fields¶

The policy schema lists every field of synthetic. A table entry needs a name as schema.table and a size: either ratio or rows.

Each column gets one generator. Sluice takes the first match in this order:

1. The entry of the column under tables[].columns.
2. The first matching rule under tables[].rules.
3. The first matching rule under synthetic.rules.
4. The default for the column type.

A rule matches by columns (name patterns), types (type patterns such as DECIMAL*) and tags. All fields that are set must match.

A default column with fewer than minGroupSize non-null rows is written as NULL, with a report warning. A column entry for a column that the source table does not have fails the build.

Generators¶

source means "from the profile of the source column". Every generator also takes nullRate and unique:

• nullRate: source (default) or a share from 0 to 1. For template and expression the default is 0.
• unique: true: no value repeats in the column. The build fails when the generator runs out of values. template, expression, foreignKey and correlated do not take it.

Number types are the integer types, DECIMAL, FLOAT and DOUBLE. Text is VARCHAR.

• distribution, categorical and dateDistribution also take by. See Groups with by.
• length and words take source, a number, or {min, max}.
• fanOut takes source or {min, max}: the number of child rows per parent, from 0 to 100000.
• orphanRate takes source or a share from 0 to 1. It is the share of child rows with a key that has no parent.
• offset takes source or {min, max} in days, from -36500 to 36500.
• In a template, {name} inserts a column of the row. Write {{ and }} for a brace. lower: true writes the result in lower case.
• sourceVocabulary keeps a word only when it occurs minWordCount times and in minGroupSize rows. A word is a run of letters, so digits never appear. maxWords is the size of the word list.
• sentenceShape.vocabulary is one of lorem, en, de, source. minWordCount and maxWords work only with source.
• fake.kind is one of name, first_name, last_name, email, phone, company, city, country, street, iban, uuid.
• granularity is one of second, minute, hour, day, month, year. A DATE column takes only day, month and year.
• Quote the keys "Y" and "N" in weighted.values. YAML reads them as booleans otherwise.
• A generator that does not fit the column type fails the build before Sluice profiles the column.

Ages¶

age keeps the age mix of a birth date column. Sluice counts the source rows per age in whole years at the date asOf. For each row it draws an age with these shares and writes a date in that year of life before asOf. For example: birth_date: { generator: age, args: { asOf: "2024-06-30" } }.

• asOf is an ISO date or timestamp. Without it, asOf is the upper bound of the column after clipOutliers and extremeJitter. Set it to the date of the source data, so the ages mean something.
• An age with fewer than minGroupSize rows never appears. Its rows are left out of the shares.
• clipOutliers cuts the dates before Sluice counts the ages. A spike at the lowest or highest date gives a warning.
• A date after asOf counts as age 0. An age above 150 counts as 150.
• granularity cuts the written date. The cut date keeps the drawn age.
• With noise and bounds on the column, asOf defaults to bounds.max, and dates outside the bounds count as the nearest bound.

Times of day¶

timeOfDay keeps the times of day of a column. Sluice counts the source rows per 15 minutes of the day. Each row gets a slot with these shares and a second in that slot.

• A slot with fewer than minGroupSize rows never appears. Its rows are left out of the shares.
• On a TIMESTAMP column the date comes from the source dates, as with dateDistribution and granularity: day. Then the time of day is added. A value past the range on the first or last day moves one day into the range.
• On a TIME column the times have whole seconds.

Correlated columns¶

correlated keeps the rank correlation between number columns of one table. In the example source, a larger amount comes with a larger quantity:

amount:
  generator: correlated
  args: { with: [quantity], decimals: 2 }
quantity: { generator: correlated }

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_dana.world" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT round(corr(amount, quantity), 1) AS correlation FROM shopsynth.main.orders"}'

{
  "query_id": "<query-id>",
  "columns": ["correlation"],
  "rows": [
    [0.9]
  ],
  "row_count": 1,
  "truncated": false
}

What happened: each column got its own values, as with distribution. The group only decided which values meet in one row.

• with names 1 to 7 other correlated columns of the same table entry. One side is enough: quantity needs no with.
• Columns that with links form a group of 2 to 8 columns.
• Sluice measures the rank correlation (Spearman) of each pair from a table of up to 20 by 20 cells.
• A pair needs 2 x minGroupSize rows where both values are set. Fewer rows fail the build.
• A pair without spread, such as a constant column, gets the correlation 0 and a report warning.
• When the measured values do not fit together, Sluice moves them toward 0 until they do. The report shows this share as shrinkage.
• correlated is set only under tables[].columns, not in a rule. DATE and TIMESTAMP columns are refused.
• correlated does not take unique, and rejectExactMatches cannot name it. Sluice cannot draw one row of a group again.

Privacy settings¶

Set privacy on the data source, on a table, or on a column entry. The most specific level wins.

• Columns with fewer than minGroupSize non-null rows fail the build, unless the generator needs no profile. Examples: constant, sequence, range, weighted, regex, uuid, redact, null.
• A spike goes to the build log, the report and the metric sluice_synthetic_spikes. The build does not fail. Set clipOutliers to remove it.
• strictTags is set only on the data source. Tags come from DataClassification objects on the source catalog.
• rejectExactMatches is set only on a table. It takes 1 to 32 column names.
• noise is set only on the data source. bounds is set only on a column entry.

rejectExactMatches compares the listed columns of each synthetic row with every source row:

• Values compare in the snapshot types. NULL equals NULL.
• The build fails when more than 1% of the rows match. Then the columns are too predictable together.
• Otherwise Sluice draws the listed columns of the matching rows again, and the columns that read them. It checks again, up to 10 rounds. Rows that still match fail the build.
• Source rows stay inside DuckDB. Errors and the report hold only counts and column names.

Noise¶

noise adds random noise (Laplace noise) to the counts that Sluice reads from the source. One source row then changes the snapshot only a little. A smaller epsilon means more noise.

shopnoisy builds the orders of the example with noise. amount gets public bounds:

apiVersion: sluice.bino.bi/v1alpha1
kind: DataSource
metadata:
  name: shopnoisy
spec:
  type: synthetic
  synthetic:
    source: shop
    seed: secret://env/SHOP_SYNTH_SEED
    privacy:
      minGroupSize: 5
      noise: { epsilon: 10 }
    tables:
      - name: main.orders
        size: { ratio: 0.2 }
        columns:
          product: { generator: categorical }
          amount:
            generator: histogram
            args: { bins: 20, decimals: 2 }
            privacy:
              bounds: { min: 0, max: 260 }

sluice synth report shopnoisy --config server.yaml

build <build-id> succeeded, origin cli, finished <time>
noise: epsilon 10, 22 measurements
TABLE        COLUMN       GENERATOR         SOURCE MEDIAN  SYNTHETIC MEDIAN  DISTANCE   WARNINGS
main.orders  id           distribution      1000.5         1029              ks 0.0701  1
main.orders  customer_id  distribution      189.5          151               ks 0.023   1
main.orders  region       categorical       -              -                 tv 0.0222  0
main.orders  product      categorical       -              -                 tv 0.0213  0
main.orders  amount       histogram         67.375         71.99             -          0
main.orders  quantity     categorical       5.5            5                 tv 0.0649  0
main.orders  placed_on    dateDistribution  20602.5        20591             ks 0.0323  1
main.orders  shipped_on   dateDistribution  20607.5        20611.5           ks 0.0292  1
warning: main.orders.id: range is not covered by noise, set privacy.bounds
warning: main.orders.customer_id: range is not covered by noise, set privacy.bounds
warning: main.orders.placed_on: range is not covered by noise, set privacy.bounds
warning: main.orders.shipped_on: range is not covered by noise, set privacy.bounds

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_dana.world" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT round(min(amount)) AS min_amount, round(max(amount)) AS max_amount FROM shopnoisy.main.orders"}'

{
  "query_id": "<query-id>",
  "columns": ["min_amount", "max_amount"],
  "rows": [
    [0, 259]
  ],
  "row_count": 1,
  "truncated": false
}

What happened: the build made 22 noisy measurements, so each got an epsilon of 10 / 22. The range of amount comes from its bounds and the kept bins. The other number and date columns warn that their range has no noise.

Budget¶

• epsilon is the budget of one build of the whole data source. Sluice splits it evenly over all noisy measurements of the build.
• A measurement is one released set of counts. Examples: the rows of a table, the nulls of a column, one histogram, one correlated pair.
• A rebuild over changed data spends epsilon again. Nothing adds up the budget over builds.
• Sluice counts the measurements before it reads data. A default with a fallback counts every generator it may fall back to.
• foreignKey with fanOut: source or orphanRate: source, dateOffset, sourceVocabulary, sentenceShape and by are refused with noise.

Counts¶

• Each count is the true count plus noise, rounded, and at least 0.
• A histogram cell is kept when it reaches minGroupSize and a floor that grows with the noise. A cell is a bin, a weekday, an age or a time slot.
• A value, initial, pattern or prefix is kept when its noisy count reaches minGroupSize.
• Quantiles, mean and deviation come from a noisy histogram of 200 bins, or of bins for histogram.
• The same data, seed, spec and DuckDB version give the same rows. Any change in the source changes the noise of every count.

Min and max¶

• With bounds, Sluice cuts the values to the bounds and splits that range into equal bins. The output range is the outer edges of the kept bins.
• A lone far value cannot keep its bin, so it does not show in the range.
• Set bounds close to the real range. A bin is (max - min) / 200 wide, so wide bounds put all rows in a few bins.
• On small tables use histogram with fewer bins. The example has 2000 rows and needs epsilon: 10 for usable numbers.
• Without bounds, the range comes from the profile, as without noise. The report marks the column range: profile and warns.
• bounds takes numbers for number columns and seconds of the day for TIME. For DATE and TIMESTAMP columns it takes ISO dates or timestamps.
• bounds on a generator that reads no numbers, such as categorical, fails the build.
• A very small epsilon can fail the build for a number column. It can also give the column a narrow random range.

Not covered by noise¶

Do not call a snapshot differentially private without this list. Noise does not cover:

• Which values, initials, patterns and prefixes appear. minGroupSize protects them, as without noise.
• The range of number and date columns without bounds, and spike warnings without bounds.
• The report and the audit record. They hold exact counts of the source.
• Whether the source changed between two builds.
• The rows that rejectExactMatches compares with the source.
• Attacks on the rounding of floating-point numbers in the noise.
• A known seed. Anyone with the seed and the spec can remove the noise.

Relations¶

foreignKey, dateOffset, template, expression and by read other columns. Set them only under tables[].columns. A rule matches columns in many tables, so a column name in its args could point to a different column in each.

Every synthetic order of the example points to a synthetic customer. Count the orders per city through a join:

curl -s http://localhost:8080/v1/query \
  -H "X-Api-Key: sl_demo_dana.world" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT c.city, count(*) AS orders FROM shopsynth.main.orders o LEFT JOIN shopsynth.main.customers c ON c.id = o.customer_id GROUP BY c.city ORDER BY c.city NULLS LAST"}'

{
  "query_id": "<query-id>",
  "columns": ["city", "orders"],
  "rows": [
    ["Bremen", 97],
    ["Essen", 72],
    ["Hamburg", 105],
    ["Kiel", 126]
  ],
  "row_count": 4,
  "truncated": false
}

What happened: all 400 orders find their customer, so no row has the city NULL.

Order¶

• Sluice builds a parent table before the tables that point to it. Other tables keep the list order. The report lists the tables in build order.
• Inside a row, Sluice draws a column after the columns it reads.
• A foreignKey to its own table is refused. Tables or columns that depend on each other in a cycle are refused.
• sluice config validate finds these errors. A column name that the source table does not have fails the build.

Foreign keys¶

• The parent column needs unique values: the generator sequence or uuid, or unique: true.
• Parent and child column hold the same kind of key: both integers, or both text or UUID.
• fanOut: source keeps the fan-out of the source: the number of child rows per parent. A fan-out that fewer than minGroupSize parents share is merged into a near one.
• When the table sizes do not fit the source fan-out, Sluice scales it. The report warns when the scale is below 0.5 or above 2.
• Orphan keys are new values, not source keys.
• After the write, Sluice checks that every key except the orphans finds its parent. The snapshot has no FOREIGN KEY constraint.
• Sluice keeps the parent keys in memory during the build, about 50 bytes per parent row.

Groups with by¶

by names 1 to 3 columns. The generator then uses the profile of the rows that have the same values in these columns. The example uses the dates of the same product.

• A by column is text, ENUM, BOOLEAN, DATE or an integer type up to BIGINT.
• Values compare as text. The by column needs a generator that keeps source labels: categorical, uniformCategory, shuffleLabels, weighted, boolean or constant. Other generators are refused, also when a default falls back to one. The report warns when most rows find no group.
• minGroupSize counts rows, not people. Do not group by a column that holds one value per person, such as a customer id.
• A group needs minGroupSize rows. Sluice uses the 100 largest groups. Other rows use the profile of the whole column.
• NULL is a group value of its own.
• The spike check runs per group. The report counts groups with a spike, but names none.

Expressions¶

expression computes a column with a DuckDB SQL expression over other columns of the same row, for example quantity * unit_price. DuckDB computes it when Sluice writes the snapshot, in the snapshot types.

• Allowed: constants, columns, operators, CASE, CAST and scalar functions that give the same result each time.
• Refused: subqueries, table names, aggregates, window functions, table functions, settings, and functions such as random() or now().
• An expression cannot read another expression column. template, dateOffset, by and rejectExactMatches cannot name one either.
• sql holds at most 1024 bytes, without ;, comments or line breaks. sluice config validate checks only this. The full check runs at build start, before Sluice reads the source.

A template is NULL when a column it reads is NULL. A dateOffset is NULL when from is NULL. With offset: source, the gap keeps its sign: when no source gap is negative, no synthetic gap is either.

Profile on a sample¶

By default a build reads the full source table, once for each profile query. For a large table, set profile.sampleRows on the table, such as profile: { sampleRows: 1000000 }.

• When the table has more rows than sampleRows, Sluice reads it once and keeps a sample of about sampleRows rows in the builder. Every profile query and the source side of the report then read the sample.
• Sluice keeps each row by a hash of its values, its copy number among equal rows and the seed. So the sample size varies a little around sampleRows.
• A table with at most sampleRows rows is read in full. Without noise the build is the same as without the setting.
• sampleRows is 1000 to 100 000 000.
• The sample follows from the seed and the row values, not from the row order. The same seed and data give the same sample, also from Postgres or MySQL. The DuckDB version must be the same.
• These steps still read the full table: the row count for size and synthetic.maxRatio, rejectExactMatches, and the source fingerprint of noise.
• minGroupSize counts the rows in the sample, so rare values drop out sooner. Sluice does not scale the counts up. A scaled count would let a value of one person pass.
• foreignKey with fanOut: source or orphanRate: source fails on a sampled table, because each fan-out count would shrink. Set fanOut: { min, max } and a fixed orphanRate instead.
• With noise the full row count is one more noisy measurement, also when Sluice does not sample. The noisy count decides whether Sluice samples.
• The sample lives in the memory of the builder. A million rows of 20 columns take a few hundred MB. To number equal rows, DuckDB groups the full table once, and it may spill to disk for that.
• The report shows tables[].profiled_rows and a warning for each sampled table.

Build and publish¶

Three things start a build:

• sluice synth build <name> reads the server config and the policy directory. It needs no running server. See the CLI reference.
• POST /admin/synthetic/<name>/build on the admin API builds in the running server.
• build.onStart: ifMissing builds at server start when no snapshot exists. The server serves queries during the build.

The size check runs before the profile. A ratio above synthetic.maxRatio of the server config fails the build, and so does a rows count above that share.

A build writes into synthetic.dir:

Sluice changes current only after every check passed. A failed build writes its report and leaves current as it was. Sluice keeps the current and the previous snapshot and deletes older ones. Clients can read only the snapshots folder.

The same spec, seed and source data give the same rows on the same CPU architecture. With noise, the DuckDB version must be the same too. The report holds a content_hash of the rows to compare builds.

Status¶

GET /admin/datasources shows a status for each synthetic data source:

curl -s http://localhost:9091/admin/datasources \
  -H "Authorization: Bearer demo-admin-token-change-me"

{
  "datasources": [
    {
      "name": "shop",
      "type": "sqlite",
      "healthy": true,
      "last_check": "<time>",
      "schema_pulled_at": "<time>"
    },
    {
      "name": "shopnoisy",
      "type": "synthetic",
      "healthy": true,
      "last_check": "<time>",
      "schema_pulled_at": "<time>",
      "status": "ready"
    },
    {
      "name": "shopsynth",
      "type": "synthetic",
      "healthy": true,
      "last_check": "<time>",
      "schema_pulled_at": "<time>",
      "status": "ready"
    }
  ]
}

Report¶

sluice synth report prints the report of the current build. --json prints the whole file, and GET /admin/synthetic/<name> returns it too.

sluice synth report shopsynth --config server.yaml

build <build-id> succeeded, origin cli, finished <time>
TABLE           COLUMN       GENERATOR         SOURCE MEDIAN  SYNTHETIC MEDIAN  DISTANCE   WARNINGS
main.customers  id           sequence          200.5          40.5              -          0
main.customers  first_name   fake              -              -                 -          0
main.customers  last_name    fake              -              -                 -          0
main.customers  city         categorical       -              -                 tv 0.0625  0
main.customers  email        template          -              -                 -          0
main.orders     id           sequence          1000.5         200.5             -          0
main.orders     customer_id  foreignKey        189.5          39.5              tv 0.198   0
main.orders     region       categorical       -              -                 tv 0.02    0
main.orders     product      categorical       -              -                 tv 0.05    0
main.orders     amount       correlated        67.375         67.905            ks 0.0636  0
main.orders     quantity     correlated        5.5            7                 ks 0.12    0
main.orders     placed_on    dateDistribution  20602.5        20601             ks 0.0567  0
main.orders     shipped_on   dateOffset        20607.5        20606             ks 0.11    0
correlated main.orders: amount, quantity max_diff 0.028 shrinkage 0.00

• DISTANCE compares source and synthetic data. ks is the largest gap between the two distributions. tv is the share of rows that would need another value.
• For foreignKey, tv compares the fan-outs. For dateOffset, ks compares the gaps to from.
• Medians of dates are in days since 1970-01-01. Medians of timestamps are in seconds.
• A spike in a by group gives one warning line with the number of such groups.
• sluice synth report prints a noise line with the budget, and a correlated line for each group.
• The source min and max in the JSON are not the real extremes. They hold the minGroupSize-th value from each end, or the clipOutliers bounds.
• The report holds no source rows, but it holds counts and statistics of the source. Keep it private.

The JSON has these fields for relations, correlated columns and noise. Each one is left out when it does not apply.

sluice synth build also prints one line for each rejectExactMatches table and each foreignKey column.

Limits¶

• Without noise the settings give no formal privacy guarantee. With noise, epsilon covers the counts listed in Noise, not which labels appear and not ranges without bounds.
• rejectExactMatches stops exact copies, not near copies. sourceVocabulary keeps a name that many rows hold. by uses at most 100 groups.
• sluice synth build writes no record to the audit log. It prints the record on stderr and stores it in the report.
• A running server picks up a snapshot from sluice synth build only after a restart. Each build keeps only the current and the previous snapshot, so two CLI builds delete the file a running server serves. Use the admin API to build in the server.
• Sluice attaches data sources only at start. Restart it after you add a synthetic data source or change its spec. The seed value is not part of the stale check, so build again after you change it.

See also¶

• Data sources
• Classification tags
• Policy schema
• Admin API