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.
2. Improving a brittle process
Tell a story about replacing manual operations, improving CI/CD, automating validation, or adding observability.↳ Virtual Desktop Rescue
Translate it to migration work:
- repeatable tooling
- preflight validation
- deterministic runbooks
- operator-friendly outputs
- reduced human error
3. Working with ambiguous requirements
Show that you uncover the real contract:
- Who owns source data quality?
- Which fields are required?
- What can be dropped, transformed, or defaulted?
- Is the migration one-time, incremental, or bidirectional?
- What is the acceptance/reconciliation criterion?
- What is the rollback or repair strategy?
4. Code review / quality disagreement
Demonstrate practical judgment, not dogmatism:
- Explain risk concretely.
- Offer a smaller safe alternative.
- Align with timeline and team conventions.
- Add tests, logging, or follow-up work rather than blocking unnecessarily.↳ Code Review / Quality Disagreement
5. Learning an unfamiliar domain quickly
This is ideal for the metadata/library-domain preference.
Structure:
- how you read existing code and docs
- how you identify data contracts and invariants
- how you validate assumptions with domain experts
- how you turn discoveries into tests and documentation↳ Fast Learner
6. Practice Interview Questions
System design
“Design a migration process for customer CSV/XML data into a multi-tenant Django application.”
Cover:
- Source intake and immutable storage in S3
- Migration job record with tenant, mapping version, source checksum, status
- Preflight validation and profiling
- Staging records / error records
- Batch transformation and loading
- Unique keys and tenant scoping
- Per-batch transactions and checkpoints
- Idempotency and retry behavior
- SQS/ECS orchestration
- Metrics, reporting, reconciliation, and operator workflow
“How would you rerun a migration after a job fails halfway through?”
Answer:
- Do not rerun blindly.
- Inspect job status, checkpoint, and completed batch state.
- Confirm writes are idempotent.
- Resume from the last durable checkpoint, or reprocess all source records safely if upserts make that acceptable.
- Reconcile afterward.
- If code/mappings changed, record a new migration version and make the repair path explicit.
“How would you prevent data from one customer appearing in another customer’s account?”
Mention defense in depth:
- Tenant ID is required in every migration context.
- Tenant is derived from trusted job configuration, not source-file values alone.
- Composite unique constraints include tenant scope where appropriate.
- Querysets/repositories are tenant-scoped by default.
- Validate all referenced IDs belong to the same tenant.
- Test cross-tenant negative cases.
- Include tenant ID in logs, metrics, and reports.
Django/Postgres
“When would you use bulk_create?”
For large inserts where model-level save behavior is not needed or has been explicitly accounted for.↳ Why Override .save() Use batches, validate first, and reconcile after. Be mindful of database constraints, IDs needed for child records, and behavior skipped by bulk operations.
“How do you decide transaction boundaries?”
Use a bounded, retryable batch as the normal transaction scope. Avoid transactions spanning the entire migration or long external operations. Commit the data and checkpoint together where possible.
“How do you handle duplicate records?”
Define a stable business key—often tenant plus source external ID—enforce it in the database, and choose explicit upsert/update policy. Duplicates that represent source ambiguity should appear in a validation report rather than being silently merged.
Debugging
“The job reports success, but a customer says 200 assets are missing.”
Start with reconciliation rather than assumptions:
- Compare source and target counts by collection/type/status.
- Check validation/quarantine report.
- Determine whether assets were loaded but hidden due to permissions/indexing/UI filters.
- Look at migration job logs and batch metrics.
- Sample source identifiers and trace them through staging and target tables.
- Identify systemic mapping/filtering behavior versus isolated data issues.
- Create and verify a targeted repair run.
7. A 20-Minute Mini Exercise
Practice describing or sketching this design:
A customer uploads a 500,000-record CSV to S3. Each row represents an asset with metadata, a collection identifier, and a legacy asset ID. Some records have invalid collection IDs or unsupported resource types. The import may run for hours and can be interrupted.
Your answer should include:
- S3 object version/checksum stored in a
MigrationJob
- Tenant and mapping configuration stored separately from input
- Initial validation/profiling task
- Streaming CSV parser
- Stable per-row identifier / source row number
- Staging/error table or durable error report
- Batches of perhaps 500–5,000, tuned empirically
- Atomic DB transaction per batch
- PostgreSQL uniqueness constraint on
(tenant_id, legacy_asset_id)
- Checkpoint only after batch commit
- Record-level errors captured with source row, field, value, and remediation message
- ECS task/task status and structured CloudWatch logs
- SQS retry/DLQ behavior
- Reconciliation report at completion
- A clear “resume” operation
A concise summary answer:
I would create a durable migration job tied to a tenant, input object version, checksum, and mapping version. I would validate and profile the source before loading. The worker would stream the file, process bounded batches, and use a transaction per batch. Target writes would be idempotent through a tenant-scoped external-ID constraint and explicit upsert policy. I would persist checkpoints only after successful commits, capture record-level failures in an operator-readable report, and reconcile source, valid, loaded, and failed counts at the end. The orchestration would assume at-least-once execution, so both job creation and record loading must be safe to retry.
Final 10-Minute Interview Cheat Sheet
Keep these phrases handy:
- “I design migration operations to be idempotent, checkpointed, observable, and reconcilable.”
- “I distinguish fatal file-level validation failures from record-level data quality errors.”
- “For long-running loads, I prefer bounded transactions per batch, rather than one transaction for the entire migration.”
- “A successful task exit is not enough; I need reconciliation against source and expected business outcomes.”
- “In distributed systems, I assume at-least-once delivery and make writes safe to retry.”
- “For multi-tenant systems, tenant isolation needs to be enforced in configuration, application queries, constraints, validation, and tests.”
- “I preserve source provenance and mapping versions so that errors and reruns are explainable.”
- “I would start by understanding the existing migration tooling and improve the highest-risk or most operator-painful parts first.”
Most importantly, position your background positively:
My Django experience gives me confidence in the application and persistence layer, and my AWS/DevOps experience means I naturally think about failure modes, observability, safe retries, operational runbooks, and how a batch process behaves under real production conditions. For migration work, I would apply those strengths to make data loads predictable and safe for both operators and customers.