Canonical Data Model: A Modern Approach to Unified Business Insights

Explore the importance of the canonical data model in ensuring unified, accurate business insights using the Medallion Architecture with Snowflake.

Prashanth Meka

Salesforce Practice Lead

Three things have happened in recent times, giving rise to the importance of the canonical data model (not that it was ever not important):

1. The advent of ELT as an equal or superior methodology to ETL, means that data will be transformed not at source or in transit, but at the destination
2. Acceptance of the Medallion Architecture, legitimizing the progressively increasing quality of data with each layer
3. An understanding that every business needs a source of truth for their data (sales cannot look at a dashboard with $1 million in quarterly revenue when marketing is looking at another dashboard with $5 million in revenue for that same quarter).

What is a canonical data model?

A canonical data model is a true representation of a business through data, reflecting all its products/services, suppliers, customers, distributors, employees, partners, and every other aspect of their business.

Just like every department is tied to other departments, every piece of data generated (or consumed) by a department is connected to other data generated (or consumed) by other departments.

So, when done correctly, a canonical data model brings together data from various systems across all the departments into a single data warehouse, which can be the source of truth for the business. It shouldn’t matter that each department’s systems store data in a different database, publish data in a different format (and at a different frequency), and with different schemas. The goal of the canonical data model is to bring all this information together in a single, logical set of relationships that can function as the source of truth for the business.

This canonical data model can then serve as the basis for executive reporting, managerial decision-making, forecasting, cross-functional alignment, and for taking action. In summary, the canonical data model is the beating heart of business analysis, planning, and action.

It is a reasonable assumption that businesses within any given industry would have the same canonical data model. There might be some extensions based on business strategies. For example, a retailer who offers a Loyalty Program might have a Membership earn-and-burn component to their data model which a retailer who doesn’t have a Loyalty Program wouldn’t. Apart from such nuances to business strategies, every business within an industry should, in theory, have the same canonical data model.

How do you build the canonical data model?

Let’s take a minute to look at the Medallion Architecture. The Medallion Architecture has 3 layers:

• Bronze: where data gets ingested as-is from various systems. This can serve as the data lake, storing unstructured, semi-structured, and structured data without obsessing over the quality of the data.
• Silver: where Bronze data gets transformed into a clean, high-quality, unified, and augmented dataset that can serve as the canonical data model.
• Gold: where the Silver data is activated to various downstream applications - be that analytics, data science, or business applications such as marketing automation platforms

So then, 2 things need to happen to build the canonical data model:

1. We need to define a schema that is accepted universally (within the business) as the true representation of the business
2. We need to build transformations that convert data from the Bronze layer into the Silver layer.

What happens after you build the canonical data model?

Given that businesses are ever-evolving, so are their canonical data models. For instance, a bank that has just launched an insurance product must now incorporate Policy and Claims information into their canonical data model.

Further, the purpose of the canonical data model is to serve as the source of truth for all downstream applications. So building out the data model is not the end but rather the beginning of your data product. You might choose to create a Gold layer where you expose one subset of your data to the FP&A team for forecasting, then a different subset of your data to the marketing team so they can run campaigns, and finally a third subset of your data to your supply chain team so that they can optimize inventory.

Benefits of the canonical data model

According to Gartner, businesses lose about $12.9 million every year due to poor data quality. While the canonical data model by itself cannot solve all data quality problems, it can help in the following ways:

• Ensure that every data consumer is working off of the same data (going back to the example of different revenue figures in sales dashboards versus marketing dashboards at the beginning of the blog)
• Nullifies any quirks in source systems. For example, a bank might have System1 that allows one account to be tied to multiple customers. On the other hand, System2 might allow an account to be tied to only a single customer. In this situation, System1 would probably have more customers than System2. By bringing data from System1 and System2 into the bronze layer, and then forcing them to reconcile in the silver layer, you ensure that the customer count is normalized.

Summary

As you build your data strategy, make sure you spare enough thought to the canonical data model and how you can bring it to life. The Snowflake data platform lends itself well because:

1. It allows you to create the Medallion architecture (bronze, silver, and gold layers)
2. It can absorb unstructured, semi-structured, and structured data into the Bronze layer
3. It is flexible enough to facilitate the creation of your business’ unique canonical data model.
4. It allows you to transform your Bronze data into the canonical data model within the Silver layer
5. It facilitates easy sharing of data to different internal and external consumers through the Gold layer

Are you ready to unify your business data and gain accurate, actionable insights? As a Snowflake Partner, Accelerize 360 can help you implement a robust canonical data model with the power of the Snowflake data platform.

Contact us today to define and execute a data strategy that ensures your entire organization operates from a single source of truth. Reach out now to transform your data into a strategic asset and drive your business forward.

‍Get in touch with Accelerize 360 and start your journey towards a cohesive, high-quality data infrastructure.