Building a data model

Eg - Ride hailing example

Identify business process
Ask clarifying questions - eg:

• does it support ride sharing, or only single ride
• should data model support future bookings

Identify events and entities associated. Helps to figure out fact and dim tables.

List down arrtibutes of table
Eg - driverid, arrived at, pickup at etc

interviewer is looking for

• asking questions, gathering business requirements to translate it into a list of tables
• strength of dimensional modelling concepts
  • translate requirements into facts and dims
  • identify slowly changing dims
  • appropriately define relationships between tables etc
• Data model behaviour when a change comes in. Eg - turkey wants to change name to turkiye. How are those changes handled?

Questions:

• ride hailing companies - uber, ola
• food delivery - swiggy, zomato
• streaming platforms - netflix, youtube
• social media apps - instagram, facebook

Schema design types

1. Star Schema
2. Snowflake Schema
3. Galaxy Schema

Star

• central fact table connects directly to denormalized dimension tables
• Simple queries, fewer joins, good performance
• Duplication of some dimension data
• BEST FOR DRILL DOWN REPORTS (moving summarized data to more detailed levels like year -> quarter -> month ->day)

Eg:

• FactSales
  • DimCustomer
  • DimProduct
  • DimDate

Snowflake

• central fact table connects to dimension tables that are further normalized into related tables
• Less data duplication due to more normalization
• More joins and greater query complexity

Eg:

• FactSales
  • DimProduct
    • DimCategory

Galaxy

• multiple fact tables share common dimension tables
• Supports analysis across multiple business processes
• More comples to design and query

Eg:

• FactSales
• FactInventory
  • DimProduct
  • DimDate

Rule of thumb: Use a star schema for most BI dashboards and drill-down reports. The critical factor is designing dimensions with the correct hierarchies; drill-down capability itself does not require a particular schema.

Fact tables types

3 major types of fact tables:

1. Transaction fact tables
   1. Record individual business events as they occur. 1 row = 1 transaction/event
   2. Eg: sales record storing product id, quantity etc
2. Periodic snapshot fact tables
   1. records state of business at regular intervals (hour, day, week, month etc)
   2. Eg - daily account balances, oil tanker levels etc
3. Accumulating snapshot fact tables
   1. Tracks a business process from beginning to completion. Row is updated as process progresses through milestones.
   2. One row per process instance. Store milestone dates (fk to DimDate)
   3. Eg - order fulfillment.

Other types of fact tables:

1. Factless fact table
   1. Records an event or relationship without necessarily storing numerical measures. So typically only contains foreign keys to dimensions.
   2. Eg - Student attendence (table cols - dateId, studentId, classId)
2. Aggregate fact table
   1. Aggregate data from more granular fact tables
   2. Eg - monthly total sales of each product category
3. Consolidated fact table
   1. Combines measures from multiple business processes into a common fact table when compatible
   2. Eg - combine sales and returns into a common table, to compare financial impacts
   
   3.

Resources:

• holistics
• msft

Property of fact - additivity

This makes 3 types of facts

1. additive
2. semi-additive
3. non-additive

additive - values can be added across all relevant dimensions without losing its meaning

eg - sales amount

semi-additive

The fact can be summed across some dimensions, but not across others

Eg - account balance. We can add all account balances on one particular date. But we cannot add account balance across all dates.

Other examples - account balance, inventory on hand, headcount, outstanding loan balance

Non-additive

cannot be summed across dimensions.

Eg - profit margin. Total profit margin isn't simply a sum-total of all stored profit margins. It has to be re-calculated by adding all profit and all revenue, and then finding their ratio.

Types of dims

1. Static - never changes
2. Slowly changing
3. Conformed - used by multiple facts, and has the same meaning across the model. Eg - list of places.
4. Degenerate - fact storing dimensional values. Eg - invoice number
5. Junk - too many small dims put into one dim, even though each of those small dims are unrelated
6. Role-playing - values associated with multiple fats. Eg - dim_date can take the role of purchaseDate, holidayDate etc
7. Shrunken - dim that's further divided. Eg - dimproduct is further snowflaked into dimproduct_category

Misc

Bridge tables for m:n relationships

Resources:

• Manish Kumar playlist - https://www.youtube.com/watch?v=UiTvqSd52ak&list=PLTsNSGeIpGnGP8A74Ie1PgqHhewsqD3fv
• chatGPT
• Kimball - Dim modelling techniques