Портфолио

ELT-пайплайн на dbt + ClickHouse для анализа рынка недвижимости Москвы и ML-прогнозирования цен

Проблема

Аналитики рынка недвижимости сталкиваются с необходимостью агрегировать и анализировать данные из множества разрозненных источников. Данные о продажах и аренде часто лежат в отдельных CSV-файлах с разными схемами, содержат дубли, пропуски и несовместимые форматы. Задача состояла в том, чтобы создать единую, чистую и обогащённую витрину данных для мониторинга рынка и построения модели машинного обучения для прогнозирования цен.

Что сделали

Разработали ELT-пайплайн на dbt и ClickHouse, который:

  1. Загружает 5 CSV-файлов (~78 000 записей) в колоночную БД ClickHouse (RAW-слой)
  2. Послойно трансформирует данные через dbt:
  3. Staging — очистка, типизация, переименование колонок (5 моделей)
  4. Intermediate — объединение всех типов объявлений, обогащение метро и ставкой ЦБ (1 модель)
  5. Marts — создание витрин для дашборда и для ML (2 модели)
  6. Обучает модель Random Forest на витрине признаков, прогнозируя цену за кв.м с R² = 0.85
  7. Автоматически генерирует документацию и линэйдж (граф зависимостей) всех моделей

Все компоненты запускаются в Docker — одна команда для воспроизведения окружения.

Архитектура

CSV → ClickHouse (RAW) → dbt (Staging → Intermediate → Marts) → ML-модель
Слои dbt:
  • Staging: Приведение сырых данных к единому стандарту (типы, имена, фильтрация)
  • Intermediate: Объединение всех объявлений в одну таблицу (UNION ALL), добавление метро и макроэкономики
  • Marts: Две витрины — агрегированная для дашборда и плоская таблица для ML

Ключевые фрагменты кода

Объединение трёх сегментов рынка (Intermediate-модель):
with secondary_market as (
    select listing_id, 'secondary' as listing_type, ...
    from {{ ref('stg_secondary_market') }}
),
rentals as (
    select listing_id, 'rental' as listing_type, ...
    from {{ ref('stg_rentals') }}
)
select * from secondary_market
union all
select * from rentals
Категоризация близости к метро:
case
    when metro_distance_min <= 15 and metro_distance_type = 'transport'
        then 'walk_<15min'
    when metro_distance_min <= 30 and metro_distance_type = 'transport'
        then 'walk_15-30min'
    else 'by_car'
end as metro_accessibility
Обучение ML-модели (Python + scikit-learn):
from sklearn.ensemble import RandomForestRegressor
model = RandomForestRegressor(n_estimators=100, max_depth=15)
model.fit(X_train, y_train)
# R² на тесте: 0.85
# Важность признаков: to_center_km — 60.7%
Весь код открыт на GitHub: https://github.com/olkhovii/data-engineer-portfolio

Результаты

  • 78 000 объявлений обработано и объединено
  • 22 автоматических теста на качество данных (not_null, unique, accepted_values)
  • ML-модель: R² = 0.85 на тестовой выборке
  • Главный фактор цены: удалённость от центра (важность 60.7%), доступность метро (8.6%)
  • Ставка ЦБ найдена для 100% объявлений
  • Документация: автоматический сайт с линэйджем всех моделей
2026-06-16 16:10 BI аналитика