Phase 2: Enterprise Data Engineering · 55 min · Python · PostgreSQL · SQL

Schema Recovery & Undocumented Databases

The DBA left five years ago. The schema is in your hands.

Hiring signal: Practical coding rounds at AI companies test SQL depth: candidates who can write recursive CTEs for hierarchical data and use window functions for data profiling pass. Candidates who can only do basic SELECT/JOIN/WHERE fail. Schema recovery from information_schema introspection is a real FDE task that demonstrates the ability to work with unfamiliar, undocumented client databases.

What you will learn

Reverse-engineer undocumented databases using information_schema introspection
Discover foreign key relationships without constraints: column matching, data overlap, join validation
Write recursive CTEs for hierarchical data: org charts, bill-of-materials, category trees
Use window functions for data profiling: LAG/LEAD, ROW_NUMBER, PERCENTILE_CONT

What You'll Learn

This lesson takes approximately 55 min. By the end, you will be able to:

Reverse-engineer undocumented databases using information_schema introspection
Discover foreign key relationships without constraints: column matching, data overlap, join validation
Write recursive CTEs for hierarchical data: org charts, bill-of-materials, category trees
Use window functions for data profiling: LAG/LEAD, ROW_NUMBER, PERCENTILE_CONT

The Problem

You arrive at the client site. They have a PostgreSQL database in production for 12 years. The original DBA left five years ago. There's no schema documentation, no ER diagram, and 340 tables with names like tbl_proc_v2_final. Your RAG system needs to query this database. This lesson teaches the systematic approach to schema recovery.

Unlock the full lesson

You've read the first 2 sections. The rest of this lesson covers Schema Introspection with information_schema, Discovering Hidden Relationships, Recursive CTEs for Hierarchical Data, Window Functions for Data Profiling, Practical Application, What Hiring Managers Look For, Resources, Key Takeaways, Next Steps — plus a hands-on lab, quiz, and project artifact.

Create a free account to unlock Phase 0 and Phase 1 of every course — no credit card.