Explain the mechanism. Prove the result. Defend the trade-off.
151 min reading 4-hour core route Full ~11h 16m
TOTAL STUDY TIME / FULL GUIDE~11h 16m
151 min reading + 8h 45m practice, including one 60-min mock.
Short route: 4 hoursSelected essentials + one mock.All three mocks: ~13h 16mFull guide + two additional mocks. Estimates include practice time; breaks are extra.
LOCAL & OFFLINEPRIMARY-SOURCE NOTESTHREE MOCK LOOPSREVISION 1.2
FOCUS TIME00:00
Estimated remaining: ~11h 16mStart a focus session to track pace.Open mock runner
01 / Start 3 min read · 10 min practice
Your route through the round
The objective is to show that a product team can trust you with an ambiguous data problem through delivery and operation. A correct definition is the start. The stronger answer connects a mechanism to a failure it prevents, an alternative it rejects and a result you can verify.
Preparation route
Work allocation
Finish line
4-hour selected core
70m technical essentials; 30m two primary LP tracks; 30m original SQL fixtures; 60m Mock A; 30m repairs; 20m evidence and final recall.
240 minutes. A selective route, not completion of every section.
Full guide: ~11h 16m
151 min reading + 8h 45m practice. Section budgets include both content passes, the knowledge check and one full mock.
Complete every section. Extra repair time and breaks are additional.
Recommended mastery route: ~13h 16m
Full guide plus Mock B and Mock C, 60 minutes each.
Clear every readiness condition, then revisit weak answers cold.
Final 30-minute review
Desk card, two real story anchors, one introduction and one diagnosis answer.
Optional extra review after the routes above.
Use the first checkbox only after you can explain the worked example. Use the second only after answering its question with the answer hidden. The two passes measure your own study actions, not an interviewer score. Your notes, chapter progress, quiz results, mock ratings and focus time save locally in this browser. Export them before changing browsers or moving the file.
Bound the preparation
Go deep in this round
Keep at explanation depth
Reserve the full drill for another round
Keys and grain; transactions and consistency; Oracle; storage and indexes; diagnosis; reliable ingestion; verifiable delivery
AWS service choices; model history; SQL edge cases; scope and cost estimates
Large SQL problem banks, difficult Python algorithms and exhaustive modeling designs. The recruiter named separate rounds for these.
Sources used: the November recruiter transcript, the new Product BI JD, the application Q&A, the project resume, the earlier phone screen, and relevant existing technical explanations. The previous guides supply practice, not current scheduling facts. Primary technical references appear beside the relevant material and in the source ledger.
02 / Position 4 min read · 15 min practice
The opening and the product lens
The JD asks for BI-scale ETL/ELT, OLAP, modeling, SQL and Oracle, with AWS and non-relational stores preferred. The recruiter specifically asked about partnership with product managers. Present the business question, the engineering responsibility and the operational consequence together.
A 75-second introduction, based on reported resume experience
This draft uses reported career anchors. It does not claim a current employer, a verified revenue saving, Oracle tenure or a production AWS service you have not established. Keep the opening short enough to invite a technical discussion.
SAY IT / Opening
Why this Product BI role?
Reveal a strong first answer
I want to work close to product decisions while remaining hands-on with the data foundation. A product manager needs a trustworthy definition of adoption or usage, not just another table. I can contribute by clarifying that definition, designing the model and pipeline, and owning validation and operation. My background with product teams and large event data is relevant, and I would first learn this team’s decision cadence and current pain points.
Pressure test: Which specific product decision would you improve first? Ask about the real team before claiming to know its roadmap.
Avoid: A generic statement about Amazon’s size, or pretending the JD establishes the team’s exact production architecture.
SAY IT / Opening
What would you do in your first month?
Reveal a strong first answer
I would identify the team’s most important consumers, the decisions they make and the datasets they rely on. I would trace one critical metric from source to consumer, review its grain, freshness and quality controls, and learn the on-call and release process. Then I would agree on one bounded improvement with an owner and a measurable acceptance criterion. I would avoid proposing a platform rewrite before understanding current constraints.
Pressure test: What if the manager wants a delivery in week one? Narrow the first task and retain its validation.
Avoid: A promise to redesign the whole warehouse in 30 days.
Turn a vague request into an engineering contract
PM says
Clarification
Resulting contract
“Adoption is down.”
Which accounts qualify, which action counts, which day and comparison cohort?
Daily active eligible accounts / eligible accounts, UTC, metric version and completeness cutoff recorded.
“Make it real time.”
What decision becomes worse after 15 minutes, one hour or one day?
A latency target tied to a decision; provisional and reconciled results may be separate.
“I need one number for revenue.”
Usage value, invoiced amount, recognized revenue or collected cash?
Separate facts and owners. Do not turn a usage estimate into recognized revenue.
“Can it launch Friday?”
Which consumer decision must work, which inputs exist, and who accepts reduced scope?
Acceptance tests, source owners, deadline, fallback and explicit exclusions.
03 / Database foundations 6 min read · 20 min practice
Grain, keys and the meaning of a row
A database answer starts with what one row represents. A physical row ID is not enough. A usage table may represent one account, resource, service and metering interval. A correction table may represent one version of that usage observation. These are different grains even when both contain the same event ID.
Declare the measurement grain before choosing joins or storage.
Concept
Precise explanation
Product BI consequence
Candidate and primary key
A candidate key is a minimal attribute set identifying a row. Choose one as the primary key. A surrogate key does not remove the need for business uniqueness.
An auto-generated ID will happily assign different IDs to two copies of the same usage event unless the business key is checked.
Foreign key
A reference to an existing parent key, with engine-specific enforcement and null behavior.
A service ID missing from the dimension must be rejected, quarantined or mapped to an explicitly unknown member.
NULL
An unknown or absent value, not numeric zero. Empty-string behavior is engine-specific; Oracle 19c treats zero-length character values as NULL. Comparisons can evaluate to unknown.
COUNT(column) excludes nulls; COUNT(*) counts rows. A WHERE filter preserves only true results.
Types
Choose a representation that preserves the business contract. Units, precision, timezone and range matter.
Use fixed precision or scaled integers for defined monetary quantities. An integer number of cents cannot represent a price in microdollars without a stated scale.
Natural vs surrogate key
A natural key has business meaning. A surrogate key is a system-assigned identity.
A versioned dimension can use a surrogate version key while retaining a stable account business key.
Normalization
Store facts with the attributes they depend on to reduce inconsistent copies. Denormalize deliberately for a query contract.
Changing an account name should not require rewriting every transaction in a transactional model; a serving model may intentionally retain the historical name.
SAY IT / Database
Why can a technically valid join produce a wrong revenue number?
Reveal a strong first answer
The join may change the number of rows representing each business event. If an event joins to two matching dimension rows, its amount is counted twice. I would state the intended grain, test uniqueness of the join keys on each side and compare row counts and amounts before and after the join. I would resolve dimension versions or use a semi-join for existence rather than hiding the expansion with DISTINCT.
Pressure test: If two legitimate events have identical amounts, what does SUM(DISTINCT amount) destroy? It collapses separate events.
Avoid: Using DISTINCT as a universal repair; aggregating the fact first does not cure a later one-to-many join.
SAY IT / Database
If a table has a primary key, can you assume there are no duplicate business events?
Reveal a strong first answer
Only after checking both the business key and the database’s enforcement semantics. A surrogate primary key can be unique while the same business event appears twice. In Redshift, primary, foreign and unique constraints are informational, and declaring invalid relationships can mislead the optimizer. I would validate the intended business uniqueness during ingestion and before publication.
Pressure test: Which constraint does Redshift enforce? NOT NULL. Which checks belong in the pipeline? Business keys, references and allowed values.
Avoid: Treating the phrase “primary key” as the same guarantee across all engines.
Pass 2: a dimension has two rows per account. Is it corrupt?
Not necessarily. It may be valid SCD Type 2 history. The question is whether the consumer wants current attributes or attributes at event time. Resolve the single applicable interval with an as-of join. Overlapping intervals for the same business key are a separate defect.
Normal forms: explain the dependency, not only the acronym
Form / design
Small example
What the split protects
1NF
Store one service subscription per row rather than service_1, service_2 and service_3 columns. Define one value per attribute under the chosen model.
The model can represent new subscriptions without altering the schema or searching repeated columns.
2NF
In a table keyed by (account_id, service_id), account_name depends only on account_id. Move account attributes to an account table.
A non-key attribute should not depend on only part of a candidate composite key. Updating the name no longer touches every subscription.
3NF
account_id determines region_id; region_id determines region_name. Store the region name with the region.
Avoid a non-key attribute depending transitively on the key through another non-key attribute.
Dimensional serving model
A fact at a declared grain joins conformed account, service and date dimensions. A star usually keeps useful descriptive attributes in dimensions.
Repeated analytical queries become simpler. Deliberate redundancy must have an owner and a historical rule.
Snowflaked dimension
Split some dimension attributes into related hierarchy tables.
Reduce duplicated hierarchy attributes, at the cost of more joins and a more complex consumer model.
SAY IT / Database
Should the BI warehouse be fully normalized?
Reveal a strong first answer
I would separate source integrity from analytical serving. Normalization can reduce update anomalies in operational data. A dimensional serving layer can deliberately denormalize attributes and organize facts around business processes for predictable analytics. I would choose based on query patterns, change frequency, history and governance. Denormalization must preserve a declared grain and a repeatable refresh policy; it is not permission to duplicate measures.
Pressure test: When does the convenience become harmful? When redundant definitions diverge, joins multiply facts or history is silently overwritten.
Avoid: Saying star schemas are always superior or that normalization is merely splitting tables.
Three relational questions that expose weak assumptions
Question
Answer
Reason
Will CHECK(amount >= 0) reject a NULL amount?
Not by itself under standard SQL null semantics; add NOT NULL when required.
A check that is not false can pass. Unknown is not the same as a negative number.
Does every foreign key create an index on the referencing column?
No. For example, PostgreSQL does not automatically create that referencing-side index. Evaluate it for joins and parent-row changes.
Integrity enforcement and access-path creation are different choices.
Can you sum an account balance across dates?
Usually not to obtain a meaningful total balance. It is a snapshot measure with a temporal interpretation.
An additive event amount and a semi-additive balance have different aggregation contracts.
These are compact database reasoning exercises, not a substitute for the separate SQL round. Every query below was executed against SQLite during the build. The browser reveals those verified results; it does not execute arbitrary SQL. Engine-specific transaction behavior is taught separately.
Shared fixture
Input rows
changes(event_id, account_id, seq, cents, deleted)
e1, a, 1, 100, 0
e1, a, 1, 100, 0 -- exact replay
e2, b, 1, 50, 0
e2, b, 2, 70, 0 -- corrected amount
e3, a, 1, 30, 0
e3, a, 2, 0, 1 -- deletion tombstone
tags: (a, enterprise), (a, beta), (b, starter)
accounts: a, b, c
The contract says each event’s sequence is strictly ordered, identical versions have identical payloads, and conflicting payloads at the same event/sequence are quarantined. Exact duplicates are collapsed, the maximum sequence wins and tombstones remain in current state. Active events are e1 = 100 cents and e2 = 70 cents. Total current active value is 170 cents; summing every raw row would produce 350.
SQLite-tested canonicalization; validate conflicting versions first
WITH exact_changes AS (
SELECT DISTINCT event_id, account_id, seq, cents, deleted FROM changes
), ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY event_id ORDER BY seq DESC
) AS rn FROM exact_changes
)
SELECT event_id, account_id, seq, cents, deleted
FROM ranked WHERE rn = 1;
1. Choose the current facts
Predict the active rows after replay, correction and deletion.
2. Catch the join expansion
Join active events to all account tags and sum cents. Predict the wrong total and explain the extra amount.
3. Filter on membership
Return the total for enterprise-tagged accounts without duplicating facts.
4. Keep accounts with no usage
Count matching events for every account. Explain why COUNT(*) is wrong for account c.
5. Explain the empty anti-join
The exclusion subquery includes NULL. Predict the result of NOT IN.
6. Repair the anti-join
Use NOT EXISTS. Explain which equality establishes the absence.
7. Calculate adoption correctly
Region A: 10 active / 20 eligible. Region B: 8 / 80. Predict the combined rate, assuming disjoint eligible populations.
8. Resolve the boundary instant
Account a changes from basic to pro at February 1. Assign January 31 and February 1 events to half-open validity intervals.
9. Disprove a reassuring checksum
Source amounts are 100 and 70; target amounts are 110 and 60. Totals match. Find the row-level discrepancies.
05 / Trace and test 4 min read · 15 min practice
SQL precision under interruption
Your earlier phone screen needed a prompt about descending rank. Make direction, tie handling and output grain automatic. These six additional fixtures are executed with the original nine during every build. All six use SQLite; none claims to test Oracle or Redshift runtime behavior.
Decision
Rule to say aloud
Example
Evaluation order
Reason from FROM/JOIN and ON to WHERE, grouping, HAVING, windows, output and final ordering. This is logical reasoning, not a fixed physical execution order.
Filter a window result in a subquery/CTE when the engine has no suitable QUALIFY support.
For 100, 100, 80: ranks 1,1,3; dense ranks 1,1,2. Use DESC for largest first.
Tie policy
A deterministic winner needs a business-accepted tie-breaker.
ROW_NUMBER ordered by score DESC, id picks a row. Adding id to RANK would instead break the score ties.
Window frame
Specify a ROWS frame and unique ordering for a row-by-row running total.
The usual ordered aggregate default includes peers. Two rows with the same time may jump together.
Outer join filters
ON limits matches; a null-rejecting WHERE on the right side can remove unmatched left rows.
Keep zero-usage accounts by putting the qualifying event condition in ON.
NULL and empty strings
State the engine. Do not assume empty character strings survive unchanged in Oracle 19c.
Oracle treats a zero-length character value as NULL; PostgreSQL distinguishes them.
UNION versus UNION ALL
UNION removes duplicate result rows; UNION ALL retains them.
Use a business key/version rule for replay, not set union as an unexplained deduplication policy.
10. Rank the ties
Predict ROW_NUMBER, RANK and DENSE_RANK for a=100, b=100, c=80. Explain why only ROW_NUMBER uses id in its window ordering.
11. Trace the running total
Two events share one ordering value. Compare explicit ROWS and RANGE frames.
12. Preserve the missing account
Keep every account, counting only events above 80 cents. Predict zeros.
13. Move the predicate
Move the same condition to WHERE. Which accounts disappear, and why?
14. Aggregate no rows
Predict COUNT(*), COUNT(cents), SUM and AVG for an empty relation. Decide whether a consumer should receive NULL or a business-defined zero.
15. Test three-valued logic
Predict comparisons involving NULL and the result of a COALESCE. SQLite output is not a claim about every Oracle function.
SAY IT / SQL precision
The PM asks for the top two services. What do you clarify?
Reveal a strong first answer
I would clarify whether they want exactly two rows or all services at the two highest distinct values, then define the measure, population, time window and tie policy. For exactly two rows I can use ROW_NUMBER with a deterministic accepted tie-breaker. For the two highest distinct values I would use DENSE_RANK over the measure descending and retain ranks at most two. I would not add an identity column to that ranking order because it would split ties.
Pressure test: If three services tie for first, how many rows should be returned? The clarified contract decides.
Avoid: Using RANK <= 2 when the requirement is the top two distinct values.
06 / Database foundations 5 min read · 25 min practice
Transactions, snapshots and concurrent writers
An atomic change and a complete business result are different guarantees. A transaction can commit perfectly valid rows that omit an entire source region. Conversely, a pipeline can compute every expected row but expose only half of them while publishing. Name the invariant, then choose the boundary that protects it.
Property
Mechanism-level answer
Failure to discuss
Atomicity
Group changes so they all commit or none become visible as committed.
Account A is debited but account B is never credited.
Consistency
Preserve declared constraints and application invariants through valid transitions.
An invariant omitted from both schema and application is not magically enforced.
Isolation
Control which effects concurrent transactions can observe and which concurrent histories can commit.
Two transactions independently see enough remaining budget and together overspend it.
Durability
Once committed under the configured durability guarantees, data survives the specified failures.
A replica or backup may lag; recovery objectives still need measurement.
A snapshot demonstration
Initial committed balance is 100. Reader A reads. Writer B commits balance 70. Reader A reads again. Select a mode and step through the schedule. This teaching simulation represents ordinary SELECT behavior, not a live database.
In PostgreSQL, ordinary Read Committed reads use statement snapshots; Repeatable Read holds a transaction snapshot and prevents phantoms, but can still permit serialization anomalies. Serializable may abort a transaction so the application must retry the whole transaction safely. A stable snapshot does not mean every business rule across concurrent writers is safe.
SAY IT / Database
Our report runs two queries in one transaction. Are they guaranteed to agree?
Reveal a strong first answer
Not at every isolation level. Under statement-level Read Committed, another transaction can commit between the queries, so they can see different committed states. If the report requires one consistent snapshot, I would use an appropriate transaction-level snapshot or query a published version. I would also check whether multiple source systems share that boundary. One warehouse transaction cannot make unrelated source snapshots simultaneous.
Pressure test: What does this cost? Longer snapshots can retain old versions and interact with maintenance; choose the shortest useful boundary.
Avoid: Saying that BEGIN automatically freezes all reads in every database.
Write skew: a concrete counterexample
Policy: at least one regional collector must remain enabled. Both east and west are enabled. Transaction A reads both and disables east because west is enabled. Transaction B reads the same old state and disables west because east is enabled. They update different rows. A snapshot alone can allow both to commit, leaving zero collectors. Protect the shared invariant using a suitable serializable protocol or a common guard row locked by every relevant writer. Locking only the row each writer changes does not establish the invariant.
SAY IT / Database
Two jobs occasionally deadlock. What would you change?
Reveal a strong first answer
I would collect the deadlock graph and identify the resources and acquisition order. I would make all writers acquire shared resources in a consistent order, keep transactions short and ensure indexes avoid unnecessary locking work. I would still implement bounded retries of the entire aborted transaction, with idempotent effects. A retry is recovery; removing the inconsistent lock order addresses the cause.
Pressure test: How do you avoid duplicate notifications on retry? Commit an outbox record with the transaction and make its consumer idempotent.
Avoid: Retrying only the failed statement or moving external side effects inside an unsafe retry loop.
07 / Failure reasoning 7 min read · 25 min practice
Recovery, replication and safe writes
Follow a write through a crash
In a WAL-based engine such as PostgreSQL, log records reach durable storage before the affected data pages are written. Recovery can redo changes after a crash. A checkpoint bounds recovery work; it is not a replacement for a backup. The precise acknowledgement guarantee depends on durability configuration and the failure being considered. A client timeout also does not prove that the server rolled back.
Boundary
What can go wrong
Decision and verification
Client acknowledgement
The commit succeeds but the response is lost.
Use a request identity and a durable receipt/result record in the same transaction where possible. Retry the same identity; investigate its recorded outcome.
Read replica
A reader sees old state after a successful primary write.
For a read-your-writes path, route to the appropriate writer or require a replication-position guarantee. Do not sleep for an arbitrary interval and call it consistent.
Failover
The promoted replica lacks acknowledged asynchronous writes.
Define the acceptable RPO and validate the actual replication mode. Fence the old writer so two primaries do not accept conflicting writes.
Deletion or bad migration
The mistake is replicated to every replica.
Restore an isolated copy from a valid backup and logs, before the bad change when supported. Reconcile before controlled cutover.
Backups
A successful backup job produces unusable recovery material.
Run restore drills, verify the log chain, keys/permissions, data checks and measured recovery duration.
Outage budget
A 15-minute RPO and 60-minute RTO are asserted without a drill.
If failure occurs at 12:00 and the last recoverable state is 11:42, the 18-minute loss exceeds RPO. Restoring by 12:45 can still meet RTO.
SAY IT / Recovery
We have two replicas. Why do we still need backups?
Reveal a strong first answer
Replicas improve availability and can serve reads, but they may quickly reproduce a deletion, corruption or bad application write. Backups plus a supported retained log chain can recover an earlier state. I would define RPO and RTO, protect the recovery material, restore into isolation and validate both data and application behavior before a controlled cutover. I would also test the time to restore, not merely the time to take a backup.
Pressure test: What if the encryption key is unavailable during disaster recovery? A complete recovery plan must include authorized access to required keys and dependencies.
Avoid: Calling replication a historical recovery strategy by itself.
Lost updates: make the check part of the write
Two writers read version 7, then each writes its own full replacement. The second can erase the first. Optimistic concurrency attaches the expected version to the update; pessimistic concurrency locks the intended conflict boundary before acting. Both need a failure policy. A single-key condition cannot enforce a cross-key budget invariant by itself.
SQL
-- Teaching SQL: bind parameters and inspect the affected-row count.
UPDATE account_state
SET status = :new_status, version = version + 1
WHERE account_id = :account_id AND version = :expected_version;
-- One row: your expected version matched.
-- Zero rows: missing entity or conflict. Re-read and re-evaluate.
-- For an ambiguous timeout, inspect a durable request receipt;
-- blindly replaying a non-idempotent increment is unsafe.
SAY IT / Concurrency
Both jobs read the same row and update it. How do you prevent a lost update?
Reveal a strong first answer
I would first identify the invariant. For a single-row replacement, an expected-version condition can reject a stale writer. I would check the affected-row count, re-read and re-evaluate on conflict, and bound retries. A locking transaction is another option if its contention and duration are acceptable. If the rule spans rows, I need a shared lock boundary or appropriate transaction isolation rather than assuming one version column protects the whole business rule.
Pressure test: Why can an unconditional retry of balance = balance + 10 duplicate an effect after a timeout? The first operation may already have committed.
Avoid: Equating a strong read with atomic read-modify-write.
SAY IT / Recovery
The database commit timed out. Do you retry or skip the batch?
Reveal a strong first answer
Neither blindly. The outcome is unknown to the client. I would identify the batch by a stable operation ID, inspect the committed receipt or publication version, and retry through the same idempotent protocol if its outcome remains uncertain. I would checkpoint only a durably established result. The receipt and data must share an appropriate transaction boundary, or the recovery protocol must handle their partial states explicitly.
Pressure test: What happens if you generate a new operation ID on every retry? Deduplication can no longer recognize the same logical request.
Avoid: Treating a missing success response as proof of rollback.
Read consistency and the network boundary
CAP concerns a network partition: a distributed service cannot promise both linearizable results and a successful response to every request at every nonfailed node in that partition. This is not a rule that every database is permanently assigned two letters. ACID consistency refers to invariants; CAP consistency refers to a single real-time order of operations. A reporting endpoint can deliberately serve a labeled older version while a balance-changing endpoint rejects an unsafe write.
Store / pattern
Useful fit
Boundary to defend
Key-value
Known-key access to one entity or bounded key range.
Choose partition keys to avoid hot tenants; ad hoc joins are a different workload.
Document
Aggregate-shaped records with evolving nested fields.
Schema evolution and validation still exist; flexible storage does not remove contracts.
Wide-column / column-family
Key-oriented access to large sparse or ordered families of data.
This is a different model from analytical columnar storage such as Redshift.
Graph
Relationship traversal is the central query.
Measure traversal needs before adding another serving system.
DynamoDB concrete case
Table/LSI reads can request strong consistency; GSI reads cannot.
A strong read is not a lock. Use write conditions or transactions for the required invariant. Current global tables have MREC and MRSC modes; verify configuration.
SAY IT / Distributed data
Is NoSQL always eventually consistent?
Reveal a strong first answer
No. The data model does not determine a single consistency guarantee. I would name the product, operation, index and replication mode. For DynamoDB, tables and local secondary indexes support strongly consistent reads, while global secondary index reads are eventually consistent. Even a strong read does not make a later write conditional on what I read; that requires the correct write condition or transaction.
Pressure test: Can an eventually consistent analytics result still be useful? Yes, when freshness and correction behavior match the consumer contract.
Avoid: Saying every NoSQL store lacks transactions or every strong read provides a multi-item snapshot.
The practical Oracle conversation is about consistent extraction, transactions, access paths and diagnosis. Learn the mechanism first, then explain where you used it. This section uses the Oracle Database 19c documentation as an explicit teaching baseline; do not imply a particular version is the team’s production version.
Mechanism
What it does
How to explain the consequence
SCN and read consistency
Oracle identifies database change progress with system change numbers. An ordinary Read Committed query sees a statement-consistent committed view.
Two separately started queries may use different cutoffs. A financial extraction needs an intentional common snapshot strategy.
Undo
Keeps earlier data needed for rollback and consistent reads.
Long-running reads can depend on historical versions that are no longer available; investigate undo retention and workload rather than adding retries blindly.
Redo
Records changes used in recovery.
Undo and redo are not synonyms. A restart/recovery discussion is distinct from reconstructing an earlier query view.
Row locks
Protect conflicting modifications. Ordinary consistent reads generally do not block writers.
SELECT FOR UPDATE is a locking read. “Readers never block” without the qualification is too broad.
DDL transaction boundaries
Oracle issues implicit commits around DDL according to its DDL rules.
Do not put schema changes into a rollback demo and assume they share the same semantics as ordinary DML.
Optimizer statistics
Describe data distributions and sizes used for plan selection.
Compare estimates with observed cardinality and current data before deciding that an index or hint is the remedy.
SAY IT / Oracle
How would you extract consistent data from Oracle while the application keeps writing?
Reveal a strong first answer
I would first establish whether the requirement is statement-level consistency or a coordinated snapshot across several tables. For a multi-table extract, I would use a supported snapshot mechanism with a common source cutoff and ensure the retention and permissions support it. If the extract feeds ongoing CDC, I would coordinate the snapshot with a change-log position and retain overlap safely. I would monitor source impact and reconcile the extracted population at that same cutoff.
Pressure test: If the source retains only six hours of changes and your outage lasts eight hours, what is the recovery? Detect the gap and rebootstrap from a supported snapshot; do not silently continue.
Avoid: Running independent SELECTs at different times and calling the combined extract a snapshot.
Index choices with boundaries
Choice
Useful when
Why it may be wrong
B-tree
Selective equality or range predicates, ordered access and common transactional lookups.
Large result fractions can make a scan cheaper; every write has maintenance cost.
Bitmap
Read-heavy analytical combinations of low-cardinality attributes.
Concurrent high-frequency OLTP updates can create costly contention; low cardinality alone is not sufficient justification.
Function-based
A measured workload repeatedly filters the same deterministic expression.
Expression matching, data conversion, statistics and write cost matter; first consider a predicate that can use the ordinary column.
Partitioning
Access and lifecycle management align with a partition boundary, such as time.
A query without a useful partition predicate can still scan everything. Partitioning is not an automatic performance improvement.
SAY IT / Oracle
An Oracle query changed from seconds to minutes. Walk me through the investigation.
Reveal a strong first answer
I would confirm the same SQL shape, bind values and data volume, then separate waiting from execution work. I would compare the current and prior plan, estimated versus observed row counts, access paths and join order. I would check recent loads, statistics, skew, blocking and environment changes. I would test one evidence-based remedy in a controlled environment and verify both output equivalence and latency before rollout.
Pressure test: What if the plan is unchanged? Check volume, skew, cache, contention, I/O and resource concurrency; a plan is not the whole workload.
Avoid: Gathering all statistics, adding an index and changing a hint simultaneously, then claiming to know which helped.
A concise experience answer you can substantiate
Use a concrete system from the resume, your actual task and a technical decision. If your responsibility was extracting Oracle data into a Spark/Hive platform, say that. Do not convert source integration into Oracle DBA ownership. Describe the query, cutoff, key, load and validation you actually controlled. Your strongest senior signal is precision about the boundary of your work.
-- Oracle 19c illustration. Not executed by the SQLite lab runner.
-- Use the SAME supported source SCN for related extracts.
SELECT account_id, status
FROM accounts AS OF SCN :cutoff_scn;
-- Inspect a known cached cursor. Requires the relevant privileges.
-- Runtime rows appear only if the necessary statistics were collected.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
:sql_id, :child_number, 'ALLSTATS LAST'));
Probe
Precise response
Boundary
Snapshot too old
The required older version could no longer be reconstructed for that read. Check query duration, undo availability and competing workload.
Retrying at a later cutoff can change the result. A common SCN needs retained history and supported objects/permissions.
Estimated versus actual plan
EXPLAIN PLAN is an estimate; DISPLAY_CURSOR can inspect a cached execution plan and collected runtime statistics.
A displayed plan without actual rows does not demonstrate measured row counts.
DELETE / TRUNCATE / DROP
In Oracle, DELETE is DML and can be rolled back before commit. TRUNCATE and DROP are DDL with DDL transaction behavior.
Do not generalize Oracle TRUNCATE semantics to PostgreSQL. Recovery capabilities and transaction rollback are separate questions.
MERGE duplicate source keys
Define one source row/version per target key before applying. Validate keys and replay semantics.
MERGE is not by itself a duplicate-event or cross-system exactly-once guarantee.
Separate the logical model from its physical implementation. A star schema is a logical organization. Column storage is a physical representation. Partitioning divides data into managed subsets. Distribution assigns work and data across compute. An index gives another access path. These choices solve different problems.
Workload
Starting choice
Reason and trade-off
Single-account state update with integrity rules
A relational transactional database with selective indexes.
Short transactions and constraints are central. Large analytical scans can interfere with that workload.
Daily service adoption across many accounts
Columnar analytical warehouse or lake query layer.
Read a subset of columns across many records. Compression and scan pruning matter more than individual-row access.
Cheap replayable raw events
Object storage with explicit manifests/catalog and retention.
Durable objects support reprocessing. SQL tables, transactions and discovery need additional systems.
Low-latency entity lookup at large key volume
Key-value design when access paths and consistency requirements fit.
Graph representation if traversal is the measured central workload.
Useful relationship queries do not make every BI dataset a graph problem.
Explain which work each optimization removes. More compute alone does not correct the data.SAY IT / Database
Why would a warehouse prefer column storage to row storage?
Reveal a strong first answer
Analytical queries often scan many rows but use a small subset of columns. Storing each column together reduces irrelevant data reads and can improve compression. Row-oriented storage is often a better fit for retrieving and updating a complete individual record. I would choose based on access patterns, update frequency, latency and concurrency rather than treating one representation as universally faster.
Pressure test: If the query selects almost every column and every row, what advantage shrinks? Column projection savings.
Avoid: “Columnar is always faster” or “OLAP means the data is already correct.”
SAY IT / Database
What index would you consider for account_id = ? and event_time between two bounds?
Reveal a strong first answer
A composite B-tree beginning with account_id and then event_time is a reasonable candidate for that selective equality-plus-range pattern. I would check actual distribution, query frequency, sort needs and write cost. I would also test whether partition pruning or an existing access path already does enough. The exact optimizer and engine version matter; I would not claim later index columns can never be used without the first.
Pressure test: What if one account owns 60% of the rows? Its selectivity differs sharply from the average; compare plans and cardinality.
Avoid: Making a categorical leftmost-prefix rule or adding one index per column without workload evidence.
Redshift: three distinctions worth remembering
Feature
Purpose
Boundary
Distribution style
Reduce data movement for joins/aggregations while balancing work. AUTO is a sensible initial option.
A popular tenant key can create skew. Co-location helps only when the join and distribution actually align.
Sort key
Organize block ranges so metadata can skip blocks for useful predicates. AUTO is an initial option.
Sorting is not a conventional OLTP B-tree index. Query filters and loaded data order affect pruning.
Column encoding
Reduce storage and data movement/read cost through representation.
Compression benefits depend on values and access. It does not eliminate joins or guarantee freshness.
SAY IT / Redshift
A Redshift join is slow. Do you add an index?
Reveal a strong first answer
I would inspect scans, redistribution, skew, spill and queue time, then reduce unnecessary work and evaluate physical layout. Conventional secondary B-tree indexing is not the Redshift tuning model. Distribution can improve join locality; sort keys can improve pruning; statistics and workload management also matter. I would compare before and after results and resource use under representative concurrency.
Pressure test: If one slice processes most rows, does adding nodes necessarily solve it? No; the skewed key can remain the bottleneck.
Avoid: Copying an Oracle indexing answer into Redshift.
A saved query definition; generally not its own stored result.
Does it simplify access and enforce a useful contract? It does not automatically make the underlying query faster.
Materialized view
A stored query result with engine-specific refresh rules.
How stale may it be, what changes trigger refresh and how does the engine maintain it? Do not assume automatic or incremental refresh.
Partition
A logical/physical subdivision managed within a table design, often by range.
Do predicates prune the partitions, and can retention operations use the boundary?
Shard/distribution
Placement of data and work across nodes or databases.
How are joins, hot keys, cross-shard invariants and resharding handled? Partitioning and sharding are not synonyms.
SAY IT / Database
Would you solve a slow dashboard with a materialized view?
Reveal a strong first answer
It can help if the expensive work is repeated and the consumer accepts the refresh/freshness contract. I would first verify the underlying query’s correctness and grain, then choose what to precompute, how to refresh it and how to monitor staleness. I would also measure refresh cost and behavior under late corrections. A fast stale answer is acceptable only if its staleness is part of the agreed product.
Pressure test: What happens when historical source data is corrected? The refresh strategy must repair affected materialized results, not just append new days.
Avoid: Using a materialized view to conceal an incorrect join or promising zero refresh cost.
One level deeper: what the access path actually does
Mechanism
Why it helps
Cost or boundary
B-tree family
A balanced ordered search structure narrows to the relevant keys and supports ordered ranges.
Returning many scattered rows can cost more than scanning. Inserts, updates and splits require maintenance.
Hash index
Hash-based lookup targets equality matches.
Hashing does not preserve order, so it does not by itself provide a range access path.
Covering / index-only access
The needed values may be available from the index without fetching all table data.
Engine visibility checks and plan eligibility still matter. Extra included values increase index size and write work.
LSM-style engine, exemplified by RocksDB
Writes enter an in-memory structure and are flushed to sorted files; background compaction reorganizes files.
Read amplification, write amplification, compaction pressure and retained versions depend on configuration. It is not simply a faster B-tree.
MVCC cleanup, PostgreSQL example
Old row versions cannot be removed while a transaction may still need them. Vacuum makes eligible space reusable and supports other maintenance.
A long snapshot can delay cleanup. Ordinary VACUUM is not a guarantee of immediately shrinking the operating-system file; VACUUM FULL has stronger locking/cost.
SAY IT / Storage internals
Why can an index make a query slower?
Reveal a strong first answer
An index is another access path with its own traversal and data-fetch work. If the predicate selects much of the table, many scattered fetches can cost more than a sequential scan. The index also consumes space and adds write maintenance. I would compare selectivity, clustering/locality, row width, caching and observed I/O under representative parameters before choosing the access path.
Pressure test: What if the same predicate is selective for one tenant but matches most rows for another? The best plan can differ with the parameter distribution.
Avoid: Treating index use as a success metric instead of correct output and measured workload cost.
10 / Reasoning under pressure 5 min read · 25 min practice
Read a plan and prove the fix
Start by defining “slow”: queue delay, lock wait, compute time, I/O, network exchange or final result transfer. Then compare a known baseline with the current run. A plausible theory becomes a useful diagnosis only when a measurement could prove it wrong.
An illustrative plan, not output from a production system
Plan-reading exercise
Aggregate by service
Hash join: usage.account_id = account_history.account_id
Scan usage: estimated 1 million, observed 20 million rows
Scan account_history: 3 versions per account on average
Join output observed: 60 million rows
Question: which is the first correctness issue?
Answer: the history join lacks a current/as-of rule.
The 3x expansion must be resolved before celebrating faster aggregation.
Observation
Hypothesis
Next measurement
Estimated 1M, observed 20M
Stale or insufficient statistics, correlated filters, or changed distribution.
Compare predicate selectivity and statistics recency; locate the first large estimate error.
Input 20M, output 60M after dimension join
Unexpected one-to-many relationship or unresolved history.
Count matches per fact key; inspect overlapping versions and the time predicate.
Same rows, much more disk use
Hash/sort spill or broader projection.
Inspect memory/spill metrics and row width; do not raise memory globally without a concurrency budget.
One worker dominates runtime
Skew or a hot key.
Compare per-worker rows, bytes and duration; test key-frequency distribution.
Execution stable, end-to-end time higher
Queueing, lock wait, network or client bottleneck.
Separate request timeline stages before rewriting SQL.
SAY IT / Database
What does EXPLAIN tell you, and what does it not tell you?
Reveal a strong first answer
An estimated plan shows the optimizer’s chosen operators, estimated cardinalities and relative costs. It does not by itself prove runtime. In PostgreSQL, EXPLAIN ANALYZE executes the statement and adds observed timings and row counts, so I would use it carefully, particularly for writes or expensive queries. In Redshift, EXPLAIN is an estimated plan; I would use execution metrics separately.
Pressure test: Can you add every node’s time to get total latency? No; parent and child work can overlap or include one another, and loops matter.
Avoid: Reading cost units as milliseconds, or running ANALYZE on a production mutation without considering effects.
SAY IT / Database
How do you prove an optimization is safe?
Reveal a strong first answer
I would hold the business contract constant, test outputs at the same source cutoff and compare key-level results as well as totals. I would include duplicates, nulls, late records and history boundaries. Then I would benchmark representative volumes and concurrency, compare cost and latency, and deploy with monitoring and a rollback path. If freshness or correctness changed, I would describe that as a product trade-off rather than a pure optimization.
Pressure test: Why can equal sums still be wrong? Positive and negative errors can cancel, or amounts can be assigned to the wrong account.
Avoid: Reporting only the fastest warm-cache run or only the aggregate checksum.
Pass 2: the query is 10x faster after removing a join. Is that success?
First prove that the join was semantically redundant under enforced or validated relationships. Check whether it filtered invalid keys, supplied historical attributes or changed eligibility. Compare the same output contract and explain remaining assumptions. A faster answer to a different question is not the original optimization.
A small outer input and cheap indexed lookups on the inner side.
A badly underestimated outer input can repeat expensive inner work millions of times.
Hash join
Equi-join where a build-side hash structure is efficient.
Memory pressure, skew or spill; reducing row width or early input size can matter.
Merge join
Inputs already use a suitable ordered access path or sorting is worthwhile.
Sort cost and duplicate-key runs; do not ignore the work to produce ordered inputs.
SAY IT / Database
A nested-loop join is slow. Should you force a hash join?
Reveal a strong first answer
Not before identifying why the chosen plan performs badly. I would compare outer-row estimates with actuals, inspect the inner lookup and evaluate selectivity, statistics and indexes. A hash join might improve this workload, but a hint can hide the underlying estimation problem and harm other parameter values. I would test a justified change under representative inputs and preserve an output-equivalence check.
Pressure test: Why might the same SQL need a different plan for a very large tenant? Its cardinality and selectivity differ from those of a typical tenant.
Avoid: Treating one join algorithm as universally correct.
Design the load as a state transition, not a cron command. For each input, define its identity, ordering, interpretation and replay behavior. For each output, define the commit and checkpoint boundary. This makes failures explainable rather than mysterious.
A source checkpoint, target commit and published metric version are related but distinct boundaries.
Problem
Required decision
Failure if omitted
Duplicate transport
Stable event/change ID and a replay horizon.
Each retry becomes new revenue or usage.
Out-of-order changes
Source ordering per key or a supported ordered log position.
An older update replaces a newer value.
Delete then old insert
Retain tombstone version or equivalent version ledger.
Replaying an old insert resurrects a deleted entity.
Same key/version, different payload
Quarantine conflicting evidence and resolve the source contract.
An arbitrary ingestion timestamp silently decides the “truth.”
Snapshot plus streaming
Coordinate a source-consistent snapshot and log position.
Writes between two unrelated cutoffs disappear.
Checkpoint after ambiguous timeout
Determine commit outcome or use idempotent reapplication.
Uncertain acknowledgment produces either duplicate writes or missing data.
SAY IT / Reliability
Is updated_at polling equivalent to CDC?
Reveal a strong first answer
No. Timestamp polling can miss hard deletes, updates that do not change the timestamp, ties and late/backdated updates. I would define a composite high-water mark, a safe overlap window and deduplication, and add a delete feed or reconciliation process. Log-based CDC captures changes from the source’s supported change stream, but it still needs snapshot coordination, retention, checkpointing and monitoring.
Pressure test: Why is “timestamp > last timestamp” unsafe? Other rows can share that timestamp; a stable tie-breaker and overlap are required.
Avoid: Calling an arbitrary 24-hour lookback a guarantee when later corrections are possible.
SAY IT / Reliability
Can you guarantee exactly-once processing?
Reveal a strong first answer
I would first define the boundary. For one target state, stable identities, version-aware application and coordinated writes/checkpoints can make replay produce the same final result. That does not automatically cover a second database, a notification or an external API. I would describe at-least-once delivery with idempotent effects where that is the actual contract, and test retries, ordering and state retention.
Pressure test: What if your deduplication table expires before the maximum replay age? Old duplicates can re-enter unless a longer-lived invariant protects the sink.
Avoid: Treating a broker or MERGE keyword as an end-to-end guarantee.
The executable replay invariant
SQL
-- SQLite teaching implementation; syntax is not portable to Oracle.
INSERT INTO state(event_id, seq, cents, deleted) VALUES (?, ?, ?, ?)
ON CONFLICT(event_id) DO UPDATE SET
seq = excluded.seq, cents = excluded.cents, deleted = excluded.deleted
WHERE excluded.seq > state.seq;
-- This assumes identical payloads for the same event/version.
-- Reject conflicting versions before applying; retain tombstones.
SELECT SUM(cents) FROM state WHERE deleted = 0;
The build ran 100 shuffled trials containing three deliveries of each input change. Every trial ended with 170 active cents and the e3 deletion retained. That verifies this fixture’s stated invariant; it is not a proof of production concurrency, cross-system transactions or source correctness.
Late data is a product policy
Keep event time, ingestion time and source progress separately. Agree when a metric is provisional, when it becomes reconciled and how older corrections are published. A seven-day repair window is a chosen service policy, not a fact about nature. Route older corrections to an explicit backfill path and expose version changes. Track arrival lag by source and business-time partition, not just the latest received event.
A completed job is a technical signal. A usable product also needs the expected population, correct semantics, an explicit cutoff and a consumer-visible version. Validate those separately. Row-count thresholds should be tied to source evidence and historical variation, not treated as a universal “5% is okay” rule.
Gate
Concrete check
Action on failure
Identity
One current row per business key; no conflicting source versions.
Quarantine ambiguous changes; do not arbitrarily choose a winner.
Completeness
Expected source partitions or manifests received by cutoff; compare per-source counts.
Hold the affected version or publish explicitly partial output if the consumer accepts it.
References/history
No invalid references; at most one valid dimension version per event time.
Apply agreed unknown handling or reject. Resolve overlapping intervals.
Measures
Reconcile counts, distinct keys and amounts at equivalent grain, units and cutoff.
Investigate deltas by partition and key, including offsetting errors.
Freshness
Source progress, transformation lag and published watermark meet the consumer contract.
Expose stale status and choose agreed fallback. Do not just change the dashboard timestamp.
Publication
Consumers resolve one validated version for the entire query/report.
Retain the previous good version; avoid an uncoordinated mixture of old and new tables.
SAY IT / Reliability
How do you prevent a dashboard seeing half a refresh?
Reveal a strong first answer
I would build and validate a new version away from the consumer’s active version, then publish through an atomic boundary supported by the serving system. That may be a database transaction, a table-format snapshot or a manifest/pointer protocol that every reader honors. If a report reads several tables, I would bind them to the same version. I would keep the prior validated version available for rollback and monitor freshness separately.
Pressure test: Does uploading several S3 objects give one atomic table update? No. Strong object consistency is not a multi-object transaction.
Avoid: Calling a set of file renames atomic, or promising cross-table consistency when readers ignore the publication manifest.
Choose the release decision
Select a case and choose a decision. Explain the consumer consequence aloud.
SAY IT / Ownership
A PM needs numbers now, but one source is incomplete. What do you do?
Reveal a strong first answer
I would explain exactly which population is missing, which decisions are affected and what remains trustworthy. If partial output supports a bounded decision, I would get explicit acceptance and label it partial with its cutoff and exclusions. Otherwise I would keep the last validated output with a stale indicator. In parallel I would assign recovery ownership and communicate a next checkpoint. I would not label incomplete data final just to meet a reporting deadline.
Pressure test: What if the missing region is 1% of rows but 40% of value? Row proportion alone is not a business materiality test.
Avoid: Silently filling missing revenue with zero or declaring a threshold without assessing consequences.
Transaction, periodic-snapshot and accumulating-snapshot facts have different grains and update rules.
SCD Type 1 / Type 2
Correct current label in place; or retain dated account-plan versions.
Choose whether the question asks current classification or classification at event time. A late dimension update may require re-keying affected facts.
A worked metric contract
SAY IT / Product reasoning
A PM says adoption fell 20%. What do you check before explaining the business?
Reveal a strong first answer
I would confirm the metric version, comparison periods, eligibility denominator and whether the change is relative percent or percentage points. Then I would separate instrumentation, source completeness, lateness and identity changes from actual behavior. I would compare by service, region and cohort at equivalent cutoffs, check the absolute counts and validate with the product owner. I would not attribute a causal business change to a dashboard movement before checking the measurement.
Pressure test: From 50% to 40%, what changed? Ten percentage points, or a 20% relative decrease.
Avoid: Assuming that lower activity, lower eligibility and a missing source produce the same interpretation.
ETL, ELT and the release boundary
Choice
Use it when
What stays necessary
ETL
Transform before loading the serving target when source isolation, format or governance makes that useful.
Preserve adequate authorized evidence and reconciliation to reproduce the output.
ELT
Load into a governed target/staging layer before target-side transformations.
Raw access controls, workload isolation, versioned logic and source-to-target validation.
Full reload
The bounded data volume and refresh window allow complete replacement.
Stage and validate the new version before publication. Avoid exposing an empty or partial target.
Incremental apply
Only changed entities/partitions need reprocessing.
Stable keys, deletes, late corrections, idempotency and recoverable watermarks.
Batch versus streaming
Use the least complex approach that meets the consumer freshness objective.
Streaming adds state, ordering and recovery obligations. Daily decisions may not benefit from second-level freshness.
SAY IT / Delivery
The source changes amount from dollars to microdollars without changing the column name. What happens?
Reveal a strong first answer
A type-only schema check can pass while every measure becomes wrong. I would treat units and meaning as part of the contract, use versioned producer/consumer agreements and validate representative source examples. I would quarantine incompatible versions, retain the last valid output with a freshness status and coordinate a corrected mapping and backfill. The recovery should identify every downstream version affected, not only fix the next batch.
Pressure test: What if the new field is only an optional column? It can be compatible, but strict parsers, SELECT * consumers or positional loading still need testing.
Avoid: Calling every additive change safe or relying only on column types.
A safe migration: expand, validate, switch, retire
Add the new contract or target alongside the old one. Agree owners, consumers and rollback criteria.
Backfill from a defined cutoff, capture concurrent changes, and prevent a gap between historical and live data.
Run shadow comparisons at the same version: keys, values, population, permissions, latency and cost. Investigate discrepancies before cutover.
Switch readers using the serving system’s supported boundary. Observe real consumers and preserve the old path for the agreed rollback period.
Retire the old path only after adoption, reconciliations and retention obligations are met. Dual writes without a reconciliation protocol create a new divergence problem.
SAY IT / Delivery
How do you prove a replacement pipeline is ready for cutover?
Reveal a strong first answer
I would compare the same source cutoff and metric contract, including key-level values, history boundaries, late corrections and deletes. I would test consumer queries, access controls, peak load and recovery. I would agree acceptance criteria and a rollback decision with the owner, run a bounded shadow period and monitor the actual reader switch. Equivalent row counts alone would not establish readiness.
Pressure test: What if the new pipeline deliberately fixes an old bug? Specify the intended difference and obtain acceptance; do not force parity with a known defect.
Avoid: Making dual-running systems permanent because no one owns the final transition.
14 / Integrated practice 5 min read · 35 min practice
A Product BI case from first question to operation
A PM wants daily active accounts, usage and an estimated usage charge by service and region. Sources are resource events and account metadata. Assume 100 million events/day, average raw payload 500 bytes, a 10x peak-to-average arrival rate, 90 days of hot raw replay and a 06:00 UTC daily delivery target. Events can arrive late and can be corrected or deleted. There is no stated requirement for sub-minute reporting.
1. Clarify the contract before drawing
Define an active account as one eligible account with at least one accepted usage event in the day. Clarify which event types qualify, how internal accounts are excluded and whether test accounts are known at event time. The denominator is eligible accounts, not all account records. Define units per service: compute-hours and storage-GB-days cannot be added into a meaningful “total usage.” An estimated usage charge is a separate metric from invoiced or recognized revenue. Ask for the price/version source before promising the latter.
2. Check the order of magnitude
Calculation
Result
Meaning
100,000,000 × 500 bytes
50 GB/day, decimal, before overhead and compression
An input estimate, not a bill or a compressed Parquet size.
100,000,000 / 86,400
About 1,157 events/second average
Do not provision only for the average.
10 × average
About 11,574 events/second peak assumption
Measure payload distribution and key skew before choosing capacity.
50 GB × 90 days
4.5 TB raw payload baseline
Copies, metadata, compression, retention and reprocessing change actual storage.
One day replay in one hour
About 27,778 events/second
Recovery capacity can exceed live peak. Check source and sink headroom.
3. Choose a simple first architecture
Candidate design: choose the service based on the source contract and workload, not because every icon must appear.
Use supported CDC for relational account changes and a durable event feed or batch delivery for usage. Retain immutable raw records plus source metadata in S3. Apply version-aware corrections, validate keys and units, and generate a bounded daily serving model with Glue or EMR. Choose one transformation approach based on workload and team operation. Load a warehouse serving layer for repeated BI queries and publish only a validated version. Use a catalog and least-privilege access. Keep the transformation rules versioned and the output reproducible.
4. State the model without over-designing it
Table
Grain and key
Why separate
usage_change
One event version; key event_id + source_sequence
Replay evidence, changes and deletes.
usage_current
One latest state per event_id, including tombstones
Determines current accepted usage.
account_history
One account version over a non-overlapping interval
Historical eligibility and segment at event time.
account_service_day
One account/service/region/day, with explicit units
Supports activity and per-account usage without multiplying billing rows.
service_day_metrics
One service/region/day/version
Publication, denominator, active accounts and completeness flags.
Daily distinct accounts are not additive across days or regions when populations overlap. A monthly distinct requires the set of qualifying accounts or an explicitly approximate mergeable sketch. Retain the lower-grain account-day dataset if exact monthly distinct is required. Keep usage, invoicing and payment facts separate until their grains and join contract are explicit.
5. Explain the failures before the dashboard
If a worker crashes after applying changes but before acknowledging them, replay must be safe. If a source region misses the cutoff, completeness must fail independently of job success. If a late account correction changes eligibility, reprocess affected historical intervals under a defined policy. If a raw partition is lost, restore or re-extract before claiming the version is reproducible. If a warehouse table is unavailable, the fallback needs a freshness label and an owner; silent stale serving is not recovery.
SAY IT / Architecture
Why not use streaming for every part of this daily BI problem?
Reveal a strong first answer
I would match latency to the decision. If consumers need a daily reconciled result, a reliable batch or micro-batch path can reduce operational complexity. I would retain a durable ingestion path and design for replay. If a separate operational use case needs low latency, I would define its provisional semantics and reconcile it with the final daily product. The extra path must have a measurable consumer benefit.
Pressure test: What if leadership later needs five-minute alerts? Add the justified low-latency serving path while sharing definitions and reconciliation, not two unrelated truths.
Avoid: Choosing streaming because it sounds more senior.
Pass 2: the PM changes the requirement to exact monthly active accounts by historical segment. What changes?
Daily aggregate distinct counts are insufficient. Keep account identities at the account/day grain, define whether the segment is at event time or month-end, and recompute a monthly distinct over qualifying accounts. If an account occupies multiple segments, define whether it counts in both and how totals should reconcile. Add the affected-history repair path for late segment corrections. An approximate sketch is only acceptable if the PM accepts the accuracy contract.
15 / Behavioral rehearsal 5 min read · 25 min practice
Ownership: find the problem, stay for the outcome
Ownership is visible when you notice an important gap, make the boundary between teams workable and remain accountable after the initial artifact ships. Working hard on assigned tasks is useful but does not by itself establish that broader responsibility. Explain the customer consequence and the mechanism that remains when you move on.
The resume-supported anchor
A complete two-minute practice story
Interviewer probe
Answer direction
Why it matters
“Why was this your problem?”
The customer’s outcome crossed the handoff. Explain why no existing owner or control covered it and how you obtained agreement.
Shows accountable initiative rather than territorial expansion.
“What did you personally do?”
Evidence schema, source checks, pipeline, proposal and acceptance. Separate investigator decisions and leadership approval.
Prevents the project’s overall impact from replacing your contribution.
“Only 70% actionable. Why launch?”
28/40 is a reviewed-pilot rate, not universal precision. Launch a bounded human-reviewed workflow, not automatic enforcement.
Matches confidence to the consequence of an error.
“How were cases sampled?”
State that the practice case uses a selected investigation set. It cannot estimate population recall or prevalence.
An honest denominator is more credible than impressive arithmetic.
“What did you give up?”
In the scenario, broad detection coverage was deferred to prioritize explainable categories and usable evidence.
Ownership requires a real prioritization cost.
“How did it survive your handoff?”
Versioned rules, replayable cases, named disposition owners and a review of usefulness and burden.
Shows a durable operating mechanism.
“How was the revenue impact calculated?”
The pilot has no validated revenue estimate. For a real program, explain period, population, counterfactual, exclusions and attribution.
Protects the distinction between exposure, prevented loss and recovered cash.
“When would you stop?”
If the signals remain unusable at an acceptable review cost or an existing control solves the same problem more effectively.
Shows commitment to outcomes rather than personal attachment.
SAY IT / Ownership
Tell me about a problem you took ownership of beyond your assigned work.
Reveal a strong first answer
Use Ads System Health as the reported project anchor. Describe the actual gap, why it mattered, how you established ownership across teams, the concrete work you did and the observed outcome. The complete constructed rehearsal above demonstrates the level of detail, but its pilot numbers are not personal evidence. A strong real answer separates your intervention from the wider program and explains the lasting control.
Pressure test: What evidence changed your original plan, and what responsibility remained after launch?
Avoid: Retelling an entire program without one decision, or converting an invented pilot into a claimed real incident.
16 / Behavioral rehearsal 5 min read · 25 min practice
Deliver Results: make and keep an honest commitment
Delivery is a commitment with a consumer, scope, quality standard and date. Show what you promised, what threatened it, what you changed and what actually arrived. If the original commitment was missed, keep that visible. A negotiated scope change can be sound judgment, but it does not make the initial scope complete.
The resume-supported anchor
A complete two-minute practice story
Interviewer probe
Strong response in this scenario
What it demonstrates
“You reduced scope. Is that really success?”
The original commitment was missed. The useful outcome was an explicitly accepted narrower release, followed by completion of the remaining work.
Results without rewriting the history of the promise.
“Why not estimate the fourth measure?”
A proxy did not measure the same activity. A separately labeled estimate would need its own acceptance; it could not silently replace the agreed metric.
Preserving meaning under deadline pressure.
“What if the owner refused?”
Escalate the dependency, explore safe reprioritization and communicate the likely miss. Nonexistent observations cannot be created by overtime.
A realistic alternative rather than a scripted success.
“What did you prioritize away?”
Secondary report views; the time went to source correctness and validation of the essential decision.
A specific trade-off.
“Why was the missing metric not critical?”
It supported a different outcome-analysis decision. If its absence invalidated the launch decision, the partial launch would be rejected.
Customer utility, not checklist completion.
“What was measurable?”
Three of four original measures by day ten; all four by day eighteen in this constructed case. No invented revenue lift.
An honest unit and outcome window.
“What did you change permanently?”
A source-readiness gate before date commitments, with owner and representative input required.
A prevention mechanism, not “I communicated better.”
“How did others contribute?”
Upstream owned logging; the product owner accepted scope; you owned data implementation and validation.
Personal accountability without claiming everyone’s work.
SAY IT / Deliver Results
Tell me about a difficult deadline and how you delivered.
Reveal a strong first answer
Anchor the answer in the real Automotive/Travel foundation work, then choose one actual commitment. State its date and acceptance criteria, the obstacle, your decision and the accepted output. Use the constructed scenario only as a rehearsal model. Keep the original and revised promises separate and describe the unfinished work if there was any. Close with the mechanism that improved future commitments.
Pressure test: What would make your revised scope useless to the customer?
Avoid: Counting activity as delivery, or presenting product ARR as revenue personally created.
SAY IT / Deliver Results
Tell me about a commitment you failed to meet.
Reveal a strong first answer
Pick a real miss that you owned. State the original promise and the specific assumption you got wrong before discussing external dependencies. Explain when you recognized the risk, what you communicated, the mitigation you delivered and what remained late. Give the final outcome and a concrete change to your planning or validation process. The lesson should be visible in a repeatable mechanism, not a promise to try harder.
Pressure test: Why did you fail to recognize the dependency earlier, and what evidence shows your change worked afterward?
Avoid: A disguised success, blaming another team, or inventing a failure because the story sounds polished.
17 / Speak and defend 8 min read · 25 min practice
Behavioral follow-ups that break weak stories
Prepare two distinct anchors for each principle, plus one failure. Use the main stories for depth, then these variants to practice answering a different question without forcing the same incident into every prompt. The source resume supports program anchors; the incidents below are explicitly constructed rehearsal material.
Prompt family
Useful resume anchor
Specific evidence the answer needs
Ownership beyond assigned work
Ads System Health proposal.
The unowned customer consequence, why you intervened, what authority you needed and who owned the mechanism after you.
Long-term cost versus immediate win
Historical platform work at Citi or reusable product foundations.
An alternative that shipped faster, the future cost you demonstrated and a decision someone accepted.
Deliver with reduced capacity
Automotive/Travel data foundations.
Original deadline, consumer acceptance, scope trade-off, what was left unfinished and its final disposition.
Failure, missed signal or bad estimate
One real incident from any listed project.
Your own incorrect assumption first, early warning, impact, recovery and evidence that recurrence reduced.
Conflicting commitments
Cross-product DE ownership at Meta.
The decision owner, customer consequences, capacity evidence and how you communicated the displaced commitment.
Interviewer: why did you not simply cast every identifier to an integer?
That destroys leading zeros and can collapse distinct identifiers. The safe mapping depends on the business identity contract, not the most convenient type. Keep raw evidence and handle ambiguous collisions explicitly.
Backup Deliver Results rehearsal: choose between two commitments
SAY IT / Ownership
Tell me about two competing commitments. How did you choose?
Reveal a strong first answer
Use one real conflict. State both promises and the capacity or dependency evidence. Explain the customer consequences, your proposed priority and the decision owner who accepted the change. Show what you personally stopped or changed and what happened to the displaced obligation. Close with the mechanism that prevents recurring overcommitment.
Pressure test: Who lost something because of your choice, and what did you tell them before the deadline?
Avoid: Calling everything critical, hiding the delayed item or presenting stakeholder agreement as your entire contribution.
SAY IT / Ownership
Tell me about a problem outside your formal responsibility.
Reveal a strong first answer
Identify a real unowned consequence, explain how you verified it mattered and describe the authority or partnerships you needed. Show the bounded action you took, the alternative you rejected and the lasting owner or operating mechanism. Taking ownership does not mean bypassing controls or doing every task yourself.
Pressure test: What would have happened if you had not intervened, and how do you know?
Avoid: Using a speculative counterfactual as measured savings.
SAY IT / Ownership
When did you accept a short-term cost for a longer-term benefit?
Reveal a strong first answer
Choose a real decision where the short-term and long-term costs were both visible. Explain the cheaper immediate option, the future failure or repeated work it would create, the evidence and who accepted the trade-off. State the bounded result and how you checked whether the investment paid back.
Pressure test: What evidence would have made you choose the quick solution instead?
Avoid: Describing a preference for elegant architecture without a customer or operating consequence.
SAY IT / Deliver Results
When did quality threaten a deadline?
Reveal a strong first answer
Separate launch-blocking correctness from useful follow-up work. State the original commitment, the defect and the decision it threatened. Describe the options, what you recommended, who accepted the scope or date change and the actual delivered result. Show how unfinished work was completed or explicitly retired.
Pressure test: If the reduced scope did not support the customer decision, would an on-time release still count as useful delivery? No.
Avoid: Renaming a missed goal as success without describing the changed commitment.
SAY IT / Deliver Results
What was your biggest mistake in that project?
Reveal a strong first answer
Name your decision or assumption before describing external problems. Explain the warning you missed, the impact, what you changed during recovery and the follow-through. If the result was mixed, state both the delivered benefit and remaining miss. Make the lesson a visible change in how work is accepted or operated.
Pressure test: What would your partner say you should have done differently?
Avoid: A disguised strength or an invented personal failure presented as fact.
Seven follow-ups to answer without changing the facts
Probe
Strong answer shape
Repair if weak
What did you personally do?
Separate your decision, implementation and influence from the team’s work.
Remove vague we statements until ownership is clear.
What alternative did you reject?
One plausible alternative, evidence and the rejected trade-off.
Do not invent an obviously foolish alternative.
Where did the number come from?
Unit, period, baseline, denominator, source and attribution.
Use a bounded qualitative result if no defensible number exists.
Who disagreed?
Their legitimate constraint and how it changed your decision.
Avoid casting every partner as an obstacle.
What remained unfinished?
Named remaining obligation and its final disposition.
Separate original scope from accepted revised scope.
How did the improvement last?
Owner, monitor, contract, runbook or review mechanism.
Describe what another engineer could operate after you left.
What would you change now?
One concrete improvement supported by what you learned.
18 / Pressure practice 6 min read · 25 min practice
Hard follow-ups and senior judgment
Give a bounded first answer, then expand when asked. For a technical question: conclusion, mechanism, failure boundary, verification. For experience: context, your decision, evidence, lesson. Do not respond to every short question with an entire architecture tour.
SAY IT / Senior scope
How did you influence teams without managing them?
Reveal a strong first answer
Use a real example where teams had different goals. Explain the shared decision, the evidence you brought, the alternative you accepted or rejected and the owner who made the final call. Describe a mechanism that reduced repeated coordination, such as a source contract, shared metric definition or review checkpoint. Mentoring and cross-team scope are stronger when tied to a change in how others deliver.
Pressure test: Who disagreed, what did their constraint reveal, and what did you change?
Avoid: Equating years of experience or the number of meetings with senior impact.
SAY IT / Judgment
The PM wants fast delivery; you want correctness. How do you resolve it?
Reveal a strong first answer
I would quantify the decision risk and distinguish reversible presentation choices from errors that can change customer or financial decisions. I would propose a bounded option such as a validated subset or a provisional metric, explicitly stating exclusions and the correction plan. The accountable owner can choose among real trade-offs. I would not frame every preference as a safety requirement or silently accept a misleading number.
Pressure test: Which quality checks are launch-blocking and which can follow? Explain the consequence, not just a label.
Avoid: Making “quality” an excuse to avoid a deadline, or letting urgency erase the metric definition.
SAY IT / Reliability
How would you backfill six months without disrupting today’s SLA?
Reveal a strong first answer
I would isolate backfill capacity and write into a versioned or staged target. I would use bounded partitions, checkpoints and replay-safe logic, and throttle against live workloads. I would validate historical definitions and source availability, compare per-partition outcomes and publish an agreed version. I would preserve a rollback path and communicate whether previously reported figures change.
Pressure test: What if the old source data cannot reproduce the old metric version? Document the limitation and avoid claiming identical reconstruction.
Avoid: Running one huge job against shared production resources and hoping capacity is sufficient.
SAY IT / Reliability
A job writes the warehouse successfully, then fails to publish a notification. What now?
Reveal a strong first answer
I would define the warehouse commit as a durable state and avoid rerunning it as an unguarded new write. A transactional outbox can record the intended notification with the data update where a shared transaction is possible. A separate consumer retries delivery with an idempotency key and recorded status. If the systems do not share a transaction, I would use an explicit recovery protocol and reconcile the incomplete step.
Pressure test: Can the recipient still receive a duplicate? Yes, unless the delivery contract or receiver deduplicates the effect.
Avoid: Calling two independent API calls atomic.
SAY IT / Architecture
How would you protect sensitive account data in BI?
Reveal a strong first answer
I would begin with classification and approved use, minimize collected and exposed attributes, and separate raw access from curated serving access. I would apply least privilege, encryption and auditable access controls, and test tenant or row-level restrictions with negative cases. I would define retention and deletion propagation through derived data, caches and replay sources. The implementation must match the organization’s actual policy and service capabilities.
Pressure test: If raw history is replayed after a deletion request, how do you prevent reintroducing restricted data? Carry the deletion/eligibility policy into reprocessing.
Avoid: Naming IAM and encryption without explaining who can read what or how derived copies are handled.
SAY IT / Architecture
Your daily input is 50 GB. Is that enough to size the system?
Reveal a strong first answer
No. I would also need peak rates, payload distribution, compression, transformations, joins and state, retention, concurrency, replay requirements and output access patterns. Fifty GB describes one raw input estimate. A one-hour backfill or a highly skewed join can dominate capacity. I would build a bounded estimate and measure representative workloads before committing performance or cost.
Pressure test: What is the one-hour replay throughput for 100M events? About 27,778 events per second before overhead.
Avoid: Treating raw bytes as a complete cloud cost estimate.
SAY IT / Database
Does strong S3 consistency mean my multi-file dataset is transactional?
Reveal a strong first answer
No. Object operations can be strongly consistent while a set of objects is still published at different moments. A reader can observe a mixed set unless the dataset has a commit protocol. I would use a table format or manifest/version protocol and ensure readers resolve the committed version rather than listing a mutable directory and assuming it is complete.
Pressure test: How do you stop one report reading dimensions from version 2 and facts from version 3? Bind its reads to one published version or coordinated transaction.
Avoid: Confusing object consistency with multi-object atomicity.
Questions to ask the hiring manager
Which product decisions depend most on this team’s data, and where is trust or freshness weakest today?
What would you want this hire to own and measurably improve in the first six months?
How are metric definitions and source contracts agreed with PMs and upstream engineering?
Where does the team most need stronger mechanisms: delivery predictability, correctness, performance or adoption?
Reach 100% on knowledge checks, clear the repair queue and repeat the weak answers cold.
The standard: no unresolved errors
Use 100% as the preparation target. First answer without opening the explanation, then explain why the other choices are wrong. Reset and retake the knowledge check after a break. A correct click after reading feedback is practice; a cold explanation is recall. Every technical answer should include mechanism, failure case, trade-off and verification. Every personal story must survive the seven follow-ups with the same facts.
The live gate combines the knowledge score, all chapter recall checks, all three full-mock stage ratings, the open repair queue and the nine evidence checks. Ratings and checkboxes are self-assessments. The clock measures time used; it does not declare mastery. Revisit any answer that depends on a guessed fact or an unverified personal claim.
Run these adversarial prompts after the scheduled mocks
Change the assumption: one tenant owns 60% of the data, the log retention is shorter than the outage, or a source changes units without changing types. Explain which design decision changes.
Remove the happy path: the commit succeeds but the acknowledgement fails; the deletion arrives before the old insert; the failover replica is behind. Trace state, not just service names.
Challenge the result: totals match but accounts are wrong; latency improves because a filter silently drops customers; the on-time deliverable cannot support the promised decision.
Challenge the story: tell the same event in 90 seconds, then defend it for ten minutes. Correct any inconsistency in scope, ownership, denominator or chronology.
20 / Recall 9 min read · 25 min practice
Check understanding before the mock
Choose an answer before viewing feedback. These questions check mechanism knowledge, not confidence in a personal story. Wrong answers remain highlighted on their cards until you answer them correctly. A perfect quiz is not a substitute for speaking, designing and defending the answer.
21 / Timed rehearsal 2 min read · 60 min practice
The interview room
Three complete interviews, with first-person scripts, explicit follow-ups and checked technical answers.
Read the scripts in the next three chapters, then rehearse cold. Use the 60-minute mode for a full interview or the short drill for concise recall. The continuous clock pauses and resumes; moving stages does not reset it.
Nailed: correct mechanism and a defensible follow-up. Wobbled: right direction with a missing condition or weak evidence. Blank: no usable solution. Repair each gap, then repeat cold.
22 / Complete interview 12 min read · 0 min practice
Full mock A / Product usage, ambiguous ownership and a fixed deadline
Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.
Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.
Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.
STAGE 1 / 00:00 TO 05:00 / 5 MIN
Shafi, introduce yourself and connect your experience to this interview.
Resume-based introduction
Shafi’s full answer + 2 answered follow-ups
I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.
In this discussion I would connect database fundamentals to concrete failure modes. I would start with the row and customer decision, explain the storage and transaction mechanism, and then show how I validate and deliver the result.
For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.
Interviewer follow-ups, fully answered
1. What did you personally own rather than the broader team?
I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.
2. Why are you interested in this Data Engineer II role?
The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.
STAGE 2 / 05:00 TO 20:00 / 15 MIN
Design a daily service-usage metric for three eligible accounts a, b and c. a emits events worth 10 and 5 units, b emits 20, and c emits none. What is the grain, the adoption rate and the reliable load?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would separate event grain from reporting grain. A canonical event has a stable event ID, account, service, region, event time, source version and unit. The daily aggregate is one row per account, service and reporting day. Eligibility is a separate account-service-day relation so an account with no usage remains in the population.
For this fixture, a has 15 units, b has 20 and c has zero. Two of three eligible accounts adopted the service, so adoption is 66.67 percent. I would not count three events as three adopting accounts or divide by only accounts that emitted data. I would define positive qualifying usage, the day’s time zone and the source cutoff before publishing that metric.
The load first validates schema and event identity, resolves source versions and deletes, then recomputes affected daily groups. Exact retries are harmless; conflicting same-version payloads are rejected for resolution. I would publish only after key, coverage and semantic checks pass. If a region is missing, a successful job does not justify labeling the output complete. I would expose the agreed stale or partial state and the affected consumer decision.
Interviewer follow-ups, fully answered
1. What if a uses the service in two regions?
I would retain region in the regional fact but deduplicate account-service-day when computing global adoption. Summing regional distinct accounts can count a twice. The reporting grain and denominator decide whether a regional breakdown can be added; the storage grain alone does not.
2. How do you stop a retry from duplicating usage?
I would use stable source identity and version in the canonical store, with idempotent upserts or replacement of a deterministic affected partition. The aggregation consumes canonical state. I would test a crash after target commit but before checkpoint persistence, then replay and verify the same logical rows and totals.
STAGE 3 / 20:00 TO 35:00 / 15 MIN
Tell me about a time you took ownership of a problem that had no clear owner.
Constructed personal incident; Meta shared-metrics anchor
Shafi’s full answer + 2 answered follow-ups
During a product-analytics rollout, several teams saw the same metric differently. Each local pipeline was internally consistent, so every team initially believed the discrepancy belonged somewhere else. I owned one of the data products, and I decided that handing off a ticket would not resolve the decision the product team needed to make.
I brought the consumers and source owners together around one concrete example. I documented the population, time cutoff, identity rule and units for each version. The discrepancy came from a current-account lookup being applied to historical events in one path. I owned the comparison and the proposed contract, while the source owner confirmed how account changes were represented.
I implemented an event-time mapping in the affected transformation, reconciled representative account histories and then compared the full impacted partition. I asked the consumers to validate their actual decision query, not merely approve a row count. I also wrote down who owned future definition changes and added an interval-overlap check to the release process.
The immediate result was one accepted historical interpretation and corrected outputs for the affected period. The durable result was that the next definition question had a named decision path. I learned that ownership means staying through consumer acceptance and operation, even when the initial defect crosses team boundaries.
Interviewer follow-ups, fully answered
1. What did you do beyond your normal assigned task?
I organized the cross-team definition review, made the conflicting interpretations explicit and carried the correction through the consumer query. My assigned pipeline was only one part of that path. I did not take over everyone’s implementation; I made the shared decision and remaining ownership visible.
2. What would you do if the teams still disagreed?
I would document which customer question each definition answered and ask the accountable product owner to choose or name two distinct metrics. I would not force different meanings into one ambiguous field. The engineering implementation would then enforce the chosen contract and label any separate interpretations.
STAGE 4 / 35:00 TO 50:00 / 15 MIN
Describe a delivery with a hard deadline and an unexpected dependency problem.
Constructed personal incident; Citi integration anchor
Shafi’s full answer + 2 answered follow-ups
In a reporting delivery, I committed to a review date before two source feeds had completed exception validation. As the deadline approached, the normal rows were ready, but the exceptions could map activity to the wrong account. I owned the commitment and did not want the schedule pressure to turn an incomplete view into a misleading final report.
I split the remaining work by customer consequence. Six feeds had passed the agreed checks and could answer a bounded operational question. The two unresolved feeds affected a separate breakdown. I showed the partner the exact scope, the missing cases and the next validation checkpoint. I acknowledged that my original plan had underestimated exception discovery.
I proposed delivering the validated scope on the original date with explicit coverage, while finishing the two feeds as a separate accepted milestone. I assigned the exception work, paired on the highest-risk mapping and kept a daily reconciliation review. I did not remove the original commitment from the status record.
The partner accepted the bounded delivery, and the remaining feeds passed review three days later. The result was useful work on time and a clearly owned miss on the rest, rather than a false claim that the original scope had fully shipped. I changed subsequent planning to validate the exception path before setting the final acceptance date.
Interviewer follow-ups, fully answered
1. Did you actually meet the original commitment?
Not in full. I delivered an accepted reduced scope on the original date and completed the remaining scope three days later. I would distinguish those facts explicitly. A revised agreement was the right recovery action, but it did not erase the planning error.
2. Why not add more engineers and preserve the date?
The bottleneck was understanding the source exception semantics, not simply typing more code. I used pairing where it helped, but parallel implementation before resolving meaning could create inconsistent fixes. I prioritized the uncertain dependency and kept the validated delivery useful.
STAGE 5 / 50:00 TO 55:00 / 5 MIN
The pipeline is green but one expected partition is absent. Would you publish?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would not publish it as complete. I would compare the expected source manifest with arrived and accepted partitions, identify which customers and decisions the missing partition affects, and use the documented publication policy. If a partial view is useful, its coverage and cutoff must be explicit and accepted by the accountable consumer. Otherwise I would retain the last validated version with visible staleness while recovering the missing data.
The recovery would replay the missing scope safely and validate both completeness and row-level correctness before switching readers to a coherent version. My incident update would include what is verified, what is missing, the decision impact, the owner and the next checkpoint.
Interviewer follow-ups, fully answered
1. What if only one percent of rows is missing?
I would examine the missing population. One percent of rows can contain an entire region or a high-value customer segment. Percentage alone is not an impact decision. I would measure affected decisions and apply the agreed acceptance rule.
2. How do readers see a coherent release?
I would publish a validated version or manifest and switch a pointer only after its dependent outputs are ready. Readers must resolve that same version consistently. Merely writing a release ID into a log does not prevent readers from mixing old and new tables.
STAGE 6 / 55:00 TO 60:00 / 5 MIN
What questions do you have for me, and how would you close?
Role-focused closing
Shafi’s full answer + 2 answered follow-ups
I have two questions. Which product decisions are most sensitive to data correctness or freshness on this team? What would you want this hire to own independently after six months?
The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.
Interviewer follow-ups, fully answered
1. What would you focus on during your first month?
I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.
2. What would you do if the team’s approach differed from your previous company?
I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.
23 / Complete interview 11 min read · 0 min practice
Full mock B / Oracle, query diagnosis and a missed commitment
Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.
Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.
Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.
STAGE 1 / 00:00 TO 05:00 / 5 MIN
Shafi, introduce yourself and connect your experience to this interview.
Resume-based introduction
Shafi’s full answer + 2 answered follow-ups
I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.
In this discussion I would connect database fundamentals to concrete failure modes. I would start with the row and customer decision, explain the storage and transaction mechanism, and then show how I validate and deliver the result.
For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.
Interviewer follow-ups, fully answered
1. What did you personally own rather than the broader team?
I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.
2. Why are you interested in this Data Engineer II role?
The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.
STAGE 2 / 05:00 TO 20:00 / 15 MIN
An Oracle report joins a 50-million-row event table to an account dimension. It became ten times slower after a data load. What do you investigate, and how do transactions affect the answer?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would first separate elapsed time into waiting and execution. I would compare the current SQL, bind values, execution plan and actual row counts with a known representative run. A changed selectivity estimate, stale statistics, a new skewed key, a different join method or more table lookups could explain the regression. I would also check whether the load changed the amount of data the query should legitimately read.
I would inspect the predicates and join cardinality before adding an index. For a selective account-and-time query, a suitable composite index may reduce reads; for a broad analytical scan, index lookups can cost more than scanning. Partition pruning, indexing and join choice are separate mechanisms. I would test a specific hypothesis and compare equivalent output under representative volume and concurrency.
For correctness, I would state the isolation behavior. Oracle read consistency uses undo to reconstruct the appropriate committed view. Under READ COMMITTED, separate statements can observe different committed states. A multi-step report that requires one consistent cutoff needs an explicit transaction or snapshot strategy appropriate to the engine. I would not assume that a stable statement snapshot makes every concurrent history serializable.
Interviewer follow-ups, fully answered
1. A plan estimate says 1,000 rows but the operator returns 10 million. What next?
I would investigate the statistics and the actual predicate distribution, including correlated columns and bind-sensitive behavior. That estimate error can lead to a poor join or memory decision. I would verify the data distribution, refresh or improve the relevant statistics as appropriate, then compare the new plan and runtime without changing the result contract.
2. Why not say an index always fixes the report?
An index has value only when its access pattern reduces enough work to outweigh lookup and maintenance costs. A query returning a large fraction of the table may be better served by a scan. I would show the actual reads and join behavior, and account for added write and storage cost.
STAGE 3 / 20:00 TO 35:00 / 15 MIN
Tell me about a time you stayed with a problem after another team supplied the fix.
Constructed personal incident; Meta data-quality anchor
Shafi’s full answer + 2 answered follow-ups
In a data-quality incident, a source team corrected the upstream mapping and considered its part complete. I owned a downstream analytics output that had already incorporated the old mapping. A source repair alone would leave historical reports inconsistent, so I took responsibility for the remaining path.
I identified the affected source versions and downstream partitions, then traced which reports had consumed them. I proposed a bounded backfill and a reconciliation at the same cutoff. I worked with the source owner to verify the corrected semantics, but I owned the downstream replay plan, validation and consumer communication.
I ran the correction through a staging version, compared keys and classifications and checked that unaffected partitions stayed unchanged. After consumer acceptance, I switched the published version and retained the prior output for diagnosis. I also updated the incident record to distinguish source repair from downstream recovery.
The outcome was a corrected consumer view and a documented recovery process for future source changes. My lesson was that an upstream ticket closing is a milestone, not proof that the customer consequence has ended. I now make downstream correction obligations explicit when the incident is opened.
Interviewer follow-ups, fully answered
1. How did you bound the backfill?
I used the source-version and event-time relationship to identify affected partitions, then validated the proposed boundary against sampled identities and population-level checks. I did not assume that a recent source update affected only recent business events.
2. How did you know you had finished?
The corrected version passed identity and classification reconciliation, the consumer’s acceptance query matched the agreed meaning, and the release status was communicated. I also recorded any unresolved scope. Job completion alone was not my finish line.
STAGE 4 / 35:00 TO 50:00 / 15 MIN
Tell me about a time you missed a deadline. What was your responsibility?
Constructed personal incident; migration anchor
Shafi’s full answer + 2 answered follow-ups
I missed a reporting milestone because I estimated a migration from the normal data path and discovered the historical exceptions too late. The initial transformation was running, but reconciliation exposed records whose identities had changed over time. I was responsible for the estimate and for not testing that assumption early enough.
When I confirmed the gap, I told the partner which acceptance conditions had failed and which results were already reliable. I resisted the temptation to describe a running job as a completed delivery. I narrowed the remaining work to the historical identity cases, paired with the domain owner and set a new checkpoint based on tested scope rather than optimism.
I delivered a reviewed interim output for the unaffected population and then completed the historical correction. I kept the original date and miss visible in the retrospective. The important change was to move representative exception fixtures and consumer reconciliation into the estimation phase.
The result was eventual acceptance with a clear explanation of the delay. I do not treat that as an on-time success. The lesson was that a credible commitment requires evidence about the difficult part of the data, not just a working transformation for typical rows.
Interviewer follow-ups, fully answered
1. What would you do differently before committing?
I would identify the conditions most likely to invalidate the estimate, including history changes and exceptions, and run a small acceptance spike before promising the full date. I would make the dependency uncertainty visible and agree which part of scope could be delivered independently.
2. How did you avoid blaming the source team?
I described the source behavior as a constraint I should have investigated, not as an excuse. The source owner helped resolve the semantics, while I retained responsibility for the plan, the transformation and communication of the missed commitment.
STAGE 5 / 50:00 TO 55:00 / 5 MIN
The client times out after COMMIT. Should it resend the transaction?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would treat the outcome as unknown until I can establish whether the transaction committed. Retrying an additive operation with a new identity can duplicate its effect. I would use a stable operation ID, query the committed result or apply an idempotent transactional upsert that detects the existing operation.
The design needs to specify which side effects share the transaction. A database row may be committed while an external notification is not. For that boundary I would use an appropriate transactional outbox or another durable handoff with consumer deduplication, rather than claim the database transaction covers the external system.
Interviewer follow-ups, fully answered
1. Would replication solve that ambiguity?
Replication can copy the committed state, but it does not decide whether a client should repeat an unknown-outcome operation. I still need stable identity and an authoritative way to resolve or safely replay the operation.
2. How do you test the recovery?
I would inject failure after the server commit and before the client acknowledgement, then retry using the same operation ID. I would verify one logical effect and the expected downstream notification behavior, including duplicate delivery and delayed acknowledgement.
STAGE 6 / 55:00 TO 60:00 / 5 MIN
What questions do you have for me, and how would you close?
Role-focused closing
Shafi’s full answer + 2 answered follow-ups
I have two questions. Which product decisions are most sensitive to data correctness or freshness on this team? What would you want this hire to own independently after six months?
The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.
Interviewer follow-ups, fully answered
1. What would you focus on during your first month?
I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.
2. What would you do if the team’s approach differed from your previous company?
I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.
24 / Complete interview 11 min read · 0 min practice
Full mock C / Reliable ingestion, prioritization and operating ownership
Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.
Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.
Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.
STAGE 1 / 00:00 TO 05:00 / 5 MIN
Shafi, introduce yourself and connect your experience to this interview.
Resume-based introduction
Shafi’s full answer + 2 answered follow-ups
I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.
In this discussion I would connect database fundamentals to concrete failure modes. I would start with the row and customer decision, explain the storage and transaction mechanism, and then show how I validate and deliver the result.
For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.
Interviewer follow-ups, fully answered
1. What did you personally own rather than the broader team?
I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.
2. Why are you interested in this Data Engineer II role?
The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.
STAGE 2 / 05:00 TO 20:00 / 15 MIN
Design an incremental load with updates, deletes, late events and retries. When would you use a non-relational store?
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would begin with the source change contract: stable key, change ordering, operation type and the retention of the change log. I would preserve a raw replayable representation, validate incoming records and resolve the authoritative version per entity. A delete must participate in version selection; filtering it before choosing the latest state can resurrect an older live record.
The target write and progress checkpoint need a recovery design. I would use a transactional batch where supported, or deterministic idempotent writes with a durable manifest. A crash before checkpoint persistence must allow replay without a second logical effect. Corrections to old event dates require recomputing the affected historical aggregates, not merely the latest day.
I would choose storage from access patterns. A relational warehouse suits joins and analytical aggregation. An object store is useful for durable raw/history data. A key-value or document store can serve low-latency entity access if its consistency, indexing and update behavior match the contract. I would not move analytical joins into application code just because the input arrives as JSON.
Operationally I would track lag, accepted/rejected identities, version conflicts and consumer freshness. The publication gate would compare expected coverage and meaningful values, with an owner for rejected data and a tested replay path.
Interviewer follow-ups, fully answered
1. What if the source has no monotonic version?
I would not invent authoritative order from arrival time without agreement. I would seek a source sequence, transaction position or documented last-write rule. If none exists, I would define conflict handling and limitations with the source owner; an ingestion timestamp cannot reliably recover the source’s true order.
2. Can you delete raw data immediately after publishing?
Only if retention, audit and recovery requirements permit it and another authoritative replay path exists. I would define retention and deletion obligations explicitly. A derived table does not automatically preserve the evidence needed to repair a future semantic defect.
STAGE 3 / 20:00 TO 35:00 / 15 MIN
Two teams need your help urgently. How did you take ownership without promising both everything?
Constructed personal incident; shared-metrics ownership anchor
Shafi’s full answer + 2 answered follow-ups
Two consumers needed changes to a shared dataset in the same week. One request fixed a population error affecting an active decision; the other added a useful breakdown for a future review. Both were described as urgent, so I brought the actual decision dates and consequences into the discussion.
I confirmed the correctness issue first with a small counterexample and identified the consumer decisions it could affect. I proposed repairing that path immediately, while delivering a narrower version of the new breakdown using the existing validated fields. I made the trade-off visible to the accountable owners rather than choosing privately and surprising one team later.
I owned the shared plan, protected the critical repair from unrelated scope and gave both teams specific checkpoints. I paired with a colleague on the lower-risk breakdown after its contract was settled. When the repair passed reconciliation, I communicated the corrected scope and then completed the remaining enhancement.
The outcome was that the incorrect decision input was repaired first, and the second team received a useful bounded result with a clear follow-up. I learned to prioritize by customer consequence, deadline and reversibility, not by who describes a request most forcefully.
Interviewer follow-ups, fully answered
1. What if both requests truly had the same deadline?
I would quantify the minimum acceptable scope for each and make the capacity conflict explicit to the decision owner. I could propose additional help or reduced scope, but I would not promise two full deliveries with one team’s capacity and hope the conflict disappears.
2. How did you prevent the shared dataset from diverging?
I kept the common definition and acceptance checks in one contract. Consumer-specific views could differ, but I did not create two incompatible versions of the same named metric to make both conversations easier.
STAGE 4 / 35:00 TO 50:00 / 15 MIN
Describe a result you delivered under pressure and the control you refused to remove.
Constructed personal incident; Citi reporting anchor
Shafi’s full answer + 2 answered follow-ups
During a reporting migration, the new pipeline completed within the target window, but one reconciliation check showed a small group of misclassified accounts. The schedule pressure was to disable that check and declare the migration complete. I owned the delivery and believed the check represented a real consumer risk, not a cosmetic difference.
I traced the mismatch to a historical mapping boundary and separated the affected population from the rest. I showed the partner why matching the overall amount did not make the account assignments correct. I offered a bounded delivery of the validated scope and a focused correction rather than an open-ended delay.
I implemented the boundary fix, added an exact-transition fixture and reran key-level reconciliation. I also measured runtime again because the correction could change the physical work. We accepted the migration only after both the correctness and timing conditions passed.
The result was a delivery that met the agreed operating window with the known classification defect repaired. The lesson was that delivery and quality are connected: a fast report that directs action at the wrong accounts is not the result the customer asked for. I kept the acceptance check and improved the path that failed it.
Interviewer follow-ups, fully answered
1. How did you decide the mismatch was material?
I examined which accounts and actions changed, not only the fraction of records. The report was used for account-level follow-up, so incorrect assignment changed its purpose even when the total amount stayed the same.
2. What if the corrected pipeline exceeded the window?
I would report that neither candidate yet met the full acceptance contract, retain the validated prior path or agreed bounded output, and target the actual expensive operator. I would not trade an unacknowledged correctness defect for a runtime target.
STAGE 5 / 50:00 TO 55:00 / 5 MIN
Give me your release checklist for an analytical data product.
Technical scenario
Shafi’s full answer + 2 answered follow-ups
I would verify the output grain and keys, expected population, freshness cutoff, null/duplicate policy, meaningful totals and the consumer’s acceptance query. I would confirm that corrections, deletes and retries have deterministic behavior and that the release is coherent across dependent tables.
I would also check ownership: who is alerted, who can replay or roll back, what the consumer sees during a failure, and which changes require a definition review. I would publish the version only when those conditions are met or a clearly documented bounded exception has been accepted by the accountable owner.
Interviewer follow-ups, fully answered
1. Which check is the most important?
The most important check depends on the customer decision. I would begin with the invariant whose violation could make that decision wrong, then cover the operational risks. A generic list without a connection to the use case is weaker than a small set of meaningful gates.
2. How would you reduce alert fatigue?
I would separate hard publication gates from diagnostic signals, assign owners and test the action each alert should trigger. I would review false positives and adjust thresholds or contracts using evidence while preserving visibility of material coverage and correctness failures.
STAGE 6 / 55:00 TO 60:00 / 5 MIN
What questions do you have for me, and how would you close?
Role-focused closing
Shafi’s full answer + 2 answered follow-ups
I have two questions. Which product decisions are most sensitive to data correctness or freshness on this team? What would you want this hire to own independently after six months?
The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.
Interviewer follow-ups, fully answered
1. What would you focus on during your first month?
I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.
2. What would you do if the team’s approach differed from your previous company?
I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.
25 / Recall 2 min read · 10 min practice
The final desk card and readiness gates
The five moves
Move
Say or do this
Define
Decision, consumer, grain, key, unit, time and cutoff.
Explain
Mechanism before service names. Show where rows, bytes or state change.
Challenge
Duplicate, delete, late update, missing partition, concurrent writer, crash after commit.
Verify
Compare expected population and per-key outcomes; measure latency, cost and freshness.
Own
Name the decision, your contribution, remaining obligation and durable operating mechanism.
The short answers worth recalling
A successful job is not proof of complete or semantically correct data.
A declared key is not the same as an enforced business invariant.
A stable snapshot is not automatically a serializable concurrent history.
An idempotent target update does not make all external side effects exactly once.
A correct aggregate can conceal wrong records or wrong attribution.
Distribution, sorting, partitioning and indexing solve different physical problems.
A versioned release needs readers to honor the same version.
A revised commitment must not erase the original miss.
Product ARR and program-level impact are not automatically your personal causal impact.
Complete these gates with evidence
26 / Reference 2 min read · 5 min practice
Database fundamentals glossary
#
Term
ELI5 description
1
Grain
What exactly one row stands for.
2
Business key
The identifier that says two records describe the same real thing.
3
Surrogate key
A system-created row identity.
4
Cardinality
How many rows or distinct values exist at a step.
5
Selectivity
How much of the data a condition keeps.
6
Fanout
One input row becomes multiple joined rows.
7
OLTP
Many small changes to operational records.
8
OLAP
Analysis across many records, usually reading more than writing.
9
MVCC
Readers can see appropriate earlier versions while changes occur.
10
SCN
Oracle’s marker of database change progress.
11
Undo
Earlier values used for rollback and consistent reading.
12
Redo
Change records used during recovery.
13
Isolation
Rules controlling what concurrent transactions can see and commit.
14
Deadlock
Transactions wait on one another in a cycle.
15
CDC
Capture inserts, updates and deletes from a supported source change stream.
16
Watermark
A declared progress boundary; it is not proof that all older events arrived.
17
Tombstone
A retained deletion marker that can prevent stale resurrection.
18
Idempotency
Repeating the operation does not create another unintended effect.
19
SCD Type 2
Keep attribute versions with validity intervals.
20
Skew
One key or worker receives much more work than the others.
21
Spill
Intermediate data exceeds working memory and uses slower storage.
22
Publication version
The specific validated dataset state a consumer reads.
23
RPO
How much data loss a recovery objective permits.
24
RTO
How long restoration is targeted to take.
Advanced, Rare & Cross-Disciplinary
Write skew: concurrent decisions on an old snapshot violate a shared invariant while updating different rows.
Transactional outbox: record the intended external action in the same transaction as a state change; deliver it separately with replay-safe handling.
Bitemporal reasoning: distinguish when a fact was valid in the business from when the system learned it.
Counterfactual attribution: estimating what would have happened without an intervention before claiming causal impact.
27 / Reference 5 min read · 5 min practice
Sources, evidence boundaries and revision log
Research checked November 12, 2026. Technical explanations and exercises are original synthesis. The sources support engine behavior; they do not establish Amazon’s actual production architecture or interview questions. PostgreSQL examples use the 18 documentation series and Oracle examples the 19c series. AWS documentation describes the service at review time; verify configuration and version in real work.
Strong object operations do not provide multi-object transactions.
Local project inputs
Input
How it was used
Boundary
Recruiter Call for next role.txt
Hiring-manager competency bundle and L5/L6 discussion.
Transcript, not a confirmed calendar order or written level decision.
Amazon Job Application QnA.txt
BI/OLAP/ETL/SQL/Oracle claim coverage.
The review copy records answers; it is not independent verification of experience. Administrative inconsistencies were raised in chat.
MShafi_ResumeDE (1).pdf, existing extracted text
Career anchors and transferable technical experience.
Not confirmed to be byte-identical to MShafi_Resume_DataEngineer.pdf named in the application. Old employment wording must not establish current status.
phone screen transcription.txt
Practice gaps: SQL direction, dictionary efficiency and architecture specificity.
Observable conversation, not Amazon’s internal evaluation.
DBSBI_Loop_Deck.html and DBSBI_Full_Loop_Mocks.html
Selected mechanisms, prior story anchors and worked-practice structure.
Old dates and role assumptions were not carried forward. Constructed stories remain constructed.
Personal claims: choose evidence over a rehearsed number
The $1B+ ARR figure is reported product scale. The approximately $500M annual protection figure is a resume-reported program claim with an unestablished causal method in the supplied sources. Public company figures cannot prove personal delivery. The 40-case pilot and four-measure deadline are invented practice examples, explicitly requested for rehearsal. Replace their incident facts with your own before using them as personal history.
Revision log
Revision
Date
Change
1.0
2026-11-12
Added prominent total study time, a persistent time budget and a fix for imported progress being overwritten on reload. Created a focused Product BI hiring-manager guide. Added engine-specific references, nine executed SQL fixtures, 100 shuffled replay trials, two complete constructed LP rehearsals, three timed mocks, two-pass progress, local notes, export/import, mobile and print support.
The final verification report is stored beside the build source. Runtime controls report local-save failures visibly. This file has no remote analytics, account connection or email action. External source links open only when you choose them.
Coverage audit, revision 1.1
Added six executed SQL precision fixtures, recovery/replication/conditional-write depth, NoSQL consistency, OLAP and schema contracts, concrete Oracle operations, two explicitly constructed backup stories, eighteen additional knowledge checks and the live mastery gate. Corrected the Oracle empty-string generalization, conflicting study-route estimates and repair-queue navigation. Left navigation now shows read, practice and total time for every section. Timing is a study estimate, not a mastery or hiring prediction.
Added three complete 60-minute interview transcripts: 18 first-person primary answers and 36 fully answered follow-ups. Added complete technical fixtures and checked solutions where relevant, linked all stages to the existing timer and repair queue, and recalculated sidebar and full-guide study times. Preserved existing saved notes and migrated prior mock ratings into individual practice.