---
name: dbgen-playground-import
description: Generate dbgen templates from user-provided table schemas, representative SQL, and EXPLAIN or statistics evidence, then validate them by generating CSVs, starting a local TiDB playground, creating tables from the supplied schema, importing with IMPORT INTO, and checking representative joins or query samples. Use when the user wants an end-to-end workflow for "schema/sql/plan -> dbgen data -> playground import -> verification", especially for TiDB or MySQL-compatible workloads, load-simulation data generation, join-heavy queries, skewed workloads, or import validation in a customer-local environment. The workload may be multi-tenant, single-tenant, region-sharded, time-bucketed, or organized by some other grouping dimension.
---

# DBGen Playground Import

Use this skill to turn workload evidence into reusable dbgen templates and a repeatable TiDB playground import workflow.

Default posture:

- Treat SQL literals as examples unless the user explicitly asks for exact reproduction.
- Default to a natural business-like distribution, not a hard-coded row-for-row replay.
- Use the user-provided schema to create target tables; do not assume the generated schema should be used for import.
- Split dbgen templates when independent root tables exist.
- Prefer parameterized distribution rules over fixed ID lists or fixed magic counts.
- Infer modeling rules from schema, statistics, SQL, and explicit user input instead of forcing a canned pattern.
- Read the customer's background and business description before choosing realistic values, cardinalities, and hotspots.
- If the user does not specify target scale, default to a medium-sized simulation.
- After generating templates, default to validating them in a customer-local `tiup playground nightly` environment unless the user explicitly asks for another target host.

## Workflow

### 1. Gather Inputs

Collect these inputs before generating anything:

- Table schemas or DDL files
- Representative SQL
- EXPLAIN, EXPLAIN ANALYZE, statistics, or observed row counts
- Customer or business background description when available
- Desired validation target:
  natural distribution or reproduction-oriented distribution
- Execution environment:
  customer-local TiDB playground by default, or another explicitly requested environment

If the user provides both SQL and execution plans, trust the plans more than the SQL comments or surrounding narrative when they conflict.
If the user explicitly corrects a modeling assumption, override your default heuristics.

### 2. Model the Workload

Infer these properties from the SQL and plans:

- Root tables and join graph
- Join keys and whether they must actually match after generation
- Filter classes:
  equality, suffix/pattern LIKE, IN lists, NULL-sensitive predicates, time windows
- Skew shape:
  hot group, medium group, long tail
- Aggregation shape:
  repeated `inbox_id`, repeated `assignee_id`, window partitions, history rows
- Time shape:
  hot recent slice, broad historical background, partition-like clustering
- Grouping shape:
  if the workload has a grouping dimension, estimate group count, group size distribution, hotspot depth, and per-group reuse

Use these evidence sources together:

1. schema structure
2. representative SQL
3. plans or statistics
4. user-provided business context

Treat them as complementary, not interchangeable.

Business context should influence:

- realistic entity naming and value shape
- likely group sizes
- likely hotspot depth
- likely optional vs. frequently populated fields
- likely transaction or history behavior

Do not optimize for total row count first. Optimize for:

1. Filter selectivity
2. Key reuse and clustering
3. Joinability
4. Then total volume

If the evidence conflicts, resolve in this order:

1. explicit user correction
2. schema constraints
3. observed plans or statistics
4. SQL literals and surrounding prose

### 3. Build dbgen Templates

Default output style:

- One template per independent root-table family
- Prefer names like:
  `*-public.sql`, `*-whatsapp.sql`
- Keep a light version for smoke tests and a larger version for full import tests when useful

When writing templates:

- Use synthetic anchors, not user example literals, unless the user explicitly wants exact values preserved
- Keep business-looking values realistic:
  numeric phone numbers, plausible channels, realistic timestamps
- Preserve query shape:
  hot inbox reuse, queue history, dialogs history, sparse message hits, time-range clusters
- Make sure join keys line up across tables
- If the workload has a grouping dimension, derive group cardinality and group-size buckets from target volume instead of hard-coding a tiny list of IDs
- Only use a tenant or account distribution when the evidence indicates such a dimension exists
- When a query tests `LIKE '%094%'`, reserve a small intentional numeric slice for that and avoid accidental overmatching elsewhere
- Avoid making JSON columns almost always empty unless the workload evidence suggests that

If the workload has independent roots, split templates instead of forcing one giant template.

Read [dbgen-compatibility.md](references/dbgen-compatibility.md) before finalizing templates for concrete generator constraints and remote bench quirks.
Read [load-modeling.md](references/load-modeling.md) before choosing group distribution, JSON fill rates, queue history rules, assignee pool sizing, or a non-tenant hotspot model.

### 4. Validate dbgen Output

Validate in layers:

1. Syntax and generator compatibility
2. Output file presence and schema file generation
3. CSV-level joinability
4. SQL-level verification after import

Minimum checks:

- Confirm all expected CSV and schema files exist
- Show first few rows from each important table
- Check whether the generated grouping-cardinality and size distribution still match the intended workload model
- Check whether the inferred grouping dimension was actually the right one
- Validate representative joins before import if the workflow is join-heavy
- After import, rerun a representative SQL or a reduced verification query

If the user provided a specific workload like SQL1, verify that exact join path with generated data.

### 5. Start TiDB Playground and Import

