DE What Is Data Modeling
Storing data is not enough. The way data is organized inside a database or data warehouse determines how fast queries run, how easy it is for analysts to understand the data, and whether the system scales as data volumes grow. Data modeling is the discipline of designing that organization deliberately and thoughtfully.
The Core Idea
A data model is a blueprint that defines what data exists, how it is structured, and how different pieces of data relate to each other. Just as an architect draws floor plans before a building is constructed, a data engineer designs a data model before tables are created. The model captures which tables exist, what columns each table contains, which data types each column uses, and how tables connect through keys.
The City Map Analogy
Imagine planning a new city without a map. Streets go wherever builders feel like, addresses repeat, no one knows which road connects to which. Every trip through the city is a frustrating guess. A city map plans everything in advance — streets connect logically, addresses are unique, districts have clear boundaries. A data model is the city map for a database. Without it, data becomes a maze. With it, analysts find what they need immediately.
What Data Modeling Produces
A data model produces a set of tables, columns, relationships, and constraints that together form the schema of a database. The schema is the structure — the skeleton — that all data must fit into.
Example: A simple e-commerce data model
Table: customers Table: orders Table: products
+-------------+ +-------------+ +------------+
| customer_id | <---+ | order_id | +--> | product_id |
| name | | | customer_id | ------+ | name |
| email | +---- | product_id | | price |
| city | | quantity | | category |
+-------------+ | order_date | +------------+
+-------------+
Three Levels of Data Modeling
Conceptual Model
The conceptual model is the highest-level view. It identifies the major entities and their relationships without worrying about technical details. A conceptual model for an e-commerce system might simply state: customers place orders, orders contain products. No columns, no data types — just the big picture. Business stakeholders participate in this level.
Logical Model
The logical model adds more detail. It defines the attributes of each entity, identifies primary and foreign keys, and specifies relationships (one-to-many, many-to-many). It remains independent of any specific database technology. A logical model shows that a customer has a customer_id, name, and email, and that each customer can have many orders.
Physical Model
The physical model translates the logical model into the actual database implementation. It specifies the exact data types (VARCHAR(100), INTEGER, DATE), indexes to add for performance, partitioning strategy, and the specific syntax for the chosen database system. This is what the data engineer actually builds.
Physical Model Example (PostgreSQL): CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE NOT NULL, city VARCHAR(80), created_at TIMESTAMP DEFAULT NOW() ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), order_date DATE NOT NULL, total NUMERIC(10, 2) NOT NULL );
Two Major Modeling Approaches
Normalized Modeling (OLTP Style)
Normalization organizes data to minimize redundancy. Each fact lives in exactly one place. A customer's name appears only in the customers table, never repeated inside the orders table. This design protects data integrity and makes inserts, updates, and deletes efficient. Operational databases (the ones powering applications) use normalized models.
Dimensional Modeling (OLAP Style)
Dimensional modeling organizes data for analytical queries. It accepts some redundancy to make queries simpler and faster. A fact table stores measurable events (sales, clicks, transactions) surrounded by dimension tables that provide context (who, what, where, when). This model powers data warehouses. The star schema and snowflake schema — covered in upcoming topics — are the two primary dimensional modeling patterns.
Why Data Modeling Matters
Query Performance
A well-designed model allows queries to run in seconds. A poorly designed model forces analysts to write complex workarounds that take minutes or hours. Proper indexing, correct join relationships, and thoughtful partitioning all depend on good modeling decisions.
Data Integrity
Constraints defined in the model prevent bad data from entering the system. A foreign key constraint prevents an order from referencing a customer that does not exist. A NOT NULL constraint prevents a required field from going empty. These protections happen automatically at the database level.
Maintainability
A clearly structured model is easy for new engineers and analysts to understand. When a model is logically organized and well-documented, teams onboard faster, debug issues more quickly, and add new data sources without breaking existing pipelines.
Common Data Modeling Tools
Tool | Use Case ----------------|--------------------------------------------- dbt | Transform and document models in a warehouse ERDiagram tools | Visualize entity-relationship diagrams Lucidchart | Draw conceptual and logical models visually dbdiagram.io | Generate schema diagrams from text syntax DataGrip | Explore and design database schemas
Summary
Data modeling defines how data is structured, organized, and related inside a database or warehouse. It works at three levels — conceptual, logical, and physical — moving from business concepts to technical implementation. Good data modeling produces fast queries, protects data integrity, and creates systems that analysts and engineers can understand and maintain. It is one of the most impactful skills a data engineer can develop.