More and more credit unions are investing in the democratization of data so that report writers, data analysts, and business stakeholders can access relevant data in a self-service manner without seeking help from data analytics or IT teams. That is where they are investing in either building their data warehouse or buying one of the solutions.

Data modeling is how data is organized in your data warehouse or lakehouse so that your users can get a complete picture of what information is available in the warehouse and locate the data they are looking for.

In many ways, a data warehouse is very similar to a Home Depot warehouse. There are many sources for the items. The staff takes the items from the dock and then reorganizes them in the store to promote quick self-service by the consumers. Similar items (e.g., electric blubs) are stocked close to each other and organized by type, size, usage, supplier, brand, etc., so end-users can self-serve themselves. Also, all the electrical stuff is kept close to each other, making it easy for people. Similarly, good data models are designed to promote quick self-serve for the users leading to higher adoption and usage.

In the last CULytics data analytics roundtable, we extensively discussed data modeling and the different principles or design considerations when defining/adopting specific data modeling standards for your data warehouse or data lake.

Here are some of the design considerations. 

1. Ease of access to the data by the users is the foremost for any data modeling. If all the data needs from the business are going to be met through the technical team, then the data should be modeled in a way that makes the most sense for the technical team; otherwise, you should make it easy for business users to access it. 

2. The flexibility of bringing more data into the warehouse. The data model should be flexible to get more disparate data from multiple sources that make the most sense for your business users. In addition, the business is constantly changing and evolving, with new data sources, new versions of existing data sources being introduced, and some old data sources going away. The data model should be able to accommodate all these changes. 

3. Speed of development – How quickly can the technical team bring new data sources into the warehouse? E.g., When new data sources are brought in, does it require extensive planning and design, or can these standards evolve as new data is brought in? 

4. Performance – How quickly can the users find the data and use it? E.g., Are you summarizing the suitable tables and columns ahead of time so that the data is available to the users immediately instead of waiting for an extended duration to run time-consuming queries? 

5. Storage Cost – In older days, storage cost was high; therefore, some traditional data modeling standards focused on redundancy at the expense of other factors such as performance and usability. Since the storage cost is now low, it allows you to focus on attributes that matter more to the business and technical stakeholders. 

6. Security – Data in the organization includes PII data. The data model should ensure appropriate protection is applied to the data coming from various sources and indicate to users how they can access secured data based on their access rights. 

7. Availability – How long does the technical process take to get the data from source systems to the data warehouse? Typically, a well-defined small time window is available to bring data from different systems into the warehouse. 

8. Ability and speed to regenerate the data model from raw data. There are times when, due to some data issues or other reasons, you need to recreate all the tables in the data warehouse or reorganize the data based on a new model. The data model should allow this flexibility and keep the raw data available. 

A data model is the heart of the data warehouse program. Changes to the data model usually lead to much work (imagine reorganizing the warehouse) to the reports, dashboards, documentation, and data models. Therefore, consider the short- and long-term requirements when defining the data modeling standards.