Part 2 · 1 chapters · ~8 min

ETL and ELT

Extract-transform-load versus extract-load-transform, raw, staging and mart layers, dbt models with ref(), incremental models, tests and documentation, idempotent and replayable pipelines, backfills, and ingestion tools (Fivetran, Airbyte, Debezium).

Load raw, transform with dbt

code

-- models/staging/stg_transfers.sql
select id as transfer_id, account_id, amount_kobo, upper(currency) as currency, status,
       created_at at time zone 'UTC' as created_at_utc
from {{ source('core', 'transfers') }}

-- models/marts/fct_daily_volume.sql (incremental: only process new days)
{{ config(materialized='incremental', unique_key=['day', 'currency']) }}
select date_trunc('day', created_at_utc) as day, currency, sum(amount_kobo) as volume_kobo, count(*) as transfers
from {{ ref('stg_transfers') }}
where status = 'completed'
{% if is_incremental() %} and created_at_utc >= (select max(day) - interval '2 days' from {{ this }}) {% endif %}
group by 1, 2

# models/marts/schema.yml
- name: fct_daily_volume
  columns:
    - { name: currency, tests: [not_null, { accepted_values: { values: [NGN, USD, GHS, KES] } }] }
    - { name: volume_kobo, tests: [not_null] }

Idempotent pipelines: rerunning a day must produce the same result (merge on keys, overwrite partitions), so retries and backfills are safe. The two-day look-back above re-processes late-arriving rows.

ETL AND ELT

where the transformation happens

swipe the figure sideways, or tap expand for full screen

1/4

ETL

Extract, transform in a separate engine, then load the result. Common when storage and warehouse compute were expensive; transformations live outside SQL.

transform before loadingolder, still used