Dimensional modeling

Введение в моделирование данных в Snowflake

Nuno Rocha

Director of Engineering

Введение в dimensional data model

Dimensional modeling: Техника структурирования данных, разделяющая измерения (факты) и описательные характеристики (измерения), оптимизированная для отчётности и анализа.

Dimensional model

Введение в dimensional data model (1)

Измерения (Dimensions): Сущности с категориальными данными в dimensional model.
Факты (Facts): Сущности, фиксирующие и количественно описывающие активности внутри категорий измерений.

Dimensional college model

Схема «звезда» dimensional model

Dimensional star model

Схема «звезда» dimensional model

Snowflake Data Warehouse: Облачный сервис хранения данных и аналитики.
Snowflake Schema: Метод организации данных в dimensional model, включающий субизмерения.

Snowflake, data warehouse vs dimensional model

Схема «снежинка» dimensional model

Dimensional snowflake model

Определение измерений

Переименуйте сущности по шаблону dim_EntityName для ясности, обозначив измерения в модели:

ALTER TABLE students RENAME TO dim_students;

ALTER TABLE classes RENAME TO dim_classes;

ALTER TABLE schools RENAME TO dim_schools;

Определение измерения дат

Создание таблицы dim_date для хранения ключевых фиксированных дат, связанных с зачислением студентов:

CREATE OR REPLACE TABLE dim_date (
    date_id NUMBER(10,0) PRIMARY KEY,
    year NUMBER(4,0),
    semester VARCHAR(255)
);

Определение факта зачислений

Создайте сущность фактов со ссылками на все измерения:

CREATE OR REPLACE TABLE fact_enrollments (
    enrollment_id NUMBER(10,0) PRIMARY KEY,
    student_id NUMBER(10,0),
    class_id NUMBER(10,0),
    date_id NUMBER(10,0),
    FOREIGN KEY (student_id) REFERENCES dim_students(student_id),
    FOREIGN KEY (class_id) REFERENCES dim_classes(class_id),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id)
);

Извлечение данных из измерений

College dimensional data model

Извлечение данных из измерений (1)

SELECT name, 
    class_name
FROM fact_enrollments
    JOIN dim_students -- Joining to get student names
    ON fact_enrollments.student_id = dim_students.student_id
    JOIN dim_classes -- Joining to get class names
    ON fact_enrollments.class_id = dim_classes.class_id
    JOIN dim_schools -- Joining to filter for the 'Science' school
    ON dim_classes.school_id = dim_schools.school_id
    JOIN dim_date -- Joining to restrict data to the year 2023
    ON fact_enrollments.date_id = dim_date.date_id
WHERE dim_schools.school_name = 'Science' 
    AND dim_date.year = 2023;

Обзор терминологии и функций

Dimensional modeling: Техника структурирования данных, разделяющая измерения (факты) и описательные характеристики (dimensions), оптимизированная для отчётности и анализа
Dimensions: Сущности с категориальными данными в dimensional model
Facts: Сущности, фиксирующие и количественно описывающие активности внутри категорий измерений
ALTER TABLE: SQL-команда для изменения структуры существующей сущности
RENAME TO: SQL-команда, используемая с ALTER TABLE для переименования сущности
JOIN ON: SQL-конструкция для объединения строк из таблиц на основе связанного столбца (ON)
WHERE: SQL-конструкция для фильтрации записей по заданному условию
AND: Логический оператор, используемый с WHERE для объединения нескольких условий

Обзор функций

-- Modifying a entity
ALTER TABLE table_name 
RENAME TO new_name;

-- Querying data from merged entities filtered by specific conditions
SELECT 
    table_name.column_name,
    other_name.*
FROM table_name
    JOIN other_table 
    ON table_name.FK = other_table.PK
WHERE column_name  condition  value
    AND column_name  condition  value;

Давайте потренируемся!

Введение в моделирование данных в Snowflake