🤖 SDA 06: Detecting Primary Keys, Foreign Keys, and Join Relationships

SDA 06: When your synthetic data pipeline gets so good it starts tagging the wall before the tables.

---

Perfect Columns, Broken World

Profiling Is Not Relational Understanding

Profiling taught the agent how each column behaves: its ranges, distributions, formats, null rates, cardinality, and outliers.

Useful, but incomplete.

A synthetic customers table and a synthetic orders table can both look statistically convincing while becoming nonsense the moment they are joined:

• Every customer_id is unique.
• Every order_id is unique.
• Order amounts follow the source distribution.
• Country frequencies are perfectly reproduced.
• Half the orders reference customers who don’t exist.

Individually, the tables look real. Together, they describe a broken system.

Relationships Are Data Too

Enterprise data gets much of its meaning from connections: customers own accounts, accounts produce transactions, orders contain lines, products belong to categories, claims reference policies, and devices emit observations.

The goal is therefore not to find columns that merely can be joined. Shared names, compatible types, or overlapping values may suggest a relationship, but they don’t prove one.

The agent must identify the relationships the generator needs to preserve: valid keys, parent-child dependencies, join cardinality, optional links, and expected fan-out.

📌 Profiling taught the agent how columns behave. Now we need to stop those columns from forming completely fictional relationships.

---

What the relationship_detector Must Determine

The relationship_detector has four jobs:

1. Read declared primary, unique, and foreign keys.
2. Find undeclared columns — or column combinations — that behave like keys.
3. Evaluate which cross-table candidates behave like genuine joins.
4. Convert accepted relationships into a parent-child dependency graph.

Keeping these steps separate prevents metadata, statistical evidence, and business meaning from collapsing into one suspiciously confident guess.

Declared, Validated, and Inferred Are Different Things

A small but important distinction: declared and inferred describe where a relationship came from; validated describes whether the data supports it.

The output should therefore track two independent properties:

• Provenance: declared or inferred
• Validation status: validated, failed, or untested

Unity Catalog can expose declared primary, foreign, and unique constraints, but Databricks treats them as informational rather than enforcing them against the stored data. A declared foreign key may therefore contain orphans, while a strong undeclared relationship may exist only in the data.

declared = true doesn’t mean valid = true.

declared = false doesn’t mean relationship = false.

Scope of This Article

This article discovers keys, evaluates joins, and builds the dependency order required for synthetic generation. It doesn’t yet generate parent and child records, infer broader cross-column business rules, or reproduce complex graph topology.

SDA 07 will add business-rule semantics. SDA 11 will use this relationship graph to generate tables in the correct order and preserve referential integrity. Here, we build the evidence both stages need.

---

Start with Declared Unity Catalog Constraints

Read What the Data Model Claims

The relationship_detector should consume the normalized artifact produced by uc_metadata_reader, not query Unity Catalog independently and create a second interpretation of the same metadata.

Unity Catalog’s information schema exposes the declared model through several complementary views:

• TABLE_CONSTRAINTS identifies primary, foreign, and unique constraints.
• KEY_COLUMN_USAGE lists the columns used by primary and foreign keys, including their ordinal positions in composite keys.
• REFERENTIAL_CONSTRAINTS connects each foreign key to its referenced primary or unique constraint.
• CONSTRAINT_COLUMN_USAGE and CONSTRAINT_TABLE_USAGE identify the columns and tables referenced by those constraints.

These views are privilege-filtered. A missing constraint may therefore mean “not visible to this execution identity,” not necessarily “doesn’t exist.”

Metadata Is a Hypothesis, Not a Verdict

Databricks treats primary, foreign, and unique constraints as informational rather than enforced. They document intended relationships but don’t prevent duplicate parent keys or orphaned child rows from being written.

Every declared constraint must therefore be checked against the current data. The detector should distinguish between:

• Declared and validated
• Declared but backed by a non-unique parent key
• Declared but containing orphaned child values
• Apparently valid only for the inspected snapshot or filter
• Declared but untestable because an object is inaccessible

Declared metadata gives us an excellent place to start. It doesn’t give us permission to stop thinking.

Treat RELY Carefully

Databricks key constraints can be marked RELY, which tells the optimizer that it’s allowed to trust the declared constraint when applying certain query optimizations.

RELY doesn’t validate a constraint. It tells Databricks that the optimizer may assume the constraint is correct when rewriting queries. If that assumption is false, the rewritten query can return incorrect results; Databricks explicitly leaves validation to the user.

Databricks currently applies these RELY-based query optimizations only on Photon-enabled compute.

