DuckDB lets you quickly generate synthetic datasets by combining a few building blocks: functions and clauses that generate rows, functions that generate values to fill those rows, and extensions that generate whole benchmark datasets. This page shows each building block with a runnable example.
Generating Rows
- range functions
- hash functions, e.g.,
hash,md5,sha256 - the Faker Python package via the Python function API
- using cross products (Cartesian products)
range and generate_series
The range functions turn a start, stop, and step into a table of rows, which is the usual starting point for a synthetic dataset:
SELECT i FROM range(1, 4) t(i);
┌───────┐
│ i │
│ int64 │
├───────┤
│ 1 │
│ 2 │
│ 3 │
└───────┘
range excludes the stop value, while generate_series includes it.
VALUES
The VALUES clause generates rows from literal tuples, which is convenient for a small fixed set of values to draw from:
SELECT * FROM (VALUES ('Alice', 'Engineering'), ('Bob', 'Sales')) t(name, department);
┌─────────┬─────────────┐
│ name │ department │
│ varchar │ varchar │
├─────────┼─────────────┤
│ Alice │ Engineering │
│ Bob │ Sales │
└─────────┴─────────────┘
repeat_row
repeat_row(varargs, num_rows) returns a fixed number of identical rows, which is useful as a scaffold to fill with generated values:
FROM repeat_row('placeholder', 0, num_rows = 3);
┌─────────────┬─────────┐
│ column0 │ column1 │
│ varchar │ int32 │
├─────────────┼─────────┤
│ placeholder │ 0 │
│ placeholder │ 0 │
│ placeholder │ 0 │
└─────────────┴─────────┘
Cross Products
A cross product (Cartesian product) multiplies rows from two sources, so combining two range calls is a compact way to produce a large number of rows:
SELECT i, j FROM range(1, 3) s(i) CROSS JOIN range(1, 3) t(j);
Generating Values
Random Numbers
random() returns a random DOUBLE in the range [0, 1), which you can scale and cast into any numeric range:
SELECT (random() * 100)::INTEGER AS score FROM range(3);
UUIDs
Several utility functions generate universally unique identifiers, which make good synthetic primary keys:
uuid() and its alias uuidv4() generate random (version 4) UUIDs, uuidv7() generates time-ordered (version 7) UUIDs, and gen_random_uuid() is an alias for uuid():
SELECT uuidv7() AS id FROM range(3);
Current Date and Time
Functions that return the current date or time are handy for populating timestamp columns:
today() returns the current date,
get_current_time() returns the current time, and
current_localtimestamp() returns the current local timestamp:
SELECT today() AS date, get_current_time() AS time, current_localtimestamp() AS timestamp;
Hashing
Hash functions such as hash, md5 and sha256 turn a row number into a deterministic pseudo-random value, which is useful when you want reproducible identifiers:
SELECT hash(i) AS id, md5(i::VARCHAR) AS token FROM range(3) t(i);
Repeating Strings
repeat(string, count) builds a string by repeating another, which is useful for padding values to a target length:
SELECT repeat('ab', 3) AS padded;
Sequences
A sequence produces a monotonically increasing series of numbers. nextval('sequence_name') advances the sequence and returns the next value, while currval('sequence_name') returns the value most recently produced:
CREATE SEQUENCE id_seq START 100;
SELECT nextval('id_seq') AS id FROM range(3);
Generating Text with Faker
For realistic-looking names, addresses and other text, you can call the Faker Python package through the Python function API:
import duckdb
from duckdb.sqltypes import *
from faker import Faker
fake = Faker()
def random_date():
return fake.date_between()
def random_short_text():
return fake.text(max_nb_chars=20)
def random_long_text():
return fake.text(max_nb_chars=200)
con = duckdb.connect()
con.create_function("random_date", random_date, [], DATE, type="native", side_effects=True)
con.create_function("random_short_text", random_short_text, [], VARCHAR, type="native", side_effects=True)
con.create_function("random_long_text", random_long_text, [], VARCHAR, type="native", side_effects=True)
res = con.sql("""
SELECT
hash(i * 10 + j) AS id,
random_date() AS creation_date,
random_short_text() AS short,
random_long_text() AS long,
IF (j % 2, TRUE, FALSE) AS bool
FROM generate_series(1, 5) s(i)
CROSS JOIN generate_series(1, 2) t(j)
""")
res.show()
This generates the following:
┌──────────────────────┬───────────────┬─────────────────────┬───────────────────────────────────────────────┬─────────┐
│ id │ creation_date │ short │ long │ bool │
│ uint64 │ date │ varchar │ varchar │ boolean │
├──────────────────────┼───────────────┼─────────────────────┼───────────────────────────────────────────────┼─────────┤
│ 6770051751173734325 │ 1998-01-04 │ Head keep face age. │ Stay different gun according make large read… │ true │
│ 16510940941872865459 │ 2006-07-30 │ Ten quickly expect. │ Early race claim. Middle economy box nor. An… │ true │
│ 13285076694688170502 │ 2018-02-19 │ Spring writer onto. │ Lose painting time dog sure marriage month. … │ true │
│ 11757770452869451863 │ 2011-09-01 │ Recently single. │ Power class party information this. Appear e… │ true │
│ 2064835973596856015 │ 2014-03-09 │ Write nor national. │ Total need tend Democrat. Group under boy no… │ true │
│ 17776805813723356275 │ 2000-10-21 │ Commercial occur. │ Professor big but executive plan fast body b… │ false │
│ 13540103502347468651 │ 2023-08-23 │ Specific author. │ Across cut market eight. Thing pull open spa… │ false │
│ 4800297459639118879 │ 2004-02-18 │ Beautiful learn. │ Upon common box beautiful. War check investm… │ false │
│ 7199933130570745587 │ 2024-11-30 │ Fact nation cup. │ Trouble that sing former sing sure range ser… │ false │
│ 18103378254596719331 │ 2000-07-27 │ Late ago serious. │ Million operation six school station give us… │ false │
└──────────────────────┴───────────────┴─────────────────────┴───────────────────────────────────────────────┴─────────┘
10 rows 5 columns
Generating Benchmark Datasets with Extensions
To generate large, well-defined datasets, DuckDB ships with the TPC-H and TPC-DS extensions. Each provides a data generator function that creates and populates the benchmark's tables at a given scale factor:
INSTALL tpch;
LOAD tpch;
CALL dbgen(sf = 0.1);
SELECT count(*) AS lineitem_rows FROM lineitem;
Community Extensions
The fakeit community extension provides table functions that generate fake data (such as names, emails, and addresses) directly in SQL, similar to the Faker Python package.