AIDS Healthcare Foundation Software Engineer Interview Guide

The 60-second brief

AIDS Healthcare Foundation (AHF) was founded in 1987 and provides HIV/AIDS medical care. Candidates describe its data systems as the layer under its healthcare centers, pharmacies and advocacy work. The Software Engineer role in this guide is a Database Developer position: you design and maintain relational databases, write and tune SQL scripts, stored procedures and triggers, run ETL or ELT processes that move data from operational systems into central reporting stores, and deliver Business Intelligence reports and dashboards for clinical, operational and executive audiences. The requirements candidates describe name SQL Server and PostgreSQL, and list Tableau, Power BI and SSRS as reporting tools. Healthcare experience with EMR/EHR systems and HIPAA-compliant environments is described as an advantage rather than a requirement.

This guide is for candidates interviewing for the Software Engineer (Database Developer) position at AIDS Healthcare Foundation, which candidates describe as onsite in Los Angeles, CA. It covers the three reported rounds, the SQL, database design, ETL, data governance and BI questions candidates report, the behavioral and mission questions that sit alongside them, and a seven-day plan. It is not a general software engineering guide: the reported material is about databases and reporting, not algorithm puzzles.

Candidates report three rounds over roughly 3-5 weeks: a recruiter screen, a technical interview by video call or take-home, and an onsite or virtual panel. Reports say technical and behavioral questions can appear at every stage.

Focus areas

• SQL
• JOINs (SQL joins)
• Database Development

Overview

The work behind this loop is database development in a healthcare setting. The responsibilities candidates describe are designing and maintaining relational schemas, writing and optimizing SQL, running ETL or ELT processes from operational systems into a central reporting environment, and turning the result into BI reports that clinical staff, operations managers and executives can use. The requirements name SQL Server and PostgreSQL, stored procedures, triggers, data modeling and ETL/ELT pipeline development, with 3 to 5+ years of database, BI or data engineering experience described as typical.

That shapes preparation. The reported technical questions are about writing a second-highest-salary query, finding patients who missed consecutive appointments, explaining inner, left and full outer joins with real scenarios, choosing between clustered and non-clustered indexes, designing a schema from vague requirements and untangling a production deadlock. Candidates also report data governance and BI questions: keeping figures accurate across several reporting systems, handling incomplete or corrupted source data, running a nightly ETL job without disturbing systems that are live around the clock, and masking sensitive data in reports. Every one of these is about SQL, database design, pipelines or reporting, so window functions, execution plans and schema design are where preparation time pays off.

