ADP payroll and time card data, event-driven into Azure SQL.

The challenge

Payroll, headcount and hours lived only inside ADP. Finance and operations wanted them alongside quality and production data in Power BI, refreshed after every pay run, at the grain of a single payroll line item and a single clock punch.

ADP's APIs are powerful but unforgiving: inconsistent JSON shapes, undocumented pagination behaviour, mutual TLS on some endpoints, and a team time card endpoint that returns the same employee once for every manager in their reporting chain.

What we built

• An Azure Function App that handles ADP OAuth for two separate API projects, mutual TLS, pagination, and the defensive parsing ADP's responses require. It exposes clean JSON routes for payroll output, payroll details, workers and team time cards.
• Azure Data Factory pipelines for each dataset, with incremental lookups so only new payroll runs are fetched, upserts keyed on natural business keys, and a two-level orchestration for historical backfills.
• A webhook receiver so that when ADP finishes a payroll run it calls us, and the master pipeline starts on its own. API-key authentication, signature validation and an event log in SQL.
• Duplicate prevention at the database: a persisted SHA-256 row hash and a unique index that silently discards re-inserted rows, so a re-run or a double-fired trigger can never inflate hours or dollars.
• Punch-level time cards: daily totals plus individual clock entries, with ISO durations converted to decimal hours and a periodic backfill for prior pay periods.

What changed

Payroll details, workers and time cards now land in Azure SQL automatically after each pay run and feed the company's Power BI reporting. When the client later flagged hours that looked low for a handful of employees, the row-level audit trail let us trace it to a hash that lacked a per-week discriminator, correct the definition, and reload the affected periods, restoring roughly $366,000 of earnings records across 597 employee pay periods that had been silently dropped by the original de-duplication.

The integration is documented end to end, from ADP app configuration through to the SQL views the reports read, so the client's own team can operate it.