Data Systems, Cloud & Platform Engineering

1. PostgreSQL: begin with invariants and access paths

A senior data answer starts with what must remain true under concurrency. Tables, indexes, and transactions are mechanisms for those invariants—not independent checklist items.

Model stable facts, preserve uncertain input

Normalize entities that have independent identity and lifecycle: tenant, source, document, document version, processing run, chunk, and access grant. Use foreign keys and unique constraints for truths the database can enforce. Keep the immutable source object in object storage and a raw metadata reference or carefully bounded JSONB column for provider-specific fields. Promoting every uncertain field into a column creates migration churn; placing every stable relationship in JSON discards relational guarantees and makes query behavior harder to predict.

document(
  tenant_id, document_id, source_id, external_id,
  current_version_id, lifecycle_state, created_at, updated_at,
  UNIQUE (tenant_id, source_id, external_id)
)

document_version(
  tenant_id, version_id, document_id, content_hash,
  object_uri, parser_version, source_modified_at, status,
  UNIQUE (tenant_id, document_id, content_hash)
)

Put tenant_id in ownership and uniqueness keys, not only in a nullable filter. Row-level security can add defense in depth: when enabled, normal access must be allowed by a policy, as the current PostgreSQL row-security documentation explains. Still test connection-pool session state, roles that bypass RLS, background jobs, migrations, and administrative access. RLS is not a substitute for explicit tenant-aware application APIs.

Index for a concrete query

“Add an index” is not a diagnosis. Capture the representative query and parameters, table/index sizes, data distribution, concurrency, cache state, and latency percentiles. Use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) in a safe environment: ANALYZE executes the query, including writes. Compare estimated versus actual rows at each node, loops, scan type, join algorithm, sorts/spills, heap fetches, and shared reads/hits. PostgreSQL’s official guide emphasizes that a plan is a tree and estimates depend on statistics: Using EXPLAIN.

Transactions, locks, and pools

PostgreSQL defaults to Read Committed, where each statement sees a snapshot at statement start. Repeatable Read provides a stable transaction view; Serializable detects executions that cannot be ordered safely and requires the application to retry serialization failures. The exact behavior is documented in Transaction Isolation. Choose by invariant, keep transactions short, update resources in a consistent order, inspect lock waits, and make retries idempotent. A deadlock victim is expected safety behavior, not evidence the database is broken.

Connection pools protect a finite database resource. Size them from database capacity across all replicas/workers, not from request count. Long model calls must not hold an open transaction or idle connection. Use a pool acquisition timeout, statement timeout, transaction timeout, and metrics for active, idle, waiting, and age. Pool exhaustion can look like a slow query even when no query has started.

2. Build ingestion as a replayable ledger

Messy enterprise data means malformed files, duplicate exports, password-protected PDFs, changing parsers, conflicting encodings, and tables whose visual layout carries meaning. A trustworthy pipeline never makes the only copy of the input its latest derived output.

Stages and contracts

1. Discover: enumerate source objects with a stable source ID, version/etag, modified time, and permission snapshot. Do not assume directory listing order is a checkpoint.
2. Land: stream bytes to immutable object storage while computing size and cryptographic content hash. Enforce file and decompression limits before expensive parsing.
3. Validate: detect actual media type, malware policy result, encryption, structural damage, and tenant/source authorization. Quarantine with a reason; do not silently skip.
4. Extract: select a versioned parser by media type. Preserve page, sheet, cell, bounding box, and OCR confidence so answers can cite the source.
5. Normalize: produce a canonical document representation. Keep extraction warnings and unknowns; do not fabricate clean text from missing data.
6. Publish: atomically point the document at a complete index generation only after validation. Old generation remains available for rollback.

ETL transforms before loading into the analytical destination and is useful when strict sanitization or a stable target schema is required. ELT lands raw data first and transforms in a capable data platform, improving reprocessing and exploratory flexibility. A robust document pipeline often combines them: immutable landing is ELT-like; security validation before broad availability is ETL-like.

Batch, stream, checkpoints, and deduplication

Streaming lowers freshness but increases state and operational complexity. Batch improves throughput and gives clean processing boundaries. Use events for fast discovery and a scheduled inventory for correctness. A checkpoint must name a durable completed boundary: source cursor plus tie-breaker, manifest ID, input partition, and pipeline version. Commit it only after all outputs for that boundary are durable.