📌 The practical rule is simple: never treat RELY as evidence. Validate the constraint first, record when and how it was tested, and only then allow the platform—or the agent—to depend on it.

---

Detecting Candidate Primary Keys

Uniqueness Is Only the First Test

For each plausible single-column key, calculate:

• Total and non-null row counts
• Distinct non-null values
• Excess duplicates
• Null rate
• Uniqueness ratio

uniqueness_ratio = distinct_non_null_values / non_null_rows

A strong primary-key candidate normally has no nulls, no duplicate values, and a uniqueness ratio of 1.0.

But that result proves only that the column is unique in the inspected snapshot. It doesn’t prove that the value identifies the table’s business entity, remains stable over time, or will stay unique when new data arrives.

Unique Does Not Mean “Identifier”

Many columns can be accidentally unique: ingestion timestamps, event tokens, row hashes, file sequence numbers, high-precision measurements, and row numbers introduced during ETL. Some may be useful technical identifiers, but they are not automatically the table’s intended key.

Rank candidates using additional context:

• Identifier-like names such as _id, _key, or _sk
• Comments describing identity or table grain
• Governance tags and domain metadata
• Declared uniqueness
• Stability across partitions or snapshots
• Use as the parent side of plausible joins

These are supporting signals, not proof. Otherwise, every column named id becomes the ruler of its own tiny data kingdom.

Composite Keys

Keys are not always single columns. Common examples include:

(order_id, line_number)  
(account_id, snapshot_date)  
(country_code, local_customer_number)  
(device_id, observation_timestamp)

Databricks supports composite primary and foreign-key declarations, so declared column order must be preserved when reading Unity Catalog metadata.

For inferred combinations, test both uniqueness and minimality: the full combination must identify every row, while no proper subset should do so. Enumerating every possible combination quickly becomes impractical as the number of columns and candidate width grow.

A practical detector should therefore:

• Test declared combinations first
• Prioritize combinations suggested by names, comments, and profiling
• Stop expanding a combination once a subset is already unique
• Cap the maximum key width

The objective is credible key discovery — not an archaeological expedition through every possible column permutation.

---

Finding Plausible Foreign-Key Candidates

Prune Before Joining

Comparing every column with every other column creates a roughly quadratic search space, expensive scans, and many coincidental matches. Inclusion-dependency discovery across large schemas is already computationally demanding, so candidate pruning is essential.

Before reading values, require a plausible child-parent pair:

• Compatible data types and key widths
• A declared or statistically unique parent key
• Related names, comments, tags, or business meaning
• Plausible cardinalities
• Tables within the configured discovery scope

Obvious measures, free text, booleans, and timestamps should normally be excluded unless metadata provides a strong reason otherwise.

Separate Evidence from Hints

The detector should calculate hard evidence:

• Declared constraint status
• Parent uniqueness
• Type compatibility
• Child-value containment
• Orphan rate
• Observed join multiplicity

The agent can then interpret softer context such as similar names, shared tags, related comments, lineage, domain membership, and modelling conventions.

This separation matters because value containment is a prerequisite for a foreign-key relationship, not proof of one. Two unrelated code columns can contain exactly the same values.

Never Normalize Blindly

Casting every candidate to STRING may make the join executable, but it can also invent relationships.

For example, converting "00123" and "123" to integers collapses identifiers whose leading zeros may be meaningful. Lowercasing can merge case-sensitive codes, trimming can alter fixed-width identifiers, and numeric coercion can make values from unrelated namespaces appear compatible.

Any canonicalization must therefore be explicit, type-aware, recorded in the result, and reversible where possible. A relationship that exists only after undocumented coercion should receive a warning and a lower confidence score.

---

Measure Whether the Relationship Holds

Measure Inclusion, Not Just Overlap

A foreign-key candidate should behave directionally: every populated child key should resolve to a valid parent key. This is an inclusion dependency, a necessary — but not sufficient — condition for a foreign key. The parent side must also be a key, and the relationship must make semantic sense.

Symmetric measures such as Jaccard similarity are less useful here. A parent table may contain many valid but currently unreferenced keys, making overlap look weak even when every child row resolves correctly.

Child Row Coverage

child_row_coverage = matched_non_null_child_rows / non_null_child_rows

This answers: What proportion of child records resolves to a parent?

Use a left-semi join — or an equivalent existence test — to count matches without multiplying child rows when the parent contains duplicates. Use a left anti-join over the same non-null population to find orphans. Spark supports both join types directly.

orphan_rate = unmatched_non_null_child_rows / non_null_child_rows

When both metrics use the same population, orphan_rate = 1 - child_row_coverage.

Distinct-Value Coverage

