# Mock Data Generation

> Use when generating synthetic CSV datasets from a semantic model schema for local development. Triggers: "generate mock data", "create CSV", "generate test data", "fake data", "date dimension CSV", "referential integrity", "update TMDL partitions", "data generation script", "pandas faker", "seed data".

- Skill: `natalinio/mock-data-generation` (Agent Skill)
- Install (CLI): `npx skillmds@latest add natalinio/mock-data-generation`
- Raw SKILL.md: https://api.skillmd.com/api/skills/natalinio/mock-data-generation/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: natalinio (https://skillmd.com/u/natalinio)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/natalinio/mock-data-generation

---


# Skill: Mock Data Generation

## Purpose
Generate realistic mock data as CSV files to validate the PBIP semantic model locally in Power BI Desktop. Use SDV probabilistic synthesis when the runtime supports it; otherwise fall back to deterministic generation.

## Input / Output

| | |
|---|---|
| **Input** | TMDL table files (column names and data types), optional sample data from user |
| **Output** | `<ProjectName>/scripts/generate_mock_data.py`, `<ProjectName>/data/*.csv`, updated TMDL partitions |

## Python Environment Setup

Before generating data, verify the Python virtual environment is set up:

### Prerequisites
The user must have **Python 3.10+** installed on their local machine.

### Setup Commands
```powershell
# Navigate to the repository root
cd <repository-root>

# Create virtual environment (if not already done)
python -m venv .venv

# Activate virtual environment (Windows PowerShell)
.\.venv\Scripts\Activate.ps1

# Install required packages (default cross-step baseline)
pip install -r requirements.txt

# Optional: install SDV core package only if you explicitly want probabilistic synthesis
# and you accept that some environments may still be unable to load the full SDV stack.
python -m pip install sdv==1.34.3 --no-deps
```

### Verify Installation
```powershell
python -c "import pandas; import faker; print('OK')"
```

Optional SDV verification:
```powershell
python -c "import sdv; print('SDV core package available')"
```

## Script Generation Rules

### Technology
- Use **Python** with `pandas` and `faker` libraries.
- Use `sdv` only when the local runtime can import it successfully; otherwise the generator must continue with deterministic fallback logic.
- Use `faker` only to bootstrap compact seed datasets when no source extracts are available.
- Generate ONE script file: `<ProjectName>/scripts/generate_mock_data.py`
- Output CSV files to `<ProjectName>/data/` folder.

### SDV Strategy for Star Schema Models
Use a star-schema-oriented generation flow optimized for performance and referential integrity:

1. Generate deterministic seed datasets for conformed dimensions:
    - `Dim_Date` MUST remain fully programmatic.
    - Low-cardinality dimensions may use controlled lists or `faker` to create a compact bootstrap sample.
2. Build SDV metadata from the seed tables and explicitly define primary keys and fact-to-dimension relationships.
3. Prefer a **hybrid SDV strategy** for performance:
    - Use SDV single-table synthesizers for dimensions.
    - Use an SDV probabilistic synthesizer for each fact table with FK columns treated as categorical business keys.
4. Use `HMASynthesizer` only when preserving cross-table dependency structure is more important than runtime.
5. After sampling, run post-processing to enforce integer keys, decimal precision, null rules, and orphan-key repair.

This hybrid pattern is preferred for Power BI star schemas because dimensions are usually small and stable, while fact tables require faster generation at higher volumes.

### Data Quality Requirements
1. **Referential Integrity**: ALL Foreign Keys in Fact tables MUST exist as Primary Keys in the corresponding Dimension tables.
2. **Data Type Consistency**: Generated data types must EXACTLY match the TMDL `dataType` definitions.
3. **Realistic Values**: Use SDV probabilistic synthesis to learn realistic value distributions from the bootstrap seed data. Use `faker` only to create the minimal seed sample when needed.
4. **Volume**: Generate sufficient data for meaningful visuals:
   - Dimension tables: 20-100 rows (depending on cardinality)
   - Fact tables: 500-2000 rows
   - Date dimension: Full fiscal year(s) — one row per day

