Synthetic Data Agent · 4 September 2026

🤖 SDA 07: Capturing Cross-Column Patterns and Business Rules

Building pattern_detector so the SDA learns which values belong together—not merely how each column behaves alone.

0% read·Calculating this view…

Perfect Columns, Impossible Rows

Marginal Realism is not Joint Realism

table_profiler may tell us that 8% of orders are cancelled, cancellation_timestamp is null in 92% of rows, premium accounts represent 12% of accounts, and electronics make up 25% of purchases.

Useful. Still not enough.

Those marginals don’t tell us whether cancellation timestamps belong to cancelled orders, premium accounts favour certain products, or transaction amounts change by category.

We now care about four layers:

Marginal distributions: one column in isolation.
Joint distributions: several columns together.
Conditional distributions: an outcome within a state, segment, or context.
Business rules: what must, should, or usually happen.

A generator can match every marginal and still create impossible rows. The columns look innocent under the warehouse lights. The row tells a different story.

Relationships Give Structure; Patterns Give Behaviour

relationship_detector tells us that customers own accounts and accounts produce transactions. pattern_detector asks what happens inside that structure.

Do premium customers transact more? Does product category change amount? When may closed_at be null? Which state transitions occur, and how long do they take?

The boundary remains simple:

📌 Code measures the evidence. The agent interprets it, resolves ambiguity, and asks for missing business context.

That separation is the difference between discovering a pattern and quietly inventing a policy.

Define the `pattern_detector` Contract

Reuse Evidence; don’t Rediscover it

pattern_detector should consume upstream evidence rather than reinterpret it: normalized metadata from uc_metadata_reader, profiles from table_profiler, validated relationships/entity keys/fan-out from relationship_detector, approved scope and column roles, optional user/domain rules, detection thresholds, and source snapshot/version references.

Entity keys matter especially for temporal work. No account lifecycle can be reconstructed until the tool knows which rows belong to the same account and how to order them.

The output should be a versioned pattern registry in governed Delta/Unity Catalog storage, preserving population, conditions, method, support, effect or probability, violations, stability, evidence mode, source snapshot, origin, proposed actions, review state, and warnings. The agent gets the compact summary. The registry keeps the receipts.

Keep the Boundary Narrow

pattern_detector doesn’t prove causality, invent business definitions, generate rows, decide privacy policy, solve graph topology, or replace domain review.

Its job is narrower and more useful:

📌 Discover, quantify, classify, and explain how values behave together.

Select Candidates Before Calculating Everything

Turn Columns into Roles

A 100-column table contains 4,950 unordered pairs and 161,700 three-column combinations. Testing all of them wastes compute and manufactures coincidental discoveries before breakfast.

Prune candidates using what the SDA already knows: types, cardinality, names, comments, tags, lifecycle semantics, entity keys, conditional-null clues, relationship fan-out, and user or domain hints.

Classify columns by role:

Drivers: segment, tier, status, country, channel, product category.
Outcomes: amount, count, duration, category choice, null indicator.
Lifecycle: creation, activation, cancellation, shipment, closure.
Entity keys: customer, account, order, device, claim.
Context: currency, geography, source system, effective period.

Identifiers usually belong in grouping logic, not as explanatory variables. Free text, sensitive attributes, and extreme-cardinality fields should be excluded unless explicitly approved.

Control Broad Statistical Searches

If the tool still runs broad inferential tests, the policy should be explicit and versioned. Minimum support alone does not solve multiple testing; a false-discovery-rate procedure is one reasonable option when p-value-based screening is used.

The principle matters more than the specific method:

📌 Don’t let “small p-value” become a synonym for “new business law.”

Measure Dependencies Without Abusing Correlation

Numerical Relationships

For numerical pairs, Pearson summarizes linear association; Spearman captures monotonic association. Spark DataFrame.corr supports Pearson only, while MLlib Correlation supports Pearson and Spearman on vector columns; Spearman is costlier because ranking and sorting are required.

Store more than the coefficient: valid-pair count, population, filters, null policy, sampling configuration, outlier sensitivity, and segment/time stability.

A coefficient without context is just a decimal in a trench coat.

Categorical and Mixed Dependencies