child_value_coverage = matched_distinct_child_keys / distinct_non_null_child_keys

Row coverage can hide a long tail of invalid identifiers when a few valid keys appear very frequently. Distinct-value coverage exposes that problem.

Report both metrics: one measures affected rows; the other measures affected identifiers.

Parent Reference Rate

parent_reference_rate = referenced_distinct_parent_keys / distinct_parent_keys

This shows whether nearly every parent has children or only a small subset does.

A low rate is not automatically a defect. Many customers may legitimately have no orders. It is still valuable generation evidence because the synthetic data should preserve the expected proportion of zero-child parents.

Treat Nulls Separately

A null foreign key may represent an optional relationship, so it should not automatically count as an orphan. Calculate coverage over populated child keys and report the null rate separately.

For composite candidates, distinguish:

• Every component populated
• Every component null
• Only some components populated

Partial tuples cannot be tested as complete join keys and should trigger a warning. When validating a declared constraint, also preserve its configured match semantics rather than imposing a different rule during profiling. Databricks supports multi-column foreign keys and an optional MATCH FULL setting, although key constraints remain informational rather than enforced.

---

Determine Join Cardinality and Fan-Out

Classify the Observed Relationship

Once a candidate passes key and containment checks, inspect uniqueness and repetition on both sides. From parent to child:

• Unique parent keys with repeated child references indicate one-to-many
• Unique values on both sides indicate an observed one-to-one relationship
• Duplicate parent keys make the relationship inconsistent unless the proposed parent columns are incomplete

Treat optionality separately. A parent may have zero children, and a nullable child key may have zero or one parent. Also label cardinality as observed: uniqueness in today’s snapshot doesn’t guarantee that future data will behave the same way.

Repeated values on both sides don’t automatically prove many-to-many. They may simply indicate duplicate parent keys or the wrong join columns. Genuine many-to-many relationships are commonly represented through a bridge table that connects two parent entities.

Names remain hints, not evidence. A table called customer_accounts sounds convincing, but naming conventions have disappointed better systems than ours.

Preserve the Fan-Out Distribution

One-to-many is not enough information for generation. For each accepted relationship, calculate children per parent and retain:

• The proportion of parents with no children
• Counts for referenced parents
• Median and upper percentiles
• Maximum, skew, and long-tail behaviour

This distinguishes a model where most customers place one or two orders from one where a small group places hundreds. The synthetic generator must reproduce that distribution, not attach a uniformly random number of children to every parent.

Recognize Bridge Tables

A bridge — or junction — table often contains two or more foreign keys, repeated values on each foreign-key side, and a composite uniqueness rule or equivalent surrogate-key design. Examples include user_role, student_course, and product_supplier.

Record every parent relationship independently, then classify the table as a bridge when the combined evidence supports it. SDA 11 can then preserve the many-to-many structure and its pairing frequencies instead of generating random combinations that merely satisfy the foreign keys.

---

Build the Generation Dependency Graph

Turn Relationships into Dependencies

Represent each table as a node and every accepted parent-child relationship as a directed edge:

parent_table ➔ child_table

For an acyclic graph, a topological sort produces a generation order in which every parent appears before its children, ensuring that referenced keys already exist when foreign keys are assigned.

customers  
   ↓  
orders  
   ↓  
order_lines  
   ↓  
line_discounts

Bridge tables simply depend on multiple parents and should be generated only after all of them.

Detect Cycles Instead of Inventing an Order

Real schemas are not always directed acyclic graphs. Self-referencing hierarchies, mutual dependencies, and larger circular relationships prevent a complete topological ordering.

The detector should identify the affected nodes — ideally as strongly connected components — and mark them for a later strategy such as staged generation, deferred foreign-key assignment, or explicit configuration. Cycles must never be silently broken because that would replace a real dependency with an arbitrary one.

This tool produces the graph and its warnings. Solving cyclic generation comes later.

Preserve Uncertainty

Every candidate edge should remain auditable, including its confidence and decision:

• Accepted
• Rejected
• Awaiting review
• Untestable

The resulting artifact should also report which tables have no reliable parent and which cannot be safely ordered because of unresolved or cyclic dependencies.

“No reliable relationship detected” is not a failure. It’s safer than confidently generating a fictional one.

---

Confidence Scoring Without Confidence Theatre

Keep the Evidence, Not Just the Score

A single confidence number is useful for ranking candidates, but useless if nobody can explain it. Preserve the evidence behind every decision:

• Declared-constraint status
• Parent uniqueness
• Row and distinct-value coverage
• Orphan rate
• Type compatibility
• Metadata and name similarity
• Observed cardinality
• Exact, approximate, or sampled validation
• Warnings and applied transformations

