# Database Models for Analytics Teams: Full Comparison > Relational, dimensional, document, graph, and key-value database models explained for analytics teams, with when each one fits. Source: https://www.erathos.com/en/blog/database-models-for-analytics-teams Em português: https://www.erathos.com/blog/database-models-for-analytics-teams Published: 2026-09-07 Category: Business Intelligence & Analytics ![Database Models for Analytics Teams](https://cms-media.erathos.com/Database Models for Analytics Teams.png) Most guides to database models ask one question: which model should you pick? For an analytics team, that framing misses how the work goes. You rarely pick the model your sources use. The application teams already picked Postgres, MongoDB, Redis, or Neo4j years ago. You inherit those models. So the real decision splits in two. First, understand the model each source system uses, because it decides what the extracted data looks like. Second, choose the model your analytics layer serves to the business, which usually means relational tables shaped into a dimensional design. This guide covers the five main models with that split in mind. ![Two decisions in the analytics pipeline](https://cms-media.erathos.com/Two decisions in the analytics pipeline.png) ## What is a database model? A database model is the general way a database represents data and its relationships: as tables of rows, as nested documents, as nodes connected by edges, or as values looked up by a key. A schema is one concrete implementation of a model, with specific tables, fields, and keys. The distinction matters because a source system's physical schema is an implementation detail, and it changes. Codd made this the whole point of the relational model in [his 1970 paper](https://db.dobo.sk/wp-content/uploads/2015/11/Codd_1970_A_relational_model.pdf): applications should keep working when the stored representation of data changes. The same idea protects an analytics team. If your dashboards read a source's raw table layout directly, upstream schema changes break them. If they read a serving layer you model on purpose, upstream changes stay contained in your transformation code. ## What are the main database models? The five main database models are relational (tables with rows and keys), dimensional (fact and dimension tables for analytics), document (nested JSON-like records), graph (nodes and relationships), and key-value (values fetched by a unique key). Wide-column and vector models are common adjacent categories. Model Structure Example systems Typical role for an analytics team Relational Tables of rows with primary and foreign keys PostgreSQL, MySQL Common source model and the base of the warehouse Dimensional Fact tables joined to dimension tables Any SQL warehouse (BigQuery, Snowflake, Databricks) The serving model for BI and shared metrics Document Nested field/value documents with arrays MongoDB, DynamoDB Source model; flatten what analytics needs Graph Nodes and typed, directed relationships Neo4j Source model; strong for path and network questions Key-value One value per unique key Redis, DynamoDB Operational lookups and caches; rarely a BI model Wide-column Partitioned tables queried by partition key Cassandra Source model built around known access patterns The categories overlap in practice. [DynamoDB supports both key-value and document models](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html) in the same service, so the table works as a map of shapes rather than a strict product taxonomy. ## How does the relational model work for analytics? The relational model stores data as relations: tables where [each row is a distinct tuple, row order carries no meaning](https://db.dobo.sk/wp-content/uploads/2015/11/Codd_1970_A_relational_model.pdf), and a primary key identifies each row. Foreign keys connect tables, and SQL queries join them. For analytics this is the common ground everything else converges on. Warehouses run SQL. BI tools generate SQL. Transformation frameworks like dbt compile to SQL. Whatever model your sources use, the data becomes relational tables at some point in the pipeline, because joins, filters, and aggregations across sources need shared keys and typed columns. For analytics, arranging those relational tables into a model the business can query matters more than picking a model. ## Why is dimensional modeling usually the analytics serving model? Dimensional modeling splits data into measurements and context. [Facts are the repeated, usually numeric measurements](https://www.kimballgroup.com/2003/01/fact-tables-and-dimension-tables/) of a business process, like order amounts. Dimensions are the context: product, customer, store, date. A fact table holds foreign keys to dimensions plus the measurements. Two rules do most of the work. First, state the grain of a fact table before you pick its columns: one row per order line, or per order, or per daily account snapshot. Second, keep one grain per fact table. A sound design [allows only facts of a uniform grain in a single fact table](https://www.kimballgroup.com/2003/01/fact-tables-and-dimension-tables/). Mixed grains produce sums that look right and are wrong. The payoff is reuse. When revenue is defined once in a fact table at a known grain, every dashboard slices the same number by any dimension. Without that shared layer, each report re-derives revenue from raw tables, and the definitions drift apart. Dimensional modeling is [one of the most widely adopted data modeling techniques for analytics](https://docs.getdbt.com/blog/kimball-dimensional-model). ## Star schema vs. snowflake schema: which should an analytics team use? A star schema joins one fact table directly to flat, denormalized dimension tables. A snowflake schema normalizes those dimensions into more tables, so a product dimension might split into product, category, and brand tables. For BI serving, the star is the better default. The reasoning is about who pays for the joins. Snowflaking pushes joins onto every consumer of the model, while [denormalizing dimensions into a star removes those joins](https://docs.getdbt.com/blog/kimball-dimensional-model) from the consumer's query. Modern warehouses have not reversed this. Star schemas are [typically already optimized schemas for analytics](https://docs.cloud.google.com/bigquery/docs/best-practices-performance-nested) even in BigQuery, the same docs that recommend nested fields. Storage is cheap in a warehouse. The repeated values in a flat dimension cost little compared to the analyst time a snowflaked model burns. ## Kimball vs. Data Vault vs. One Big Table: what changed in the dbt era? Dimensional modeling remains the business-facing default, and the live alternatives are [Data Vault, Third Normal Form, and One Big Table](https://docs.getdbt.com/blog/kimball-dimensional-model). They solve different problems, and the choice depends on which layer you are building. Third Normal Form fits an integration layer where you want redundancy eliminated before shaping marts. Data Vault fits teams tracking history from many changing sources. One Big Table is a consumption pattern with a clear trigger: when your [BI tool has no semantic layer that supports relationships](https://docs.getdbt.com/blog/kimball-dimensional-model), you join the fact table to all its dimensions and publish the wide result. The practical dbt-era pattern is to keep the dimensional model as the source of truth and generate wide tables from it where tools demand them, rather than making One Big Table the model itself. A wide table built from governed facts and dimensions inherits their definitions. A wide table built directly from raw data becomes one more place where metrics drift. ## When are document, graph, key-value, and wide-column models the right source models? These models fit operational systems, where the application controls the access pattern. An analytics team meets them as sources to extract from, and each one shapes the extracted data differently. Document databases store nested records. MongoDB stores documents in BSON, a binary form of JSON, [built from field/value pairs](https://www.mongodb.com/docs/manual/core/document/), where a value can be another document or an array, up to 16 mebibytes per document. That nesting mirrors application objects, which is why product catalogs and user profiles fit well. For analytics it means one source "table" hides several relational tables inside each record. Erathos moves this data with a [MongoDB connector](https://docs.erathos.com/connectors/databases/mongodb) that accepts standard and Atlas connection strings. Graph databases store connections as data. Neo4j's property graph model has [nodes and typed, directed relationships](https://neo4j.com/docs/getting-started/appendix/graphdb-concepts/), both carrying properties. Fraud rings, recommendations, and network questions are natural here because the query follows paths instead of joining tables. When those graphs feed reporting, an [Erathos Neo4j connection](https://docs.erathos.com/connectors/databases/neo4j) moves the data to BigQuery, Databricks, Redshift, or Postgres. Key-value stores do one thing: fetch the value for a unique key. In Redis, [every stored object has its own key](https://redis.io/docs/latest/develop/using-commands/keyspace/), and commands work on the keyspace directly. Sessions, caches, and rate limits fit. Cross-record analytics does not, because there is no join and no scan by attribute worth relying on. Wide-column stores like Cassandra use a [partitioned model where performant queries supply the partition key](https://cassandra.apache.org/doc/latest/cassandra/architecture/overview.html). Cassandra deliberately leaves out [operations that need cross-partition coordination](https://cassandra.apache.org/doc/latest/cassandra/architecture/overview.html), which rules out the arbitrary joins analytics needs. Data modeled this way answers the queries it was designed for and little else, so analytics work happens after export, in the warehouse. ## How do Snowflake, BigQuery, and Databricks change database modeling? Modern warehouses change the physical layer, and they relax how strictly you must flatten data before loading. They do not remove the need to model a serving layer. Snowflake stores every table in [compressed, columnar micro-partitions of 50 MB to 500 MB](https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions) of uncompressed data, and scans only the columns a query touches. It also loads whole JSON values into a VARIANT column, [up to 128 MB uncompressed each](https://docs.snowflake.com/en/user-guide/semistructured-considerations). So a nested document can land as-is, and you can still query it. BigQuery goes further and recommends [nested and repeated fields](https://docs.cloud.google.com/bigquery/docs/best-practices-performance-nested) for hierarchical data that gets queried together, like orders with their line items. In Google's own [documented Stack Overflow example](https://docs.cloud.google.com/bigquery/docs/best-practices-performance-nested), the flat self-join version of a query ran in about 25 seconds over 1.88 GB, while the nested version ran in about 10 seconds over 1.28 GB. That is one documented exercise on one dataset, and the same page still points to star schemas for general analytics. Databricks makes the sequencing explicit with its [medallion architecture](https://docs.databricks.com/aws/en/lakehouse/medallion): bronze holds raw ingested data, silver holds validated and cleaned tables, and gold is where you [model data for reporting using a dimensional model](https://docs.databricks.com/aws/en/lakehouse/medallion). Star and snowflake schemas [translate exceptionally well to Delta tables](https://www.databricks.com/blog/databricks-lakehouse-data-modeling-myths-truths-and-best-practices). The lakehouse changed the storage engine, and kept the modeling advice. _Databricks' own layer-by-layer breakdown of the medallion architecture_ ![Databricks' own layer-by-layer breakdown of the medallion architecture](https://cms-media.erathos.com/Databricks' own layer-by-layer breakdown of the medallion architecture.png) ## How should you load document and key-value source data into a warehouse? Land it raw first, then flatten the fields analytics needs. While you are unsure what you will do with semi-structured data, [keep it in a VARIANT column](https://docs.snowflake.com/en/user-guide/semistructured-considerations). Then flatten the fields that hold dates and timestamps, numbers inside strings, or arrays into typed relational columns, because typed columns filter and compress better than JSON text. ![Nested document to star schema](https://cms-media.erathos.com/Nested document to star schema.png) The flattening itself follows the shape of the documents. Nested objects become [parent tables and sub-tables](https://www.fivetran.com/blog/database-schema-design). Here is the whole pattern in DuckDB, small enough to read. The source is a file of order documents, each with a nested customer object and an items array: `{"_id": "ORD-1001", "order_ts": "2026-08-28T09:15:00Z",` ` "customer": {"customer_id": "CUST-001", "name": "Alice Nguyen", "city": "Seattle"},` ` "items": [{"sku": "COF-001", "product_name": "Single-Origin Coffee", "quantity": 2, "unit_price": 18.50},` ` {"sku": "MUG-101", "product_name": "Ceramic Travel Mug", "quantity": 1, "unit_price": 14.00}]}` Load it raw, keeping the nested structure: `CREATE OR REPLACE TABLE raw_orders AS` `SELECT *` `FROM read_json_auto('orders.json');` The nested customer object becomes a customer dimension: `CREATE OR REPLACE TABLE dim_customers AS` `SELECT DISTINCT` ` customer.customer_id AS customer_id,` ` customer.name AS name,` ` customer.city AS city` `FROM raw_orders;` The items array unnests into a fact table at the order-item grain: `CREATE OR REPLACE TABLE fct_order_items AS` `SELECT` ` _id AS order_id,` ` order_ts,` ` customer.customer_id AS customer_id,` ` item.sku AS sku,` ` item.quantity AS quantity,` ` item.unit_price AS unit_price,` ` item.quantity * item.unit_price AS revenue` `FROM raw_orders,` `UNNEST(items) AS t(item);` Now a plain star-schema query answers a business question the document model could not answer without application code: `SELECT` ` c.city,` ` SUM(f.revenue) AS total_revenue` `FROM fct_order_items AS f` `JOIN dim_customers AS c USING (customer_id)` `GROUP BY c.city` `ORDER BY total_revenue DESC;` Running this against six sample orders returns: `┌──────────┬───────────────┐` `│ city │ total_revenue │` `│ varchar │ double │` `├──────────┼───────────────┤` `│ Austin │ 221.49 │` `│ Seattle │ 117.0 │` `│ Chicago │ 101.25 │` `│ Portland │ 97.0 │` `└──────────┴───────────────┘` The three-step shape (raw, dimension, fact) stays the same on a bigger extract. Only the flattening SQL grows. ## What does a practical ELT pattern look like from source model to analytics model? The pattern is extract-load-transform: move source data into the warehouse mostly as-is, then do the modeling in SQL where all sources share one engine. We compared this with transform-first pipelines in our [ETL vs. ELT guide](https://www.erathos.com/en/blog/etl-vs-elt-key-differences?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=database-models-for-analytics-teams); the short version is that transforming after loading keeps raw data available when the model changes. Extraction is the part that decides how trustworthy the layers above are. Erathos [extracts from a configured source, stages the data in a temporary cloud bucket, and loads it with COPY or the destination's equivalent](https://docs.erathos.com/platform/how-we-move-data), then deletes the temporary files. The [sync type](https://docs.erathos.com/platform/connections/sync-types) sets what the raw layer can express: a Full Refresh overwrites the destination table with the current source state, while Partial modes use a date or datetime cursor, and the Versioned variant appends rows and marks the latest version, so history survives in raw. Cursor columns miss deletes and any update that does not touch the cursor. For sources where that matters, change data capture reads the database's own change log instead. We cover the tradeoff in [cursor-based sync vs. CDC](https://www.erathos.com/en/blog/cursor-based-sync-vs-change-data-capture?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=database-models-for-analytics-teams). Erathos supports log-based CDC for [PostgreSQL](https://docs.erathos.com/connectors/databases/postgresql) through the write-ahead log and for [MySQL](https://docs.erathos.com/connectors/databases/mysql) through row-format binary logs. From there the medallion responsibilities apply whatever tools you run: validate and type in silver, including the document-flattening from the previous section, and build the dimensional model in gold. If the destination is Databricks or BigQuery, the Erathos [Databricks destination](https://docs.erathos.com/destinations/what-is-a-destination/databricks) works with any instance on AWS, Azure, or Google Cloud, and the [BigQuery destination](https://docs.erathos.com/destinations/what-is-a-destination/bigquery) needs a service account with four data and job roles. ## Which database model should your analytics team choose? For the serving layer, choose relational tables in a dimensional design: facts at a stated grain, flat dimensions in a star. For everything upstream, you match the model to the workload instead of choosing one winner. Situation Model that fits Shared BI metrics, slicing and filtering across sources Dimensional star schema in the warehouse Hierarchical data queried together in BigQuery Nested and repeated fields Semi-structured data with unknown future use Raw VARIANT (or equivalent), flatten later Path and network questions (fraud rings, recommendations) Graph, as an operational system Single-record lookups, sessions, caches Key-value Known partition-key access at operational scale Wide-column The questions that settle it are concrete. What queries will consumers run, and at what grain? How will you capture changes from each source without losing deletes? Who owns the metric definitions? Answer those, and the model choice for each layer gets easier: sources stay whatever they are, raw layers preserve them, and the business reads a dimensional model built on purpose. If you want to put this into practice, [try Erathos free for 14 days](https://app.erathos.com/signup?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=database-models-for-analytics-teams) and start moving your sources into the warehouse.