Data Modeling

Most data projects fail not due to poor coding, but due to the fact that someone created the wrong structure prior to writing a single line of code, and by the time this became obvious, fixing it meant rewriting half the project.

And this is precisely the problem that data modeling solves. Yet, every article on the subject allocates 90% of its pages to definitions and very little (if any) to the practical question that people want answered: which technique should I use, and when?

This guide will provide you with all the definitions that you need in no time, and a framework that data teams usually learn through costly trial-and-error experience.

What Is Data Modeling?

Data modeling refers to the process of designing how data will be structured, interconnected, and managed prior to developing the system itself.

The data model is the end product, which is a map of your data, connections, and the way it should be controlled. Imagine it as an architectural blueprint for your building. The blueprint does not construct the building. However, without it, each construction worker will assume something different, and the walls will not match.

Two things data modeling is not:

• It is not the same thing as database design. While data modeling tells us what to build, database design tells us how to build it within the system.
• It is not a once-off task. An unmaintained model will only serve as a blueprint of a non-existent system.

Why Data Modeling Matters in 2026

Here’s the problem: AI and real-time analytics have made data modeling even more important, not less.

If your warehouse was used to generate monthly reports, a bad schema was merely an annoyance. If your AI models learn from the data, or your dashboards are querying it in real time, a bad schema is exacerbated. All downstream systems are affected by it.

There are three job roles involved in data modeling at most companies:

• The data architect designs the entire schema and architecture
• The data engineer builds and maintains the pipelines that train machine learning models
• The analytics engineer, a relatively new position five years ago, creates SQL transformations using platforms like dbt, and models data directly in the warehouse

Most posts about data modeling are aimed at data architects alone. That’s not right anymore. By 2026, the analytics engineer will be doing more data modeling than almost any other person, and ignoring this change means most guides explain a process that is at least three years outdated.

The 3 Types of Data Models

Data models exist at three levels of abstraction. Skipping levels, jumping from a whiteboard sketch straight to a physical database, is one of the most reliable ways to guarantee expensive rework.

Conceptual Data Model

A conceptual data model is an abstract view of the existence and association of data. No technical information. No SQL. No database system.

It addresses business requirements like: Who are our customers? What do they purchase? How does the order relate to products? The conceptual data model records those associations in natural language among the business and technical parties before any design considerations are taken into account.

For instance, an e-commerce organization models three entities as Customer, Order, and Product. It states that “one customer makes many orders” and “one order consists of many products.” This is a full conceptual data model without mentioning any tables.

Target audience: Business parties, product owners, project managers.

Logical Data Model

The logical model brings more structure to the conceptual model without committing to any particular database technology.

This is where you define tables, columns, data types, primary keys, and foreign keys. You follow the rules of normalization. You define the relationships between tables using a precise notation like crow’s foot or IDEF1X.

Target audience: data architects, senior analysts.

One true compromise: agile development teams tend to bypass this step altogether, jumping directly from the conceptual model to the physical design. This can be okay for smaller and well-understood projects, but it usually comes back to bite you in more complex enterprise data scenarios.

Physical Data Model

The physical model is the blueprint that is used to build a particular database system.

All the technical details are found here, including table names, column names, data types, indexes, partition strategy, storage configuration. It is exactly what database administrators use to build their database systems.

By 2026, it became necessary to take into account the storage layer when creating physical models. The physical model created for PostgreSQL will be different from the one developed for Delta Lake or Apache Iceberg, which are used in modern lakehouses architecture. It is incorrect to consider physical modeling as platform-independent.

	Conceptual	Logical	Physical
Purpose	Define what data exists	Structure data relationships	Build the actual database
Audience	Business stakeholders	Data architects	DBAs, developers
Detail level	High-level, no tech	Detailed, no platform	Platform-specific
Includes SQL?	No	No	Yes
Example tool	Lucidchart, draw.io	erwin, ER/Studio	PostgreSQL, Databricks SQL

6 Data Modeling Approaches — and When to Use Each

Every guide lists these approaches. None of them tells you when to choose one. That’s the actual decision. Each section below starts with a one-line rule, which is the part that matters.

Hierarchical Model

Use this when: the data is strictly hierarchical, and each record will never have more than one parent.

