Senior Engineer Migration Role: Focused Prep Plan (3–4 hours)
You already have the strongest foundations for this role: Python/Django plus AWS operations. Focus your time on translating that experience into data-migration-specific design, operational judgment, and clear interview narratives.
1. What the interviewers are likely evaluating
They probably do not need you to prove you can write Django models or deploy to AWS. They will be looking for whether you can safely operate in a context where:
- Source data is inconsistent, incomplete, or unexpectedly large.
- Migrations have partial failures and must be safely resumed.
- A bad transformation can affect thousands or millions of records.
- Customers need understandable status, error reports, and rerun procedures.
- Timelines matter, but correctness, auditability, and reversibility matter more.
- You can improve existing tooling pragmatically rather than proposing a rewrite.
Frame your answers around these principles:
- Make work idempotent.
- Validate before mutation.
- Use transactions at the right scope.
- Checkpoint and make retries safe.
- Preserve provenance and produce actionable errors.
- Separate transformation logic from I/O and orchestration.
- Measure and observe every stage.
- Prefer a safe, operable solution over an elegant but risky one.
Suggested Schedule
| Time |
Topic |
Outcome |
| 0:00–0:35 |
Migration architecture and terminology |
Speak fluently about ETL, mappings, validation, checkpoints, idempotency |
| 0:35–1:20 |
Django/Postgres migration implementation |
Be ready for system-design and coding questions |
| 1:20–1:50 |
AWS batch/distributed workflows |
Connect your DevOps background directly to migration operations |
| 1:50–2:25 |
Metadata, XML/CSV, and data-integrity scenarios |
Cover domain-specific gaps |
| 2:25–3:10 |
Prepare behavioral stories |
Have concise examples for ownership, debugging, and stakeholder communication |
| 3:10–3:45 |
Practice questions / mini design exercise |
Rehearse concise, senior-level answers |
If you only have two hours, prioritize sections 1, 2, 3, and 5.
1. Migration Concepts and Vocabulary — 35 minutes
Core migration pipeline
A solid default model:
Extract → Profile → Validate → Transform → Stage → Load → Reconcile → Report
Extract
Read from source systems: CSV/XML exports, APIs, S3 objects, legacy database dumps.
Important concerns:
- Encoding and delimiter issues
- Large files / streaming parsing
- Source versioning
- Immutable source-file retention
- Recording file checksums and source identifiers
Profile
Understand the incoming data before loading it:
- Row counts
- Null rates
- Unique-value counts
- Date ranges
- Invalid controlled-vocabulary values
- Unexpected schema changes
- Duplicates and identifier collisions
A strong phrase:
I would treat source profiling as a first-class migration stage, not just an implementation detail. It lets us identify data-quality problems before we create partial customer state in the destination system.
Validate
Distinguish two validation categories:
- Structural validation: required columns, XML schema shape, parseability, data types, file format.
- Business validation: required metadata, allowed vocabularies, referential integrity, tenant ownership, identifier uniqueness.
Useful distinction:
- Fatal errors: invalid file shape, wrong tenant, incompatible schema version. Do not start the load.
- Record-level errors: missing optional metadata, invalid vocabulary value, malformed date. Quarantine/report the record while allowing valid records to continue, depending on agreed policy.
Transform
Mapping legacy values to new-system representations:
def transform_record(source: dict, mappings: MappingConfig) -> AssetInput:
return AssetInput(
external_id=source["legacy_id"],
title=clean_text(source.get("title")),
creator=normalize_creator(source.get("author")),
resource_type=mappings.resource_types.get(source.get("type")),
metadata=build_metadata(source),
)
Good design traits:
- Pure, testable transformation functions
- Explicit mapping/version configuration
- Preserved original source values where needed
- Clear handling for unknown values
- No hidden database calls in parsing/transformation code unless necessary
Stage
For complex or high-volume work, load normalized source records into a staging table before loading production tables.
Benefits:
- Auditability
- Easier replay/reprocessing
- SQL-based reconciliation
- Separation of source parsing from target writes
- Ability to review failures without reparsing source data
Load
Use bulk operations carefully, while preserving a way to identify which source records correspond to target records.
Reconcile
A migration is not complete when the job says “success.” It is complete when expected results match actual results.
Examples:
Source records: 100,000
Valid after validation: 99,850
Loaded successfully: 99,820
Quarantined: 30
Unexpected failures: 0
Also reconcile:
- Counts by collection / tenant / resource type
- File/object counts
- Relationships and child records
- Checksums where files are copied
- Metadata completeness
- Sampled visual/UI verification
Concepts to be able to define quickly
| Concept |
Interview-ready definition |
| Idempotency |
Running the same migration or batch more than once produces the same final state, without duplicate records or corrupt updates. |
| Checkpointing |
Persisting job and batch progress so work can resume safely after failure. |
| Upsert |
Insert a record if it does not exist; otherwise update it, usually keyed by a stable external identifier and tenant. |
| Reconciliation |
Verifying that source, staged, and target data agree in count and meaningful content after a load. |
| Data provenance |
Recording where a migrated value came from: source system, file, row/path, original identifier, transformation version, and load time. |
| Quarantine / dead-letter |
Isolating records that cannot be processed, along with actionable error context, without necessarily failing an entire migration.↳ Dead Letter Queue |
| Controlled vocabulary |
A governed list of allowed terms, such as resource type, language, rights statement, or subject category. |
| Referential integrity |
Ensuring relationships point to valid records: e.g., assets link to a valid collection and tenant. |
2. Django and PostgreSQL Prep — 45 minutes
Django management command design
They may ask how you would build or improve a migration command.
A good structure:
management command
└── orchestration service
├── source reader
├── validator
├── transformer
├── staging/repository layer
├── batch loader
├── checkpoint repository
└── reporting/metrics
Keep the management command thin:
class Command(BaseCommand):
def add_arguments(self, parser):
parser.add_argument("--migration-id", required=True)
parser.add_argument("--resume", action="store_true")
parser.add_argument("--dry-run", action="store_true")
def handle(self, *args, **options):
service = MigrationService(...)
result = service.run(
migration_id=options["migration_id"],
resume=options["resume"],
dry_run=options["dry_run"],
)
self.stdout.write(self.style.SUCCESS(result.summary()))
Mention useful command capabilities:
--dry-run--limit--resume--from-checkpoint--tenant--input-uri--validate-only--report-path- explicit confirmation for production/destructive runs
Transactions: avoid one giant transaction
A senior answer should call this out:
I generally would not wrap a multi-hour migration in one database transaction. It creates long lock durations, large rollback costs, and operational risk. I would use bounded batches, with each batch committed atomically and checkpointed only after its successful commit.
Pattern:
for batch in batched(records, size=1000):
with transaction.atomic():
load_batch(batch)
save_checkpoint(migration_id, batch.last_source_offset)
Considerations:
- A batch should be small enough to retry safely.
- Checkpoint update must be coordinated with the completed database write.
- External side effects—such as S3 copies or API calls—need separate handling because they cannot be rolled back by PostgreSQL.
For external effects, discuss either:
- an outbox pattern, or
- a persisted per-record state machine such as
PENDING → LOADED → FILE_COPIED → COMPLETE.
bulk_create, bulk_update, and their tradeoffs
Know these points:
bulk_create() reduces round trips and is useful for new rows.- It may not invoke application-level behavior you would get from per-object
.save() workflows; validate model and Django-version-specific behavior before relying on signals/hooks.
bulk_update() is useful for known existing rows but can generate large SQL statements.- Use sensible batch sizes and measure them.
- Bulk writes should not bypass required validation or tenant scoping.
- If parent IDs are required for child objects, load parents first, map IDs, then load children.
PostgreSQL upserts
Typical approach:
INSERT INTO asset (
tenant_id,
external_id,
title,
metadata,
source_updated_at
)
VALUES (...)
ON CONFLICT (tenant_id, external_id)
DO UPDATE SET
title = EXCLUDED.title,
metadata = EXCLUDED.metadata,
source_updated_at = EXCLUDED.source_updated_at
WHERE asset.source_updated_at <= EXCLUDED.source_updated_at;
Points to mention:
- The conflict target requires an appropriate unique constraint, often
(tenant_id, external_id).
- External IDs must be stable and scoped appropriately.
- Decide whether migrations are insert-only, update-only, or synchronizing.
- Avoid silently overwriting newer destination edits.
- Store migration/source version information to make update rules explicit.
JSONB
Be ready to say:
- JSONB is useful for flexible or source-specific metadata.
- It should not replace relational columns for frequently queried or integrity-critical fields.
- Use GIN indexes for containment-style JSONB queries when justified by query patterns.
- Normalize keys and data types during ingestion; otherwise JSONB becomes a consistency trap.
- Preserve original/raw metadata separately when it is valuable for traceability.
Example:
CREATE INDEX asset_metadata_gin
ON asset
USING GIN (metadata jsonb_path_ops);
Common data integrity debugging sequence
If asked, “A migration produced missing or duplicate records. What do you do?”
- Pause or prevent additional runs if continuing may amplify corruption.
- Identify the migration version, input file/version, tenant, batch range, and time window.
- Compare source, staging, target, and error counts.
- Check idempotency/conflict-key behavior.
- Check checkpoint state versus actual committed database state.
- Inspect representative failed and duplicate records.
- Determine whether the issue is transformation, mapping, target constraints, concurrency, or retry behavior.
- Create a targeted repair plan, preferably based on migration IDs and source identifiers.
- Test repair in a non-production-like environment.
- Reconcile again and document rerun steps.
3. AWS and Distributed Workflow Prep — 30 minutes
This is a chance to make your current role highly relevant.
A credible AWS architecture
Customer export uploaded to S3
↓
S3 event notification
↓
SQS queue
↓
Worker orchestration / ECS Fargate task
↓
Validate and profile source
↓
Load staging + process batches into PostgreSQL
↓
Store reports in S3 and migration state in PostgreSQL
↓
Notify operators / update migration UI
Key operational concerns
S3 events are at-least-once
An event can be delivered more than once. Therefore:
- Deduplicate by bucket/key/version ID or object checksum.
- Ensure job creation and processing are idempotent.
- Never assume one event equals exactly one processing attempt.
SQS visibility timeout
For long-running jobs:
- Set an appropriate visibility timeout.
- Extend it while the worker is alive if needed.
- Use a dead-letter queue for repeatedly failing messages.
- Prefer one message that triggers a tracked job rather than trying to process a huge file entirely within an SQS-message lifecycle.
ECS/Fargate
Useful operational points:
- Use task-level CPU/memory appropriate for parsing and batch loading.
- Pass migration ID/configuration via environment variables or task overrides.
- Send structured logs to CloudWatch with migration ID, tenant ID, source file, batch number, and correlation ID.
- Use task exit codes, CloudWatch alarms, and application-level status updates.
- Ensure tasks can be stopped and resumed safely.
Exactly-once is usually not realistic end-to-end
A strong senior answer:
I would not promise exactly-once execution across S3, SQS, ECS, and PostgreSQL. I would design for at-least-once delivery and make individual operations idempotent through stable external IDs, unique constraints, persisted status, and safe retry behavior.↳ Exactly Once vs At Least Once
4. Metadata, XML, CSV, and DAM/Library Domain — 30 minutes
You do not need deep archival-domain expertise. Learn the terms and show an appropriate approach to unfamiliar metadata standards.
Terms worth recognizing
- Dublin Core: a common simple metadata vocabulary: title, creator, subject, description, publisher, date, type, format, identifier, language, rights, etc.
- MODS: richer XML metadata schema often used in library contexts.
- METS: XML wrapper/structural metadata format, sometimes connecting descriptive metadata and files.
- EAD: Encoded Archival Description, XML-based archival finding-aid format.
- IIIF: standard for image delivery and presentation metadata, common in digital collections.
- Controlled vocabularies: standardized terms such as Library of Congress Subject Headings, Getty vocabularies, or internal approved lists.
- Authority control: using stable identifiers for people, organizations, places, and subjects rather than only free text.
- DAM: Digital Asset Management—management of media files plus descriptive, technical, rights, and relationship metadata.
You can say:
I have not necessarily worked directly with every library metadata standard, but I am comfortable approaching schemas as contracts: understanding cardinality, namespaces, identifiers, controlled fields, required fields, and transformation rules. I would preserve raw source metadata, version mapping rules, and make unmapped or lossy transformations visible in reporting.↳ Cardinality
XML processing
For large XML files, avoid loading the entire document into memory:
from lxml import etree
for _, element in etree.iterparse("export.xml", events=("end",), tag="{namespace}record"):
source_record = parse_record(element)
process(source_record)
element.clear()
Mention:
- XML namespaces are a frequent source of bugs.
- Validate against an XSD if supplied and practical.↳ XML XSD?
- Use streaming parsing for large input.
- Protect parsers against unsafe XML features; use a hardened parser configuration where applicable.
- Capture XPath/record identifiers in error reports.
CSV concerns
Know common pitfalls:
- UTF-8 BOM
- inconsistent delimiters
- Excel formatting changes
- quoted newlines
- leading zeros lost in spreadsheet exports
- duplicate headers
- “null” versus blank strings
- locale-specific dates and decimal formats
- fields containing multiple values with undocumented separators
5. Prepare Behavioral Stories — 45 minutes
Use concise STAR format: Situation, Task, Action, Result. Keep each story to roughly 90 seconds.
Prepare at least these five.
1. Complex production incident / partial failure
Use a DevOps incident if needed.
Emphasize:
- How you limited blast radius
- How you correlated logs, metrics, deployment/configuration changes, and data
- How you restored service or safely resumed processing
- What permanent prevention you introduced
Migration framing:
I first establish whether retries are safe. If that is uncertain, I pause automation, capture the current state, and use durable identifiers and counts to determine exactly what completed versus what only appeared to complete.