The process of creating a visual representation of data structures and relationships, defining how data is stored, organized, and accessed in databases and warehouses.
Data modeling is the unglamorous, consequential discipline that decides whether your warehouse is a pleasure to work with in three years or a swamp nobody understands. Everyone focuses on the tool (dbt, Snowflake, BigQuery) and skips the modeling; a year later they have 2,400 models with no consistent naming convention, no grain discipline, no SCD strategy, and every analyst writes subtly different versions of "revenue."
Kimball (dimensional) — star schemas, fact and dimension tables, denormalized dimensions. Intuitive for analysts, fast for BI, well-understood. Works for 80% of analytics workloads.
Data Vault 2.0 — hubs/links/satellites, auditability and history preservation as first-class concerns. Shines for regulated industries (banking, insurance) with heavy audit requirements and complex source-system integration. Costs more to build; pays off at scale with auditability.
One Big Table (OBT) / wide denormalized — pre-joined, analyst-friendly mart. Works well on Snowflake/BigQuery where storage is cheap and columnar pruning handles wide tables. Simpler for analysts but harder to evolve and test.
A mid-sized SaaS I worked with had grown their dbt project to 1,800 models over three years with no model layering — staging, intermediate, and marts all mixed together, no consistent primary key naming, no documented grain per model. New engineers took six weeks to ramp; a typical analyst question required reading 8-10 models to answer. We did a six-week refactor: adopted Kimball as the mart layer, enforced staging/intermediate/marts layering, renamed primary keys consistently, added {{ config(contract={enforced: true}) }} on marts, and documented grain for every fact table. Ramp time dropped to one week; the senior team stopped being the bottleneck on every analyst question. The lesson: modeling discipline compounds. Invest early, or pay interest forever.
Data modeling is the process of creating a visual representation of how data is structured, stored, and accessed in a system. It defines entities, attributes, relationships, and constraints—serving as the blueprint for databases and data warehouses.
Data modeling pitfalls I see repeatedly:
id, some order_id, some pk_order. Pick a convention (I like _id _sk unique, not_null, relationships, accepted_values tests catch 80% of modeling bugs. Teams that skip tests discover modeling bugs in production dashboards.Data modeling is the design step of deciding how data will be organized in a database or warehouse — what entities exist (customers, orders, products), what attributes they have (name, status, price), how they relate to each other (customers place orders, orders contain products), and what rules enforce consistency (primary keys, foreign keys, uniqueness). Good modeling produces a schema that is fast to query, intuitive for analysts, and resilient to change. Bad modeling produces a swamp of models nobody understands.
Three levels of abstraction: (1) Conceptual — high-level business entities and relationships without technical detail, usually diagrams for stakeholder alignment; (2) Logical — entities + attributes + keys + normalization, platform-agnostic; (3) Physical — actual DDL with data types, indexes, partitioning, and clustering for a specific platform. Separately, there are methodologies: Kimball dimensional (fact/dimension), Data Vault 2.0 (hub/link/satellite), Inmon 3NF, Anchor Modeling, One Big Table. The levels describe *what* you're modeling; the methodologies describe *how*.
A star schema is a Kimball dimensional design with a central fact table (one row per business event — a sale, a click, a payment) surrounded by dimension tables (descriptive context — customer, product, date, store). Foreign keys in the fact connect to primary keys in dimensions. It's called "star" because diagrams show the fact in the center with dimensions radiating out like points. Star schemas are the default for BI workloads because queries need few joins, grain is clear, and analysts can self-serve without understanding complex normalization.
For transformation modeling (the actual SQL/logic that shapes your models): dbt is the dominant choice in modern cloud warehouses. For schema visualization: dbdiagram.io, SqlDBM, Lucidchart, draw.io, Erwin (enterprise). For semantic modeling: dbt's semantic layer, Looker LookML, Cube.dev, Snowflake semantic views, MetricFlow. For Data Vault automation: Vaultspeed, WhereScape, AutomateDV (dbt package). Most modern teams center on dbt + a diagramming tool and skip heavyweight enterprise modeling products.
Kimball (dimensional) optimizes for analyst usability and query performance — denormalized star schemas where each dimension holds flat, readable attributes. Data Vault 2.0 optimizes for source-system integration, auditability, and flexibility — hubs (business keys), links (relationships), satellites (context with history). Kimball is simpler and faster to build; Data Vault is more resilient to source changes and better for regulated industries needing full audit history. Most teams use Kimball; some regulated enterprises use Data Vault for raw/integration layer with Kimball marts on top.
Yes — dimensional modeling remains the default for analytics mart layers, and its patterns (fact/dimension, grain discipline, SCD) are foundational knowledge for any data engineer. What has changed is the surrounding stack: dbt replaced hand-written ETL, cloud columnar warehouses (Snowflake, BigQuery, Databricks) handle wide denormalized tables efficiently, and the OBT pattern competes with traditional star schemas for some use cases. But the *thinking* of dimensional modeling — declaring grain, separating facts from context, handling SCD — is as relevant as ever.
Six I see repeatedly: (1) no documented grain per fact table, leading to silent fanout bugs; (2) inconsistent PK/FK naming across models; (3) treating SCD as an afterthought and retrofitting later (painful); (4) skipping the staging/intermediate/marts layering, ending up with circular dependencies; (5) no unit tests (dbt `unique`, `not_null`, `relationships`) so modeling bugs ship to prod; (6) not involving business stakeholders, producing technically-correct models nobody actually uses. All six are cultural/discipline issues, not technical ones — the tools support good practice; teams skip it anyway.