The initial incremental clause looked like this:

    where true

    {% if is_incremental() %}
        and orders.__metadata_timestamp >= 
            (select max(__metadata_timestamp) as high_watermark from {{ this }})
    {% endif %} 

So I basically took only rows where metadata timestamp was newer (later) than timestamp of those rows I already have in my table.

Then I realized sometimes metadata timestamp (which is the exact time when the row was fetched from source system) does not increase gradually, some rows get into different batches and land to DWH at different times, so I introduced a lookback period of 3 hours to make sure I don’t miss anything:

    where true

    {% if is_incremental() %}
        and orders.__metadata_timestamp >= 
            (select max(__metadata_timestamp) as high_watermark from {{ this }}) - interval '3 hours'
    {% endif %} 

That means I have more assurance I will take all the missing rows, but on the other hand I will process a significant amount of rows multiple times as well as trigger more UPDATEs and DELETEs on my target table since incremental periods overlap.

It was all going OK until rare cases of EXTRACT - LOAD errors / pauses / outages showed that even 3 hours of lookback period sometimes is not sufficient.

One can increase lookback interval to 12, 24 or even 48 hours which totally can solve most of the cases, but I decided to rewrite my query in a smart way using NOT EXISTS clause:

    where true

    {% if is_incremental() %}
        and not exists (
            select 1
            from {{ this }}
            where orders.request_id = {{ this }}.request_id
            and orders.__metadata_timestamp = {{ this }}.__metadata_timestamp
        )
    {% endif %} 

That simply means:

– take either completely new rows (‘request_id’ does not exist in {{ this }})
– or take ‘request_id’ which exist in {{ this }} but have different __metadata_timestamp (row has been modified)

I thought it was perfect, but Amazon Redshift didn’t think so 😅:

> This type of correlated subquery pattern is not supported due to internal error

So took another apporach – Anti-join

{% if is_incremental() %}
        left join {{ this }}
            on orders.request_id = {{ this }}.request_id
                and orders.__metadata_timestamp = {{ this }}.__metadata_timestamp

    where {{ this }}.request_id is null
{% endif %} 

But unfortunately EXPLAIN plan revealed Redshift performs anti-join + filtering after performing 2 GEO-spatial joins.
Which technically means processing full data set and resets any incrementality benefits.

In older times I would just use a hint to make joins run in a specific way to filter rows early, however today just shuffling join order was good enough!

    from {{ ref('stg_orders_tmp') }} as orders

    {% if is_incremental() %}
        left join {{ this }}
            on orders.request_id = {{ this }}.request_id
                and orders.__metadata_timestamp = {{ this }}.__metadata_timestamp
    {% endif %} 

        left join {{ ref('stg_zones_tmp') }} as pickup
            on ST_Intersects(
                ST_Point(orders.pickup_position_lon, orders.pickup_position_lat), pickup.geometry)

        left join {{ ref('stg_zones_tmp') }} as dropoff
            on ST_Intersects(
                ST_Point(orders.dropoff_position_lon, orders.dropoff_position_lat), dropoff.geometry)

{% if is_incremental() %}
    where {{ this }}.request_id is null
{% endif %}

So, what do you think of it all?
Have you ever used dbt incremental models?
Let's discuss in comments section.

#dbt #incremental #subquery

Новая публикация на Хабр ⬇️⬇️⬇️

Новая публикация на Хабр ⬇️⬇️⬇️

Требования к ETL-сервисам – построение аналитических решений на базе myBI Connect

Сегодня речь пойдет о сервисах интеграции данных, их функциональных возможностях и ограничениях. Рассмотрение будем вести на примере сервиса myBI Connect, опираясь на который я реализовал с десяток аналитических проектов за последние несколько лет.

Отмечу, что с конца февраля ребята сделали значительные шаги в сторону развития отказоустойчивости и масштабируемости своего решения. Заглядывайте под кат, если стоите перед выбором коннектора или хотите выжимать максимум из доступного:

— Требования и ожидаемые результаты.

— Функциональные возможности.

— Сценарии использования и бизнес-ценность.

— Планы развития, продвинутое моделирование и BI.

Читать на Хабр →

#pipelines #ELT #dwh

Excerpt from Python for DevOps / What Does DevOps Mean to the Authors?

At one company, Noah had a project that was over a year late, and the web application had been rewritten three times in multiple languages. This next release only needed a “performance engineer” to get it finished. I remember being the only one brave or stupid enough to say, “What is a performance engineer?” This engineer made everything work at scale. He realized at that point that they were looking for a superhero to save them. Superhero hiring syndrome is the best way to pick up on something being very wrong on a new product or a new startup. No employee will save a company unless they first save themselves.

it turned out that the real issue was inadequate technical supervision. The wrong people were in charge (and verbally shouting down the people who could fix it). By removing a poor performer, listening to an existing team member who knew how to fix the problem all along, deleting that job listing, doing one right thing at a time, and inserting qualified engineering management, the issue resolved itself without a superhero hire.

The solution to the problem isn’t a new hire; it is being honest and mindful about the situation you are in, how you got there, and doing one right thing at a time until you work your way out. There is no superhero unless it is you.

Новая публикация на Хабр ⬇️⬇️⬇️

Мы могли бы долго и нудно обсуждать, кто такой Analytics (Data / Backend) Engineer, какими инструментами он должен владеть, какие buzzwords в тренде и ценятся в CV, однако, на мой взгляд, гораздо интереснее рассмотреть процесс и результаты его деятельности в рамках конкретной прикладной задачи.

В этой публикации:

— Что значит решение End-to-End и в чем его ценность?
— Организация Extract & Load данных из асинхронного API MaestroQA
— Моделирование витрин данных с помощью dbt
— Поставка ценности для пользователей с помощью Looker

Читать на Хабр →

#pipelines #ELT #dwh #modeling #bi