Also version the scoring policy. A score is reproducible only when its inputs, thresholds, weights, and validation context are known.

Use Gates Before Weights

Some failures should not be averaged away. An incompatible type, non-unique parent key, or severe orphan rate may disqualify a candidate regardless of how well its names match.

After these hard checks, use a configurable score to rank the remaining relationships:

• High confidence: supported strongly enough for automatic planning.
• Medium confidence: plausible, but requires review.
• Low confidence: rejected or retained only as a weak hint.

A declared relationship with verified uniqueness and containment may be high confidence. An undeclared relationship can also qualify when structural and semantic evidence agree. Conversely, perfect overlap between two low-cardinality columns is weak evidence on its own — not proof that the relationship is false. A country code can be a legitimate foreign key when the parent is unique and the metadata supports the meaning.

Approximate counts and samples should lower certainty because they estimate rather than prove uniqueness or containment; Databricks’ approx_count_distinct, for example, explicitly returns an estimated cardinality.

Make Penalties Explicit

Reduce confidence for duplicate parent keys, orphaned children, generic names, unexplained coercions, partially null composite keys, temporal misalignment, sample-only checks, or relationships that hold only after filtering.

The final score is a prioritisation mechanism — not a probability, and certainly not proof of business meaning. Confidence without visible evidence is just optimism with decimal places.

---

Practical Implementation on Databricks

Reuse Existing Evidence

relationship_detector should consume:

• The normalized uc_metadata_reader artifact
• Table and column metrics from table_profiler
• The configured catalog, schema, and table scope
• Candidate-pruning and acceptance thresholds
• Profiling run identifiers and, where available, source table versions or timestamps

This avoids rescanning data for cardinality, null, and uniqueness metrics already calculated in SDA 05. Recording the source state also makes results reproducible: Delta tables can be identified by version or timestamp, although retained history depends on the table’s retention configuration.

Keep the Notebook Thin

Add the relationship logic as an importable package:

src/sda/relationships/  
    candidates.py  
    metrics.py  
    scoring.py  
    graph.py  
    detector.py  
notebooks/  
    06_detect_relationships.py  
tests/  
    test_relationship_detector.py

Each module has one responsibility:

• candidates.py prunes possible key pairs.
• metrics.py calculates uniqueness, coverage, orphans, and fan-out.
• scoring.py applies gates, weights, and confidence bands.
• graph.py builds dependencies and detects cycles.
• detector.py coordinates the workflow and produces the artifact.

The notebook should select the scope, call the package, persist the results, and display a concise summary. It should not become the place where three hundred lines of join logic go to retire.

Discover Broadly, Verify Carefully

Use two execution stages.

Candidate discovery works mostly from metadata and cached profiles. It compares key width, types, names, comments, cardinalities, and declared constraints, rejecting implausible pairs before running expensive joins. Approximate functions such as approx_count_distinct can help rank large candidate sets, but they return estimates rather than proof of uniqueness.

Relationship verification runs distributed checks only for the surviving candidates:

• Exact parent-key uniqueness where required
• Left-semi joins to count resolving child rows
• Left anti-joins to locate orphans
• Distinct-key coverage
• Join multiplicity and children-per-parent distributions

Spark supports left-semi and left anti-joins directly, allowing the checks to remain distributed without materializing matching key sets on the driver.

Sampling is useful for prioritisation, but accepted relationships should use exact validation whenever cost and scale permit. Rare orphan keys are precisely the defect a sample is most likely to miss.

Add Operational Guardrails

The first implementation should enforce a few unglamorous but important rules:

• Never collect complete key sets to the driver
• Cap candidates per table and maximum composite-key width
• Distinguish exact, approximate, and sampled evidence
• Make thresholds and scoring deterministic and versioned
• Reuse cached profiling artifacts
• Record execution time, candidate counts, and validation mode
• Attach source versions or timestamps where available
• Persist reasons for rejected, skipped, or inaccessible tables

The objective is not to run every possible join. It is to narrow the search intelligently, verify the strongest candidates, and leave an auditable explanation of every decision.

---

The relationship_detector Output Contract

Each evaluated relationship should produce a structured, versioned record containing both the decision and the evidence behind it:

