You are redesigning a schema for a system that has been running for years, and the only artefacts available are production data and conversations with domain experts. How do you establish which functional dependencies genuinely hold, and what goes wrong if you get them wrong?

## Why this is a judgement problem Functional dependencies are constraints over every legal state, and no dataset is a complete record of legal states. So dependency discovery is inference under uncertainty: data narrows the space of candidates, domain knowledge fixes them, and constraints defend them going forward. ## Step 1 - mine candidates from data For each plausible pair, count distinct right-hand values per left-hand value; more than one is a counterexample. Automated profilers do this exhaustively over attribute pairs and small combinations, but combinatorics limit exhaustive search to modest left-hand sizes, so guided testing of business-plausible candidates beats brute force on wide tables. Run the profiling over **full history**, including archived partitions and rows produced by long-dead import paths, since those are where violations concentrate. ## Step 2 - subject each survivor to the traps **Temporal validity.** The most frequent false positive. A price, an address, a tax rate or a display name can each hold a dependency at a single point in time and fail across history. The clarifying question is never "has this changed?" but "is this allowed to change, and if so, do we keep old rows?" If old rows are kept, the dependency belongs to a *versioned* determinant such as `(product_id, valid_from)`, not to `product_id`. **Scope and multi-tenancy.** `order_number -> customer_id` may be true within a tenant and false globally, meaning the true dependency is `(tenant_id, order_number) -> customer_id`. Any dependency mined from a single-tenant-dominant dataset is suspect, because one large tenant can mask the collision. **Nulls.** Standard SQL FD semantics are awkward with nulls, and grouping treats nulls as equal in some contexts and not others. Nullable columns should be tested with explicit predicates, and the answer to "does this determine that?" usually differs for the null and non-null cases - which is itself a signal that the column is optional for a reason and probably belongs in a different relation. **Accidental enforcement.** A dependency may hold because exactly one service writes both columns in one statement. That is not a domain rule; it is an implementation coincidence. The question to ask is who else writes these columns - backfills, admin tools, support scripts, other services - and whether they are all bound by the same rule. **Selection bias.** Filters silently applied to the export (recent rows only, active records only, one region) hide exactly the violations you need. Soft-deleted and cancelled rows are frequent sources. ## Step 3 - confirm with domain owners, precisely Interviews fail when the question is vague. "Does a customer have one address?" gets "yes" from someone thinking of the common case. Better questions are adversarial and concrete: "Can two active orders ever carry the same external reference? What happens today if a partner resubmits one? Has support ever had to merge two records because of it?" Incidents and support workarounds are the best evidence, because they are where exceptions surfaced. Write each confirmed dependency down with its owner and the rule that justifies it. A dependency without a named business rule behind it is a guess. ## Step 4 - encode and let the database prove you right An asserted dependency should become a declared constraint - typically uniqueness on a determinant that is a candidate key, or a check that pins the scoping column. Two benefits: violations become impossible rather than merely unobserved, and the act of adding the constraint to existing data is itself the strongest test you will ever run. If the constraint cannot be created because existing rows violate it, the dependency was false and you found out before the redesign, not after. Where the volume makes a blocking validation impractical, add the constraint in a non-validating mode where the engine supports it - new writes are checked immediately - and validate the historical backlog separately. ## What failure looks like - **Wrong candidate key.** Keys derive from dependencies, so a false dependency yields a key that is not unique. Everything built on it - references from other tables, caches, idempotency logic - inherits the error. - **Unsafe decomposition.** The lossless-join test is a closure computation over the asserted dependencies. Assert a dependency that does not hold and the test approves a split that is genuinely lossy; the fragments are then written independently and the original combinations become unrecoverable. This is the failure that destroys data rather than merely annoying people. - **Production rejections.** A uniqueness constraint derived from a false dependency starts rejecting legitimate writes, usually for the rarest and most important customer. - **Wrong deduplication.** "These rows have the same determinant, so they are duplicates" deletes real, distinct records. ## How to hedge Stage the risk. Assert the dependency, add the constraint, run it in production for a period, and only then perform the structural change that depends on it. Keep the original wide table readable until the fragments have been reconciled. For anything irreversible, require a second, independent confirmation of the dependency - one from data, one from a named domain owner.