Interview guides · Civil & Environmental Consultants

Civil & Environmental Consultants Software Engineer Interview Questions & Guide 2026

Civil & Environmental Consultants software engineer prep on operations: decide what you would need to see to know a release is going wrong.

The 60-second brief

As a Software Engineer at Civil & Environmental Consultants, you play a critical bridging role between advanced technical systems and specialized engineering domains. This position drives the development and maintenance of software tools that support vital environmental, civil, and solid waste projects. Your work directly impacts how internal engineering teams process complex data, model environmental factors, and deliver reliable solutions to clients across various sectors.

Treat capacity estimation as a conversion skill rather than a table to memorise: turn a user count and an action rate into requests per second and bytes per day, then name the component that number breaks first. The figure only matters if it changes the design.

Focus areas

Diagnose failures inside environments you cannot instrument
Evolve schemas expand-contract across un-upgradable client deployments
Bound credential lifetime by the engagement end date

Overview

The challenge and excitement of this role lie in understanding the unique intersection of software development and professional engineering consulting. You will work on specialized problem spaces ranging from land development modeling software to data management platforms for environmental compliance. Because consulting environments demand high efficiency and precision, your contributions ensure that technical operations run smoothly and securely.

You can expect an environment that values practical, hands-on software application and domain familiarity. While the culture emphasizes professional expertise, you will be expected to bring robust technical capabilities from day one to support fast-paced project delivery. Success in this role requires a blend of core engineering discipline and a willingness to adapt your software solutions to real-world consulting challenges.

The Civil & Environmental Consultants interview process

Civil & Environmental Consultants candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Phone Screening

Half of this call is the part candidates treat as small talk: start date, notice period, work authorisation and its timing, location and time zone, on-call, and the number. Those are what kill offers late, after several engineers have each spent a day. Surfacing a hard constraint now costs you nothing and occasionally buys you something, since a loop compressed to fit a competing deadline can usually only be arranged if it is asked for early. The common failure is deflecting the compensation question twice, then discovering at offer stage that the band never reached your number.

What to demonstrate

Whether your hard constraints are compatible with the role before a loop gets booked: earliest start, notice period, what authorisation you hold and when it needs action, days on site, willingness to carry a pager
Whether you give a compensation range with something behind it, such as current total compensation or a competing timeline, rather than leaving the band untested
Whether your stated timeline is real, since a competing deadline raised now is something scheduling can sometimes work around and the same deadline raised at offer stage usually is not

How to prepare

Write each constraint down in one line before the call and state them as facts rather than negotiating them live under a question you were not expecting
Set your range from two or three current data points for that level and location, and name the structure you are quoting in, so the number is comparable to the one they are holding
If another process is running, say where it stands and by when, and ask directly whether this loop can be scheduled inside that window

Virtual/In-Person Interviews

An unlabelled round is first an information problem, and the cheapest information is free. Whoever schedules it can usually tell you how long it runs, who will be in the room and what they work on, whether you will be writing code and in what environment, and whether anything is being sent beforehand. Ask in writing so the answer is on record, then prepare for the two or three formats those answers still leave open instead of betting on one. What separates a strong candidate is not guessing right; it is having an opening that works whichever one it turns out to be.

What to demonstrate

Whether you can start work from an ambiguous brief, since tolerating a vague scope without stalling is the same thing the job asks for
Whether the questions you asked beforehand were ones that change your preparation, such as duration, medium and who is joining, rather than ones whose answers you could not have acted on
Whether you adapt when the round turns out to be something other than what you were told, instead of spending the first ten minutes visibly recalibrating

How to prepare

Send one short scheduling message asking four things: how long, who is joining and what they work on, whether you will be writing code and where, and whether to prepare anything in advance. Treat a vague reply as real information, since it means the round is loosely structured and you will be shaping it yourself.
Write one opening that works in any of the formats still open: restate in your own words what you have been asked to do, then ask which of two directions is more useful to them. Say it aloud until it stops sounding recited.
Set up for the two most likely formats before the call starts, with a blank editor in the language you would choose and a shared document you can type into, so a format surprise costs you nothing in the first minutes

Where candidates lose points

Depending on a client field because it is always populated

It is populated by their operational process, not by a constraint in their database, and their process changes without telling you. When it stops being populated, a permissive pipeline writes nulls or defaults into derived data and the damage spreads for weeks before anyone notices. Validate at the boundary and fail the run with failure_class='schema_mismatch' plus a redacted sample, so the failure is loud, attributable to the client's change, and cheap to diagnose. A run that fails clearly is far cheaper than a backfill that has to reconstruct which rows were silently wrong.

