Migrating 5,689 Employees from Dayforce to UKG Pro for $3.58

# Dayforce allows strictly 100 explicit profiles per minute
chunk_size = 100
...
# 3. Mathematically enforce the 65-second sleep block directly on the queue!
# The first message runs now, the second exactly 65s from now, the third 130s, etc.
timestamp = timestamp_pb2.Timestamp()
timestamp.FromDatetime(now + datetime.timedelta(seconds=65 * total_tasks))
task["schedule_time"] = timestamp

Burning the quota fast, then getting out

Within its 65-second window a Worker has a 100-call budget and every reason to spend it immediately:

# Worker spawns 25 subthreads to instantly process its 100 quota
with ThreadPoolExecutor(max_workers=25) as executor:

Twenty-five threads clear a 100-record batch in roughly 20–30 seconds, leaving 35–45 seconds of headroom inside the window — headroom that is then deliberately spent on a serial retry pass for transient failures, so that a flaky record is recovered within its own batch rather than lost or deferred.

The threads share a single module-global requests.Session:

# Global TCP Session Configuration (Eliminates SNAT Port Exhaustion)
session.mount("https://", HTTPAdapter(pool_connections=30, pool_maxsize=30))

Retry taxonomy: knowing what's worth retrying

Under a hard rate limit, a wasted retry is stolen throughput. So failures are classified rather than blanket-retried:

if reason.startswith("http_5"):            return True
if reason == "http_200_null_data":         return True
if reason.startswith("timeout_"):          return True
if reason.startswith("exception_"):        return True
if reason.startswith("future_exception:"): return True
return False

The docstring states the intent plainly: "Excludes 4xx responses (genuinely bad records) but includes 5xx, timeouts, exceptions, and 200-with-null-Data." A 404 will be a 404 again — retrying it burns quota that a recoverable record needs. Note http_200_null_data: Dayforce will occasionally return HTTP 200 with a null payload. That is a lie, and the pipeline treats it as one.

Rate-limit responses are handled reactively as a second layer, honoring the server's own guidance:

if resp.status_code == 429:
    sleep_time = int(resp.headers.get("Retry-After", 10))
    time.sleep(sleep_time)
    continue

The "god payload"

The single highest-leverage API decision was refusing to fetch narrowly. Every employee is retrieved with a 19-node expansion:

Addresses,Contacts,DirectDeposits,EmploymentStatuses,OrgUnitInfos,
CompensationSummary,PayGradeRates,USFederalTaxes,USStateTaxes,USTaxStatuses,
CANFederalTaxes,CANStateTaxes,CANTaxStatuses,WorkAssignments,
LastActiveManagers,EmployeeProperties,EmergencyContacts,Ethnicities,MaritalStatuses

The US MF1 view alone contains 127 JSON_EXTRACT_SCALAR calls — reading raw JSON and emitting a 106-column, load-ready row.

State tax filing status: 39 regimes, no shared vocabulary

The hardest single problem. US state withholding codes are not standardized — each state invented its own, and Dayforce stores a human-readable description while UKG demands the state's specific code:

