Data Engineering

Reduced time by 75% and cost by 90%.

Main project dashboard |full

Local Government Municipality Faces a Significant Challenge in Transforming Data from PDF Files into Machine-Readable Formats

Challenge

A Local Government Municipality was experiencing significant delays and errors in processing payroll data due to manual extraction and quality checks from PDF files. These inefficiencies not only slowed down operations but also hindered timely financial analysis and decision-making, creating bottlenecks for effective governance.

My Approach

To address these challenges, I developed a streamlined, automated pipeline for payroll data extraction and verification from PDF files. This solution involved:

  • Secure Storage in AWS S3 Automated scripts ingested PDFs into AWS S3, ensuring secure and scalable access for processing.

  • Conversion and Text Extraction Using AWS Textract Each PDF page was converted into an image, allowing AWS Textract to extract text with high precision, significantly reducing manual workloads.

  • Robust Quality Checks Implemented thorough quality checks throughout the pipeline to identify and correct data inconsistencies. Rows with unresolved errors were flagged for manual review, with targeted screenshots generated for closer inspection.

  • Leveraging Advanced LLMs Used advanced large language models (LLMs) to extract high-precision text from flagged screenshots, further enhancing data accuracy and reducing manual intervention.

  • Staging Environment for Analysis After applying corrections, the clean dataset underwent a final round of quality checks before being loaded into a staging environment, ensuring readiness for in-depth analysis.

Results

The automation significantly reduced processing time and errors, enabling staff to focus on more strategic tasks. Key outcomes included:

  • Time Savings: Reduced processing time by 75%.
  • Cost Reduction: Lowered operational costs by 90%.
  • Improved Data Reliability: Provided an accurate, machine-readable dataset for financial analysis, boosting operational efficiency and enabling informed decision-making.

Future Plans

The clean, machine-readable data will serve as the foundation for implementing a text-to-SQL AI agent. This system will enable advanced financial analysis of payroll data, offering deeper insights and supporting better governance.

Technologies Used

  • AWS S3: For secure storage and scalable access to PDF files.
  • AWS Textract: For extracting text from PDF pages.
  • LLMs: To enhance text extraction and resolve flagged errors.
  • Python: For scripting and automating the data pipeline.