Synthetic Data Agent · 22 May 2026

🤖 SDA 02: Designing the Synthetic Data Agent

In the last article, we committed to building a Synthetic Data Agent on Databricks: one that inspects real governed data and generates…

27 min read

Synthetic DataDatabricksTroubleshootingStreaming

0% read·27 min in this view

1. No, the Agent Is Not the Whole System

Before writing code like caffeinated raccoons, let’s build on the goal set in SDA 01 and design this properly.

The agent is not magic. It’s not the data generator. It’s not the database. And it’s definitely not our governance model wearing a hoodie and pretending to be innovative.

The agent is the orchestrator. That distinction matters.

The Agent Has a Job, Not a Superpower

The agent interprets requirements, inspects data, routes workflows through tools, tracks state, and explains results. That is already enough.

We don’t want one massive prompt handling profiling, relationship detection, row generation, privacy constraints, and publishing at once.

Instead of acting like a wizard, the agent coordinates the workflow: analyzes user requests, calls specialized tools, tracks operational state, checks intermediate results, asks for missing context and explains outcomes transparently.

Tools Do the Boring, Deterministic Work

A serious framework separates reasoning from execution.

The agent orchestrates. Tools calculate facts. We don’t want an LLM guessing null rates from five sample rows or inventing relationships because two columns contain id.

Specialized tools should handle:

UC metadata reading: schemas, tags, constraints.
Profiling and pattern detection: distributions, null behavior, row-level patterns.
Relationship discovery: PK/FK overlap, dependencies, confidence.
Generation and validation: execution plans, synthetic records, quality checks.
Publishing and observability: Unity Catalog outputs and dashboards.

The agent decides when to use them. The tools decide what is actually true.

Unity Catalog Is the Source of Governance

Unity Catalog is the governed source of truth, not just storage.

The agent must start there, inspecting catalogs, columns, constraints, ownership, sensitivity tags, and even messy real-world gems like flag_2_final_old.

UC tells the agent what can be read, what should be protected, and where outputs can safely land. Without it, the agent is just wandering the lakehouse with a flashlight and too much confidence.

State Is Not Optional

The agent needs operational memory. If every run starts from zero, debugging becomes archaeology: the bad kind, digging through old cell outputs to discover why a key column suddenly became nullable.

Persistent metadata, stored in Lakebase or Unity Catalog tables, should track:

Scope and context: selected catalogs, schemas, tables, and user constraints.
Extracted knowledge: profiling results, relationship graphs, generation plans.
Run history: created synthetic tables, validation outputs, warnings, and limitations.

Validation Keeps the Agent Honest

Validation is the adult in the room. Generators can create beautiful nonsense that looks fine in a screenshot but collapses in pipelines. Realistic synthetic data needs statistical proof.

The validator checks:

Structure: schemas, row counts, key uniqueness, FK integrity.
Statistics: null rates, categorical frequencies, distributions, correlations.
Context: date ranges, business rules, rare scenarios, privacy barriers.

Without validation, we are not building a data solution. We are aiming a fake data cannon at the lakehouse.

The Real Architecture

The foundation is a crisp division of labor:

Agent: reasoning, routing, explaining.
Tools: profiling, generation, calculations.
Unity Catalog: governance, sources, targets, permissions.
Lakebase/metadata tables: state, lineage, run history.
Validation: quality gates and silent judgment.

This separation gives us a testable, reproducible pipeline instead of an expensive random number generator with great branding.

2. Recap the Mission

Before assembling our nerdy Avengers team of tools, let’s recap the mission: build a Synthetic Data Agent on Databricks that inspects governed data in Unity Catalog, understands how it behaves, and generates realistic synthetic datasets.

The Use Case

The agent gives data teams a secure, automated way to generate scalable synthetic data for testing, demos, development, and AI experimentation, without handing sensitive production data to everyone with a laptop and a dream.

To do that, it must answer:

What tables exist and what do they contain?
Which columns are sensitive or have critical null patterns?
What are the distributions, frequencies, and dependencies?
Which columns act as keys?
What business rules must be preserved?
How much data should be generated, and where should it land?
How do we prove the output is good enough?

What the System Needs to Do

The system must cover the full lifecycle.

Discovery and metadata reading: identify catalogs, schemas, and tables so the agent is not wandering the lakehouse like a junior engineer on their first production incident. It reads column types, tags, constraints, ownership, and context.

Column profiling: extract ranges, null rates, cardinality, distributions, seasonality, and outliers.

Relationship and rule detection: map declared keys, infer missing dependencies, and capture business rules like “closed accounts should not have new transactions.”

Synthetic generation: scale row counts while generating fake data with real structural responsibilities.

What the Output Must Preserve

The generated data should preserve:

Column distributions, value ranges, string formats, and null behavior.
Categorical frequencies and cross-column correlations.
Referential integrity, parent-child dependencies, and temporal patterns.
Complex structures like network topologies or rare edge cases when requested.

Validation Is Part of the Mission

Validation is not optional. It’s the safety gate.

A generator can easily produce visually plausible nonsense, so the system must compare real and synthetic tables across schemas, distributions, frequencies, correlations, relationships, and business rules.