The other half is communication. The requirements stress client relationships, explaining data decisions to non-technical colleagues and advocating for practices such as data governance. Candidates report mission and values questions (the stated values are Patient-Centered, Value Employees, Respect for Diversity, Nimble and Fight for What's Right) and behavioral questions on scope changes, difficult stakeholders, resisted best practices and competing urgent requests. Prepare stories that carry real database detail, such as a reconciliation you built or a definition you got two departments to agree on, so the technical and behavioral answers support each other.

Because reports say the technical interview may be a take-home, practise both formats: spoken answers you can give without an editor, and written work that stands alone, with runnable scripts, stated assumptions and a short note on the indexing and design choices you made.

The AIDS Healthcare Foundation interview process

Candidates report three rounds over about 3-5 weeks. The descriptions below separate what is reported about each stage from how to prepare for it.

Initial Recruiter Screen

Candidates describe a phone conversation about your background, how you relate to the foundation's stated values and practical logistics, including whether you can work onsite in Los Angeles, CA. Treat it as the place to make the connection between your database and reporting experience and the role's SQL Server or PostgreSQL, ETL and BI work, and to settle the format of the technical stage before you leave the call.

What to demonstrate

• How your database development, ETL and BI background lines up with the responsibilities candidates describe
• Your answer on mission and values, which candidates report is raised from the first stage
• Whether the onsite Los Angeles requirement works for you

How to prepare

• Write a short career summary that names the relational databases you have used, the largest ETL or reporting process you owned and one measurable outcome.
• Prepare the answer to why you are passionate about the foundation's mission, using the structure in the mission question guidance below.
• Confirm you can work onsite in Los Angeles before the call and have your pay expectations ready.
• Ask whether the technical interview will be a video call or a take-home, and which tools and database engine it uses.

Technical Interview

Candidates report a technical interview held either over video or as a take-home assessment, centred on SQL proficiency, database design and understanding of BI tools. The reported SQL and database-architecture questions are the natural material for this stage: query writing with window functions and joins, index choice, schema design from loosely stated requirements and diagnosing locking problems. Prepare to speak your reasoning aloud for a video format, and to deliver self-explanatory scripts and notes for a take-home.

What to demonstrate

• Working SQL: ranking and gap-finding queries and correct use of inner, left and full outer joins
• Database design: turning loose business requirements into tables, keys, constraints and indexes
• Performance and concurrency reasoning, including execution plans and deadlocks
• Familiarity with reporting and BI tooling

How to prepare

• Write the second-highest-salary query three ways (DENSE_RANK, a subquery on MAX, OFFSET) and say what each does with ties and with fewer than two rows.
• Build a small schema for patient visits, prescribed medications and pharmacy fulfillment with the indexes a monthly dashboard needs, packaged as a take-home would be: DDL, sample data, queries and a note on assumptions and index choices.
• Reproduce a two-session deadlock in PostgreSQL or SQL Server and practise explaining the cycle and the fix.
• Practise BI tool mechanics in the listed tools: an SSRS report parameterised on a stored procedure, Power BI import versus DirectQuery against a live SQL Server, and how row-level security carries through to the report.

Onsite/Panel Interview

Candidates describe an onsite or virtual panel with engineering leads and stakeholders, and note that clinical or operational staff may join. The reported focus is technical problem-solving, your approach to data governance and behavioral fit, so expect to move between a design discussion (an ETL pipeline, a reporting schema, masking sensitive data) and stories about stakeholders. Explain database decisions in plain language, since part of the audience may not be engineers.

What to demonstrate

• Technical problem-solving across ETL design, governance and reporting accuracy
• Behavioral fit with the foundation's stated values
• Ability to explain data decisions to a mixed technical and non-technical panel

How to prepare

• Outline a nightly ETL design from memory: staging, incremental loads, idempotent re-runs, row-count reconciliation and scheduling around live systems.
• Prepare a one-minute explanation of data governance with concrete mechanisms: constraints, a data dictionary with named owners, access roles and audit logs.
• Have stories ready for a scope change, a difficult stakeholder, a resisted best practice and competing urgent requests.
• Practise explaining an index choice or a deadlock fix to a non-technical listener without jargon.

Where candidates lose points

Solving the second-highest-salary question with a bare OFFSET or TOP and ignoring ties, or finding missed consecutive appointments without defining the terms

Ask whether duplicate salaries count as one rank and what to return when fewer than two distinct values exist. For appointments, define missed (no-show versus cancelled), order by appointment date per patient and use LAG or a row-number gap, then state how you treat the first appointment.

Explaining inner, left and full outer joins by definition only, with no real scenario and no mention of how filters interact with them

Give one example each: visits with matching patients, patients with no visits through a left join, and a reconciliation of two systems through a full outer join. Mention that a WHERE condition on the right-hand table turns a left join into an inner join.

Answering the deadlock question with NOLOCK or a retry and nothing about the cause

Describe the sequence: capture the deadlock graph, identify the two statements and the resources they hold, then fix with consistent access order, shorter transactions, supporting indexes or row-versioned isolation, and confirm the result under concurrent load.

Describing data governance as policy and enthusiasm, with no mechanisms for accuracy across reporting systems or for masking sensitive data

Name the controls: constraints and validation at load, row-count and total reconciliation between systems, a data dictionary with an owner per metric, role-based access, audit logs and masked or de-identified views for reporting.

Giving a mission answer that recites the five values or could be about any healthcare employer, then telling behavioral stories with no stakes or numbers

Anchor the mission answer in one specific personal reason and the foundation's actual work, and write each story with a decision you made, the trade-off, the outcome and what you would change.

Questions & practice

Canonicalise a request body into a stable idempotency fingerprint

Medium · parsing · canonicalisation · hashing

idempotency_key.request_fingerprint is a SHA-256 over the method, path and canonicalised body, and a retry whose fingerprint differs must be rejected with 422 rather than served the stored response. Write the canonicaliser. Bodies are JSON up to 256 KB nested at most 32 levels; clients vary key order, whitespace and unicode escaping, and some send 64-bit ids as JSON numbers. Produce a deterministic byte string such that semantically identical bodies match and any semantic difference does not. State your complexity and name two normalisations you refuse to perform.

Approach

1. Parse once into a tree, then re-serialise under fixed rules: object keys sorted, array order preserved, one escaping convention, no insignificant whitespace. Parsing is O(n) and sorting keys is O(k log k) per object, so O(n log n) overall with O(depth) stack, and the 32-level cap is enforced during parsing because hostile nesting is how a canonicaliser becomes a stack overflow.
2. Sort keys by their UTF-8 bytes and say why the obvious implementation is wrong in some runtimes: a default string comparison that orders by UTF-16 code units places surrogate pairs, meaning code points from U+10000 up, below U+E000 to U+FFFF, which is not UTF-8 byte order, so two services written in different languages disagree on the same document.
3. Do not re-encode numbers through a double. IEEE-754 binary64 represents integers exactly only up to 2^53, so normalising a 19-digit id through a float changes it, and 1 against 1.0 cannot be reconciled without deciding whether they are the same value. Preserve the literal token, and require ids as strings at the API boundary if you want them comparable.
4. Reject duplicate keys rather than picking one. JSON permits them and parsers disagree, most keeping the last, so any choice you make ties the fingerprint to a parser detail that the code handling the request does not necessarily share.

Follow-up

• A client sends the same logical request with an extra field your API ignores. Same key, different fingerprint, so you return 422. Is that the right answer?
• Where does the fingerprint get computed relative to request decompression and the body-size limit?

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

1. 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.
2. 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.
3. 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.
4. 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

1. 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.
2. 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.
3. 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.
4. 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?

Write a query to find the second highest salary in an employee table, or find patients who have missed consecu

Medium · SQL & Database Architecture

Write a query to find the second highest salary in an employee table, or find patients who have missed consecutive appointments.

Approach

1. Second highest salary: I use DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE and keep rank 2, so ties at the top do not hide the real second value. Rank 2 returns one row per employee at that salary, so I select DISTINCT salary, which gives an empty set when there is no second value. Wrapping it in MAX returns NULL instead, as does the classic SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee).
2. If I use paging instead, I sort DISTINCT salaries descending and skip one: LIMIT 1 OFFSET 1 in PostgreSQL, or OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY in SQL Server. Without DISTINCT, two people tied on the top salary make the query return the top salary again.
3. Consecutive missed appointments: I order each patient's appointments by scheduled time and use LAG(status) OVER (PARTITION BY patient_id ORDER BY appointment_ts). A row where both the current and previous status are 'missed' marks a patient with at least two misses in a row.
4. For a run of N or more I use gaps-and-islands: ROW_NUMBER() over the patient minus ROW_NUMBER() over the patient and status gives a constant group id for each run of the same status. Then I filter status = 'missed', GROUP BY patient_id and that group id, and keep HAVING COUNT(*) >= N.
5. Edge cases I say out loud: cancelled or rescheduled appointments (exclude them or treat them as breaking the run), two appointments with the same timestamp (add a tiebreaker column to ORDER BY), and NULL status. An index on (patient_id, appointment_ts) lets the window sort use the index.

