arvindmits / covid19retail

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

Covid-19/Retail analysis with a modern data stack

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:

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

• Airflow: http://localhost:8080
• Marquez: http://localhost:3000
• Superset: http://localhost:8088

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:

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:

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

In Marquez we can see this data lineage visualization:

Finally, the following dashboards were built with Superset: