Use this guide to prepare for Database or Data Engineer interviews, with a focus on postgresql, live vs extract, mysql. Explain your reasoning and connect it to experience you can substantiate.
These preparation themes come from the questions in this role’s bank. They help you organise your examples; individual employers may assess different things.
PostgreSQL
Live vs extract
MySQL
Centre of excellence
MongoDB
Redis
A useful preparation sequence
Choose your experience level and the round you expect.
Answer one question in your own words before opening its guide.
Compare your reasoning, evidence and trade-offs; adapt the answer to your experience.
Practise the follow-up, then revisit one answer you want to improve.
Representative questions and answer guidance
Open any question to read its answer. The complete guidance is included on this page.
Technical · Mid-level
1. Why might PostgreSQL use a sequential scan even when an index exists?
Answer guide
The planner chooses the cheapest plan by estimated cost, not by whether an index exists. If a query needs a large share of the table, reading it sequentially is cheaper than thousands of random index lookups. Other causes are a very small table, stale statistics that mislead estimates, a predicate that cannot use the index such as a function on the column, or a data type mismatch. Use EXPLAIN ANALYZE to compare estimated and actual rows and see real timings. Disabling sequential scans is only a diagnostic step, never a production setting.
What this question explores
Understanding that PostgreSQL chooses plans by cost and estimates, and a method to investigate rather than force them.
Common mistakes
Assuming the planner is wrong whenever it ignores an index and switching off sequential scans.
Ignoring statistics and the fraction of rows a predicate returns.
Practise a follow-up
How would you tell from EXPLAIN ANALYZE that row estimates are wrong?
When would a partial index or an expression index be the right answer?
2. What is the difference between a live connection and an extract in Tableau?
Answer guide
A live connection sends queries to the source database every time a view loads, so users always see current data but performance depends on that database and network. An extract is a compressed snapshot stored in Tableau's own columnar format, which is usually faster and works offline, but it only changes when it is refreshed. I would choose live when freshness matters and the database is fast, and an extract when the source is slow, busy, or a flat file. Extracts can also be filtered or aggregated before loading to keep them small.
What this question explores
Whether you understand the freshness versus speed trade-off and can pick the right connection type for a given source.
Common mistakes
Saying extracts are always better without mentioning that the data goes stale between refreshes.
Forgetting that live connections put query load on the production database and depend on its speed.
Practise a follow-up
How would you keep an extract reasonably fresh for a daily report?
When would a live connection to a cloud warehouse still be the better choice?
3. What does the leftmost-prefix principle imply for a composite MySQL index?
Answer guide
In a composite index the entries are sorted by the first column, then the second, and so on, so the index helps most when the query filters on columns from the left without gaps. A range condition on an earlier column limits how much of the later columns can narrow the search. Put equality columns first and the range or sort column last. The trade-off is that one index cannot serve every query shape. Confirm with EXPLAIN for your real queries and version, and drop indexes that nothing uses.
What this question explores
Whether you can reason about column order in composite indexes and design indexes around real query patterns.
Common mistakes
Believing a composite index helps equally for a filter on any one of its columns.
Creating many overlapping indexes rather than designing one around actual queries.
Practise a follow-up
How does a range condition on the first column affect use of the second column?
How would you decide the column order for an index that serves two different queries?
4. A PostgreSQL transaction runs for hours and table bloat grows. How are those related?
Answer guide
PostgreSQL keeps old row versions so running transactions see a consistent snapshot. Vacuum can only remove dead tuples that no active transaction could still need, so one transaction open for hours pins the cleanup horizon and dead rows pile up as bloat, hurting scans and cache use. Idle-in-transaction sessions, forgotten replication slots and prepared transactions have the same effect. I would look at pg_stat_activity for the oldest transaction, shorten or split the work, and set timeouts for idle sessions. Monitoring the age of the oldest transaction alerts you before the table swells.
What this question explores
Whether you can connect long transactions to vacuum limits and bloat, and know the operational levers to find and prevent it.
Common mistakes
Blaming autovacuum settings alone and making it more aggressive while the long transaction still blocks cleanup.
Saying deleted rows are removed immediately, so bloat cannot come from a transaction.
Practise a follow-up
What would you check before terminating a session?
How do replication slots contribute to the same problem?
5. How would you set up a Tableau centre of excellence to scale analytics across a large organisation?
Answer guide
I would start with a clear mandate linked to business outcomes, a sponsor at executive level, and a small team covering platform administration, data modelling, design standards and training. The centre would define governance, certified data sources, templates and a community of champions in each business unit, so most work happens close to the users. I would set measurable goals such as adoption, time to insight, reduction of duplicate reports and cost per user. Funding and priorities should be agreed with the leadership, and the model should enable teams rather than become a bottleneck for requests.
What this question explores
Whether you can connect a platform capability to business outcomes and balance central control with federated ownership.
Common mistakes
Building a central team that owns all report requests, which becomes a bottleneck.
Defining success by number of dashboards rather than decisions improved or time saved.
Practise a follow-up
How would you measure adoption and value?
How would you fund the centre across business units?
6. When would you embed related data rather than reference another collection?
Answer guide
Embed when the related data is read together with its parent, is updated with it, and stays small and bounded, such as a few address lines or order line items, because one read then returns everything and a single-document write stays atomic. Embedding risks oversized documents and duplicated data that is hard to update everywhere; referencing needs extra lookups or joins-style queries. Design from your actual access patterns rather than from an ideal schema. I would sketch the main queries first, then check document size growth and the cost of updating duplicates.
What this question explores
Ability to model MongoDB data around access patterns and weigh duplication against extra lookups.
Common mistakes
Embedding an unbounded array, such as all comments, and letting the document grow indefinitely.
Copying the relational normalisation habit and referencing everything, causing many extra lookups.
Practise a follow-up
What problems can an ever-growing embedded array cause in MongoDB?
How would you keep duplicated embedded data consistent when the source value changes?
7. How would you avoid stale data after adding Redis caching to a product API?
Answer guide
Decide who owns the truth, which is the database, and treat Redis as a disposable copy. On writes, either delete or update the cached entry after the database change succeeds, and accept that a race can still leave old data briefly, which a short TTL limits. Cache-aside is the common pattern. Add protection against a stampede when a hot key expires, for example locking or jitter on TTLs. Monitor hit rate, memory and evictions, and measure how often users see stale values. If data must always be exact, such as stock during checkout, read from the database.
What this question explores
Awareness of cache consistency, invalidation and the risk of treating a cache as the source of truth.
Common mistakes
Adding a cache with no invalidation plan and no TTL, so stale values live forever.
Writing to the cache but not the database, or treating Redis as the system of record.
Practise a follow-up
What is a cache stampede and how would you reduce the risk?
Which product data would you refuse to serve from cache, and why?
8. Why can a search index disagree temporarily with the system-of-record database?
Answer guide
A search engine such as Elasticsearch is a separate system fed from the primary database, and documents become searchable only after indexing and a refresh step, so there is always some delay, usually short but not zero. Failures, retries, bulk load backlogs or mapping errors can widen the gap, and deletes or updates can be missed. So the database remains the source of truth, and search is an eventually consistent view. Tell users that new items may take a moment to appear, and read critical detail pages from the database. Monitor indexing lag as a measured metric.
What this question explores
Understanding of eventual consistency between a source database and a search index, and how to manage it.
Common mistakes
Assuming search results are always instantly consistent with the database.
Treating the index as the source of truth and having no way to rebuild or reconcile it.
Practise a follow-up
How would you rebuild an index from the database without downtime?
What is an outbox pattern and how does it help keep search in sync?
9. How would you protect a Firestore collection that is consumed directly by a mobile app?
Answer guide
Security rules are the server-side gate for a client that talks straight to Firestore, so they must allow only what each signed-in user needs. Validate incoming fields, types and sizes in the rules, and never rely on hiding client code or on app-side checks, since anyone can call the API directly. Operations that need broad access, such as admin actions or cross-user changes, belong in trusted backend code. Write tests using the emulator for both allowed and denied cases, and review rules whenever the data model changes, because a permissive wildcard is the classic mistake.
What this question explores
Awareness that client-side apps need server-enforced rules, and how to write and test least-privilege access.
Common mistakes
Leaving test-mode rules or a wildcard allow in place after launch.
Enforcing permissions only in the app UI and assuming users cannot call the database directly.
Practise a follow-up
How would you write a rule so users can edit only their own profile document?
What kind of operations would you move out of the client into a trusted backend?
10. Why can a Snowflake query remain slow after increasing warehouse size?
Answer guide
A bigger warehouse adds compute, which helps when the work parallelises, but many slow queries are limited by something else: scanning too much data because micro-partitions are not pruned, a join that explodes rows, data skew where one node does most of the work, spilling to disk, or queueing because many queries share one warehouse. Open the query profile to see which step consumes time, how much data is scanned versus pruned, and whether spilling occurs. Since larger sizes cost more credits, prove the benefit by comparing runtime and cost, not just speed.
What this question explores
Ability to diagnose warehouse performance from evidence, and to separate compute problems from data-layout and query problems.
Common mistakes
Scaling up the warehouse as the first and only response to any slow query.
Ignoring pruning and spill information in the query profile.
Practise a follow-up
What in the query profile would suggest the query is spilling to disk?
When would a clustering key help, and when is it not worth its cost?
11. How would you reduce scanned bytes in a date-partitioned BigQuery table?
Answer guide
BigQuery charges and speed depend mainly on bytes scanned. On a date-partitioned table, filter directly on the partition column with a simple condition so that unneeded partitions are skipped; wrapping the column in a function or comparing it to an expression that cannot be evaluated early may stop pruning. Select only the columns you need, never SELECT star, because storage is columnar and each extra column adds cost. LIMIT alone does not reduce bytes billed. Confirm by using a dry run or the job details to compare estimated and billed bytes before and after.
What this question explores
Understanding of how partitioning, column selection and clustering control the data scanned and cost in BigQuery.
Common mistakes
Believing LIMIT reduces the amount of data scanned and billed.
Using SELECT star and filtering the partition column in a way that prevents pruning.
Practise a follow-up
How would you enforce that analysts always filter on the partition column?
How does clustering differ from partitioning, and when would you add it?
12. What guarantees do Kafka partitions give for event ordering?
Answer guide
Kafka orders records only within a single partition: records that share a key go to the same partition and are read in the order written, but across partitions there is no global order. So choose the key around the entity whose order matters, for example an account ID, and accept that unrelated entities may interleave. Consumers in a group each get some partitions, so parallelism is capped by the partition count. If you truly need a single global order, you pay with one partition and no scaling. I would confirm the design by replaying sample events and checking sequence per key.
What this question explores
Clear knowledge of Kafka's per-partition ordering guarantee and how key choice shapes correctness and scale.
Common mistakes
Claiming Kafka guarantees ordering across a whole topic.
Choosing a random or null key and then expecting per-customer ordering.
Practise a follow-up
What happens to ordering guarantees if you increase a topic's partition count later?
How do producer retries interact with ordering, and how do you protect it?
Choose one answer containing an example or practical sequence. Explain what you would actually do, what you would check and when you would ask for help. Keep claims about your experience honest.
For technical or regulated work, check current documentation and applicable local requirements alongside this practice material.