Skip to content
InfraDrill

Data modelling & storage in a Senior interview

Our data questions start from a statement that does something other than its author meant: an upsert that stops matching after a migration, an index the planner will not use, a read that keeps a lock for forty minutes. They ask what the database does with it, and why. They cover PostgreSQL and other databases, the managed stores of AWS and Google Cloud, and the data side of system design.

An axis of our Senior backend and Senior fullstack targets.

Check whether this one is a gapTry the free demo

The check comes with every pass and covers all the competencies of the target you choose: 8 for Senior backend (about 16 minutes) and 10 for Senior fullstack (about 20 minutes). It reports each one as Ready, Borderline or a Gap for this level. It never predicts a pass.

Patterns our questions are designed to test at Senior

These are the distinctions the questions on this axis are written to separate — our judgement from writing them and having them reviewed, not a measurement of candidates. We have run no study, so nothing here is a statistic and none of it is phrased as one.

  1. 1.

    A statement credited with a promise it does not make

    In PostgreSQL, INSERT … ON CONFLICT DO NOTHING RETURNING id returns no row when the row already exists, and an automatically updatable view accepts an insert that its own WHERE then hides, unless it was created WITH CHECK OPTION. DynamoDB's TTL deletes an expired item in the background, typically within a few days after expiry, and the item stays readable until then, so finding a session in the table does not mean it is still valid. Our questions ask what the statement or setting promises, and what the application still has to check.

  2. 2.

    A lock nobody meant to hold

    With psycopg 3's defaults, the first SELECT opens a transaction that lasts until commit, so a job that reads orders and then spends forty minutes calling a partner API holds an ACCESS SHARE lock on the table the whole time. A migration's ALTER TABLE waits behind it for ACCESS EXCLUSIVE, and queries that arrive after the ALTER wait behind it. Ten workers that each take the first pending row FOR UPDATE wait on one another and run one at a time; SKIP LOCKED lets each take the next free row. Our questions ask who holds the lock, in which mode, and who is queued behind it.

  3. 3.

    An index that is there and is not used

    A partial index on status = 'active' serves only queries whose own condition PostgreSQL recognises as implying that predicate, and beyond a few simple inequalities the recognition is textual. An index-only scan still reads the table for entries on pages not marked all-visible; a large update clears those marks, and vacuum sets them again only on pages whose rows every transaction can see. A GIN index built with jsonb_path_ops is smaller and does not serve the key-exists operator ?. Our questions ask what the planner can prove from the query and from the table's current state.

  4. 4.

    A transaction asked to cover more than it does

    SERIALIZABLE in PostgreSQL prevents write skew by aborting a transaction with a serialization failure when it detects a dangerous pattern, so it works only in an application that retries the whole transaction. A row committed and an event then published to a broker are two writes with no transaction around both, and moving the publish before the commit trades one failure for the other: an event for a row that was rolled back. Our questions ask where the transaction ends and what lies outside it.

What a strong answer sounds like: A strong answer names the rule the database applies in the case given (a lock mode, an isolation level, what the planner can prove) and what that rule leaves for the application to handle.

How the check measures it

3 questions drawn on this axis alone — 2 medium and 1 hard for a Senior target, because the bar is the level. Hard counts for one and a half times a medium, which is what makes the verdict relative:

Medium rightHard rightVerdict
2 of 21 of 1Ready
1 of 21 of 1Ready
2 of 20 of 1Borderline
0 of 21 of 1Borderline
1 of 20 of 1Gap
0 of 20 of 1Gap

Three questions is coarse and the report says so beside every verdict. It is enough to separate this axis from the others in your target, which is what the check is for — there is no total, no percentage and no single number anywhere in the report.

What there is to practise

233 questions on this axis
161 medium, 72 hard. Easy is not drawn for a Senior target.

Every answer cites the source it was checked against.

Drawn from Databases & SQL (136), AWS (48), System Design (21), Google Cloud (14), Python (7).

Practised in the System design and Technical deep dive rounds
Design a system to requirements and defend the trade-offs: storage, messaging, caching, capacity and cost. How things actually work and why they fail: networking, concurrency, performance, security, and diagnosing a symptom to its mechanism.

Practice is arranged by interview round rather than by topic, so you can work through it one round at a time.

Questions here most often also test technical judgment: decisions and trade-offs, debugging and incident reasoning, distributed systems and reliability & failure modes. Few questions on this axis test it alone, which is why the check reports every competency separately rather than averaging them.

One of them, in full

A question from the free demo, not written for this page. A demo quiz opens with it; the answer, the explanation and the source come when you submit — no account needed.

Data modelling & storagehard

A team rehearses restoring its PostgreSQL 16 shop database onto a new cluster from a pg_dump -Fc shop backup. In production the tables are owned by app_owner, and the application connects as app_rw, which holds SELECT, INSERT and UPDATE grants on them. The runbook creates the database, runs pg_restore -d shop shop.dump as the postgres superuser and compares row counts. Every count matches; pg_restore reported two errors and "errors ignored on restore: 2". The team then creates the app_rw role with its password and starts the application. What does the application get, and what does the runbook lack?

Answer it on the demo

One axis is not the loop

This axis is one of 8 in our Senior backend target and one of 10 in Senior fullstack, and being strong here says nothing about the others. The check covers all of them in one sitting and reports each separately, so what comes back is a profile rather than a score.

See the passesWhat we check for the whole Senior backend targetWhat we check for the whole Senior fullstack target