Database Architect interviews sit a level above “can you write good SQL” or “can you tune a query” — they’re testing whether you can design a data platform that will still make sense in three years, under load nobody’s fully predicted yet, across teams who don’t all agree on priorities. That’s a fundamentally different skill than deep single-database expertise, even though it depends on having that expertise as a foundation.
Split into Beginner (1–30), Intermediate (31–70), and Advanced (71–100), every answer leans on trade-off reasoning — because that’s what this role actually is: a long series of “it depends, and here’s what it depends on” decisions made explicit and defensible.
One framing worth carrying through the whole list: almost every architecture question has multiple technically valid answers, and what actually distinguishes a strong architect isn’t picking the “correct” one — it’s correctly identifying which constraints matter most for this specific situation and explaining the trade-off honestly, including what you’re giving up.
Beginner Level
1. What is database architecture, and how is it different from database administration? Database architecture is the design of how data is structured, stored, accessed, and integrated across systems — the blueprint. Database administration is the ongoing operation and maintenance of a database against that blueprint — backups, performance tuning, security. An architect designs the system; a DBA keeps it running, though in smaller organizations one person often does both.
2. What is normalization, and why does it matter in database design? The process of organizing tables and columns to minimize data redundancy and avoid update anomalies, typically by breaking data into related tables connected via foreign keys. It matters because redundant data is prone to inconsistency — updating one copy of a fact but missing another — and wastes storage.
3. What are the first three normal forms, in plain terms? First Normal Form (1NF) requires atomic values — no repeating groups or multi-valued fields in a single column. Second Normal Form (2NF) requires every non-key column to depend on the entire primary key, not just part of it (relevant for composite keys). Third Normal Form (3NF) requires non-key columns to depend only on the primary key, not on other non-key columns.
4. What is denormalization, and when would an architect choose it deliberately? The deliberate reintroduction of redundancy into a normalized design to improve read performance for a specific access pattern, avoiding expensive joins — an architect chooses it when a known, high-frequency query pattern benefits more from that speed than the design loses from the added redundancy and write complexity.
5. What’s the difference between OLTP and OLAP systems? OLTP (Online Transaction Processing) systems handle many small, frequent read/write transactions — an e-commerce checkout system. OLAP (Online Analytical Processing) systems handle complex, large-scale analytical queries over historical data — a reporting warehouse. They’re optimized for genuinely different access patterns, which is why they’re often architected as separate systems rather than one system trying to serve both well.
6. What is a primary key, and why is choosing the right one an architectural decision, not just a technical one? A column (or set of columns) uniquely identifying each row. Choosing it is architectural because a natural key (like an email address) versus a surrogate key (an auto-generated ID) has downstream implications for stability (a natural key can change, breaking references), join performance, and how easily the schema can evolve later.
7. What is a foreign key, and what does it actually enforce? A column referencing another table’s primary key, enforcing referential integrity — ensuring a value in the referencing table must correspond to an existing value in the referenced table, preventing orphaned records that point to nothing.
8. What is an Entity-Relationship (ER) diagram, and why is it a foundational architecture tool? A visual representation of a database’s entities (tables), their attributes, and the relationships between them — foundational because it’s how an architect communicates and validates a data model with stakeholders and other engineers before any table actually gets created, catching design problems on a whiteboard rather than in production.
9. What’s the difference between a logical data model and a physical data model? A logical model describes entities, attributes, and relationships independent of any specific database technology — the “what.” A physical model translates that logical design into actual tables, columns, data types, and indexes for a specific database platform — the “how,” including platform-specific implementation details the logical model deliberately leaves out.
10. What is a schema, in the broader architectural sense? The overall structure defining how data is organized — tables, columns, relationships, constraints — for a database or a specific namespace within it. In architecture discussions, “schema design” broadly refers to the whole structural blueprint of how an application’s data is modeled.
11. What is scalability, and what’s the difference between vertical and horizontal scaling? Scalability is a system’s ability to handle increased load. Vertical scaling means adding more resources (CPU, memory) to a single existing machine — simpler, but with a hard ceiling. Horizontal scaling means adding more machines and distributing load across them — more complex to implement, but without the same hard ceiling.
12. What is data redundancy, and is it always bad? Storing the same piece of data in more than one place. It’s not always bad — deliberate, controlled redundancy (as in denormalization, or replication for availability) is a legitimate architectural tool; the problem is uncontrolled redundancy where copies can drift out of sync without anyone noticing or managing it.
13. What is a data warehouse, and how is it different from a regular operational database? A centralized repository specifically designed for analytical querying and reporting, typically consolidating data from multiple operational (OLTP) source systems, structured for read-heavy analytical access patterns rather than the fast, frequent transactional writes an operational database is optimized for.
14. What is ETL, and what do the three letters stand for? Extract, Transform, Load — the process of pulling data from source systems (Extract), reshaping and cleaning it (Transform), and loading it into a target system like a data warehouse (Load). It’s the standard pattern for moving data from operational systems into an analytical environment.
15. What’s the difference between a relational database and a NoSQL database, at a basic level? A relational database organizes data into structured tables with defined schemas and relationships, using SQL. NoSQL databases (document, key-value, column-family, graph) generally offer more flexible or entirely schema-less structures, often trading some of the strict consistency and relational guarantees for scalability or flexibility suited to specific access patterns.
16. What is a data dictionary, and why does an architect maintain one? A centralized, documented catalog describing every table, column, and relationship in a database — including meaning, data type, and constraints. An architect maintains it because a large system’s data model quickly becomes too complex for anyone to hold entirely in their head, and undocumented meaning (“what does this column actually represent?”) becomes a real source of costly mistakes.
17. What is a surrogate key, and why is it commonly preferred over a natural key? An artificially generated identifier (typically an auto-incrementing integer or a GUID) with no inherent business meaning, used as a primary key. It’s commonly preferred because it stays stable even if the “natural” business identifier it represents changes over time (a customer’s email address, a product’s SKU), avoiding a cascade of foreign key updates across related tables.
18. What is data integrity, and what are the main types? The overall accuracy and consistency of data over its lifecycle. Main types include entity integrity (every row uniquely identifiable, enforced by primary keys), referential integrity (foreign keys point to valid rows), and domain integrity (values fall within valid, expected ranges or types, enforced by constraints and data types).
19. What’s the difference between a data model and a database? A data model is the abstract design — the blueprint of entities, attributes, and relationships. A database is the actual, physical implementation of that model on a real system, storing real data — the model is the plan, the database is the built structure.
20. What is a composite key, and when would an architect use one? A primary key made up of two or more columns together, used when no single column alone is unique but the combination is — common in junction/bridge tables representing a many-to-many relationship between two other entities.
21. What is capacity planning, in the context of database architecture? Estimating future data volume and workload growth to ensure a system is designed and provisioned to handle that growth without a painful, disruptive re-architecture — an architect thinks ahead about storage, throughput, and scaling needs rather than only designing for today’s known volume.
22. What is a data lake, and how is it different from a data warehouse? A data lake stores raw data in its native format (structured, semi-structured, or unstructured) without requiring an upfront schema, typically at large scale and lower cost. A data warehouse stores structured, already-transformed data optimized for specific analytical querying — a lake is more flexible and cheaper for storage but requires more work to actually query meaningfully, while a warehouse is more structured but requires that structure to be defined upfront.
23. What’s the difference between structured, semi-structured, and unstructured data? Structured data fits neatly into rows and columns with a defined schema (a relational table). Semi-structured data has some organizational structure but isn’t strictly tabular (JSON, XML). Unstructured data has no predefined structure at all (images, free text documents, video) — each requires different storage and processing approaches.
24. What is a data governance framework, and why does an architect care about it? A set of policies and processes for managing data quality, security, ownership, and compliance across an organization. An architect cares because the technical design of a system needs to actually support governance requirements (who owns what data, how it’s classified, how access is controlled) — a technically elegant design that ignores governance requirements often needs painful retrofitting later.
25. What is a single point of failure, and why does an architect specifically design to avoid it? Any single component whose failure would bring down the entire system. An architect designs to avoid it because a system’s actual reliability is only as strong as its weakest, least-redundant link — identifying and eliminating single points of failure (a single database instance with no replica, a single network path) is core to designing for genuine availability.
26. What’s the difference between availability and durability in a data system? Availability is whether the system is up and responsive right now. Durability is whether committed data survives failures and remains intact over time, even if the system is briefly unavailable — a system can be durable (data is safe) while temporarily unavailable (can’t currently be accessed), and these are genuinely separate guarantees worth designing for independently.
27. What is a data pipeline, in simple terms? An automated sequence of steps that moves and transforms data from one or more sources to a destination — commonly used to describe the flow feeding a data warehouse or analytical system, encompassing extraction, transformation, and loading (or increasingly, load-then-transform) stages.
28. What is master data management (MDM), and what problem does it solve? A discipline and set of tools for creating a single, authoritative, consistent version of core business entities (customers, products, vendors) across an organization’s many systems — solving the problem where the same customer might exist slightly differently in five different systems, with no single trusted source of truth for who that customer actually is.
29. What’s a simple way to explain why “it depends” is such a common answer in architecture interviews? Because almost every architectural decision is a trade-off shaped by specific constraints — expected scale, budget, team expertise, consistency requirements — that vary from situation to situation; a good architect explains what it depends on and why, rather than treating “it depends” as a way to avoid committing to an actual recommendation.
30. What’s a reasonable first step when asked to design the data architecture for a brand-new application? Understand the actual requirements first — expected data volume and growth, read/write patterns, consistency needs, and existing team/technology constraints — rather than jumping straight to picking a specific database technology, since the right technology choice is a consequence of those requirements, not a starting assumption.
Intermediate Level
31. Explain the CAP theorem, and why a distributed system can’t have all three properties simultaneously. CAP theorem states a distributed data system can only guarantee two of three properties during a network partition: Consistency (every read receives the most recent write), Availability (every request receives a response, even if not the most recent data), and Partition tolerance (the system continues operating despite network failures between nodes). Since network partitions are a genuine possibility in any distributed system, partition tolerance is effectively non-negotiable in practice, meaning the real-world choice is actually between prioritizing consistency or availability when a partition occurs.
32. What’s the difference between strong consistency and eventual consistency, and when would an architect choose eventual consistency deliberately? Strong consistency guarantees every read reflects the most recent write immediately, everywhere. Eventual consistency guarantees that, given enough time without new updates, all replicas will converge to the same value — but a read might temporarily see stale data. An architect chooses eventual consistency deliberately for use cases where availability and performance under high write/read volume matter more than every single read being perfectly current (a social media like count, for instance), and where the business can genuinely tolerate that brief staleness window.
33. What is database sharding, and what are the main sharding strategies? Sharding splits a large dataset across multiple database instances (shards), each holding a subset of the data, to scale beyond what a single instance can handle. Common strategies include range-based sharding (splitting by a value range, like date), hash-based sharding (distributing based on a hash of a key, generally more even), and directory-based sharding (a lookup service mapping keys to specific shards, offering the most flexibility at the cost of an added lookup layer).
34. What are the main challenges introduced by sharding that a single-database design doesn’t have to deal with? Cross-shard queries and joins become significantly more complex and expensive, since data that would be a simple local join in a single database now potentially requires querying and combining results from multiple shards. Rebalancing shards as data grows unevenly, maintaining referential integrity across shard boundaries, and handling transactions spanning multiple shards are all genuinely harder problems that a single-database architecture doesn’t need to solve at all.
35. What’s the difference between database replication and sharding? Replication maintains multiple copies of the same full dataset across different nodes, primarily for availability and read scaling. Sharding splits a dataset into different, non-overlapping subsets distributed across nodes, primarily for write scaling and handling data volume beyond a single instance’s capacity — they solve different problems and are often used together (each shard itself replicated for availability).
36. What is a data mart, and how does it relate to a data warehouse? A data mart is a smaller, more focused subset of a data warehouse, typically scoped to a specific business function or department (a sales data mart, a finance data mart) — either built from and dependent on a broader central warehouse (a “dependent” data mart) or built independently for a specific need (an “independent” data mart, generally less recommended due to the resulting inconsistency risk across departments).
37. What’s the difference between a star schema and a snowflake schema in data warehouse design? A star schema has a central fact table connected directly to denormalized dimension tables. A snowflake schema further normalizes those dimension tables into additional related sub-tables — a snowflake schema saves some storage and reduces redundancy, but generally requires more joins for the same query, which is why star schemas are more commonly preferred for read-heavy analytical workloads despite their redundancy.
38. What is a slowly changing dimension (SCD), and what are Type 1 and Type 2, specifically? A slowly changing dimension is a dimension table whose attribute values change over time in a way that needs to be tracked or handled deliberately (a customer’s address changing, for instance). Type 1 simply overwrites the old value with the new one, losing history. Type 2 preserves history by creating a new row for the changed record (often with effective-date columns), letting you accurately query “what was true as of this date” — the choice depends entirely on whether the business genuinely needs that historical accuracy.
39. What’s the difference between ETL and ELT, and why has ELT become more common with modern cloud data platforms? ETL transforms data before loading it into the target system. ELT loads raw data into the target first, then transforms it there. ELT has become more common because modern cloud data warehouses have enough compute power to handle transformation efficiently within the warehouse itself, and loading raw data first preserves flexibility — you can re-derive different transformations later without re-extracting from the source again.
40. What is a data lakehouse, and what problem does it try to solve? An architecture combining a data lake’s flexible, low-cost storage of raw data with a data warehouse’s structured querying and transaction support (via technologies like Delta Lake or Apache Iceberg) — trying to solve the historical trade-off where you needed a lake for flexibility and cost, and a separate warehouse for structured, reliable analytical querying, by giving you both capabilities on a single underlying storage layer rather than maintaining two separate systems.
41. What’s the difference between horizontal partitioning and vertical partitioning? Horizontal partitioning splits a table’s rows across multiple physical structures (by date range, or a hash) while keeping the same columns in each partition. Vertical partitioning splits a table’s columns across multiple structures, typically separating frequently-accessed columns from rarely-accessed ones to improve performance for the common access pattern.
42. What is idempotency, and why does it matter for data pipeline design? An idempotent operation produces the same result no matter how many times it’s applied — re-running it doesn’t cause duplicate or incorrect side effects. It matters for data pipelines because failures and retries are inevitable in distributed systems; a pipeline step that isn’t idempotent risks double-counting or corrupting data if it has to be safely re-run after a partial failure, which is a very real, common operational scenario.
43. What’s the difference between a synchronous and an asynchronous integration pattern between two systems? Synchronous integration means the calling system waits for the other system to respond before proceeding — simpler to reason about, but couples the two systems’ availability and performance together tightly. Asynchronous integration (via a message queue or event stream) decouples them — the calling system doesn’t wait, improving resilience and independent scalability, at the cost of added complexity in handling eventual delivery and potential ordering issues.
44. What is a message queue, and what architectural problem does it solve? A system that lets one component send messages to another without requiring both to be available and responsive at the same instant — solving the tight-coupling and cascading-failure problem of direct synchronous calls between services, letting a consuming system process messages at its own pace, and providing a buffer during traffic spikes rather than the sender being blocked or failing outright.
45. What’s the difference between a data warehouse’s fact table grain and its overall schema design, and why does choosing the right grain matter so much? Grain refers to the level of detail a single row in the fact table represents — one row per individual transaction line item, versus one row per daily summary, for instance. Choosing the right grain matters enormously because it’s very difficult to go from summarized data back to finer detail later if a reporting need arises that the chosen grain can’t support, while starting at the finest reasonable grain preserves the flexibility to aggregate up to any coarser level later.
46. What is database federation, and when would an architect consider it over full data consolidation? Federation lets queries span multiple independent, physically separate databases as if they were one logical system, without physically consolidating the data into a single store. An architect considers it when full consolidation isn’t feasible or desirable (regulatory data residency requirements, organizational boundaries, or genuinely different systems of record that shouldn’t be merged), accepting the added query complexity and typically weaker performance compared to a truly consolidated single system.
47. What’s the difference between a strong schema-on-write approach versus a schema-on-read approach, and the trade-offs of each? Schema-on-write validates and enforces structure at the moment data is written, catching structural problems immediately but requiring the schema to be defined upfront and adding some write-time overhead. Schema-on-read stores data more flexibly and applies structure only when it’s queried, offering more flexibility for evolving or varied data (common in data lake scenarios) at the cost of potentially discovering structural inconsistencies only later, at query time, rather than immediately at ingestion.
48. What is data lineage, and why is it an architectural concern, not just an operational one? Data lineage tracks where a specific piece of data originated and every transformation it went through on its way to its current form. It’s an architectural concern because a system genuinely needs to be designed (with appropriate logging, metadata capture, and traceable pipeline steps) to support lineage tracking — it’s not something you can bolt on easily after the fact if the underlying pipeline architecture wasn’t built with that traceability in mind from the start.
49. What’s the difference between a transactional consistency model and an analytical consistency model, and why might an architect deliberately accept weaker consistency in an analytical system? A transactional system typically needs strong, immediate consistency, since incorrect intermediate states (like a partial financial transaction) are genuinely unacceptable. An analytical/reporting system often can tolerate some staleness or eventual consistency, since a dashboard reflecting data that’s a few minutes or hours old is usually acceptable, and relaxing that consistency requirement can meaningfully improve the analytical system’s scalability and performance without a real business cost.
50. What’s the significance of choosing between a relational database and a document database for a specific application’s data model, beyond just “SQL versus NoSQL”? The real question is how well the application’s natural data access pattern matches each model — a relational database excels when data has many genuinely relational, normalized connections queried in varied, ad hoc ways. A document database excels when data is naturally hierarchical and typically accessed as a whole cohesive unit (a product catalog entry with nested attributes, for instance), where forcing it into a normalized relational structure would mean reassembling that document via joins on every single read.
51. What is a change data capture (CDC) pattern, and why is it architecturally preferable to periodic full-table extraction for feeding a downstream system? CDC captures individual changes (inserts, updates, deletes) as they happen, typically by reading the source database’s transaction log, and streams just those changes to downstream consumers. It’s architecturally preferable to periodic full extraction because it dramatically reduces the load on the source system, provides near-real-time downstream freshness instead of only-as-current-as-the-last-batch-job, and avoids the wasted work of re-extracting unchanged data repeatedly.
52. What’s the difference between designing for read-heavy versus write-heavy workloads, at an architectural level? Read-heavy workloads generally benefit from denormalization, caching layers, read replicas, and indexes optimized for the specific common query patterns — accepting some write-side complexity in exchange for read speed. Write-heavy workloads generally benefit from normalized schemas (reducing what needs updating per write), minimizing index count (since every index adds write overhead), and architectures that can absorb write bursts (queuing, batching) rather than requiring every write to be immediately, synchronously durable everywhere.
53. What is a data contract, and why has it become an increasingly discussed architectural concept? A formal, agreed-upon specification of the structure, semantics, and quality guarantees a data producer commits to for a dataset it publishes, which downstream consumers can rely on. It’s become increasingly discussed because as organizations decentralize data ownership (data mesh-style architectures), the lack of an explicit contract between producer and consumer teams becomes a real, recurring source of silent breakage when a producer changes their data’s structure without warning consumers who depend on it.
54. What’s the difference between a data mesh and a traditional centralized data platform architecture? A traditional centralized architecture funnels all data through a single central team and platform (a central data warehouse team owning all ETL and modeling). A data mesh decentralizes ownership, treating data as a product owned and served by the domain teams that generate it, with federated governance ensuring interoperability — trading centralized control and consistency for scalability of ownership across a large organization where a single central team has become a genuine bottleneck.
55. What is a bounded context, and why does this concept from domain-driven design matter for database architecture? A bounded context defines a clear boundary within which a specific data model and its terminology are consistent and meaningful — the same term (like “Customer”) can legitimately mean something different in a billing context versus a support context. It matters for database architecture because trying to force a single unified data model across genuinely different bounded contexts often produces an overly compromised design that serves no context particularly well, whereas respecting the boundaries with appropriately scoped, separate models (even if it means some intentional data duplication) often produces a cleaner overall system.
56. What’s the trade-off consideration between a monolithic database architecture and a microservices-aligned “database per service” pattern? A monolithic shared database is simpler for cross-entity queries and transactions (a single database can enforce consistency across everything), but creates tight coupling between services that should ideally be independently deployable and scalable. Database-per-service improves service independence and scalability but pushes cross-service data consistency and querying complexity into the application layer (via APIs, events, or a dedicated query aggregation layer), since you can no longer rely on a simple database join across service boundaries.
57. What is a Saga pattern, and what problem does it solve in a database-per-service architecture? A Saga coordinates a sequence of local transactions across multiple services, each with a corresponding compensating action to undo it if a later step fails — solving the problem of maintaining data consistency across a business process that spans multiple independently-owned databases, where a traditional single distributed transaction (like two-phase commit) either isn’t available or isn’t practical at the required scale.
58. What’s the significance of choosing an appropriate consistency boundary — deciding what genuinely needs to be transactionally consistent versus what can be eventually consistent — in a large system’s design? Not every relationship in a system needs the same consistency guarantee — a payment amount and its corresponding ledger entry likely need strong, immediate consistency, while a recommendation engine’s view of a user’s recent purchases can tolerate some lag. An architect who correctly identifies which specific boundaries genuinely require strong consistency (and confines that expensive guarantee to just those) versus which can be relaxed builds a system that’s both correct where it matters and scalable everywhere else, rather than either over-engineering unnecessary strong consistency everywhere or under-protecting something that genuinely needed it.
59. What is a data quality framework, and what are typical dimensions it measures? A structured approach to defining, measuring, and improving data quality, typically assessing dimensions like accuracy, completeness, consistency, timeliness, uniqueness, and validity — an architect designs systems with these dimensions in mind (validation rules, deduplication logic, freshness monitoring) rather than treating data quality as something to fix reactively after problems are discovered downstream.
60. What’s the difference between a batch processing architecture and a streaming architecture, and when would you choose each? Batch processing handles data in discrete, scheduled chunks (nightly ETL jobs), simpler to reason about and generally more resource-efficient for genuinely non-time-sensitive processing. Streaming processes data continuously as it arrives, appropriate when genuinely low-latency insight or action is required (fraud detection, real-time dashboards) — the choice should be driven by actual business latency requirements, not by streaming being the more modern-sounding option, since batch remains the simpler and often more appropriate choice when true real-time processing isn’t actually needed.
61. What is a Lambda architecture, and what problem does the newer Kappa architecture try to simplify? Lambda architecture runs both a batch layer (accurate, comprehensive, but higher latency) and a speed/streaming layer (fast, but potentially less complete) in parallel, merging their results — providing both accuracy and low latency, but requiring maintaining two separate processing codepaths for the same logical data. Kappa architecture simplifies this by processing everything through a single streaming pipeline (treating batch as just a replay of the stream), avoiding the dual-codepath maintenance burden, at the cost of requiring the streaming system to be robust enough to serve as the sole source of truth.
62. What’s the architectural significance of designing idempotent, replayable data pipeline steps for disaster recovery, beyond normal operational reliability? If a pipeline’s steps are genuinely idempotent and the source events/data can be replayed from a durable log, recovering from a significant failure (a corrupted downstream table, a lost data mart) can often be achieved by simply re-running the pipeline from a known-good point, rather than requiring a complex, bespoke recovery procedure — this is a design property worth deliberately architecting for upfront, since retrofitting replayability into a pipeline that wasn’t designed with it is often much harder than building it in from the start.
63. What is a polyglot persistence strategy, and what’s the risk of over-applying it? Using different database technologies for different parts of a system, each chosen to match that specific part’s actual data and access pattern needs (a document database for a catalog, a graph database for a recommendation engine, a relational database for transactional order processing). The risk of over-applying it is real operational complexity — every additional distinct database technology adds its own operational burden, expertise requirement, and integration complexity, so the decision to introduce a new technology should be justified by a genuine, significant fit advantage, not novelty or a marginal improvement.
64. What’s the trade-off between building a custom data integration solution versus adopting a commercial or open-source ETL/data integration platform? A custom solution offers maximum control and can be precisely tailored to specific needs, but requires ongoing internal engineering investment to build and maintain. A platform solution offers faster initial implementation and often broader, battle-tested connector support, at the cost of licensing cost (for commercial options) and being constrained by that platform’s own capabilities and limitations — the right choice depends on the organization’s specific needs’ uniqueness, team capacity, and how central data integration is to the core business versus being a supporting function.
65. What is a canonical data model, and what problem does it solve in a system integrating many different source systems? A single, standardized representation of a business entity (like “Customer”) that all integrations translate to and from, rather than every system integration needing to understand every other system’s specific format directly. It solves the “N-squared” integration complexity problem — without a canonical model, each new system integrated requires custom translation logic to every other system it needs to exchange data with; with one, each system only needs to translate to and from the single canonical format.
66. What’s the significance of designing explicit data retention and archival policies as part of an architecture, rather than an afterthought? Storage costs and query performance both degrade as a system accumulates indefinitely growing historical data without a deliberate retention strategy — designing archival (moving old, rarely-accessed data to cheaper storage) and retention (defining how long data must or should be kept, often driven by compliance requirements) into the architecture from the start avoids both a slow performance degradation and a much harder retrofit later once a huge volume of undifferentiated historical data has already accumulated.
67. What is a data catalog, and how does it differ from a data dictionary in scope? A data dictionary describes the technical structure of specific database objects (tables, columns, types). A data catalog is broader — an organization-wide, often searchable inventory of all available datasets across many systems, including business context, ownership, quality metrics, and lineage — helping people across a large organization discover what data exists and understand whether it’s trustworthy for their specific need, beyond just knowing a table’s column names.
68. What’s the architectural trade-off of choosing eventual consistency for a shopping cart or inventory system specifically, versus a payment system? An inventory count being briefly stale (showing one more item available than truly exists) is usually a tolerable, recoverable business risk — the worst case is briefly overselling, correctable after the fact. A payment amount being inconsistent, even briefly, is generally not an acceptable risk, since it directly represents money and trust — this is a concrete example of why consistency requirements should be evaluated per-entity based on actual business risk tolerance, not applied uniformly across an entire system regardless of what each piece of data actually represents.
69. What is a reference data management strategy, and why does poorly managed reference data cause disproportionate downstream problems? Reference data (country codes, currency codes, standard classification lists) is used broadly across many systems and processes — when it’s inconsistent or poorly governed (different systems using slightly different code lists, or a code changing meaning without coordination), the resulting problems ripple disproportionately widely compared to how small and seemingly unimportant the reference data itself might seem, since so much else depends on it being consistent everywhere it’s used.
70. What’s a reasonable framework for evaluating whether a proposed new database technology is actually justified for a specific use case, versus using an existing, already-supported platform? I’d weigh the specific, concrete capability gap the new technology closes (not a vague “it’s better for this kind of data” impression) against the genuine cost of introducing it — operational burden, team learning curve, integration effort — and specifically check whether the existing platform could reasonably be stretched to cover the need with acceptable trade-offs before concluding a new technology is genuinely justified, since technology proliferation itself has a real, compounding organizational cost that should be a deliberate decision, not a default.
Advanced Level
71. Design the data architecture for a global e-commerce platform requiring low-latency reads in multiple regions, strong consistency for inventory and payments, and analytical reporting across the whole business. Walk through your reasoning. I’d separate concerns by consistency requirement rather than forcing one architecture to serve all of them: a strongly consistent, primary-region-anchored transactional store for payments and inventory (accepting some added latency for genuinely consistency-critical writes), regional read replicas or a CQRS-style read model for low-latency product catalog and browsing reads that can tolerate eventual consistency, and a separate analytical data warehouse/lakehouse fed via CDC from the transactional systems for cross-business reporting, decoupled entirely from the operational systems’ performance and consistency constraints. The core principle: don’t let the analytical workload’s needs distort the transactional system’s design, and don’t force uniform strong consistency onto reads that don’t actually require it.
72. Explain how you’d approach a schema migration for a large, high-traffic production table with zero acceptable downtime, where the change is a genuinely breaking structural change (splitting one table into two). I’d use an expand-contract pattern: first expand the schema to support both old and new structures simultaneously (adding the new tables/columns alongside the old, without removing anything yet), deploy application code that writes to both structures while still reading from the old one, backfill historical data into the new structure, then switch reads over to the new structure once backfill is validated as complete and correct, and only after a safe observation period, contract by removing the old structure — each step independently deployable and reversible, avoiding a single risky, all-at-once cutover.
73. Explain the architectural trade-offs of choosing two-phase commit (2PC) for distributed transactions versus a Saga pattern, beyond just “2PC is synchronous and Sagas are asynchronous.” 2PC provides genuine ACID guarantees across participating systems but requires all participants to be available and responsive during the commit protocol, creating a blocking dependency and a coordinator single point of failure — it doesn’t scale well across many participants or across systems with independent availability requirements. Saga achieves eventual consistency through compensating transactions instead, avoiding that blocking dependency and scaling better across independently-owned services, but requires the application to explicitly design and correctly implement compensating logic for every step, and accepts a window where the system is in a genuinely intermediate, not-yet-fully-consistent state — a real trade-off between strong correctness guarantees and system-wide availability/scalability, not just a stylistic difference.
74. Design a multi-region disaster recovery strategy for a data platform where different datasets have genuinely different RPO/RTO requirements. Explain how the architecture accommodates that variance rather than applying one blanket policy. I’d tier datasets explicitly by business criticality and define distinct RPO/RTO targets per tier rather than a single platform-wide DR policy — synchronous or near-synchronous cross-region replication for the highest tier (genuinely mission-critical, low RPO tolerance), asynchronous replication with a defined acceptable lag for a middle tier, and backup-based recovery (accepting a longer RTO/RPO) for lower-criticality datasets where the cost of stronger protection isn’t justified by the actual business risk. The architecture explicitly supports this tiering — not every dataset flows through the same replication mechanism — rather than either over-protecting low-value data or under-protecting genuinely critical data by applying one uniform policy across everything.
75. Explain how you’d design a data platform to support both strict data residency requirements (some data must never leave a specific geography) and a unified global analytics capability. I’d keep genuinely residency-constrained data within its required geographic boundary at the storage layer, but design the analytics layer to work with appropriately aggregated, anonymized, or otherwise compliant derived datasets that can legally cross the boundary for global reporting purposes — rather than assuming global analytics requires raw data consolidation in one place. This often means accepting that truly row-level global analytics on the constrained data simply isn’t achievable within the compliance boundary, and being explicit with stakeholders about that constraint rather than architecting around it in a way that quietly violates the actual requirement.
76. Explain the concept of “data gravity,” and how it should influence where you choose to run compute for a large-scale analytical workload. Data gravity refers to the tendency for applications, services, and further data to accumulate around large datasets, because moving compute to data is generally cheaper and faster than moving large volumes of data to compute — this should influence architecture decisions toward running analytical compute in the same environment/region where the bulk of the relevant data already lives, rather than centralizing compute somewhere convenient and repeatedly pulling large data volumes across a network to reach it, which becomes an increasingly expensive and slow pattern as data volume grows.
77. Design a data architecture supporting a machine learning platform that needs both historical training data and low-latency feature serving for real-time inference. Explain the key architectural components. I’d separate the training path (a feature store’s offline layer, typically backed by a data warehouse/lakehouse, optimized for large-scale historical batch access) from the serving path (the feature store’s online layer, typically backed by a low-latency key-value store, optimized for fast point lookups at inference time) — with a consistent feature computation pipeline feeding both, specifically to avoid training-serving skew, where features computed differently for training versus real-time serving cause a model to behave unexpectedly in production compared to how it was validated during training.
78. Explain how you’d approach capacity and cost forecasting for a data platform expected to grow 10x in data volume over three years, without over-provisioning today for growth that might not materialize as predicted. I’d design for near-term actual needs with an explicit, documented scaling path already identified for the growth scenario, rather than provisioning for the full 10x target from day one — favoring architectural choices that scale incrementally (horizontally scalable storage/compute that can grow node by node) over choices that require a large, discrete re-architecture at a specific threshold, and building in regular checkpoints to revisit the actual growth trajectory against the original forecast, adjusting the scaling plan based on real observed data rather than the original three-year-old projection.
79. Explain a scenario where a data mesh architecture would genuinely be the wrong choice for an organization, despite its current popularity. A smaller organization, or one without genuinely independent, mature domain teams capable of owning their own data products end-to-end (including the data engineering discipline that requires), would likely find data mesh’s decentralized ownership model adds coordination and governance overhead without a corresponding benefit — data mesh solves a specific organizational scaling problem (a central team becoming a genuine bottleneck at large scale), and applying it where that bottleneck doesn’t yet exist just adds complexity without solving a real problem the organization actually has.
80. Explain how you’d design database architecture to support a genuinely multi-tenant SaaS platform, comparing the trade-offs of separate databases per tenant, a shared database with tenant ID columns, and a hybrid approach. Separate databases per tenant give the strongest isolation (security, performance, easier per-tenant customization and compliance) but the highest operational overhead at scale (managing potentially thousands of database instances). A shared database with tenant ID columns is operationally simpler and more cost-efficient at scale, but requires very careful, consistently-enforced tenant isolation (Row-Level Security or equivalent) to avoid a catastrophic cross-tenant data leak, and complicates giving any single tenant dedicated performance guarantees. A hybrid approach — pooling smaller tenants together while isolating the largest, highest-value, or most compliance-sensitive tenants into dedicated databases — is often the pragmatic middle ground, and the right threshold for that split should be based on actual tenant size distribution and specific compliance needs, not an arbitrary line.
81. Explain the architectural implications of choosing a graph database for a specific use case (like a social network or fraud detection system) versus modeling the same relationships in a relational database. A graph database is architecturally optimized for traversing deep, variable-depth relationships efficiently (finding all connections within three hops, for instance) — a query pattern that degrades badly in a relational database as traversal depth grows, since each additional hop typically means another join, and the number of possible joins can explode. For a use case genuinely dominated by this kind of relationship-traversal query pattern, a graph database’s fundamentally different storage and query model provides a real, not just marginal, architectural advantage — but for a use case with only occasional or shallow relationship queries, the operational cost of introducing an entirely separate database technology may not be justified by that advantage.
82. Explain how you’d design a system to detect and handle “split-brain” scenarios in a distributed database architecture. Split-brain occurs when a network partition causes a cluster to divide into two or more groups, each believing itself to be the legitimate primary, potentially leading to conflicting, divergent writes. I’d design around a quorum-based consensus mechanism (requiring a majority of nodes to agree before accepting a write as legitimate primary), ensuring an odd number of voting members specifically to avoid an evenly-split tie scenario, and combine that with fencing mechanisms (actively preventing a former primary that’s lost quorum from continuing to accept writes) rather than relying purely on detection-and-cleanup after the fact, since preventing the split-brain write conflict from happening at all is architecturally preferable to reconciling divergent data after it’s already occurred.
83. Explain how you’d design for backward and forward compatibility in an event-driven architecture where many independent consumers depend on a shared event schema. I’d establish explicit schema evolution rules upfront — additive-only changes are safe (new optional fields), while removing or renaming a field, or changing its type, is a breaking change requiring a new schema version and a coordinated consumer migration — enforced via a schema registry that validates producers against these compatibility rules before a change can even be published, rather than relying on informal agreement or hoping consumers notice a breaking change before it causes a production failure downstream.
84. Explain the trade-offs of building a real-time streaming data platform (Kafka or similar) as the backbone of an organization’s data architecture, versus a more traditional batch-oriented approach, at genuine organizational scale. A streaming backbone provides low-latency data availability across the organization and a natural decoupling point between producers and many independent consumers, but requires genuine operational maturity to run reliably at scale (partition management, consumer group coordination, handling backpressure) and a cultural shift toward thinking about data as continuous events rather than periodic snapshots. A batch-oriented approach is operationally simpler and well-understood, appropriate when the organization’s actual latency requirements don’t genuinely need real-time, and premature adoption of a streaming backbone specifically to seem “modern,” without a genuine low-latency requirement driving it, often just adds operational complexity without proportional benefit.
85. Explain how you’d approach designing a data architecture that needs to support both GDPR’s “right to be forgotten” and a data warehouse’s typical practice of retaining full historical data for trend analysis. This is a genuine architectural tension requiring an explicit design decision, not an assumption it’ll resolve itself — I’d design for pseudonymization or tokenization of personally identifiable fields at the point of ingestion into the analytical layer, so historical trend data can be retained in aggregate/de-identified form even after a specific individual’s identifiable data is deleted from the systems where “right to be forgotten” actually applies, keeping a clear, documented mapping of exactly which layer holds identifiable data (subject to deletion) versus which holds de-identified historical aggregates (which can be retained) rather than a single undifferentiated warehouse where this distinction isn’t architecturally enforced.
86. Explain how you’d design a data platform to support A/B testing and experimentation at scale, including the data architecture considerations beyond just “log the experiment assignment.” Beyond logging assignment, I’d design for consistent, deterministic assignment (the same user always lands in the same experiment variant across sessions and services, typically via a consistent hashing scheme rather than random assignment per event), a data model that clearly separates experiment metadata (which experiments exist, their variants, start/end dates) from experiment exposure/assignment events and outcome/metric events, and a query layer capable of efficiently joining assignment against outcome data at genuine analytical scale — since experimentation platforms commonly become a very high-volume, high-query-load part of the data platform once an organization runs many concurrent experiments.
87. Explain how you’d evaluate whether an organization’s data architecture has accumulated “technical debt” specifically at the architectural level, distinct from code-level technical debt, and how you’d prioritize addressing it. Architectural technical debt shows up as things like: a schema that’s been patched around repeatedly rather than genuinely redesigned as requirements evolved, undocumented or poorly understood data lineage making changes risky, inconsistent data models for the same logical entity across different systems, or a single database technology being stretched well beyond the access patterns it was actually designed for. I’d prioritize addressing it based on which specific debt is actively constraining current business needs (blocking a genuinely important new capability, or causing recurring, costly incidents) rather than attempting a comprehensive architectural cleanup that isn’t tied to concrete, currently-felt business pain.
88. Explain how you’d design a data platform’s security architecture to support fine-grained, attribute-based access control across a large, heterogeneous set of data systems, rather than relying purely on each system’s own native access control. I’d design a centralized policy layer (an attribute-based access control engine, evaluated at query or API-gateway time) that can express access rules based on data sensitivity classification, user attributes, and context, applied consistently across otherwise heterogeneous underlying systems — rather than relying purely on each individual database’s own, potentially inconsistent native permission model, which tends to drift out of alignment across many different systems each with their own access control philosophy and configuration surface.
89. Explain how you’d architect a system to support genuine zero-downtime database technology migration (e.g., moving from one database platform to an entirely different one), not just a version upgrade. I’d use a dual-write pattern during a transition period (writing to both the old and new systems simultaneously, with careful handling of any inconsistency that might arise), backed by a comprehensive reconciliation process comparing data between the two systems to build confidence before cutover, then a phased read migration (moving read traffic to the new system gradually, with the ability to fall back quickly if problems emerge) before finally retiring the old system — treating this as a genuinely gradual, validated, reversible process rather than a single high-risk cutover event, given how much can differ (consistency model, query capabilities, performance characteristics) between fundamentally different database technologies.
90. Explain the architectural considerations for designing a data platform that needs to support both real-time operational decisions and long-term strategic analytics from the same underlying business events, without building and maintaining two entirely separate, disconnected systems. I’d design a single, durable event log (via a streaming platform) as the canonical source of truth for business events, with the operational, low-latency decision-making systems consuming directly from that stream for immediate needs, and the same stream also feeding, via a separate consumer, the long-term analytical warehouse/lakehouse — meaning both operational and analytical needs derive from the same single source of truth rather than maintaining two independently-built, potentially inconsistent pipelines that each reimplement their own version of “what happened,” which tends to drift apart in subtle ways over time if built and maintained separately.
91. Explain how you’d design a database architecture’s approach to schema versioning and API contract stability when the underlying data model needs to evolve frequently to support a fast-moving product. I’d separate the physical database schema from the external-facing data contract (an API or an event schema) via an explicit mapping/translation layer, allowing the internal schema to evolve more freely and frequently to support product needs, while the external contract changes more deliberately and on its own versioned, communicated cadence — avoiding the situation where every internal schema tweak forces a breaking change for every external consumer, which would otherwise create strong pressure to avoid necessary internal evolution simply to protect external stability.
92. Explain how you’d approach designing disaster recovery testing specifically for a complex, multi-system data architecture (not just a single database), where a real disaster would require multiple systems to recover in a coordinated sequence. I’d map the actual dependency graph across the full architecture — which systems must recover before others can meaningfully function (a message queue needing to be available before downstream consumers can process anything, for instance) — and design DR tests that validate the coordinated, correctly-sequenced recovery of the whole dependency chain, not just independently testing each system’s own individual recovery in isolation, since a real disaster recovery failure often happens specifically at the coordination points between systems, which isolated per-system testing wouldn’t catch.
93. Explain the trade-offs of adopting a “database as code” / infrastructure-as-code approach to schema and configuration management for a large data platform, versus more traditional manual DBA-managed change processes. Infrastructure-as-code brings version control, repeatability, and peer review discipline to database changes the same way it does for application code, reducing configuration drift and making changes auditable and reversible — genuinely valuable at scale across many databases. The trade-off is a real cultural and tooling shift for teams accustomed to manual, ad hoc database administration, and some database changes (particularly large-scale data migrations, not just schema changes) don’t always fit cleanly into the same declarative, idempotent model that works well for schema-only infrastructure-as-code, requiring careful tooling choices that handle both cleanly.
94. Explain how you’d design a data architecture to support a genuine “single source of truth” requirement for a critical business metric that’s currently calculated differently (and inconsistently) across several different reports and teams. I’d establish a single, authoritative, well-governed calculation (owned by a clearly accountable team, with an explicit, documented definition) published as a certified dataset or metric layer that all downstream reports are required to consume rather than independently recalculating, and specifically deprecate the inconsistent parallel calculations rather than letting them continue to coexist alongside the new authoritative one — since a “single source of truth” that still has competing, uncontrolled alternate calculations running alongside it doesn’t actually solve the underlying inconsistency problem, it just adds one more version to the pile.
95. Explain how you’d approach making a build-versus-buy decision for a significant piece of data infrastructure (like a custom data catalog versus a commercial one), including the factors beyond pure feature comparison. Beyond feature comparison, I’d weigh the total cost of ownership honestly (including the ongoing engineering time a “free” open-source or custom-built solution actually consumes, which is very rarely genuinely free once maintenance is accounted for), how central this specific capability is to the organization’s actual competitive differentiation (build where it’s genuinely core and differentiating, buy where it’s a supporting capability many organizations need in roughly the same way), and the organization’s realistic capacity to maintain a custom solution’s institutional knowledge over time as team composition inevitably changes, rather than only comparing feature checklists between the build and buy options.
96. Explain how you’d design a data architecture’s approach to handling late-arriving data in a streaming analytical pipeline, where events can genuinely arrive out of order or significantly delayed. I’d design the pipeline around event time (when something actually happened) rather than purely processing time (when the system received it), using watermarking to define how long the system waits for late data before considering a time window “closed,” with an explicit, deliberate policy for what happens to data arriving after that watermark (dropped, or triggering a correction/reprocessing of already-emitted results) — since naively processing purely by arrival order in a genuinely distributed, real-world data collection scenario will produce measurably incorrect results without this deliberate handling of the event-time-versus-processing-time distinction.
97. Explain how you’d design a data architecture to support genuine multi-cloud portability, and honestly evaluate whether that goal is actually worth its cost for a typical organization. Genuine multi-cloud portability requires deliberately avoiding deep dependency on any single cloud provider’s proprietary managed services in favor of more portable, open-standard technologies — a real, ongoing constraint that trades away some of the operational convenience and deep integration those managed services offer. I’d be honest with stakeholders that for most organizations, this trade-off isn’t actually worth its cost unless there’s a genuine, specific business driver (a real regulatory requirement, or genuine negotiating leverage need with a specific cloud vendor) — pursuing multi-cloud portability as a hedge against a hypothetical future need, without a concrete current driver, usually costs more in ongoing complexity than the optionality it provides is actually worth.
98. Explain how you’d design observability into a complex, multi-system data architecture from the start, rather than retrofitting monitoring after the system is already built. I’d design for consistent, structured logging and tracing across every component from day one (using correlation IDs that let a single logical data flow be traced across multiple systems), define clear data quality and freshness SLAs per critical dataset with automated monitoring against them, and specifically design pipeline steps to emit their own operational metadata (row counts processed, processing duration, error counts) as a first-class part of the pipeline’s own output, rather than observability being an afterthought bolted onto a system that wasn’t designed with these traceability hooks built in from the start.
99. Explain how you’d balance an architecture’s need for genuine flexibility to support future, not-yet-known requirements against the real risk of over-engineering for hypothetical needs that never materialize. I’d favor architectural decisions that are genuinely cheap to reverse or extend later (avoiding decisions that lock you into a specific, hard-to-change path without a clear, current justification) over trying to predict and pre-build for every conceivable future requirement — the practical discipline is distinguishing between “this specific extensibility point is cheap to include now and meaningfully reduces future risk” versus “this speculative flexibility adds real complexity today for a benefit that may never actually be needed,” and consistently leaning toward the simpler, more directly-justified design when that distinction is genuinely unclear.
100. Walk through a genuinely complex database architecture decision you’ve made or would expect to make, demonstrating your end-to-end reasoning process. Intentionally open-ended, since interviewers are evaluating judgment and trade-off reasoning, not a memorized architecture pattern. A strong structure to demonstrate regardless of the specific decision: (1) establish the actual requirements and constraints clearly — scale, consistency needs, team capability, budget, compliance — rather than jumping to a favorite or trendy solution, (2) identify the two or three genuinely viable options given those constraints, explicitly naming what each one trades away, not just what it provides, (3) make a specific recommendation with clear reasoning tied directly back to the stated requirements, (4) acknowledge the recommendation’s real limitations and the conditions under which it would need to be revisited, and (5) describe how you’d validate the decision was right after implementation, rather than treating the initial decision as permanently correct without ever checking. Interviewers at this level consistently favor candidates who can articulate what they’re giving up with a given choice, as clearly as what they’re gaining — that honesty about trade-offs, more than knowing every architecture pattern by name, is what the role is actually testing.
A Closing Thought
The idea running through nearly every advanced answer here: there is no universally “best” database architecture — only architectures that are well-matched or poorly matched to a specific set of constraints, and the real skill is naming those constraints honestly and explaining exactly what’s being traded away, not just what’s being gained.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
