(Snowflake Series): Dimensional Modelling

-

In modern analytics, efficiently organising and connecting business data is essential for meaningful insights. Dimensional Modelling schemas such as the star and snowflake schema are power data models that can be used to building high-speed analytical databases in platforms like snowflake. 

In this article, we will look into:

what is dimensional modelling? What are the core components of dimensional models? What are the different schemas in dimensional modelling? When to use which schema?

What is Dimensional Modelling?

Dimensional modelling:

- is a design methodology specifically geared for data warehouses and business intelligence (BI) systems

- were the main goal is to organise data so it can be queried quickly and intuitively

- empowering both simple reporting and complex analytics. 

Key Features of dimensional modelling

1. Simplifies data structures for end-users

2. Supports efficient aggregations and slicing/dicing of data

3. Optimised for read-heavy analytical workloads

What are the core components of dimensional models?

Dimensional models are designed around Fact & dimension tables. 

Fact Table:

- Stores quantitative, measurable business data (e.g., sales amounts, revenue, units sold).

- Includes keys linking to dimension tables, and numerical metrics called “measures.”

Dimension Table

- Contains descriptive information, or context, for facts (e.g., product name, customer location).

- Typically textual, less frequently updated than fact tables.

What are the different schemas in dimensional modelling? And when to use which?

Star Schema:

- has a central fact table connected directly to multiple dimension tables.

-  used when simplicity and speed are most important.

Snowflake Schema:

- is a normalised extension of the star schema. Dimension tables are further split into related sub-tables.

- used when data space savings and eliminating duplicated information are priorities.    

Conclusion:

Dimensional modelling is a vital approach for structuring data in analytical environments, making it easier for users to derive insights from complex datasets. By focusing on fact and dimension tables, dimensional models facilitate efficient processing and querying of data, crucial for business intelligence tasks.


Comments

Post a Comment

Popular posts from this blog

(Commentary): Trusting the process

-
 Life is a very puzzling thing. Sometimes when we frantically chase after something, we do not get it. But, we go past the need of wanting it, we easily get it.  Success is similar in that sense. Whether it is success in career, relationship, happiness, or money. When we chase after love and relationship, we end up becoming "needy". When we chase after money, we are labelled as "greedy". It is not to say that we should not chase after things, or wholeheartedly pursue things.  I believe the nuance there is the difference between chasing "frantically" and chasing "wholeheartedly". When we say frantic, we often mean that the person is in such desperation that they believe that they cannot live without. When we say wholeheartedly, we mean that the person is sole focus and pure intention pursuing something .  The intention and the mood with which (the state of mind) that we pursue a goal is important. So what is the state of mind we should cultivate...
Read more

About me

-
Image
 Hello World! In this ever-changing world of AI, returning to blogging or blogger on 2026 is the last thing that I expected of myself. I did not even know if blogger was still around but I just thought that I should be a producer instead of a consumer. Consuming knowledge after knowledge, course after course, can be an energy consuming task. While the advent of AI has made the consumption and breakdown of knowledge better, there is still no better way to retain and understand knowledge then to teach or put it into practice.  This blog series is an small attempt from me to do that. To becoming a better version of me. To improve 1% every single day.  Promise to oneself: "It is okay if you do not blog for a long period of time, just jump back in and do it. Just do it." Hi, I am Lokesh. This is about my learning journey. I am a techie, a student, and a dad.   I am currently invested in learning about DevOps, AI, and MLOps. Balancing family, work, and studies.  ...
Read more

(Hat) AI Engineer

-
Image
 AI Engineer is probably one of the most popular roles right now. Many people aspire to be AI Engineer without fully understanding on what the role is about.  In this article, we will look at: What is an AI Engineer? What an AI Engineer does? How is different from the other AI roles that people might get confused with? AI Engineer: - is responsible for designing and developing AI Models that can be used in various applications - work across different stages of the AI Development life cycle, including data collection, pre-processing, model training, and deployment - integrate the AI models into existing systems and ensuring they function correctly with a broader architecture - also fine-tune models for efficiency and accuracy What is the value that AI Engineers bring to an Organisation? That question must be answered by what is the value that AI brings to an organisation. Ultimately, for any organisation where AI is not the product or service that they provide, it is about 2 th...
Read more