Follow-up

• How would you return the Nth highest salary per department, and what changes if ties should count as separate ranks?
• How do you find patients whose most recent three appointments were all missed, rather than any three in a row?
• Your missed-appointment query is slow on several years of history. What do you index or pre-aggregate?

Walk me through your process for designing a database schema from scratch based on a vague set of business req

Medium · SQL & Database Architecture

Walk me through your process for designing a database schema from scratch based on a vague set of business requirements.

Approach

1. I start from the workflows rather than the tables: who records what, when, and which questions the data has to answer. From that I list the entities (for a clinic, say, patient, visit, provider, prescription and fulfillment) and ask about cardinality, such as whether a visit can have several providers or a prescription several fills.
2. I sketch an ER diagram and normalize the transactional schema to roughly 3NF: one fact in one place, junction tables for many-to-many, and lookup tables for coded values. I denormalize only where a measured read path needs it.
3. Keys and constraints carry the rules: a surrogate primary key, a UNIQUE constraint on the natural key (such as MRN plus facility), foreign keys, NOT NULL, and CHECK constraints for status values and date order. Constraints in the database catch bad data that application checks miss.
4. I decide early which attributes need history (address, insurance, provider assignment) and model them with effective-from/effective-to rows instead of overwriting. I index from the known query patterns, such as (patient_id, visit_date).
5. I validate the design by writing the five or six queries the stakeholders actually need against sample data. I keep reporting in a separate star schema (fact tables at a stated grain, plus dimensions) so analytic queries do not run against the transactional tables.