The goal is measurable evidence, not “probably fine” or dashboard confetti. We want the brutal truth before someone builds a demo on garbage.

Publishing, Exploration, and Deployment

Once validated, the data product moves into the platform.

Publishing: approved tables are written back to Unity Catalog as discoverable, documented assets, not a temporary dataframe named df_final_final_really_final.

Exploration: results should be available through dashboards, Genie Spaces, Databricks Apps, and Microsoft Copilot Studio.

Deployment: the full system — notebooks, workflows, metrics, and configs — should be packaged with Databricks Asset Bundles and CI/CD for repeatable, versioned, multi-environment deployments.

3. The Core Design Principle: Agent as Orchestrator, Not Wizard

This principle is the backbone of the design: the agent should not do everything inside one heroic, terrifying, 900-line prompt.

Throwing twelve tables at an LLM and asking it to infer relationships, handle privacy, generate millions of rows, and validate itself is not architecture. It’s asking a model to cosplay as a data platform.

It may look fine in a demo. Then a distribution drifts, a foreign key breaks, or every synthetic customer mysteriously owns a yacht.

Awkward. A serious system needs orchestration, not wizardry.

The Agent Coordinates, Tools Execute

The agent reasons, routes, and explains. The tools calculate facts. That means every major step should be handled by specialized, deterministic components:

UC Metadata Reader: extracts schemas, tags, comments, and constraints.
Table Profiler & Pattern Detector: calculates distributions, null behavior, and statistical patterns.
Relationship Detector: evaluates joins and builds a dependency graph.
Generation Planner: turns all inputs into an execution contract.
Synthetic Data Generator: creates Delta records based on the plan.
Quality Validator: audits outputs with measurable quality scores.
Publisher & Helpers: writes approved results to Unity Catalog and prepares dashboard or Genie metadata.

One giant prompt is a trap because it hides intermediate results, blocks step-by-step validation, and makes failures hard to isolate. A modular workflow leaves a paper trail.

What the Agent Actually Owns

The agent owns the workflow, not the raw execution. When a user requests a 10x scaled dataset, the agent should not immediately generate rows like an overexcited intern. It should check the prerequisites first:

Do schemas exist?  
         ↓  
Any declared keys?  
         ↓  
What are the inferred join hierarchies?  
         ↓   
Which columns are marked sensitive?  
         ↓  
What thresholds define validation success?

Only then should it build a generation plan.

Generation is not step one. It’s what happens after the platform knows enough not to embarrass itself in public.

Tool Specialization Makes the System Testable

Modular tools make debugging possible. If referential integrity breaks, we inspect the relationship detector or generator. If distributions drift, we inspect the profiler, planner, or validation thresholds.

That is much better than opening a black-box prompt and whispering: Why are you like this?

The Orchestration Flow

User Request  
    ↓  
Agent Interprets Intent  
    ↓  
Metadata Reader  
    ↓  
Profiler  
    ↓  
Relationship Graph  
    ↓  
Business Rules Extraction  
    ↓  
Generation Planner  
    ↓  
Synthetic Generator  
    ↓  
Quality Validator  
    ↓  
UC Publisher  
    ↓  
Dashboard / Genie Preparation  
    ↓  
Agent Final Explanation

This flow is not glamorous. Good. Glamour is how production incidents get nice logos.

The Agent Should Explain, Not Pretend

At the end, the agent must explain what happened:

Which tables were used.
Which relationships were declared.
Which relationships were inferred.
Which assumptions were made.
Which validation checks passed.
Which warnings or limitations remain.

If relationship inference was weak, say so.
If anomalies were smoothed over, say so.
If privacy controls were limited, say so.

No fake confidence. No wizard hats.

The Core Design Principle

Agent + Tools + Metadata + State + Validator = Trustworthy System

Agent: orchestrates the workflow and interprets user intent.
Tools: execute deterministic tasks like profiling, key detection, generation, and publishing.
Metadata: grounds the system in Unity Catalog schemas, tags, comments, and constraints.
State: records run parameters, configurations, outputs, and history.
Validator: judges generated data against real source patterns before publication.

By separating reasoning from computation and wrapping execution in metadata, state, and validation, we build a repeatable data engineering product. Not a chaotic AI prompt with ambition.

4. System Architecture at a Glance

Now that the agent is not a single chaotic blob of responsibility, let’s look at the framework from a distance.

User  
 │  
 ▼  
Databricks App / Copilot Studio / Notebook  
 │  
 ▼  
Synthetic Data Agent  
 │  
 ├── Specialized Operational Tools  
 │   ├── Profiler  
 │   ├── Validator  
 │   ├── Generator  
 │   └── Publisher  
 │  
 ▼  
Unity Catalog  
Source & Destination Control Plane

The User Interface Layer

The user may interact through a Databricks App, Copilot Studio, or a development notebook. The UI captures raw intent:

Commanding parameters: source tables, scale factors, target volumes.
Setting guardrails: preserve relationships, isolate rare events, exclude sensitive fields.
Demanding proof: request telemetry, validation metrics, and quality evidence.