Deduplicate at several levels: source object ID/version prevents repeat discovery, content hash detects identical bytes across exports, and deterministic derived IDs prevent duplicate chunks. Whether two tenants may share physical bytes is a security and encryption decision; logical documents and access controls remain separate. Do not use fuzzy text similarity as the only identity rule.

Lineage and data-quality gates

For every indexed chunk, answer: which tenant, source object, source version, byte hash, parser/OCR version, normalization version, page/region, chunker configuration, embedding model/version, processing run, and access-control snapshot produced it? That lineage makes a targeted replay possible when a parser defect affects only scanned PDFs.

Quality gates should distinguish hard failures from warnings: empty extraction, implausible page counts, unreadable percentage, duplicated pages, corrupted tables, missing mandatory columns, OCR confidence distribution, and access-control mismatch. Route ambiguous cases to human review with the original rendering and extracted representation side by side.

3. Containers and Kubernetes: make runtime intent explicit

Kubernetes can restart and route around failures only when the application exposes truthful health and resource behavior. Deployment YAML cannot compensate for an endpoint that lies.

Build a small, reproducible image

Use a multi-stage build so compilers and build caches do not enter the runtime image; Docker’s official guide shows how stages selectively copy artifacts: multi-stage builds. Pin base images by an intentional version or digest, run as a non-root user, use a read-only filesystem where possible, exclude secrets and build context, emit an SBOM/signature in the supply-chain workflow, and scan both dependencies and the final image. Rebuild images for patches; do not mutate running containers.

# Illustrative structure; pin reviewed versions/digests in a real build.
FROM python:3.13-slim AS build
WORKDIR /build
COPY pyproject.toml uv.lock ./
RUN ... build a locked wheelhouse ...

FROM python:3.13-slim AS runtime
RUN useradd --system --uid 10001 app
COPY --from=build /build/wheels /wheels
RUN ... install only locked runtime wheels ...
USER 10001
CMD ["python", "-m", "service"]

Three probes, three questions

• Startup: has this slow-starting process initialized enough for other probes to begin?
• Readiness: should this pod receive new traffic now? Overload or loss of a mandatory local capability may make it unready.
• Liveness: is the process irrecoverably stuck such that restart is likely to help?

Kubernetes suppresses readiness/liveness until a configured startup probe succeeds, and a failed readiness probe removes the pod from service endpoints. Its documentation also warns that bad liveness probes can cause cascading restarts under load: probe guidance. Do not make liveness depend on every remote model provider; restarting healthy pods during a provider outage increases damage.

Resources, scaling, and rollout

CPU requests influence scheduling and CPU limits can throttle; memory limits can end in OOM termination. Start from load-test profiles for API, parser, and worker separately. Observe working set, CPU throttling, garbage collection, queue age, and latency. Keep headroom for bursts and node disruption. A HorizontalPodAutoscaler adjusts replicas from observed metrics, but scaling on CPU alone can fail for I/O-bound queue workers; queue age or outstanding work per ready worker is often closer to user pain. See Kubernetes autoscaling concepts.

A rolling deployment needs enough surge capacity, readiness that reflects warm-up, termination grace, request/lease draining, and a disruption budget. Canary releases route a small, observable cohort to the new parser/model/service and compare errors, latency, cost, and quality before expansion. Blue-green gives a clean environment switch and fast rollback but doubles capacity during transition and does not magically reverse database migrations. Prefer expand/migrate/contract schemas and backward-compatible readers.

4. Cloud platform design: identity, network, delivery, and recovery

A reference architecture should explain traffic and trust, not display a cloud catalog. Trace both the data plane and the control plane.

One request from DNS to data

DNS resolves the service name; TLS authenticates the endpoint and encrypts transport; a load balancer or ingress applies routing and connection policy; the service authenticates the caller; workload identity authorizes narrowly scoped calls to database, object storage, queue, secret manager, and model provider. Network policy and private endpoints reduce paths but do not replace identity checks. Timeouts must descend: client deadline > ingress > service subcalls, leaving time to return a useful error.

Prefer short-lived workload identity to static cloud keys. Separate deployer identity from runtime identity. A worker reading source objects need not mutate infrastructure; an API serving job status need not decrypt every source credential. Centralize secrets in a managed store, rotate them, and record access without printing values.

Infrastructure as code and CI/CD

Terraform describes desired resources and records bindings in state. State can contain sensitive information and must be shared and locked safely; HashiCorp recommends remote state for team use and warns against insecure version-control storage: Terraform state documentation. Pin provider/module versions, review a saved plan, use separate state blast radii, run policy/security tests, and avoid broad production credentials in pull-request jobs.

