Remember Data Engineer Assignment — Codex Alternative

# Remember Data Engineer Assignment — Codex Alternative

AI Summary

Purpose:

Preserve durable implementation and verification context for the Codex alternative to the Remember & Company Data Engineer take-home assignment.

Key points:

Standalone repo: Google Drive 내 드라이브/projects/remember-data-engineer-assignment-codex.
Stack: Python 3.11, Airflow 2.10.5, SQLite, Docker Compose.
Two explicit DAGs implement 2022 insert-only initial load and 2023 UPSERT sync.
Actual result: 30,513 initial rows, 2,174 inserts, 21,087 matches, 4,134 normalized payload changes, 32,687 final rows.
Repeating the 2023 snapshot produces zero physical writes and the same master fingerprint.
Test result: 25 passed, 99% total coverage; Airflow import check and three real DagRuns succeeded.
gstack Eng Review status is CLEAR with three findings fixed and zero unresolved decisions.
A cross-Mac handoff is pinned in ai/workspace/active-context.md; tomorrow's task is comparison and final-submission judgment, not implementation recovery.

Relevant when:

Continuing or explaining the Codex assignment implementation.
Comparing the Codex policy choices with the separate Claude implementation (ai/repo-notes/remember-data-engineer-assignment-claude.md).
Re-running the project on macOS Google Drive.

Do not read full document unless:

Exact policy, commands, failure history, or acceptance counts are needed.

Linked documents:

ai/workspace/repos.md
ai/workspace/active-context.md
ai/workspace/machines.md
ai/worklog/2026/2026-W30.md

Open Questions

Confirm with the reviewer whether a blank 2023 field is an authoritative latest value or a missing extraction. This implementation follows the literal latest-value rule and writes NULL. UPDATE 2026-07-26: the Claude alternative now defaults to the same literal rule and offers preservation as an opt-in flag, so this is no longer a difference between the two packages.
Confirm whether 2022-only rows should remain unchanged or be marked inactive. This implementation keeps them unchanged because the assignment specifies only Insert and Update.

Details

Repository

Local path: /Users/khw/Library/CloudStorage/[email protected]/내 드라이브/projects/remember-data-engineer-assignment-codex
Submission ZIP: /Users/khw/Library/CloudStorage/[email protected]/내 드라이브/projects/remember-data-engineer-assignment-codex.zip
Submission ZIP SHA-256: e311290f50b504f4311140618ee272dec390fec6fdb4fd3b96893cad163cf745
Branch: main
Initial commit: c1bb32d
Remote: local-only

Design

The pipeline uses four explicit boundaries:

stage -> validate -> apply -> reconcile

Tables:

pipeline_run: source checksum, state, metrics, final fingerprint.
staging_workplace: normalized run-scoped rows and validation errors.
workplace_master: text business key, latest payload, lineage metadata.
dq_result: hard/soft check outcomes.

Policies:

Business registration number is a 10-character text primary key.
Employee counts normalize 7 and 7.0 to the same integer.
Blank optional 2023 fields become authoritative NULL values.
Rows absent from 2023 remain in the master; no delete or inactive mutation is inferred.
An older sync year cannot overwrite a newer master row.
Initial load is insert-only and cannot revert a later sync.

Verified acceptance counts

Run	Source	Insert	Match	Changed	Unchanged	Physical writes	Final
2022 initial	30,513	30,513	0	0	0	30,513	30,513
2023 sync	23,261	2,174	21,087	4,134	16,953	23,261	32,687
repeated 2023 sync	23,261	0	23,261	0	23,261	0	32,687

Master fingerprint:

03081704061030e941c5ff63e626f8a1e562a0a2e47f891e76402c0dc7030f9b

Commands

make build
make test-coverage
make airflow-import-check
make initial
make sync
make sync-again
make verify
make verify-executed

Review-driven fixes

SQLite WAL failed with disk I/O error on the Google Drive bind mount. Use rollback journal DELETE, synchronous=FULL, and Airflow max_active_runs=1.
An Airflow TaskFlow callable argument named run_id collided with a reserved context key only at task execution. Rename it to pipeline_run_id and assert signatures in DAG tests.
validate_staged_run returned a different dictionary shape on retry. Recompute soft_warning_count from dq_result on the fast path.

Artifact boundaries

runtime/ metadata and logs are ignored and excluded from the submission ZIP.
artifacts/remember_master.executed.sqlite3 is ignored by Git but is included in the Google Drive folder and submission ZIP as execution evidence.
The final DB has PRAGMA integrity_check = ok, 32,687 master rows, three successful runs, and zero other runs.
The submission ZIP was created with UTF-8-compatible macOS ditto, extracted into a fresh temporary directory, and rechecked against the original CSV and DB SHA-256 values.

Cross-Mac resume procedure

The project folder and submission ZIP are already under Google Drive, so no source copy is required. On the next Mac, wait for Drive sync to finish and run:

REMEMBER_REPO=$(find "$HOME/Library/CloudStorage" -maxdepth 4 -type d \
  -name 'remember-data-engineer-assignment-codex' \
  -print -quit)
cd "$REMEMBER_REPO"
git status --short
git log -1 --oneline
git fsck --full
shasum -a 256 ../remember-data-engineer-assignment-codex.zip

Expected results:

git status --short: no output.
latest commit: c1bb32d feat: implement Airflow master synchronization assignment.
ZIP SHA-256: e311290f50b504f4311140618ee272dec390fec6fdb4fd3b96893cad163cf745.

With Docker Desktop running, verify the shipped execution evidence using make verify-executed. If implementation changes are made, run make test-coverage, make airflow-import-check, and the actual DAG sequence before rebuilding the ZIP.

Do not work from the ZIP and do not edit the synced repository concurrently on two Macs. The repo has no remote; Google Drive provides cross-machine transport, while the embedded Git history provides the clean restore point.