Designed a normalized PostgreSQL schema for orders, customers, products and shipments from an ER diagram, then a star schema on top of it for reporting. Chose the indexes from the actual query log, wrote the data dictionary, and documented the three decisions I would make differently with more volume.
Structured a dbt project with staging, intermediate and mart layers on BigQuery: sources with freshness checks, unique and not-null tests on every key, a slowly-changing seller dimension, and generated docs. The marts feed a revenue dashboard that reconciles to the finance export to the cent.
Orchestrated a daily pipeline: a sensor waits for the partner's CSV drop, a Python task validates and loads it into PostgreSQL, a dbt run rebuilds the marts, and a Slack alert fires on failure with the failing task and row counts. Idempotent, so a rerun of any day is safe.
Python producer sending page-view events to a Kafka topic, a consumer group writing them to PostgreSQL in micro-batches, and a small dashboard reading the last 15 minutes. Handled duplicate deliveries with an idempotency key, and measured end-to-end latency at under two seconds on a laptop.
Soil-moisture and temperature sensors on the family farm, read by a Raspberry Pi, pushed hourly to a PostgreSQL database in Docker and charted in Grafana. Two seasons of data so far. The irrigation schedule now follows the readings instead of my uncle's instincts, which were not bad, to be fair.
| Airflow | BigQuery | dbt | Docker | Kafka | PostgreSQL | Python | SQL | |
|---|---|---|---|---|---|---|---|---|
| Warehouse schema for an online store | ||||||||
| dbt project for marketplace analytics | ||||||||
| Nightly ETL with Airflow | ||||||||
| Clickstream pipeline with Kafka | ||||||||
| Beet farm sensor pipeline |