Have you ever heard of Operational Analytics?

While your data resides in DWH – it is passive. It awaits while somebody queries it.

Operational Analytics is about making data actionable, not only available through SQL and BI on demand, but really working on day-to-day business in customer-facing workflows:
– Ad Networks (Facebook, Google Ads, …)
– E-mail Tools (Hubspot, Mailchimp, …)
– Lifecycle tools (Salesforce, Braze, …)

reverse-ETL is approach / class of tools for implementing Operational Analytics.
It enables data flowing out of your DWH into Operational Systems in a reliable/predictable/fault tolerant way.

Real-world use-cases:
– Prioritizing leads for Account Managers – most valuable customers first
– Generating custom audiences for advertising campaigns
– Delivering personal incentives and bonuses
– Communicating with customers about to churn

#reverse_etl

There is a number of successful companies who aim to provide reverse-ETL as a service:

– Census
– Hightouch
– Grouparoo

However there are multiple ways to do it yourself:

1. Writing your own noscripts

– Every noscript is adding complexity to your pipelines
– Non-scalable: you should write new code for each integration
– Requires Software Engineering skills: not applicable to business users

Could be automated with:

– Python + Airflow DAGs
– Jupyter + cron + any Python libs (pandas, sklearn, …)

2. Using off the shelf integrations

– Enables using built-in integration capabilities from Software you already use
– Could be tweaked easy enough

Examples are:

– Looker Action Hub
– Redshift with its Spectrum, Datashares, Unloading to S3

#reverse_etl

Let’s examine some integrations I currently use

UNLOADing customer/partner/chauffeur labels to S3 bucket files

Let’s say we want to exchange some data with other teams be it Backend, Financial Service, Frontend. You may also treat it as publishing data for whom it may concern, so there could be multiple consumers.

Could be easily automated it with dbt + Redshift UNLOAD:

– Create a model with desired attributes and rows
– Materialize it as a view
– Add dbt post-hook with UNLOAD noscript
– Schedule it on a daily/hourly basis

As a result:

– No actual excessive data is stored – materialized='view'
– Export is always up-to-date as it is scheduled with dbt jobs
– UNLOAD is performed only with production runs
– For consumers snapshot or history is available
– Multiple file formats supported: CSV, JSON, Parquet

Continued:

Also, pay attention to how a model is built with dbt_utils.unpivot macro:

{{ dbt_utils.unpivot(
  relation=ref('dim_chauffeurs_labels'),
  cast_to='varchar',
  exclude=['actual_dt', 'chauffeur_id'],
  field_name='label',
  value_name='value'
) }}

A list of attributes unloaded could be expanded or narrowed without actual code changes. Schema is ['actual_dt', 'chauffeur_id', 'label', 'value']

Looker action -> Braze (Customer engagement platform)

Let's keep observing ways to build reverse-ETL pipelines.

Looker enables its user to leverage Actions Hub – way of integrating Looker with 3rd party tools. Amongst most notable are:

– Airtable
– S3
– Braze
– Dropbox
– gDrive / gSheets / gAnalytics / gAds
– Hubspot
– mParticle

We calculate certain labels/traits/dimensions on our passengers/chauffeurs based on their behaviour and services usage in DWH.

Looker Action enables scheduled sync of data from Redshift to Braze user cards.

All you need to perform sync is:

– Identifier (user_id / external_id)
– Dimensions which will become traits in Braze card
– Schedule for this Action

Then in Braze we are able to:

– Target our campaigns precisely
– Deliver personal experiences and communications
– Split segments and perform A/B testing
– Identify users who are about to churn

#reverse_etl

Трансляция на YouTube, начинаем!
https://www.youtube.com/watch?v=10H45iYaCc8

[RU] Слайды с митапа:
– [dbt meetup] Почему вашей компании нужен dbt?
– [dbt meetup] Статистика запусков dbt и поиск узких мест

#meetup

The world ain't all sunshine and rainbows. It's a very mean and nasty place.

Especially when you use select *

When a base table changes, all the downstream dependencies might break down.
Please don't deploy such things to production.

⬇️⬇️⬇️

#bugs