hardScenarioCodingCase StudyMidSeniorStaff
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?
6 min readUpdated 2026-08-15
Answer
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.
Was this answer helpful?
Likely follow-ups
- Two tables both look like the customer master and neither is empty. How do you determine which one the business runs on?
- How would you handle a column whose meaning changed part-way through the history, with no flag marking the change?
- What would make you stop reverse-engineering and insist on an interview with someone in the business instead?
- You find rows that violate a constraint the schema does not declare. Is that a data quality problem or a clue?
Related questions
- The business told you a field is always populated, and in production it is null for a third of the rows. How do you work out what is going on and what do you do about it?hardAlso on customer-data-integration and sql7 min
- You get a messy customer export and three hours to turn it into something they can use. What do you build first?mediumAlso on customer-data-integration and data-profiling5 min
- You have read access to the customer's production database and there is no staging environment. How do you develop and test without putting their system at risk?hardAlso on customer-data-integration6 min
- Find me every customer with no order in the last thirty days, then tell me which ways of writing that get NULLs wrong.mediumAlso on sql4 min