The views expressed in this post are my own and do not represent those of my employer.
Not long ago, generating realistic synthetic data meant building custom pipelines with GANs, Transformers, or RNN-based models. It worked, but the engineering overhead was real: training loops, schema drift, distribution tuning, and infrastructure to keep it all running.
Today, you can do the same thing in pure SQL using Databricks AI Functions. No Python. No external tools. No CSV imports. What used to take weeks of pipeline work now takes minutes.
If you're testing data pipelines, dashboards, or ML models without access to production data, this one's for you. In this post, I'll show you how, so tag along!
TL;DR:
Databricks AI Functions are built-in SQL functions that invoke large language models directly from SQL queries. The simplest is ai_gen, which takes a prompt and returns generated text:
SELECT ai_gen('Generate a realistic company name for a Nordic fintech startup');
-- Returns: "Fjord Payments AB"
AI Functions run on Databricks Foundation Model APIs, require a Serverless SQL Warehouse, and are billed as SQL compute plus token usage. The key insight: when combined with SQL constructs like EXPLODE and SEQUENCE, you can generate entire tables of realistic data.
The simplest way to generate a dimension table: use ai_gen with pipe-separated output and SPLIT to parse it into columns. I cycle countries via customer_id % 4 to guarantee geographic diversity:
CREATE OR REPLACE TABLE dim_customer_synthetic AS
WITH row_ids AS (
SELECT EXPLODE(SEQUENCE(1, 20)) AS customer_id
)
SELECT
customer_id,
CASE WHEN customer_id % 4 = 0 THEN 'Sweden'
WHEN customer_id % 4 = 1 THEN 'Norway'
WHEN customer_id % 4 = 2 THEN 'Denmark'
ELSE 'Finland' END AS country,
ai_gen(
CONCAT(
'Generate a fictional customer profile from ',
CASE WHEN customer_id % 4 = 0 THEN 'Sweden'
WHEN customer_id % 4 = 1 THEN 'Norway'
WHEN customer_id % 4 = 2 THEN 'Denmark'
ELSE 'Finland' END,
' as pipe-separated values: first_name|last_name|email|city|segment. ',
'Customer #', customer_id, '. Only return the pipe-separated values, nothing else.'
)
) AS raw_profile
FROM row_ids;
-- Parse pipe-separated output into columns
CREATE OR REPLACE TABLE dim_customer AS
SELECT
customer_id, country,
SPLIT(raw_profile, '\\|')[0] AS first_name,
SPLIT(raw_profile, '\\|')[1] AS last_name,
SPLIT(raw_profile, '\\|')[2] AS email,
SPLIT(raw_profile, '\\|')[3] AS city,
SPLIT(raw_profile, '\\|')[4] AS segment
FROM dim_customer_synthetic;
This is quick and readable, but has two drawbacks: you need a second query to parse the output, and ai_gen doesn't support temperature or modelParameters, so the LLM may produce some repeated rows. For more control, see Pattern 2.
Result:
|
customer_id |
first_name |
last_name |
|
city |
country |
segment |
|
1 |
Bjørn |
Olsen |
bjornolsen@norge.no |
Oslo |
Norway |
premium |
|
2 |
Astrid |
Rasmussen |
astrid.r@yahoo.dk |
Aarhus |
Denmark |
mid_range |
|
3 |
Simo |
Laulajainen |
laku3@esimeti.fi |
Turku |
Finland |
premium |
|
4 |
Luna |
Svensson |
lunasvensson@example.com |
Stockholm |
Sweden |
mid_range |
|
5 |
Gert |
Frisvold |
gerton5@nya.no |
Bergen |
Norway |
premium |
Pattern 1 works but has limitations: ai_gen doesn't let you control randomness or enforce output structure, so you end up splitting strings and sometimes getting repeated rows.
ai_query is the more powerful alternative. It lets you choose a model, set the temperature for diversity, and enforce a JSON schema with responseFormat. No string splitting, just parse the JSON with FROM_JSON:
CREATE OR REPLACE TABLE dim_product_synthetic AS
WITH row_ids AS (
SELECT EXPLODE(SEQUENCE(1, 15)) AS product_id
),
raw AS (
SELECT product_id,
ai_query(
'databricks-meta-llama-3-3-70b-instruct',
CONCAT(
'Generate a unique fictional product for an electronics retailer. ',
'Product #', product_id, ' of 15. Must be completely different from others. Return JSON only.'
),
modelParameters => named_struct('temperature', CAST(1.5 AS DOUBLE)),
responseFormat => 'STRUCT<result: STRUCT<product_name: STRING, category: STRING, brand: STRING, price_usd: DOUBLE, weight_kg: DOUBLE>>'
) AS json_result
FROM row_ids
),
parsed AS (
SELECT product_id,
FROM_JSON(json_result, 'STRUCT<product_name: STRING, category: STRING, brand: STRING, price_usd: DOUBLE, weight_kg: DOUBLE>') AS p
FROM raw
)
SELECT product_id, p.product_name, p.category, p.brand, p.price_usd, p.weight_kg
FROM parsed;
The responseFormat parameter tells the LLM to output JSON matching the specified schema. Since ai_query returns this as a STRING, I use FROM_JSON to parse it into a proper struct, then extract the fields.
Result:
|
product_id |
product_name |
category |
brand |
price_usd |
weight_kg |
|
1 |
Aurora X1 |
Smart Glasses |
Eonix |
499.99 |
0.06 |
|
2 |
DreamWeaver Pods |
Sleep Technology |
Aurora |
249.99 |
0.50 |
|
3 |
Aurora X1 |
Smart Home Devices |
EchoPlex |
249.99 |
0.58 |
|
4 |
DreamWeaver Neuroheadset |
Brain-Computer Interface |
AuroraTech |
499.99 |
0.25 |
|
5 |
DreamWeaver |
Sleep Technology |
Aurora |
249.99 |
1.20 |
Once you have dimension tables, you can generate fact rows that reference them. Here I'll show two approaches: an AI-powered one that produces contextually realistic orders, and a lightweight one that's faster and cheaper.
What if the LLM could decide realistic quantities and order patterns based on who the customer is and what they're buying? An Enterprise customer buying a projector might order 3 units; an SMB customer buying headphones probably orders 1.
The idea is simple: CROSS JOIN your dimensions to create candidate orders, then let ai_query generate the order details based on context:
CREATE OR REPLACE TABLE fact_orders_synthetic AS
WITH combos AS (
-- CROSS JOIN creates all possible customer/product pairs, RAND filters to ~40%
SELECT
ROW_NUMBER() OVER (ORDER BY RAND()) AS order_id,
c.customer_id, c.segment, c.country,
p.product_id, p.product_name, p.category, p.price_usd
FROM dim_customer c
CROSS JOIN dim_product_synthetic p
WHERE RAND() < 0.4
)
SELECT
order_id, customer_id, product_id, price_usd,
-- ai_query generates context-aware order details as JSON, FROM_JSON parses it
FROM_JSON(
ai_query(
'databricks-meta-llama-3-3-70b-instruct',
CONCAT(
'A ', segment, ' customer from ', country,
' is ordering "', product_name, '" (', category, ', $', CAST(price_usd AS STRING), '). ',
'Generate a realistic order. Return JSON only.'
),
modelParameters => named_struct('temperature', CAST(1.2 AS DOUBLE)),
responseFormat => 'STRUCT<result: STRUCT<quantity: INT, status: STRING, order_date: STRING>>'
),
'STRUCT<quantity: INT, status: STRING, order_date: STRING>'
) AS details
FROM combos;
-- Query the result, computing total_amount from price * quantity
SELECT order_id, customer_id, product_id,
details.order_date, details.quantity,
ROUND(price_usd * details.quantity, 2) AS total_amount,
details.status
FROM fact_orders_synthetic;
Result:
|
order_id |
customer_id |
product_id |
order_date |
quantity |
total_amount |
status |
|
1 |
9 |
4 |
2025-06-15 |
2 |
999.98 |
completed |
|
2 |
4 |
1 |
2025-03-05 |
1 |
499.99 |
completed |
|
3 |
8 |
2 |
2025-08-22 |
3 |
749.97 |
processing |
|
4 |
1 |
5 |
2025-11-11 |
1 |
249.99 |
completed |
The LLM generates order details based on the customer/product context. However, this is expensive: one LLM call per order row. Use this when realism matters more than cost (e.g., a demo dataset for a customer meeting). For better variation, refine the prompt. For example, specify the year, provide example statuses, or ask for quantities proportional to customer segment.
For most cases, you don't need AI for fact tables. Standard SQL randomization produces good-enough transactional data when combined with AI-generated dimensions:
CREATE OR REPLACE TABLE fact_orders_synthetic AS
SELECT order_id, customer_id, product_id, order_date, quantity,
ROUND(price_usd * quantity, 2) AS total_amount,
status
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY RAND()) AS order_id,
c.customer_id,
p.product_id,
p.price_usd,
DATE_ADD(DATE '2025-01-01', CAST(RAND() * 365 AS INT)) AS order_date,
CAST(1 + FLOOR(RAND() * 3) AS INT) AS quantity,
CASE WHEN RAND() > 0.1 THEN 'completed' ELSE 'returned' END AS status
FROM dim_customer c
CROSS JOIN dim_product_synthetic p
WHERE RAND() < 0.3
);
Both approaches use the same CROSS JOIN + RAND filter pattern. The difference: Approach A asks the LLM to fill in the details, Approach B uses RAND(). Approach B is free (no LLM calls) and produces hundreds of rows in seconds.
When to use which: Approach A for demos and customer-facing datasets where realism justifies the cost. Approach B for everything else.
This is where AI Functions truly shine: generating the kind of messy, realistic text that's hard to fake with deterministic code. I use ai_query with temperature for diversity and cycle the topic via modulo to guarantee coverage across issue types:
CREATE OR REPLACE TABLE support_tickets_synthetic AS
WITH ticket_ids AS (
SELECT EXPLODE(SEQUENCE(1, 30)) AS ticket_id
)
SELECT
ticket_id,
DATE_ADD(CURRENT_DATE(), -CAST(RAND() * 90 AS INT)) AS created_date,
ai_query(
'databricks-meta-llama-3-3-70b-instruct',
CONCAT(
'Write a realistic customer support ticket (2-3 sentences) for an electronics retailer. ',
'Ticket #', ticket_id, '. Topic: ',
CASE WHEN ticket_id % 5 = 0 THEN 'shipping delay'
WHEN ticket_id % 5 = 1 THEN 'defective product'
WHEN ticket_id % 5 = 2 THEN 'billing question'
WHEN ticket_id % 5 = 3 THEN 'return request'
ELSE 'warranty claim' END,
'. Use natural informal language. Return only the ticket text.'
),
modelParameters => named_struct('temperature', CAST(1.2 AS DOUBLE))
) AS description,
CASE WHEN ticket_id % 5 = 0 THEN 'shipping'
WHEN ticket_id % 5 = 1 THEN 'product_defect'
WHEN ticket_id % 5 = 2 THEN 'billing'
WHEN ticket_id % 5 = 3 THEN 'return'
ELSE 'warranty' END AS category
FROM ticket_ids;
Result:
|
ticket_id |
created_date |
description |
category |
|
1 |
2026-01-24 |
I received my new smartwatch last week, but it's already stopped working - the screen just goes black after a few minutes of use. I've tried restarting it and charging it, but nothing seems to work. Can I get a replacement or a refund? |
product_defect |
|
2 |
2026-01-10 |
I just got my credit card statement and I'm being charged $50 more than I thought for the new TV I bought last week. Can you help me figure out what the extra charge is for? My order number is #1234. |
billing |
|
3 |
2026-03-23 |
I'm having some issues with the Bluetooth headphones I bought last week, they keep cutting in and out, and I'd like to return them for a refund or exchange. I've only used them a few times and still have all the original packaging. |
return |
SELECT
customer_id, country,
ai_query(
'databricks-meta-llama-3-3-70b-instruct',
CONCAT(
'Generate a realistic but fictional street address in ', country, '. ',
'Return only: street_address|postal_code|city. Use the local address format. ',
'Nothing else.'
),
modelParameters => named_struct('temperature', CAST(1.3 AS DOUBLE))
) AS address_raw
FROM dim_customer
LIMIT 10;
Result:
|
customer_id |
country |
address_raw |
|
1 |
Norway |
Fjellveien 14B | 6050 | Ă…lesund |
|
2 |
Denmark |
Løvevej 12, st. tv. | 2800 | Kgs. Lyngby |
|
3 |
Finland |
Keskuskatu 14 B 10 | 87100 | Kajaani |
|
4 |
Sweden |
Kämpevägen 14 | 123 45 | Lidingö |
|
5 |
Norway |
Kirkeveien 14 | 0372 | Oslo |
Because the LLM understands regional conventions, you get properly formatted Norwegian postal codes (e.g., "6050"), Danish apartment notation (e.g., "st. tv."), Finnish address patterns (e.g., "B 10"), and Swedish postal codes (e.g., "123 45") all without maintaining lookup tables.
|
Tip |
Why |
|
Always use `LIMIT` during development |
Each row = one LLM call. Start with 5-10 rows, scale up when the prompt is right. |
|
Set `temperature` >= 1.0 |
Without it, the LLM generates near-identical rows. Use 1.2-1.5 for good diversity. |
|
Use `ai_query` with `responseFormat` for structured data |
Avoids string parsing, reduces prompt tokens, gives typed output. |
|
Cycle dimensions with modulo |
customer_id % 4 for countries, ticket_id % 5 for topics. Guarantees diversity even if the LLM gets lazy. |
|
Use deterministic SQL for numeric/date columns |
RAND(), SEQUENCE, DATE_ADD are free. Only use AI for text fields. |
|
Cache results in a table |
CREATE TABLE AS SELECT once, then query the table. Don't regenerate every time. |
|
Use `failOnError => false` with ai_query |
Prevents one bad row from killing a batch of 100. |
When generating synthetic data in batch, LLM calls can occasionally fail: a malformed response, a rate limit hit, or a timeout. By default, ai_query will abort the entire query if any single row fails. For a 100-row generation job, that means losing all results because of one bad row.
Setting failOnError => false changes the return type to a struct with both the result and any error message, so you can generate the batch and deal with failures after:
-- Generate 50 products, tolerating individual failures
CREATE OR REPLACE TABLE products_raw AS
WITH ids AS (SELECT EXPLODE(SEQUENCE(1, 50)) AS product_id)
SELECT
product_id,
response.result AS product_data,
response.errorMessage AS error
FROM ids,
LATERAL (
SELECT ai_query(
'databricks-meta-llama-3-3-70b-instruct',
CONCAT('Generate a fictional electronics product #', product_id),
responseFormat => 'STRUCT<result: STRUCT<name: STRING, category: STRING, price: DOUBLE>>',
failOnError => false
) AS response
);
-- Check: how many succeeded vs failed?
SELECT
COUNT(*) AS total,
COUNT_IF(error IS NULL) AS succeeded,
COUNT_IF(error IS NOT NULL) AS failed
FROM products_raw;
-- Use only the successful rows
SELECT product_id, product_data.*
FROM products_raw
WHERE error IS NULL;
This pattern is especially useful when generating larger batches (50+ rows) where the occasional LLM hiccup is expected. Generate first, filter failures, then backfill the gaps if needed.
|
Approach |
Best For |
Drawback |
|
ai_gen / ai_query |
Realistic text, regional data, domain-specific content |
Cost per row, rate limits |
|
Faker (Python) |
High volume (millions of rows), deterministic seeds |
Requires Python, less contextually realistic |
|
dbldatagen |
Spark-native, schema-aware, statistical distributions |
Requires cluster, not SQL-native |
|
Manual CSV |
Fixed reference data |
Doesn't scale, hard to maintain |
The sweet spot for AI Functions: generating 10-1,000 rows of realistic dimension data and free-text fields where regional accuracy and domain context matter.
I've published a complete end-to-end script that generates a full Nordic e-commerce test dataset (customers, products, orders, support tickets), four relational tables, all in pure SQL. You can find it here: complete synthetic data generation script on GitHub.
Databricks AI Functions turn SQL into a synthetic data generator. The pattern is simple:
Next time you need test data for a demo, a POC, or a development environment, try generating it right where you'll use it: in SQL.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.