How can you name and document data warehouses effectively?

1 Naming conventions

Naming conventions are important rules or guidelines for choosing meaningful and consistent names for data warehouse objects. These conventions help to avoid confusion, ambiguity, duplication, and errors during development and usage. When creating naming conventions, it is important to use descriptive and concise names that reflect the business meaning and context of the data warehouse objects. Additionally, a consistent format and style should be used for different types of data warehouse objects, such as prefixes, suffixes, separators, capitalization, and abbreviations. Standard and common terms and abbreviations should be used that are familiar to the data warehouse users and developers, while avoiding jargon, slang, and acronyms that are not widely understood. Furthermore, reserved words, keywords, symbols, spaces, or special characters should be avoided as these can cause conflicts or errors in the data warehouse system. Finally, names should not be too long, too short, too generic or too similar to other data warehouse objects.

2 Documentation standards

Documentation standards are essential for creating and maintaining clear and comprehensive documentation of data warehouse objects, such as metadata, definitions, descriptions, relationships, constraints, transformations, and lineage. These standards help to ensure accuracy, completeness, and consistency of the information. To adhere to documentation standards, it is important to use a common format and structure for different types of data warehouse objects; a common language and style for writing the documentation; a common tool or platform for storing and accessing the documentation; a common process and procedure for creating, updating, reviewing, and approving the documentation; as well as a common method and metric for measuring and evaluating the quality and effectiveness of the documentation.

3 Naming examples

Naming conventions can be applied to data warehouse objects, such as tables, columns, and views. For example, a good name for a table storing sales data from different regions and channels would be 'sales_fact', while a bad name would be 'tbl1'. Similarly, a good column name would be 'region_code' and a bad one would be 'r'. Furthermore, a good view name would be 'sales_by_region_channel' whereas a bad one would be 'v1'. Applying appropriate naming conventions can help to improve the organization and readability of data warehouse objects.

4 Documentation examples

To illustrate how documentation standards can be applied to data warehouse objects, here are some examples of good and bad documentation for tables, columns, and views in a data warehouse that stores sales data from different regions and channels. For instance, a good table documentation would include the name of the table (e.g. sales_fact), the columns (e.g. sales_id, region_code, channel_code, product_code, quantity, price, and date), the relationships (e.g. sales_id is the primary key and region_code references region_dim), constraints (e.g. sales_id is unique and not null), transformations (e.g. quantity and price are converted to the same unit and currency). In contrast, bad table documentation would be too vague (e.g. “tbl1 is a table that has some data”). Similarly, a good column documentation would include the name of the column (e.g. region_code), its purpose (e.g. it stores the code of the region where the sales transaction occurred), its type (e.g. char(2)), and its values (e.g. NA for North America). On the other hand, bad column documentation would be too general (e.g “r is a column in tbl1 that has some data”). Finally, a good view documentation would include its name (e.g. sales_by_region_channel), how it is created (e.g by joining sales_fact, region_dim, and channel_dim), its columns (e.g region_name, channel_name, and sales_amount), filters (e.g date between 2020-01-01 and 2020-12-31) and calculations (e.g sales_amount is the sum of quantity multiplied by price). In contrast, bad view documentation would be too brief (e.g “v1 is a view that has some data”).

5 Naming pitfalls

Naming conventions for data warehouses can be tricky to follow and enforce, and there are several potential issues that can arise. Without clarity or agreement on the rules and guidelines among stakeholders, such as business users, analysts, developers, and administrators, or without enforcement or validation of these rules by the system, it can be difficult to ensure quality and consistency. Furthermore, when communication or collaboration among teams or projects is lacking, such as when sharing, reviewing, or synchronizing the names is not done properly, it can be hard to adapt the guidelines to changes in requirements like new sources, objects, or features.

6 Documentation pitfalls

Adhering to documentation standards for data warehouse objects is not always a straightforward process, and there may be some challenges or pitfalls that can affect the quality and completeness of the documentation. These can include a lack of time or resources to create or update the documentation, a lack of motivation or incentive to do so, a lack of access or visibility to the documentation, and a lack of alignment or consistency between the documentation and the actual data warehouse objects.

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?