Data Build Tool (dbt) for Effective Data Transformation on AWS – Part 4 EMR on EKS

— emr-eks/emr_eks/models/marts/analytics/titles.sql

{{
    config(
        schema=’analytics’,
        materialized=’table’,
        file_format=’parquet’
    )
}}

with titles as (

    select * from {{ ref(‘stg_imdb__title_basics’) }}

),

principals as (

    select
        title_id,
        count(name_id) as num_principals
    from {{ ref(‘stg_imdb__title_principals’) }}
    group by title_id

),

names as (

    select
        title_id,
        count(name_id) as num_names
    from {{ ref(‘int_known_for_titles_flattened_from_name_basics’) }}
    group by title_id

),

ratings as (

    select
        title_id,
        average_rating,
        num_votes
    from {{ ref(‘stg_imdb__title_ratings’) }}

),

episodes as (

    select
        parent_title_id,
        count(title_id) as num_episodes
    from {{ ref(‘stg_imdb__title_episodes’) }}
    group by parent_title_id

),

distributions as (

    select
        title_id,
        count(title) as num_distributions
    from {{ ref(‘stg_imdb__title_akas’) }}
    group by title_id

),

final as (

    select
        t.title_id,
        t.title_type,
        t.primary_title,
        t.original_title,
        t.is_adult,
        t.start_year,
        t.end_year,
        t.runtime_minutes,
        t.genres,
        p.num_principals,
        n.num_names,
        r.average_rating,
        r.num_votes,
        e.num_episodes,
        d.num_distributions
    from titles as t
    left join principals as p on t.title_id = p.title_id
    left join names as n on t.title_id = n.title_id
    left join ratings as r on t.title_id = r.title_id
    left join episodes as e on t.title_id = e.parent_title_id
    left join distributions as d on t.title_id = d.title_id

)

select * from final

# emr-eks/emr_eks/models/marts/analytics/_analytics__models.yml

version: 2

models:
  – name: names
    description: Table that contains all names with additional details
    tests:
      – dbt_utils.equal_rowcount:
          compare_model: ref(‘stg_imdb__name_basics’)
  – name: titles
    description: Table that contains all titles with additional details
    tests:
      – dbt_utils.equal_rowcount:
          compare_model: ref(‘stg_imdb__title_basics’)
  – name: genre_titles
    description: Table that contains basic title details after flattening genres

$ dbt test –select marts
06:05:30  Running with dbt=1.3.0
06:05:30  Found 15 models, 17 tests, 0 snapshots, 0 analyses, 569 macros, 0 operations, 0 seed files, 7 sources, 0 exposures, 0 metrics
06:05:30 
06:06:03  Concurrency: 3 threads (target=‘dev’)
06:06:03 
06:06:03  1 of 2 START test dbt_utils_equal_rowcount_names_ref_stg_imdb__name_basics_ …. [RUN]
06:06:03  2 of 2 START test dbt_utils_equal_rowcount_titles_ref_stg_imdb__title_basics_ .. [RUN]
06:06:57  1 of 2 PASS dbt_utils_equal_rowcount_names_ref_stg_imdb__name_basics_ ………. [PASS in 53.54s]
06:06:59  2 of 2 PASS dbt_utils_equal_rowcount_titles_ref_stg_imdb__title_basics_ …….. [PASS in 56.40s]
06:07:02 
06:07:02  Finished running 2 tests in 0 hours 1 minutes and 31.75 seconds (91.75s).
06:07:02 
06:07:02  Completed successfully
06:07:02 
06:07:02  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2