113 ) how to find the - grain and granularity

 

  how to find the grain 

• Grain is stated using column names (or the combination of attributes/keys) that uniquely define a single row (e.g., Policy_ID + Sale_Date), not a numeric count of columns.

• Granularity is expressed in levels of detail or hierarchy (e.g., transactional level, daily level, monthly level, or region level) describing how coarse or fine the data is.

 Requirements: 

    Practical Example: Insurance Policies Sold

Let’s use the scenario the interviewer provided: "

The client wants to know how many policies are sold per month in a region."

Step 1: Break down the request

• Metric: Number of policies sold (Count of Policy ID).
• Dimensions: Time (Month) and Geography (Region).

Step 2: Determine the atomic level (The Grain)

If you build a table where the lowest level of detail is simply Region + Month (e.g., "North region in January = 500 policies"), you meet the immediate requirement.

However, data warehousing best practice dictates that you should always capture data at the lowest operational grain possible. In the real world, a policy isn't sold at a "region" level; it is sold by a specific agent, to a specific customer, on a specific day.

Therefore, the true atomic grain of the underlying data should be:

Grain = One row per policy sale (Captured by: Policy_ID, Sale_Date, Agent_ID, Customer_ID, Branch_ID, Region_ID).

otherwise if customer just asked for number of (policies sold per month) then 

• • Grain: Policy_ID + Sale_Date

  • Granularity: Transactional / individual policy sale level (Monthly )

• 
  

Step 3: Answer the Interviewer's Follow-up — Weekly, Monthly, Yearly Views

Once you establish the base transaction grain (one row per policy sale), how do you handle weekly, monthly, or yearly requirements?

• The Base Grain Stays the Same: You store the fact table at the lowest transactional grain (daily/per policy).
• Aggregation Handles the Time Periods: You don't usually create separate physical tables for weekly, monthly, and yearly data unless performance requires pre-aggregation. Instead, you use a Time Dimension table linked to your policy fact table.
• How Reporting Works:
• For a weekly report, your BI tool rolls up the daily policy sales using the week attribute in the calendar dimension.
• For a monthly report, it rolls them up by month.
• For a yearly report, it rolls them up by year.

Interview Answer Summary: "To find the grain, I look at the lowest level of business activity needed to answer the question. For the policy example, while the client asks for monthly regional totals, the true operational grain is individual policy sales. By storing the fact table at the lowest transactional grain and leveraging a date dimension, we can easily aggregate the data to answer weekly, monthly, or yearly queries dynamically."

=====================================================

Breaking Down the Fact Table: Columns, Grain, and Reporting

1. What Are the Columns of the Fact Table?

Based on our insurance policy example, the fact table contains two types of columns:

• Foreign Keys (Dimensions): IDs that link to dimension tables, such as Date_ID, Region_ID, Agent_ID, Customer_ID, and Policy_ID.
• Measures (Metrics): Numeric values you want to measure, such as Policy_Count (set to 1 for each row) and Premium_Amount.

2. How Do You Create the Grain?

You create the grain during the data modeling and ETL (Extract, Transform, Load) process. By designing the table's primary key to be a combination of unique identifiers—such as Policy_ID and Sale_Date—you ensure that every single row represents exactly one individual policy sale. You load the data from your source system at this exact, lowest level of detail without pre-aggregating it.

3. Do You Document the Grain Anywhere?

Yes, you always document the grain because it is critical for anyone using the data warehouse. It is typically documented in:

• Data Dictionaries: A reference document that explains what each table and column means.
• Data Models / ER Diagrams: Visual maps of your database design.
• Technical Architecture Documents: Design specifications created during the project planning phase.

4. How Do You View Weekly, Monthly, and Yearly Reports?

You view these time-based reports by using the Date_ID column in your fact table. This column connects to a separate Date Dimension (Calendar Table). The Date table contains labels and numbers for every day, week, month, and year. When you want a monthly report, your tool groups the policy sales by the month attribute found in that Date table.

5. Are You Going to Make a Report or Run a Query?

You actually use both:

• Queries are written in SQL to pull and group the raw data from the fact table based on the time period you need.
• Reports (like dashboards in Power BI or Tableau) take those queries and turn them into visual charts and graphs so business users can easily see their weekly, monthly, or yearly numbers.

=====================================================

Complete Step-by-Step Guide: From Requirements to Weekly Reports

Here is the complete end-to-end process, using our insurance policy example to show how a business request turns into a final weekly report.

Step 1: Requirement Gathering

• What happens: You sit down with the business stakeholders or clients to understand what they need.
• The Goal: Listen to what they want to measure and how they want to slice it.
• Example: The client says, "We need to track our insurance business performance by region across different time periods like weeks, months, and years."

Step 2: Defining the Grain

• What happens: You figure out the lowest, most detailed level of data needed to support the requirements.
• The Goal: Decide what one single row in your data will represent.
• Example: Even though the client asks for weekly and monthly summaries, you determine the true operational grain should be one row per individual policy sale. This ensures you never lose fine-grained details.

Step 3: Designing the Fact Table Schema

• What happens: You design the structure of your database table based on your grain and requirements.
• The Goal: Define the columns for your Fact Table.
• Foreign Keys (Dimensions): Policy_ID, Sale_Date_ID, Region_ID, Agent_ID
• Measures (Metrics): Policy_Count (value of 1 per row), Premium_Amount
• Documentation: You document this grain and table structure in a Data Dictionary or data model diagram so the whole team understands it.

Step 4: Building and Loading the Data (ETL)

• What happens: Data Engineers build ETL (Extract, Transform, Load) pipelines.
• The Goal: Extract raw policy transaction data from source operational systems, clean it up, and load it into your Data Warehouse Fact Table at that precise daily/transactional grain.

Step 5: Connecting the Date Dimension

• What happens: You ensure your fact table's Sale_Date_ID links to a comprehensive Date (Calendar) Dimension table.
• The Goal: Provide a calendar lookup that translates standard dates (2026-07-25) into weeks (Week 30), months (July 2026), and years (2026).

Step 6: Writing the Query or Building the Report

• What happens: Now that the data warehouse is ready, you create the final view for the user.
• The Goal: Aggregate the granular rows into the time periods the client asked for.
• Writing a SQL Query: You write a query that joins your Fact Table to the Date Dimension, filters by the desired region, and groups the results by week (GROUP BY Week_Number).
• Creating a Report: You connect that query or data model to a BI tool (like Power BI or Tableau) to generate an interactive visual dashboard where the user can click a button to view data by week, month, or year.