Tenant context leaking across a connection pool

Setting the tenant on a connection and relying on it for the rest of the request looks correct in every test that runs one request at a time. Under transaction pooling the connection goes back to the pool with that session state still attached, and the next checkout, serving a different client, inherits it; row-level security then enforces the previous tenant's policy flawlessly and returns the wrong rows. It reproduces only under concurrency, which is the load profile your integration tests do not have. Use a transaction-scoped setting, and assert that the setting matches the request's tenant immediately before the first query rather than trusting that it was set correctly upstream.

Starting work without saying what you are about to spend time on

State the plan before executing it: the approach, roughly how long it will take, and what you intend to leave hand-waved. That gives the interviewer a chance to redirect you in ten seconds rather than watching you spend fifteen minutes on the wrong sub-problem.

Never running a concrete value through the code

Trace one small input and one edge input by hand, index by index, out loud. Re-reading your own code catches design mistakes; walking a real value through it catches the off-by-one, the uninitialised accumulator and the loop that never advances.

Questions & practice

Collapse connector retry attempts into logical jobs and failure counts

Easy · hash aggregation · idempotency · retries

You are given one UTC day of connector_run rows: (connector_id, idempotency_key, attempt_no, status, failure_class, records_read, records_applied), unique on (connector_id, idempotency_key, attempt_no), up to 5,000,000 rows in arbitrary order. Retries of one logical job share the idempotency_key; the job's outcome is the status of its highest attempt_no. Return, per connector_id: the number of logical jobs, a count of terminally failed jobs by failure_class, and the dedupe hit total, meaning sum(records_read - records_applied) over terminal-succeeded attempts only. One pass over the input; state your memory bound.

Approach

Key a hash map on (connector_id, idempotency_key) and keep only the highest attempt_no seen so far plus that row's status, failure_class and record counts. One pass, O(n) time, O(d) space where d is the number of distinct logical jobs, not O(n).
Treat a repeated (key, attempt_no) as corrupt input and raise, since the table's uniqueness constraint says it cannot happen; silently overwriting hides a double-insert in the runtime.
Fold the d surviving entries into per-connector counters in a second pass over the map, not over the rows. The failure_class histogram is a small fixed-width map per connector because failure_class is an enum.
Compute dedupe hits only from terminal-succeeded attempts. A failed attempt's records_read is work attempted, not work deduplicated, and adding it counts the same source records once per retry.
If d does not fit in memory, partition the input by hash(connector_id, idempotency_key) into p files and aggregate each partition independently. Same O(n) total work, p sequential passes, memory traded for I/O; the partition function must use the full key or a job's attempts split across files.

Follow-up

A job's highest attempt is 'cancelled' but an earlier attempt succeeded. What is the job's outcome, and what does that say about who writes the cancel?
Produce this incrementally as runs land instead of as a daily batch: what state do you keep per key, and what happens when an attempt arrives out of order?
records_applied is written by your runtime, not by the client. What would you reconcile it against before anyone trusts the dedupe-hit number?

Measure credential exposure past engagement close without double counting

Medium · interval merge · sweep line · credential ttl

For one engagement you have credential_grant rows: (grant_id, principal_id, target_system_id, issued_at, not_after, revoked_at which may be NULL). The cutoff instant T is the engagement's ends_on plus access_grace_days. A grant is live over the half-open interval [issued_at, min(not_after, revoked_at)). There are up to 200,000 grants across target systems. For each target_system_id, return the total wall-clock time after T during which at least one grant was live, plus the disjoint segments that make it up. Grants that overlap must be counted once.

Approach

Clip each grant to [max(issued_at, T), min(not_after, coalesce(revoked_at, +inf))) and discard any interval whose start is not strictly before its end. O(n), and it removes every grant that already expired inside the engagement window.
Bucket the survivors by target_system_id and sort each bucket by start. O(n log n) overall, and sorting dominates the whole algorithm.
Sweep each bucket once, holding one open segment [s,e): if the next start is greater than e, emit [s,e) and open a new segment, otherwise set e = max(e, next_end). O(n) after the sort, O(1) working state, O(number of emitted segments) output.
Total exposure is the sum of emitted segment lengths, which is the measure of the union. Summing per-grant durations instead answers a different question and is unbounded above by the elapsed wall clock.
Produce a second, conservative figure that ignores revoked_at and uses not_after alone. revoked_at records that you asked for revocation; whether the client's identity provider honoured it is not something this table knows, and a self-contained token validated offline stays valid to its own expiry regardless.
If a bucket does not fit in memory, sort externally and sweep the stream, or push end timestamps into a min-heap keyed by end and pop those below the current start. Same O(n log n), memory proportional to the maximum number of concurrently live grants.