The data is arranged in a hierarchy: one root node with branches of children and grandchildren. This method of organization was developed by IBM in their IMS system, first used in 1966, and was also the basis for early banking systems.

When it still applies: systems using XML, file directories, certain health care record formats like HL7 v2. When it no longer applies: any time a record would need more than one parent. An item belonging in two different categories is no longer hierarchical. Don’t use this on anything that isn’t really a tree.

Relational Model

Use this when: Your data is organized, the relationship between entities is clear, and you require ACID properties.

The E.F. Codd model was introduced in an IBM research paper in 1970. All major databases such as Oracle, SQL Server, MySQL, PostgreSQL, etc., have been built based on this concept. Data is stored in tables. Rows represent records while columns stand for attributes. Relationships are handled using foreign keys.

Ideal for: Transactional systems, banking, e-commerce, CRM, ERP, and any other application where data consistency is critical.

Entity-Relationship (ER) Model

Use this when: you are developing a system that requires the communication of the data structure prior to selecting the appropriate database.

The ER model is a design tool rather than a database type. The ER model uses diagrams, referred to as ERDs, which represent entities and their attributes as well as the relationship between entities. The cardinality is represented graphically in a manner that a project sponsor can evaluate and approve.

Object-Oriented Model

Use This When: you have an object-oriented application with data that reflects rich entities with behavior and which don’t fit well into flat tables.

The data is stored in objects, which reflect how the data is represented in Java, Python, and C#. These objects have both attributes and methods. Inheritance between classes exists. It is used for multimedia database applications, computer-aided design (CAD) systems, and other scientific applications that deal with complex hierarchical data.

Dimensional Model (Star and Snowflake Schema)

Use this when: You’re designing a data warehouse to support analysis and reporting, and query performance is more important than space optimization.

This paradigm was introduced by Ralph Kimball in the 1990s. Its premise is simple: distinguish between facts and dimensions. Facts are the events that can be measured: sales, clicks, transactions. Dimensions are the surrounding context: the customer, the product, the date, the location.

Star schema: one fact table in the center, surrounded by dimension tables with some denormalization. Querying is fast; there is some redundancy.

Snowflake schema: Dimension tables are even more normalized, with sub-tables. There is less redundancy; more complex queries are needed.

One thing almost every book on data modeling glosses over: slowly changing dimensions. What do you do when a customer changes address? Overwrite the previous information? Keep both versions? Or track changes with date ranges? Kimball described no fewer than six ways to handle SCDs. And you’ll need a strategy before your first table goes into production.

NoSQL and Document Models

Apply it in cases where your data is unstructured or semi-structured, flexibility of schema is important, or joins at your scale create a performance problem.

The NoSQL family includes four types of databases:

• Document-oriented databases (MongoDB, Firestore): data stored as document structures similar to JSON; useful for user profiles, product catalogs, content management
• Key-value databases (DynamoDB, Redis): extremely fast access using only one key; typically used for caching, sessions management, and shopping cart operations
• Column-family databases (Cassandra, HBase): designed for high-write workloads; used in Internet of Things data storage, time-series logging, event logging
• Graph databases (Neo4j): data represented through nodes and edges; designed for connected data – social networks, recommendation engines, fraud detection

Most enterprise-level data modeling guides issued by leading vendors ignore NoSQL entirely. By 2026, this will be a serious oversight, since a significant portion of production systems include at least one non-relational database in conjunction with a relational database.

Use Case	Best Approach	Avoid
Transactional system (banking, e-commerce, CRM)	Relational	Hierarchical, document store
Analytics / data warehouse	Dimensional (star schema)	Fully normalized relational
Content management, user profiles	Document store	Relational (too rigid)
Caching, session data	Key-value	Dimensional
Social graph, recommendations, fraud	Graph	Relational (join-heavy at scale)
High-write IoT / time-series logs	Column-family	Star schema
Communicating structure to stakeholders	ER model (design stage)	Physical model (too much detail)

Key Components of a Data Model

All data models consist of the same six components, irrespective of the modeling methodology used.

Entities: The entities are the objects that the business keeps track of, such as customers, products, and orders. Each entity in a relational database is represented by a table.

