Streamlined ETL Process: Unleashing Airflow, BigQuery, Looker, Polars, Soda, DuckDB and YData Profiling

Project Summary:

More about this project

Sections

Architecture Overview

Architecture of continuous integration with GitHub Actions

BigQuery table

BigQuery query to create report view and export it to Looker Studio

CREATE OR REPLACEVIEWchicago-cafe-permits.cafe_permits.vw_reportOPTIONS(  description='Report view for Looker Studio dashboard',  labels=[('legal_name','total_permits')],  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL60 DAY))ASSELECT   legal_name,  doing_business_as_name,  street_type,  city,  state,  latitude,  longitude,  issued_date,  expiration_date,  payment_date,  site_number,COUNT(DISTINCT(permit_number))AS total_permits,COUNT(site_number)AS total_sites,  ST_GEOGPOINT(latitude, longitude)AS geo,  CASE     WHEN expiration_date> issued_date THEN'TRUE'    ELSE'FALSE'  ENDAS expiredFROM`chicago-cafe-permits.cafe_permits.cafe_permits`GROUP BY legal_name, doing_business_as_name, street_type, city,state, issued_date, expiration_date, payment_date,site_number, latitude, longitude, expired;SELECT*FROM`chicago-cafe-permits.cafe_permits.vw_report`;

Looker dashboard

Workflow with Airflow

Part 1

Part 2

Part 3

Project structure

├── .devcontainer                        # VS Code development container |   └── devcontainer.json                ├── .github                              # GitHub Actions for continuous integration (CI) |   └── workflows|       └── main.yml                     # GitHub Actions configurations ├── Dockerfile├── LICENSE├── Makefile                             # Makefile with some helpful commands  ├── README.md├── airflow_settings.yaml├── dags│   ├── __init__.py│   ├── etl_chicago_cafe_permits_dag.py│   ├── example_etl.py│   └── utils│       ├── __init__.py│       ├── drop_duplicates.py           # Function to remove duplicates│       ├── drop_full_null_columns.py    # Function to drop columns if all values are null│       ├── drop_full_null_rows.py       # Function to drop rows if all values in a row are null│       ├── drop_missing.py              # Function to drop rows with missing values│       ├── format_url.py                # Function to format the URL│       ├── get_time_period.py           # Function to get the current period│       ├── modify_file_name.py          # Function to create a formatted file name│       └── rename_columns.py            # Function to rename DataFrame columns name├── format.sh                            # Bash script to format code with ruff  ├── include│   ├── data│   │   ├── chicago_sidewalk_cafe_permits_2023_11.csv│   │   └── jobs_nyc_postings_2023_10.csv│   ├── my_local_ducks.db│   ├── my_local_ducks.db.wal│   ├── reports                          # Directory with reports│   │   ├── chicago_comparison_2023_11.html│   │   ├── chicago_raw_profiling_report_2023_11.html│   │   └── chicago_transformed_profiling_report_2023_11.html│   └── soda                             # Directory with SODA files│       ├── check_function.py            # Helpful function for running SODA data quality checks │       ├── checks                       # Directory containing data quality rules YML files│       │   ├── sources│       │   │   └── raw.yml              # Soda data quality check for raw data │       │   └── transform│       │       └── transformed.yml      # Soda data quality check for transformed data │       └── configuration.yml            # Configurations to connect Soda to a data source (DuckDB)├── lint.sh                              # Bash script to format code with ruff  ├── notebooks                            # COLAB notebooks│   └── gov_etl.ipynb├── packages.txt├── plugins├── requirements.txt├── setup_data_folders.sh                # Bash script to create some directories├── source_env_linux.sh                  # Bash script to create a Python virtual environment in linux├── source_env_windows.sh                # Bash script to create a Python virtual environment in windows├── test.sh                              # Bash script to test code with pytest └── tests                                # Diretory for Python test files    ├── __init__.py    ├── dags    │   └── test_dag_example.py    └── utils        ├── __init__.py        ├── test_drop_duplicates.py        ├── test_drop_full_null_columns.py        ├── test_drop_full_null_rows.py        ├── test_drop_missing.py        ├── test_format_url.py        ├── test_modify_file_name.py        ├── test_rename_columns.py        └── test_rename_columns_name.py

Running this project

Prerequisites

Starting the project with the Astro CLI

astro dev start

astro-start

astro dev ps

Creating a new connection to DuckDB

Creating a new connection to Google Cloud