Do not encode categories as arbitrary integers and run Pearson. The assigned numbers have no meaningful distance.

For categorical or mixed pairs, prefer contingency counts, conditional frequencies, share differences, lift, grouped numerical distributions, useful effect sizes, and association tests only where they help prioritisation.

📌 Premium accounts purchase analytics products 2.3 times as often as the overall population.

That is generation knowledge. A generic dependency score is mostly decoration.

And the hard rule remains:

correlation ≠ business_rule

Association may be indirect, confounded, segment-specific, time-limited, outlier-driven, or reversed after conditioning. It is evidence, not causality.

Conditional Behaviour Is the Core of the Tool

Conditional Distributions and Fan-out

The most useful output is usually a conditional profile: transaction count by customer segment, amount by product category and currency, product mix by account tier, resolution time by priority, or cancellation reason by channel.

Preserve both the global baseline and the conditional distribution. The future generator should consume approved pattern evidence — not rescan raw source rows during generation.

For “high-value customers have more transactions,” use the relationship graph to aggregate transactions per customer, starting from the full parent population so zero-transaction customers are not lost. Retain zero-child rate, median and upper percentiles, skew, long tails, segment size, lift over baseline, and stability.

Conditional Nulls

Missingness is often conditional. Treat the null indicator as an outcome:

P(cancellation_timestamp IS NULL | status = 'CANCELLED')
P(closed_at IS NULL | account_status = 'OPEN')
P(tax_id IS NULL | customer_type, country)
P(shipped_at IS NULL | fulfilment_method)

Suppose 97.8% of cancelled orders have a cancellation timestamp. The remaining 2.2% may be legacy behaviour, delayed updates, valid exceptions, or defects.

The detector should report the eligible, supporting, and violating populations and whether violations cluster by period, geography, or source system. It should not decide what the 2.2% mean.

Support, Fallback, and Drift

Small groups need discipline. Enforce minimum row and support-rate thresholds, cap segmentation depth, and fall back from narrow approved segments to broader ones and finally the global distribution. Record every fallback.

Patterns drift too, so test stability across relevant periods, geography, source systems, partitions, or source versions.

The data may remember the old city perfectly. That does not mean anyone still lives there.

Temporal Dependencies And State Transitions

Ordering and Realistic Delays

Every timestamp may look plausible while the lifecycle is impossible:

created_at ➔ approved_at ➔ shipped_at ➔ delivered_at

Evaluate applicable ordering rules such as created_at <= approved_at, approved_at <= shipped_at, cancelled_at >= created_at, and closed_at >= opened_at. Preserve eligible population, null policy, support, violations, and applicability conditions.

Correct order is only half the job. Generation also needs realistic delay distributions: medians, percentiles, zero-duration rates, long tails, and segment differences. Spark windows can partition by entity and order by event time; use lag/lead and a deterministic tie-breaker for equal timestamps.

State Transitions and Censoring

For event or snapshot histories, build observed transitions:

PENDING   ➔ ACTIVE  
PENDING   ➔ CANCELLED  
ACTIVE    ➔ SUSPENDED  
SUSPENDED ➔ ACTIVE  
ACTIVE    ➔ CLOSED

Record counts, probabilities, self-transitions, unseen transitions, apparent terminal states, and time in state. In periodic snapshots, distinguish a true self-transition from simply observing the same state twice.

The final observed state is usually right-censored: no later event means we do not know when that state ended.

An unseen transition is not automatically forbidden. Only an approved rule should call it invalid.

Use the Correct Clock

Business-event time and ingestion time are different clocks. Ordering by arrival can make a valid late event look impossible.

Record which timestamp defines sequence, which records ingestion, and how delayed or tied events were handled.

From Observed Pattern To Business Rule

Classify Strength Explicitly

Every finding should land in one of four buckets:

Hard invariant: must always hold.
Conditional requirement: must hold when a predicate is true.
Probabilistic pattern: usually holds and should be reproduced statistically.
Anomaly signal: unusual behaviour that may be valid, rare, or defective.

Automatically discovered findings should normally start as probabilistic patterns or candidate rules. “100% in this sample” is not the same as “the business requires this forever.”

Respect Databricks Constraint Semantics

