Conceptual vs Logical vs Physical Data Models

Why Are Data Models Important?

Before diving into the specifics, let’s understand the purpose of data modeling. Imagine building a house. You wouldn’t start hammering wood together randomly—you’d begin with a sketch, then a blueprint, and finally the construction. Data models serve a similar purpose for databases and data systems.

They help ensure everyone involved (business users, developers, engineers) shares the same understanding of how data is organized, connected, and accessed.

1. What Is a Conceptual Data Model?

The conceptual model is your high-level business map. It’s like the sketch of your house drawn on a napkin. It’s not concerned with technology or database structures. Instead, it focuses on the business concepts and how they relate.

In simple terms, it answers questions like:

• What are the key things we care about? (e.g., Customers, Products, Orders)
• How are they related? (e.g., Customers purchase Products)

Here’s a basic example:

• Entities: Customer, Product, Purchase
• Relationships: A Customer makes a Purchase; a Purchase involves a Product

You don’t list detailed fields or attributes yet—just the big picture. It’s usually created during the early discussions between business stakeholders and data professionals. It helps everyone align on the language and goals before jumping into technical design.

When Do You Use a Conceptual Model?

You use this at the start of a project, especially when:

• You’re gathering requirements from the business
• You’re building a shared understanding with non-technical stakeholders
• You want to clarify business rules and entities

2. What Is a Logical Data Model?

Once you understand the business concepts, it’s time to turn those into a more detailed, technology-independent design. That’s the job of the logical model.

The logical model focuses on the structure of data—what fields each entity has, how they’re connected, and what kind of data they store. But it still doesn’t worry about the actual database platform or syntax.

Continuing the earlier example, a logical model might say:

• Customer has attributes like First Name, Last Name, Email
• Product has attributes like Product ID, Name, Price
• Purchase links Customer and Product using unique IDs

In the context of Data Vault (a methodology for modeling enterprise data warehouses), this is where you define your:

• Hubs: Core business entities (e.g., Customer, Product)
• Links: Relationships between entities (e.g., Purchase)
• Satellites: Descriptive data about Hubs and Links (e.g., customer name, address)

You also classify data here. For instance, you might mark some attributes as sensitive for privacy reasons or note how frequently they change.

When Do You Use a Logical Model?

This comes after your conceptual model, typically during solution design or system architecture planning. You use it when:

• You need to define how data should be structured and connected
• You’re planning your Data Vault architecture
• You want to define metadata for automation tools like dbt, Wherescape, or Coalesce

3. What Is a Physical Data Model?

Now we get technical. The physical model is the actual implementation in a database system. It’s like building the house based on the blueprints.

This includes:

• Create table statements
• Insert/load scripts
• Indexes and constraints (e.g., foreign keys)
• Platform-specific configurations (e.g., Snowflake vs. Oracle)

In the physical model, you decide:

• How data is stored (table structures, partitions)
• How data is accessed (views, security layers)
• How data is secured (row-level or column-level permissions)

For example, if your logical model says an attribute is sensitive, your physical model might enforce this by putting that attribute in a separate satellite or restricting access using database roles.

When Do You Use a Physical Model?

This comes last in the chain. You use it when:

• You’re ready to implement in a database
• You’re generating SQL from your metadata (often using automation tools)
• You’re deploying the actual tables, views, and loading processes

Summary: The Three Layers Compared

Aspect	Conceptual Model	Logical Model	Physical Model
Purpose	High-level business understanding	Detailed data structure without technology	Actual implementation in a specific database
Focus	Entities & relationships	Attributes, keys, classifications	SQL scripts, schemas, storage
Audience	Business users & analysts	Data architects & engineers	DBAs & developers
Examples	Customer purchases Product	Customer has Name, Email; Link to Product	CREATE TABLE Customer (…); GRANT SELECT ON ViewX

The Modeling Process: From Concept to Code

One of the key takeaways from Michael Olschimke’s explanation in the Data Vault Friday session is that these models are not alternatives—they’re steps in a process.

1. Start with the conceptual model to understand the business.
2. Create a logical model based on the incoming data and requirements.
3. Generate the physical model using automation tools or code templates tailored to your database platform.

Each step builds on the one before it, guiding you from abstract ideas to concrete implementation.

Final Thoughts

If you’re new to data engineering, start small. Talk to the business. Sketch out their world. Then, gradually evolve those ideas into structured data models and finally into code. Understanding the differences between conceptual, logical, and physical models will make you a more effective engineer—and a better bridge between business and tech.

Watch the Video

Meet the Speaker