Data warehouse: 3NF — star schema трансформация

Условие задания

**Компания:** Сбер

**Контекст:** Операционная база в 3NF. Нужно спроектировать аналитическое хранилище в star schema и написать ETL-трансформацию.

**3NF (источник):**

[см. код в задании]

**Задание:**
1. Спроектируйте star schema: fact_transactions + dim_client + dim_date + dim_account_type
2. Напишите ETL-запросы для заполнения каждого измерения (денормализация)
3. Напишите ETL для таблицы фактов
4. Напишите аналитический запрос: топ-10 регионов по объёму транзакций за квартал

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE dim_client (
    client_sk SERIAL PRIMARY KEY,
    client_id INT NOT NULL,
    client_name VARCHAR(200),
    city_name VARCHAR(100),
    region_name VARCHAR(100),
    federal_district VARCHAR(100),
    valid_from DATE DEFAULT CURRENT_DATE,
    valid_to DATE DEFAULT '9999-12-31',
    is_current BOOLEAN DEFAULT TRUE
);

CREATE TABLE dim_account_type (
    account_type_sk SERIAL PRIMARY KEY,
    account_type VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE dim_date (
    date_sk INT PRIMARY KEY,    -- YYYYMMDD
    full_date DATE NOT NULL UNIQUE,
    year INT, quarter INT, month INT,
    month_name VARCHAR(20), day_of_week INT,
    is_weekend BOOLEAN, is_holiday BOOLEAN DEFAULT FALSE
);

CREATE TABLE fact_transactions (
    txn_sk BIGSERIAL PRIMARY KEY,
    client_sk INT REFERENCES dim_client(client_sk),
    account_type_sk INT REFERENCES dim_account_type(account_type_sk),
    date_sk INT REFERENCES dim_date(date_sk),
    txn_id BIGINT NOT NULL,
    amount NUMERIC(14,2),
    txn_type VARCHAR(20)
);

Темы

DWH 3NF star schema трансформация моделирование

Подсказки

• Денормализация: dim_client объединяет clients + cities + regions в одну таблицу
• dim_date предзаполняется, не загружается из источника
• Факт ссылается на суррогатные ключи измерений

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Data warehouse: 3NF — star schema трансформация"?

Это задание для уровня hard. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «data_engineering» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: DWH, 3NF, star schema, трансформация, моделирование.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «data_engineering»?

На zasqlpython.ru есть другие задания в категории «data_engineering», продуктовые кейсы, справочник метрик, AI мок-собеседование с разбором ваших ответов.

← Все задания