Interview guides · Ayesa

Ayesa Software Engineer Interview Guide

Ayesa Software Engineer interview guide: the 3 reported rounds, .NET, SQL and API questions, project walk-throughs and a 7-day prep plan.

The 60-second brief

Candidates describe Ayesa as an engineering consultancy that also delivers technology solutions. Software engineers there build and maintain business software and digital systems for clients in public administration, energy, transport and commercial sectors; reported examples include large public-administration systems, utilities management platforms and custom enterprise applications. The work is organised by project: engineers join teams with project managers, systems analysts, architects and client stakeholders, write application logic, set up database tables, build API endpoints, fix defects found in testing or production, and take part in code reviews, documentation and status updates. Some engagements involve modernizing legacy applications or integrating them with other systems.

This guide is for candidates applying to Ayesa as a Software Engineer, including recent graduates who will lean on a university project. It covers the three reported rounds, the backend, SQL and API topics candidates report, the project walk-through questions, and the behavioral questions about unfamiliar stacks, reporting to a project manager, prioritisation and tight client schedules.

Candidates report three rounds over roughly three to five weeks: an HR screening, a technical interview that may include a short practical exercise, and a final discussion with a senior engineering manager about staffing and contract details.

Focus areas

Technical interviewing (Q&A)
Coding challenges / pruebas de programación
Experiencia en proyectos (explicar trabajos)

Overview

The reported questions are about practical engineering rather than puzzles. They fall into three groups: framework and database knowledge (data access layers in .NET or similar technologies, table design, ORM versus direct SQL, REST versus SOAP, error handling and logging across tiers), questions about your own past project (scope, stack choice, hardest problem, late requirement changes, the SDLC you followed), and behavioral questions about new stacks, reporting to a project manager, prioritising tickets and working with client technical teams. Preparation therefore means having working answers on a small set of backend and database topics, plus two or three projects you can describe down to the schema and endpoint level.

The requirements candidates describe list object-oriented languages such as C# on .NET or Java, SQL databases such as SQL Server, PostgreSQL or Oracle, and familiarity with REST APIs. Topics reported as less common include legacy system integration, VBA automation scripts, asynchronous messaging queues, Azure or AWS deployment, Docker, Git branching strategies and CI/CD pipelines. Treat the first list as the core of your preparation and the second as breadth to be able to mention honestly if you have touched it.

Candidates also report being asked to explain their specific contribution to database schemas or API endpoints, so vague team-level answers are a risk. For each project, prepare the tables you designed and why, one endpoint you wrote and how it validated input and handled failure, one bug you diagnosed, and what changed when requirements moved. Because the final conversation is reported to cover staffing alignment and contract details, also prepare questions about the project domain, stack, team structure and first deliverables.

The Ayesa interview process

Candidates describe three stages, from an HR screening through a technical interview to a final conversation with a senior engineering manager. The timeline is what candidates report, not a published process.

Initial Screening

Candidates report that the first call is with Human Resources and covers your professional background, your degree studies and your general availability. It is the stage where your CV has to be told as a short, accurate story. Prepare a chronological walk-through in which each project has its stack, your own contribution and the outcome, because the same material is reused in the technical rounds. If you are a recent graduate, decide which university project carries your technical story.

What to demonstrate

Professional background and degree studies as you present them
General availability for open project positions
A clear, concise account of your own CV

How to prepare

Write a chronological CV walk-through with stack, your specific contribution and outcome for each project, and say it aloud until it is short and clear.
Have your availability and location constraints ready, and pick the two projects you want the technical interview to start from.
Prepare a short reason for wanting consultancy-style project work, in your own words and tied to your experience.

Technical Interview

Candidates report that this interview is led by a project lead, technical manager or department head, and that it can include a brief practical exercise or a discussion tied to a stack such as .NET or database querying. The reported questions in this area cover data access layers in .NET or similar technologies, table design for query performance and integrity, ORM versus direct SQL, REST versus SOAP, and your past projects. Approach it by answering from real projects: name the layer, table or endpoint, the decision you made and its trade-off.

