A Database Architect designs data platforms for large applications and analytics, choosing database technologies, schema patterns, partitioning, indexing, replication and archiving strategies. They set standards for naming, security and change management, review designs from multiple teams and plan for scale and compliance. It is a senior role reached after years as a developer, DBA or data engineer.
Data architecture Scalability planning Partitioning Security design Technical leadership
Solved for this roleStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What are referential actions such as ON DELETE CASCADE?
Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.
A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.
03
Rewrite a non-SARGable date filter to use an index.
Slow:
SELECT * FROM orders
WHERE YEAR(order_date) = 2025
AND MONTH(order_date) = 3;
Fast:
SELECT * FROM orders
WHERE order_date >= '2025-03-01'
AND order_date < '2025-04-01';
The second form compares the raw column, so an index on order_date can be used with a range seek.
04
Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?
Aggregate in two levels. First compute total spend per customer and city in a CTE: SELECT city, customer_id, SUM(amount) AS spend FROM orders GROUP BY city, customer_id. Then in the outer query: SELECT city, COUNT(*) FROM cte WHERE spend > 100000 GROUP BY city. Trying to do it in one GROUP BY by city fails because the threshold applies to each customer, not each city.
Boyce-Codd Normal Form requires that for every functional dependency, the determinant is a candidate key. 3NF allows a dependency where the dependent attribute is part of a candidate key, while BCNF does not. For example, if a teacher teaches only one subject but a student-subject pair determines the teacher, the table is in 3NF but not BCNF and should be split.
06
How do you balance performance tuning with delivery deadlines?
Situation: a release was due in two days, but a key query was slow under load testing. Task: fix the risk without delaying the release. Action: I identified the top two bottlenecks from the plan, applied an index and a query rewrite that were low risk, and logged deeper redesign work as a follow-up ticket with data. Result: the release went live on time and the redesign was completed in the next sprint.
07
What are dirty reads, non-repeatable reads and phantom reads?
A dirty read sees uncommitted changes from another transaction that may roll back. A non-repeatable read gets different values when reading the same row twice because another transaction updated and committed it. A phantom read gets a different set of rows for the same query because another transaction inserted or deleted matching rows. Higher isolation levels prevent more of these.
Avoid indexes on small tables where scans are cheap, on columns with very low selectivity such as a boolean flag used alone, on tables with heavy writes and few reads, and duplicates of existing indexes. Every index slows inserts and updates and uses memory and storage. Review unused indexes periodically with database statistics and drop them after checking dependencies.
09
Your team debates using UUIDs or auto-increment integers as primary keys. What do you advise?
Auto-increment integers are compact, fast to index and naturally ordered, but expose counts and are hard to generate across distributed systems. UUIDs are globally unique and good for distributed inserts and merging data, but larger and random, causing index fragmentation. Time-ordered UUIDs such as UUIDv7 reduce fragmentation. Choose based on scale, distribution and security needs.
10
How do you handle disagreement with developers about schema changes?
Situation: a developer wanted to drop foreign keys to speed up inserts. Task: protect data integrity without blocking him. Action: I measured insert performance with and without constraints, showed the overhead was under five percent, and suggested batch inserts and proper indexes instead. Result: we kept the constraints, insert speed improved through batching, and orphan records stayed at zero.
11
Tell me about a database design decision you made and its outcome.
Situation: our app stored user preferences in a wide table with 60 nullable columns. Task: make it flexible for new settings. Action: I proposed a key-value preferences table with a JSON column for rarely used settings, kept frequently queried fields as columns, and wrote a migration. Result: new settings no longer needed schema changes and query speed for common fields stayed the same.
12
What are slowly changing dimensions in data warehousing?
Slowly changing dimensions track changes in dimension attributes over time. Type 1 overwrites old values with no history. Type 2 adds a new row with effective dates and a current flag, preserving full history, such as a customer moving city. Type 3 keeps a previous value column for limited history. Type 2 is most common when reports must reflect attributes as they were at transaction time.
13
What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?
A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.
Multi-Version Concurrency Control keeps multiple versions of rows, so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL stores old row versions in the table and cleans them with VACUUM, while MySQL InnoDB uses undo logs. MVCC enables high concurrency in read-heavy systems, but long-running transactions keep old versions alive and cause bloat.
15
Two users book the last seat at the same time and both succeed. How do you fix it?
This is a race condition. Wrap the check and booking in one transaction and lock the seat row with SELECT ... FOR UPDATE so the second transaction waits and then sees it is taken. Alternatively use an atomic update such as UPDATE seats SET booked_by = :user WHERE seat_id = :id AND booked_by IS NULL and check affected rows, plus a unique constraint as a final safety net.
16
Implement keyset pagination instead of OFFSET for large tables.
Slow for deep pages:
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 500000;
Keyset pagination:
SELECT * FROM orders
WHERE id > :last_seen_id
ORDER BY id
LIMIT 50;
OFFSET reads and discards all skipped rows, while keyset seeks directly using the index on id, keeping each page fast.
17
Tell me about a production incident involving a database and how you handled it.
Situation: during a sale, order placement started failing with lock timeouts. Task: restore service quickly. Action: I identified a long-running reporting query holding locks on the orders table, coordinated with the team to kill it, and moved that report to the read replica. Result: orders recovered within ten minutes, and we added query timeouts and a rule that reports never run on the primary.
18
A long-running report transaction is causing table bloat and blocking. What do you do?
Long transactions hold snapshots and locks, preventing cleanup of old row versions and blocking DDL or writers. Move the report to a read replica, break it into smaller batches, use read committed snapshot where supported, and avoid leaving transactions open in client tools. Set statement or idle-in-transaction timeouts, and monitor sessions with long transaction durations.
19
Inserts into a heavily used table have become slow. What could be causing it?
Too many indexes increase write cost, so audit and remove unused ones. Random UUID primary keys cause page splits and fragmentation. Triggers, foreign key checks without indexes on child columns, lock contention from long transactions, and log disk bottlenecks also slow inserts. Use batch inserts, sequential keys, proper indexing of foreign keys and monitor waits to find the actual bottleneck.
20
What is parameter sniffing and how do you handle it?
In databases that cache plans, such as SQL Server, the plan for a parameterised query is compiled using the first parameter values. If those values are unusual, the cached plan may be poor for typical values, causing sudden slowness. Fixes include OPTION (RECOMPILE) for that query, OPTIMIZE FOR hints, plan guides, splitting procedures for different data shapes, or updating statistics.
21
Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?
Capture deadlock graphs or logs to see which statements and resources are involved. Common fixes are accessing tables and rows in the same order in all code paths, keeping transactions short, adding indexes so updates lock fewer rows, avoiding user interaction inside transactions, and using lower isolation where safe. Add retry logic for deadlock victims since some deadlocks cannot be fully eliminated.
22
Produce region subtotals and a grand total in one query.
SELECT COALESCE(region, 'All Regions') AS region,
COALESCE(city, 'Subtotal') AS city,
SUM(sales) AS sales
FROM store_sales
GROUP BY ROLLUP(region, city)
ORDER BY region, city;
In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.
23
Show all dates in a month with sales, including days with zero sales.
WITH days AS (
SELECT generate_series(DATE '2025-01-01',
DATE '2025-01-31', INTERVAL '1 day')::date AS d
)
SELECT d.d, COALESCE(SUM(s.amount), 0) AS sales
FROM days d
LEFT JOIN sales s ON s.sale_date = d.d
GROUP BY d.d
ORDER BY d.d;
This PostgreSQL example uses a date series; other databases use a calendar table.
24
What is a deadlock and how do databases handle it?
A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.
25
What is table partitioning and how does it help performance?
Partitioning splits a large table into smaller physical pieces by a key, such as range partitions by month, list partitions by region, or hash partitions. Queries filtering on the partition key read only relevant partitions, called partition pruning. It also makes maintenance easier, such as dropping old months instantly. It does not help queries that do not filter on the partition key.
26
A business user needs a dashboard on a complex 6-table join that runs slowly. Would you use a view or a materialised view?
A normal view only simplifies the query; it still runs the full join every time, so performance does not improve. If the data can be a few minutes or hours old, a materialised view or summary table refreshed on schedule gives fast reads. If real-time data is required, tune the underlying query with indexes or build an incrementally maintained table.
27
An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
28
A query uses SELECT * on a table with 80 columns. Why is that a problem?
SELECT * fetches every column, increasing disk reads, memory, network transfer and application processing. It prevents covering indexes from being used and can break applications when columns are added or reordered. Select only required columns, which lets the optimiser use narrower indexes and makes the intent clear. SELECT * is acceptable only for quick exploration, not production code.
29
You added an index but the query still does a full table scan. Why might that be?
Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.
PostgreSQL:
CREATE FUNCTION log_salary() RETURNS trigger AS $$
BEGIN
IF NEW.salary <> OLD.salary THEN
INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at)
VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END IF;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_salary AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary();
31
A reporting team wants to query the production OLTP database directly. What are the risks and alternatives?
Heavy analytical queries can lock rows, use CPU and slow customer transactions, and normalised schemas make reporting complex. Alternatives are a read replica for reporting, a data warehouse loaded by ETL or CDC with a star schema, or materialised summary tables. Grant read-only access with limits if direct access is unavoidable, and schedule heavy jobs off-peak.
32
A query with many OR conditions across different columns is slow. How can you optimise it?
OR across different columns often prevents a single index seek. Rewrite it as UNION ALL of separate queries, each using its own index, removing duplicates carefully if needed, or use IN for multiple values of the same column. Some optimisers can combine indexes with bitmap or index merge plans. Compare plans and timings to choose the best approach.
33
The dashboard queries hit the production database and slow down the app at month end. What do you propose?
Separate workloads: route reporting to a read replica or data warehouse, pre-aggregate month-end metrics into summary tables refreshed after close, and cache results for dashboards. Tune the heaviest queries with indexes and better predicates. Schedule batch reports outside business hours and set resource limits or query timeouts for reporting users so customer transactions are protected.
34
Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?
Store the unit price, discount and tax in order_items when the order is created instead of reading the current product price, so history is not affected by updates. Maintain a product_price_history table with effective_from and effective_to dates for analysis. Reports on past orders use order_items values, while catalogue pages use the current price.
35
What is the difference between optimistic and pessimistic locking?
Pessimistic locking locks rows when read, using SELECT ... FOR UPDATE, so other transactions wait; it suits high-conflict cases such as seat booking. Optimistic locking does not lock but checks at update time that the row has not changed, usually with a version column: UPDATE ... SET version = version + 1 WHERE id = 1 AND version = 5. If zero rows update, the application retries. It suits low-conflict workloads.
A materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.
37
Why can't you use a window function in WHERE, and how do you filter on it?
Window functions are evaluated after WHERE, GROUP BY and HAVING in the logical order, as part of SELECT, so their results do not exist when WHERE runs. To filter, compute the window function in a subquery or CTE and filter in the outer query. Some databases such as Snowflake and BigQuery support a QUALIFY clause for filtering window results directly.
38
How would you design data archiving for a table growing by 10 million rows a month?
Partition the table by month so old partitions can be detached or moved to an archive table or cheaper storage without heavy deletes. Define a retention policy with business and compliance teams, for example 24 months online. Keep archived data queryable through a separate schema or warehouse, update indexes and statistics, and automate the process with monitoring and alerts.
A SARGable (Search ARGument able) predicate lets the optimiser use an index seek. Wrapping an indexed column in a function, such as WHERE YEAR(order_date) = 2025 or WHERE LOWER(email) = 'x', usually prevents index use. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. Leading wildcards and implicit type conversions also make predicates non-SARGable.
40
How do you read an EXPLAIN ANALYZE output in PostgreSQL?
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 101;
Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.
Frequently asked questions
What does a Database Architect do?
A Database Architect designs data platforms for large applications and analytics, choosing database technologies, schema patterns, partitioning, indexing, replication and archiving strategies. They set standards for naming, security and change management, review designs from multiple teams and plan for scale and compliance. It is a senior role reached after years as a developer, DBA or data engineer.
Which skills are tested in a Database Architect interview?
Expect questions on Data architecture, Scalability planning, Partitioning, Security design, Technical leadership. Most rounds also ask you to explain a project and solve one practical problem live.
How do I prepare for a Database Architect interview?
Revise Database Design, Indexes and Performance, Transactions and Advanced SQL first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.