Interview guides · Azuria Water Solutions

Azuria Water Solutions Software Engineer Interview Guide

Azuria Water Solutions Software Engineer interview guide: reported rounds, schema, offline sync, GIS and telemetry questions, and a 7-day prep plan.

The 60-second brief

Candidates and role descriptions place Azuria Water Solutions in water infrastructure, with corrosion prevention and pipeline rehabilitation as the project work its software supports. The engineers described here build digital systems, data pipelines and field applications for Corrosion Engineers, Field Engineers and project managers. Reported responsibilities include taking in telemetry from corrosion monitoring sensors, connecting to geographic information systems (GIS), maintaining proprietary databases and supporting real-time monitoring and field data collection. The software has to work in offices and at remote sites where connectivity is poor, and the clients named are municipal and industrial customers across the country.

This guide is for candidates interviewing for the Software Engineer role. It covers the three reported rounds, the eleven reported questions (database and sync design, query tuning, GIS integration, data-loss debugging, legacy and technical-debt trade-offs, and four collaboration stories), the backend and SQL skills the requirements list, and a seven-day plan. Candidates report that corrosion or water-utility experience is not required, so the guide teaches the engineering side rather than the domain.

Candidates report three rounds over about 3-5 weeks: an HR screening, a technical assessment (a coding test or a system design review) and a final round with cross-functional managers. Other reports describe a single conversational interview on some teams, and timelines that stretch to several months on others.

Focus areas

Engineering Domain Knowledge (Corrosion)
Software Engineering (General)
Engineering Domain Knowledge (Field Engineering)

Overview

The work is software that sits between physical assets and the people who maintain them. Data arrives from corrosion monitoring sensors and from crews entering inspection results in the field, and engineers downstream act on it. That makes ingest reliability, data integrity and error recovery the common thread in the reported questions: a schema for inspection logs, an offline-first sync for a mobile app, faster queries over historical telemetry, and a GIS integration. Two reported example scenarios are worth practising end to end as design problems: ingesting daily cathodic protection readings from thousands of remote test stations, and refactoring a legacy reporting tool whose queries have become slow for large municipal clients.