### Date Dimension Generation
The `Dim_Date` table MUST be generated programmatically covering the required fiscal year range:
```python
import pandas as pd

def generate_dim_date(start_date, end_date, fiscal_year_start_month=7):
    dates = pd.date_range(start=start_date, end=end_date, freq='D')
    df = pd.DataFrame({'Date': dates})
    df['DateKey'] = df['Date'].dt.strftime('%Y%m%d').astype(int)
    df['Year'] = df['Date'].dt.year.astype(str)
    df['Quarter'] = 'Q' + df['Date'].dt.quarter.astype(str)
    df['Month'] = df['Date'].dt.month
    df['MonthName'] = df['Date'].dt.strftime('%B')
    df['DayOfWeek'] = df['Date'].dt.dayofweek + 1
    df['DayName'] = df['Date'].dt.strftime('%A')
    df['IsWeekend'] = df['DayOfWeek'].isin([6, 7])
    # Fiscal year logic
    df['FiscalYear'] = df['Date'].apply(
        lambda d: f"FY{d.year + 1}" if d.month >= fiscal_year_start_month else f"FY{d.year}"
    )
    df['FiscalMonth'] = df['Date'].apply(
        lambda d: ((d.month - fiscal_year_start_month) % 12) + 1
    )
    df['FiscalQuarter'] = 'FQ' + ((df['FiscalMonth'] - 1) // 3 + 1).astype(str)
    return df
```

### SDV Metadata and Sampling Pattern
```python
from sdv.metadata import MultiTableMetadata, SingleTableMetadata
from sdv.single_table import GaussianCopulaSynthesizer

def build_metadata(dim_date_seed, dim_customer_seed, dim_area_seed, fact_sales_seed):
    metadata = MultiTableMetadata()
    metadata.detect_table_from_dataframe(table_name='Dim_Date', data=dim_date_seed)
    metadata.detect_table_from_dataframe(table_name='Dim_Customer', data=dim_customer_seed)
    metadata.detect_table_from_dataframe(table_name='Dim_Area', data=dim_area_seed)
    metadata.detect_table_from_dataframe(table_name='Fact_Sales', data=fact_sales_seed)

    metadata.set_primary_key(table_name='Dim_Date', column_name='DateKey')
    metadata.set_primary_key(table_name='Dim_Customer', column_name='CustomerKey')
    metadata.set_primary_key(table_name='Dim_Area', column_name='AreaKey')
    metadata.set_primary_key(table_name='Fact_Sales', column_name='SalesKey')

    metadata.add_relationship(
        parent_table_name='Dim_Date',
        parent_primary_key='DateKey',
        child_table_name='Fact_Sales',
        child_foreign_key='DateKey',
    )
    metadata.add_relationship(
        parent_table_name='Dim_Customer',
        parent_primary_key='CustomerKey',
        child_table_name='Fact_Sales',
        child_foreign_key='CustomerKey',
    )
    metadata.add_relationship(
        parent_table_name='Dim_Area',
        parent_primary_key='AreaKey',
        child_table_name='Fact_Sales',
        child_foreign_key='AreaKey',
    )
    return metadata

def synthesize_dimension(seed_df, num_rows):
    metadata = SingleTableMetadata()
    metadata.detect_from_dataframe(data=seed_df)
    synthesizer = GaussianCopulaSynthesizer(metadata)
    synthesizer.fit(seed_df)
    return synthesizer.sample(num_rows=num_rows)
```

### Fact Table Generation Pattern
```python
from sdv.metadata import SingleTableMetadata
from sdv.single_table import GaussianCopulaSynthesizer

def generate_fact_sales(dim_date, dim_customer, dim_area, fact_seed, num_rows=1000):
    fact_metadata = SingleTableMetadata()
    fact_metadata.detect_from_dataframe(data=fact_seed)
    synthesizer = GaussianCopulaSynthesizer(fact_metadata)
    synthesizer.fit(fact_seed)
    df = synthesizer.sample(num_rows=num_rows)

    df['DateKey'] = df['DateKey'].where(df['DateKey'].isin(dim_date['DateKey']), dim_date['DateKey'].sample(len(df), replace=True).to_numpy())
    df['CustomerKey'] = df['CustomerKey'].where(df['CustomerKey'].isin(dim_customer['CustomerKey']), dim_customer['CustomerKey'].sample(len(df), replace=True).to_numpy())
    df['AreaKey'] = df['AreaKey'].where(df['AreaKey'].isin(dim_area['AreaKey']), dim_area['AreaKey'].sample(len(df), replace=True).to_numpy())
    df['AdjustedProfitLC'] = df[['AdjustedProfitLC', 'SalesAmountLC']].min(axis=1)

    return df
```

