Part 3 - Transformations, ELT

Let’s learn by example.

Throughout this tutorial, we’ll walk you through the creation of a end-to-end modern ELT stack.

In parts 1 & 2, we extracted data from GitHub and loaded it into a (local) PostgreSQL database. Now it is time to have more fun. We decide to load all attributes from the data we selected previously, and then build a model listing the different authors of commits to our repository.

That means, in this part we’re going to unleash dbt (data build tool) onto our data to transform it into meaningful information. Don’t worry, you don’t need to know anything about dbt, this tutorial is self-contained. You do not need to install dbt yourself, it works as a Meltano plugin.

Select more source data #

To get all the data from the GitHub commits, you can use the meltano select command:

meltano select tap-github commits "*"

This will add the following line to your project file:

      extractors:
      - name: tap-github
        select:
        - commits.url # <== technically not necessary anymore, but no need to delete
        - commits.sha # <== technically not necessary anymore, but no need to delete
        - commits.commit_timestamp # <== technically not necessary anymore, but no need to delete
        - commits.* # <== new data.

You can test that the new data is extracted by using meltano invoke:

Next, we add the dbt plugin to transform this data.

Install and configure the postgres specific dbt transformer #

dbt uses different adapters depending on the database/warehouse/platform you use. Meltano transformers match this pattern; in this case our transformer is dbt-postgres. As usual, you can use the meltano add command to add it to your project.

As you can see, this adds both the transformer as well as a file bundle to your project. You can verify that this worked by viewing the newly populated directory transform.

Configure dbt #

Configure the dbt-postgres transformer to use the same configuration as our target-postgres loader using meltano config:

Add our source data to dbt #

The EL pipeline run already added our source data into the schema tap_github as table commits. dbt will need to know where to locate this data. Let’s add that to our dbt project:

mkdir transform/models/tap_github

Add a file called source.yml into this directory with the following content:

config-version: 2
version: 2
sources:
  - name: tap_github     # the name we want to reference this source by
    schema: tap_github   # the schema the raw data was loaded into
    tables:
      - name: commits

Now we’re able to reference the table using the keyword “source” as you can see next.

Add a transformed model #

Add a file called authors.sql to the folder transform/models/tap_github with the following contents:

{{
  config(
    materialized='table'
  )
}}


with base as (select *
from {{ source('tap_github', 'commits') }}) 

select distinct (commit -> 'author' -> 'name') as authors from base

This model is configured to creating a table via the materialized='table' configuration. The keyword source is used in dbt to reference the source we just created. The actual model selects the distinct author names from the commits which are wrapped into a JSON blob.

Run the transformation process #

To create the actual table, we run the dbt model via meltano invoke dbt-postgres:run:

You can check the data inside the database using your favourite SQL editor. There should now be a table inside the analytics schema called authors populated with data.

Run the complete pipeline #

To check that everything works together as a pipeline, we clean out once more and run the whole ELT pipeline. Drop the tap_github.commits and the analytics.authors tables by running

docker exec meltano_postgres psql -U meltano -c 'DROP TABLE tap_github.commits; DROP TABLE analytics.authors;'

Run the final pipeline alltogether using the parameter --full-refresh to ignore the stored state:

There we have it, a complete ELT pipeline.

Next Steps #

Next, head over to Part 4, Data Mappings.