Data Modeling & Warehouse Architectures

Question 1

What is the primary goal of data modeling in analytics and warehousing?

Answer 1

To structure data so that it is easy, efficient, and unambiguous to query for reporting, analysis, and downstream applications.

Question 2

How does data modeling in analytics differ from OLTP modeling?

Answer 2

Analytics modeling prioritizes read performance and simplicity for aggregations and slicing, while OLTP modeling prioritizes write performance and transaction integrity.

Question 3

What is a star schema?

Answer 3

A schema with a central fact table joined to multiple dimension tables, forming a star-like pattern.

Question 4

What is a snowflake schema?

Answer 4

A star schema variant where dimensions are further normalized into additional related tables, creating more joins and a snowflake-like structure.

Question 5

Why are star schemas popular in data warehouses?

Answer 5

They provide a clear separation between facts and dimensions, simplify queries, and support efficient aggregations and BI tools.

Question 6

What is a fact table?

Answer 6

A table that stores business events or measurements, typically with foreign keys to dimensions and numeric metrics to aggregate.

Question 7

What is a dimension table?

Answer 7

A table that stores descriptive attributes about business entities such as customers, products, or dates.

Question 8

What does the grain of a fact table mean?

Answer 8

The level of detail represented by each row, such as one row per order line, per daily metric per customer, or per event.

Question 9

Why must the grain of a fact table be defined explicitly?

Answer 9

To avoid ambiguity, ensure measures are consistent, and design dimensions and keys that align with that level of detail.

Question 10

What is a transactional fact table?

Answer 10

A fact table that records individual business transactions or events, such as orders or clicks.

Question 11

What is a snapshot fact table?

Answer 11

A fact table that captures the state of a process or entity at a regular point in time, such as daily account balances.

Question 12

What is an accumulating snapshot fact table?

Answer 12

A fact table that tracks the lifecycle of a process with multiple milestones, updating the row as the process advances.

Question 13

What are conformed dimensions?

Answer 13

Dimensions that are shared consistently across multiple fact tables or data marts, using the same keys and semantics.

Question 14

Why are conformed dimensions important?

Answer 14

They enable consistent reporting and cross-fact analysis across different subject areas, such as sales and marketing.

Question 15

What is a junk dimension?

Answer 15

A dimension that groups low-cardinality flags and attributes that do not naturally belong in other dimensions to avoid cluttering fact tables.

Question 16

What is a role-playing dimension?

Answer 16

A single physical dimension table used in multiple roles in a fact table, such as a Date dimension serving order_date, ship_date, and invoice_date.

Question 17

What is a degenerate dimension?

Answer 17

A dimension attribute, such as an order number, stored directly in the fact table without a separate dimension table.

Question 18

What is a slowly changing dimension (SCD)?

Answer 18

A dimension table where attribute values change over time and must be tracked with specific patterns.

Question 19

What is SCD Type 1?

Answer 19

A pattern that overwrites old attribute values with new ones, not preserving history.

Question 20

What is SCD Type 2?

Answer 20

A pattern that preserves history by adding new rows with new surrogate keys and effective dates or versioning.

Question 21

When is SCD Type 1 appropriate?

Answer 21

When only the current value matters and historical attribute values are not needed for analysis.

Question 22

When is SCD Type 2 appropriate?

Answer 22

When historical analysis requires knowing what attribute values were at the time of past events.

Question 23

What is a surrogate key in dimensional modeling?

Answer 23

An artificial key, typically an integer, that uniquely identifies dimension rows and is used as the primary key.

Question 24

Why are surrogate keys preferred over natural business keys in dimensions?

Answer 24

They are stable, independent of source systems, and support SCD Type 2 and other modeling needs cleanly.

Question 25

What is a bridge table?

Answer 25

A table used to resolve many-to-many relationships between facts and dimensions, often for multi-valued attributes.

Question 26

What is a data mart?

Answer 26

A subject-oriented subset of the data warehouse tailored to specific analytical areas such as finance or marketing.

Question 27

What is the Kimball approach to data warehousing?

Answer 27

A bottom-up methodology that emphasizes dimensional modeling, conformed dimensions, and subject-area data marts feeding a bus architecture.

Question 28

What is the Inmon approach to data warehousing?

Answer 28

A top-down methodology focusing on a centralized, normalized enterprise data warehouse from which data marts are derived.

Question 29

What is a data vault model?

Answer 29

A modeling technique that separates hubs (business keys), links (relationships), and satellites (descriptive attributes) for flexible, audit-friendly warehouses.

Question 30

When might a data vault be preferred?

Answer 30

When auditability, traceability, and flexibility in integrating many source systems are more important than simple BI models.

Question 31

What is a common pattern for integrating data vault with dimensional models?

Answer 31

Use data vault as the raw, auditable storage layer and build dimensional star schemas on top for reporting and BI.

Question 32

What is the medallion (bronze/silver/gold) architecture?

Answer 32

A layered approach where bronze holds raw data, silver holds cleaned and standardized data, and gold holds curated, business-ready tables.

Question 33

How does the medallion architecture relate to modeling?

Answer 33

Bronze layers are minimally modeled, silver layers capture core entities and relationships, and gold layers implement dimensional or application-specific models.

Question 34

What is a semantic layer in analytics?

Answer 34

An abstraction that defines business metrics, dimensions, and relationships in business terms, independent of physical storage details.

Question 35

Why is a semantic layer useful?

Answer 35

It provides consistent definitions for metrics and dimensions across tools and teams, reducing ambiguity and duplication.

Question 36

What is a factless fact table?

Answer 36

A fact table without numeric measures, used to record the occurrence of events or coverage relationships for counting and existence checks.

Question 37

What is a grain mismatch problem between fact tables?

Answer 37

A situation where fact tables have different levels of detail that do not align, complicating joins and aggregations across them.

Question 38

How can grain mismatches be mitigated?

Answer 38

By designing fact tables with clear, compatible grains, or by creating derived aggregate tables to align grains for common analyses.

Question 39

What is an aggregate table in a warehouse?

Answer 39

A pre-aggregated table at a coarser grain than the base fact, used to speed up common queries.

Question 40

Why must aggregate tables be kept consistent with base facts?

Answer 40

Inconsistencies lead to conflicting results, so pipelines and refresh logic must keep aggregates in sync.

Question 41

What is referential integrity in a dimensional model?

Answer 41

The property that every foreign key in a fact table matches a valid primary key in a dimension table, often enforced logically or via checks.

Question 42

Why is referential integrity important for analytics?

Answer 42

Broken references cause orphaned facts, missing dimension attributes, and misleading aggregates.

Question 43

What is a data model diagram used for?

Answer 43

To visualize tables, keys, relationships, and cardinalities, helping stakeholders understand how data is structured and connected.

Question 44

Why should data models be versioned and documented?

Answer 44

Models evolve over time; documentation and versioning help teams understand changes, avoid breaking consumers, and comply with governance.

Question 45

What is a good one-sentence mental model for warehouse data modeling?

Answer 45

Choose a clear grain for each fact, attach well-designed conformed dimensions, and structure schemas so business questions can be answered with simple, performant queries.