cancel
Showing results forĀ 
Search instead forĀ 
Did you mean:Ā 
Technical Blog
Explore in-depth articles, tutorials, and insights on data analytics and machine learning in the Databricks Technical Blog. Stay updated on industry trends, best practices, and advanced techniques.
cancel
Showing results forĀ 
Search instead forĀ 
Did you mean:Ā 
DouglasGarcia
Databricks Employee
Databricks Employee

Screenshot 2026-07-14 at 15.58.13.png

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 (`AI_GEN` and `AI_QUERY`) let you generate realistic synthetic datasets entirely in SQL. No Python, no external tools, no CSV imports.
  • The silver bullet is the `AI_QUERY` function using the `responseFormat` parameter for structured output and temperature >= 1.0 for diversity.
  • AI is best for text and profiles; use standard SQL for numbers and dates. Generate once, persist to tables, and query many times.

What are AI Functions?

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.

How do you quickly generate a dimension table with ai_gen?

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

email

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

 

How can you enforce structured JSON output with ai_query?

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

 

How do you generate fact data with referential integrity?

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.

Approach A: AI-Aware Orders (Context-Driven)

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.

Approach B: Deterministic CROSS JOIN (Recommended for Volume)

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.

How do you generate realistic free-text fields?

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


How do you generate address data with regional accuracy?

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.

What are the cost and performance tips?

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.

How do you handle failures gracefully?

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.

When should you use AI Functions vs. other approaches?

 

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.

Putting It All Together

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.

Conclusion

Databricks AI Functions turn SQL into a synthetic data generator. The pattern is simple:

  1. Generate row IDs with SEQUENCE + EXPLODE
  2. Use `ai_query` with `temperature >= 1.0` for diverse text and profile data
  3. Cycle dimensions with modulo to guarantee variety across countries, topics, categories
  4. Use SQL for numbers and dates: RAND(), DATE_ADD, CASE WHEN
  5. Persist to tables: generate once, query many times

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.

References

1 Comment
gnakan
Databricks MVP

Thanks for writing this up, @DouglasGarcia. The split you draw, "AI is best for text and profiles; use standard SQL for numbers and dates," was interesting enough that I ran an experiment on it.

I built customer, order and ticket tables with Databricks' ai_query function on Databricks Free Edition, the same way your post does, with Llama 3.3 70B at temperature 1.0. The one change: I let the model pick the categories from a list too, and kept your modulo cycle on one column as the control.

The experiment results suggest keeping your split. The model wrote 114 different cities across 978 customers, which is the job you give it. Picking from five ticket types, it filed 971 of 973 tickets as technical and never used the other four. Order status landed on delivered for 969 of 995. Your modulo cycle dealt four plan tiers 247, 242, 245 and 244.

I also reran the tickets at 1.5, the top of your range. The model still chose technical for 865 of 886, and 21 tickets came back with a type that isn't on the list. So for a list column, the modulo cycle is what gets you variety, as your ticket example shows.

My experiment, including my notebook, is at https://medium.com/lab-notes/databricks-ai-query-function-filed-971-of-973-synthetic-tickets-as-tech...