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.

To refresh your database tables after the configuration changes you can run meltano run --full-refresh tap-github target-postgres:

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

Install the postgres specific dbt utility #

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

Initialize dbt #

Next you can run the initialize command to have the transformer utility populate the project scaffold for dbt.

You can verify that this worked by viewing that the transform directory is newly populated with dbt configuration files.

Configure dbt #

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

The result of your configuration will look like this in your meltano.yml, remember that sensitive configurations are in your .env file:

  utilities:
  - name: dbt-postgres
    [...]
    config:
      host: localhost
      port: 5432
      user: meltano
      dbname: postgres
      schema: analytics

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 transform/models/tap_github/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. Note this relies on previously running meltano run --full-refresh tap-github target-postgres to postgres your database commits table:

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 -d postgres -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.