Data Modeling
Star schema, fact and dimension tables, normalization, denormalization, SCD, grain
1What is a star schema in data modeling?
What is a star schema in data modeling?
Answer
The star schema is a dimensional modeling architecture where a central fact table is surrounded by dimension tables directly connected to it. This structure simplifies analytical queries and optimizes performance by avoiding multiple joins between dimensions. The name comes from the visual shape of the diagram, where the central fact table resembles the body of a star and the dimensions its branches.
2What is the role of a fact table in a dimensional model?
What is the role of a fact table in a dimensional model?
Answer
The fact table stores quantitative measures (revenue, quantity sold, number of clicks) and foreign keys to dimension tables. It represents business events or transactions. Each row corresponds to a measurable event at a defined level of granularity. The numeric columns (measures) are the data that analysts aggregate in their queries.
3What is a dimension table in a dimensional model?
What is a dimension table in a dimensional model?
Answer
A dimension table contains descriptive attributes that qualify the measures in the fact table. For example, a Product dimension contains the name, category, brand, and unit price. Dimensions enable filtering, grouping, and slicing data during analysis. They are typically denormalized to simplify queries and avoid additional joins.
What is the grain (granularity) of a fact table?
What is the difference between an additive measure and a semi-additive measure?
+17 interview questions
Other Data Analytics interview topics
Google Sheets - Fundamentals
Google Sheets - Advanced Formulas
SQL - Fundamentals
SQL - Aggregations and Grouping
SQL - Joins
BigQuery - Fundamentals
Data Cleaning
KPIs and Business Metrics
Descriptive Statistics
Zapier and No-Code Automation
Data Visualization Principles
Python & Pandas - Fundamentals
Google Sheets - Automated Dashboards
SQL - Subqueries and CTEs
SQL - Window Functions
BigQuery - Advanced Features
Funnel and Conversion Analysis
Cohort and Retention Analysis
Google Tag Manager and Tracking
APIs and Webhooks
dbt - Fundamentals
AB Testing and Applied Statistics
Looker Studio (Google Data Studio)
Power BI - Fundamentals
SQL - Advanced Analytical Queries
dbt - Advanced Features
Power BI - DAX and Advanced Dashboards
Python Analytics - Advanced Analysis and ML
Master Data Analytics for your next interview
Access all questions, flashcards, technical tests, code review exercises and interview simulators.
Start for free