The stack candidates describe is conventional. Requirements list a backend language (C#, Java or Python), relational databases and SQL, and clean API design. Nice-to-have items are mobile development, AWS or Azure, GIS integrations and experience with IoT or industrial monitoring data; MQTT, AMQP and GIS formats such as shapefiles are listed as advanced topics. You do not need all of them, but you should be able to explain delivery guarantees on a message broker (at-least-once delivery means idempotent consumers), what a coordinate reference system is and why mixing them corrupts map data, and how an index, a partition and a rollup table each change a query plan.

The rest of the loop is about working with people who are not engineers. Candidates report collaboration questions on explaining technical limits to project managers, adapting when requirements shift, resolving design disagreements and learning a field crew's workflow. They also report advice to favour practical, working solutions over elaborate architectures, because low bandwidth and legacy data formats are everyday constraints. Prepare both halves: be able to sketch a resilient design on a whiteboard, and have four real stories with outcomes ready. Since reports say the format and pace vary by team, ask your recruiter early which stages apply to you.

The Azuria Water Solutions interview process

Candidates report the stages below, but the order and format vary by team, and some teams use one conversational interview instead of separate rounds.

HR Screening

Candidates report this first stage as an HR screening that checks fit and qualifications. In the single-conversation variant some candidates describe, the discussion covers your technical background, your interest in the industry and how you match the team's current projects. Treat it as the place to connect your experience to the role: backend work in C#, Java or Python, SQL, APIs, and anything touching mobile, cloud, GIS or device data. Reports also warn that timelines can be unpredictable, so use this stage to ask which stages your team runs and how you will be kept informed.

What to demonstrate

Fit with the role and how your stated qualifications line up with the listed requirements
Your interest in software for physical infrastructure and field operations
How clearly you describe your own experience and communicate with a non-engineer

How to prepare

Write a short account of your career that puts your backend language, SQL and API experience first, then any mobile, cloud, GIS or IoT work.
Prepare an honest answer to why software for water infrastructure and field crews interests you, naming one concrete thing you have learned about the field.
Ask which stages your team runs (staged rounds or a single conversation) and what format the technical assessment takes.
Have your location, hybrid-work and availability constraints ready, since candidates report arrangements depend on the team.

Technical Assessment

Candidates report a technical assessment described as a coding test or a system design review. The format is not specified per team, so prepare for both. The reported design material includes a database schema for corrosion inspection logs across regions, an offline-first sync strategy for a field mobile app, query tuning over large volumes of historical sensor telemetry, and integrating third-party GIS or mapping APIs; the scenario questions on debugging data loss, legacy blockers and technical debt are the kind of reasoning a design review invites. Candidates also report advice to write clear, working code instead of chasing exotic algorithms, and to stress data integrity and error recovery in every design.

What to demonstrate

Working, readable code in your chosen language if the stage is a coding test
Relational schema, API and integration design if the stage is a system design review
Whether your designs protect data integrity and recover from failures, which reports single out
Practical choices that fit low bandwidth and legacy data formats

How to prepare

Design the inspection-log schema on paper (regions, assets, inspections, measurements, corrections) and write the three queries it must serve, with indexes.
Design the offline-first sync for field crews: local store, operation queue, client-generated ids, idempotent upload, conflict policy.
Build a million-row telemetry table and tune a latest-reading-per-station query and a daily aggregate using EXPLAIN.
Sketch a GIS integration with your own adapter interface, one internal coordinate system, caching and an outage fallback.

Final Round

Candidates report a final interview with cross-functional managers about overall fit and alignment. Because managers from outside engineering are in the conversation, this is where stories about working across functions fit: explaining a technical limit to a project manager, changing course when requirements shift, settling a design disagreement, and learning a field crew's workflow. Reports also suggest using the stage to ask how the managers' teams collaborate, which helps you judge whether the team is aligned on what the role needs.

What to demonstrate

Fit and alignment with the team, as the stage is described by candidates
Ability to explain technical ideas in plain language to non-engineers
Adaptability when scope or priorities change
Empathy for the field technicians and project managers who use the software

How to prepare

Write four story outlines (situation, your decision, result, what you would change) for the reported collaboration questions, each with a concrete outcome.
Rehearse explaining one technical limit, such as sync conflicts or data latency, in terms of schedule, cost and risk, without jargon.
Prepare two or three questions about how the team works with field operations, hardware specialists and database administrators.
Pick an example where you worked through ambiguous requirements or stepped outside your comfort zone, and be ready to give the details.

Where candidates lose points

Presenting an offline sync design that stops at 'store locally and upload later'

Cover what happens when an upload is interrupted halfway, how a retry avoids duplicate records (client-generated ids and server-side idempotency), how conflicting edits to the same inspection are resolved, and how a failed record is surfaced to the technician instead of silently dropped.

Answering the telemetry query question with only 'add an index'

Work through the query's shape: filter and sort columns, a composite index matching them, time-range partitioning, pre-aggregated rollups for dashboards, retention for old readings, and a plan check with EXPLAIN before and after. Say which queries each choice makes slower.

Designing for the happy path in the schema, GIS and data-loss questions

Reports single out data integrity and error recovery. For each design say how a bad or missing record is detected (constraints, sequence numbers, counts at each hop), where it is stored for review, and how a correction is recorded without erasing the original.

Proposing an elaborate architecture for constraints that call for something simple

Candidates report that low bandwidth and legacy data formats are everyday constraints and that practical solutions are preferred. Start with the smallest design that works, then name the specific trigger that would make you add a broker, a service split or a rewrite.

Telling collaboration stories with jargon, no outcome, or an unclear personal role

Use a four-part outline for every story: the situation in two sentences, the decision you personally made, the result with something concrete, and what you would change. Translate any term a project manager would not know.

Questions & practice

Archive a resource graph without breaking live references or recursing

Medium · graph traversal · topological order · tenant isolation

Resources reference other resources within a tenant; for the largest tenant the reference table holds up to 2,000,000 nodes and 8,000,000 edges. Archiving a resource must archive everything reachable from it that nothing outside the set still references, refuse when a live external referrer exists, and terminate when references form cycles, which they legitimately do. Produce the archive order and the refusal list, targeting O(V+E). Say what stops the traversal crossing a tenant boundary, and why recursion is the wrong control structure at this size.

Approach

Load the subgraph with the tenant predicate on both endpoints of the edge, not only on the side you started from. Scoping the left table alone is the classic cross-tenant leak: one mis-entered edge then pulls another tenant's resources into the traversal and, worse, into the archive.
Traverse iteratively with an explicit stack. A 2,000,000-node graph can hold a chain deep enough to exhaust a native stack in the low tens of thousands of frames, and that failure is a process crash rather than an error you can return.
Treat cycles as data rather than corruption: compute strongly connected components with Tarjan in O(V+E) using its own explicit stack, then condense. The condensation is a DAG, so a topological order over it gives the archive order, and every member of a component archives in one transaction because no order within a cycle is valid.
Decide refusals with reverse edges. A candidate is archivable only if every in-edge originates inside the candidate set, so build the transpose or count in-degrees restricted to the visited set, and emit each blocked resource with the id of the external referrer, which is the only part of the answer an operator can act on.

Follow-up

The graph is read in one query and the archive writes a minute later. What can change in between, and how do you make the write safe?
The candidate set is 400,000 resources. Is that one transaction, and if not, what does a half-finished archive look like to a reader?

Collapse a redelivered event batch into per-aggregate high-water marks

Easy · hashing · at-least-once · aggregation

You drain a batch of up to 5,000,000 events, each (aggregate_id BIGINT, aggregate_version INT, event_type, payload). The log guarantees order within one aggregate only; the batch merges 64 partitions, and a relay failover has redelivered a range, so an older version for an aggregate can appear after a newer one. Given a map of last_applied_version per aggregate, produce the events worth applying, at most one per (aggregate_id, version), plus the count discarded. Target O(n) time. State the memory for 2,000,000 distinct aggregates and what you do when it does not fit.

Approach

One pass, one hash map from aggregate_id to the highest version kept, and a discard counter. An event whose version is at or below last_applied_version for its aggregate is dropped without further work, which is the whole reason the event carries its version rather than a delta. O(n) expected time, O(d) space in distinct aggregates.
Keep the maximum, never the last occurrence. The redelivered range means the final appearance of an aggregate in the batch can be an older version than one seen earlier in the same batch, so last-wins applies stale state over newer state and the projection regresses with no error anywhere.
Cost the memory instead of calling it large: an 8-byte key plus a 4-byte version is 12 bytes of payload, and an open-addressed table held at a 0.7 load factor costs roughly 17 bytes per entry before per-slot metadata, so 2,000,000 aggregates is tens of megabytes in a native layout and several times that in a runtime that boxes both key and value.
If the distinct set exceeds memory, partition on hash(aggregate_id) mod P and reduce each partition independently. Every event for one aggregate hashes to the same partition, so the per-partition result is exact and the merge is concatenation rather than a second reduction.

Follow-up

The payload is a patch rather than a snapshot, so applying only the highest version loses the intermediate changes. What changes in your reduction?
How do you detect that version 7 arrived while version 6 was never delivered, and what should the consumer do about the gap?

Merge partitioned event streams into one ordered feed with bounded lateness

Hard · k-way merge · watermarks · out-of-order streams

The read-model service consumes 64 log partitions carrying about 4,000 events per second in total. Each partition is ordered within itself, but partitions drift by up to 30 seconds, and the activity feed must present a tenant's events in occurred_at order. Produce the merge. State its complexity, the buffer it requires in events and in bytes, what happens when one partition is idle, and what you do with an event that arrives after you have already emitted its position. Payloads average 1 KB.

Approach

Merge with a min-heap over the 64 partition heads keyed on (occurred_at, event_id): O(log P) per event and O(n log P) overall. The tie-break on event_id is what makes the output deterministic when two partitions carry the same millisecond, which matters because the feed is paginated and a non-deterministic order reorders pages under the reader.
Emitting the heap head is only correct once every partition has produced everything up to that timestamp, so the emit condition is a watermark: the minimum across partitions of the highest occurred_at seen, less the allowed lateness. Events are held until the watermark passes them, which is what turns individually ordered streams into a jointly ordered one.
Size the buffer from the lateness rather than guessing: 4,000 events per second times 30 seconds is 120,000 buffered events, and at 1 KB each about 120 MB of heap. That number is the real price of the ordering guarantee and belongs in front of whoever asked for it.
Handle the idle partition explicitly, because it fails the feed rather than corrupting it: a partition with no traffic never advances its own maximum, so the watermark freezes and output stops entirely. Either every partition emits a periodic idle marker carrying the broker's current time, or the watermark falls back to wall clock for a partition silent beyond a threshold.

Follow-up

The lateness budget is raised to five minutes. What is the new buffer, and what besides memory changes?
The consumer restarts. Where does it resume from, and what does the feed look like for the first 30 seconds?

Explain why the owner filter ignores the listing index

Medium · composite index · query plans · sort elimination

The only index on resource is (tenant_id, status, updated_at DESC, resource_id DESC). A new endpoint returns one user's resources across all statuses, newest created first: WHERE tenant_id = $1 AND owner_user_id = $2 ORDER BY created_at DESC LIMIT 20. On a tenant with 2M rows it takes 900 ms and EXPLAIN shows a sort above a large scan. Explain precisely why the existing index cannot serve it, give the index that can, and state which of these the new index still will not help: owner_user_id alone across tenants; the same query ordered by updated_at. PostgreSQL 16.

Approach

Separate the two jobs an index does. For filtering, a composite btree is seekable only on a left prefix, so with no predicate on status the scan can at best range over tenant_id and test owner_user_id per row; PostgreSQL 16 has no btree skip scan to jump the unconstrained column.
For ordering, the index is sorted by (status, updated_at) within a tenant and not by created_at, so the LIMIT cannot stop early: every matching row is read and then sorted. That is the 'Sort Method: top-N heapsort' line, and it is why the plan reads 2M rows to answer with 20.
Derive the replacement from the access path — equality, equality, then the ordering column: CREATE INDEX CONCURRENTLY ON resource (tenant_id, owner_user_id, created_at DESC). The scan seeks to the (tenant, owner) range and walks 20 entries in order, so the Sort node disappears along with the row-read.
Treat INCLUDE (title, status) as conditional, not free. An index-only scan still visits the heap for any row whose page is not marked all-visible, so on a table taking 1.2k writes/second the win depends on autovacuum keeping the visibility map current, and the wider index costs more on every insert.

Follow-up

90% of rows are status='active'. Would a partial index WHERE status = 'active' change your answer, and for which of the three queries?
A dashboard runs this for 40 owners in one page load. What changes about the design?

Denormalise tenant onto revisions and backfill it live

Hard · denormalisation · online migration · backfill

resource_revision (revision_id, resource_id, version, actor_user_id, change_kind, patch, request_id, created_at) has 400M rows and no tenant column; tenant_id lives only on resource. Two reads need it: a tenant-scoped audit feed ordered by created_at DESC, and an offboarding purge. Both join back to resource today. Justify adding tenant_id to resource_revision against those two reads, name the anomaly the copy introduces and the constraint that prevents it, then give the ordered migration for a live table taking 1.2k writes/second — the lock each step takes, how the backfill is batched, and where each step stops being reversible. PostgreSQL 16.

Approach

Justify from the access path rather than from taste. Without the column, the audit feed either scans resource_revision by created_at and discards other tenants' rows, or resolves the tenant's resource_ids first and probes with them — both proportional to the tenant's whole history rather than to one page. With (tenant_id, created_at DESC, revision_id DESC) it is a seek that stops at 50 rows, and the purge becomes a ranged delete instead of a join.
Name the cost exactly: a second copy of a fact can disagree with the first. Make the disagreement unwritable rather than documented — add UNIQUE (resource_id, tenant_id) on resource so it can serve as a foreign-key target, then FOREIGN KEY (resource_id, tenant_id) REFERENCES resource (resource_id, tenant_id) on the revision table. A revision can then only ever carry its parent's tenant.
Step one, expand: ALTER TABLE resource_revision ADD COLUMN tenant_id BIGINT NULL, with no default, so it is a catalogue change and no rewrite. It still needs ACCESS EXCLUSIVE for an instant, and that instant queues behind the longest open transaction on the table while every later query queues behind it — set lock_timeout to 2s and retry rather than wait.
Step two, dual-write: deploy the writer that populates tenant_id on every new revision while reads still use the join. Reversible by redeploying the previous build, because nothing reads the column yet.

Follow-up

The backfill is half finished and a rollback is required. What state is the table in, and what does the previous build do with a half-populated column?
How do you verify the backfill actually finished, given rows are still being inserted while it runs?

How would you design a database schema to track corrosion inspection logs across multiple geographic regions?

Medium · Technical & System Architecture

Approach

Name the core tables first: region, asset (pipeline segment or test station, with region_id), inspection (asset_id, inspector_id, inspected_at as a UTC timestamptz, method) and a child measurement table (inspection_id, measurement_type, value, unit). That way one inspection can hold many readings without adding a column for every new reading type.
Store region on the asset and look it up through a join, or copy region_id onto inspection only if you partition or filter by it. Copying means you have to decide what happens to old rows when a region boundary moves.
Index for the two main reads: (asset_id, inspected_at DESC) for 'latest inspection per asset' and (region_id, inspected_at) for regional reports. Store location as a PostGIS geometry(Point, 4326) with a GiST index if you run radius or polygon queries; plain lat/lon columns are enough if you never do.
Treat inspection logs as append-only. A correction becomes a new row that points to the row it supersedes, which keeps the audit trail. Put a unique, client-generated UUID on each inspection so a field device that resubmits cannot create duplicates.
At scale, range-partition inspection by inspected_at so old data can be pruned and archived. Say the trade-off out loud: partitioning by region instead makes cross-region time-range queries touch every partition.

Follow-up

How would you return the most recent inspection for every asset in a region efficiently (DISTINCT ON, ROW_NUMBER() over a window, or a LATERAL join)?
Different inspection methods record different measurements: would you use per-type tables, a key-value measurement table or a JSONB column, and what does each cost you?
An asset is reassigned to a new region after a boundary change: what happens to its historical reports?

Explain how you would implement an offline-first data synchronization strategy for a mobile application used b

Medium · Technical & System Architecture

Explain how you would implement an offline-first data synchronization strategy for a mobile application used by field crews.

Approach

Make the on-device database (for example SQLite) the source of truth for the UI. Every local edit writes the record and an outbox entry in the same local transaction, so a crash can never leave a change that was saved but never queued.
Generate UUIDs on the client for records, and give each mutation its own idempotency key. The server upserts against a unique constraint and acknowledges each item, so retrying a half-sent batch never duplicates rows, and the device deletes outbox entries only after they are acknowledged.
Pull changes as deltas against a monotonically increasing sequence number assigned by the server, never device clocks, which drift and can be set wrong. The client stores its last cursor. Deletes travel as tombstones so devices that were offline still learn about them.
Choose conflict handling per entity. Append-only inspection records need none. Shared mutable records use a version number for optimistic concurrency: reject a stale write and show the conflict to the user, or merge field by field. Last-writer-wins is simple but silently loses edits.
Cover the edge cases: send photos and other large attachments through a separate resumable upload linked by ID, sync in small batches so a dropped connection loses little progress, and version the sync API so devices on an old app version keep working.

Follow-up

Two crew members edit the same asset record while both are offline: what does each of them see after syncing?
How do you ship a schema change while some devices running the old app version are still offline?
How would you know a device has unsynced data that has sat in its outbox for days?

How do you optimize database queries when dealing with large volumes of historical sensor telemetry?

Medium · Technical & System Architecture

Approach

Start from the actual slow queries: run EXPLAIN (ANALYZE, BUFFERS) and look for sequential scans, row estimates far from the actual counts, and sorts that spill to disk, before adding anything.
Index the way the data is read: a composite B-tree on (sensor_id, ts) with the equality column first and the range column second. For large append-only tables where physical order follows time, a BRIN index on ts is tiny and prunes block ranges well.
Range-partition the table by time (for example monthly) so queries with a time filter skip old partitions, and retention becomes detaching or dropping a partition instead of a huge DELETE that bloats the table.
Keep dashboards off the raw rows: maintain hourly or daily rollups (min, max, avg, count) in summary tables or materialized views, refreshed incrementally. An extension such as TimescaleDB continuous aggregates is one option if it fits the stack.
Clean up the queries themselves: write half-open ranges (ts >= :start AND ts < :end) rather than wrapping the column in a function such as DATE(ts), which stops a plain index on ts from being used. Select only the columns you need, and page with a keyset on (sensor_id, ts) instead of OFFSET.

Follow-up

When would a BRIN index perform worse than a B-tree here, for example with late-arriving or back-filled readings?
Readings arrive hours late from a field device: how do you keep the pre-aggregated rollups correct?
Storage keeps growing: how would you downsample or archive old telemetry and still answer historical questions?

Describe your approach to integrating third-party GIS or mapping APIs into a proprietary asset management plat

Medium · Technical & System Architecture

Describe your approach to integrating third-party GIS or mapping APIs into a proprietary asset management platform.

Approach

Put the vendor behind your own interface (an adapter or anti-corruption layer) for geocoding, reverse geocoding, basemaps and feature queries, and map vendor responses into your own asset and location types. Swapping providers or upgrading their API version then touches one module.
Pin down coordinate systems at that boundary: store a canonical CRS such as WGS84 (EPSG:4326), remember that web map tiles use Web Mercator (EPSG:3857), and watch axis order, because GeoJSON (RFC 7946) uses longitude then latitude. Swapped coordinates are a classic integration bug.
Make the calls resilient: explicit timeouts, retries with exponential backoff and jitter only on idempotent requests, honouring 429 and Retry-After, a circuit breaker, and caching geocode results where the provider's terms allow it. If the map is down, the asset list and forms should still work.
When importing spatial layers (shapefiles, feature services), store the external ID and source version, make the import idempotent, validate geometries (ST_IsValid, repair with ST_MakeValid) and add a spatial index before anything queries them.
Keep API keys server-side, or use keys restricted by referrer or app, and check the licensing terms on storing and displaying results before you design around caching them.

Follow-up

The provider deprecates the endpoint you depend on: how much of your codebase has to change, and how would you test the switch?
Field crews need maps where there is no connectivity: how would you handle offline tiles or cached layers?
How do you test this integration in CI without calling the live vendor API on every run?

If a legacy software system is blocking the deployment of a new corrosion monitoring feature, what steps do yo

Medium · Problem-Solving & Operational Scenarios

If a legacy software system is blocking the deployment of a new corrosion monitoring feature, what steps do you take to resolve the bottleneck?

Approach

Name the exact blocker before choosing a fix: a shared database schema the feature has to change, a deploy pipeline that couples releases, a missing API, an unsupported runtime or a release freeze. Each one has a different remedy.
Avoid a big-bang rewrite and use the strangler fig pattern: put a facade or router in front of the legacy system, build the new monitoring feature as its own component, and move functionality behind the facade piece by piece.
Decouple the data without editing legacy code where you can: feed the new feature through change data capture or an outbox/event stream, and use expand-and-contract schema changes (add the new structure, dual-write, backfill, switch reads, remove the old) so old and new code both keep running.
Write characterization tests that pin the legacy system's current behaviour before touching it, ship the new path behind a feature flag, and where possible run it in shadow mode, comparing its outputs with legacy before switching traffic.
Make the trade-off visible to stakeholders: a short-term workaround with its cost written down as debt, against a refactor with a timeline, so the decision is made on purpose.

Follow-up

The legacy system has no tests and nobody who wrote it is still around: where do you start?
When would you argue for a rewrite instead of incremental migration?
What is your rollback plan if the new path misbehaves after the switch?

How do you prioritize technical debt against the pressure to deliver new features for active engineering proje

Medium · Problem-Solving & Operational Scenarios

How do you prioritize technical debt against the pressure to deliver new features for active engineering projects?

Approach

Make the debt concrete: list the items and attach measurable cost to each, such as incidents caused, extra time every change in that area takes, slower lead time, or risk to data integrity and security. 'The code is messy' does not get prioritized; 'this module caused three bad releases' does.
Rank by cost of delay and risk: debt that threatens data correctness or security comes first, and next is debt in the code that the upcoming features will touch, so the payoff comes immediately.
Pick a mechanism and say why: fold targeted refactors into the feature work that touches that code, keep a recurring capacity share agreed with the product owner for the rest, and avoid freezing features for a big cleanup.
Explain the trade-off to the project manager in delivery terms. Example phrasing in my own words: 'I can ship this feature on the current code, or spend a bit longer refactoring first, and the next features in this area then get cheaper.'
Back it with a real example: what you deferred, what you paid down, how you measured the effect, and one case where taking on debt deliberately to hit a date was the right call.

Follow-up

The project manager rejects any time for debt this quarter: what do you do?
How would you show afterwards that paying down a particular piece of debt was worth it?
Tell me about a time you knowingly took on technical debt. How did you make sure it got repaid?

How would you debug a critical data loss issue occurring between field devices and the central cloud database?

Medium · Problem-Solving & Operational Scenarios

Approach

Define the loss exactly before forming a theory: which records, which devices, app versions and regions, and since when. Get one concrete missing record ID and the device it came from, because tracing one real record beats reasoning about aggregates.
Trace that record hop by hop: device local store and outbox, client upload logs, API or gateway access logs, the ingestion queue, then the database write. A correlation ID carried end to end shows the last hop where the record still existed.
Check the known failure modes at that hop: the device deleting its local copy before the server acknowledges it, the server returning 2xx before a durable commit (for example a queue that drops messages), an upsert keyed on something non-unique (a timestamp, or a local autoincrement that resets on reinstall) overwriting rows, validation that silently discards records, and payload size limits.
Rule out records that only look missing: timezone or device-clock errors can put rows outside the reporting window, and a swallowed transaction rollback can look like a successful write in the application logs.
Contain, then prevent: make devices keep data until it is acknowledged, send rejected payloads to a dead-letter store, recover what still sits on devices, then add daily reconciliation (per-device counts or ID sets compared with the server) and tests that inject network drops mid-upload.

Follow-up

The request shows up in the server access logs but the row is not in the database: where do you look next?
How would you recover records that were already lost, and how do you tell users which data is affected?
After the fix, how do you prove the loss has stopped rather than just become rarer?

Suggested practice checklist

The plan follows the reported loop: a day for the screening and your stack, then one day each for the schema, telemetry queries, offline sync, GIS and legacy work, debugging and technical debt, and a final day for the collaboration stories and a mock run.

Day 1 — Map the loop and refresh your stack

Write out the three reported rounds and the single-conversation variant, and mark the stage you are least ready for.
Pick C#, Java or Python and write from memory a small REST endpoint that accepts a batch of sensor readings, validates them and inserts them with SQL.
Refresh SQL basics you will need all week: joins, window functions, composite indexes and reading an execution plan.

Deliverable: A one-page loop map, plus a working endpoint with its insert statement and one passing test.

Day 2 — Inspection-log schema across regions

Sketch the schema for the question about corrosion inspection logs across regions: regions, assets, inspections, measurements, attachments and a way to record corrections without erasing the original.
Write the three queries it must serve (inspections for an asset in a date range, overdue inspections by region, latest reading per station) and choose indexes for each.
Work the practice SQL exercise on a filter that ignores an index, and note what makes a plan pick a scan.

Deliverable: Schema DDL, the three queries with their indexes, and a short note justifying each index.

Day 3 — Telemetry query performance

Generate a synthetic table of about a million readings and time the latest-reading-per-station and daily-aggregate queries before tuning.
Apply a composite index, time-range partitioning and a rollup table one at a time, and record the effect of each with EXPLAIN ANALYZE.
Work the practice exercise on adding a column and backfilling it live, as the model for changing a large table without downtime.

Deliverable: A before-and-after timing table with plans for both queries, and a one-page tuning checklist in the order you would apply it.

Day 4 — Offline-first sync for field crews

Design the sync: local store, operation queue, client-generated ids, an idempotency key on upload, a sync cursor and a conflict policy for edited inspections.
Decide how large attachments such as photos upload separately and how a failed item is shown to the technician.
Solve the practice coding exercises on collapsing redelivered event batches and merging partitioned streams into one ordered feed.

Deliverable: A sequence diagram of one sync with a dropped connection in the middle, plus passing code for the two practice exercises.

Day 5 — GIS integration and legacy bottlenecks

Design the integration with a third-party mapping API: your own adapter interface, one internal coordinate system, caching, rate-limit handling and a fallback when the provider is down.
Outline how you would import a shapefile: validate geometry, reproject, load into a staging table, then merge.
Write a plan for the legacy system blocking a new monitoring feature: characterise current behaviour with tests, add a facade, route the new feature through it, cut over in steps with a rollback.

Deliverable: A one-page integration design and a one-page legacy unblock plan listing the first three steps and the rollback.

Day 6 — Data-loss debugging and technical-debt trade-offs

Write a runbook for records going missing between field devices and the cloud database: count records at each hop (device log, gateway or broker, ingest worker, database), use sequence numbers to find gaps, and check acknowledgements, retries, duplicates dropped by constraints, clock skew and recent deploys.
Write a rubric for weighing technical debt against feature delivery: risk to data integrity, how often it hurts, whether it blocks upcoming work and the cost to fix.
Apply the rubric to two debt items you have met and say which one you would schedule first.

Deliverable: A hop-by-hop debugging runbook with the queries you would run, and a rubric with two worked examples.

Day 7 — Stories, screening and full rehearsal

Write outlines for the four reported collaboration questions (explaining a limit to a project manager, shifting requirements, a design disagreement, working with field personnel), each with a concrete outcome.
Rehearse each story aloud and record it; cut any jargon a project manager would not follow.
Run a mixed mock: your HR screening introduction, one design question, one debugging question and one story, then list the questions you will ask managers about team collaboration.

Deliverable: Four story outlines, one recorded run-through, and a short list of questions for the hiring team.

Collaboration and field-team stories

Candidates report the behavioral questions as centred on communication, adaptability and teamwork with non-technical stakeholders such as project managers, field personnel and business partners. Prepare one specific story for each prompt, with your own decision and a concrete result, and keep a spare story about ambiguity or stepping outside your comfort zone.

Tell me about a time you balanced a delivery deadline against fixing something you knew was fragile.
Tell me about a time you worked with hardware or data specialists to agree on a data format.

Tell me about a time when you had to explain a complex technical limitation to a non-technical project manager

Medium · Behavioral & Collaboration

Tell me about a time when you had to explain a complex technical limitation to a non-technical project manager or business partner.

Approach

Choose a limitation that forced a decision, for example a sync conflict that could overwrite a technician's entry, readings that arrive hours late, or a legacy format that capped what a report could show. Say who had to decide something because of it.
Lead with the consequence in their terms (schedule, cost, risk to data, what the field crew will see), then give the technical reason in one plain sentence or one analogy. Name the jargon you chose to drop.
Offer two or three options with trade-offs and a recommendation, so the manager makes a decision instead of receiving a lecture.
Check understanding by asking them to restate the trade-off, and follow up in writing. Finish with what they decided and what happened.
The mistake that sinks it is a story where the other person simply did not get it. Show that you changed your explanation, not only their understanding.

Follow-up

What did they misunderstand the first time, and how did you change the explanation?
What if the manager insisted on the original deadline anyway?
How do you document a limitation so it does not get reopened every sprint?

Describe a situation where project requirements changed significantly mid-development. How did you adapt your

Medium · Behavioral & Collaboration

Describe a situation where project requirements changed significantly mid-development. How did you adapt your approach?

Approach

Choose a change that invalidated work already done, not a small tweak. State the change and how you learned of it in two sentences.
Show the impact assessment: which code, schema and API contracts survived, what was discarded, and the new risk. Say that you told stakeholders the schedule or scope cost before agreeing.
Describe how you re-sequenced: the smallest slice that met the new need first, feature flags or a versioned API so existing clients kept working, and a migration path for data already collected.
Close with the result and one lasting change, such as isolating the volatile part behind an interface or agreeing shorter feedback loops with the users.
Avoid the story where you silently absorbed the change with overtime, or one that is mostly complaint. The answer is your method for adapting.

Follow-up

How did you decide what to cut or defer?
Did you push back on the change, and on what grounds?
What would you build differently so the next change costs less?

How do you handle disagreements within an engineering team regarding system design or technology choices?

Medium · Behavioral & Collaboration

Approach

Describe one real disagreement, such as relational versus document storage for readings, buying versus building the mapping layer, or a rewrite versus an incremental fix. State both positions fairly, including the one you opposed.
Show the move from opinion to criteria: agree on what matters (operating cost, team skills, data integrity, how reversible the choice is), then test it with a spike or a benchmark on realistic data.
Say how the decision was made (consensus, the tech lead, a short written decision record) and what you did if it went against you: commit, note your concerns, set a point to revisit.
Give the outcome and the state of the relationship afterwards, ideally with a measurable result.
Avoid a story you won by seniority, or the claim that everyone always agreed with you. The question asks for your method, so state it, then support it with the example.

Follow-up

What would you have done if the evidence was inconclusive?
Tell me about a time you were the one who was wrong.
How do you handle a teammate who keeps reopening a settled decision?

Share an experience where you had to collaborate with field personnel to understand their workflow and build a

Medium · Behavioral & Collaboration

Share an experience where you had to collaborate with field personnel to understand their workflow and build a better software tool for them.

Approach

Open with how you learned the workflow first-hand: shadowing a crew, watching them use the old tool or paper form, or visiting a site, rather than collecting a requirements list from a manager.
Name the constraints you found that changed the design (patchy connectivity, gloves or glare, entering data in a hurry, double entry, local vocabulary) and the design change each caused.
Show iteration: an early prototype in their hands, a small pilot group, and fixes made from their feedback.
Close with evidence of adoption, such as fewer re-entries, fewer missing fields or faster submission, and say how you measured it.
If you have no field users, use a comparable non-desk group (warehouse staff, support agents, clinicians) and say so. Avoid describing users as resistant; show that you changed the tool.

Follow-up

What did they ask for that you chose not to build, and why?
What did you do when a user's account conflicted with what their manager told you?
How did you know the tool was being used the way you intended?