218 ) Explain a sample data model -for LOANS DATABASE with snowflake and kpi

 Here is e

My answer : 

Exactly why the interviewers pushed back on that specific follow-up response and asked for your methodology:

1. You Didn't Explicitly Define the "Grain"

• The Issue: "Grain" is not a table of columns; it is a clear, concise statement of what one single row in your fact table represents.
• What they wanted to hear: Instead of going straight to drawing columns, you must state the grain in a sentence. For example: "The grain is one row per loan disbursal event."

2. The Attributes Don't Support Your Sample KPI

• The Issue: Your sample KPI is "How many loans are dispersed in week, month". But look at your auto_loans_facts table attributes: loanid, customerid, term, interest%, loan amt.
• Why it fails: There is no Date or Time column listed in your table. Without a date_id or disbursal_date column in the fact table, you cannot group or filter by week or month to calculate that KPI.

3. Mixing Dimensions into the Fact Table

• The Issue: You put term and interest% inside the fact table.
• The Methodology Violation: According to Kimball methodology, a fact table should only contain Foreign Keys (to connect to dimensions) and Numeric Measures (to aggregate). term and interest% are descriptive attributes that belong in your loan dimension table.

---

How to Correctly Present This to an Interviewer

To show them you follow a structured methodology, you should present your sample KPI, Grain, and Fact Table like this:

• Sample KPI: Number of loans dispersed by week/month, and total loan amount disbursed.
• Declared Grain: One row per individual loan transaction.

The Correct Fact Table Attributes:

Instead of your current table, the fact table attributes should be cleanly separated into Keys and Measures:

Column Name	Type	Description
date_key	Foreign Key	Connects to Date Dimension (Needed for your KPI!)
customer_key	Foreign Key	Connects to Customer Dimension
loan_key	Foreign Key	Connects to Loan Dimension
vehicle_key	Foreign Key	Connects to Vehicle Dimension
loan_amt	Fact / Measure	Numeric value (Additive)

Note: To calculate your KPI ("How many loans"), you do a COUNT(loan_key) grouped by the week/month from your date dimension.

------------------------------------------------------------------

CORRECT ANSWER ( FROM GOOGLE ) 

Data Warehouse Architecture Design: Loans Database (Snowflake Schema)

---

➡️ Step 1: Select the Business Process

The business process is the foundational operational event being measured or tracked.

• Process: Loan Disbursal (the precise point in time when a loan agreement is finalized and funds are released).

➡️ Step 2: Declare the Grain

The grain explicitly establishes what exactly a single, individual row inside your central fact table represents.

• Declared Grain: One row per individual loan issued to a customer.

➡️ Step 3: Define Sample KPIs

These are the performance metrics the business layer needs to calculate using this schema.

• KPI 1: Total number (count) of loans disbursed per week and per month.
• KPI 2: Total dollar volume (sum) of loan amounts issued by vehicle brand.
• KPI 3: Average interest rate offered per customer tier (e.g., VIP vs. Standard).
• KPI 4: Total unique clients served and total profit generated by month.

---

➡️ Step 4: List of Dimension Tables & Columns

Here is the clean list of all dimensions, including base tables and their normalized snowflake outriggers, along with their columns:

• 📅 Dim_Date (date_key, full_date, day_of_week, week_number, month_name, calendar_year)
• 👤 Dim_Customer (customer_key, customer_id, first_name, last_name, address_key, customer_type_key)
• 📍 Dim_Customer_Address (address_key, city, state, postal_code)
• 🏷️ Dim_Customer_Type (customer_type_key, type_name)
• 🚗 Dim_Vehicle (vehicle_key, vin_number, vehicle_year, model_key)
• 🚘 Dim_Vehicle_Model (model_key, model_name, brand_key)
• 🏢 Dim_Vehicle_Brand (brand_key, brand_name)
• 📄 Dim_Loan_Details (loan_details_key, loan_id, loan_term_months, interest_rate_percentage, loan_type_key)
• 🗂️ Dim_Loan_Type (loan_type_key, loan_type_name)

---

📊 Step 5: The Fact-Dimensional Snowflake Model Diagram (Schema Layout)

1. Central Fact Table

Fact_Auto_Loans

Column Name	Key Type	Data Type	Description
date_key	Foreign Key (FK)	INT	Links to Dim_Date (Resolves KPI 1 & KPI 4)
customer_key	Foreign Key (FK)	INT	Links to Dim_Customer (Resolves KPI 4)
vehicle_key	Foreign Key (FK)	INT	Links to Dim_Vehicle (Resolves KPI 2)
loan_details_key	Foreign Key (FK)	INT	Links to Dim_Loan_Details (Resolves KPI 3)
loan_amount	Fact / Measure	DECIMAL	Numeric, fully additive principal dollar value
profit_amount	Fact / Measure	DECIMAL	Numeric, fully additive profit dollar value (Resolves KPI 4)

---

2. Snowflake Dimension Tables and Outriggers Breakdown

📅 Time Branch

Dim_Date

• date_key (PK)
• full_date
• day_of_week
• week_number ➡️ (Calculates weekly loan count KPI)
• month_name ➡️ (Calculates monthly loan count, client count, and profit KPIs)
• calendar_year

👤 Customer Hierarchy

Dim_Customer

• customer_key (PK)
• customer_id
• first_name
• last_name
• address_key (FK) ➡️ Links to Dim_Customer_Address
• customer_type_key (FK) ➡️ Links to Dim_Customer_Type

Dim_Customer_Address (Outrigger)

• address_key (PK)
• city
• state
• postal_code

Dim_Customer_Type (Outrigger)

• customer_type_key (PK)
• type_name ➡️ (e.g., Retail, Corporate, VIP)

🚗 Vehicle Asset Hierarchy

Dim_Vehicle

• vehicle_key (PK)
• vin_number
• vehicle_year
• model_key (FK) ➡️ Links to Dim_Vehicle_Model

Dim_Vehicle_Model (Outrigger)

• model_key (PK)
• model_name
• brand_key (FK) ➡️ Links to Dim_Vehicle_Brand

Dim_Vehicle_Brand (Outrigger)

• brand_key (PK)
• brand_name ➡️ (e.g., Honda, Ford - Calculates brand volume KPI)

📄 Loan Rules Hierarchy

Dim_Loan_Details

• loan_details_key (PK)
• loan_id
• loan_term_months
• interest_rate_percentage ➡️ (Calculates average interest rate KPI)
• loan_type_key (FK) ➡️ Links to Dim_Loan_Type

Dim_Loan_Type (Outrigger)

• loan_type_key (PK)
• loan_type_name

---

Let me know if you are ready to explore the SQL query execution to build a comprehensive dashboard for this schema or if you want to look at another process like Loan Repayments!