Databricks distinguishes enforced and informational constraints. NOT NULL and CHECK are enforced on writes. Primary keys, foreign keys, and UNIQUE constraints are informational and are not enforced.

As of August 2026, UNIQUE constraints are in Public Preview for Unity Catalog Delta tables in Databricks SQL and Databricks Runtime 18.2+.

So enforced constraints are stronger platform evidence, while key semantics still rely on SDA 06 validation. pattern_detector should read those constraints, not rewrite source schemas or publish inferred rules automatically.

User and Domain Rules Need Precedence

A user may say: Cancelled orders must always contain a cancellation timestamp. Convert that into a versioned, testable rule with scope, predicate, requirement, origin, owner, approval state, and effective period, then measure support and conflicts before approval.

A sensible configurable precedence is:

security, privacy, and platform-safety policy;
enforced destination constraints;
approved domain rules;
approved request-specific user constraints;
declared informational metadata;
observed statistical patterns.

If an approved rule overrides history, preserve the disagreement, decision, owner, and effective period.

Silently choosing one answer is not intelligence. It is improv theatre with production data.

Practical Implementation On Databricks

Keep the Implementation Modular

A practical layout is:

src/sda/patterns/  
    candidates.py  
    correlations.py  
    conditionals.py  
    missingness.py  
    temporal.py  
    rules.py  
    scoring.py  
    detector.py  
src/sda/models/  
    pattern.py  
    business_rule.py  
notebooks/  
    07_detect_patterns.py  
bundle/resources/  
    pattern_detector_job.yml

databricks.yml can include modular YAML, and Declarative Automation Bundles support source files, resources, built artifacts, and wheel dependencies. The notebook should only validate parameters, invoke the package, persist results, and return a compact summary.

No one needs a 600-line notebook where conditional probabilities go to become folklore.

Quick and Full Modes Should Mean Different Things

A practical flow is:

Read metadata, profiles, relationships  
        ↓  
Validate scope, snapshots, permissions, columns  
        ↓  
Assign analytical roles  
        ↓  
Generate and prune candidates  
        ↓  
Run bounded discovery  
        ↓  
Verify strong candidates  
        ↓  
Merge observed, declared, user/domain rules  
        ↓  
Detect conflicts, weak support, instability  
        ↓  
Persist registry + evidence  
        ↓  
Return findings and review questions

Quick mode ranks candidates with bounded, optionally sampled or approximate analysis. Full mode verifies generation-relevant patterns on the approved population, using exact computation where practical and documented scalable approximations otherwise.

Helper modules do not make the milestone complete. If production only calls correlation, the detector is still a correlation job wearing a larger wardrobe.

Operational Guardrails

Cap candidates and segmentation depth, enforce support thresholds, reuse upstream evidence, label exact/approximate/sampled methods, exclude sensitive raw values, and version algorithms, thresholds, seeds, and precedence policies.

Spark collect() returns all records to the driver, so large contingency and transition evidence should stay distributed, be capped, or be persisted directly.

Find credible patterns. Don’t burn the warehouse down to prove enthusiasm.

Make The Output Contract Explicit

Preserve Evidence Before Approval

An observed pattern should not be named a conditional_requirement before anyone approves it:

{  
  "pattern_id": "pat_000127",  
  "pattern_type": "conditional_pattern",  
  "candidate_rule_class": "conditional_requirement",  
  "table": "main.sales.orders",  
  "condition": {"column": "status", "operator": "=", "value": "CANCELLED"},  
  "outcome": {"column": "cancellation_timestamp", "requirement": "IS NOT NULL"},  
  "origin": "observed",  
  "population_rows": 148071,  
  "condition_rows": 12438,  
  "condition_rate": 0.084,  
  "satisfied_rows": 12164,  
  "condition_satisfaction_rate": 0.978,  
  "violation_rows": 274,  
  "violation_rate_within_condition": 0.022,  
  "baseline_non_null_rate": 0.081,  
  "validation_mode": "exact",  
  "stability": {"period": "month", "minimum_satisfaction_rate": 0.965},  
  "decision": "review_required",  
  "proposed_generation_action": "populate_when_condition_is_true",  
  "proposed_validation_action": "measure_violation_rate",  
  "warnings": [  
    "observed_pattern_not_domain_approved",  
    "violations_concentrated_in_legacy_source"  
  ]  
}

