8:06
Learn Data Modeling in 8 minutes: Dimensional Data Modeling, Data Vault, and One Big Table
Data with Zach
Overview
This video explains the three main levels of data modeling: conceptual, logical, and physical. Conceptual modeling defines what data is needed and its sources at a high level, focusing on business value. Logical modeling then structures this data into facts (events) and dimensions (context), detailing their relationships. Finally, physical modeling addresses the technical implementation, including schemas, data types, and storage. The video also introduces three common physical data modeling techniques: One Big Table (OBT), Dimensional Data Modeling, and Data Vault, highlighting their trade-offs and use cases, and emphasizes that these techniques can be combined to meet specific business needs.
How was this?
Save this permanently with flashcards, quizzes, and AI chat
Chapters
- Data modeling is crucial for data engineers as it structures data for business decision-making.
- Conceptual modeling is the highest level, focusing on identifying necessary data, sources, and high-level relationships, while considering feasibility and ROI.
- Logical modeling is the next level, defining entities as facts (events) and dimensions (context) and their interrelationships.
- Physical modeling is the most granular level, dealing with actual database schemas, data types, storage, and compression.
Understanding these distinct levels ensures that data models align with business needs from the outset and are technically feasible, preventing wasted effort on irrelevant or unattainable data.
During conceptual modeling, a data engineer might push back on a data scientist's request for a rarely used column, saving significant development time and resources.
- Facts represent events or actions that occur, such as a user login or a product purchase, and are immutable once recorded.
- Dimensions provide context to facts, representing the 'who,' 'what,' 'where,' and 'when' related to an event, like user details, device, or browser.
- The goal of logical modeling is to identify all relevant entities and define how they connect to enrich fact data.
This level of detail is essential for creating rich, queryable datasets that can answer complex business questions by providing the necessary context around events.
When a user clicks an ad on Facebook, the fact is the 'click,' and the dimensions include the 'user,' the 'device' used, and the 'browser.'
- One Big Table (OBT) consolidates all fact and dimension data into a single table to avoid costly joins, trading storage for faster aggregations.
- Dimensional Data Modeling, a traditional approach, separates facts and dimensions into distinct tables that are then joined, serving as a foundational technique.
- Data Vault focuses on preserving the raw, untransformed state of data, allowing for easy rollback and a clear audit trail, often associated with ELT processes.
Choosing the right physical modeling technique impacts performance, storage, and the ease of data analysis, with each having specific advantages and disadvantages.
OBT duplicates user data across hundreds of events, making aggregations faster but increasing storage needs.
- Data modeling is more of an art than a strict science, allowing for flexibility and combination of techniques.
- The choice of technique should align with specific business requirements and available tools.
- Complex data types like arrays and structs are often indicators of a One Big Table approach.
Recognizing that no single technique is universally superior empowers data engineers to create hybrid solutions that best meet the unique demands of their organization.
At Airbnb, a hybrid model was used: Data Vault captured raw availability and price rules (inputs), which were then processed into a One Big Table for listing prices, using complex data types to represent nightly rates.
Key takeaways
- Data modeling is foundational for effective data utilization and decision-making in businesses.
- Conceptual, logical, and physical modeling represent distinct but interconnected layers of data structuring.
- Facts represent immutable events, while dimensions provide the contextual details for those events.
- One Big Table prioritizes query speed over storage efficiency by denormalizing data.
- Dimensional modeling is a widely adopted, foundational technique separating facts and dimensions.
- Data Vault emphasizes data lineage and raw data preservation, supporting auditability.
- Effective data modeling often involves a pragmatic blend of different techniques tailored to specific needs.
Key terms
Data ModelingConceptual Data ModelingLogical Data ModelingPhysical Data ModelingFactsDimensionsOne Big Table (OBT)Dimensional Data ModelingData VaultELT (Extract, Load, Transform)
Test your understanding
- What is the primary purpose of conceptual data modeling, and why is it considered the highest level?
- How do facts and dimensions differ in logical data modeling, and what role does each play?
- What is the core trade-off inherent in the One Big Table (OBT) physical modeling technique?
- Why is Dimensional Data Modeling considered a foundational technique in data engineering?
- How does the Data Vault approach differ from Dimensional Modeling in its handling of raw data?