Worked solution (25 min)

Set T = 2026-06-30T00:00Z for one target system with four grants: g1 not_after 2026-06-29T12:00Z; g2 not_after 2026-07-01T00:00Z, revoked_at NULL; g3 not_after 2026-07-01T06:00Z, revoked_at 2026-06-30T18:00Z; g4 issued 2026-07-01T06:00Z, not_after 2026-07-02T06:00Z.
Clip: g1 becomes empty and drops out. g2 -> [Jun30 00:00, Jul1 00:00). g3 -> [Jun30 00:00, Jun30 18:00). g4 -> [Jul1 06:00, Jul2 06:00).
Sort by start and sweep: g2 and g3 merge into [Jun30 00:00, Jul1 00:00); g4 starts after that end, so it is emitted separately.
Union = 24h + 24h = 48h = 2,880 minutes, in two segments with a 6-hour uncovered gap between them. Summing the same three clipped intervals per grant instead gives 24 + 18 + 24 = 66h; the 18h excess is exactly the [Jun30 00:00, Jun30 18:00) stretch where g2 and g3 overlap.
Recompute ignoring revoked_at: g3 now runs to Jul1 06:00, which bridges the gap, and the union becomes the single segment [Jun30 00:00, Jul2 06:00) = 54h.

Expected result: Exposure after T = 2,880 minutes in segments [Jun30 00:00, Jul1 00:00) and [Jul1 06:00, Jul2 06:00). The conservative figure that ignores revoked_at is 3,240 minutes in one segment. Naively summing the clipped per-grant durations (24 + 18 + 24) would have reported 66 hours, more than the 54 hours that elapsed between T and the last expiry.

Verify it yourself

Union (48h) <= the clipped per-grant sum (66h) always, with equality only when no two clipped intervals overlap; here the 18h difference is the single g2/g3 overlap.
Union (48h) <= last expiry minus T (54h), since the union cannot exceed the elapsed span it lives in.
The emitted segments are strictly increasing and pairwise disjoint, and their count is 2, less than the 3 surviving grants.
g1 contributes nothing, confirming the clip ran before the sort rather than after it.

Follow-up

Three grants overlap and one of them belongs to an offboarded contractor. What must the sweep emit so exposure can be attributed per principal?
Which of your two numbers is the real bound on access if the client system validates tokens offline, and what does that imply about issuing TTLs in the first place?
Run this across 20,000 engagements as a nightly job with a fixed memory budget. What changes in the shape of the computation?

Compute the minimal re-run set that preserves idempotency keys

Hard · interval complement · idempotency keys · watermarks

One connector binding must cover [span_start, span_end). You have all its connector_run rows: (idempotency_key, attempt_no, status, watermark_from, watermark_to), with half-open ranges, where idempotency_key is derived deterministically from the connector and the range, so re-running a range reproduces its key. Only status='succeeded' counts as covered. Produce the minimal re-run plan: every instant in the span ends up covered, any sub-interval some prior run already claimed is re-run at exactly that claim's range, and genuinely unclaimed sub-intervals become new claims cut at the binding's chunk size. Explain what merging ranges would cost.

Approach

Merge the succeeded ranges into a maximal disjoint union: sort by start, sweep extending the open segment while the next start is not beyond the current end. O(n log n) time, O(n) space, and this is the only status that contributes coverage.
Take the complement of that union inside [span_start, span_end) to get the uncovered gaps, in O(u) over the merged segments. Clip at both ends so the plan never reaches outside the span.
Sweep the non-succeeded claims (ambiguous, failed, abandoned) against the gap list with a two-pointer over both sorted lists. Any claim intersecting a gap is emitted at its original watermark_from and watermark_to, byte for byte, so the derived idempotency_key reproduces and the retry rejoins the same ledger row. O(n) after sorting.
Subtract the emitted claim ranges from the gaps; whatever survives was never claimed by any attempt and becomes fresh ranges cut at the chunk size. Only these get new keys. O(u + c).
Accept that a re-emitted claim may overlap time that is already succeeded. Range bookkeeping is not what makes the retry safe; the record-level dedupe key is, and re-reading a covered window costs one extra fetch against the client's quota, which is far cheaper than a key you can no longer reproduce.
Exclude anything still in status 'running' with a live lease. Re-claiming it races the in-flight attempt, doubles the quota spend against the client endpoint, and produces two ledger writers for one key. Total plan cost is O(n log n) time and O(n) space.

