Data Transformation Pipeline with dbt

● OVERVIEW

This project demonstrates how a traditionally manual business process can be transformed into a structured, automated workflow using VBA and efficient Excel-based data processing techniques.

The solution centralizes payroll processing and preparation into three core layers:

• Data Input layer: imports time-tracking data (clock-in / clock-out records).

• Processing & Validation Layer: calculates hours worked and payments per entry, and aggregates results per employee, providing a clear summary table for review and validation.

• Payment Export Layer: generates a simplified payment table and exports results to CSV for integration with payroll software.

● KEY FUNCTIONALITIES

• Import monthly time-clock data into the working file

• Search workers by ID, first name, last name, or role

• Select workers from search results and view their details

• View all time entries related to a selected worker

• Add, update, or delete worker records and time entries

• Calculate regular hours, overtime hours, and payment amounts

• Generate an employee-level payroll summary

• Generate a payment export file in CSV format

● HOW IT WORKS

➜ Data Processing. The tool uses arrays to load and process larger datasets efficiently. Calculations are performed in memory before results are written back to the workbook, reducing unnecessary cell-by-cell operations.

➜ Modular VBA Structure. The VBA logic is organized into reusable components:

• Config module: stores column and row indexes and constants to centralize layout changes and shared parameters.

• Helper functions: contain the reusable logic such as ID lookup and repeated utility tasks.

• Core procedures: handle importing, searching, calculating, updating, deleting, refreshing views, and exporting payment data, organized into separate modules and procedures.

➜ Search and Filtering. The tool uses Advanced Filter logic driven by the selected search field and user input. It supports text-based searches with wildcards and exact-match numeric searches for worker IDs.

➜ User Interface. The workbook includes conditional formatting, data validation, navigation links, and structured input areas to create a smoother user experience inside Excel.

● DESIGN PRINCIPLES

• Separation of concerns: Data import, search, calculation, validation, updates, and payment export are handled separately.

• Scalability: The solution is designed to handle thousands of time entries using array-based processing.

• Traceability: Each payment can be traced back to individual time entries.

• Maintainability: Shared constants and helper functions reduce hardcoded references and make layout changes easier to manage.