The interface collects the request. The agent turns it into a structured plan.

The Synthetic Data Agent Layer

The agent is the brain of the workflow, not the muscles, skeleton, or legal department. It coordinates execution like a conductor, even if the orchestra is mostly JSON, Spark jobs, metadata, and regret.

The agent tracks:

Which tables are in scope.
Which constraints must be enforced.
Which profiles already exist.
Which tools should run next.
Whether PK/FK relationships are verified.
Whether generation can safely proceed.
Whether validation passed.
Which warnings humans need to see.

It manages the lifecycle. It does not do the raw data work itself.

The Tool Layer

Every tool has one job, which means each component can be isolated, tested, optimized, and blamed accurately when something breaks.

The agent orchestrates tools like:

uc_schema_reader: discovers catalogs and extracts schemas, types, tags, and constraints.
table_profiler: computes null rates, distinct counts, ranges, and distributions.
relationship_detector: verifies declared constraints and scores inferred key overlap.
pattern_detector: detects dependencies, temporal sequences, and topology-like structures.
generation_planner: converts profiles into a column-level execution blueprint.
synthetic_data_generator: creates synthetic records according to the plan.
quality_validator: audits outputs as acceptable, suspicious, or a beautiful disaster.
publisher: commits verified assets back to governed destinations.
helpers: prepare dashboards and Genie semantic metadata.

Every tool emits an auditable artifact. That checkpoint trace is the difference between architecture and interpretive data dance.

The Unity Catalog Layer

At the base, Unity Catalog is both source and destination. It wraps the full execution footprint:

Inputs: source records, schemas, governance metadata.
State store: profiling matrices, relationship graphs, generation plans.
Outputs: synthetic tables, validation scorecards, lineage records.

Why This Architecture Works

The separation of concerns gives us:

Independent engineering: the agent reads statuses; tools calculate distributions.
Granular testability: every block can be mocked, optimized, and evaluated.
Auditing: when someone asks why a distribution has that shape, we do not answer, “the model felt that way.” We show the source profile, generation seed, and validator thresholds.

The user provides intent. The agent coordinates the lifecycle. Deterministic tools handle the math. Unity Catalog governs the borders. Validation proves utility.

Everything leaves a trace. That turns a black-box AI experiment into a trustworthy, debuggable system. Magical systems can only be worshipped, feared, or rewritten after the demo. Trustworthy ones can actually ship.

5. Agent Responsibilities

Without clear boundaries, the agent becomes a monolithic monster. So the rule is simple: the agent should not do everything. It should coordinate everything. It’s the responsible adult in the room, which is unfortunate, considering it is also the AI.

Understanding the User Request

Users rarely provide clean engineering requirements. They say “make it realistic” and expect the machine to infer business rules, edge cases, and possibly their childhood trauma.

The agent’s first job is to turn messy intent into executable requirements. It extracts: target catalogs, schemas, and tables, row counts and scaling factors, privacy modes, output locations, deployment or usage context and validation expectations.

It also flags missing context, such as undefined output locations, unclear sensitive column handling, or missing validation thresholds.

Inspecting the Source Data

Before writing a single row, the agent calls tools to interrogate Unity Catalog for schemas, column definitions, constraints, comments, tags, and sensitivity metadata.

This shifts the process from blind guesswork to disciplined engineering. It exposes unpleasant realities early, like nullable key fields, deprecated assets, or undocumented columns, before the generation process fails in public.

Deciding What Needs to Be Profiled

A categorical status field is not a skewed transaction amount. A timestamp is not an identifier. The agent selects profiling strategies based on column type:

Numeric fields: distributions, bounds, percentiles, skew, correlations.
Categorical and string fields: frequencies, cardinality, length patterns, regex-like formats.
Timestamps and identifiers: date ranges, seasonality, gaps, uniqueness, key coverage.

If an LLM guesses these metrics from a tiny preview instead of exact code, the generator will confidently manufacture invalid rows.

Building the Generation Plan

The generation plan is the contract between the agent and deterministic tools. It defines table order, scaling rules, column strategies, relationship handling, privacy controls, and validation thresholds.

It also enforces generation topology. Parent tables must be generated before child tables.

Explaining Limitations

Synthetic data can mimic a schema while hiding structural flaws underneath. The agent must be painfully honest about what worked, what was inferred, and what remains risky. It should attach warnings directly to the run record, such as:

Missing primary keys.
Inferred, not declared, foreign keys.
Low-cardinality columns that may leak patterns.
Weak seasonality detection.
Validation drift or unsupported use cases.

That way, if someone reuses the dataset weeks later, the boundaries are still visible. Not buried in a notebook. Not stored in someone’s memory.

The Responsibility Boundary

To avoid an un-debuggable black box, draw the line clearly.

The agent owns:

Parsing user intent into executable requirements.
Inspecting source data through tools.
Choosing profiling strategies.
Drafting the generation plan.
Coordinating execution.
Explaining validation results and limitations.

The agent does not own:

Calculating null rates, distinct counts, or percentiles directly.
Replacing Unity Catalog governance.
Acting as the physical synthesis engine.
Skipping validation.
Treating inferred relationships as absolute truth.

