Fabric Synthetic Data Generator — Reference
Generates realistic synthetic data locally as Parquet files, then uploads them to a Fabric lakehouse as Delta tables. Designed for populating test/dev lakehouses with star-schema-compatible data.
Data Domain Templates
The agent should ask the user what domain they want. Common domains and their table structures:
Retail / E-commerce
| Table |
Type |
Key Columns |
Sample Columns |
| customers |
dim |
customer_id |
name, email, phone, city, country, registration_date |
| products |
dim |
product_id |
name, category, brand, price, cost |
| stores |
dim |
store_id |
name, city, country, opening_date |
| dates |
dim |
date_id |
date, year, quarter, month, month_name, day, day_of_week, is_weekend |
| orders |
fact |
order_id |
customer_id, store_id, date_id, total_amount, discount, tax |
| order_lines |
fact |
order_line_id |
order_id, product_id, quantity, unit_price, line_total |
Healthcare
| Table |
Type |
Key Columns |
Sample Columns |
| patients |
dim |
patient_id |
name, dni, birth_date, gender, city, phone |
| doctors |
dim |
doctor_id |
name, speciality, hospital_id |
| hospitals |
dim |
hospital_id |
name, city, beds, founded_date |
| visits |
fact |
visit_id |
patient_id, doctor_id, hospital_id, visit_date, diagnosis_code, cost |
Airlines (like your existing data)
| Table |
Type |
Key Columns |
Sample Columns |
| airlines |
dim |
airline_id |
name, code, country |
| airports |
dim |
airport_id |
name, code, city, country, latitude, longitude |
| aircraft |
dim |
aircraft_id |
model, manufacturer, capacity, airline_id |
| passengers |
dim |
passenger_id |
name, email, phone, passport, nationality |
| flights |
fact |
flight_id |
airline_id, aircraft_id, origin_airport_id, dest_airport_id, departure_date, arrival_date, status, delay_minutes |
| bookings |
fact |
booking_id |
passenger_id, flight_id, booking_date, seat, class, price |
Citizen Incidents (Smart City / Municipal)
| Table |
Type |
Key Columns |
Sample Columns |
| citizens |
dim |
citizen_id |
name, dni, email, phone, birth_date, address, city, postal_code, registration_date |
| streets |
dim |
street_id |
street_name, street_type, district, neighborhood, postal_code, city, latitude, longitude |
| incident_types |
dim |
incident_type_id |
name, category, default_priority_id, sla_hours |
| priorities |
dim |
priority_id |
name, level, max_resolution_hours, color |
| statuses |
dim |
status_id |
name, is_final, sort_order |
| departments |
dim |
department_id |
name, manager_name, phone, email |
| technicians |
dim |
technician_id |
name, dni, department_id, role, hire_date, phone |
| dates |
dim |
date_id |
date, year, quarter, month, month_name, day, day_of_week, is_weekend, is_holiday |
| incidents |
fact |
incident_id |
citizen_id, street_id, incident_type_id, priority_id, status_id, department_id, assigned_technician_id, report_date_id, resolution_date_id, description, latitude, longitude, channel, resolution_time_hours, citizen_satisfaction, cost |
| incident_updates |
fact |
update_id |
incident_id, technician_id, update_date_id, previous_status_id, new_status_id, comment |
Custom
The user describes their tables and the agent designs the schema.
Questions to Ask the User
Before generating data, the agent must gather:
- Domain: What kind of data? (retail, healthcare, airlines, HR, finance, or custom)
- Tables: Which tables to generate? (all from template, or pick specific ones)
- Volume: How many rows per table? Suggest defaults:
- Dimensions: 100-10,000 rows
- Facts: 10,000-1,000,000 rows
- Date dimension: auto-calculated from date range
- Date range: What time period? (e.g., 2020-2024). Affects date dimension and fact table timestamps.
- Locale: Spanish (DNIs, Spanish names, phone +34) or international?
- Target lakehouse: Which workspace and lakehouse to upload to?
- Referential integrity: Should FK values always reference valid PKs? (default: yes)
Data Generation Rules
Real-Time-Friendly Schema Design
Because the agent may later be asked to deploy a real-time streaming notebook (see
"Real-Time Streaming (Optional Follow-Up)"), every schema must be designed from
the start so it can be streamed event-by-event without rework. Apply these rules
during Phase 2 (schema design), not as an afterthought:
Pick a clear "stream-able" fact. At least one fact table must represent a
discrete event that could plausibly happen in real time (an order placed, an
incident reported, a flight departing, a sensor reading, a booking made). If the
domain has no such fact, add one. Facts that are periodic aggregates (e.g.
monthly_sales_summary) are NOT stream-able — do not pick those as the only fact.
Event timestamp column. Every stream-able fact should have either:
- a
report_date_id / event_date_id FK to the date dimension, and/or
- a full-resolution
event_timestamp (timestamp) column.
The runtime notebook always injects an event_timestamp field into the emitted
JSON, so having one in the fact schema too is natural and avoids surprising the
downstream consumer.
Separate "event-creation" columns from "event-completion" columns. A real-time
event is emitted the moment it starts — completion data does not exist yet. Mark
the completion columns explicitly in the schema proposal with a (post-event)
tag so the user understands they will be NULL in the stream. Typical examples:
| Creation-time (always set) |
Post-event (often NULL in stream) |
| citizen_id, incident_type_id, priority_id, report_date_id, channel, latitude, longitude, description |
resolution_date_id, resolution_time_hours, citizen_satisfaction, cost |
| customer_id, product_id, store_id, order_date_id, quantity, unit_price |
shipped_date_id, delivered_date_id, return_reason, rating |
| passenger_id, flight_id, booking_date_id, seat, class, price |
check_in_time, boarding_time, satisfaction_score |
The fact should still include these columns (the static dataset fills them in), but
the real-time generator will emit them as None most of the time.
Status / lifecycle columns. If the fact has a status FK (status_id), the
statuses dimension must include at least one "initial" status (open,
pending, scheduled, booked) so new events have something valid to point to.
Avoid schemas where every status implies the event already finished.
Long-integer primary keys. Stream-able fact PKs must be int64 / long. The
streaming template generates PKs as int(time.time()*1000)*1000 + counter which
overflows anything smaller. Do NOT use UUID strings or composite PKs for a fact
the user might want to stream.
Dimensions must be cacheable in memory. Keep every dimension that a
stream-able fact references under ~50,000 rows. The streaming notebook loads each
dim with .limit(50000).toPandas(), so huge dims would get silently truncated.
If a "dimension" is naturally large (e.g. citizens = 10M), either (a) cap it at
50k for the demo, or (b) warn the user and tell them the stream will only sample
the first 50k PKs.
Categorical value lists must be explicit and reusable. For every categorical
column (channel, class, category, gender, …), record the exact list of
allowed values in Phase 2. The streaming notebook's generate_event() re-uses
the same list via random.choice([...]). Ad-hoc categories break parity between
the static table and the live stream.
Numeric ranges must be recorded. Same as categoricals — for every numeric
column that isn't a key, record the realistic (min, max) range in Phase 2. The
streaming notebook re-uses these ranges via random.uniform(min, max).
Avoid "post-hoc only" facts. Do not design a fact table whose rows can only
be computed after the event (e.g. customer_lifetime_value_snapshot,
daily_aggregate). These have no streaming semantics.
When presenting the schema to the user in Phase 2, annotate each fact column with
one of: (key), (FK → dim), (creation), (post-event), (enum: a/b/c),
(range: min..max). This makes it trivial to produce a correct RECORD_GENERATOR_BODY
later without re-interviewing the user.
Primary Keys
- Use sequential integers starting from 1:
1, 2, 3, ...
- Date dimension keys use
YYYYMMDD format: 20240101, 20240102, ...
Foreign Keys
- Randomly sample from the referenced dimension's PK values
- For time-series facts, distribute dates across the full date range (not uniform — use a slight recent-bias if realistic)
Realistic Values
- Names: For Spanish locale, generate peninsular Spanish names with two surnames (paterno + materno), e.g., "Antonio García López". Use common first names and surnames from Spain (not Latin American). Do NOT use
faker for Spanish names — it generates Latin American names.
- Emails: Derive from names:
firstname.lastname@domain.com
- Spanish DNIs: Generate valid DNIs using the mod-23 algorithm (8 digits + computed letter)
- Phone numbers: Spanish format
+34 6XX XXX XXX (mobile) or +34 9XX XXX XXX (landline)
- Prices/amounts: Use
random.uniform() with realistic ranges, round to 2 decimals
- Dates: Use
pandas.date_range() for date dimensions, random.choice() from date_id list for facts
- Categories: Use realistic category lists (not "Category A, B, C")
Spanish DNI Generation (valid checksums)
import random
DNI_LETTERS = "TRWAGMYFPDXBNJZSQVHLCKE"
def generate_valid_dni():
number = random.randint(10000000, 99999999)
letter = DNI_LETTERS[number % 23]
return f"{number}{letter}"
Date Dimension Generation
import pandas as pd
def generate_date_dimension(start_date, end_date):
dates = pd.date_range(start=start_date, end=end_date, freq='D')
df = pd.DataFrame({
'date_id': dates.strftime('%Y%m%d').astype(int),
'date': dates,
'year': dates.year,
'quarter': dates.quarter,
'month': dates.month,
'month_name': dates.strftime('%B'),
'day': dates.day,
'day_of_week': dates.strftime('%A'),
'is_weekend': dates.weekday >= 5,
})
return df
Output Format
Generate data as Parquet files (one per table). Save locally to:
./synthetic_data/{LAKEHOUSE_NAME}/{TIMESTAMP}/{table_name}.parquet
Use pandas + pyarrow for Parquet generation:
df.to_parquet(f"{output_dir}/{table_name}.parquet", index=False, engine="pyarrow")
Dirty Data Generation (Optional)
When requested, inject realistic data quality issues into the generated dataset. The user specifies a dirt percentage (e.g., 5% = 5% of rows per table are affected). Apply the following contamination types randomly across eligible columns:
Contamination Types
| Type |
Applies To |
What It Does |
| Null injection |
Any column |
Replace value with None / NaN |
| Empty string |
String columns |
Replace value with "" |
| Whitespace-only |
String columns |
Replace value with " " (spaces) |
| Exact duplicate rows |
Any table |
Duplicate a random row exactly |
| Near-duplicate rows |
Fact tables |
Duplicate a row but change one non-key column |
| Invalid DNI |
DNI columns |
Wrong checksum letter, missing letter, wrong format (e.g., 1234567X instead of 12345678X) |
| Invalid email |
Email columns |
Missing @, missing domain, spaces (e.g., juan garcia@, noarroba.com, juan @mail .com) |
| Invalid phone |
Phone columns |
Wrong digit count, invalid prefix (e.g., +34 1234, 612345, +33 612345678) |
| Comma decimal |
Numeric columns stored as string |
Replace . with , as decimal separator (e.g., "1234,56" instead of 1234.56) |
| String in numeric |
Numeric columns stored as string |
Replace value with non-numeric text (e.g., "N/A", "pending", "-", "#REF!") |
| Wrong date format |
Date columns stored as string |
Mix formats: some dd/MM/yyyy, some MM/dd/yyyy, some yyyy-MM-dd |
| Future/past dates |
Date columns |
Dates far in the future (2099) or before 1900 |
| Leading/trailing spaces |
String columns |
Add spaces around values (e.g., " Madrid ") |
| Case inconsistency |
String columns |
Random uppercase/lowercase (e.g., "MADRID", "madrid", "Madrid") |
How to Apply Dirt
- Calculate the number of dirty rows:
dirty_count = int(len(df) * dirt_percentage / 100)
- For each contamination type, randomly select a subset of rows and eligible columns
- Distribute contamination types roughly evenly — don't apply all dirt to one column
- Exact duplicate injection: append
int(dirty_count * 0.1) duplicated rows to the table
- For numeric columns that need comma decimals or string contamination: convert the column to string type first, then inject the dirty values
- Apply dirt AFTER generating clean data — never corrupt the primary keys or foreign keys (that would break referential integrity)
Real-Time Streaming (Optional Follow-Up)
After the static dataset is loaded into the lakehouse, the user can optionally deploy a
real-time streaming notebook that continuously emits synthetic fact records matching
the same schema and pushes them to a Fabric Eventstream custom endpoint (Event
Hub–compatible). This is useful for demoing real-time analytics, KQL databases,
Activator alerts, or Power BI direct-lake-on-event.
Notebook template
Use ./notebooks/realtime_stream.ipynb. It is a
templated PySpark notebook with the following placeholders that the agent must fill
in before deployment:
| Placeholder |
Filled by |
Meaning |
{{EVENTSTREAM_CONNECTION_STRING}} |
user |
Full Event Hub connection string of the Eventstream custom endpoint |
{{EVENTSTREAM_ENTITY_PATH}} |
user |
Event hub name (e.g. es_xxx) |
{{DURATION_MINUTES}} |
user |
How long the notebook streams before stopping (e.g. 10) |
{{EVENTS_PER_SECOND}} |
user |
Emission rate (e.g. 5) |
{{BATCH_SIZE}} |
user |
Events per Event Hub batch (default 20) |
{{FACT_TABLE_NAME}} |
agent |
The fact table whose schema is mirrored |
{{LAKEHOUSE_NAME}} |
agent |
Display-only label |
{{DIMENSION_TABLES}} |
agent |
Python list of dim tables the notebook will cache for FK sampling, e.g. ["passengers","airports"] |
{{PK_COLUMN}} |
agent |
Primary key column of the fact table (e.g. flight_id) |
{{RECORD_GENERATOR_BODY}} |
agent |
Python dict body that builds one event from the fact schema (see rules below) |
How the notebook works
- Installs
azure-eventhub via %pip install.
- Loads every dimension listed in
DIMENSION_TABLES into an in-memory list of dicts
(dim_cache[table_name]) so FKs and categorical values can be sampled fast.
- Calls
generate_event() (whose body the agent filled in) on each tick.
- Sends events in batches of
BATCH_SIZE via EventHubProducerClient until
DURATION_MINUTES elapses.
Rules for {{RECORD_GENERATOR_BODY}}
The agent must generate Python "key": value, entries (comma-terminated, indented
with 8 spaces to match the surrounding dict) for every column in the fact schema
except the PK and event_timestamp (those are emitted by the template itself).
Note: event_timestamp is an intentional streaming-only field added by the
template. It is NOT part of the fact table schema — it exists so downstream
Eventstream consumers (KQL DB, Activator, Power BI direct-lake-on-event) have a
reliable event time. Do not try to "match" the fact schema by removing it.
- FK columns: sample from the matching dimension cache:
"airline_id": random.choice(dim_cache["airlines"])["airline_id"],
- Date FKs to a date dimension: for a real-time stream the event is happening
now, so prefer
int(datetime.now(timezone.utc).strftime("%Y%m%d")) over sampling
from dim_cache["dates"]. Only sample from the date dim if the column semantically
refers to a past date (e.g. booking_date_id on a flight that departs in the future).
- Numeric amounts:
round(random.uniform(low, high), 2) with realistic ranges.
- Categorical strings:
random.choice([...realistic list...]).
- Status / enum columns: match the exact value set used in the static data. For a
new event, default to the "open / in progress / pending" status rather than a
terminal one.
- "Post-event" columns must default to
None. Any column that is only populated
after the event completes — e.g. resolution_date_id, resolution_time_hours,
citizen_satisfaction, cost, delay_minutes, actual_arrival, rating — should
be None for a freshly-emitted event (or None ~80% of the time with a realistic
value ~20% of the time to simulate late-arriving completions). Do NOT fill these
with random values for new events — it breaks the "live stream" semantics.
- Free-form timestamps inside the event (departure, resolution, etc.): derive from
datetime.now(timezone.utc) with small random offsets — never use static past dates.
- Type drift from dirty data is expected. If Phase 4 injected nulls into a long
column, pandas converts it to
float64/double in the stored Delta table. The
streaming generator should still emit the semantically correct type (integer for
IDs, None for unknown) — Spark/Eventstream will coerce. Do not try to emit floats
just because the stored column is double.
- Do NOT overwrite the template's
{{PK_COLUMN}} entry or event_timestamp.
Fact table selection
- If the dataset has exactly one fact table, use it.
- If there are multiple, ask the user which one to stream. Pick the one with the most
time-oriented semantics (e.g.
orders, incidents, flights, bookings).
- Only one fact table per notebook. If the user wants multiple streams, deploy multiple
notebooks (one per fact).
Parameters to ask the user
- Eventstream connection string (paste from Fabric custom endpoint)
- Event hub name (from the same endpoint panel)
- Duration in minutes (default: 10)
- Events per second (default: 5; cap around 50 for a single notebook)
- Batch size (default: 20)
- Fact table to stream (if more than one fact exists)
Deployment
Reuse the existing script commands — no new commands are needed:
- Fill placeholders in a local copy of
realtime_stream.ipynb under
./synthetic_data/{LAKEHOUSE_NAME}/{TIMESTAMP}/realtime_stream.ipynb
deploy-notebook <workspace_id> <lakehouse_id> <notebook_name> <ipynb_path>- Do not auto-run the notebook. The user may want to configure the Eventstream
side first, or run it manually from Fabric when ready. Report the notebook URL /
id and let the user trigger it.
- The user runs the notebook from Fabric when their Eventstream is ready. It will
self-terminate after
DURATION_MINUTES.
Upload Flow
The upload to Fabric is a two-step process per table. There are two paths depending on whether the lakehouse has schemas enabled.
Standard path (schemas NOT enabled)
- Upload Parquet to OneLake
Files/: Use the script's upload command
- Load as Delta table: Use the script's
load-table command
Schemas-enabled path (Load Table API fails)
When a lakehouse has schemas enabled, the Load Table API returns an error. Use the notebook-based approach instead:
- Upload Parquet to OneLake
Files/: Same upload command
- Generate a load notebook: Use
load-via-notebook — creates a PySpark .ipynb that reads all Parquet files from Files/synthetic_data/ and writes them as Delta tables via spark.write
- Deploy the notebook: Use
deploy-notebook — uploads to Fabric with lakehouse binding
- Run the notebook: Use
run-notebook — triggers execution on the Fabric Spark cluster
- Poll status: Use
status-notebook — wait until completed or failed
- Delete the notebook: Use
delete-notebook — clean up after successful run
How to detect schemas-enabled: If load-table fails with an error mentioning schemas, switch to the notebook path. Do NOT improvise — use the script commands above.
Script — fabric_synthetic_data.py
Use ./scripts/fabric_synthetic_data.py to upload and load data. Uses DefaultAzureCredential (Azure CLI).
Install dependencies (one-time):
pip install azure-identity requests pandas pyarrow faker
Upload a Parquet file to OneLake Files/
python scripts/fabric_synthetic_data.py upload <workspace_id> <lakehouse_id> <local_parquet_path> <remote_filename>
Uploads to Files/synthetic_data/{remote_filename} in the lakehouse.
Load a file as a Delta table (standard path)
python scripts/fabric_synthetic_data.py load-table <workspace_id> <lakehouse_id> <table_name> <relative_path>
Calls the Load Table API. If this fails due to schemas being enabled, switch to the notebook path below.
Generate a load notebook (schemas-enabled path)
python scripts/fabric_synthetic_data.py load-via-notebook <workspace_id> <lakehouse_id> <parquet_dir> <output_notebook.ipynb>
Scans <parquet_dir> for .parquet files and generates a single PySpark notebook that loads all of them as Delta tables. The notebook includes lakehouse binding metadata.
Deploy a load notebook to Fabric
python scripts/fabric_synthetic_data.py deploy-notebook <workspace_id> <lakehouse_id> <notebook_name> <ipynb_path>
Uploads the notebook with lakehouse binding so spark.write.saveAsTable() works.
Run a notebook
python scripts/fabric_synthetic_data.py run-notebook <workspace_id> <notebook_id>
Check notebook job status
python scripts/fabric_synthetic_data.py status-notebook <workspace_id> <notebook_id> <job_instance_id>
Delete a notebook
python scripts/fabric_synthetic_data.py delete-notebook <workspace_id> <notebook_id>
List existing tables
python scripts/fabric_synthetic_data.py list-tables <workspace_id> <lakehouse_id>
Delete a table
python scripts/fabric_synthetic_data.py delete-table <workspace_id> <lakehouse_id> <table_name>
Gotchas
- Schemas-enabled lakehouses break the Load Table API. If
load-table fails, the lakehouse likely has schemas enabled. Switch to the notebook path: load-via-notebook → deploy-notebook → run-notebook → status-notebook → delete-notebook. Do NOT try to improvise or create notebooks manually — use the script commands.
TIMESTAMP_NTZ is not supported by Fabric Delta. Pandas datetime64 columns get written to Parquet as TIMESTAMP_NTZ which Fabric rejects with Columns of the specified data types are not supported. The load-via-notebook script handles this automatically by casting TIMESTAMP_NTZ columns to DATE before writing. If generating data manually, use .dt.date to convert datetime to date objects before saving Parquet.list-tables and load-table both fail on schemas-enabled lakehouses. The entire Lakehouse Tables REST API is unsupported. Use MCP onelake_list_tables for listing and the notebook path for loading.- Upload then load — two steps. You cannot write directly to
Tables/. Upload Parquet to Files/ first, then load as Delta.
- Load Table
relativePath must start with Files/ and use the pattern Files/path/to/file.parquet. No leading slash.
- Table names must match
^[a-zA-Z_][a-zA-Z0-9_]{0,255}$. No spaces, no dashes, no special chars.
faker locale for Spanish is es_ES. Use Faker('es_ES') for Spanish names, addresses, etc.- Generate dimensions before facts so FK values can reference valid PKs.
- Date dimension should cover the full range of dates in fact tables. Generate it first, then sample date_ids for facts.
- Parquet preserves types. Use proper pandas dtypes:
int64 for IDs, float64 for amounts, datetime64 for dates, object for strings.
- Load Table API is async (202). The script polls automatically.
- OneLake upload has a file size limit. For large datasets (>100MB), consider splitting into multiple Parquet files and using
pathType: Folder.
- Real-time notebook needs
%pip install azure-eventhub. Fabric Spark pools don't ship it by default. The template already installs it — do not remove the magic.
- Eventstream connection string must include an
EntityPath or the event hub name must be passed separately. The template passes both and uses eventhub_name=EVENTSTREAM_ENTITY_PATH explicitly; this works whether or not EntityPath is in the connection string.
- Don't auto-run the real-time notebook. The user usually wants to configure consumers on the Eventstream side (KQL DB, Activator, Power BI) before events start flowing. Deploy only — let the user trigger it.
Global Rules
- Generate dimensions first, facts second. Facts reference dimensions via FKs.
- Always maintain referential integrity. Every FK value must exist in the referenced dimension.
- Use realistic data, not "test1, test2". Use faker for names, realistic ranges for numbers, proper date distributions.
- Parquet format only. No CSV — Parquet preserves types and is faster to load.
- Non-destructive by default. Use
mode: Overwrite for the specific table, but never delete unrelated tables.
1---2name: fabric-synthetic-data3description: Generate and upload synthetic data to Microsoft Fabric lakehouse tables. Covers star-schema generation, realistic fake data (names, dates, IDs, transactions), configurable row counts, referential integrity between tables, Parquet export, and OneLake upload. Use when: synthetic data, test data, fake data, sample data, generate tables, populate lakehouse, seed data, mock data.4---56# Fabric Synthetic Data Generator — Reference78Generates realistic synthetic data locally as Parquet files, then uploads them to a Fabric lakehouse as Delta tables. Designed for populating test/dev lakehouses with star-schema-compatible data.910## Data Domain Templates1112The agent should ask the user what domain they want. Common domains and their table structures:1314### Retail / E-commerce15| Table | Type | Key Columns | Sample Columns |16|-------|------|-------------|----------------|17| customers | dim | customer_id | name, email, phone, city, country, registration_date |18| products | dim | product_id | name, category, brand, price, cost |19| stores | dim | store_id | name, city, country, opening_date |20| dates | dim | date_id | date, year, quarter, month, month_name, day, day_of_week, is_weekend |21| orders | fact | order_id | customer_id, store_id, date_id, total_amount, discount, tax |22| order_lines | fact | order_line_id | order_id, product_id, quantity, unit_price, line_total |2324### Healthcare25| Table | Type | Key Columns | Sample Columns |26|-------|------|-------------|----------------|27| patients | dim | patient_id | name, dni, birth_date, gender, city, phone |28| doctors | dim | doctor_id | name, speciality, hospital_id |29| hospitals | dim | hospital_id | name, city, beds, founded_date |30| visits | fact | visit_id | patient_id, doctor_id, hospital_id, visit_date, diagnosis_code, cost |3132### Airlines (like your existing data)33| Table | Type | Key Columns | Sample Columns |34|-------|------|-------------|----------------|35| airlines | dim | airline_id | name, code, country |36| airports | dim | airport_id | name, code, city, country, latitude, longitude |37| aircraft | dim | aircraft_id | model, manufacturer, capacity, airline_id |38| passengers | dim | passenger_id | name, email, phone, passport, nationality |39| flights | fact | flight_id | airline_id, aircraft_id, origin_airport_id, dest_airport_id, departure_date, arrival_date, status, delay_minutes |40| bookings | fact | booking_id | passenger_id, flight_id, booking_date, seat, class, price |4142### Citizen Incidents (Smart City / Municipal)43| Table | Type | Key Columns | Sample Columns |44|-------|------|-------------|----------------|45| citizens | dim | citizen_id | name, dni, email, phone, birth_date, address, city, postal_code, registration_date |46| streets | dim | street_id | street_name, street_type, district, neighborhood, postal_code, city, latitude, longitude |47| incident_types | dim | incident_type_id | name, category, default_priority_id, sla_hours |48| priorities | dim | priority_id | name, level, max_resolution_hours, color |49| statuses | dim | status_id | name, is_final, sort_order |50| departments | dim | department_id | name, manager_name, phone, email |51| technicians | dim | technician_id | name, dni, department_id, role, hire_date, phone |52| dates | dim | date_id | date, year, quarter, month, month_name, day, day_of_week, is_weekend, is_holiday |53| incidents | fact | incident_id | citizen_id, street_id, incident_type_id, priority_id, status_id, department_id, assigned_technician_id, report_date_id, resolution_date_id, description, latitude, longitude, channel, resolution_time_hours, citizen_satisfaction, cost |54| incident_updates | fact | update_id | incident_id, technician_id, update_date_id, previous_status_id, new_status_id, comment |5556### Custom57The user describes their tables and the agent designs the schema.5859## Questions to Ask the User6061Before generating data, the agent must gather:62631. **Domain**: What kind of data? (retail, healthcare, airlines, HR, finance, or custom)642. **Tables**: Which tables to generate? (all from template, or pick specific ones)653. **Volume**: How many rows per table? Suggest defaults:66 - Dimensions: 100-10,000 rows67 - Facts: 10,000-1,000,000 rows68 - Date dimension: auto-calculated from date range694. **Date range**: What time period? (e.g., 2020-2024). Affects date dimension and fact table timestamps.705. **Locale**: Spanish (DNIs, Spanish names, phone +34) or international?716. **Target lakehouse**: Which workspace and lakehouse to upload to?727. **Referential integrity**: Should FK values always reference valid PKs? (default: yes)7374## Data Generation Rules7576### Real-Time-Friendly Schema Design7778Because the agent may later be asked to deploy a real-time streaming notebook (see79"Real-Time Streaming (Optional Follow-Up)"), **every schema must be designed from80the start so it can be streamed event-by-event without rework.** Apply these rules81during Phase 2 (schema design), not as an afterthought:82831. **Pick a clear "stream-able" fact.** At least one fact table must represent a84 discrete event that could plausibly happen in real time (an order placed, an85 incident reported, a flight departing, a sensor reading, a booking made). If the86 domain has no such fact, add one. Facts that are periodic aggregates (e.g.87 `monthly_sales_summary`) are NOT stream-able — do not pick those as the only fact.88892. **Event timestamp column.** Every stream-able fact should have either:90 - a `report_date_id` / `event_date_id` FK to the date dimension, **and/or**91 - a full-resolution `event_timestamp` (timestamp) column.9293 The runtime notebook always injects an `event_timestamp` field into the emitted94 JSON, so having one in the fact schema too is natural and avoids surprising the95 downstream consumer.96973. **Separate "event-creation" columns from "event-completion" columns.** A real-time98 event is emitted the moment it starts — completion data does not exist yet. Mark99 the completion columns explicitly in the schema proposal with a **(post-event)**100 tag so the user understands they will be `NULL` in the stream. Typical examples:101102 | Creation-time (always set) | Post-event (often NULL in stream) |103 |----------------------------|-----------------------------------|104 | citizen_id, incident_type_id, priority_id, report_date_id, channel, latitude, longitude, description | resolution_date_id, resolution_time_hours, citizen_satisfaction, cost |105 | customer_id, product_id, store_id, order_date_id, quantity, unit_price | shipped_date_id, delivered_date_id, return_reason, rating |106 | passenger_id, flight_id, booking_date_id, seat, class, price | check_in_time, boarding_time, satisfaction_score |107108 The fact should still include these columns (the static dataset fills them in), but109 the real-time generator will emit them as `None` most of the time.1101114. **Status / lifecycle columns.** If the fact has a status FK (`status_id`), the112 `statuses` dimension must include at least one "initial" status (`open`,113 `pending`, `scheduled`, `booked`) so new events have something valid to point to.114 Avoid schemas where every status implies the event already finished.1151165. **Long-integer primary keys.** Stream-able fact PKs must be `int64` / `long`. The117 streaming template generates PKs as `int(time.time()*1000)*1000 + counter` which118 overflows anything smaller. Do NOT use UUID strings or composite PKs for a fact119 the user might want to stream.1201216. **Dimensions must be cacheable in memory.** Keep every dimension that a122 stream-able fact references under ~50,000 rows. The streaming notebook loads each123 dim with `.limit(50000).toPandas()`, so huge dims would get silently truncated.124 If a "dimension" is naturally large (e.g. `citizens` = 10M), either (a) cap it at125 50k for the demo, or (b) warn the user and tell them the stream will only sample126 the first 50k PKs.1271287. **Categorical value lists must be explicit and reusable.** For every categorical129 column (`channel`, `class`, `category`, `gender`, …), record the **exact** list of130 allowed values in Phase 2. The streaming notebook's `generate_event()` re-uses131 the same list via `random.choice([...])`. Ad-hoc categories break parity between132 the static table and the live stream.1331348. **Numeric ranges must be recorded.** Same as categoricals — for every numeric135 column that isn't a key, record the realistic `(min, max)` range in Phase 2. The136 streaming notebook re-uses these ranges via `random.uniform(min, max)`.1371389. **Avoid "post-hoc only" facts.** Do not design a fact table whose rows can only139 be computed *after* the event (e.g. `customer_lifetime_value_snapshot`,140 `daily_aggregate`). These have no streaming semantics.141142When presenting the schema to the user in Phase 2, annotate each fact column with143one of: **(key)**, **(FK → dim)**, **(creation)**, **(post-event)**, **(enum: a/b/c)**,144**(range: min..max)**. This makes it trivial to produce a correct `RECORD_GENERATOR_BODY`145later without re-interviewing the user.146147### Primary Keys148- Use sequential integers starting from 1: `1, 2, 3, ...`149- Date dimension keys use `YYYYMMDD` format: `20240101, 20240102, ...`150151### Foreign Keys152- Randomly sample from the referenced dimension's PK values153- For time-series facts, distribute dates across the full date range (not uniform — use a slight recent-bias if realistic)154155### Realistic Values156- **Names**: For Spanish locale, generate peninsular Spanish names with two surnames (paterno + materno), e.g., "Antonio García López". Use common first names and surnames from Spain (not Latin American). Do NOT use `faker` for Spanish names — it generates Latin American names.157- **Emails**: Derive from names: `firstname.lastname@domain.com`158- **Spanish DNIs**: Generate valid DNIs using the mod-23 algorithm (8 digits + computed letter)159- **Phone numbers**: Spanish format `+34 6XX XXX XXX` (mobile) or `+34 9XX XXX XXX` (landline)160- **Prices/amounts**: Use `random.uniform()` with realistic ranges, round to 2 decimals161- **Dates**: Use `pandas.date_range()` for date dimensions, `random.choice()` from date_id list for facts162- **Categories**: Use realistic category lists (not "Category A, B, C")163164### Spanish DNI Generation (valid checksums)165```python166import random167168DNI_LETTERS = "TRWAGMYFPDXBNJZSQVHLCKE"169170def generate_valid_dni():171 number = random.randint(10000000, 99999999)172 letter = DNI_LETTERS[number % 23]173 return f"{number}{letter}"174```175176### Date Dimension Generation177```python178import pandas as pd179180def generate_date_dimension(start_date, end_date):181 dates = pd.date_range(start=start_date, end=end_date, freq='D')182 df = pd.DataFrame({183 'date_id': dates.strftime('%Y%m%d').astype(int),184 'date': dates,185 'year': dates.year,186 'quarter': dates.quarter,187 'month': dates.month,188 'month_name': dates.strftime('%B'),189 'day': dates.day,190 'day_of_week': dates.strftime('%A'),191 'is_weekend': dates.weekday >= 5,192 })193 return df194```195196## Output Format197198Generate data as **Parquet files** (one per table). Save locally to:199`./synthetic_data/{LAKEHOUSE_NAME}/{TIMESTAMP}/{table_name}.parquet`200201Use `pandas` + `pyarrow` for Parquet generation:202```python203df.to_parquet(f"{output_dir}/{table_name}.parquet", index=False, engine="pyarrow")204```205206## Dirty Data Generation (Optional)207208When requested, inject realistic data quality issues into the generated dataset. The user specifies a **dirt percentage** (e.g., 5% = 5% of rows per table are affected). Apply the following contamination types randomly across eligible columns:209210### Contamination Types211212| Type | Applies To | What It Does |213|------|-----------|-------------|214| **Null injection** | Any column | Replace value with `None` / `NaN` |215| **Empty string** | String columns | Replace value with `""` |216| **Whitespace-only** | String columns | Replace value with `" "` (spaces) |217| **Exact duplicate rows** | Any table | Duplicate a random row exactly |218| **Near-duplicate rows** | Fact tables | Duplicate a row but change one non-key column |219| **Invalid DNI** | DNI columns | Wrong checksum letter, missing letter, wrong format (e.g., `1234567X` instead of `12345678X`) |220| **Invalid email** | Email columns | Missing `@`, missing domain, spaces (e.g., `juan garcia@`, `noarroba.com`, `juan @mail .com`) |221| **Invalid phone** | Phone columns | Wrong digit count, invalid prefix (e.g., `+34 1234`, `612345`, `+33 612345678`) |222| **Comma decimal** | Numeric columns stored as string | Replace `.` with `,` as decimal separator (e.g., `"1234,56"` instead of `1234.56`) |223| **String in numeric** | Numeric columns stored as string | Replace value with non-numeric text (e.g., `"N/A"`, `"pending"`, `"-"`, `"#REF!"`) |224| **Wrong date format** | Date columns stored as string | Mix formats: some `dd/MM/yyyy`, some `MM/dd/yyyy`, some `yyyy-MM-dd` |225| **Future/past dates** | Date columns | Dates far in the future (2099) or before 1900 |226| **Leading/trailing spaces** | String columns | Add spaces around values (e.g., `" Madrid "`) |227| **Case inconsistency** | String columns | Random uppercase/lowercase (e.g., `"MADRID"`, `"madrid"`, `"Madrid"`) |228229### How to Apply Dirt2302311. Calculate the number of dirty rows: `dirty_count = int(len(df) * dirt_percentage / 100)`2322. For each contamination type, randomly select a subset of rows and eligible columns2333. Distribute contamination types roughly evenly — don't apply all dirt to one column2344. Exact duplicate injection: append `int(dirty_count * 0.1)` duplicated rows to the table2355. For numeric columns that need comma decimals or string contamination: convert the column to string type first, then inject the dirty values2366. Apply dirt AFTER generating clean data — never corrupt the primary keys or foreign keys (that would break referential integrity)237238## Real-Time Streaming (Optional Follow-Up)239240After the static dataset is loaded into the lakehouse, the user can optionally deploy a241**real-time streaming notebook** that continuously emits synthetic fact records matching242the same schema and pushes them to a **Fabric Eventstream** custom endpoint (Event243Hub–compatible). This is useful for demoing real-time analytics, KQL databases,244Activator alerts, or Power BI direct-lake-on-event.245246### Notebook template247248Use [./notebooks/realtime_stream.ipynb](./notebooks/realtime_stream.ipynb). It is a249templated PySpark notebook with the following placeholders that the agent must fill250in before deployment:251252| Placeholder | Filled by | Meaning |253|-------------|-----------|---------|254| `{{EVENTSTREAM_CONNECTION_STRING}}` | user | Full Event Hub connection string of the Eventstream custom endpoint |255| `{{EVENTSTREAM_ENTITY_PATH}}` | user | Event hub name (e.g. `es_xxx`) |256| `{{DURATION_MINUTES}}` | user | How long the notebook streams before stopping (e.g. `10`) |257| `{{EVENTS_PER_SECOND}}` | user | Emission rate (e.g. `5`) |258| `{{BATCH_SIZE}}` | user | Events per Event Hub batch (default `20`) |259| `{{FACT_TABLE_NAME}}` | agent | The fact table whose schema is mirrored |260| `{{LAKEHOUSE_NAME}}` | agent | Display-only label |261| `{{DIMENSION_TABLES}}` | agent | Python list of dim tables the notebook will cache for FK sampling, e.g. `["passengers","airports"]` |262| `{{PK_COLUMN}}` | agent | Primary key column of the fact table (e.g. `flight_id`) |263| `{{RECORD_GENERATOR_BODY}}` | agent | Python dict body that builds one event from the fact schema (see rules below) |264265### How the notebook works2662671. Installs `azure-eventhub` via `%pip install`.2682. Loads every dimension listed in `DIMENSION_TABLES` into an in-memory list of dicts269 (`dim_cache[table_name]`) so FKs and categorical values can be sampled fast.2703. Calls `generate_event()` (whose body the agent filled in) on each tick.2714. Sends events in batches of `BATCH_SIZE` via `EventHubProducerClient` until272 `DURATION_MINUTES` elapses.273274### Rules for `{{RECORD_GENERATOR_BODY}}`275276The agent must generate Python `"key": value,` entries (comma-terminated, indented277with 8 spaces to match the surrounding dict) for every column in the fact schema278**except** the PK and `event_timestamp` (those are emitted by the template itself).279280> **Note**: `event_timestamp` is an intentional streaming-only field added by the281> template. It is NOT part of the fact table schema — it exists so downstream282> Eventstream consumers (KQL DB, Activator, Power BI direct-lake-on-event) have a283> reliable event time. Do not try to "match" the fact schema by removing it.284285- **FK columns**: sample from the matching dimension cache:286 `"airline_id": random.choice(dim_cache["airlines"])["airline_id"],`287- **Date FKs to a date dimension**: for a real-time stream the event is happening288 **now**, so prefer `int(datetime.now(timezone.utc).strftime("%Y%m%d"))` over sampling289 from `dim_cache["dates"]`. Only sample from the date dim if the column semantically290 refers to a past date (e.g. `booking_date_id` on a flight that departs in the future).291- **Numeric amounts**: `round(random.uniform(low, high), 2)` with realistic ranges.292- **Categorical strings**: `random.choice([...realistic list...])`.293- **Status / enum columns**: match the exact value set used in the static data. For a294 new event, default to the "open / in progress / pending" status rather than a295 terminal one.296- **"Post-event" columns must default to `None`.** Any column that is only populated297 *after* the event completes — e.g. `resolution_date_id`, `resolution_time_hours`,298 `citizen_satisfaction`, `cost`, `delay_minutes`, `actual_arrival`, `rating` — should299 be `None` for a freshly-emitted event (or `None` ~80% of the time with a realistic300 value ~20% of the time to simulate late-arriving completions). Do NOT fill these301 with random values for new events — it breaks the "live stream" semantics.302- **Free-form timestamps** inside the event (departure, resolution, etc.): derive from303 `datetime.now(timezone.utc)` with small random offsets — never use static past dates.304- **Type drift from dirty data is expected.** If Phase 4 injected nulls into a long305 column, pandas converts it to `float64`/`double` in the stored Delta table. The306 streaming generator should still emit the **semantically correct** type (integer for307 IDs, `None` for unknown) — Spark/Eventstream will coerce. Do not try to emit floats308 just because the stored column is `double`.309- Do NOT overwrite the template's `{{PK_COLUMN}}` entry or `event_timestamp`.310311### Fact table selection312313- If the dataset has exactly one fact table, use it.314- If there are multiple, ask the user which one to stream. Pick the one with the most315 time-oriented semantics (e.g. `orders`, `incidents`, `flights`, `bookings`).316- Only one fact table per notebook. If the user wants multiple streams, deploy multiple317 notebooks (one per fact).318319### Parameters to ask the user3203211. **Eventstream connection string** (paste from Fabric custom endpoint)3222. **Event hub name** (from the same endpoint panel)3233. **Duration in minutes** (default: 10)3244. **Events per second** (default: 5; cap around 50 for a single notebook)3255. **Batch size** (default: 20)3266. **Fact table to stream** (if more than one fact exists)327328### Deployment329330Reuse the existing script commands — no new commands are needed:3313321. Fill placeholders in a local copy of `realtime_stream.ipynb` under333 `./synthetic_data/{LAKEHOUSE_NAME}/{TIMESTAMP}/realtime_stream.ipynb`3342. `deploy-notebook <workspace_id> <lakehouse_id> <notebook_name> <ipynb_path>`3353. Do **not** auto-run the notebook. The user may want to configure the Eventstream336 side first, or run it manually from Fabric when ready. Report the notebook URL /337 id and let the user trigger it.3384. The user runs the notebook from Fabric when their Eventstream is ready. It will339 self-terminate after `DURATION_MINUTES`.340341## Upload Flow342343The upload to Fabric is a two-step process per table. There are **two paths** depending on whether the lakehouse has schemas enabled.344345### Standard path (schemas NOT enabled)3463471. **Upload Parquet to OneLake `Files/`**: Use the script's `upload` command3482. **Load as Delta table**: Use the script's `load-table` command349350### Schemas-enabled path (Load Table API fails)351352When a lakehouse has schemas enabled, the Load Table API returns an error. Use the notebook-based approach instead:3533541. **Upload Parquet to OneLake `Files/`**: Same `upload` command3552. **Generate a load notebook**: Use `load-via-notebook` — creates a PySpark `.ipynb` that reads all Parquet files from `Files/synthetic_data/` and writes them as Delta tables via `spark.write`3563. **Deploy the notebook**: Use `deploy-notebook` — uploads to Fabric with lakehouse binding3574. **Run the notebook**: Use `run-notebook` — triggers execution on the Fabric Spark cluster3585. **Poll status**: Use `status-notebook` — wait until completed or failed3596. **Delete the notebook**: Use `delete-notebook` — clean up after successful run360361**How to detect schemas-enabled**: If `load-table` fails with an error mentioning schemas, switch to the notebook path. Do NOT improvise — use the script commands above.362363## Script — fabric_synthetic_data.py364365Use [./scripts/fabric_synthetic_data.py](./scripts/fabric_synthetic_data.py) to upload and load data. Uses `DefaultAzureCredential` (Azure CLI).366367**Install dependencies** (one-time):368```bash369pip install azure-identity requests pandas pyarrow faker370```371372### Upload a Parquet file to OneLake Files/373374```bash375python scripts/fabric_synthetic_data.py upload <workspace_id> <lakehouse_id> <local_parquet_path> <remote_filename>376```377378Uploads to `Files/synthetic_data/{remote_filename}` in the lakehouse.379380### Load a file as a Delta table (standard path)381382```bash383python scripts/fabric_synthetic_data.py load-table <workspace_id> <lakehouse_id> <table_name> <relative_path>384```385386Calls the Load Table API. If this fails due to schemas being enabled, switch to the notebook path below.387388### Generate a load notebook (schemas-enabled path)389390```bash391python scripts/fabric_synthetic_data.py load-via-notebook <workspace_id> <lakehouse_id> <parquet_dir> <output_notebook.ipynb>392```393394Scans `<parquet_dir>` for `.parquet` files and generates a single PySpark notebook that loads all of them as Delta tables. The notebook includes lakehouse binding metadata.395396### Deploy a load notebook to Fabric397398```bash399python scripts/fabric_synthetic_data.py deploy-notebook <workspace_id> <lakehouse_id> <notebook_name> <ipynb_path>400```401402Uploads the notebook with lakehouse binding so `spark.write.saveAsTable()` works.403404### Run a notebook405406```bash407python scripts/fabric_synthetic_data.py run-notebook <workspace_id> <notebook_id>408```409410### Check notebook job status411412```bash413python scripts/fabric_synthetic_data.py status-notebook <workspace_id> <notebook_id> <job_instance_id>414```415416### Delete a notebook417418```bash419python scripts/fabric_synthetic_data.py delete-notebook <workspace_id> <notebook_id>420```421422### List existing tables423424```bash425python scripts/fabric_synthetic_data.py list-tables <workspace_id> <lakehouse_id>426```427428### Delete a table429430```bash431python scripts/fabric_synthetic_data.py delete-table <workspace_id> <lakehouse_id> <table_name>432```433434## Gotchas435436- **Schemas-enabled lakehouses break the Load Table API.** If `load-table` fails, the lakehouse likely has schemas enabled. Switch to the notebook path: `load-via-notebook` → `deploy-notebook` → `run-notebook` → `status-notebook` → `delete-notebook`. Do NOT try to improvise or create notebooks manually — use the script commands.437- **`TIMESTAMP_NTZ` is not supported by Fabric Delta.** Pandas `datetime64` columns get written to Parquet as `TIMESTAMP_NTZ` which Fabric rejects with `Columns of the specified data types are not supported`. The `load-via-notebook` script handles this automatically by casting `TIMESTAMP_NTZ` columns to `DATE` before writing. If generating data manually, use `.dt.date` to convert datetime to date objects before saving Parquet.438- **`list-tables` and `load-table` both fail on schemas-enabled lakehouses.** The entire Lakehouse Tables REST API is unsupported. Use MCP `onelake_list_tables` for listing and the notebook path for loading.439- **Upload then load — two steps.** You cannot write directly to `Tables/`. Upload Parquet to `Files/` first, then load as Delta.440- **Load Table `relativePath` must start with `Files/`** and use the pattern `Files/path/to/file.parquet`. No leading slash.441- **Table names must match `^[a-zA-Z_][a-zA-Z0-9_]{0,255}$`**. No spaces, no dashes, no special chars.442- **`faker` locale for Spanish is `es_ES`**. Use `Faker('es_ES')` for Spanish names, addresses, etc.443- **Generate dimensions before facts** so FK values can reference valid PKs.444- **Date dimension should cover the full range** of dates in fact tables. Generate it first, then sample date_ids for facts.445- **Parquet preserves types.** Use proper pandas dtypes: `int64` for IDs, `float64` for amounts, `datetime64` for dates, `object` for strings.446- **Load Table API is async (202).** The script polls automatically.447- **OneLake upload has a file size limit.** For large datasets (>100MB), consider splitting into multiple Parquet files and using `pathType: Folder`.448- **Real-time notebook needs `%pip install azure-eventhub`.** Fabric Spark pools don't ship it by default. The template already installs it — do not remove the magic.449- **Eventstream connection string must include an `EntityPath` or the event hub name must be passed separately.** The template passes both and uses `eventhub_name=EVENTSTREAM_ENTITY_PATH` explicitly; this works whether or not `EntityPath` is in the connection string.450- **Don't auto-run the real-time notebook.** The user usually wants to configure consumers on the Eventstream side (KQL DB, Activator, Power BI) before events start flowing. Deploy only — let the user trigger it.451452## Global Rules453454- **Generate dimensions first, facts second.** Facts reference dimensions via FKs.455- **Always maintain referential integrity.** Every FK value must exist in the referenced dimension.456- **Use realistic data, not "test1, test2".** Use faker for names, realistic ranges for numbers, proper date distributions.457- **Parquet format only.** No CSV — Parquet preserves types and is faster to load.458- **Non-destructive by default.** Use `mode: Overwrite` for the specific table, but never delete unrelated tables.
drhelius