Skip to content

Instantly share code, notes, and snippets.

@adinsmoor

adinsmoor/dbt_snowflake_solutions_ex1.sql

Forked from dltm-markwan/dbt_snowflake_solutions_ex1.sql
Last active March 27, 2025 23:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save adinsmoor/ba3e36e0c3f6f5dbbf4411ea51856758 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save adinsmoor/ba3e36e0c3f6f5dbbf4411ea51856758 to your computer and use it in GitHub Desktop.
Download ZIP
Snowflake Bootcamp Gists
Raw
dbt_snowflake_solutions_ex1.sql
{{
config(
materialized='table'
)
}}
with
source as (
select * from {{ source('fifa', 'player') }}
),
renamed as (
select
id as player_id,
player_first_name,
player_middle_name,
player_last_name,
player_known_name,
concat(player_first_name, ' ', player_last_name) as player_name,
datediff(year,birth_date,'2018-06-14') as age,
birth_date,
weight,
height,
city,
national_team_affiliation_id,
affiliation_id
from source
)
select * from renamed
Raw
dbt_snowflake_solutions_ex2.yml
version: 2
models:
- name: stg_player
description: This model consolidates player information from the FIFA dataset, providing
key details such as player names, age, physical attributes, and team affiliations.
It is used to analyze player demographics and characteristics.
columns:
- name: player_id
description: A unique identifier for each player.
- name: player_first_name
description: The first name of the player.
- name: player_middle_name
description: The middle name of the player, if available.
- name: player_last_name
description: The last name of the player.
- name: player_known_name
description: The name by which the player is commonly known.
- name: player_name
description: The full name of the player, combining first and last names.
tests:
- not_null
- name: age
description: The age of the player as of June 14, 2018.
tests:
- not_null
- name: birth_date
description: The birth date of the player.
- name: weight
description: The weight of the player.
- name: height
description: The height of the player.
- name: city
description: The city associated with the player, possibly their birthplace or
residence.
- name: national_team_affiliation_id
description: The identifier for the player's national team affiliation.
- name: affiliation_id
description: The identifier for the player's club or other team affiliation.
Raw
dbt_snowflake_solutions_ex3.sql
with player as (
select * from {{ ref('stg_player') }}
)
, team as (
select * from {{ ref('stg_team') }}
)
, final as (
select
player.player_id
, player.affiliation_id
, player.player_name
, player.weight
, player.height
, player.city
, player.birth_date
, player.age
, team.team_name
, team.country_code
from player
left join team on player.affiliation_id = team.affiliation_id
)
select * from final
Raw
dbt_snowflake_solutions_ex4.sql
{{
config(
severity = 'warn',
error_if = '>10'
)
}}
with player as (
select * from {{ ref('dim_players') }}
)
select
player_id
, player_name
, age
from player
where age < 18
OR age > 36
Raw
dbt_snowflake_solutions_ex5.sql
with dim_players as (
select * from {{ ref('dim_players') }}
)
, fct_events as (
select * from {{ ref('fct_events') }}
)
,final as (
select
dim_players.player_id
, player_name
, weight
, height
, city
, birth_date
, affiliation_id
, team_name
, country_code
, sum (case when event_type_name = 'goal' then 1 else 0 end) as goal_count
, sum (case when event_type_name = 'miss' then 1 else 0 end) as miss_count
, sum (case when event_type_name = 'card' then 1 else 0 end) as card_count
, sum (case when event_type_name = 'pass' then 1 else 0 end) as pass_count
, 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
Raw
dbt_snowflake_solutions_ex5_jinja.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
Raw
dbt_snowflake_solutions_ex5_pkg.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
, {{ dbt_utils.pivot(
'event_type_name',
dbt_utils.get_column_values(ref('fct_events'), 'event_type_name'),
suffix='_count',
quote_identifiers=False
) }}
, 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
Raw
group_exercise_dim_player_snapshot.sql
{% snapshot dim_players_snapshot %}
{{
config(
unique_key='player_id',
strategy='check',
check_cols = ['city', 'team_name', 'country_code'],
target_schema = 'mwan_snapshots'
)
}}
select * from {{ ref('dim_players') }}
{% endsnapshot %}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment