Data and databases

What interviewers look for

• Excel under time pressure: clean a messy sheet, build a pivot table, use XLOOKUP or VLOOKUP, and check the totals.
• Care with data quality: you spot duplicates, blanks, wrong dates and impossible values before you analyse anything.
• Survey data skills: a KoboToolbox or ODK form with constraints and skip logic, and cleaning what comes back from the field.
• Turning numbers into a clear message: a simple chart and two sentences a manager can act on.
• Data protection: personal data of beneficiaries, customers or patients kept safe, shared only when allowed, and removed from reports.
• Honesty about numbers: you report what the data shows, even when it disappoints the programme.

Questions they ask

1. 1“Survey data from five districts does not match the totals in the field reports. What do you do?”

   Why they ask: It tests your data quality method, and whether you find the cause instead of adjusting numbers to make them fit.

   How to answer

   • Never adjust numbers to match; find where the gap comes from.
   • Check duplicates, test entries, unsynced forms, wrong district codes and the date range.
   • Record every change in a cleaning log and share it with the M&E lead.

   I wouldn't change any numbers to make them match. First I check for duplicates using the submission ID, and remove test entries made before the start date. Then I compare the forms per enumerator with what each team leader reported, because the gap is often forms still saved on a phone that hasn't synced. I also check district codes, since one wrong code moves a whole village. Every change goes in a cleaning log, which I share with the M&E lead.

2. 2“What is the difference between VLOOKUP and XLOOKUP, and why does a lookup sometimes return #N/A?”

   Why they ask: Lookups are in almost every data test. They check you understand them and can fix the common errors.

   How to answer

   • VLOOKUP searches the first column and returns a value to the right; use FALSE for an exact match.
   • XLOOKUP looks in any direction, matches exactly by default, and can show a message when nothing is found.
   • #N/A usually means extra spaces, different spelling, or numbers stored as text.

3. 3“How would you design a KoboToolbox or ODK form to reduce errors at the source?”

   Why they ask: Good forms save days of cleaning. They check you think about the enumerator in the field, not only the analysis.

   How to answer

   • Choice lists instead of free text, and constraints such as a sensible age range.
   • Required fields for key questions and skip logic so people only see questions that apply.
   • Automatic date and GPS, and a field test with enumerators before launch.

   I'd make most questions choice lists instead of free text, so we don't get ten spellings of one district. I add constraints, like age between 0 and 120, and make the key questions required. Skip logic hides questions that don't apply, so a household with no children never sees the school questions. The form records the date and GPS automatically. Before launch, I test it with two enumerators in the field for a day and fix whatever confuses them.

4. 4“Tell us about a dashboard or report you built. Who used it, and what decision did it support?”

   Why they ask: They want to see that you build for a user and a decision, not charts for their own sake.

   How to answer

   • Name the user and the question the dashboard answered.
   • Say where the data came from and how often it refreshed.
   • Give one decision it changed, and one design choice you made for the user.

   At an INGO in Garowe I built a Power BI dashboard for the WASH team's monthly water-point monitoring. The data came from a KoboToolbox form and refreshed weekly. The main page showed which water points were not working, by district, and for how long. The programme manager used it in the monthly meeting to decide where to send the repair team first, instead of going by who called loudest. I kept it to one page, because the managers did not scroll.

5. 5“What SQL can you write? Talk us through a query that counts customers by region for this year.”

   Why they ask: Telecoms, banks and larger NGOs keep data in databases. They check you can get your own data out without help.

   How to answer

   • Build it step by step: SELECT region and COUNT(*), FROM the customers table.
   • WHERE the registration date is this year, GROUP BY region, ORDER BY the count.
   • Mention JOIN if the region name sits in another table, and say honestly how much SQL you have used.

6. 6“A manager asks you to send the beneficiary list, with names and phone numbers, to a partner organisation by WhatsApp. What do you do?”

   Why they ask: Beneficiary data can put people at risk. They check your judgement: agreements, consent, minimum data and a safe channel.

   How to answer

   • Check there is a data sharing agreement and that people agreed to their details being shared.
   • Share only the fields the partner needs, through an approved, protected channel, not WhatsApp.
   • Get written approval, and if something is missing, explain the risk politely.

   I'd first check whether we have a data sharing agreement with that partner, and whether people agreed to their details being shared. If so, I'd send only the fields the partner needs, perhaps names and phone numbers for one district, not the household details. I'd use our organisation's approved file sharing with a password, not WhatsApp, and ask the manager to approve it in writing. If there is no agreement, I'd explain the risk politely and suggest we set one up first.

7. 7“Your analysis shows the programme did not reach its target. How do you report it?”

   Why they ask: Integrity with numbers matters more than good news. They check you stay accurate and still help the team understand why.

   How to answer

   • Check the data first, so you are sure the gap is real.
   • Report the number accurately, with the denominator and the period.
   • Add what the data suggests about why, and share it with your lead before the wider report.

