Data Pipeline Automation Specialist

• Design and build an end-to-end pipeline: ingest, clean, standardise, transform, report. • Extract only the specific columns and data points needed from each of the 14 datasets. • Implement calculations, transformations and derived columns required for the final report. • Handle inconsistent formats, naming, data types, duplicates, nulls and encoding issues automatically. • Produce a clear exceptions view that flags anything missing, malformed, late or unprocessable. • Make the weekly run repeatable and hands-off (scheduled or triggered on file arrival). Key Responsibilities • Review the 14 source datasets and the current manual process; document the rules and logic. • Define a per-dataset schema and mapping (required columns, types, allowed values). • Build cleaning and validation logic with clear, testable rules per dataset. • Build the transformation layer: calculations, joins across datasets, derived fields. • Generate the final report in the agreed format (e.g. Excel, PDF or dashboard). • Build a data quality and exceptions report: missing files, missing fields, failed rows, reason codes. • Add logging, error handling and alerting (e.g. email or Slack) so failures never pass silently. • Deliver documentation and a handover session so the team can maintain and extend the pipeline. Required Skills • Strong Python (pandas or Polars) and SQL; proven experience cleaning messy, real-world data. • Experience building production ETL/ELT pipelines with validation and error handling. • Schema validation and data quality tooling (e.g. Pydantic, Pandera, Great Expectations). • Automated reporting (Excel/openpyxl, PDF, or BI dashboards). • Scheduling and orchestration (cron, Airflow, Prefect, Dagster, or cloud equivalents). • Clear written communication and the ability to translate business rules into code. Nice to Have • Experience applying LLMs or ML to messy data (fuzzy matching, entity resolution, free-text normalisation), with deterministic fallbacks and human review of low-confidence results. • Cloud experience (AWS, Azure or GCP) and file-drop triggers (S3, SharePoint, SFTP, email ingestion). • Version control, CI and automated testing for data pipelines. Tools and Approach You are free to choose the tools, technologies and AI-based solutions you consider most appropriate. We value reliability, transparency and maintainability over novelty. Please explain your choices and trade-offs. Deliverables • Working, tested pipeline that processes all 14 datasets end to end. • Final report generated automatically each week in the agreed format. • Exceptions and data quality report highlighting anything missing or unprocessable. • Source code in a repository, configuration files, and setup/run documentation. • Handover walkthrough and a short period of post-launch support. Success Criteria • Weekly run completes with no manual cleaning or intervention on normal data. • Output matches the current manual report on a parallel test run. • Every issue is surfaced in the exceptions report; nothing is silently dropped. • Adding or changing a dataset rule is straightforward without rewriting the pipeline.