How mjeisenh/trackman-data-pipeline works
Project Type and Purpose
• Type: A data ingestion pipeline (ETL) built in Python.
• Purpose:
• Securely connect via FTPS to a remote Trackman server
• Download previous-day CSV folders (YYYY/MM/DD)
• Clean and transform the CSVs (add IDs, convert types)
• Batch-upload to an Azure SQL database
• Run automatically daily via cron
Main Components
a. External FTPS Server (Trackman)
b. Scheduling Layer (cron on a host/VM)
• Scripts/csv_to_db.py
• Scripts/ftp_csv_downloader.py
d. Local Storage (optional intermediate folder for CSVs & logs)
e. Azure SQL Database
f. Logging Output (timestamped log files)
Relationships and Data Flows
• cron → (at 06:30) → ftp_csv_downloader.py
• ftp_csv_downloader.py → FTPS Server:
• Authenticate via FTPS → Download yesterday’s folder of CSVs → Save to Local Storage
• cron → (at 06:45) → csv_to_db.py
• csv_to_db.py → Local Storage:
• Read CSVs → Clean/transform data (SQLAlchemy + ODBC Driver) → Batch‐insert into Azure SQL DB
• Both scripts → Logging subsystem → Write timestamped log files
Architectural Patterns and Design Principles
• Modular ETL: Separate “extract” (downloader) from “load” (db uploader).
• Idempotency: Scripts operate on date partitions; can be re-run safely.
• Externalized Configuration: .env.example for credentials + SQL connection strings.
• Scheduling as infrastructure (cron).
• Error handling & logging: Each script logs progress & errors.
• The flow from remote FTPS to local host
• The separation of concerns between downloader and uploader
• The interaction with the Azure SQL database
• The role of scheduling and logging
This level of detail ensures any new team member or stakeholder can quickly grasp how data moves through your pipeline, where points of failure may occur, and which technologies are in use.