Piotr Kononow
Data Dictionary is a repository of information about data in a database or a data set (a type of metadata).
Passive data dictionary is a data dictionary that is not part of and managed by the DBMS. Unlike in the case of active data dictionary, changes in database structure need to be applied in passive data dictionary manually or with dedicated software.
Benefits of passive data dictionary
You may be wondering that since metadata is not managed automatically and needs to be updated manually or as a separate project it is worse solution than active data dictionary. There are more things to consider than managing changes.
1. Central data dictionary repository
Active data dictionaries are tied to specific database or server and you cannot easily look into data dictionary of databases located on different servers and engines. Passive data dictionary has the capacity to centralize metadata in one place.
2. Unification of metadata from different DBMS
Active data dictionaries are vendor specific and they are quite different from one another. To find list of tables in Oracle, SQL Server and Redshift databases you need to know and use different catalog tables.
3. Additional metadata
Metadata in active data dictionary is limited only to what DBMS provides, which is usually limited to very basic metadata (read: what can you comment in Oracle database).
4. Do not interfere with database
To update active data dictionary you need to impact schema of the database which is often not possible. People doing the documentation cannot go into schema and update comments, not to mention to define foreign key constraint to show table relationships. Passive data dictionary doesn’t have such limitations - metadata can be easily updated without the risk of negative impact on the database.
5. User friendly access (UI)
Passive Data Dictionaries often provide user friendly interface with search functionality, while active data dictionary requires accessing with SQL or similar query language.
Data Dictionary export from Dataedo
Disadvantages of passive data dictionary
There are downsides of implementing of passive data dictionary in organization.
1. Requires manual maintenance
The obvious disadvantage is that passive data dictionary requires manual, or independent form DBMS, maintenance of the metadata to apply all the changes in database structure. This requires work and it a potential point of failure.
2. Metadata can be out of sync from database
A consequence of the previous point is that metadata in passive data dictionary can be at times out of sync from the database.
3. Additional costs
Passive data dictionaries require additional work and often separate dedicated software which involves additional costs.
Extension to active data dictionary
Both, active and passive, data dictionaries are not really competing with one another, but serve different purposes. In fact, passive data dictionaries get metadata from active data dictionaries so you can think of them as an additional, detached layer on top of the latter.
Tools
There can be many different approaches to passive data dictionary. There can also be multiple passive data dictionaries in organization used for different databases, purposes, used by different people and tools and at a different stage of the lifecycle of the database. Those tools can be:
- Data Catalogs
- Data integration/ETL metadata repositories
- Data modeling tools
- Manual spreadsheets
- Custom implementations
Dataedo
Good example of a passive data dictionary tool that collects metadata from existing databases is Dataedo, a data dictionary tool and lightweight data catalog. Dataedo automatically extracts metadata from various databases into central repository, allows to add additional metadata, such as comments, table relationships, business terms and additional descriptive fields, and exports documentation into interactive HTML pages for easy sharing.
Data Dictionary in Dataedo. [See live sample]