Solution code for hands-on exercises

dbt_nz_lab_solution_code.sql

-- Exercise 1: Define a staging model (stg_player.sql)

with player as (
    select * from {{ source('fifa', 'player') }}
)

select 
    id as player_id
    , affiliation_id
    , concat(player_first_name, ' ', player_last_name) as player_name
    , weight
    , height
    , city
    , birth_date
    -- obtain age at 2018 world cup start
    , datediff(year,birth_date,'2018-06-14') as age
from player


-- Exercise 1B: Bonus

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

-- Exercise 2: Enrich our model (dim_players.sql) 

wwith stg_player as (
    select * from {{ ref('stg_fifa__player') }}
)
, stg_team as (
    select * from {{ ref('stg_fifa__team') }}
)
, final as (
    select 
        stg_player.player_id
        , stg_player.affiliation_id
        , stg_player.player_name
        , stg_player.weight
        , stg_player.height
        , stg_player.city
        , stg_player.birth_date
        , stg_player.age
        , stg_team.team_name
        , stg_team.country_code
    from stg_player
    left join stg_team on stg_player.affiliation_id = stg_team.affiliation_id
)

select * from final

-- Exercise 2B: Bonus

- name: dim_players
    description: "Single source of truth for player information. Enhanced with team data."
    columns:
      - name: player_id
      - name: team_name
        description: "Team name is taken from stg_team model and joined on affiliation_id"



-- Exercise 3: Test our event stream data 

    columns:
      - name: player_id
        tests:
        - not_null

-- Exercise 3B: Bonus 


{{
    config(
        error_if = '>10'
        , warn_if = '>0'
    )
}}

select 
    *
from {{ ref('stg_player') }}
where age <19 or age>36


-- Exercise 4: analyze player characteristics  (player_facts.sql)


with dim_players as (
    select * from {{ ref('dim_players') }}
)
, fct_events as (
    select * from {{ ref('fct_events') }}
) 
,final as (

    {% set event_types= ['goal','miss','card','pass'] %}

    select 
        dim_players.player_id
        , player_name
        , weight
        , height
        , city
        , birth_date
        , affiliation_id
        , team_name
        , country_code
        {% for et in event_types %}
            , sum (case when event_type_name = '{{et}}' then 1 else 0 end) as {{et}}_count
        {% endfor %}
        , 1.0*(goal_count / nullif(miss_count + goal_count,0)) as goal_percentage    
    from dim_players
    left join fct_events on dim_players.player_id = fct_events.player_id
    group by 1,2,3,4,5,6,7,8,9
)

select * from final 

-- Director's cut: try using the pivot macro below

        , {{ dbt_utils.pivot(
            'event_type_name',
            dbt_utils.get_column_values(ref('fct_events'), 'event_type_name'),
            suffix='_count',
            quote_identifiers=False
        ) }}