BigQuery на собеседовании Data Engineer

Архитектура

Google's serverless DWH. Под капотом — Dremel (origin), Capacitor storage.

Storage и compute fully separated. Pay для storage / scanned bytes / slots.

Slots

Unit compute. 1 slot — fraction CPU.

On-demand: pay per TB scanned.
Slots reservation: fixed monthly.

Reservation cheaper для steady workloads. On-demand для bursty.

Pricing

Storage. $0.02 / GB / month (active). Cheaper для long-term.

Queries.

• On-demand: $5 / TB scanned.
• Reserved slots: monthly fixed.

Streaming inserts. $0.05 / GB. Tracking add up.

Partitioning / clustering

Partitioning. Date / integer / ingestion time.

CREATE TABLE orders (...) PARTITION BY DATE(created_at);

Filter on partition column → scan only relevant.

Clustering. Up to 4 columns.

CLUSTER BY country, category;

Within partition — data sorted. WHERE filters — partial scan.

Combine — partitioning по дате + clustering по country / product.

BigQuery ML

Train / predict ML models через SQL.

CREATE MODEL my_dataset.churn_model
OPTIONS(model_type='logistic_reg', input_label_cols=['churned'])
AS SELECT * FROM my_dataset.users;

SELECT *, predicted_churn FROM ML.PREDICT(MODEL my_dataset.churn_model, ...);

Linear regression, RF, boosted trees, time series, AutoML, even deep learning.

В РФ BigQuery недоступен — sanctions. Local — ClickHouse / Yandex DWH.

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

• Snowflake vs BigQuery для DE
• Партиционирование таблиц для DE
• DWH ClickHouse для DE
• DWH cost optimization для DE
• Подготовка к собесу Data Engineer

FAQ

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

Нет. Статья основана на документации Google BigQuery.

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