Introduction

Analytics engineering attempts to bridge the gap between data engineering and data analytics by introducing good software engineering practices.

Data Modeling Concepts

ETL vs ELT (extract transform load vs extract load transform)

• ELT will transform the data after it’s loaded data warehouse.
  • Faster and more flexible data analysis
  • lower cost and lower maintenance
  • however, less stable and less compliant data analysis

Kimball’s Dimensional Modeling

• Objective: Deliver data understandable to the business users with fast query performance
• Approach: Prioritize user understandability and query performance over non redundant data (3NF)
  • other approaches include Bill Inmon, Data vault…
• Key Principles
  • Conformed dimensions: Shared across multiple fact tables for consistent reporting
  • Atomic data: Store data at the lowest granularity
  • Surrogate keys: Use generated keys instead of natural business keys
  • Denormalization: Accept redundancy in dimension tables for query simplicity
• Different types of tables
  • Fact tables
    • These contain the actual measurements, metrics, or facts about business processes (like sales amounts, quantities sold, etc.)
    • Each row represents one business event or transaction
    • Contains foreign keys to dimension tables and numerical measures
    • Usually the largest tables in the system
  • Dimension tables

    • Contain descriptive attributes that give context to the facts

    • Examples include Customer, Product, Time, Location

    • Typically denormalized for simpler queries and better performance

      • In a normalized database design, related information would be split across multiple tables to eliminate redundancy. However, in dimensional modeling, we intentionally combine this information into a single dimension table - this is denormalization.
      • benefits
        • Faster queries since no joins are needed
        • Simpler SQL statements for business users
        • Better query performance in data warehouses
        • Direct access to all product-related attributes in one place
      • cons
        • data redundancy

      -- normalized design
      
      -- Product table
      ProductID, ProductName, CategoryID
      -- Category table
      CategoryID, CategoryName, DepartmentID
      -- Department table
      DepartmentID, DepartmentName
      
      ~~~~
      -- denormalized design
      
      -- Product Dimension
      ProductID, 
      ProductName,
      CategoryName,    -- Pulled from Category table
      DepartmentName,  -- Pulled from Department table
      Brand,
      Size,
      Color,
      ... other attributes
      

    • Much smaller than fact tables but wider (more columns)

  • Star Schema
    • The primary pattern where one fact table is connected to multiple dimension tables
    • Resembles a star with the fact table in the center
    • Simpler to understand and query than normalized models
  • Slowly Changing Dimensions (SCDs)
    • Handles how dimensional attributes change over time
    • Type 1: Overwrite old values
    • Type 2: Keep historical values by adding new rows
    • Type 3: Keep limited history using previous value columns

What is dbt?

Allows users to work with SQL to deploy analytics code following software engineering best practices like modularity, DRY, portability, CI/CD, and documentation.

How does it work?

1. Raw data flows from the warehouse → modeling layer where it is transformed