Follow-up

An ambiguous run may have applied every record before the timeout. What do you read to settle it before re-running, and what is left if the client's schema offers no natural key?
Retention pruned the ledger rows for one of these keys. What does the re-run do now, and what bounds how long the ledger must be kept?
Two planners run concurrently against the same binding. What stops them emitting and executing the same plan twice?

Fix a billing report that double-counts through a join

Medium · join fan-out · aggregation · reporting

A monthly report joins engagement to work_record (engagement_id, principal_id, work_date, minutes, is_billable, status) and to invoice_line (engagement_id, period_start, period_end, amount_minor, line_type, status) in a single FROM clause, then aggregates sum(minutes), count(DISTINCT principal_id) and sum(amount_minor) grouped by engagement. Finance reports the minutes are roughly triple the timesheets while the headcount column looks correct. Explain the arithmetic, then write the corrected query for one client and one period, returning zero rather than NULL for engagements with no work.

Approach

Name the arithmetic before touching SQL: joining two independent one-to-many children of the same parent produces the Cartesian product per engagement, |W| x |L| rows. sum(minutes) is therefore multiplied by the invoice-line count and sum(amount_minor) by the work-record count. Three lines per engagement — fees, expense, credit note — is exactly the factor of three finance reported.
Explain why the headcount column looked fine: count(DISTINCT principal_id) collapses the duplication, so it is correct by accident. That is precisely why the bug survived review, and it is the reason a column agreeing with expectations is not evidence that a join is at the right grain.
Pre-aggregate each branch to the join grain first: one subquery per fact table, grouped by engagement_id, each producing at most one row per engagement. Join those results, which are now one-to-one, so no multiplication is possible by construction rather than by care.
Drive the outer query from engagement with LEFT JOINs and wrap each aggregate in COALESCE, because sum over zero rows is NULL rather than 0. For counts, count the key column and not count(*), which returns 1 for a non-matching LEFT JOIN row.
Keep credit notes inside the amount sum deliberately: amount_minor is negative for line_type = 'credit_note', so filtering them out overstates what was billed. Exclude status = 'void' instead, and state that choice in the query as a comment because the next reader will assume the opposite.
Compare cost: the fan-out plan materialises |W| x |L| intermediate rows before aggregating, while pre-aggregation reads each table once under its own index and hash-joins two small results. The base-table I/O is identical; the intermediate work differs by orders of magnitude on a large engagement.

Worked solution (25 min)

Build a fixture: one engagement with two work records of 60 and 90 minutes and three invoice lines.
Run the broken join and confirm sum(minutes) returns 450 rather than 150, and that count(DISTINCT principal_id) is unaffected.
Rewrite with one aggregating CTE per fact table and LEFT JOIN both onto engagement.
Add an engagement with invoice lines but no work records and confirm it returns 0 rather than NULL or a missing row.

Expected result: WITH w AS (SELECT engagement_id, sum(minutes) AS billable_minutes, count(DISTINCT principal_id) AS people FROM work_record WHERE work_date >= $2 AND work_date < $3 AND is_billable AND status IN ('approved','invoiced') GROUP BY engagement_id), l AS (SELECT engagement_id, sum(amount_minor) AS billed_minor FROM invoice_line WHERE period_start >= $2 AND period_end <= $3 AND status <> 'void' GROUP BY engagement_id) SELECT e.engagement_id, COALESCE(w.billable_minutes, 0) AS billable_minutes, COALESCE(w.people, 0) AS people, COALESCE(l.billed_minor, 0) AS billed_minor FROM engagement e LEFT JOIN w USING (engagement_id) LEFT JOIN l USING (engagement_id) WHERE e.client_id = $1 AND e.effective_to IS NULL ORDER BY e.engagement_id;

Verify it yourself

On the fixture the corrected query returns 150 minutes where the broken one returned 450.
Totals reconcile against SELECT sum(minutes) FROM work_record with the same predicates run on its own.
An engagement with no work records appears once with zeros, not absent and not NULL.
Adding a fourth invoice line changes billed_minor and leaves billable_minutes untouched.

Follow-up

The report now needs a per-principal breakdown alongside the per-engagement totals. Does your shape survive, or do you need a different grain entirely?
How do you stop the next analyst reintroducing this — a view, a naming convention, or a test that fails on a fixture with two children?
Why does sum(DISTINCT minutes) not fix it, and what does it actually compute?

Denormalise the entitlement snapshot without freezing the clock

