Skip to content

Instantly share code, notes, and snippets.

@dltm-markwan

dltm-markwan/dbt_snowflake_solutions_ex1.sql

Last active September 30, 2025 15:13
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dltm-markwan/60ba0f4a194cd0d526742a3d20c32485 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dltm-markwan/60ba0f4a194cd0d526742a3d20c32485 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.sql
version: 2
models:
- name: event
- name: stg_event_type
description: ""
columns:
- name: event_type_id
tests:
- unique
- not_null
- name: stg_player
description: "this is a plater"
columns:
- name: player_id
description: " this is a primary key"
tests:
- unique
- not_null
- name: age
tests:
- not_null
- name: player_name
tests:
- not_null
- name: stg_team
description: ""
columns:
- name: team_id
tests:
- unique
- not_null
Raw
dbt_snowflake_solutions_ex3.sql
with player as (
select * from {{ ref('stg_fifa__player') }}
)
, team as (
select * from {{ ref('stg_fifa__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