This keeps the platform modular, testable, and capable of growing one responsibility at a time.

6. The Tool List

This is where the agent stops being a conversational novelty and becomes a structured system.

`uc_schema_reader`

Queries Unity Catalog to build the entry map before anything gets generated.

Inputs: catalog name, schema name, optional table list.
Outputs: tables, columns, data types, comments, constraints, ownership, and sensitivity tags.

`table_profiler`

Calculates the statistical footprint the generator must mirror.

Calculates: null rates, cardinality, percentiles, skew, date ranges, outliers.
Outputs: a JSON profiling report describing the source table’s real behavior.

`relationship_detector`

Separates declared constraints from messy inferred joins.

Identifies: primary keys, foreign keys, parent-child lineages, value coverage, orphan records.
Outputs: relationship links with statistical confidence scores.

`pattern_detector`

Looks deeper into row-level processes, temporal patterns, and topology-like structures.

Analyzes: transaction behavior, temporal sequences, session lifecycles, device paths, graph patterns.
Outputs: behavioral patterns that turn flat rows into realistic relational processes.

`generation_planner`

Converts metadata, profiles, and relationships into the execution blueprint.

Decides topology: generation order, so child rows never appear before parents.
Determines guardrails: scaling rules, conditional nulls, business logic, privacy controls, validation thresholds.

`synthetic_data_generator`

Creates synthetic row blocks strictly from the approved plan.

Inputs: generation plan, profiles, relationship graph, seeds, user constraints.
Outputs: synthetic datasets matching source frequencies, key uniqueness, null behavior, and scale targets.
Rule: generation must be predictable and repeatable, so the system remains testable and debuggable.

`quality_validator`

Audits generated output against source benchmarks.

Audits: row counts, schema drift, null rates, category frequencies, distributions, relationships, business rules.
Outputs: quality metrics and warning objects, not vague guesses.

`publisher`

Registers approved assets into governed locations.

Writes: synthetic Delta tables into Unity Catalog.
Saves: run history, validation scorecards, profiling metadata, and relationship lineage.

`dashboard_builder`

Turns pipeline metrics into visual quality reports.

Visualizes: row counts, null rates, distribution drift, relationship integrity, validation status, run history.
Rule: dashboards don’t replace validation. They make validation visible.

`genie_space_helper`

Turns generated tables into something people can actually explore.

Structures: business labels, column explanations, documentation notes, suggested questions.
Enables: natural-language exploration of synthetic behavior and quality trends.

7. Agent State: The Memory of the Beast

We need to talk about state: actual persistent state, not model vibes or “the LLM probably remembers.”

Why State Matters

State turns isolated chat interactions into a continuous data engineering workflow. As the agent runs, it builds critical operational knowledge: selected tables, profiles, relationships, constraints, plans, runs, and validation results.

If that knowledge only lives in a prompt window, the architecture breaks fast:

No auditability: security and governance teams cannot review what happened.
No reuse: every run recalculates the same parameters from scratch.
No observability: dashboards have no history to show.
Archaeological debugging: developers dig through stale notebook cells to explain why a data type mutated.

The Fundamental State Objects

The system must capture seven key metadata layers.

1. Selected source configuration. Tracks catalog, schema, and exact source tables. This prevents cross-environment confusion when identical table names exist in different schemas and anchors the whole workflow.

2. Profiling results. Caches table and column metrics to avoid repeated expensive scans.

Table metrics: row counts, column volumes, freshness timestamps.
Column metrics: null rates, cardinality, percentiles, regex patterns, distribution shapes.

3. Relationship dependency graph. Stores tables and keys as nodes, and joins as edges.

Ordering logic: parent-child dependencies prevent orphan records.
Confidence scoring: distinguishes declared relationships from statistically inferred ones.

4. User constraints. Stores scale factors, output targets, column suppression rules, privacy settings, custom noise thresholds, and runtime seeds. This explains why a run followed a specific requirement profile.

5. Approved generation plan. The executable contract between agent and tools. It defines synthesis logic, table order, privacy configuration, validation thresholds, and success criteria before compute starts.

6. Run history log. Every run gets a ledger entry, especially the ugly failed ones. Track run IDs, timestamps, requester, compute time, status, outputs, and errors. This supports debugging, trend analysis, and later cost accounting.

7. Validation metrics summary. The evidence layer. Store schema checks, null drift, category frequency distance, distribution similarity, and FK integrity.

Where State Should Live

State should not be treated as one monolithic blob. Different data belongs in different places.

Lakebase framework layer. Best for interactive, application-style state: user parameters, session context, runtime status, workflow checkpoints, approval steps.
Unity Catalog Delta metadata layer. Best for governed, analytical state: long-term profiles, relationship graphs, generation plans, run history, validation reports, lineage tables.

This split gives the agent working memory while giving engineers a structured, queryable audit trail.

8. Where Lakebase or Metadata Tables Fit

This framework needs persistent operational metadata. Metadata is not glamorous, but it is what makes the agent usable after the first demo. Without it, we debug the same pipeline twelve times and start naming bugs after coworkers.