Medium · denormalisation · read patterns · caching

Authorisation currently joins engagement (effective-dated, with status, ends_on, access_grace_days, effective_from, effective_to), entitlement and principal_assignment on every request, at the platform's full read rate, against tables taking a few hundred writes a day. You are asked to publish a denormalised snapshot that callers cache with a few seconds of staleness and invalidate on change events. Specify the snapshot's key and shape, say which columns you denormalise, name the one thing that must not be precomputed, and say what a cache miss does.

Approach

Justify the shape from the ratio rather than from taste: a few hundred writes a day against the whole estate's read rate is the case for a published read model. The normalised tables stay the system of record; the snapshot is derived, versioned and disposable, and can be rebuilt from source at any time.
Key it on (client_id, principal_id) with a monotonic snapshot_version and a generated_at. Consumers cache by version, refresh on a change event, and reject anything older than the staleness bound, which turns the bound into an enforced property instead of an assumption about how fast events usually arrive.
Denormalise only what changes on a write: the open engagement version's engagement_id, status, isolation_tier, data_region, ends_on, access_grace_days, and the resolved scope set. Resolving the effective-dated row once at publish time — selecting the version WHERE effective_to IS NULL — is the entire saving, because that resolution is what the per-request join was paying for.
Do not precompute is_active or any boolean that folds now() into the stored value. A write event invalidates the snapshot; the passage of time does not, so an engagement that ended an hour ago keeps authorising until something unrelated triggers a republish. Ship ends_on and access_grace_days as data and compare them to the current time at read, so time-based expiry needs no invalidation at all.
Fail closed on a miss or on over-staleness: deny, and do not fall through to the source tables. A fallback read path means the first mass cache miss converts an availability incident into either an authorisation bypass or a stampede onto the one service whose availability target already exceeds everyone else's.
State the cost you accepted: a revoked entitlement is still honoured for up to the staleness bound. That is defensible for widening access and not for narrowing it, so pair it with an out-of-band deny signal for revocation and closure, and write the residual window into the runbook instead of leaving it for an auditor to discover.

Follow-up

How do you detect the snapshot quietly diverging from the source tables, given that a wrong snapshot is silently permissive?
An amendment creates a new engagement version mid-request. Which version does the in-flight request use, and can you defend that?
The snapshot carries data_region. What stops the snapshot itself from being replicated somewhere its own residency rule forbids?

Can you walk me through your professional background and how your past projects align with consulting work?

Medium

Approach

Clarify what is being asked and what a complete answer contains.
Work from the requirement backwards to the design.
Say what you would check first and why it is the highest-information step.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

What experience do you have collaborating with multidisciplinary teams of engineers and consultants?

Medium

Approach

Say what you would check first and why it is the highest-information step.
Work from the requirement backwards to the design.
State your assumptions explicitly before working the problem.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

Make timesheet submission safe under a client's automatic retries

Medium · idempotency · retries · unique-constraints · http-semantics

work_record holds work_record_id, engagement_id, principal_id, work_date, entered_at, minutes, is_billable, status, approved_at, locked_at and invoice_line_id, and entered_at is assigned by the server at insert. A delivery-team app submits entries over mobile networks and retries any request that has not answered within ten seconds. Today POST /engagements/{id}/work-records inserts a row and returns 201, and month-end shows duplicated minutes. Design the write contract so a retried submission cannot double-book: start from whether those columns give you a natural key, then specify the key you actually use, its scope and lifetime, the response on replay, the behaviour when the same key arrives with a different body, and what the app does on each outcome.

Approach