### When to Use `HMASynthesizer`
- Use `HMASynthesizer` when you already have a representative relational seed dataset and must preserve multi-table correlations across the entire star schema.
- Do NOT make `HMASynthesizer` the default for Step 05 when the goal is fast local mock generation for Power BI validation.
- If `HMASynthesizer` is used, keep the seed dataset compact and sample up to the target row counts instead of fitting on large bootstrap datasets.

### CSV Output
- Files go to `<ProjectName>/data/` folder
- Use comma delimiter, **UTF-8 encoding WITHOUT BOM**, no index
- Filename convention: lowercase table name (e.g., `dim_date.csv`, `fact_sales.csv`)

```python
df.to_csv('<ProjectName>/data/dim_date.csv', index=False, encoding='utf-8')
```

**CRITICAL**: Pandas `encoding='utf-8'` generates UTF-8 **without BOM** by default, which is the format required by Power BI Desktop for TMDL parsing. Do NOT use other tools (like PowerShell `WriteAllText`) to modify CSV files after generation, as they may add a BOM and cause parsing errors.

## TMDL Partition Update

After generating CSVs, update each table's TMDL partition `source` expression to point to the correct CSV path.

The M expression should use **absolute paths** for local development:
```
source =
	let
		Source = Csv.Document(File.Contents("C:\path\to\repo\<ProjectName>\data\dim_date.csv"), [Delimiter = ",", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
		PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
	in
		PromotedHeaders
```

Alternatively, use a **parameterized path** via `expressions.tmdl`:
```
expression DataPath = "C:\path\to\repo\<ProjectName>\data" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]
```

Then reference in partition source:
```
Source = Csv.Document(File.Contents(DataPath & "\dim_date.csv"), [Delimiter = ",", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None])
```

## Lift and Shift Strategy — Transition to Production Data Source

**Important**: CSV files are intended as **temporary mock data** for local development and validation. In production, the semantic model will connect directly to a database (SQL Server, Azure SQL, Fabric Lakehouse, etc.).

### Recommended Approach for Easy Migration

#### Option 1: Parameterized Source (Recommended)
Create a shared M parameter in `expressions.tmdl` that controls the data source type:

```tmdl
expression SourceType = "CSV" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

expression DataPath = "C:\path\to\repo\<ProjectName>\data" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

expression DatabaseConnectionString = "Server=myserver;Database=mydb" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = false]
```

Then in each table partition, use conditional logic:
```m
source =
	let
		CSVSource = Csv.Document(File.Contents(DataPath & "\dim_date.csv"), [Delimiter = ",", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
		DatabaseSource = Sql.Database("myserver", "mydb"){[Schema="dbo",Item="Dim_Date"]}[Data],
		Source = if SourceType = "CSV" then CSVSource else DatabaseSource,
		PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
	in
		PromotedHeaders
```

**Advantage**: Switch between CSV and database by changing a single parameter value.

#### Option 2: Replace Partition Source (Simpler)
When ready for production, replace the entire partition `source` expression in each table's TMDL file:

**Before (CSV mock)**:
```m
source =
	let
		Source = Csv.Document(File.Contents("C:\...\<ProjectName>\data\dim_date.csv"), ...),
```

**After (SQL Database)**:
```m
source =
	let
		Source = Sql.Database("myserver.database.windows.net", "mydatabase"){[Schema="dbo",Item="Dim_Date"]}[Data],
```

**Advantage**: Simpler partition expressions (no conditional logic). Use Power BI Desktop's "Transform Data" UI to reconnect to the database, then save the TMDL changes.

