Favoriting means this is a discussion worth sharing. It gets shared to your followers' Disqus feeds, and gives the creator kudos!
COVID-19 Dataset Analysis in the Rakam environment
In an effort to help combat COVID-19, Google has created a COVID-19 public datasets program to make data more accessible to researchers. This data is available in the BigQuery platform. I have chosen to work with the covid19_open_data dataset since it contains more than 500 columns of data, providing information regarding daily COVID-19 trends as well as country-specific information.
Working with such a large dataset is tedious and time-consuming, but dbt (data build tool) and Rakam simplify the procedure. Dbt is an open-source command-line tool that enables data analysts to transform data in their warehouses effectively. Rakam is a BI solution that allows product teams to gain insights directly from their data warehouse. Rakam’s integration with dbt makes it an ideal choice for analysis.
Model development with dbt
After installing dbt, I followed the setting up guide to connect dbt with BigQuery. The created models are primarily written in SQL, with the ‘ref()’ function to reference other data models and create a DAG of models in the dbt project.
The model I developed pulls all columns from the public BigQuery COVID-19 dataset, filtering by ‘aggregation_level’ to retrieve country-level data. By running the dbt run command, I deployed this data as a new view on my BigQuery account.
Setting up the analysis in Rakam
Next, I connected my GitHub repository for dbt files to Rakam using an SSH public key, allowing for easier manipulation of dbt files via Rakam’s editor. I then connected Rakam with the COVID-19 dataset, and I was ready to begin my analysis.
I defined the model’s metrics in the dbt’s schema.yml file. More information on metrics can be found here and the schema.yml documentation can be found here.
COVID-19 Dataset Analysis in the Rakam environment
After defining the metrics, I utilized Rakam’s segmentation feature for analyses.
The picture shows how users can analyze by selecting different measures, dimensions, and filters.
Users can access the SQL code developed for the derived results.
After developing several reports, I added a graph to my dashboard showing the daily trend of confirmed cases, deceased, and recovered patients.
The dashboard allows filtering by specific countries and date ranges for more detailed analysis. Insights included the impact of COVID-19 waves and an increasing number of recoveries, indicating improvements in treatment.
Another analysis involved a new measure called ‘Fully Vaccinated Percentage.’ This was calculated as the proportion of people fully vaccinated against the population, used to compare vaccination policies across countries.
The analysis revealed that countries with lower populations had high vaccination percentages, while countries like Israel, UAE, Chile, and the US showed high vaccination rates despite larger populations.
I also compared COVID-19 lethality by gender, observing greater lethality in male patients.
Further analysis examined the effectiveness of different countries' health systems through the measure ‘Deaths To Confirmed Ratio,’ indicating health system effectiveness in treating COVID-19.
I compared two northern (Sweden and Denmark) and two southern European countries (Bulgaria and Greece). The findings suggested that Bulgaria and Greece, with lower health system investments, faced higher COVID-19 related deaths.
Finally, I explored correlations between smoking and COVID-19 lethality, creating the ‘Covid Mortality Rate’ measure.
Countries with high smoking prevalence, like Greece and Bulgaria, showed higher COVID-19 deaths, but outliers existed, indicating that various factors affect mortality rates.
Additionally, I investigated how effective restrictions were in reducing the COVID-19 death toll using Sweden as a case study.
The graph shows that an increase in restrictions led to a decline in death toll after earlier increases during COVID-19 waves.