Attributes: Each entity is described by its attributes. For example, a Customer entity has attributes like Name, Email, and Date of Registration. An Order entity has attributes like 

Order ID, Order Date, and Order Value. The attributes are represented as columns.

Relationship: The relationships between entities are defined as follows:

• One-to-One: One person has one passport.
• One-to-Many: One customer has multiple orders; this is the most common relationship in business data.
• Many-to-Many: Multiple students take multiple courses, with each student taking multiple courses and each course having multiple students; such relationships require a junction table in a relational database system.

Constraints ensure data purity:

• Primary Key: uniquely defines each record; no duplicates, no nulls
• Foreign Key: connects a record in one table to a record in another; ensures referential integrity
• Unique: prevents duplicate values in a field
• Not Null: mandates a value; no incomplete records should get into the database
• Check: enforces a rule, such as a price being positive

Metadata is data about your data; it includes data definitions, lineage, ownership, refresh cycles, and other governance policies. Metadata is always ignored until the first audit and paid for with every compliance assessment and new analyst training.

Normalization helps to organize data to avoid redundancies, where each piece of information is stored once. First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) are the main normal forms.

This is the compromise worth considering: normalization saves space and avoids update anomalies; however, for analytics queries joining many tables, too much normalization will ruin performance. It is precisely because of this reason that dimensional modeling introduces redundancy deliberately. Star schema allows redundancy in order to gain fast querying speed. Using 3NF normalization in an analytics database is a bad choice, just like using dimensional modeling in a transactional database.Normalization degree is a choice to be made, not a golden rule to be blindly followed.

The Data Modeling Process: Step by Step

Step 1: Define Business Requirements First

Each and every guideline begins with the phrase “Identify entities.” Incorrect initial step.

Begin with what your organization needs to know and do. Who will consume this data? What questions must it answer? What decisions will it make? Only then will you be able to determine the entities and attributes. 

This means speaking to product managers, finance directors, and operations personnel before even launching a modeler application. This means documenting the business rules in clear, concise language. “An order can be placed only by a verified customer” is a business rule.

Step 2: Identify Entities and Attributes

Now identify what things your business tracks (entities) and what you know about each (attributes). Be specific. “Customer data” is not an entity. “Customer” is, with attributes like customer_id, email, registration_date, and subscription_tier. Keep entities discrete; each should represent one clear concept.

Step 3: Map Relationships and Cardinality

Record the manner by which they relate and determine their cardinality: one-to-one, one-to-many, or many-to-many. The many-to-many relationship requires a junction table within the relational world. Design for this right now. It will be far more costly to discover these relationships after your physical design is already in place.

Step 4: Choose Your Approach

Refer to the decision table from the preceding section. The correct choice will depend on your particular case, your organization’s capabilities, and whether this system will need to support transactions, analytics, or both. Organizations that take one approach for all cases will sooner or later end up creating a data warehouse that their analysts despise, or a transactional system they cannot trust.

Step 5: Normalize (or Denormalize) Deliberately

Normalize to 3NF for transactional environments. Denormalize on purpose, not by accident, for analytical purposes. Accidental denormalization leads to integrity issues that are difficult to locate and costly to solve. Deliberate denormalization, when properly documented, results in quick analytical queries. The term “deliberately” has meaning in this sentence.

Step 6: Build, Validate, and Maintain

Translate the logical design into a physical design and test it out using realistic queries on realistic data. Debug until it works.

And then treat the model as a living document. Business needs evolve. More sources of data emerge. Regulatory requirements are updated. If the model doesn’t reflect the reality of the system anymore, it’s not documentation – it’s a pitfall waiting to happen.

Data Modeling for the Modern Data Stack

I believe that most data modeling guidelines do not appreciate the extent to which dbt revolutionized this discipline. This is now the main data modeling layer used by analytics engineering teams. In the past, analysts would design models in a diagramming tool, while the implementation was done by the DBA. Now, analysts can create transformations using SQL SELECT statements, while dbt compiles them into tables or views in the warehouse. Models are committed to Git. Tests are automated. Documentation comes out of the same repository. All of this made it possible for a team to embrace the concept of data modeling without having one previously.