Why Persistent Metadata Matters

A synthetic data lifecycle creates a long trail of artifacts:

Original user request.
Target catalogs, schemas, and tables.
Statistical profiles.
Relationship graphs.
Generation plans.
Output paths.
Validation metrics.
Data quality warnings.
Dashboard and Genie references.

If these disappear after execution, the architecture becomes fragile. The agent calls tools, the generator creates rows, the validator checks outputs, the notebook finishes, and all evidence vanishes into the void.

Persistent metadata gives us traceability. It lets teams answer:

What source data was used?
Which profiles were calculated?
Which relationships were inferred?
What constraints were applied?
Which generation plan ran?
Where did the output land?
Did validation pass?
What warnings appeared?
Which dashboards or Genie Spaces map to the run?

A Combined Approach: The Hybrid Storage Split

Lakebase for application-style state. Lakebase fits the agent’s short-term operational memory:

generation_requests
user_preferences
agent_sessions
conversation context
live workflow_status
tool execution parameters
approval checkpoints

Delta tables in Unity Catalog for analytical metadata. Unity Catalog Delta tables fit governed, queryable, long-lived evidence:

source_profiles
statistical distribution histories
relationship_graphs
inferred connection maps
permanent generation_plans
historical generation_runs
validation_summaries
pipeline quality metrics
generated_table_refs
dashboard_refs
genie_space_refs

Lakebase manages active orchestration. Unity Catalog stores the audit trail.

The Minimal Log Signatures

At minimum, the operational store should capture structured telemetry.

// Generation Request Payload (req_001)  
{  
  "request_id": "req_001",  
  "source_catalog": "main",  
  "source_schema": "sales",  
  "source_tables": [  
      "customers",   
      "accounts",   
      "transactions"  
  ],  
  "scale_factor": 10,  
  "privacy_mode": "strict",  
  "requested_outputs": [  
      "synthetic_tables",   
      "validation_report",   
      "dashboard"  
  ]  
}  
  
  
// Profiling Metadata Object (prof_001)  
{  
  "profile_id": "prof_001",  
  "table": "main.sales.transactions",  
  "row_count": 1000000,  
  "columns_profiled": 24,  
  "created_at": "2026-05-17T10:00:00Z"  
}  
  
  
// Validation and Asset Reference Record (run_001)  
{  
  "run_id": "run_001",  
  "validation_status": "passed_with_warnings",  
  "warnings": [  
      "amount p95 differs by 7.8%",   
      "rare category frequency slightly under target"  
  ],  
  "synthetic_tables": [  
      "main.synthetic_sales.customers",   
      "main.synthetic_sales.accounts",   
      "main.synthetic_sales.transactions"  
  ],  
  "dashboard_id": "dash_001",  
  "genie_space_id": "genie_001"  
}

These references let the agent answer follow-up questions like “Show me the validation metrics for the last run” or “Open the dashboard for the 10x sales dataset.”

Metadata Enables Platform Observability

Persistent metadata also powers the rest of the platform:

Cost observability: execution durations, scanned volumes, cluster configs, token counts.
Security and governance: sensitivity tags, access controls, privacy modes.
Semantic tooling: descriptions, metric definitions, validation context for Genie Spaces.
CI/CD orchestration: resource configs, workflows, tests, and environment history.

9. The First Implementation Scope

Now comes the responsible-engineer part where we ruin everyone’s fun: narrowing version one.

What We Are Explicitly Not Building First

Version one deliberately excludes:

Scale monoliths: no 200-table processing or real-time generation.
Advanced topologies: no node-edge graphs, network structures, or advanced privacy math yet.
Downstream integrations: no Copilot Studio, automated Genie Spaces, or multi-agent committees.
Flashy deliverables: no bundles, cost dashboards, or executive scorecards before the core loop works.

The immediate objective is one working vertical slice: Can the agent inspect related tables, create a generation plan, synthesize data, validate it, and publish results safely?

The Target Dataset

The initial sandbox is a simple relational chain:

customers ➔ accounts ➔ transactions

It includes:

Diverse columns: identifiers, categories, amounts, timestamps, statuses, nulls, sensitive fields.
Lineage constraints: transactions require accounts; accounts require customers.

What Version One Must Support

Version one should deliver a minimum useful workflow, not a hollow demo.

Schema discovery. Read Unity Catalog metadata: catalogs, schemas, columns, types, constraints, and tags. Step one is asking the catalog, not the model’s feelings.

Basic profiling. Measure row counts, distinct values, null frequencies, bounds, categorical weights, and timestamp ranges.

Key and link detection. Identify declared constraints and candidate keys using names, uniqueness, and value overlap. This defines generation order and prevents orphan records.

Explicit generation plan. Create a structured contract with sequencing rules, scaling targets, privacy handling, and validation thresholds.

Synthetic generation. Generate primary keys, foreign key mappings, numeric values, formatted strings, nulls, and scaled records while preserving referential integrity.

Data quality validation. Compare output to source baselines and flag schema breaks, distribution drift, FK failures, or dates jumping to 2099.

