Ingestion DAG: MySQL to MinIO

The first task extracts rows from MySQL and writes them to MinIO as parquet, partitioned by the ingestion date. That date partition is what makes backfills cheap and idempotent. Rerunning yesterday only overwrites yesterday.

airflow/dags/batch_ingestion_dag.py
python
import aiomysql
import pyarrow as pa
import pyarrow.parquet as pq
import asyncio
from pathlib import Path


async def _extract(run_date: str) -> int:
    conn = await aiomysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PWD, db=MYSQL_DB)
    try:
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute(
                'SELECT * FROM orders WHERE DATE(updated_at) = %s', (run_date,)
            )
            rows = await cur.fetchall()
    finally:
        conn.close()
    table = pa.Table.from_pylist(rows)
    out = Path(f's3a://raw-data/orders/dt={run_date}/part-0.parquet')
    pq.write_table(table, out.as_posix())
    return len(rows)


def run_extract(**ctx):
    run_date = ctx['ds']
    count = asyncio.run(_extract(run_date))
    print(f'extracted {count} rows for {run_date}')

A minimal extract that uses aiomysql to query the source and pyarrow to write parquet. The run_date context variable comes from Airflow, so backfills hit the right partition automatically.

Notice the dt=run_date folder. That is a Hive-style partition. Spark and DuckDB both read it as a virtual column. If a day is wrong, you delete that folder and rerun that DAG run with catchup. Nothing else is touched. This is the simplest, most durable backfill strategy you will find.

Ingestion task flow

MySQL to MinIO under a date partition. Airflow owns the schedule, the task owns the idempotent write.

Partition-aligned writes make every run idempotent and every backfill targeted.

Because reading the whole table grows with time and eventually outlasts the hour budget. Filtering to the partition you are about to write keeps every run bounded. Make sure updated_at has an index, and make sure rows that update late still land in a later run. The naive full-load works for a few months and then quietly stops.

Validation checklist: Verify your extract task

Loading practice…

Quiz: Quiz

Loading practice…