#### Option 3: Fabric Lakehouse (Direct Lake)
For Fabric-based deployments, the partition mode changes from `import` to `directLake`:

```tmdl
partition Dim_Date = entity
	mode: directLake
	source
		schemaName: dbo
		tableName: Dim_Date
		expressionSource: DatabaseQuery
```

**Note**: This requires the semantic model to be published to a Fabric workspace and connected to a Lakehouse.

### Best Practice Recommendation
- Use **Option 1** (parameterized source) during Step 05 if you anticipate frequent switching between mock and real data during development.
- Use **Option 2** (replace partition source) for simpler models where the transition happens only once before production deployment.
- For Fabric deployments, plan for **Option 3** (Direct Lake) from the beginning if your target data platform is Fabric Lakehouse.

## ⚠️ Data Realism: Temporal Trend Requirement (CRITICAL)

**Flat data will cause the report agent to fail.** When generating data for time-series dashboards:

- Metrics that model business **improvement over time** (e.g., conversion rate, satisfaction score, adoption %) MUST show a **directional trend across periods** — not random noise.
- Random seeding alone produces non-monotonic variation across periods, which breaks bar charts and KPI delta visuals.

**Fix pattern — enforce a target per period using weight-based assignment:**
```python
# Define target KPI% per period: directional trend P1→P4
# Replace 'Period', 'WeightColumn', 'FlagColumn' with actual column names from the model
period_col = 'Period'          # e.g. 'Quarter', 'Month', 'FiscalPeriod'
weight_col = 'WeightColumn'    # e.g. 'Amount', 'Revenue', 'Count'
flag_col   = 'FlagColumn'      # boolean flag driving the KPI numerator

targets = {
    'P1': 0.20,   # replace with business-driven target values
    'P2': 0.25,
    'P3': 0.30,
    'P4': 0.35,
}

for period, target in targets.items():
    idx = fact[fact[period_col] == period].index
    total = fact.loc[idx, weight_col].sum()
    target_value = total * target
    fact.loc[idx, flag_col] = 0  # reset
    cumsum = 0
    for row_idx, row in fact.loc[idx].sort_values(weight_col, ascending=False).iterrows():
        if cumsum < target_value:
            fact.loc[row_idx, flag_col] = 1
            cumsum += row[weight_col]
        else:
            break
```

**Business logic rules** (enforce after flag assignment):
- If your model has dependent flags (e.g., flag A implies flag B), enforce consistency after assignment
- Validate with: `assert len(fact[(fact[flag_col]==1) & (fact[dependent_flag_col]==0)]) == 0`

## ⚠️ Python Environment Fallback (Windows)

On Windows, the `.venv` path may not be directly accessible via PowerShell `&` operator if the repo path contains spaces. Use system Python as a reliable fallback:

```powershell
# Preferred: system Python (avoids space-in-path issues)
$py = (Get-Command python -ErrorAction SilentlyContinue)?.Source
# Fallback chain if venv is needed:
$candidates = @(
    "$repoRoot\.venv\Scripts\python.exe",
    "C:\Users\$env:USERNAME\AppData\Local\Programs\Python\Python310\python.exe"
)
$py = $candidates | Where-Object { $_ -and (Test-Path $_) } | Select-Object -First 1
```

## Validation Before Output
- [ ] All FK values in fact tables exist in corresponding dimension PKs
- [ ] CSV column names match TMDL `sourceColumn` values exactly
- [ ] Data types are consistent (integers for keys, decimals for amounts)
- [ ] Date dimension covers the full required date range
- [ ] SDV metadata declares PK/FK relationships explicitly
- [ ] Post-processing repairs any orphan FK values before CSV export
- [ ] CSV files are comma-delimited, UTF-8 encoded
- [ ] Script runs without errors in the `.venv` environment
- [ ] **Period trend is directional** (not flat/random) for all time-series KPIs
- [ ] **Business flag rules enforced** (dependent flags are consistent, no contradictions)
- [ ] Print validation summary: total rows, KPI% per period, flag distribution

Save all generated artifacts to disk.