Follow-up

• A requirement arrives after launch that a visit can involve two providers. How do you migrate the schema without downtime?
• Where would you use a surrogate key versus the natural key, and what problem does each one cause?
• How would the reporting model for monthly dashboards differ from this transactional schema?

Explain the difference between an inner join, a left join, and a full outer join, and provide a real-world sce

Medium · SQL & Database Architecture

Explain the difference between an inner join, a left join, and a full outer join, and provide a real-world scenario for each.

Approach

1. INNER JOIN keeps only rows with a match on both sides. Example: patients joined to visits this quarter, which lists only patients who were seen.
2. LEFT JOIN keeps every row from the left table and fills the right side with NULLs where nothing matches. Example: all active patients with their latest lab result, including those with none. Adding WHERE lab_result.id IS NULL turns it into an anti-join that lists patients with no lab result on file.
3. FULL OUTER JOIN keeps unmatched rows from both sides. Example: reconciling the EHR's encounter list against the billing system, where COALESCE on the keys lines them up and NULLs on either side show what one system has and the other lacks. MySQL has no FULL OUTER JOIN, so I emulate it with a LEFT JOIN UNIONed with the right-side anti-join.
4. The classic mistake: putting a filter on the right table in WHERE (WHERE visit.status = 'completed') silently turns a LEFT JOIN into an INNER JOIN. That condition belongs in the ON clause.
5. Two more checks: a one-to-many join multiplies rows, so I aggregate the many side before joining or the totals inflate. NULL keys never match under =, so rows with a missing patient_id drop out of an inner join.

Follow-up

• Your LEFT JOIN returns more rows than the left table has. Why, and how do you fix it?
• Write patients with no visit this year using LEFT JOIN, then with NOT EXISTS. Which do you prefer, and why avoid NOT IN here?
• How would you reconcile two systems whose patient identifiers do not match exactly?

How do you decide when to use a clustered index versus a non-clustered index?

Medium · SQL & Database Architecture

Approach

1. A clustered index stores the table's rows in key order at its leaf level, so a table can have only one. A non-clustered index is a separate B-tree holding the key plus a row locator (the clustered key, or a RID on a heap), so a table can have many.
2. For the clustered key I want narrow, unique, static and ever-increasing, such as a bigint identity. Every non-clustered index carries the clustered key, so a wide key bloats all of them. A random GUID key causes page splits and fragmentation on insert; NEWSEQUENTIALID() or a separate identity key avoids that.
3. Range scans and ORDER BY on the clustered key are cheap because the rows sit next to each other, which suits something like visits by date when that is the main access path. Non-clustered indexes serve selective lookups on other columns. Adding INCLUDE columns makes an index covering, which removes the key lookup back to the clustered index.
4. The trade-off is write cost. Each extra non-clustered index adds work to every INSERT, to every UPDATE of its columns, and to locking, so on a busy transactional table I add them for measured query patterns and check the missing-index and index-usage DMVs before adding or dropping one.
5. In PostgreSQL every index is secondary on an unordered heap. CLUSTER reorders the table once but does not keep that order, and index-only scans depend on the visibility map, so the same reasoning maps to INCLUDE indexes and VACUUM rather than a maintained clustered index.

Follow-up

• The execution plan shows a key lookup running a million times. What does that mean and how do you remove it?
• Would you put the clustered index on the primary key or on the date column that reports filter by? Why?
• When would a filtered index or a columnstore index beat both options for a reporting workload?

What steps do you take to enforce data governance and ensure data accuracy across multiple reporting systems?

Medium · Data Governance & Business Intelligence

Approach