(SELECT CASE
  -- === AL: H, M, N, S, W ===
  WHEN sc = 'AL' AND fs = 'Single'                THEN 'S'
  WHEN sc = 'AL' AND fs = 'Married, Self Separate' THEN 'W'
  -- === AZ: Percentage-based codes A-H, Z; Dayforce doesn't capture % election ===
  WHEN sc = 'AZ'                                   THEN 'Z'
  -- === CT: Withholding codes A-F, N, X ===
  WHEN sc = 'CT' AND fs = 'Single'                THEN 'F'
  -- === MS: A, B, C, D, N, X, Y ===
  WHEN sc = 'MS' AND fs = 'Single'                THEN 'A'
  -- === NJ: Rate codes A-E ===
  WHEN sc = 'NJ' AND fs = 'Single'                THEN 'C'
  ...
  WHEN fs LIKE 'Married%'                          THEN 'M'
  ELSE 'S'
END

"Single" maps to S in Alabama, A in Mississippi, C in New Jersey, and F in Connecticut. Arizona collapses to Z because its scheme is percentage-based and the source system does not capture the election at all. 39 distinct state tax codes × 9 Dayforce filing statuses = 103 mappings, each one a small decision with a real consequence for someone's paycheck.

Every mapping is documented twice — once as executable SQL and once as a reviewable CSV carrying a justification column, so a payroll stakeholder could audit the lossy ones without reading SQL:

CASIT,Married,S,"SINGLE/MARRIED 2 or MORE INCOMES",Conservative default; Dayforce does not distinguish one vs two incomes
AZSIT,Single,Z,NO FORM A-4,AZ uses percentage-based codes (A-H); Dayforce does not capture % election
FLSIT,Head of Household,S,SINGLE,FL has no HOH code; default to S

Where information is genuinely lost, the mapping is conservative and the loss is written down. That is the difference between a migration and a data-loss event.

Canada is not "US minus fields"

A separate tax regime with its own vocabulary. Temporary SIN detection: Canadian temporary/non-resident Social Insurance Numbers begin with 9 and carry an expiry date; permanent ones do not. Emitting an expiry for a permanent SIN is a data error, so the view branches on the leading digit:

CASE WHEN LEFT(JSON_EXTRACT_SCALAR(record, '$.SocialSecurityNumber'), 1) = '9'
     THEN FORMAT_DATE('%m/%d/%Y', CAST(SAFE_CAST(JSON_EXTRACT_SCALAR(record, '$.SSNExpiryDate') AS TIMESTAMP) AS DATE))
     ELSE NULL END AS SINExpirationDate,

Canadian EFT decomposition, with a defensive filter that caught real corruption:

-- Canadian EFT: RoutingTransitNumber stores the 5-digit transit alone; BankNumber
-- holds the 3-digit institution code (RBC=003, TD=004, Scotia=002, CIBC=010, BMO=001, ...).
CONCAT('="', LPAD(JSON_EXTRACT_SCALAR(dep, '$.RoutingTransitNumber'), 5, '0'), '"') AS TransitorBranch,
CONCAT('="', LPAD(JSON_EXTRACT_SCALAR(dep, '$.BankNumber'), 3, '0'), '"') AS InstNum,
...
-- Drop rows lacking a Canadian institution code or with non-5-digit transits
-- (US ABA 9-digit routings mixed into CA direct deposits cannot clear through
-- Canadian EFT). Surfaced separately as exceptions for triage.
AND JSON_EXTRACT_SCALAR(dep, '$.BankNumber') IS NOT NULL
AND LENGTH(JSON_EXTRACT_SCALAR(dep, '$.RoutingTransitNumber')) = 5

Canadian direct deposit records in the source system contained US 9-digit ABA routing numbers — payments that would never have cleared. The pipeline refuses to emit them and routes them to a human-readable exception file instead of silently shipping broken banking data.

The details that decide whether a load succeeds

CONCAT('="', LPAD(JSON_EXTRACT_SCALAR(record, '$.XRefCode'), 6, '0'), '"') AS SrcEmpNo,
CONCAT('="', LPAD(JSON_EXTRACT_SCALAR(record, '$.SocialSecurityNumber'), 9, '0'), '"') AS SSN,

CASE
  WHEN UPPER((SELECT ...EmploymentStatus.XRefCode... LIMIT 1)) IN ('ACTIVE','INACTIVE','LOA','PRESTART')
    THEN CAST(NULL AS DATE)
  ELSE CAST(SAFE_CAST(JSON_EXTRACT_SCALAR(record, '$.TerminationDate') AS TIMESTAMP) AS DATE)
END AS TermDate,

1. Mar 3

   First line of code. API probes, auth, schema discovery.

2. Mar 6, 06:08

   First working export produced — 3 days in.

3. Mar 10

   Formal scope: US + Canada action plans, GCP implementation plan.

4. Mar 12

   Full template review with stakeholders.

5. Mar 20

   API investigation complete — every required field located.

6. Mar 23 → Apr 10

   Six further production iterations.

7. Apr 6

   Delta isolation pipeline designed and shipped.

8. Apr 10, 16:00

   Source system cutoff.

9. Apr 10, 16:44:55

   Final production extract — 5,689 employees, all templates.

Yeah, if I've got a translation table, then it's super fast. I mean, it's negligible — no work for me for all practical purposes.

The number that matters most, though, is the one about what was otherwise achievable at all:

It is a good thing I built this tool because pulling this information on four eleven as of four eleven and providing it same day, would be literally impossible without the tool. I mean, just one hundred percent out of the question.

That is the real claim, and it is a modest one. Not that the pipeline made a manual process faster — that it made a different process possible. Same-day delivery of a fully-transformed, 191-column, two-country, load-ready dataset at the exact moment of a hard cutoff is not a thing you do by hand at any speed.