When the user wants playground validation:

- Start a minimal local playground first unless they ask for a larger topology
- Prefer `tiup playground nightly --db 1 --pd 1 --kv 1 --tiflash 0 --without-monitor`
- Use a stable tag for cleanup and re-entry
- Wait until TiDB is reachable before creating databases and tables
- Treat remote pre-provisioned hosts as opt-in, not default

For import:

- Create databases from the user schema first
- Use `IMPORT INTO ... FROM '<server-local-path>' WITH skip_rows=1` for CSVs with headers
- Import table by table
- When partial regeneration is needed, re-import only the affected tables instead of truncating everything
- Run `SHOW IMPORT JOBS` if the command output is not enough

Read [playground-import.md](references/playground-import.md) when the workflow reaches the playground stage.

### 6. Verify Imported Data

Always verify both counts and behavior:

- Table row counts
- Representative join samples
- Representative filtered query samples
- Representative window or aggregation samples when those were part of the original workload

Do not stop at "import succeeded". Confirm the generated data still behaves like the intended workload.

## Decision Rules

### Natural vs Reproduction Mode

Default to natural mode when:

- The user says sample values are illustrative only
- The goal is business-like data
- The goal is system validation or repeated testing

Use reproduction-oriented mode when:

- The user explicitly wants to mimic one problematic query pattern tightly
- The goal is to recreate a specific plan shape or selectivity band

Even in reproduction-oriented mode, preserve only the shape unless the user explicitly asks for exact constants.

### Default Scale When Unspecified

If the user does not give a target row count, default to a medium-sized simulation.

Use "medium" to mean:

- large enough to expose joins, skew, and history behavior
- small enough to generate, import, and iterate quickly

Do not silently jump to tiny toy data or production-scale data unless the user asks for that.

### How to Choose Group Distribution

First identify whether the workload actually has a grouping dimension.

If no grouping dimension exists, model skew on other dominant keys such as:

- hot identifiers
- ownership or routing keys
- status or type buckets
- time windows

Infer the grouping dimension from evidence:

- schema:
  uniqueness, indexes, foreign keys, table comments, naming, parent-child boundaries
- SQL:
  join predicates, equality filters, IN filters, grouping and ordering columns
- plans and statistics:
  actual and estimated row concentrations
- user input:
  business wording about ownership, partitioning, hotspots, or unrealistic cardinality

If a grouping dimension does exist, prefer a bucketed size model over a flat average.

Typical generic buckets:

- very large
- large
- medium
- small
- tiny

Derive:

1. target total rows
2. bucket share of rows
3. rows per group per bucket
4. resulting group count per bucket

This is usually more realistic than rules like:

- `distinct group count = total rows / constant`

### How to Choose Assignee Distribution

Do not reuse a tiny global assignee set for all groups unless the user explicitly wants that.

Prefer:

- group-specific assignee pools when a grouping dimension exists
- pool size derived from group size bucket
- related `first_response_operator_*` and `team_ids` generated consistently from the same pool

Only apply this rule if assignee-like columns actually matter in the workload evidence.

### How to Choose JSON Fill Rates

Default to partially populated JSON columns, not almost-all-empty JSON.

Model JSON columns in three classes:

- Always or almost always populated:
  e.g. `channels`, `team_ids`
- Frequently populated:
  e.g. `tags`, `tag_ids`
- Optional sparse enrichment:
  e.g. `custom_attrs`, `additional_attributes`

Tune fill rate by workload class rather than making every JSON column follow the same rule.

Choose JSON fill behavior from evidence:

- schema defaults and optionality
- whether SQL reads or filters the JSON
- whether the JSON is core payload, metadata, or optional enrichment
- user feedback that the field is unrealistically sparse or dense

Choose JSON fill behavior from evidence:

- schema defaults such as `JSON_ARRAY()`
- SQL path reads or path filters
- user feedback that a column is unrealistically sparse or unrealistically dense
- whether the JSON looks like core payload, metadata, or optional enrichment

### When to Split Templates

Split templates when:

- Two groups of tables do not depend on each other for row generation
- The generator or remote environment handles only one root chain reliably
- One table family needs a very different distribution strategy

Examples:

- `contacts -> inbox_conversations -> inbox_conversation_queue -> dialogs`
- `whatsapp_message`

### When to Patch User Schema

If a user-provided schema file is almost executable but has formatting defects, patch a normalized copy instead of rewriting their original input in place.

Use [fix_schema_sql.py](scripts/fix_schema_sql.py) when multiple `CREATE TABLE` blocks are missing separators.

### When to Regenerate Only One Table

If the problem is isolated to a non-join key attribute, prefer a partial regen plus partial re-import.

Examples:

- wrong `assignee_id` diversity in `dialogs`
- wrong JSON fill rates in `contacts`
- wrong queue history depth in `inbox_conversation_queue`

Do not regenerate the entire dataset when only one table's non-key distribution is wrong.

## References

- Read [workflow.md](references/workflow.md) for the end-to-end checklist
- Read [dbgen-compatibility.md](references/dbgen-compatibility.md) for generator constraints and common pitfalls
- Read [load-modeling.md](references/load-modeling.md) for tenant, assignee, queue, and JSON modeling rules
- Read [playground-import.md](references/playground-import.md) for TiDB playground and IMPORT INTO validation steps