Unified publishing. Register approved outputs and run metadata in dedicated Unity Catalog schemas, such as _synth, instead of leaving them trapped in memory as df3.

The Version One Success Criteria

The milestone is this end-to-end pipeline:

Select Tables  
      ↓  
Read UC Metadata  
      ↓  
Profile Columns  
      ↓  
Detect Keys  
      ↓  
Map Generation Plan  
      ↓  
Synthesize Tables  
      ↓  
Validate Quality  
      ↓  
Publish Assets  
      ↓  
Log Summary

A tight vertical slice exposes real edge cases early: missing metadata, messy relationships, conditional nulls, and weird source data behavior.

Limiting the first scope is not lowering ambition. It’s protecting ambition from collapsing under an unmanageable roadmap.

Once the relational core works, we can expand into network topologies, advanced security scoring, dashboards, Genie Spaces, Copilot integration, and deployment automation.

10. Serious Design Decisions

Architecture is boundaries. These decisions separate a reliable synthetic data engine from a notebook that generates Customer_12345 and calls it innovation.

Decision 1: Deterministic Tools vs. LLM Reasoning

Rule one: let the LLM reason, but never let it calculate metrics from vibes.

LLMs are useful for interpreting messy user intent, routing tools, proposing generation strategies, explaining validation warnings, and translating technical outputs into human language.

Hard code handles the facts:

Profiling tables.
Calculating statistics.
Auditing relationships.
Generating data.
Writing tables.
Enforcing referential integrity.
Applying privacy filters.

If the agent guesses percentiles instead of running code, debugging becomes archaeology with a chatbot interface.

Decision 2: Store Every Intermediate Result

We don’t persist artifacts because we are digital hoarders. We persist them because memory makes the system explainable and recoverable.

Store:

Inputs and rules: source selections, metadata profiles, relationship graphs, user constraints, generation plans.
Outputs and logs: synthetic table targets, validation metrics, warnings, run history.

Without this audit trail, evidence disappears between executions. If output drifts, we need to know whether the problem came from the source profile, join map, plan, or generator.

Decision 3: Validate Before Publishing

A pipeline can run perfectly while producing flat distributions, broken dependencies, or smoothed-out anomalies.

The lifecycle must be:

Generate ➔ Validate ➔ Review ➔ Publish

The validator is the quality gate. Before any dataset is labeled usable, it must pass checks across schemas, null bounds, frequency behavior, joins, business rules, and privacy constraints.

Failed datasets do not get published. They get fixed. Or quietly escorted into the trash.

Decision 4: Make Relationships First-Class Citizens

Relationships are not optional footnotes. They are the structure of the dataset.

Customers open accounts. Accounts produce transactions. Tables belong to a logical universe. If we generate tables as isolated islands, we get plausible spreadsheets that do not work together.

The relationship matrix must capture:

Declared constraints.
Inferred joins.
Parent-child structures.
Cardinality patterns.
Confidence scores.

This map drives generation order, key mapping, and validation checks. Without it, synthetic data is structurally fake in all the wrong ways.

Decision 5: Privacy Is Not Automatic

Synthetic means manufactured. It does not automatically mean safe.

Careless generation can mirror rare patterns, expose production signatures, or leak distinct identifiers.

Privacy needs deterministic controls, not model optimism:

Obfuscation filters: exclude identifiers, mask high-risk strings, block direct sampling of sensitive rows.
Statistical guardrails: suppress low-frequency values, add controlled noise, generalize specific categories.
Transparency: audit nearest-neighbor risks and log whether strict privacy mode was enforced.

11. Proposed Agent Flow

Now that we have the architecture, tools, state, and boundaries, let’s tie everything into one end-to-end orchestration pipeline.

Notice how generation is not step one. It happens only after the system understands the request, inspects the data, profiles behavior, maps relationships, and builds a plan.

Step-by-Step Execution Lifecycle

1. Request ingestion and parsing. The user starts with raw intent, like: Scale the sales dataset 10x, preserve relational links, and isolate rare events. The agent converts that into structured requirements: catalogs, schemas, source tables, target volumes, privacy rules, validation expectations, and testing goals.

2. UC metadata discovery. The agent calls the metadata reader to check what exists in Unity Catalog: schemas, column types, tags, constraints, ownership, and documentation. This catches surprises early, like amount stored as text or a critical table marked deprecated.

3. Data profiling and statistical scans. The profiler measures how the source data behaves: row counts, null ratios, value bounds, distributions, outliers, cardinality, and timestamp ranges. This gives the generator real numbers to imitate instead of becoming random() with a marketing budget.

4. Dependency mapping and relationship graphing. The relationship detector checks declared keys and inferred joins, then the agent builds a directional graph showing how tables depend on each other. This graph stores confidence scores, so inferred relationships are clearly marked before generation order is decided.

5. Constraint and gap resolution. Before freezing the strategy, the agent checks for missing execution rules: output location, privacy handling, PII treatment, rare event behavior, or validation thresholds. It asks focused questions only when needed, such as: I found an unmapped sensitive identifier. Should I drop it or obfuscate it?