What to demonstrate

Hands-on knowledge of the stack under discussion, for example .NET and SQL
Ability to work through a short practical task or query if one is set
How clearly you explain your own project decisions to a technical lead

How to prepare

Rehearse the ORM versus direct SQL answer, including the N+1 problem and when you would hand-write a query.
Be able to sketch a normalized schema with keys, constraints and indexes on a whiteboard or shared editor, and explain how you would speed up a slow reporting query.
Prepare the REST versus SOAP comparison, covering JSON versus XML, contracts, security and when each fits.

Final Discussion

Candidates report a final discussion with a senior engineering manager that checks how you fit the project staffing need and settles contract details. Nothing reported suggests a new technical test, so prepare for a conversation about the project domain, your readiness to start and your preferences. Expect your earlier answers to be revisited in summary, so keep your stories consistent across the rounds.

What to demonstrate

Fit between your skills and the specific project position being staffed
Your readiness and expectations for starting on a client project
Consistency with what you said in the earlier rounds

How to prepare

Prepare questions about the project domain, technology stack, team structure and first deliverables.
Re-read your own project stories and the stack you named in the technical interview so you can summarise them in a minute each.
Be ready to talk plainly about start date, location and working arrangements.

Where candidates lose points

Describing a project as 'we built it' without saying which tables, endpoints or modules were yours

For each project, write down the schema you designed or changed, one endpoint you wrote end to end, one bug you fixed and one decision you made. Candidates report being pressed on their specific role in schemas and APIs, so use 'I' for your own work and say who did the rest.

Saying 'I use an ORM' or 'I write SQL' without explaining when each is right

Prepare both sides: an ORM for CRUD and mapping, direct SQL or stored queries for reporting and bulk operations, the N+1 problem and how eager loading or a joined query fixes it, and how you check the generated SQL. Add one real case from your own work.

Designing tables with no constraints or indexes, or indexing everything

Explain keys and normalization first, then add foreign keys, unique and check constraints for integrity, then indexes chosen from the actual queries (filter and join columns). Say how you would read an execution plan for a slow report before changing anything.

Treating REST versus SOAP as a definitions question

Compare them on concrete points: JSON versus XML payloads, contract-first WSDL versus resource-oriented endpoints, built-in standards for security and transactions in SOAP, simplicity and caching in REST, and which one you would pick for integrating with an older client system.

Giving behavioral answers that say you are flexible and adaptable but never show a case

Pick one story each for a new stack or legacy codebase, a late requirement change, a status report to a project manager, and a tight client schedule. Each states the situation briefly, your action, the trade-off you made and the result.

Questions & practice

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?

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?

Identify the heaviest tenants in a five-minute window under memory pressure

Medium · top-k · heavy hitters · streaming

The edge service handles about 3,000 requests per second across roughly 50,000 tenants, peaking near 9,000. Expose the 50 heaviest tenants by request count over the trailing five minutes so limits can be tightened before one tenant's backfill starves the fleet. You may not retain five minutes of raw records. Give the exact solution and its memory, then the bounded-memory approximation with its error stated as a formula, and say which you would ship and at what tenant cardinality that choice changes.

Approach

Do the exact version first, because it is affordable at this cardinality: a ring of 300 one-second counters per tenant, advanced lazily, is 1,200 bytes of counters per tenant and roughly 60 to 90 MB for 50,000 tenants with overhead. Carry a running total and subtract the bucket you overwrite so a window read is O(1) rather than 300 adds.
Extract the top 50 with a size-k min-heap over the tenant sums: O(d log k) for d tenants, against O(d log d) to sort them all. Maintaining the heap continuously instead of on query requires a tenant-to-heap-index map, because incrementing a count already inside the heap means sifting from a known position, and without that map you rebuild the heap on every request.
State the approximation precisely rather than gesturing at sketches. Misra-Gries with m counters retains every item whose true count exceeds N/(m+1), and each retained count underestimates the truth by at most N/(m+1). With m = 1,000 and N = 900,000 requests in the window the error is roughly 900 requests, which is fine for spotting a tenant sending 50,000 and useless for ranking two tenants 200 apart.
Say what breaks when the window slides: Misra-Gries and Space-Saving are insert-only and cannot be decremented as records age out. The workable construction is one summary per sub-window, say ten seconds, with 30 summaries merged at query time, and the merged error is the sum of the per-summary errors, so the bound degrades linearly in the number of sub-windows.