Medallion architecture (Bronze, Silver, Gold) is the standard way of organizing data in a lakehouse. Raw data is loaded in Bronze without any changes. Validated data is stored in Silver. Aggregated data ready for business use is placed in Gold. Each layer is associated with its own data model, implemented using Delta Lake or Apache Iceberg tables.

Data Mesh The concept of data mesh decentralizes data ownership to the domain teams responsible for generating data. Domain teams create, manage, and publish their data as products. However, data mesh requires balancing autonomy and consistency. Data mesh is viable when there is governance capability within the organization despite the decentralized nature of ownership. Lack of such governance leads to chaos and additional layers.

Feature stores bridge data modeling and machine learning. In essence, a feature store refers to a central storehouse of the engineered features. Engineering features refers to the creation of derived variables used by machine learning models to generate predictions. Feature modeling differs from warehouse modeling in that it involves designing for point-in-time accuracy rather than aggregation. The mistake in design will result in the inclusion of future data in training the model… all seems well in testing but fails in production.

Common Data Modeling Mistakes

1. Moving directly to the physical model. Conceptual and logical modeling is skipped, since they seem tedious and abstract. And the physical schema is based on a misunderstanding of the business requirement. Modifying a physical schema that already contains data involves migrations, downtimes, and rewriting all queries that rely on it.
2. Over-normalizing a database used for analytics purposes. 3NF is the proper normalization level for a transactional database. But for a data warehouse where queries perform advanced aggregation operations, over-normalizing is incorrect – too many joins, too slow. This mistake is made repeatedly whenever a data engineer used to working with relational databases builds his/her first warehouse. A dimensional model is the proper solution, and making a wrong normalization decision for the wrong purpose is among the most costly recurring mistakes in data modeling.
3. Assuming the model will never be touched once deployed. The model was created considering the state of the business at the time of deployment. But the business has changed since then. Nobody has updated the model. Now it reflects something that no longer applies to the data being stored in it, and each newly hired analyst spends two weeks figuring that out the hard way.
4. Failure to consider scale during design. What works for 10,000 rows may fall apart at 100 million. Partitioning strategies, indexing, and selection of primary keys are all issues of scalability that come from data modeling, not database administration.
5. Exclusion of the business. The technical team determines entities based on storage, not on business logic. This leads to defining a “customer” in the data model that includes both trial users and internal testing accounts, which will never be considered customers by the sales department. Such discrepancies arise in the dashboard during board meetings, when they are most costly to correct.

Best Data Modeling Tools in 2026

Traditional Tools

Erwin Data Modeler is the best choice for physical and logical modeling. It provides support for relational and dimensional modeling with IDEF1X notation, robust reverse-engineering capabilities, and integration with leading data governance solutions.

Enterprise Architect focuses on providing support for a wide range of modeling activities: from UML to systems architecture and business process modeling. It’s a great choice for teams that require one solution for different modeling needs.

ER/Studio is focused on database modeling and reverse engineering. Just import your schema and get a document-ready model created for you. ER/Studio is widely used in regulated industries because model documentation is a must there.

IBM InfoSphere Data Architect integrates well with IBM data governance solutions. Consider this software only if you’re using IBM’s infrastructure.

Modern Tools

dbt is not a visual modeling tool; rather, it’s the SQL transformation layer that replaced conventional modeling tools in most analytics engineering teams. Models are expressed as SELECT statements that are version-controlled in Git, tested, and documented. The difference between dbt and other modeling tools is vast enough that some teams use both: erwin for physical model documentation, dbt for transformation code.

SQLMesh is an alternative to dbt with enhanced state management capabilities and integrated CI/CD for data pipelines. It’s gaining traction among teams requiring fine-grained control over incremental runs of their models.

dbdiagram.io is a free, browser-based application that utilizes text syntax to describe tables and relationships. Ideal for drawing a quick sketch of a logical model and sharing it with your team without any software installation.

Lucidchart / draw.io are universal diagramming applications for drawing conceptual models. No database features whatsoever, but they are readily available and easy to share, which is precisely what you want at the very beginning of your project.

DataGrip (JetBrains) is a database IDE with built-in schema visualization. Best suited for reverse-engineering existing physical models from databases in production.

Frequently Asked Questions