End-to-end Data Engineering with Python DuckDB, Google Cloud, dbt

Part 1: Data Ingestion
Quarto
Python
beautifulsoup
scrapy
scrapingBee
Author
Affiliation

Basil Okola

Master of Statistics and Data Science @ Hasselt University

Published

August 8, 2025

Citation

BibTeX citation:
@online{okola2025,
  author = {Okola, Basil},
  title = {End-to-End {Data} {Engineering} with {Python} {DuckDB,}
    {Google} {Cloud,} Dbt},
  date = {2025-08-08},
  url = {https://bokola.github.io/posts/2025-08-08-data-engineering/},
  langid = {en}
}
For attribution, please cite this work as:
Okola, Basil. 2025. “End-to-End Data Engineering with Python DuckDB, Google Cloud, Dbt.” August 8, 2025. https://bokola.github.io/posts/2025-08-08-data-engineering/.
End-to-end Data Engineering with DuckDB & Python

End-to-end Data Engineering with DuckDB & Python

This is a ‘do it along’ following DuckDB material. We will source data from PyPi, a repository of python packages providing logs of how given libraries have been used across platforms, transform it into relevant tables, and feed it into a dashboard using SQL, dtb - a tool for building modular, maintainable data pipelines to power analytics, and DuckDB - an in-process SQL online analytical processing (OLAPs) relational database management system; then use evidence to create a dashboard utilizing SQL and markdown.

Part 1: Ingestion pipeline

Setup & requirements

  • Python 3.12
  • Poetry for dependency management
  • Make to run Makefile commands
  • A Google Cloud account to fetch the source data. Free tier covers any computing cost.
  • A host of libraries: poetry duckdb google-cloud-bigquery google-auth google-cloud-bigquery-storage pyarrow pandas fire loguru pydantic pytest ruff

The architecture woul look like below

from PIL import Image
from IPython.display import display

img_f = "./docs/architecture.png"
img = Image.open(img_f)
display(img)

Exploring the data

Head to your google cloud console, then under BigQuery search “file_downloads” and click SEARCH ALL PROJECTS.

img_f = "./docs/big_query_search.png"
img = Image.open(img_f)
display(img)

Limit your query to a given timestamp as the data is very big.

img_f = "./docs/query.png"
img = Image.open(img_f)
display(img)

The ingestion part is bundled in /ingestion directory with the following primary files:

!tree ./ingestion
./ingestion

├── bigquery.py

├── duck.py

├── models.py

├── pipeline.py

└── __pycache__

    ├── bigquery.cpython-312.pyc

    └── pipeline.cpython-312.pyc



2 directories, 6 files
  • bigquery.py has BigQuery helpers to get the client, get query results and query the public dataset
  • duck.py contains DuckDB helpers to create a table from a dataframe, and write to a local or MotherDuck database
  • models.py defines table schema
  • pipeline runs the ingestion pipeline use pydantic syntax

The pipeline was successfuly executed and data written locally and to motherduck:

img_f = "./docs/motherduck_db.png"
img = Image.open(img_f)
display(img)