The customer's schema has no documentation and the person who designed it has left. How do you work out what the tables actually mean?

Short answer

Read the data rather than the DDL: profile every column for cardinality, null rate and value distribution, use foreign-key-shaped joins to test relationships you suspect, and confirm each conclusion against a report the business already trusts before you build on it.

Column names lie, distributions do not

The first temptation is to read the DDL and build a mental model from names and types. This fails in a specific and expensive way: names record what somebody intended in the year the table was created, and the data records what the organisation has been doing since. A column called status with a varchar(2) type and eleven distinct values in production is not a status enum, it is at least two overlapping conventions from different eras, and the DDL tells you none of that.

So the reliable order of work is to profile first and read the schema second, using the schema only to generate hypotheses that the data then confirms or kills. What you want from every column that matters is its null rate, its distinct count relative to row count, its top values by frequency, and its minimum and maximum where it is ordered. Those four figures identify most columns without anyone explaining them: a distinct count equal to the row count is an identifier, a handful of values covering ninety-nine percent is a category, a date range that stops abruptly two years ago is a deprecated field somebody forgot to drop.

-- Profile one column without scanning the table repeatedly.
-- COUNT(col) skips NULLs while COUNT(*) does not, which is what
-- makes the null rate fall out of the same pass.
SELECT
  COUNT(*)                                        AS rows_total,
  COUNT(policy_status)                            AS rows_populated,
  COUNT(DISTINCT policy_status)                   AS distinct_values,
  MIN(effective_date)                             AS earliest,
  MAX(effective_date)                             AS latest
FROM claims.policy;

-- Then the value distribution, which is where the eras show up.
SELECT policy_status, COUNT(*) AS n
FROM claims.policy
GROUP BY policy_status
ORDER BY n DESC;

The second query is the one that repays the effort. When it returns ACTIVE at 60%, A at 30%, active at 4% and seven other values in the tail, you have learned that there was a migration, that it was incomplete, and that any filter you write on this column has to handle all three forms. No document would have told you that, and a colleague's recollection probably would have been wrong.

Test relationships, do not infer them

Undocumented schemas are usually also underconstrained: the foreign keys exist in the application's head rather than in the database. That means join keys have to be discovered, and the discovery is a measurement rather than a guess. Take a candidate pair of columns and ask what fraction of the child rows find exactly one parent. A relationship that resolves for 100% of rows is a real key. One that resolves for 94% is either a real key with an orphan problem worth naming, or the wrong column entirely and you are being fooled by overlapping ranges of integers.

-- Does claim.policy_ref really point at policy.policy_no?
SELECT
  COUNT(*)                                       AS child_rows,
  COUNT(p.policy_no)                             AS matched,
  COUNT(*) - COUNT(p.policy_no)                  AS orphans
FROM claims.claim c
LEFT JOIN claims.policy p ON p.policy_no = c.policy_ref;

Run the same shape in reverse to check cardinality: if a single parent matches thousands of children where you expected one, the column is not what you thought. This is also where you discover the join that requires a trim, a cast or a leading-zero pad, which is extremely common in schemas whose identifiers were once typed by humans and is the sort of detail that silently drops a tenth of the data if you miss it.

Anchor everything to a number the business already believes

Profiling tells you the shape of the data. It cannot tell you which table the organisation actually runs on, and undocumented estates typically contain several plausible candidates: an original table, a replacement, a reporting copy and something a departed analyst built for one project. The way through is to find a report, dashboard or regulatory return that the business trusts and reproduce one of its figures from the raw tables.

That exercise is worth more than any interview, because it forces every assumption into the open at once. To get the month's claim count to match, you will discover which table is authoritative, which status values are excluded, whether cancelled rows are soft-deleted with a flag rather than removed, and what the reporting cut-off is. When the number matches, you have a validated path through the schema. When it does not, the gap is a specific question you can take to a human — and a specific question is one somebody can answer, unlike "can you explain this database".

Profiling is not free on someone else's production system

You are frequently doing this against the customer's live estate, and a full COUNT(DISTINCT ...) across a wide table at ten in the morning is a genuine way to make yourself unwelcome in your first week. Agree a window, ask which tables are hot, and prefer bounded work: profile a recent date range first and widen it, sample where sampling would not distort the conclusion, read from a replica if one exists. Check whether the platform exposes statistics the optimiser already collects, since approximate distinct counts and null fractions gathered by the database cost you nothing and are often good enough to prioritise which columns deserve a real scan.

There is a professional dimension to this beyond politeness. An outsider whose queries caused a slowdown loses the access they need, and access is the whole engagement. Being visibly careful in week one buys latitude in week six when you need to run something expensive.

Write down your inferences with their confidence attached

Everything above produces knowledge that lives in your head, and the specific failure this creates is not forgetting it — it is that the next person, including you in six weeks, cannot tell which parts were verified and which were assumed. Keep a running data dictionary as you go, one line per column that matters, recording what you concluded, the evidence, and how sure you are. "policy_status: three conventions, ACTIVE/A/active all mean live — confirmed against the monthly regulatory count" is a durable asset. "policy_status: probably the status" is a trap you have set for yourself.

This artefact is also the most valuable thing you leave behind. The customer knows their schema is undocumented and has been unable to fix it because nobody had a reason to go through it column by column. You did, as a side effect of the actual work, and handing that back is often remembered longer than the system you built.

Where this goes wrong: mistaking a convention for a rule

The trap that catches experienced engineers is finding a pattern that holds across every row you looked at and encoding it as an invariant. amount is never negative, so you make the column unsigned. Every claim has exactly one policy, so you make the join inner. Both conclusions were true of the data you profiled, and both are properties of business behaviour over the period you sampled rather than of the system. Refunds start appearing in December, a legacy migration in the archive has claims with no policy, and your pipeline now drops rows or fails.

The defence is cheap: express the pattern as a monitored expectation instead of a hard assumption. Let the row through, count the violations, and alert when the count moves. An undocumented schema has no constraints precisely because the organisation has been permitting exceptions for years, and your integration will meet them all eventually.

In an undocumented estate, the DDL is a historical document and the data is the specification. Trust a query you ran over an explanation you were given, and reconcile to a number the business already stakes its reputation on.

---

© 2026 Preptima. Originally published at preptima.com.