1. Build once; produce immutable image digest, tests, SBOM, vulnerability result, and provenance.
2. Plan infrastructure and schema changes; require review for destructive or privilege-expanding operations.
3. Deploy to a representative pre-production environment; run smoke, contract, migration, and rollback tests.
4. Canary by environment, tenant cohort, or traffic; automatically pause on user-facing SLO and correctness signals.
5. Promote the same artifact, then verify and retain evidence. Rollback or roll forward through an exercised procedure.

Public cloud favors managed-service velocity and elastic capacity; private or customer-hosted deployments may be required for data residency, network control, or procurement policy, but increase version skew, upgrade, capacity, and support burdens. Hybrid design needs an explicit connectivity failure mode and a support boundary.

Backups are not recovery

Define recovery point objective (maximum acceptable data loss) and recovery time objective (maximum acceptable restoration time) by component. Test database point-in-time recovery, object versioning, queue redrive, index rebuild from canonical data, infrastructure recreation, identity/secrets restoration, and DNS failover. Record actual drill times. A replica can copy corruption; a backup can be unusable; an index may be cheaper and safer to rebuild than back up.

5. Worked system: multi-tenant document processing platform

This hypothetical example turns the prior decisions into a system-design narrative. The numbers are illustrative sizing assumptions, not production results.

Estimate before selecting capacity

Assume 100 tenants, 50,000 documents each, four pages per document: 20 million pages initially. If 0.5% change daily, steady state is about 100,000 pages/day, but an onboarding backfill may be 50 times the average. If OCR consumes an example 1.5 CPU-seconds/page, the daily steady-state CPU work is about 42 CPU-hours; a 10-hour processing objective needs roughly 4.2 continuously busy cores before concurrency inefficiency, retries, and headroom. Benchmark the real corpus before committing.

Store original bytes and manifests in object storage, canonical metadata/checkpoints in PostgreSQL, and tasks in a durable queue. Separate fetch, virus/format validation, OCR/extraction, normalization/chunking, embedding, and index-publish workers so each scales and retries independently. Use deterministic artifact keys and an index generation pointer. Interactive query traffic runs in a different deployment and resource pool from ingestion.

Capacity and cost model

Protect fairness with per-tenant quotas and weighted scheduling. Large backfills consume a separate budget and can pause when interactive latency or database saturation rises. Operational dashboards join queue age, stage throughput, success/warning/quarantine rates, CPU/memory, database waits, provider usage, cost per usable page, and freshness by tenant.

Deploy in one region first if requirements allow, using multi-zone managed services. Keep raw and canonical data sufficient to rebuild derived indexes. For regional disaster recovery, choose active-passive unless the recovery objective justifies active-active data consistency and operational complexity. Explain how tenant data residency changes placement and how the control plane routes a tenant to the correct deployment stamp.

Syllabus checkpoint: database, Kubernetes, Linux, and cloud breadth

PostgreSQL beyond one slow query

Schema design starts from invariants, update patterns, and query grain; normalization reduces contradictory facts, while deliberate denormalization needs an ownership and refresh rule. PostgreSQL full-text search uses document/query representations, dictionaries, ranking, and indexes and can complement pgvector for hybrid retrieval. Test migrations forward and backward against production-like data, and test backups by restoring them to a clean environment.

Configuration and delivery on Kubernetes

ConfigMaps hold non-secret configuration; Secrets are transport/storage objects whose encryption, RBAC, rotation, and workload delivery still need design. Prefer workload identity to long-lived cloud keys. Helm packages parameterized Kubernetes resources; GitOps reconciles declared state through reviewed changes. Neither replaces readiness tests, safe database migration order, canary or blue-green analysis, termination/draining, nor a verified rollback.

Linux and networking diagnosis

Trace a request through DNS resolution, TCP connection, TLS handshake, proxy/load balancer, service routing, application, and downstream dependency. On Linux, inspect process state, sockets, CPU, memory, disk and inode pressure, file descriptors, cgroups, logs, and permissions before changing configuration. Distinguish connection timeout, refusal, reset, TLS verification, and application timeout; they imply different layers and owners.

AWS and GCP mapping

Map requirements to durable primitives before provider names: identity/IAM, network boundary, compute, object storage, queue/event service, managed PostgreSQL, Kubernetes/serverless, secrets/KMS, monitoring, and audit. AWS and GCP differ in service mechanics and defaults, so validate the chosen managed service’s quotas, availability model, backup/restore, private networking, egress, and pricing. Terraform should produce reviewed, repeatable state with remote locking, least-privilege credentials, drift detection, and a recovery plan for state—not merely create resources.