condition_rows is the population where the predicate applies; satisfied_rows is the population supporting the proposed rule. condition_satisfaction_rate says what was actually measured instead of hiding behind generic “confidence.”

Make Approval Hard to Cross by Accident

Also preserve detector/scoring-policy versions, source snapshots, upstream artifact IDs, user/domain rule references, effective dates, reviewer, approval state, and superseded pattern IDs.

Before approval, it’s evidence. After approval, the planner may consume it. That line should be hard to cross by accident.

Test The Awkward Cases

Adversarial Fixtures, not Showroom Demos

Use deterministic fixtures for:

segment-dependent fan-out and product mixes;
small numbers of rule violations;
lifecycle-specific nulls;
aggregate correlations that vanish or reverse after segmentation;
tiny groups with misleading 100% support;
multiple-testing false positives;
rare transitions and repeated snapshots;
out-of-order and late events;
conflicting user and observed rules;
rules that change after a known effective date.

The detector is not proven because it finds the obvious pattern. It’s proven when it refuses the seductive wrong one.

Assert Decisions and the Real Execution Path

Assert baselines, support, violation rates, fallback, ordering, lags, transitions, right-censoring, stability, effective dates, deterministic ranking, precedence, conflict warnings, and the absence of automatic rule promotion.

PySpark provides assertDataFrameEqual and assertSchemaEqual, with configurable numerical tolerances where needed.

And test the coordinator/job, not only the helpers. A correct SDA 07 path should run metadata → profiles → relationships → patterns against deterministic managed test tables and prove that the deployed detector produces the same evidence contract it promises on paper.

Why This Matters Beyond Synthetic Generation

Agent and Genie reliability

pattern_detector gives the SDA structured definitions, support, violations, provenance, stability, conflicts, and review status instead of forcing it to improvise from names or previews. The same approved registry can later strengthen a Genie Agent—the current Databricks name for the former Genie Space—using governed datasets, example SQL, business semantics, instructions, and trusted assets.

“High-value customer” should come from an approved definition — not whatever SQL sounded persuasive at 16:57 on a Friday.

Reuse Approved Rules for Validation

Simple approved row-level rules can also feed Lakeflow pipeline expectations: warn keeps invalid rows and records metrics, drop removes them, and fail fails the affected flow/update. In a triggered pipeline, other independent flows can continue, so expectations are data-quality controls, not a general orchestration engine.

Cross-row, cross-table, aggregate, or temporal rules need validation tables, dedicated logic, or workflow orchestration.

One registry can therefore support generation, validation, and later governed exploration.

Deliverable, Caveat, and What Comes Next

Definition of Done

pattern_detector is complete when the deployed workflow can:

consume compatible metadata, profile, relationship, and source-snapshot evidence;
select candidates by role and measure numerical, categorical, mixed, fan-out, missingness, and segment patterns;
detect temporal ordering, lag distributions, and state transitions;
evaluate user/domain rules, conflicts, precedence, and effective periods;
preserve support, baselines, violations, stability, provenance, and review state;
keep observed evidence distinct from approved rules;
emit generation/validation instructions without activating unapproved rules;
persist an auditable registry and prove the deployed path with adversarial Spark/Databricks tests.

No strong correlation, perfect tiny-segment result, or polished LLM explanation gets to become business truth by charisma alone.

Data is History, not the Constitution

Historical data is not the constitution. New policies may not appear yet, legacy systems may differ, filters may hide lifecycle states, defects may look normal, and rare valid events may vanish from a sample.

Even a pattern that holds for every observed row is still an observation until its meaning is confirmed. Some rules live in documentation; others live with users or the domain expert who knows why the strange 2% exists.

That uncertainty is a safeguard, not a flaw. It stops statistical coincidence from becoming synthetic policy.

From Patterns to Memory

Once this stage is wired end to end, the SDA has structured evidence about how values belong together — and a clear boundary between observed behaviour and approved truth.

But that evidence matters only if it survives the current run. Next, we give the agent operational memory.

Because an agent that relearns the same business rules every morning does not have memory. It has an expensive routine.

When your synthetic data finally learns that matching columns isn’t the same as telling the truth.