Data Dictionary: The 'About' Page for Your Data
A data dictionary is the instruction manual for your database, defining what each piece of data means and how it's formatted. It's used by engineers to understand a schema or by analytics tools to interpret columns. The biggest footgun is letting it go stale.
The mental model
A data dictionary is the official map and legend for your database. It doesn't hold the actual data, but rather the information about the data (the metadata). Think of it as the README file for your database, answering questions like "What does the acct_stat column mean?", "Is order_date a timestamp or a string?", and "How does the users table connect to orders?"
How it works
Data dictionaries come in two main forms. An active dictionary is an integral component of a Database Management System (DBMS), like in Oracle. The database engine itself uses it to validate queries and manage its structure. When you alter a table, the dictionary updates automatically. A passive dictionary is maintained separately, often as a spreadsheet or in a dedicated software tool. This is more flexible but requires manual updates, making it vulnerable to becoming outdated.
When to use it
A data dictionary is essential when a database is shared by multiple teams or applications, ensuring everyone interprets the data consistently. It's a cornerstone of data governance for tracking data lineage and ownership. It's also invaluable for onboarding new developers, allowing them to understand a complex data model without constant interruptions. Business intelligence tools rely on it to build accurate reports.
When not to use it
For a very small, single-purpose project with a few self-explanatory tables managed by one person, a formal data dictionary can be overkill. If your schema consists of obvious columns like user_id, user_email, and creation_date, the overhead of maintaining a separate document may not provide much value. The deciding factors are the complexity of the schema and the number of people who need to understand it.
One canonical example
Consider a dictionary entry for a user_status column in a users table. The entry would specify details like: Table Name (users), Column Name (user_status), Data Type (INTEGER), Description ("The current status of the user account"), and Allowed Values ("1=active, 2=suspended, 3=pending_verification, 4=deleted"). This single entry prevents a developer from trying to insert a string like "active" into the column or an analyst from misinterpreting what the number 2 means in a report.
Interview question
In which scenario would a data dictionary provide the most significant benefit?
- a.A database where the primary goal is maximum performance for high-volume transaction processing.
- b.A database managed by a single developer for a short-term, experimental project.
- c.A small, personal database with straightforward table and column names.
- d.A complex database schema shared across multiple departments and integrated with various applications.Correct
Why? this is the answer
The card states that a data dictionary is essential when a database is shared by multiple teams or applications and for complex schemas, ensuring consistent interpretation. For small, simple, or single-user projects, it can be overkill, and its primary role is not performance optimization.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data modeling — each one lists the topics its interview covers.
See open roles