6. Generation contract codification. The agent turns metadata, profiles, constraints, and relationships into an executable generation plan. This plan defines sequencing, scaling, distributions, privacy controls, business rules, and validation thresholds. No plan, no generation.

7. Synthesis engine loop. Now generation happens. The generator follows the plan and processes parent tables before child tables, such as: customers ➔accounts ➔transactions. This preserves referential integrity, so child rows point to valid synthetic parent keys instead of wandering alone through the relational wilderness.

8. Verification and gatekeeper validation. The validator audits the generated data against the source profile and generation plan. It checks row counts, schema conformity, null behavior, distributions, formatting masks, relationships, business rules, and privacy constraints. Results are based on measurable drift and quality thresholds, not human optimism.

9. Diagnostic explanations. The agent reads the validation logs and produces a clear audit summary. It explains what matched, what drifted, which joins were inferred, which warnings were raised, and whether the dataset is safe to use.

10. Catalog publication and exploration enrichment. Once approved, the publisher writes synthetic tables to governed Unity Catalog schemas. It also stores run records, profiling logs, generation plans, validation summaries, and lineage references. Finally, dashboards and Genie Spaces are updated with metrics, descriptions, and suggested exploration paths.

12. Example User Interaction

To make the architecture tangible, let’s see how the agent behaves when a user throws a dangerously simple request at it.

User: Generate synthetic data for sales.customers, sales.accounts, and sales.transactions. Scale it 10x and preserve relationships.

Agent: I found those three tables in Unity Catalog.

customers.customer_id acts as the parent key.
accounts.customer_id maps to customers.
transactions.account_id maps to accounts.

I will generate data in this order: customers ➔accounts ➔transactions.

I will preserve: null rates,categorical frequencies, transaction amount distributions, customer/account relationships, account/transaction relationships.

Pre-flight warnings:

The account-to-transaction link is inferred with 99.2% confidence, not declared in Unity Catalog.
Sensitive fields detected in customers, including email and phone_number, will be safely synthesized.

This is the behavior we want. The alternative is an unaudited chatbot blindly producing 10 million rows and leaving us to explain why every transaction belongs to customer_id = potato.

Inspect, Don’t Build

The key behavior is restraint. The agent does not immediately write rows. It first runs discovery tools.

A loose user request hides structural obligations. The agent must:

Identify parent and child entities.
Verify key eligibility.
Separate declared constraints from inferred joins.
Decide which profiles and validation rules are needed.

Deciphering the Relational Network

Before launching Spark jobs, the agent shows the dependency map:

customers.customer_id➔accounts.customer_id➔transactions.account_id

This explains why sequencing matters: customers before accounts, accounts before transactions. Child rows cannot exist without parents unless we are building a synthetic orphanage. We are not.

The agent also exposes uncertainty:

Source: inferred  
Confidence: 99.2%

Hiding uncertainty is how fragile assumptions become production architecture.

Translating “Realistic” into Strict Targets

“Realistic data” means different things to different people.

For QA, it means referential integrity. For analytics, it means distribution curves. For executives, it means “make the dashboard believable by Tuesday.”

The agent turns vague realism into explicit preservation targets:

Null profiles: maintain sparsity thresholds.
Join topology: preserve valid foreign key mappings.
Statistical boundaries: match skewed amount distributions and tail percentiles.

That makes the plan reviewable and gives the validator concrete rules to audit.

Proactive Warnings over Post-Mortem Failures

The agent should surface warnings before generation, not bury them in runtime logs after something breaks.

Pre-flight checks may flag:

Extreme tail anomalies.
Conditional null patterns.
Low-cardinality privacy risks.
Inferred relationships.
Historical validation drift.
Sensitive fields requiring masking or synthesis.

The Final Validation Handshake

After execution, the agent should produce an auditable summary:

Agent: Synthetic generation completed with warnings. Tables were registered to main.synthetic_sales.*_synth.

Validation summary:

Row counts match the requested 10x scale.
Primary key uniqueness passed.
Foreign key integrity passed.
Null distributions stayed within tolerance.
Categorical frequencies stayed within tolerance.

Telemetry warnings:

Transaction amount distribution passed overall, but extreme P95 tail values drifted by 7.8%.
Sensitive fields were generated using manufactured token masks.

Usage recommendation:
Suitable for local database optimization and dashboard UI testing. Do not use for advanced ML model training without reviewing the P95 tail drift.

Designing to Keep It Real

Designing an agentic architecture means replacing the magic prompt with disciplined software engineering.

By separating LLM reasoning from deterministic tools, defining state, and starting with a tight relational slice, the system becomes a governed platform asset instead of an unpredictable chatbot.

Trust requires proof: validation, warnings, and an audit trail — not model optimism or dashboard confetti. Since relational data is a connected topology, structural links come first to avoid orphan records and broken outputs.

The blueprint is complete: clear role, dedicated toolbox, persistent memory, and a validator checking the homework.

Next, we initialize the workspace with Databricks Asset Bundles and start making fake data real.

Any help, feedback, ideas, warnings, or battle scars from similar projects will be more than appreciated.

