Schema evolution showcase

Data is ever changing. A couple of examples you will definitely face with while dealing with data pipelines:

– adding new attributes, removing old ones
– changing data types: int -> float, resizeing text
– renaming columns (change mapping)

Got a couple of new events with an attribute exceeding current maximum length in database.

Simple approach is to resize problem column:

ALTER TABLE hevo.wheely_prod_orders ALTER COLUMN stops TYPE VARCHAR(4096) ;

A lot of ELT tools can do it automatically without DE attention.

In my case it's tricky as I am using Materialized Views:

> SQL Error [500310] [0A000]: Amazon Invalid operation: cannot alter type of a column used by a materialized view

As a result I had to drop dependent objects, resize columns, then re-create objects once again

DROP MATERIALIZED VIEW flatten.flt_orders CASCADE ;
DROP MATERIALIZED VIEW dbt_test.flt_orders CASCADE ;
DROP MATERIALIZED VIEW ci.flt_orders CASCADE ;

ALTER TABLE hevo.wheely_prod_orders ALTER COLUMN stops TYPE VARCHAR(4096) ; 

dbt run -m flt_orders+1 --target prod
dbt run -m flt_orders+1 --target dev
dbt run -m flt_orders+1 --target ci

#pipelines #schemaevolution

One of the things to improve code quality is doing proper reviews

– Require at least 2 reviews for a PR
– Assign reviewers automatically according to CODEOWNERS file
– Require status checks to pass before merging

CODEOWNERS could look as simple as:

*  @owner1 @owner2 @owner3

/models/flatten/    @owner1
/models/staging/    @owner2
/models/marts/    @owner3

Another thing is testing expectations of your data

As soon as you face any bug or issue, add a test on it to not miss it next time!

My new expectation is simply not empty table – expect_table_row_count_to_be_between from dbt_expectations package

  tests:
    - dbt_expectations.expect_table_row_count_to_be_between:
        min_value: 100

#codereview #codequality

17 days. The end 😅

Metabase + Clickhouse tutorial on Yandex.Cloud

1. Spin up a Virtual Machine
2. Configure VM: SSH, Docker
3. Download Clickhouse plug-in
4. Deploy Metabase
5. Connect to Clickhouse Playground
6. Visualize a question

https://gist.github.com/kzzzr/ecec7dca8bb70586a23569993df470e8

#bi #clickhouse

[RU] Строим Data Vault на данных TPC-H – Greenplum + dbtVault

В публикации:
- Готовим датасет TPC-H
- Поднимаем кластер Greenplum в Яндекс.Облаке
- Погружаемся в кодогенерацию и макросы dbtVault
- Cимулируем инкрементальное наполнение Data Vault

#dbt #dbtvault

Who knows what the heck is this?
Telegram sold out?

Привет! Сегодня 18 ноября в 15.00 приглашаю на вебинар.

Полуструктурированные данные в Аналитических Хранилищах: Nested JSON + Arrays

- Источники полуструктурированных данных: Events, Webhooks, Logs
- Подходы: JSON functions, special data types, External tables (Lakehouse)
- Оптимизация производительности

Смотрим на примерах Amazon Redshift, Clickhouse.

Ссылка на регистрацию: https://otus.ru/lessons/dwh/#event-1661
Ссылка на youtube-трансляцию будет опубликована здесь за 5 минут до начала.

[RU] Вебинар Полуструктурированные данные в Аналитических Хранилищах: Nested JSON + Arrays

Слайды вебинара: https://docs.google.com/presentation/d/1dUxzGkBgXAp6s-VrFKT8Qw8UF6eZVQywpeQT4-ZohPM/edit?usp=sharing
Запись вебинара: https://youtu.be/dtu0yeFdxvY?t=276
Опрос о вебинаре: https://forms.gle/JPFqoDYhJJjvnMj7A