1. I start with definitions. Each shared KPI gets one written definition (numerator, denominator, filters, time zone and grain) in a data dictionary with a named owner, and every reporting system reads from one certified dataset instead of recomputing the metric its own way.
2. I enforce correctness as close to the source as possible: NOT NULL, foreign key, UNIQUE and CHECK constraints in the operational and warehouse schemas, so bad rows are rejected rather than cleaned up later in a report.
3. In the pipelines I add automated checks before publishing: row counts and control totals reconciled against the source, null and duplicate rates, referential integrity, and freshness. A failed check blocks the load and alerts the owner, so no report shows silently wrong numbers.
4. I record lineage, from each dashboard field back to its source columns and transformations, and I put metric definitions under change control (review, versioning, a note to consumers), so when a number changes there is a record of why.
5. Access is part of governance: role-based permissions on certified datasets, audit logs of who queried sensitive tables, and a regular review of who still needs access.

Follow-up

• Two departments show different numbers for the same monthly KPI. Walk through how you find out which is right.
• How do you bring a new third-party data feed into the warehouse without lowering data quality?
• What do you do when a stakeholder refuses to retire a spreadsheet that contradicts the certified report?

Explain your approach to designing an ETL pipeline that must run nightly without disrupting 24/7 operational s

Medium · Data Governance & Business Intelligence

Explain your approach to designing an ETL pipeline that must run nightly without disrupting 24/7 operational systems.

Approach

1. I avoid loading the primary at all where I can. I extract from a readable secondary or replica, or capture changes with CDC or change tracking in SQL Server, or logical replication in PostgreSQL. That way the nightly job reads a change stream instead of scanning live tables.
2. I extract incrementally. Change Tracking or CDC in SQL Server, or logical replication in PostgreSQL, captures deletes; a rowversion column, like a modified_at timestamp, does not, because a deleted row simply disappears. Rowversion only removes clock and application-set timestamp errors. It is assigned at write time, not at commit, so a long transaction can still land below the stored watermark unless I cap the upper bound with MIN_ACTIVE_ROWVERSION(). I store the high-water mark only after a successful load.
3. When I must read the primary, I read under snapshot isolation so the job takes no shared locks, work in small keyed batches to limit lock duration and transaction log growth, and throttle between batches. A 24/7 system has no truly idle window.
4. I load into staging tables, run data quality checks there, and publish atomically: partition switching, or a rename/synonym swap. Reports never see a half-loaded table. Loads are idempotent (MERGE or upsert on business keys), so a rerun after a failure does not duplicate rows.
5. I make the job checkpointed and restartable, log row counts per step, and alert when it overruns its window or a check fails. Late-arriving records are handled by reprocessing a trailing window rather than ignoring them.

Follow-up

• The source table has no reliable last-modified column. How do you detect changed and deleted rows?
• The job failed halfway through the night. How do you recover without double-loading or leaving reports inconsistent?
• Data volume triples. What becomes the bottleneck first, and what do you change?

How do you ensure that sensitive data is masked or secured in your reporting environments?

Medium · Data Governance & Business Intelligence

Approach

1. I classify first. I tag columns that hold protected health information or other identifiers (name, MRN, date of birth, address, diagnosis, HIV status) so every control can key off that inventory instead of being decided table by table.
2. Least privilege is the main control: report users get roles that can read views or certified datasets exposing only the columns they need, never the base tables. Row-level security (SQL Server security policies with predicate functions, or PostgreSQL RLS policies) limits each user to their own clinic's or program's rows.
3. Masking has limits I state plainly. SQL Server Dynamic Data Masking hides values in result sets, but a user who can run ad hoc queries can infer masked values through WHERE clauses, so it supplements permissions rather than replacing them. Non-production copies get static de-identification; HIPAA Safe Harbor lists 18 identifier types to remove.
4. When reports need to link records without exposing the identifier, I tokenize with a keyed HMAC rather than a plain hash. MRNs have a small value space, so an unkeyed hash can be reversed by brute force. Aggregate reports suppress small cells so that a count of one or two cannot identify a patient.
5. I encrypt in transit (TLS) and at rest (TDE, or Always Encrypted for the most sensitive columns), audit who reads sensitive tables, and review exported extracts and dashboard sharing settings, because data often leaks through an export rather than through the database.

Follow-up

• An analyst needs to join patient data across two systems but must not see identities. How do you set that up?
• Why is Dynamic Data Masking not enough on its own for a user with ad hoc query access?
• How do you keep developers productive with realistic test data without copying production PHI?

How would you troubleshoot and optimize a query that is causing a deadlock in a production database?

Medium · SQL & Database Architecture

Approach

