/covid19retail

Covid19 and Iowa Liquor Sales analysis at BigQuery using dbt, Airflow, Marquez, Google Cloud and other modern data stack tools

Primary LanguageJupyter Notebook

Covid-19/Retail analysis with a modern data stack

Docker Apache Airflow dbt Google Cloud

This projects aims to build a lambda data architecture using services and technologies from a modern data stack. So, some serverless or PaaS services are preferred than other IaaS services. The same for UI data wrangling services if possible instead of code technologies. The following architecture was developed thinking in this principles and possible main technologies:

Tech architecture

Locally, this architecture can run in the following endpoints with Docker containers:

With PaaS or serverless services, the following architecture aims to give more details about the implementation using GCP services, Airbyte Cloud, Astronomer platform + Datakin and DBT cloud services:

Cloud architecture

The silver schema stored in BigQuery is composed of the following tables:

  • iowa_liquor_sales: table extracted from `bigquery-public-data`.`iowa_liquor_sales`.`sales` with information about daily sales of alcooholic products in markets at Iowa, US
  • covid19_open_data: table extracted from `bigquery-public-data`.`covid19_open_data`.`covid19_open_data` with statuses of the Covid19 pandemic also in Iowa, US

The gold schema contains tables build from silver tables in a snowflake schema. The following figure illustrates the relationships between tables at the gold layer:

Data schema

To achieve this schema, the dbt tasks are organized as the following format:

dbt workflow

In Marquez we can see this data lineage visualization:

Marquez data lineage

Finally, the following dashboards were built with Superset:

Sales dashboard

Covid19 dashboard