DWH cost optimization на собеседовании Data Engineer

Cost drivers

Compute. Per-second / per-query / per-warehouse.

Storage. Per GB / month.

Egress. Network out (cloud → outside).

Staging. Cold storage / archive — cheap.

In modern DWH (Snowflake / BQ) — compute обычно biggest.

Scan reduction

Каждая query scans data → cost.

*Avoid SELECT . Read only needed columns. Columnar formats — pay per column scanned.

Pushdown filters. WHERE early.

Limit data range. Default to recent partition unless explicitly need history.

Dashboards. Cache results, not query на каждое page load.

Partitioning

Big tables — partition по date / category. Queries фильтруют → only scan relevant.

SELECT * FROM events WHERE DATE = '2026-05-07';  -- 1 day scanned, не ALL

In BigQuery — required для huge tables. In CH — partition design critical.

Materialized views

Pre-compute expensive aggregations.

Daily MV for daily dashboards. Refresh once вечером, queries hit MV — cheap.

Snowflake. Materialized views auto-refresh.

ClickHouse. Auto-incremental на каждый insert.

Storage tiering

Hot — recent (SSD). Cold — old (S3 / archive).

Last 30 days: hot (Snowflake / BQ).
30-365 days: warm (S3 standard via Spectrum / external table).
> 1 year: archive (Glacier).

90% storage cost savings возможна.

Cluster scaling

Auto-suspend. Idle warehouse → suspend (Snowflake). Не pay for idle.

Right-sizing. Workload analysis → smaller warehouse OK.

Spot / preemptible для batch jobs.

Reserved instances. Commit к 1-3 years → 30-70% off.

Связанные темы

• Snowflake vs BigQuery для DE
• Партиционирование таблиц для DE
• Data tiering для DE
• Materialized views для DE
• Подготовка к собесу Data Engineer

FAQ

Это официальная информация?

Нет. Статья основана на индустриальных DWH practices.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.