Let’s build. 🏗️

🔗 Code & Music 🎧

The code for this article is available here:

🔗 synthetic-data-agent

And because metadata discovery clearly needed a soundtrack, the Synthetic Data Agent now has its own songs too:

🎧 YouTube playlist

Read the article. Check the repo. Play the songs.

SDA 02 — The Agent Ain’t The System

*> **Voice A = Lead MC**

**Voice B = Hypeman / Counterpoint***

[Intro]

**B:** Yo!!

**A:** Databricks!

**B:** Unity Catalog!

**A:** Lakebase!

**B:** Synthetic Data Agent!

**Both:**
DON’T BELIEVE THE HYPE!

*(Scratch)*
“Agent…”
“…not wizard…”
“…not magic…”

[Verse 1]

**A:**
Everybody wanna build an AI,
Throw a prompt up high to the sky,
Cross their fingers —
Hope it don’t die…

**B:**
NOPE!

**A:**
That’s how bad architecture multiply!

**B:**
Facts!

**A:**
One giant prompt?
That’s a trap.
One black box?
That’s a wrap.

**B:**
Where the proof?

**A:**
Where the map?

**B:**
Where the state?

**A:**
Where the facts?

**Both:**
WHERE THEY AT?!

[Hook]

**A:**
The agent —

**B:**
AIN’T THE SYSTEM!

**A:**
The LLM —

**B:**
AIN’T THE SYSTEM!

**A:**
One big prompt —

**B:**
AIN’T THE SYSTEM!

**Both:**
TOOLS!
STATE!
TRUTH!
MISSION!

[Verse 2]

**A:**
Unity Catalog —
Open the gate.

**B:**
Governance first —
Never too late.

**A:**
Read every schema,
Every constraint,
Every comment,
Every complaint.

**B:**
Read it!

**A:**
Profiler measures —
Don’t estimate.

**B:**
Measure!

**A:**
Relationship graph —
Separate.

**B:**
Separate!

**A:**
Generation waits.

**B:**
VALIDATE!

[Bridge]

**A:**
Who reads metadata?

**B:**
THE TOOL!

**A:**
Who computes null rates?

**B:**
THE TOOL!

**A:**
Who builds distributions?

**B:**
THE TOOL!

**A:**
Who explains the workflow?

**B:**
THE AGENT!

**Both:**
NOW YOU GOT THE RULE!

[Verse 3]

**A:**
Lakebase memory —
Runs don’t disappear.

**B:**
History!

**A:**
Every checkpoint
Stays right here.

**B:**
Audit!

**A:**
Every warning,
Every fear,
Every retry
Crystal clear.

**B:**
No archaeology!

**A:**
No mystery.

**B:**
No “works on my machine!”

**A:**
Everything versioned —
Everything seen.

[Hook]

**A:**
Reason!

**B:**
Execution!

**A:**
Separate!

**B:**
Execution!

**A:**
Trust!

**B:**
Validation!

**Both:**
NO AI HALLUCINATION!

[Verse 4]

**A:**
Customer…
Accounts…
Transactions…

**B:**
In that order!

**A:**
Parents first —

**B:**
Respect the border!

**A:**
Foreign keys
Don’t freestyle.

**B:**
Never!

**A:**
Child rows
Stand in line.

**B:**
Structure!

**A:**
You call it fake —
We call it engineered.

**B:**
Governed!

**A:**
You call it random —
We call it cleared.

[Breakdown]

**A:**
Metadata!

**B:**
POWER!

**A:**
Profiler!

**B:**
POWER!

**A:**
Validator!

**B:**
POWER!

**Both:**
THE PEOPLE WANT…

REAL…

DATA…

POWER!!

[Verse 5]

**A:**
No dashboard confetti.

**B:**
NOPE!

**A:**
No demo smoke.

**B:**
NOPE!

**A:**
No billion-row miracle
Built on hope.

**B:**
NOPE!

**A:**
Evidence talks.

**B:**
Truth walks.

**A:**
Metrics louder
Than TED Talks.

**B:**
Talk that!

**A:**
If it drifts —

**B:**
Say it!

**A:**
If it breaks —

**B:**
Show it!

**A:**
If you guessed —

**B:**
OWN IT!

[Final Hook]

**A:**
The Agent!

**B:**
Coordinates!

**A:**
The Tools!

**B:**
Calculate!

**A:**
Metadata!

**B:**
Governs!

**A:**
Validator!

**B:**
Separates!

**Both:**
DON’T BELIEVE THE HYPE —

BELIEVE THE EVIDENCE!

[Outro]

*(Sirens, scratches, crowd chants)*

**A:**
What’s the mission?

**Crowd:**
BUILD TRUST!

**B:**
What’s the weapon?

**Crowd:**
GOOD DATA!

**A:**
What’s the enemy?

**Crowd:**
BLACK BOXES!

**Both:**
THE AGENT AIN’T THE SYSTEM…
THE AGENT RUNS THE SYSTEM!

*(Scratch fade)*

*> “Agent…”

“Tools…”
“Metadata…”
“Validation…”
“Trust…”*