Follow-up

The heaviest tenant is heavy because of one export job rather than user traffic. Should the limiter treat those as the same tenant?
Two tenants sit tied at the boundary of the top 50. Does your answer flap, and does the flapping matter?

Can you explain how you handle Object-Relational Mapping (ORM) and direct SQL queries when building backend se

Medium · Core Technical & Framework Knowledge

Can you explain how you handle Object-Relational Mapping (ORM) and direct SQL queries when building backend services?

Approach

Give the split you use: the ORM (EF Core, Hibernate) for transactional CRUD where change tracking and migrations save work; hand-written SQL (Dapper, FromSql, or a stored procedure) for reporting queries, window functions, bulk operations and anything where the generated SQL is poor.
Explain the N+1 problem: loading a list and then lazily loading a navigation property per row issues one query per item. Fix it with eager loading (Include), a projection with Select into a DTO, or a batched query, and confirm the fix by logging the generated SQL (ToQueryString or EF Core logging).
Mention the opposite trap: several collection Includes in one query produce a cartesian explosion of rows; EF Core 5+ offers AsSplitQuery to issue separate queries instead.
On raw SQL, always use bind parameters: FromSqlInterpolated or FromSql with an interpolated string are parameterized, while concatenating user input into FromSqlRaw is a SQL injection hole.
For bulk changes, avoid loading thousands of entities just to modify them: EF Core 7+ has ExecuteUpdate and ExecuteDelete, and when ORM and raw SQL must commit together, run both on the same connection and transaction.

Follow-up

How would you find N+1 queries in an application that is already in production?
An ORM-generated query is slow: walk through what you check before rewriting it in SQL.
How do you keep hand-written SQL in sync with the schema when migrations rename or drop columns?

Write the update path that detects a concurrent edit

Medium · optimistic concurrency · transactional outbox · audit history

resource carries version INT NOT NULL DEFAULT 1. resource_revision holds revision_id, resource_id, version, actor_user_id, change_kind, patch JSONB, request_id, created_at with UNIQUE (resource_id, version). outbox_event holds aggregate_type, aggregate_id, aggregate_version, event_type, payload, status. A PUT carries the version the client read. Write the exact statements for the single transaction that applies the edit, records the revision and enqueues 'resource.updated', and give the handler's branch on zero affected rows. Then say what PostgreSQL 16 does under READ COMMITTED when two of these updates hit one row at once.

Approach

One transaction, three writes, no network call inside it: UPDATE resource SET title = $3, version = version + 1, updated_at = now() WHERE resource_id = $1 AND tenant_id = $4 AND version = $2; then INSERT the resource_revision row at version $2 + 1; then INSERT the outbox_event row at the same aggregate_version. The event goes to a table rather than a broker because no transaction spans both.
Branch on the affected-row count before doing anything else. Zero has three causes — stale version, wrong tenant, row gone — so re-read once and map to 409 carrying the current version, or 404 for an id outside the caller's tenant, which also stops the endpoint confirming that another tenant's id exists.
State the engine behaviour instead of assuming it. Under READ COMMITTED the second UPDATE blocks on the row lock, and when the first commits PostgreSQL re-evaluates the WHERE clause against the newly committed row, so the version predicate now fails and the statement reports zero rows. Under REPEATABLE READ the identical collision raises SQLSTATE 40001 instead, so the handler must fold both shapes into one conflict response.
Keep UNIQUE (resource_id, version) even though the predicate already serialises writers. It is what makes a lost update unwritable if any other path ever reaches the revision table, and it converts a logic bug into 23505 rather than into a silently missing history row.

