# Other modelling approaches: Data Vault, one big table and activity schema Optional Alternatives and complements to star schemas: Data Vault for integrating and historising many sources, one big table for simple consumption, and activity schema for modelling behaviour as a single stream of events. > [!NOTE] > > **Before reading** > > [Dimensional modelling]() ยท [History and slowly changing dimensions]() ## Why it matters Star schemas are the most common way to model data for analysis, but not the only one. Platforms with many changing sources often use Data Vault in the middle layer; many teams serve wide tables to BI tools; product analytics often uses event-based models. Recognising these approaches, and where each fits, helps when joining an existing platform or choosing a design for a new layer. ## How it works Data Vault is designed for integrating many sources and keeping full history. It splits data into three kinds of table. - Hubs hold business keys, one row per unique key, such as each order\_id, with when and from where it was first seen. - Links hold relationships between business keys, such as order to customer. - Satellites hold descriptive attributes and their history, attached to a hub or link, with a load timestamp and a record source; each change adds a row. New sources add new satellites without changing the structure of existing tables, and every value keeps its source and load time, which suits audit-heavy environments. The cost is many tables and joins, so Data Vault usually sits in the clean layer, with star schemas or wide tables built on top for consumption. Data Vault 2.0 adds hash keys and standard loading patterns, which make loads parallel and repeatable. One big table (OBT) is the wide table described in [Dimensional modelling](), used as the final layer for one consumer, built from a star schema or Data Vault underneath. Activity schema models behaviour as one narrow table of activities: each row is one entity doing one thing at one time, such as customer c-88 placed an order, viewed a product, contacted support. Columns are fixed: activity ID, timestamp, entity, activity type, a few standard features, and links to details. Questions such as "customers who viewed a product and did not buy within a day" become queries over one table, using the order of activities. It suits customer journeys and product analytics, where questions span many kinds of event. ``` flowchart LR subgraph DV[Data Vault: clean layer] direction TB H1[(hub_order)] --- L1[(link_order_customer)] --- H2[(hub_customer)] S1[(sat_order_details)] --- H1 end DV --> STAR[(Star schema:
fct_order_line,
dim_customer)] STAR --> OBT[(One big table:
obt_order_line)] DV --> ACT[(Activity stream:
customer_activities)] ``` These approaches are layers, not rivals. A typical arrangement keeps raw data as delivered, integrates and historises it in the clean layer (as entity tables or Data Vault), and offers star schemas, wide tables and activity streams in the gold layer for different consumers. ## Key terms - Data Vault A modelling method using hubs, links and satellites for integration and history. - Hub A table of unique business keys. - Link A table of relationships between business keys. - Satellite A table of descriptive attributes and their history, attached to a hub or link. - One big table (OBT) A single wide denormalised table for a specific use. - Activity schema A model with one table of entity activities over time. ## Example The shop's orders in three shapes: - Data Vault: hub\_order (order\_id, first seen, source), hub\_customer, link\_order\_customer, sat\_order\_details (status, amount, with load time and source per change). When the marketplace orders arrive, a new satellite sat\_order\_details\_marketplace is added; existing tables keep their structure and only receive new rows. hub\_order gains the marketplace order keys, with the record source as part of the key if IDs can collide. - One big table: obt\_order\_line, one row per order line with category, brand, customer segment, region and order date as columns, refreshed nightly for the BI tool. - Activity schema: customer\_activities, with rows such as (c-88, 2026-10-28 09:10, viewed\_product, p-332) and (c-88, 2026-10-28 09:14, placed\_order, o-1002), used to measure time from first view to purchase. ## Pitfalls - Data Vault exposed to analysts. Hubs, links and satellites need a consumption layer on top. - Choosing a method by fashion. Pick per layer and per use, based on sources, history needs and consumers. - Activity schemas without clear activity definitions. Ambiguous activity types make every query a debate. ## Learn more - Dan Linstedt and Michael Olschimke, Building a Scalable Data Warehouse with Data Vault 2.0 (Morgan Kaufmann, 2015). - [Activity Schema specification]() ## Related [Multi-source conformance]() [Medallion layers: bronze, silver and gold]() [Semantic layer]() [Naming conventions]()