Silver model part II

We was analyzing our tables in bronze layer to clean in silver layer, we saw that we have some problem with the tables staff and staff_schedule because we have different id_staff from staff schedule and staff table, also we have people that exits in staff_schedule and didn’t exist in staff  table, in other hand the worker have different services and professions. We prioritize the table staff but if didn’t exits we take data from staff_schedule table additionally we have 2 more tables service and services_weekly, service is a table with all the service of the hospital, and service_weekly  is a table that have all the data about the service and the beds availability by week and months

Analysis of tables

s_staff

In this table we have all the worker data that work in the hospital, their names, their roles and they service that offer to the hospital.

We have the staff table in silver layer like this:

{{ config(alias = "staff")}}

select  distinct

   COALESCE ( s.staff_id, ss.staff_id) staff_id,

   COALESCE ( s.staff_name,ss.staff_name) staff_name,

      COALESCE ( s.role, ss.role) role,

   COALESCE ( s.service,ss.service) service

FROM {{ source('bronze', 'staff') }} s

right JOIN {{ source('bronze', 'staff_schedule') }}  ss

    ON s.staff_name = ss.staff_name

Here we have the select that we use  right join with staff and  staff_schedule,  we have coalesce in all the fields( staff_id, staff_name, role, service) prioritize the data from staff if staff data is null we take the data from staff_schedule. The filed that we join is name because we have a wrong staff_id in staff_schedule.

The file name of this script is s_staff.sql, dbt will take that name to create the table but if we put {{ config(alias = “staff”)}} we will create the table with the name of the alias

s_staff_schedule

The staff_schedule table have the data of the workers like staff id and staff name, role and service, like staff but have 2 more fields week and present, this table show us the absents of the worker in what week they are present or not, we can image that the workers not work full time in this hospital, is like their second job or side job.

{{ config(alias = "staff_schedule")}}

select week,

   COALESCE ( s.staff_id, ss.staff_id) staff_id,

   COALESCE ( s.staff_name,ss.staff_name) staff_name,

      COALESCE ( s.role, ss.role) role,

   COALESCE ( s.service,ss.service) service

,present

from   {{ source('bronze', 'staff_schedule') }} ss left outer join

{{ source('bronze', 'staff') }} s  

 ON s.staff_name = ss.staff_name

Here we also join staff_schedule and staff we use left outer join in this case and use also name to join, like the other table we prioritize the data of staff table, we see that we use coalesce in the fields staff_id, staff_name, role and service but in present and week we don’t use this because we don’t have this field in staff_schedule.

s_service :

The service table we will have all the service that the hospital offer we take it from the table in bronze layer services_weekly

{{ config(alias = "services")}}

select distinct service

from {{ source('bronze', 'services_weekly') }}

we apply a distinct to take only the service of the table services_weekly.

s_ services_weekly :

In this table we have the columns week,”month”,service,available_beds,patients_request,patients_admitted,patients_refused,patient_satisfaction,staff_morale, event, this table show the service by week and moth and the availability of the beds.

{{ config(alias = "services_weekly")}}

with swv as (

select *

from (

select service , event, contar, row_number(*) over(partition by service order by service , contar desc)  rn

from (

select  service , event, count(*) contar  

from {{ source('bronze', 'services_weekly') }}   sw

where event != 'none'

group by service , event

having count(*)>1

order by 1))

where rn =1

)

select week,"month",sw.service,available_beds,patients_request,patients_admitted,patients_refused,patient_satisfaction,staff_morale,

case when sw."event" = 'none' then swv.event else  sw."event"  end

from {{ source('bronze', 'services_weekly') }}   sw left outer join swv 
on sw.service = swv.service

In this query  we replace the value ‘none’ with the most common value in the field event.  We aptly a row number in a window to select the most common event to replace to none value.

Conclusion

We clean data from staff_schedule and service_weekly in the first table we have wrong data in the id staff and the second table we have nulls values.

Silver model part I

How to create DBT Models

How to use dbt seed

How to setup dbt core ?

Dev Container + DBT + Postgresql + Metabase Part 1