Generation Algorithms
Numeric calibration algorithm and detailed generation rules used during Phase 4.
Phase 4: Numeric Calibration Algorithm
For log-normal distributed fields (Sales, Revenue, Amounts):
- Target: generated_avg ≈ profiled_avg (within 10%), range approximately [profiled_min, profiled_max]
- Estimate log-normal parameters from profiled stats (the median and average are both profiled in Step 2C):
σ = sqrt(2 * ln(profiled_avg / profiled_median))(capped at σ ≤ 2.5 to prevent extreme skew)μ = ln(profiled_median)- Only if the median is unavailable or not below the average, fall back to
σ = sqrt(2 * ln(profiled_max / profiled_avg))(same cap) andμ = ln(profiled_avg) - σ²/2
- Generate all values:
value = random.lognormvariate(μ, σ) - Clamp each value to [profiled_min, profiled_max]
Calibration verification — multi-attempt with escape hatch:
After generating all values for a numeric field:
- Compute generated_avg = mean(values)
- Compute variance_pct = (generated_avg - profiled_avg) / profiled_avg
Attempt 1: If |variance_pct| > 0.10:
- Apply scaling factor: multiply all values by (profiled_avg / generated_avg)
- Re-clamp to [profiled_min, profiled_max]
- Recompute generated_avg
Attempt 2: If still |variance_pct| > 0.10 after scaling:
- Adjust μ: increase μ by 0.2 if under-shooting, decrease by 0.2 if over-shooting
- Regenerate the entire field with new μ
- Apply scaling again if needed
Attempt 3 (final): If still |variance_pct| > 0.10:
- Compute final variance percentage
- STOP attempting corrections. Present to user: "Calibration note: [Field] generated avg is $[X] vs profiled $[Y] ([Z]% variance). The distribution shape makes exact calibration difficult within the profiled range. Options: (a) Accept this variance, (b) I can use a simpler uniform distribution that hits the target average exactly but loses the realistic shape, or (c) adjust the acceptable range."
- Wait for user decision before proceeding.
For normal distributed fields (Scores, Ratings):
- μ = profiled_avg
- σ = (profiled_max - profiled_min) / 6 (99.7% within range)
- Generate:
value = random.gauss(μ, σ), clamp to [profiled_min, profiled_max] - Verify generated_avg within 10% (normal distributions self-calibrate well; if not, apply same escape hatch)
For integer fields (Quantity):
- Use weighted random from profiled range, with geometric decay (lower values more common)
- Verify generated_avg is within 10% of profiled_avg
- If not: adjust weights (increase probability of higher values if avg is too low, or vice versa)
Phase 4: Generation Rules
-
Names: Synthetic, culturally plausible, non-famous. Use diverse first/last name pools (100+ each). Never use placeholder patterns like "Customer_1" unless explicitly requested.
-
Emails: Pattern
{first}.{last}@{domain}with synthetic domains (example.com, testcorp.net) unless company specified. Add numeric suffix if uniqueness requires. -
IDs with date component: Extract the date from the associated date field.
ORD-{YYYY}-{#####}→ YYYY = year(order_date) for that row. NEVER generate the year independently of the date field. -
Sequential IDs without date: Start from 1 (or user-specified), increment by 1. Format with zero-padding per profiled pattern.
-
Dates:
- Generate within user-specified range (override profiled range if user provides one)
- Weekday weighting: use profiled day-of-week weights (default if not profiled: Mon-Fri 16% each, Sat-Sun 10% each; a domain template may override)
- Respect ordering constraints: Ship Date ≥ Order Date, End Date ≥ Start Date
- Ship Date delay depends on Ship Mode (Same Day: 0, First Class: 1-3, Second Class: 2-5, Standard: 3-7 days)
-
Categorical fields: Generate using profiled frequency distributions. Example: if Category is Office Supplies 60%, Furniture 22%, Technology 18% — use weighted random selection matching those exact percentages.
-
Geographic data:
- Use REAL countries, states, cities
- Validate hierarchy: City must exist in State, State must exist in Country
- Match profiled geographic distributions (CA 20%, NY 11%, TX 10%...)
- State→Region mapping must match profiled relationship exactly
- Postal codes must be plausible for the city/state (use real ZIP ranges)
- Fallback for ambiguous geography: If a city name exists in multiple states (e.g., "Springfield"), always tie it to the state already selected for that row. If no valid city can be confirmed for a state, select from a known-good list of major cities for that state. Never leave a geographic field unvalidated — if in doubt, use the state capital or largest city.
-
Nulls: For each field with profiled null rate > 0, apply that exact null rate randomly. If no null profiling was possible, default to 0% (NOT NULL) unless user specifies otherwise.
-
Cross-field correlations (enforce all):
- State → Region (must match profiled mapping)
- Country → Currency (if present)
- City → valid for State
- Discount level → Profit direction (higher discount = lower/negative profit probability)
- ID year component → Date year (must match)
- Profiled numeric correlations (Sales↔Profit, etc.) — use correlation-aware generation
-
Calculated fields: Compute per-row AFTER generating all base fields. Apply the row-level interpretation of the formula (see Step 2D examples). Validate that computed values are reasonable (no division by zero, no infinite values). If row.sales = 0 and formula divides by sales, set result to NULL for that row.