Example answers are in English, the language most panels use. Say it in your own words.

Topics to revise

• Pivot tablesA pivot table summarises thousands of rows by category in seconds: counts, sums and averages by district, sex or month. Know rows, columns, values and filters, and refresh after the data changes. A typical task: number of households by district and sex of the head of household.
• VLOOKUP, XLOOKUP and INDEX/MATCHLookups bring a value from another table using a shared key, such as a beneficiary ID. VLOOKUP needs FALSE for an exact match and only looks to the right; XLOOKUP is more flexible; INDEX/MATCH works in older Excel. Know why #N/A appears and how IFERROR hides it safely.
• Data cleaning in Excel and Power QueryRemove extra spaces with TRIM, fix capitals with PROPER, remove duplicates, split columns, and fix dates and numbers stored as text. Keep the raw data untouched on its own sheet and record changes in a cleaning log. Power Query repeats the same cleaning each month with one click.
• COUNTIFS, SUMIFS and IFCOUNTIFS counts rows that meet several conditions, such as women over 18 in one district. SUMIFS adds values the same way. IF puts rows into groups, for example “under 5” or “5 and over”. Expect a question that combines two of them.
• KoboToolbox and ODK (XLSForm)Forms are written in Excel as an XLSForm: the survey sheet (type, name, label), the choices sheet, and columns such as constraint, relevant (skip logic), required and calculation. Data is collected offline on phones and synced later, then exported to Excel or CSV. They may ask what the relevant column does.
• SQL basics (SELECT, WHERE, GROUP BY, JOIN)SELECT chooses columns, WHERE filters rows, GROUP BY with COUNT or SUM summarises, ORDER BY sorts, and JOIN links tables on a shared key. INNER JOIN keeps only matches; LEFT JOIN keeps every row from the first table. Tests often give two small tables and ask for one query.
• Power BI (Power Query, relationships, DAX)Power Query loads and cleans the data, the model links tables by key, and DAX measures such as SUM, COUNTROWS and CALCULATE do the maths. Slicers let users filter. Know how the data refreshes and who can see the published report.
• Dashboard designOne main question per page, key numbers at the top, bar charts rather than pie charts for comparisons, the same colour for the same thing, and a title that states the message. Show the date of the last refresh. Design for the person who will use it, on the screen they use.
• Mean, median and percentagesThe median is better than the mean when a few values are extreme, such as household income. Every percentage needs its denominator: 40% of whom? Break results down by sex, age and district, and do not claim one thing caused another without evidence.
• Data protection and anonymisationPersonal data includes names, phone numbers, ID numbers and GPS points; health and protection cases are extra sensitive. Collect only what you need, limit who can open it, protect files with passwords, and remove identifiers before sharing. Small numbers in a small village can still identify someone.

Practical tasks you may get

1. 1Timed Excel test (45 to 90 minutes): a messy file of beneficiaries or sales to clean, de-duplicate, summarise with pivot tables and lookups, chart, and describe in a few sentences. Keep the raw data on its own sheet, label everything and check totals. Practise on a public dataset with a timer running.
2. 2Survey form task: build a short KoboToolbox or ODK form with choice lists, constraints and skip logic, or find the errors in an XLSForm. Practise by building a 15-question household form in a free KoboToolbox account and testing it on your phone.
3. 3SQL test: write queries on a small database to filter, count by group and join two tables. Practise with SQLite or a free online SQL practice site until you can write them without looking up the syntax.
4. 4Dashboard and presentation: build a one-page Power BI or Excel dashboard from given data, then present three findings in five minutes to a panel member who is not technical. Practise explaining each chart in two sentences: what it shows and what to do about it.

Portfolio questions

• Bring two or three samples: a dashboard, a cleaned dataset with its cleaning log, or a short analysis report. Use public or made-up data, never real personal data from an employer.
• For each sample, be ready to say the question it answered, the data source, your steps and the decision it supported.
• Publish one dashboard built on open humanitarian or national statistics data, so the panel can open it on the day.
• Expect “How did you check the numbers were right?” Show how you compared your totals with the source.

Mistakes to avoid

• Cleaning directly in the raw data with no copy and no log, so nobody can check or repeat the work.
• Charts with many colours, 3D effects, or a pie chart with twelve slices.
• Sending files with names and phone numbers by email or WhatsApp without permission.
• Giving numbers with no denominator or date: “300 reached” out of how many, and by when?
• Changing or hiding numbers so the programme appears to meet its target.
• Claiming Power BI or SQL after one video. The timed practical shows the truth in minutes.

Quick check

5 questions. Answer each one to see the explanation.