1. I first separate a deadlock from plain blocking. Blocking is one session waiting on another. A deadlock is a cycle, which the engine breaks by killing a victim: error 1205 in SQL Server, SQLSTATE 40P01 in PostgreSQL. The fix differs, so I confirm which one it is before changing anything.
2. I capture the evidence. In SQL Server the system_health Extended Events session records xml_deadlock_report graphs showing each process, its statement, and the locks held and requested. In PostgreSQL the server log prints the processes and queries involved, and log_lock_waits records long waits past deadlock_timeout.
3. From the graph I find the cycle. The usual causes are two code paths updating the same tables in opposite order, a missing index that makes an UPDATE or DELETE scan and lock far more rows than it needs, a long transaction holding locks while it does unrelated work, and lock escalation to a table lock.
4. Fixes, in order: access tables and rows in one consistent order; add the index that narrows the locked range; shorten transactions and move reads and computation outside them. For reader-versus-writer conflicts, use row versioning (READ_COMMITTED_SNAPSHOT in SQL Server) so reporting reads stop taking shared locks.
5. Deadlocks can still happen after that, so the caller catches the deadlock error and retries with backoff. The engine has already rolled back the victim's whole transaction, so rerunning its database work is safe as it stands; idempotency only matters for side effects outside the transaction, such as an external call or a message, or when the retry wraps several transactions. Then I check that the deadlock count drops over time.

Follow-up

• Would adding WITH (NOLOCK) to the reporting queries fix this? What does it cost?
• A stored procedure that runs for minutes blocks reporting queries but never deadlocks. How does your approach change?
• How do you make a retried transaction safe when it inserts rows and calls another system?

Suggested practice checklist

The plan follows the reported loop: SQL and joins first, then schema and index design, concurrency and stored procedures, ETL and governance, BI tooling, and finally the stakeholder and mission stories the panel round draws on.

Day 1 — Set up a practice database and map the loop

• Create a small PostgreSQL or SQL Server schema for patients, visits, medications and pharmacy fills, with sample data that includes duplicates, gaps and missed visits.
• Write the three reported rounds on one page and next to each note what you will need: SQL fluency, design reasoning, panel stories.
• Rate your readiness on joins, window functions, indexing, deadlocks, ETL and masking, and mark the two weakest.

Deliverable: A runnable schema and sample-data script, plus a one-page loop map with your weakest topics marked.

Day 2 — SQL: ranking, gaps and joins

• Write the second-highest-salary query three ways and test each with ties, a single employee and an empty table.
• Write the query for patients with consecutive missed appointments using LAG or row-number gaps, and test it on your sample data.
• Write inner, left, full outer and anti-join queries for patients and visits, and prepare a one-sentence real-world scenario for each.

Deliverable: A SQL file with the ranking, gap and join queries and the edge-case inputs you tested them against.

Day 3 — Schema design and index choice

• Take a vague requirement (track visits, medications and pharmacy fulfillment with fast monthly dashboards) and list the questions you would ask before drawing tables.
• Design a normalized transactional schema, then a star schema for the dashboards, with keys, constraints and one slowly changing dimension.
• Choose a clustered and several non-clustered indexes for your schema and check the effect in an execution plan.

Deliverable: An entity diagram, DDL for both models, and an index decision table that says which query each index serves.

Day 4 — Concurrency, stored procedures and triggers

• Reproduce a deadlock between two sessions that update the same two tables in opposite order, and read the graph or log it produces.
• Fix it with consistent ordering and shorter transactions, then rerun both sessions to confirm.
• Take a slow stored procedure that blocks a reporting query and practise the tuning sequence: read the plan, find the scan or spill, fix and re-measure.
• Work through stored procedure and trigger pitfalls: parameter sniffing (OPTION (RECOMPILE) or OPTIMIZE FOR), set-based triggers that handle multi-row inserted and deleted tables, and when an audit trigger should give way to temporal tables or CDC.

Deliverable: A short write-up with the deadlock reproduction, its fix, before-and-after plan notes and a set-based trigger you could explain aloud.

Day 5 — ETL, governance and masking

• Outline a nightly ETL design: staging tables, incremental loads, idempotent re-runs, row-count reconciliation, load windows that avoid live-system contention and handling of bad records.
• Write a governance checklist covering constraints, reconciliation between reporting systems, a data dictionary with owners and audit logs.
• Design how sensitive columns are masked or restricted in a reporting database, including role-based access and de-identified views.