Name the actual fault: POST is not idempotent, and a ten-second timeout leaves the outcome unknown, because the row may have committed and only the response was lost. Retrying is correct client behaviour; making the retry safe is the server's job. Two mechanisms exist and they are not interchangeable — a natural key derived from the row's own grain, or an opaque client-supplied key — so the first question is which one this grain can support.
Test the natural key against the columns you were given, and reject it. The candidate is (engagement_id, principal_id, work_date), and nothing left over separates two legitimate entries: minutes and is_billable repeat honestly, because one person can book two distinct ninety-minute billable blocks on one day, and entered_at is server-assigned so it differs on every retry — it changes at exactly the moment a dedupe key must stay fixed. A unique index on that triple therefore rejects a real second entry, trading a visible bug for an invisible one: duplicated minutes surface at month-end, while silently refused minutes surface as an under-billed engagement nobody reconciles. Prefer a natural key wherever the grain genuinely has one; this grain does not, so the key has to come from the client.
Require an Idempotency-Key header and say so in the contract. With no natural key available, a request that arrives without one cannot be made retry-safe by anything the server knows, so refuse it with its own stable code rather than accepting it and hoping. The IETF header field is still a draft, so document your own semantics rather than pointing at a spec. Scope the key to (authenticated principal, endpoint, key) so one tenant cannot collide with another's, store a fingerprint of the request body beside it, and retain it at least as long as the client's maximum retry horizon; a key pruned before the last retry re-opens the duplicate window it existed to close.
Pin the client's half of the contract, because that is where this design usually fails in practice: the key is generated once per logical entry and reused byte-for-byte on every retry of it. An app that mints a fresh UUID per attempt satisfies the header and still double-books, and no server-side check can tell that apart from two genuine entries. Specify a deterministic construction the app can rebuild after a restart rather than one it holds in memory, or a crash between attempts reintroduces the duplicate.
Same key with a different body is a distinct 4xx with its own code, never a silent replay of the first response. These are two different bugs: a genuine retry must replay, and a key reused for new content is a client-side key-generation defect that stays invisible for months if you answer it with the original body.
Handle the concurrent in-flight duplicate, which is the case a single-threaded test never reaches: two copies arrive forty milliseconds apart, both miss the key lookup, both insert. Resolve it in the database rather than by check-then-act. Put a unique index on (principal_id, endpoint, idempotency_key), insert that row in the same transaction as the work record, and let the loser take the violation. Under READ COMMITTED in PostgreSQL the second insert blocks on the winner's uncommitted key row and then raises 23505 when the winner commits; that error aborts the transaction, so take a SAVEPOINT before the insert if you intend to read the winner back on the same connection. Note also that INSERT ... ON CONFLICT DO NOTHING returns no row on conflict, so the handler needs an explicit SELECT afterwards, or ON CONFLICT DO UPDATE ... RETURNING, to obtain the winning row.
Answer the loser that arrives before the winner has stored its response with a 409 carrying its own code and a Retry-After, not with a guessed body. Store each response with its status code so a later replay is byte-identical, and write the app's branch table: 201 and 200 both mean submitted; the different-body 4xx and the missing-key 4xx stop and surface to the user; a 409 against locked_at means re-read; the in-progress 409 retries after Retry-After; timeouts and 5xx retry with the same key and the same body. No branch may retry a terminal 4xx and no branch may drop a timeout.

Worked solution (30 min)

List the columns that could separate two entries on the same engagement, principal and date, strike out each with the reason it cannot, and write down the legitimate row pair a unique index on that triple would reject.
Write the handler as one transaction containing both the key row and the work record, and mark the commit point explicitly.
Enumerate four arrivals of the same request — first, retry after commit, retry after a crash before commit, and a concurrent duplicate whose winner has not yet stored a response — and give each a status code and body.
Write the different-body case, including how the body fingerprint is computed and which fields are excluded from it.
State the key's scope and retention, and tie the retention number to the client's retry horizon rather than to a round figure.
Write the app's branch table and check that no branch retries a terminal 4xx and none drops a timeout.

Expected result: A written finding that work_record has no natural key at this grain, so the endpoint requires a client-supplied Idempotency-Key scoped to (principal, endpoint, key), unique-indexed and stored with a body fingerprint and the response; replays return the original status and body; same-key-different-body returns a distinct 4xx; a request with no key is refused rather than accepted; the concurrent duplicate is settled by the unique violation rather than a pre-check, with a loser whose winner has not yet responded answered 409 plus Retry-After; and only timeouts, 5xx and that 409 retry.

Verify it yourself

Firing the same request fifty times concurrently leaves exactly one work_record row, one 201 and forty-nine 200s, all naming the same work_record_id.
Killing the process between the insert and the response leaves the retry returning the committed row rather than creating a second one.
Two genuinely distinct entries by the same principal on the same engagement and work_date, submitted under different keys, both persist.

Follow-up

The period has been billed and locked_at is set when a retry finally lands. What status does that retry get, and what does the app show the user?
What would have to be true of work_record for a natural key to be the better answer here, and what would you still keep the header for?
How long do you keep idempotency keys, and what exactly breaks on the day you prune one too early?

One connector burns a client's entire rate budget

Easy · poison message · retry loop · rate limiting

A client reports that your integration is exhausting their API quota, and every connector binding for that client now fails with client_429. For one binding, connector_run holds 300 rows sharing one idempotency_key with attempt_no incrementing, all with status 'running', finished_at NULL and failure_class NULL, created exactly 120 seconds apart over the last ten hours. The run lease is 120 seconds. Workers in that pool show restart counts in the hundreds. Other clients are unaffected. Diagnose in order, say who is at fault, and state what stops one range doing this again.