Follow-up

A client sends the version it read ten minutes ago and the resource has moved three versions. What is in your 409 so it can resolve the conflict without a full re-fetch?
Two editors, two disjoint fields, no overlap. Does your answer still refuse the second write, and should it?

What backend frameworks have you used in past projects, and how did you handle data access layers using.NET or

Medium · Core Technical & Framework Knowledge

What backend frameworks have you used in past projects, and how did you handle data access layers using.NET or similar technologies?

Approach

Name the frameworks you have actually shipped with (for example ASP.NET Core Web API, or Spring Boot if your background is Java) and describe the layering you used: controller, service, data access, with the controller never touching the DbContext directly.
For EF Core, explain the DbContext lifetime: AddDbContext registers it as scoped (one per request) because a DbContext is not thread-safe and holds a change tracker; a singleton context leaks tracked entities and breaks under concurrent requests.
Say when you add a repository or unit-of-work layer and when you skip it: the DbContext already acts as a unit of work (SaveChanges commits all tracked changes in one transaction by default), so a thin repository is mainly worth it for testability or to hide query logic.
Show read-path habits: AsNoTracking for read-only queries, projecting with Select into DTOs instead of loading whole entity graphs, and async calls (ToListAsync, SaveChangesAsync) so request threads are not blocked on I/O.
Mention where you dropped to hand-written SQL (Dapper or FromSql) for reporting or bulk work, and how schema changes were managed with EF Core migrations checked into source control.

Follow-up

Why should a DbContext not be registered as a singleton, and what goes wrong if it is?
How would you unit test a service that depends on the data access layer, and what are the limits of the EF Core in-memory provider?
Two users edit the same record at once: how do you detect it with a rowversion or concurrency token, and what do you do with DbUpdateConcurrencyException?

How do you design and structure database tables for an enterprise application to ensure query performance and

Medium · Core Technical & Framework Knowledge

How do you design and structure database tables for an enterprise application to ensure query performance and data integrity?

Approach

Start from the entities and the queries the application runs, normalize to roughly third normal form so each fact lives in one place, then denormalize only where a measured read path needs it (for example a stored total on an invoice header).
Enforce integrity in the schema, not only in code: primary keys, foreign keys, NOT NULL, UNIQUE and CHECK constraints, and correct types (DECIMAL for money, never FLOAT; datetime2 or timestamptz for timestamps).
Choose keys deliberately: an identity BIGINT is compact and sequential; a random GUID as a clustered key in SQL Server causes page splits and fragmentation, so use NEWSEQUENTIALID or keep the GUID as a non-clustered alternate key.
Design indexes from the query patterns: composite indexes with equality columns first and the range or sort column last, covering indexes with INCLUDE, and explicit indexes on foreign key columns, since neither SQL Server nor PostgreSQL creates them automatically.
State the cost: every index slows INSERT, UPDATE and DELETE and uses storage, so verify each one against the execution plan (EXPLAIN ANALYZE in PostgreSQL, the actual plan in SQL Server) rather than indexing every column.

Follow-up

When would you deliberately denormalize, and how do you keep the duplicated value consistent?
How do you enforce uniqueness on a column when rows are soft-deleted (for example with a filtered or partial unique index)?
An audit or history table grows to hundreds of millions of rows: what changes in your design (partitioning, archiving, index strategy)?

What are the core differences between RESTful APIs and SOAP web services, and when would you implement each?

Medium · Core Technical & Framework Knowledge

Approach