Deliverable: An ETL diagram, a governance checklist and a masking design, each short enough to explain in a minute or two.

Day 6 — BI tooling, bad data and competing requests

• Build one SSRS report parameterised on a stored procedure, or a Power BI report on your practice database, and be ready to compare Power BI import with DirectQuery against a live SQL Server and explain how row-level security carries through to the report.
• Write your dashboard story: the decision it supported, how you picked each KPI, how you validated it and how you measured use.
• Write your bad-data story: how you sized the damage, what you told the stakeholder and what check you added.
• Write your prioritization method for simultaneous urgent requests and rehearse it with a conflicting-request example.

Deliverable: One working report with notes on its data connection and security, plus three story outlines (dashboard, bad data, prioritization), each with a decision, a trade-off and a result.

Day 7 — Mission, stakeholders and a full rehearsal

• Write the mission answer with one specific reason and a link to database work, and rehearse the non-profit-versus-tech-company follow-up.
• Prepare your scope-change, difficult-stakeholder and resisted-best-practice stories, using the five stated values only where they fit.
• Run a full mock: one SQL query, one schema discussion, one ETL walk-through and two stories, spoken aloud and recorded.

Deliverable: A recorded mock session and a list of the three answers you will tighten before the interview.

Mission, stakeholder and data stories

Candidates report that mission and values questions start at the recruiter screen and that the panel covers behavioral fit alongside technical problem-solving. For a database developer, strong stories show a decision you made about data: a definition you agreed with stakeholders, a pipeline you protected, or a practice you introduced. Rehearse each with the situation, your action, the trade-off and the outcome.

Describe a time you built a BI dashboard. How did you choose which KPIs to display?

Medium · Data Governance & Business Intelligence

Approach

1. Pick a dashboard where you chose the KPIs, not one you were handed. Open with the decision it supported and who made it, for example a clinic manager reviewing missed appointments each month, then show how each KPI maps to a decision.
2. Describe how you turned a vague request into metrics: interview the requester, write a one-line definition of each KPI (numerator, denominator, grain, source table, owner) and cut any metric nobody could act on.
3. Show validation: reconcile the dashboard totals against the source system before launch, and say what tolerance you accepted and why.
4. Close with adoption evidence such as usage, a decision that changed or a manual report that was retired, and one thing you would change, such as fewer KPIs or an earlier review with users.

Follow-up

• Two departments define the same KPI differently. How do you decide whose definition the dashboard uses?
• Which KPI did you remove, and how did the requester react?
• How did you keep the numbers right after launch when the source data changed?

How do you handle a situation where a stakeholder requests a report, but the underlying data is incomplete or

Medium · Data Governance & Business Intelligence

How do you handle a situation where a stakeholder requests a report, but the underlying data is incomplete or corrupted?

Approach

1. Start by sizing the problem with queries rather than opinions: null and duplicate rates on key columns, row counts against the source, date gaps, orphaned foreign keys. Say what is affected, since which reports and periods are hit decides the response.
2. Trace the cause upstream (a failed ETL load, a source-system change, a bad join) and separate a fix to the pipeline from a correction of the stored data.
3. Tell the stakeholder early, with what is known, what is not and a time for the next update. Offer options: a partial report with the gaps clearly labelled, a delayed complete report, or an interim figure with its caveat. Never fill gaps silently or present a number you cannot defend.
4. Make it permanent: add constraints, row-count reconciliation and an alert to the pipeline, and record the incident. Mention that corrupted extracts containing patient data are not emailed around while you investigate.

Follow-up

• The stakeholder needs the number today and will accept the risk. What do you do?
• How did you decide between a partial report and waiting for the fix?
• What check would have caught this before the stakeholder saw it?

Why are you passionate about the mission of AIDS Healthcare Foundation?

Medium · Behavioral & Mission Alignment

Approach

1. Give one specific reason that is true for you, such as a personal or professional connection to HIV/AIDS care, public health or data work in healthcare, and say what produced it. A generic statement about helping people is the answer that sounds rehearsed.
2. Link it to the job: the data systems behind clinics and pharmacies affect how fast accurate information reaches the people giving care, so database work and governance are how you take part in the mission.
3. Cover the non-profit angle the question implies. Say why you would choose a healthcare non-profit over a conventional tech employer, and show you understand resources may be tighter and priorities may shift, which connects to the stated Nimble value.
4. Finish with what you contribute: your database, ETL or governance experience and one example of data work that improved something for end users. Do not recite the list of values; show one or two through behaviour.