Approach

Split client fault from your fault before anyone contacts the client. Group the last hour's failures by client and by binding: every binding for one client failing while other clients are clean means a resource shared with that client is exhausted, and the only thing you share with them is their quota.
Read the ledger's shape, which is the entire diagnosis. Three hundred attempts under one idempotency_key, none with a terminal status and none with a failure_class, spaced at exactly the lease interval. A handled failure writes failure_class and finished_at; writing nothing at all means the worker was killed before it could classify. The exact spacing names the killer as lease expiry, not the client endpoint.
Quantify the damage from the same rows: records_applied is zero across all 300, while each attempt got far enough to pull pages. The binding consumes quota continuously and applies nothing, which is why the other bindings for that client see 429 and the faulty one does not.
Reproduce offline. watermark_from and watermark_to are recorded, so replay that exact range against a captured payload with the lease disabled and find what exceeds 120 seconds: a page far larger than the others, a validator input that degrades badly, or an unbounded in-memory accumulation over the range.
Fix the loop before the payload. Cap attempt_no, move the range to status 'abandoned' with an alert, and hold the watermark rather than advancing past it, because a silent gap is data loss and skipping must require a human decision. Then make the work fit the lease: stream and chunk the page, renew the lease only while progress is provable, and bound the page size at the boundary.
Add the detector this evaded. Alert on attempts that never reach a terminal state, not only on failure counts, because a run that never finishes never increments any failure metric.

Follow-up

The client asks how much of their quota you consumed. Which rows answer that, and what would you have to add to answer it exactly?
The abandoned range contains records you still need. What is the safe way to get past it without breaking the at-most-once guarantee?
Lease renewal introduces a new failure: a worker that renews steadily but makes no progress. How do you catch that one?

Suggested practice checklist

For someone who has spent the last few years shipping features and reading other people's code, and who has not solved a timed problem from a blank file in a long time. Five days rebuild the primitives and the patterns that sit on them, working from invariants rather than remembered solutions, and the last two attach that back to the rest of the loop.

Day 1 — Rebuild the primitives by implementing them

Implement a dynamic array with doubling growth and an operation counter, then change the growth rule to add a fixed sixteen slots instead, and time both for n of ten thousand, a hundred thousand and a million. The fixed-increment version resizes n/16 times at O(n) each, so its total work is quadratic; doubling is what makes append amortised constant.
Implement a hash map with separate chaining and a load-factor resize, then insert ten thousand keys engineered to land in one bucket and record what happens to lookup time, so that average-case O(1) becomes a claim with a stated precondition rather than a reflex.
For dynamic-array append and hash-map insert, write down which cost is amortised rather than worst-case, which single operation pays the whole bill, and what a system with a hard per-operation deadline would have to do instead.

Deliverable: Two working implementations plus a timing table showing the input at which each structure's advertised complexity stops holding.

Day 2 — Arrays under an invariant: two pointers, sliding window, binary search

Solve longest-subarray-with-sum-at-most-K using a sliding window, then run it on an input containing negative numbers and watch it return the wrong answer: extending the window only moves the sum monotonically when every element is non-negative, and that precondition is the whole reason the technique works.
Write the binary search that finds the first index satisfying a predicate rather than an exact value, put the loop invariant above the loop in a comment, and verify termination on the two inputs that break careless versions: the empty range, and a range where every element satisfies the predicate.
Compute the midpoint as lo + (hi - lo) / 2 and write one line on why the obvious (lo + hi) / 2 is a genuine defect in a fixed-width integer type and a non-issue in a language with arbitrary-precision integers.

Deliverable: Three solved problems, each with its invariant written above the loop, plus one recorded input on which the sliding window is provably wrong.

Day 3 — Sorting, heaps, and the greedy argument that has to be proved

Solve one top-k problem three ways, by full sort, by a size-k heap, and by quickselect, then write the values of n and k at which each becomes the right choice, along with quickselect's quadratic worst case and why a randomised pivot makes that unlikely rather than impossible.
Implement bottom-up heapify and count sift-down steps to confirm it does linear work rather than n log n, because most nodes sit near the bottom of the tree and therefore move only a short distance.
Take interval scheduling by earliest finishing time and write the exchange argument out in full: given any optimal schedule, swapping in the earliest-finishing interval keeps it feasible and no smaller. Then construct the weighted variant where that same greedy fails and name what has to replace it.