Define both precisely: SOAP is a protocol with an XML envelope, a WSDL contract, typed faults and the WS-* extensions (WS-Security, WS-ReliableMessaging); REST is an architectural style built on resources addressed by URI, HTTP methods, status codes and stateless requests, usually carrying JSON.
Compare the practical properties: REST GET responses can use standard HTTP caching, and GET, PUT and DELETE are defined as idempotent while POST is not; SOAP usually sends everything as POST to one endpoint, so HTTP caching does not apply, but the WSDL gives a strict machine-readable contract and generated clients.
Say when you pick each: SOAP when integrating with an existing system that already exposes a WSDL or needs message-level security and signed messages; REST (documented with OpenAPI) for new services consumed by web and mobile front ends or third parties.
Cover the .NET angle: WCF server hosting is not part of .NET Core or .NET 5+, CoreWCF is the community port, and a .NET app can still consume a SOAP service through a client generated from the WSDL with dotnet-svcutil.
Close with error and security handling: REST maps failures to status codes (400, 401, 404, 409, 500) with a consistent error body, while SOAP returns Fault elements; both need TLS, and REST typically uses token-based authentication such as OAuth 2.0 bearer tokens.

Follow-up

How would you version a REST API without breaking existing clients?
A client retries a POST that creates a payment after a timeout: how do you prevent a duplicate (for example with an idempotency key)?
You must expose data from a legacy SOAP service to a new web front end: where do you put the translation layer and what does it handle?

Walk us through your final university project or a recent professional initiative: what was the scope, what te

Medium · Academic & Project Experience

Walk us through your final university project or a recent professional initiative: what was the scope, what technology stack did you choose, and why?

Approach

