Gold layer

Now we finally are in the final stage of our ELT process, in this layer we will see the fables that our dashboards will consume, this layer have tables group fields, dimension tables fact tables. We consume the data from the silver layer that is clean, we have one dashboard and one tab for each fact table, in this post we explain all the the table

Dimensions tables:

g_dim_patients
g_dim_rol
g_dim_service
g_dim_staff

Fact tables

g_patients
g_services_weekly
g_staff_schedule
g_staff

g_dim_patients

In this table we will see all the patients data like name and age

{{ config(alias = "dim_patients")}}
 SELECT 
    patient_id,
    initcap(patient_name) AS patient_name, 
    age
from {{ ref("s_patients")  }} p

we took the data from the table patients of our silver layer

g_dim_rol

this dimension have the roles of the staff

{{ config(alias = "dim_rol")}}
select distinct s.role
from {{ ref("s_staff")  }} s

in the query we took the role of the staff table of our silver layer

g_dim_service

This table have all the service of the hospital we took the data from our silver table

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

select *
from  {{ ref("s_service")  }}

In this query we select all the data from service table of the silver layer

g_dim_staff

This table have the data from doctors, nurses, all the staff of the hospital.

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

select distinct staff_id, staff_name
from  {{ ref("s_staff")  }} s

In this query we took data form staff table of the silver layer. we have a different values of the columns of staff_id and staff_name.

g_patients

In this table we have for every service the average of days that stay in the hospital, also we have the average satisfaction of the patient and the average age of the all the patients of the hospital in addition we have the number of patients.

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

select p.service , round(AVG(p.days_stayed ),0) avg_days_stage, round(AVG(p.satisfaction ),2) avg_satisfaction , round(avg(age),2 ) avg_age, count(distinct p.patient_id )
from {{ ref("s_patients")  }} p
group by p.service

The query group by service, take the data from the table patients of the silver layer, and select the column service and do 3 average for the columns : days_stayed , satisfaction and age also we did a count distinct of the patients id.

g_services_weekly

Here in this table we see how many patients was refused and the low morale of the staff for every service and month.

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

select sw.service, month , sum(sw.patients_refused) , min(staff_morale)
from {{ ref("s_services_weekly")  }} sw 
group by sw.service, month

in this query we group by service and month and we sum the quantity of patients refused also we take the min value of the staff morale in addition we take the data from the service_weekly of the silver layer.

g_staff_schedule

Here in this fact table we control the absenteeism of the hospital’s staff.

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


select ss.staff_id  ,role, service,  sum(case when present = 0 then 1 else 0 end) present,
sum(case when present = 1 then 1 else 0 end) absent
from   {{ ref("s_staff_schedule")  }} ss
group by staff_id ,role, service

The query group by staff_id , role and service and make a sum of the column present if present is equal to one is present y not is absent in addition we take data from staff_schedule of the silver layer

g_staff

The table staff of the golden table show us how many personnel we have by role and service

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

select role, service , count(*)
from  {{ ref("s_staff")  }} s
group by role, service

The query group by role and service and count the personnel in the staff table of the silver layer

Links:

Silver model part II

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