Deliverable: A three-way top-k comparison with measured crossover points, one written exchange argument, and one counterexample to a greedy rule that looks almost identical.

Day 4 — Recursion, memoisation, and the step to a table

Take one problem with overlapping subproblems, such as edit distance or coin change, instrument the plain recursion with a call counter to show the blow-up, then add memoisation and re-count.
Convert the memoised version to a bottom-up table and state the two properties you relied on: each subproblem's result depends only on its arguments, and the dependencies form a DAG you can enumerate in order.
Rewrite one deep recursion with an explicit stack, then find the input length at which the original hits the interpreter's frame limit, which defaults to about a thousand frames in CPython, so you know when the rewrite is required rather than decorative.

Deliverable: One problem in three forms, naive, memoised and tabulated, with call counts for each and the input length at which recursion depth becomes the binding constraint.

Day 5 — Graphs, where most of the work is choosing the traversal

Implement BFS and DFS over one adjacency list, then answer for each which finds a shortest path in an unweighted graph and which you would use to detect a cycle in a directed graph, including why the in-progress versus finished distinction matters for the second.
Implement topological sort by in-degree, feed it a graph containing a cycle, and confirm the failure signature is that fewer than V nodes come out rather than an exception, then note that the order it produces is one of several valid ones.
Run a shortest-path search on a graph with a single negative edge weight and show the wrong answer, then write the precondition Dijkstra actually needs, non-negative weights, because it finalises a node's distance the first time that node is popped, and name the algorithm you would switch to and its own limit.

Deliverable: A small graph library with BFS, DFS and topological sort, plus two inputs that produce documented wrong answers under the wrong algorithm choice.

Day 6 — One day for everything that is not an algorithm

Sketch one system only to the depth a coding-heavy loop tends to reach: the endpoints, what the service stores, and the single query pattern that decides the schema. Stop at twenty-five minutes.
Prepare the project answer for an interviewer who codes, which means rehearsing the two levels they push to: the specific thing you built, and why you chose that approach over the alternative they will name. Open with a number and be ready to say what it excludes.
Prepare the answer to what you would do differently, choosing a real technical mistake with a specific fix rather than a complaint about process or staffing.

Deliverable: One design sketch at endpoint-and-schema depth, plus a project answer rehearsed to two levels of follow-up.

Day 7 — Solve out loud, under time

Do three timed problems at twenty-five minutes each in a plain editor with no autocomplete and no execution until the end, then tally separately the failures that were syntax and the ones that were approach, because those two numbers call for different fixes.
Narrate one solution from the first sentence, stating the approach and its complexity before writing any code, and rehearse the sentence you will use when you realise mid-solution that the approach is wrong.
Re-solve from blank the two problems you were slowest on this week and compare the times against the day they first appeared.

Deliverable: A recording of one fully narrated solution and a tally that separates syntax failures from approach failures.

'We rewrote it in a better framework' is not a story

A migration is a cost you chose to pay, not an achievement. The story is what the old system made expensive, what you measured before committing, what kept serving traffic during the cutover, and what you would have done if the numbers had come back flat. Without those, a rewrite reads as taste.

How do you handle working autonomously in a fast-paced consulting environment?

Medium · behavioural and engineering judgement

Approach

Pick a story where you made the decision, not one where you watched it.
State the situation in two sentences and spend the rest on the reasoning.
Close with what you would do differently, concretely.

Follow-up

What did you decide not to do, and why?
How did you know your change caused the improvement?

Describe a time when you had to manage competing technical priorities on a demanding project.

Medium · behavioural and engineering judgement

Approach

Name the disagreement and how you resolved it with evidence.
Give the blast radius: what could have broken, and what you measured.
Close with what you would do differently, concretely.

Follow-up

How did you know your change caused the improvement?
What would you do differently if you ran that again?

Can you walk through your experience integrating custom scripts or tools with existing technical frameworks?

Medium · behavioural and engineering judgement

Approach

Close with what you would do differently, concretely.
Name the disagreement and how you resolved it with evidence.
State the situation in two sentences and spend the rest on the reasoning.

Follow-up

How did you know your change caused the improvement?
What would you do differently if you ran that again?

Describe a situation where you encountered a significant technical roadblock and how you resolved it.

Medium · behavioural and engineering judgement

Approach

Pick a story where you made the decision, not one where you watched it.
Name the disagreement and how you resolved it with evidence.
Give the blast radius: what could have broken, and what you measured.

Follow-up

What did you decide not to do, and why?
How did you know your change caused the improvement?