Open with the problem and the users in two sentences, then the scope: what was in and out, team size, and exactly which parts you built (name the tables, endpoints or modules that were yours, not the team's).
Describe the architecture in layers: client, API or backend, database, any external integrations, and how a request flows through them; a simple spoken diagram is clearer than a list of technologies.
Justify each stack choice with a reason and the alternative you rejected, for example a relational database because the data had strong relationships and needed transactions, or .NET or Java because the team or the existing codebase already used it.
Give the outcome with numbers you can stand behind (users, response time, data volume, grade or client acceptance) and one thing you would change now, which shows you can judge your own design.
Prepare to go one level deeper on any component you name: the schema of your main tables, how authentication worked, how you tested and deployed it.

Follow-up

Why that database rather than the alternative you considered?
What would break first if the number of users grew tenfold?
Which part of the project would you redesign today, and how?

What was the most complex technical challenge you faced in your previous project, and how did you resolve it?

Medium · Academic & Project Experience

Approach

Pick a challenge that is genuinely technical and that you resolved yourself: a slow query, a concurrency bug, a failing integration, a data migration, a memory leak. Avoid stories where the root cause was never found.
Walk the debugging sequence: the symptom and how it was noticed, how you reproduced it, the hypotheses you ruled out and with what evidence (logs, profiler, execution plan, git bisect to find the commit that introduced it).
State the root cause in one precise sentence, then the fix, and why you chose it over the alternatives (for example adding an index versus rewriting the query versus caching).
Show verification and prevention: the before-and-after measurement, the regression test you added, and any logging or monitoring that would catch it earlier next time.

Follow-up

How did you confirm that this was the root cause and not a coincidence?
What other fixes did you consider, and why did you reject them?
What did you change in the codebase or process so it would not happen again?

How did you handle project deadlines when technical requirements changed late in the development cycle?

Medium · Academic & Project Experience

Approach

Describe the change concretely (a new field, a changed business rule, a new integration) and the first step you took: an impact analysis of which modules, tables, APIs and tests it touched.
Explain how you turned that into a decision for the project manager: an estimate and two or three options (move the date, cut or defer other scope, deliver in phases) rather than silently absorbing the work.
Cover the technical side of absorbing change safely: isolating the change behind an interface or feature flag, a reversible database migration, and running the regression suite before the release.
End with the result (delivered on time with reduced scope, or a negotiated new date) and what you now do earlier, such as confirming acceptance criteria in writing before building.

Follow-up

How did you arrive at the estimate you gave, and how accurate was it?
What did you decide to cut or defer, and who agreed to it?
How did you make sure the late change did not break existing functionality?

Can you describe the software development lifecycle (SDLC) followed in your past engineering projects?

Medium · Academic & Project Experience

Approach

Name the model you actually worked in (Scrum sprints, Kanban, or a waterfall or V-model with signed-off specifications) and do not dress it up as something it was not.
Walk the stages with your role in each: requirements as user stories with acceptance criteria, design, implementation on feature branches with pull requests and code review, testing (unit, integration, QA, user acceptance), deployment and maintenance.
Describe the tooling that held it together: Git branching strategy (feature branches into main, or GitFlow with release branches), a CI pipeline that builds and runs tests on every pull request, and how releases reached staging and production.
Give the trade-off: a sequential model fits fixed-scope work with stable, signed-off requirements; iterative models handle changing requirements better but need an engaged product owner. Say which problems you saw in your own process and what you would change.

Follow-up

Which branching strategy did you use and why, and how were merge conflicts and releases handled?
Trace a production bug through your process: who reported it, how it was prioritised, tested and deployed?
What would you change in that process if you were leading the team?

Every query on one table stalls for forty seconds mid-deploy

Medium · lock contention · ddl · migrations

During a release on PostgreSQL, every query touching resource times out for about 40 seconds and then recovers with no intervention. The release ran one migration, ALTER TABLE resource ADD COLUMN archived_reason TEXT, and the migration log shows it completing in 6 ms. Unrelated tables showed no change in error rate. Explain how a 6 ms statement caused a 40-second stall, give the ordered checks you would run on a live system to confirm it, and give the migration procedure that prevents a repeat.

Approach

Separate the statement's duration from the lock's duration. ADD COLUMN with no default is a catalogue-only change and genuinely runs in milliseconds, but it requires ACCESS EXCLUSIVE, and it cannot acquire that until every transaction already touching the table has finished.
Account for the queueing, which is the part that surprises people. A lock request that is waiting blocks later requests for conflicting modes behind it rather than letting them overtake, so one long-open transaction holds the DDL and the DDL holds all the traffic. The stall length is set by the longest open transaction, not by the size of the change.
Confirm on a live system in this order: pg_stat_activity for that table ordered by xact_start, looking for the oldest transaction and specifically for state = idle in transaction; then pg_locks where granted = false to find the waiter; then join them on pid to name blocker and blocked. pg_blocking_pids() does that join for you and is the fastest single call.
Prevent rather than merely time it better. Set lock_timeout to a second or two on the migration session so the DDL abandons the queue after a bounded wait and is retried, instead of holding it for as long as the oldest transaction lives. Be exact about what that buys: queries arriving during the wait still queue behind the pending ACCESS EXCLUSIVE request, so each attempt costs them up to one lock_timeout of added latency. The outage goes from 40 seconds to about one second per attempt, not to zero. Also run migrations away from deploy-time peaks, and put a statement timeout and an idle-in-transaction timeout on the analytics role that opens the long transactions.

Follow-up

The same release also wants NOT NULL on that column. What is the sequence that gets there without a long lock?
Your lock_timeout retry fails ten times in a row because the analytics transaction is always open. What do you change?

Suggested practice checklist

The plan follows the three reported rounds and the three question groups candidates report: backend and database fundamentals, project walk-throughs and client-facing behavioral answers.

Day 1 — Map the loop and write your CV walk-through

Write the three reported rounds (Initial Screening, Technical Interview, Final Discussion) on one page and mark the one you are least ready for.
Write a chronological CV script giving stack, your contribution and outcome for each project, then say it aloud until it is short and clear.
List the stack terms from your CV (for example .NET, Java, SQL Server, PostgreSQL, Oracle, REST) and rate your confidence in each.

Deliverable: A one-page loop map, a spoken CV script, and a rated stack list that sets the order of days 2 to 4.

Day 2 — Backend layers and data access

Write out the layers of a backend you have built (controller, service, data access, database) and how dependency injection wired them, including the frameworks you used and why.
Write a one-page comparison of an ORM and direct SQL for your stack, with an example of the N+1 problem and its fix.
Practise the two answers aloud and time them.

Deliverable: A layer diagram from a real project, a one-page ORM versus SQL note with an N+1 example, and two spoken answers.

Day 3 — Table design and SQL

Design a schema for a small enterprise case (for example orders with customers and line items) with keys, constraints and indexes chosen from the queries it must serve.
Take a slow reporting query, read its execution plan, and write down two ways to speed it up.

Deliverable: A schema with an index rationale, an annotated query plan with two fixes, and solutions to the two practice problems.

Day 4 — APIs, errors, logging and validation

Write a comparison table of REST and SOAP covering payload format, contracts, security, error reporting and a typical use for each.
Outline the error handling, validation and logging design of an API you have built, tier by tier, including a correlation id.

Deliverable: A REST versus SOAP table, a tier-by-tier error and logging outline, and one solved practice exercise.

Day 5 — Project walk-throughs

For your two strongest projects, write scope, stack choice with the alternatives you rejected, your own contribution and the outcome.
Pick the hardest technical problem from each project and write the diagnosis, the fix and what you would do differently.
Write how requirements changed late in one project and what you did to the plan, plus a short description of the development lifecycle you followed.

Deliverable: Two project briefs with a hardest-problem story, a late-change story and a lifecycle description, each spoken in under two minutes.

Day 6 — Behavioral stories for project and client work

Write one story each for joining an unfamiliar stack or legacy codebase, and for reporting progress or an issue to a project manager.
Write one story about prioritising several tickets or milestones, and one about a tight schedule with a client's technical team.
Say each aloud, then cut it to the situation, your action, the trade-off and the result.

Deliverable: Four spoken stories with a clear result each, and a list of the follow-up questions you expect.

Day 7 — Full run-through and questions for the team

Run a mock of all three stages in order: CV walk-through, a technical conversation with one small SQL or code task, and a final discussion about fit.
Write five questions to ask about the project domain, stack, team structure and first deliverables.
Review any answer that rambled and rewrite its first two sentences.

Deliverable: A recorded mock run, a list of five questions for the team, and rewritten openers for your weakest answers.

Project, deadline and client-readiness stories

Candidates report that the behavioral questions concern joining project teams quickly: working in an unfamiliar stack or legacy codebase, reporting progress or problems to a project manager, prioritising tickets, handling late requirement changes and tight schedules with client technical teams. Prepare real stories with your own actions, the trade-off you made and the result, rather than general statements about being adaptable.

How did you handle project deadlines when technical requirements changed late in the development cycle?
Describe a time a requirement was ambiguous and how you clarified it with a project manager.

How do you handle error handling, logging, and input validation across multi-tier software applications?

Medium · Core Technical & Framework Knowledge

Approach

Organise the answer by tier: presentation or API layer, service layer, data access, database. Say which concern lives where. Validate shape, types and ranges at the boundary and reject early with a clear 400 response and field-level messages; enforce business rules in the service layer; and keep NOT NULL, UNIQUE, foreign key and CHECK constraints in the database, because other writers and scripts bypass the application.
Handle errors where you can act on them (retry, compensate, translate) and let the rest reach one central handler. In .NET that is exception-handling middleware or a filter returning a consistent error body with a correlation id, never a stack trace. Separate expected business failures (4xx) from unexpected faults (5xx, logged with the exception). Preserve the inner exception, never leave an empty catch, and roll back the transaction.
For logging, use structured entries carrying a correlation id created at the entry point and passed to every tier and downstream call. Use levels deliberately: information for business events, warning for recoverable problems, error for faults. Log an exception once, at the boundary, instead of at every layer, and keep passwords, tokens and personal data out of logs.
Tie it to a real project: name the logging library and validation mechanism you used, and describe one incident you traced through the logs from user report to root cause.

Follow-up

A user says their order failed yesterday. How do you trace it across the web, service and database tiers?
Where do you validate when your service receives XML or JSON from another system rather than from your own UI?
How do you avoid logging sensitive data while keeping logs useful for debugging?

How do you adapt when assigned to a new project that uses a technology stack or legacy codebase you have not w

Medium · Behavioral & Client Readiness

How do you adapt when assigned to a new project that uses a technology stack or legacy codebase you have not worked with before?

Approach

Open with one concrete case: an inherited codebase or unfamiliar stack, what was at stake, and your first week. Do not answer with 'I learn fast'; show the method.
Describe the method: get it running locally, read the README and any tests, trace one request end to end (entry point, service logic, data access, database), and list the build, deploy and branching conventions. Where tests are missing, write characterization tests around the module before changing it.
Say how you used people: try first, then ask the previous owner or a senior colleague targeted questions with what you already found. Keep notes or a short onboarding document as you go so the next person benefits.
Show the result with a small first change that was reviewed and shipped safely, and name what you did not change yet because the risk was unclear. The mistake that sinks this answer is claiming you rewrote the legacy code to make it understandable.

Follow-up

What do you do when the legacy code has no tests and no documentation, and the deadline is fixed?
Which technology on this role's list is weakest for you, and how would you close the gap in the first month?

Describe a situation where you had to present technical progress or report an issue to a project manager or te

Medium · Behavioral & Client Readiness

Describe a situation where you had to present technical progress or report an issue to a project manager or team lead.

Approach

Choose a real moment: a milestone at risk, a defect found late, or a blocked dependency. Make clear who you told, when, and what you asked of them.
Structure the message the way a project manager can use it: status in one sentence, impact on scope, date or cost, what you have already done, the options with their trade-offs, and the decision you need. Translate technical causes into delivery impact and leave out jargon the manager cannot act on.
Show timing and honesty: you raised the issue as soon as you knew, with evidence (logs, a failing test, an estimate range) rather than a guess, and you agreed when the next update would come.
State the outcome and the habit it created, such as a short weekly status note or a risk list. The mistake that sinks this answer is describing a status meeting where you only read out tasks, or bad news that you delayed until it was unavoidable.

Follow-up

How do you give an estimate when you do not yet know the cause of the problem?
What if the manager decides on an option you think is technically worse?
How would you report the same issue to a client's technical team instead of your own manager?

How do you prioritize your daily task queue when managing multiple open tickets or project milestones?

Medium · Behavioral & Client Readiness

Approach

Give a working method, then one example. A reasonable order is: production defects and anything blocking other people first, then work attached to the nearest milestone, then the rest, with ticket size and dependencies as tie-breakers.
Make the queue visible: a board or ticket list with explicit priorities, a start-of-day check for new defects and review requests, and focused blocks for coding rather than reacting to every message. Break large tickets into pieces that can finish independently.
Explain conflict handling: when two milestones clash, you do not decide silently; you show the project manager the trade-off (what slips if you do A first) and let them choose, then record it on the tickets.
Close with a specific week where this mattered and what you dropped or deferred. The mistake that sinks this answer is claiming you do everything at once, or ordering purely by who asked loudest.

Follow-up

A senior colleague and your project manager give you conflicting top priorities. What do you do?
How do you handle a ticket whose estimate turns out to be double what you said?

Are you comfortable working under tight project delivery schedules and collaborating closely with external cli

Medium · Behavioral & Client Readiness

Are you comfortable working under tight project delivery schedules and collaborating closely with external client technical teams?

Approach

Answer yes only with evidence: describe one project where the date was fixed and the team worked closely with a client's technical staff, and say what you personally did. Avoid slogans about thriving under pressure.
Show how you protected quality under time pressure: agree scope with the project manager and cut or defer features rather than skipping tests on the critical path, keep releases small, use code review and automated build checks where they exist.
Show how you worked with an external technical team: agree interfaces and data formats in writing early (for example an API contract or sample payloads), hold short regular syncs, keep a shared list of open questions and owners, and raise blockers on the client side to your project manager quickly.
Be honest about limits: say where you would flag that a schedule was unrealistic and what evidence you would bring. The mistake that sinks this answer is promising to meet any date with no mention of scope, risk or communication.

Follow-up

Tell me about a time a client's team was late with something you depended on.
When would you tell a project manager that a delivery date is not achievable, and how?
How do you keep documentation and handover notes current when the schedule is tight?