Interview playbook

Use QUERY → PIPELINE → PLATFORM:

1. Query: identify invariants, access patterns, scale, distribution, and transaction boundary; sketch keys before indexes.
2. Pipeline: show immutable input, versioned stages, idempotent IDs, checkpoint, quarantine, lineage, and replay.
3. Platform: trace network and identity, stateful dependencies, probes/resources, scaling metric, rollout/rollback, recovery, and cost.

For a slow-query question, ask for plan and data distribution before proposing an index. For Kubernetes, do not confuse liveness with readiness or autoscaling with capacity. For cloud architecture, name RPO/RTO and trust boundaries. State illustrative numbers as assumptions, show the arithmetic, and explain what benchmark would replace them.

Common traps include using JSONB for every field, running EXPLAIN ANALYZE on a dangerous production write, holding a database transaction across OCR/model calls, committing a checkpoint before outputs, making liveness depend on a remote provider, setting resources by guesswork, scaling workers until the database fails, and claiming a backup without a restore test.

Question bank

Answer with one concrete workload and measurable verification.

Proof artifact: operable document platform

Build a local or low-cost reference deployment. Any thresholds are example lab objectives, not claims about past work.

1. Generate a synthetic tenant-safe corpus containing clean text PDFs, scans, malformed PDFs, CSV/Excel edge cases, duplicates, deletes, and deliberate cross-tenant IDs.
2. Implement immutable landing, manifest/checkpoint tables, versioned extraction, quarantine, deterministic chunk IDs, lineage, and a mock index-generation switch.
3. Create one deliberately slow PostgreSQL workload. Save schema, data generator, query, EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), change hypothesis, new plan, and load-test comparison.
4. Containerize stages with a multi-stage non-root image. Deploy API and workers to a local Kubernetes cluster with startup/readiness/liveness, requests/limits, HPA or event-based scaling, graceful shutdown, and a rollback command.
5. Describe infrastructure in Terraform for a disposable environment or use a safe mock plan; keep state outside version control and document identity boundaries.

Measure: usable pages per minute, p95 stage latency, queue oldest age, quarantine/warning rate, checkpoint recovery time, duplicate suppression, database plan rows/buffers/time, CPU throttling, memory peak/OOM, rollout error rate, estimated cost per 1,000 usable pages, and restore/rebuild time.

Inject failures: kill a worker between artifact write and checkpoint, corrupt a file, force OCR timeout, exhaust the database pool, introduce one tenant with a huge backfill, fail readiness, OOM a parser under a limit, interrupt a rollout, and rebuild the index from canonical state. Prove that a poison document does not block its partition and a tenant cannot read another tenant’s manifest.

Present: a scale worksheet, data/lineage diagram, before/after plan with reasoning, Kubernetes manifest excerpt, one failure timeline, recovery evidence, cost sensitivity chart, and an architecture decision record for batch versus streaming or shared versus deployment-stamp isolation.

Chapter review

An operable AI data platform preserves raw truth, makes transformations versioned and replayable, enforces database invariants, exposes honest runtime health, and treats delivery, recovery, and cost as design inputs.

Glossary

Execution plan

The planner’s tree of scan, join, sort, and aggregation operations, with estimated or measured work.

Isolation level

The visibility and anomaly guarantees a transaction receives under concurrency.

Lineage

The trace from a derived artifact back through versions, transformations, and source input.

Manifest

A durable inventory of inputs and outputs for one processing boundary or generation.

Quarantine

An isolated state for unsafe or invalid input that preserves evidence and prevents publication.

Readiness

Whether a workload should receive new traffic now; distinct from whether its process should restart.

RPO / RTO

Maximum acceptable data loss and maximum acceptable restoration time.

Workload identity

A short-lived identity assigned to running software for authorized service access.

Mastery checklist

• I can derive schema keys and indexes from invariants and access patterns.
• I can interpret estimated versus actual rows, loops, buffers, waits, and pool pressure.
• I can resume an ingestion run without duplicate publication and trace every chunk to source.
• I can explain probe controller actions and demonstrate graceful shutdown.
• I can choose a scaling signal while protecting downstream capacity and tenant fairness.
• I can separate application, schema, artifact-generation, and infrastructure rollback.
• I can state RPO/RTO and show a tested restore or rebuild path.
• I can produce a cost range with assumptions and sensitivity rather than a false-precision total.