Efficient Ways to Organize Data Models

Data modeling best practices: a curated list. We'll focus on modular modeling, and explore the essential model layers.

Data modeling best practices: a curated list. We'll focus on modular modeling, and explore the essential model layers.

Poom Wettayakorn

Jul 7, 2024

Jul 7, 2024

data-modeling

👋 Hi there, welcome to Datascale: SQL+Data modeling. We help create database diagrams and map out data models from existing schema and SQL queries 🌱. Find us on LinkedIn 🎉

dbt data model layers
dbt data model layers
dbt data model layers

I've researched and curated a list of best practices for organizing data models.

Thought it'd be nice to share with y'all!

We'll focus on modular modeling, and explore the essential model layers: base, staging, intermediate, and marts (dim & facts).

Some key ideas we'll cover

  • Align best practices for creating data models

  • Pros & cons of different project structures

  • Structure models for a larger scale

Official docs

  1. Project design: https://www.getdbt.com/dbt-learn/lessons/dbt-project-design

  2. Modular modeling: https://www.getdbt.com/analytics-engineering/modular-data-modeling-technique

  3. How to structure a project: https://docs.getdbt.com/best-practices/how-we-structure

  4. Mesh patterns: https://docs.getdbt.com/best-practices/how-we-mesh

Efficient ways to organize data models

Data model layers

Data model layers

Folder structure

 models/
├─ staging
    ├─ <business_domain>
            ├─ base (optional)
                  ├─ base_<name>.sql
            ├─ stg_<name>.sql
               ....
├─ marts
    ├─ <business_domain>
            ├─ intermediate (optional)
                   ├─ int_<name>.sql       
            ├─ dim_<name>.sql
            ├─ fct_<name>.sql
               ....
  • Base = When joins are necessary to stage concepts

  • Staging = The shape you wish your data came in

  • Intermediate = Purpose-built transformation steps

  • Marts = Business-defined entities

Recommended blogs:

Project Structure

Data modeling project structure

Recommended blogs:

Pros & Cons

Real World Examples

Multi-project Architecture

Mesh Patterns

"Mesh Patterns - empowering data teams to work independently and collaboratively; sharing data, code, and best practices without sacrificing autonomy."

"When adopting a multi-project architecture, where do you draw the lines between projects?"

  • Vertical splits

  • Horizontal splits

  • Hybrid

dbt mesh patterns


Related blogs

Contents

Section

Model your database diagram

Datascale helps map out data models from existing schema and SQL queries

Try for free

Data Modeling for SQL

Data Modeling for SQL