Skip to main content

πŸ“˜ Data Modeling Basics (Data Engineering Perspective)

This section introduces just enough data modeling knowledge required for working with Snowflake as a Data Engineer.
We do not go into full data modeling theoryβ€”only the essential concepts needed to understand how data is structured before it lands in Snowflake and how DEs build pipelines around it.


🎯 Why Data Engineers Need Light Data Modeling

Even if modeling is primarily done by Data Architects/Analysts, DEs must understand:

  • How tables relate to each other
  • How facts & dimensions work
  • Why certain tables need SCD logic
  • How Bronze β†’ Silver β†’ Gold layers map to modeling
  • How Snowflake structures align with business requirements

This knowledge helps you build correct, scalable, and efficient pipelines.


🧩 1. What Is a Data Model?

A data model explains how data is organized, stored, and related within a system.

A data model is a blueprint that defines what tables exist and how they connect.

For Data Engineering, you only need to know:

  • Entities β†’ become tables
  • Attributes β†’ become columns
  • Relationships β†’ define how tables join

πŸ”— 2. Basic Relationships (Very Important for SQL & Pipelines)

1️⃣ One-to-One (1–1)​

Examples:

  • Person ↔ Passport
  • Product ↔ Product Details

2️⃣ One-to-Many (1–M) β€” Most common​

Examples:

  • Customer β†’ Orders
  • Department β†’ Employees
  • Store β†’ Sales

3️⃣ Many-to-Many (M–M)​

Examples:

  • Students ↔ Courses
  • Products ↔ Categories

These usually require a bridge table.


πŸ— 3. Dimensional Modeling (Snowflake-Friendly)

Dimensional Modeling is the foundation of analytical data structures in Snowflake.
It includes fact tables, dimension tables, star schema, snowflake schema, and SCD logic.

Star Schema and Snowflake Schema are both part of Dimensional Modeling.


🍏 Dimension Tables​

  • Descriptive attributes
  • Slowly changing
  • Examples: Customer, Product, Store

πŸ“Š Fact Tables​

  • Business events (transactions)
  • Numeric measures
  • Examples: Sales, Orders, Payments

πŸ“ Diagram: Fact & Dimension Tables​


⭐ 4. Star Schema

A star schema connects one fact to multiple dimensions.

Characteristics:

  • Central fact table
  • Surrounding dimension tables
  • Denormalized
  • Best for performance
  • Most widely used in Snowflake analytics

Why DEs care:

  • Dimensions load first
  • Facts require surrogate keys
  • Performs extremely fast in Snowflake

❄️ 5. Snowflake Schema

A normalized variation of a star schema.

Characteristics:

  • Dimensions split into sub-dimensions
  • More joins β†’ slightly slower
  • Used where consistency > performance

DE takeaway:

  • Less preferred today
  • Star schema is cleaner & faster

🎯 Why DEs Should Know This

Dimensional Modeling includes:

  • Fact tables
  • Dimension tables
  • Star/Snowflake schemas
  • Surrogate keys
  • SCD logic

DEs use these to design and build:

  • Silver Layer β†’ Dimensions
  • Gold Layer β†’ Facts

πŸ”„ 6. SCD (Slowly Changing Dimensions)

Dimensions change over time. Data Engineers most commonly work with SCD 0, 1, and 2.


πŸ“Š SCD Types Comparison​

SCD TypeNamePurposeHow It WorksKeeps History?
0Fixed / PassiveDo not allow changesOriginal value remains unchanged❌ No
1OverwriteKeep only the latest valueUpdate the existing row❌ No
2Historical TrackingPreserve complete change historyInsert new row + close old row with datesβœ… Yes

SCD Type 1 β€” Overwrite​

  • No history
  • Updates existing row
  • Used for corrections

SCD Type 2 β€” Preserve History​

  • Inserts a new row on change
  • Tracks all versions
  • Needed for accurate fact joins

SCD2 Diagram​


🧱 7. Modeling Layers in Modern Data Engineering

Bronze​

  • Raw
  • Ingested as-is

Silver​

  • Clean
  • Deduped
  • SCD applied
  • Dimension modeling

Gold​

  • Facts
  • Aggregations
  • Analytics-ready

🧠 8. Data Engineer vs Data Modeler Responsibilities

ActivityData EngineerData Modeler
Define entitiesβŒβœ”
Create fact/dimension definitionsβŒβœ”
Define relationshipsβŒβœ”
Implement SCD logicβœ”βŒ
Build pipelinesβœ”βŒ
Optimize table structuresβœ”βŒ
Create tables in Snowflakeβœ”βŒ

🎯 Summary β€” What DEs Must Remember

  • A model defines tables, columns & relationships
  • Dimensions describe β†’ Facts measure
  • Star schema = most preferred
  • SCD1 & SCD2 = must-know for DEs
  • DEs implement models, not design them
  • Understanding modeling = better pipelines