π 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 Type | Name | Purpose | How It Works | Keeps History? |
|---|---|---|---|---|
| 0 | Fixed / Passive | Do not allow changes | Original value remains unchanged | β No |
| 1 | Overwrite | Keep only the latest value | Update the existing row | β No |
| 2 | Historical Tracking | Preserve complete change history | Insert 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
| Activity | Data Engineer | Data 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