SQL Database Engineer OOPS Concepts Interview Guide

Choose composition when the relationship is about using a capability rather than being substitutable for the base type. Delegate behind a small interface and keep invariants local. Inheritance can be appropriate, but test that derived behavior honors the expectations of callers using the base abstraction.

Check your understanding: Replace the implementation behind an interface and run the same contract tests.

Common trap: Using inheritance only to avoid copying a few lines.

Concept reference: Dev.java: language, collections and JVM learning paths

Not necessarily. Encapsulation protects meaningful invariants and exposes valid operations, rather than merely making fields private. Model state transitions so callers cannot construct invalid combinations, and keep representation details from leaking through mutable references.

Check your understanding: Attempt an invalid transition through the public API.

Common trap: Equating private fields with a complete domain model.

Concept reference: Dev.java: language, collections and JVM learning paths

Look for implementers that must provide meaningless operations or callers that depend on methods they never use. Split responsibilities around real usage while avoiding one interface per line of code. Test consumers against alternate implementations to expose hidden assumptions.

Check your understanding: Implement a second legitimate provider without no-op methods.

Common trap: Designing an interface from one implementation’s internals.

Concept reference: Dev.java: language, collections and JVM learning paths

At Read Committed, each statement receives a new snapshot, so later statements can see commits made in between. Higher isolation changes the guarantees and may require retrying serialization failures. Choose isolation from the business invariant, not from an assumption that every transaction freezes all reads.

Check your understanding: Interleave two sessions and document the snapshots each statement sees.

Common trap: Treating transaction atomicity and isolation as identical.

Concept reference: PostgreSQL: transaction isolation

An index is an option, not a command. The planner considers selectivity, statistics, table size and access cost. Read estimated and actual rows, filters and scan types before changing indexes. EXPLAIN ANALYZE executes the statement, so use safe data and transaction handling for writes.

Check your understanding: Compare estimated and actual row counts on representative data.

Common trap: Forcing an index without measuring the full query.

Concept reference: PostgreSQL: reading EXPLAIN plans

Check the grain of each table and whether the join creates several rows for one order. Aggregate at the intended business grain before joining, or use the correct relationship and key. Reconcile totals using a tiny fixture containing multiple child rows and an unmatched parent.

Check your understanding: Use one order with two child records and verify the total remains correct.

Common trap: Using DISTINCT as a universal repair for a wrong join.

Concept reference: PostgreSQL: reading EXPLAIN plans

First, identify technical constraints and define measurable service objectives. Next, trace data from producer contract through transformation to a verified consumer result. Contrast architectural trade-offs across reuse, clarity, extensibility and accidental complexity, explicitly mitigate the risk of the service restarts before the triggering allocation path is visible, and confirm system stability using a heap profile, bounded reproduction and post-fix soak-test result.

Common trap: Reaching for a specific library or framework before defining constraints, failure envelopes, and automated verification criteria.

Prioritise the failure mode exhibiting the highest user blast radius and lowest observability. Formulate an explicit containment boundary, implement idempotent retries with jitter, and establish an automated rollback threshold. Verify resilience through a concurrency test and an audit trail demonstrating conflict handling.

Common trap: Relying on passive monitoring dashboards without defining explicit error-budget alerts, rollback triggers, and verified recovery procedures.

Structure the response using Context-Decision-Tradeoff-Result: articulate the business and technical constraints, compare viable alternatives, explain the implementation (trace data from producer contract through transformation to a verified consumer result), and document the accepted trade-off. Provide concrete proof: a focused unit test, dependency boundary and refactoring comparison.

Common trap: Speaking only in high-level abstractions or team accomplishments without detailing your direct implementation decisions, trade-offs, and measured results.

Formulate a falsifiable hypothesis from observable telemetry before altering configurations. Then inspect lineage, event time, partition skew, query plans and replay checkpoints. Isolate the defect to the smallest reproducible boundary, validate root cause with evidence, and confirm full resolution using lease-expiry tests, idempotency records and a restart recovery drill.

Common trap: Applying speculative fixes or restarting services blindly without establishing an observable signal connected to a falsifiable hypothesis.

Produce a schema-compatibility test with lineage evidence and an idempotent replay result. Document baseline assumptions, technical mechanism (trace data from producer contract through transformation to a verified consumer result), rejected alternatives, bounded failure envelopes, and deterministic pass criteria. Supply reproducible verification via a network waterfall, interaction timing and a constrained-device test.

Common trap: Presenting architecture diagrams or slides lacking automated unit/integration tests, observable metrics, or automated rollback configurations.