{  
  "child_table": "sales.orders",  
  "child_columns": ["customer_id"],  
  "parent_table": "crm.customers",  
  "parent_columns": ["customer_id"],  
  "origin": "inferred",  
  "declared_constraint": null,  
  "parent_uniqueness_ratio": 1.0,  
  "child_null_rate": 0.031,  
  "child_row_coverage": 0.998,  
  "child_value_coverage": 0.994,  
  "orphan_rate": 0.002,  
  "parent_reference_rate": 0.621,  
  "cardinality": "many_to_one",  
  "confidence_score": 0.94,  
  "confidence_band": "high",  
  "decision": "accepted",  
  "validation_mode": "exact",  
  "generation_direction": "crm.customers -> sales.orders",  
  "warnings": [  
    "relationship_is_inferred_not_declared"  
  ]  
}

The production artifact should also preserve run and profile identifiers, source versions or timestamps, ordered composite-key columns, reason codes, sampling parameters, graph component and generation level, review status, scoring-policy version, and detector version.

This contract becomes the shared interface between discovery and generation. The planner uses it to order tables, validation jobs use it to test referential integrity, dashboards expose its evidence, and the audit trail records why each relationship was accepted, rejected, or left for review.

---

Testing the Detector

Build Small, Adversarial Fixtures

Use deterministic datasets that isolate one behaviour at a time:

• A valid declared one-to-many relationship
• A declared foreign key containing orphans
• A strong undeclared relationship
• An accidentally unique timestamp
• A nullable foreign key
• Composite primary and foreign keys
• A many-to-many bridge
• A self-referencing hierarchy
• Unrelated low-cardinality columns with perfect overlap
• A rare orphan likely to disappear in a sample

These fixtures should include deliberate false positives, not just happy paths. A detector tested only on obvious relationships will become extremely confident at detecting obvious relationships.

Assert Evidence, Decisions, and Graph Behaviour

Tests should verify:

• Exact uniqueness, coverage, and orphan metrics
• Correct null and partial-composite-key handling
• Preservation of composite-key column order
• Candidate pruning and ranking
• Cardinality and bridge classification
• Reproducible scores under a fixed scoring policy
• Deterministic tie-breaking between valid graph orders
• Explicit detection of self-references and cycles
• No automatic promotion of review-only candidates

PySpark provides testing utilities such as assertDataFrameEqual and assertSchemaEqual for comparing distributed results and schemas.

Finally, add an integration test using short-lived managed tables in a dedicated Unity Catalog test schema — not session-temporary views, which would not exercise the same catalog metadata path. Declare primary and foreign keys, run uc_metadata_reader and relationship_detector together, verify both metadata extraction and data validation, then remove the test objects. Primary and foreign-key declarations are supported on Unity Catalog tables and remain informational, making this test especially important.

---

Definition of Done

By the end of this article, relationship_detector should:

• Ingest declared primary, unique, and foreign-key constraints from the normalized Unity Catalog metadata.
• Validate declared keys against the current data rather than assuming informational constraints are correct.
• Detect plausible single-column and composite keys.
• Prune and verify foreign-key candidates.
• Calculate row coverage, distinct-value coverage, orphan rates, and parent reference rates.
• Classify observed cardinality and profile children-per-parent fan-out.
• Identify bridge tables, self-references, and cyclic dependencies.
• Build a generation dependency graph.
• Assign explainable, reproducible confidence.
• Emit a versioned artifact containing the evidence, decision, warnings, and validation context.

The tool is complete when downstream components can determine which relationships are safe to use, which require review, and why. It should never rewrite source constraints, publish inferred relationships as official metadata, or allow weak evidence to control generation automatically.

The objective is not to make the agent certain about every relationship. It is to make every relationship decision explicit, testable, and auditable.

---

Structure Is Not Meaning

The relationship_detector gives the Synthetic Data Agent a relational view of the source: which keys are credible, which joins hold, how tables depend on one another, and where uncertainty remains.

But structural evidence is not business meaning. Two columns may share a type, name, value range, and even complete value containment while representing different concepts. billing_country_code and shipping_country_code may overlap perfectly, but neither is a parent of the other; both may reference the same country dimension. Numeric identifiers may also be reused across unrelated systems, filtered snapshots may hide orphans, slowly changing dimensions may repeat business keys by design, and a generic status_id may refer to different code sets in different domains.

The agent can now validate declared keys, expose orphans, classify joins, measure fan-out, and build the dependency order required for generation. That is enough to preserve structure — but not meaning. It still cannot explain why premium customers behave differently, why cancelled orders require cancellation timestamps, or why some fields become null after a lifecycle event.

We have connected the tables. Next, the agent must learn the rules inside the rows. Because synthetic nonsense with perfect referential integrity is still nonsense.

---

When the agent finally learns joins and starts acting like a relationship counselor for tables.

---

🔗 Code & Music 🎧

The code for this article is available here:

🔗 synthetic-data-agent

🎧 YouTube playlist

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