Follow-up

• Why a non-profit healthcare organisation rather than a traditional tech company?
• What do you already know about the services the foundation runs, and how does data support them?
• How would you stay motivated when a project is routine maintenance rather than something visibly mission-related?

Tell me about a time you had to adapt quickly to a sudden change in project scope. How did you demonstrate a "

Medium · Behavioral & Mission Alignment

Tell me about a time you had to adapt quickly to a sudden change in project scope. How did you demonstrate a "nimble" approach?

Approach

1. Choose a real change that arrived after work had started, such as a new report requirement, a source system switch or a reprioritised project. State the change and its deadline in two sentences.
2. Show your re-planning: what you kept (schema, loaded data, tested logic), what you cut or deferred, what you reordered, and how you checked the new plan against what was already built.
3. Show communication: you told the stakeholder what the change cost in time or risk and agreed the new scope in writing before continuing.
4. Define what nimble meant in practice: a shorter feedback loop, a smaller first release or a reusable component, not extra hours. Give the result and one lesson, for example designing views so a reporting change does not force a schema change.

Follow-up

• What did you decide not to do, and who agreed to that?
• Did the change break anything you had already built, and how did you find out?
• What would you do at the start of the next project to make this kind of change cheaper?

Describe a situation where you had to build a strong working relationship with a difficult stakeholder.

Medium · Behavioral & Mission Alignment

Approach

1. Name what made the stakeholder difficult in neutral terms: shifting requirements, distrust of the numbers, urgency on everything or reluctance to give time. Avoid a story where the other person is simply the villain.
2. Explain how you diagnosed their real concern. Often it is a past report that was wrong, or pressure from their own leadership, and asking what decision they need to make changes the conversation.
3. Describe concrete relationship-building actions: a regular short check-in, a prototype shown early, definitions agreed in writing, and a plain-language explanation of the limits of the data.
4. State the outcome with evidence, such as fewer rework cycles, a request that arrived with clear acceptance criteria or a stakeholder who now asks for your input first, and name what you changed about your own approach.

Follow-up

• What did you do when they still disagreed with your recommendation?
• How did you keep the relationship intact when you had to say no?
• What did you learn about your own communication from that situation?

Give an example of how you have advocated for a technical best practice that your team initially resisted.

Medium · Behavioral & Mission Alignment

Approach

1. Pick a practice you could defend with detail, such as version-controlled database scripts, peer review of stored procedures, a data dictionary or naming standards. It should have a stated cost of the status quo, such as an incident or repeated rework.
2. Explain why the team resisted (extra effort, unfamiliarity, a belief that it slowed delivery) and show you listened to it. Dismissing the objection is the mistake that sinks this story.
3. Describe how you made the case without escalating: measure the problem, run a small pilot on one project, reduce the friction with a template or script, and ask the sceptics to shape the rules.
4. Report adoption and results (incidents avoided, review time, onboarding speed), and say what you gave up or changed to get agreement.

Follow-up

• What if the team had still refused after the pilot?
• How did you know the practice caused the improvement?
• Tell me about a time you decided not to push a best practice, and why.

How do you prioritize your workload when receiving urgent data requests from multiple departments simultaneous

Medium · Behavioral & Mission Alignment

How do you prioritize your workload when receiving urgent data requests from multiple departments simultaneously?

Approach

1. Describe a triage method, not a mood. Route every request through one queue, and for each ask what decision it supports, who is waiting and by when, and what happens if it slips.
2. Rank by impact and deadline, with anything affecting patient care or a compliance obligation ahead of convenience reports. Then size the effort, so a quick win can be done between larger items.
3. Negotiate with requesters instead of silently choosing: offer a smaller first version, a later delivery date or an existing report that answers part of the question.
4. When two departments both insist theirs is first, take the conflict to a shared manager with the trade-off set out, then tell both requesters the order and why. Afterwards reduce repeat requests by turning common asks into scheduled reports or self-service views.

Follow-up

• Two department heads each say their request is the top priority. What do you do?
• A request needs sensitive patient data. How does that change how you handle it?
• How would you cut the volume of ad hoc requests over time?