Amazon Solutions Architect (Staff Level) Interview Preparation Guide

Direct answer

I name the specific harm and who it falls on, translate it into a risk the business recognises, bring a workable alternative instead of only a "no", and involve whoever owns the risk (security, privacy or legal). If I have not faced it, this is the exact sequence I would follow. I do not trade it off with revenue silently; the decision gets made visibly by someone with authority.

Method

1. State the harm concretely. "Showing a user's location to people they have blocked" is a harm. "Privacy concerns" is not.
2. Weigh severity, not only share. A small vulnerable segment (people at risk of stalking or harassment, for instance) can matter more than its percentage suggests because the harm is serious and hard to undo.
3. Frame the risk: severity, likelihood, reversibility (can it be undone? a shared location or a sent notification cannot be taken back, while a button colour can), and the cost to trust (users who learn their contact details exposed someone may leave and tell others). For legal specifics, bring in counsel instead of guessing.
4. Quantify the exposure and the alternative. Sizing the people affected makes it concrete.
5. Offer options: ship with a safer default, ship opt-in, delay a week for a fix, or ship narrower.
6. Escalate with a record, not by going around people.

Worked example (illustrative): contact syncing by default

A team plans to turn on contact syncing by default (default-on: switched on for everyone until they turn it off, versus opt-in: off until the user chooses to turn it on) because it lifts sign-ups. With 200,000 monthly active users (MAU), if 2% belong to a group where being found by contacts is dangerous, that is 4,000 people (2% of 200,000). The analytics lead estimates the default-on version brings about 5,500 sign-ups a month against about 5,000 for opt-in, roughly 500 extra a month. That is the revenue argument, and it has to be weighed against 4,000 existing people exposed on day one in a way that cannot be undone.

The pushback, in words I might use in the meeting: "If our estimate holds that about 2% of our 200,000 monthly users, so roughly 4,000 people, are in situations where being found through a phone contact list could put them in danger (for example someone hiding from an abusive ex-partner), then default-on exposes all of them on day one, and a notification to a contact cannot be unsent. Opt-in costs us some of the roughly 500 extra monthly sign-ups, and I would like to measure how much for a week before we decide." The option I bring: opt-in with a clear prompt shown at the moment of value, plus a one-week measured test of how much of the lift survives. The outcome is a trade the team makes knowingly, with a rollback condition and an owner. If leadership still chooses the risky default, I would record my objection and ask that the privacy review (the privacy team's formal check) sign off in writing.

Pitfalls

• Invoking safety as a trump card without evidence. It loses credibility.
• Being the lone "no". Bring an alternative and an ally.
• Skipping the record: if it goes wrong, the paper trail protects users and you.
• Not-yet-faced answer: be explicit that you are describing a plan, and still give specifics.

Designing a data mesh for 100+ domains has to solve four things at once: who owns what, how a domain publishes something others can safely depend on, how anyone finds it, and what stops the whole thing collapsing into chaos as more domains join.

Architecture

flowchart TB
    subgraph Domain A
    A1[Domain data + logic] --> A2[Data product: contract + schema]
    end
    subgraph Domain B
    B1[Domain data + logic] --> B2[Data product: contract + schema]
    end
    subgraph Domain N
    N1[Domain data + logic] --> N2[Data product: contract + schema]
    end
    A2 --> C[Federated catalog / discovery layer]
    B2 --> C
    N2 --> C
    C --> D[Consumers: BI, ML, other domains]
    E[Self-serve platform: storage, compute, observability, access control] -.-> A2
    E -.-> B2
    E -.-> N2
    F[Federated governance: schema standards, SLAs as code, quality gates] -.-> C

Domain ownership: each domain team owns the data it produces end to end, including its correctness and its documented contract, not just the raw pipeline. Ownership without a real contract just relocates the old central-team-as-bottleneck problem to a hundred smaller bottlenecks.
Data-product contracts: every domain publishes its data through a defined contract: a stable schema, an SLA (service-level agreement covering freshness and uptime), semantic documentation of what each field means, and a versioning policy for changes. This is what lets a consumer depend on domain B's data without needing to understand domain B's internal pipeline.
Discovery/catalog layer: a federated catalog indexes every domain's published data products with consistent metadata (owner, schema, SLA, quality score), so a consumer in domain N can find and evaluate a data product from domain A without asking around. Without this, a mesh with 100+ domains is strictly worse for discoverability than a single centralized platform.
Governance guardrails: federated computational governance means the STANDARDS (schema conventions, required SLA fields, quality thresholds) are centrally defined and automatically enforced (as code, via CI checks on every published data product), while the actual DATA remains domain-owned. This is the mechanism that prevents a hundred domains from each inventing incompatible conventions.
Platform services: a shared self-serve platform (storage, compute, observability, access control, the catalog itself) that every domain builds on, so domains aren't each reinventing infrastructure; this is what makes domain ownership operationally feasible instead of just organizationally declared.

Preventing sprawl

Every published data product must pass automated contract checks (schema validity, required metadata fields, an SLA declaration) before it's discoverable in the catalog; a domain that publishes something without meeting the baseline contract simply doesn't show up as a trustworthy option for consumers. Periodically audit for genuinely duplicate data products across domains (the same underlying concept published inconsistently by two teams) and resolve ownership, this is a governance process, not a purely technical one, but the catalog's metadata is what surfaces the duplication for a human to act on.

Trade-offs and pitfalls

The most common failure is standing up domain ownership and a catalog without genuine, automatically-enforced governance standards; the result looks like a mesh but behaves like a hundred independent, inconsistent data silos, which is worse than the centralized platform it replaced. The second is under-investing in the shared self-serve platform, which pushes every domain to build its own infrastructure from scratch, defeating the efficiency argument for a mesh in the first place.

Direct answer

A first-time FinOps practice usually has four kinds of people: a central FinOps lead who owns governance, policy, and cross-org reporting; embedded FinOps engineers who sit inside product or platform teams and do the hands-on technical work (tagging, rightsizing proposals, reservation planning); a finance analyst who owns the budget model, forecast accuracy, and validating that claimed savings actually show up on the bill; and cost owners, typically an engineering or product manager, who are accountable for a specific service's spend. During a high-impact cost-reduction initiative you'd expect the central lead to set the target and reporting cadence, the embedded engineer to be the person actually implementing whatever you scope, the finance analyst to sign off on your savings estimate before it counts toward the goal, and the cost owner to approve trade-offs that touch their service's reliability or roadmap.

Structured elaboration

The FinOps Foundation's operating framework describes this as an iterative cycle across three phases, Inform (visibility into spend), Optimize (acting on that visibility), and Operate (continuously improving the process), and the four roles map onto that cycle differently:

Two things a first-time practice tends to get wrong are worth naming here because they show up in interview follow-ups: standing up governance (the central lead) without embedded technical partners produces policy nobody implements, and the reverse, only embedded engineers with no central lead, produces disconnected point optimizations with no organization-wide view or shared standards.

Worked example

Say leadership sets a target to cut compute spend by roughly 15 percent this quarter, and this is the first real cost-reduction push the company has run. In week one, the central FinOps lead sets the target, the reporting cadence, and identifies which two or three services carry the largest share of spend. In weeks two through four, the embedded FinOps engineer on each of those services works with the local team to find and validate specific actions (a rightsizing opportunity, an idle resource, a candidate reserved-capacity purchase). Each proposed action goes to the finance analyst, who checks the estimate against the actual rate card and confirms it isn't double-counting a discount that's already applied elsewhere. Anything that changes the service's operating characteristics (a smaller instance size, a capacity commitment that reduces burst headroom) goes to the cost owner for sign-off before it ships. By the end of the quarter, the central lead rolls the validated, signed-off savings into a single number for leadership, with each line item traceable back to an owner and an action.

Trade-offs and pitfalls

• Skipping the finance validation step is the most common mistake. A rightsizing change that looks like a 20 percent saving on paper might land differently once you account for a commitment discount that was already covering part of that usage; finance is what keeps the reported number honest.
• Not identifying a real cost owner for a service means no one is empowered to approve the trade-off, and the initiative stalls waiting for a decision nobody owns.
• Over-centralizing decisions slows everything down; the central lead should set direction and adjudicate conflicts, not approve every individual optimization, or the embedded engineers become bottlenecked waiting on sign-off for routine work.
• Treating this as a one-quarter project rather than an operating model means the same tagging gaps and untracked spend reappear next quarter; the roles above are meant to persist through the Operate phase, not disband once the target is hit.

Direct answer
A service mesh gives you mutual TLS between services, consistent retries and traffic control, and rich east-west observability without every service having to implement those concerns in its own code; the trade-off is meaningful operational overhead (running and tuning a sidecar proxy alongside every service instance) and added per-hop latency, which is usually worth it once an organization has enough services that these concerns can no longer be handled consistently by convention alone.

Structured elaboration
Benefits: mutual TLS between every service-to-service call, enforced by the mesh rather than by each service's own code, means encryption and identity verification for internal traffic happen consistently without every team implementing it themselves; centralized retry and traffic-shifting policy (canary releases, circuit breaking, timeout budgets) can be configured and changed at the mesh layer instead of requiring a code change and redeploy in every service; and rich observability (every service-to-service call automatically produces consistent metrics and traces) removes the need for every service to instrument this itself. Drawbacks: running a sidecar proxy next to every service instance adds real operational surface (another process to monitor, upgrade, and debug per instance), adds a small amount of latency to every hop (typically single-digit milliseconds, but that compounds across a request that touches several services), and introduces a genuinely new skill the team needs (mesh configuration and debugging, which is a different discipline from application-level debugging).

Worked example
A commonly cited rule of thumb: below roughly 10 services, the coordination and consistency problems a mesh solves usually aren't severe enough to justify the operational cost, since a small number of services can maintain consistent mTLS, retry, and observability practices through code review and shared libraries. Past that scale, especially with fine-grained observability needs (understanding exactly which service-to-service hop is causing a latency regression across dozens of services) and multiple teams who can no longer coordinate consistent practices by convention, the mesh's centralized control starts to pay for itself.

Trade-offs and pitfalls
The most common mistake is adopting a mesh because it's a well-known pattern rather than because a specific, currently-felt pain (inconsistent mTLS enforcement, or genuinely hard-to-debug cross-service latency issues) justifies it; introducing the mesh's operational overhead without a concrete problem it's solving tends to slow the team down for a benefit nobody was missing yet. The second common mistake is underestimating the added latency in a request path that already chains many synchronous service-to-service calls, where a few extra milliseconds per hop from the mesh sidecar can meaningfully add up; measuring the actual latency impact on your specific critical paths before and after adoption is worth doing rather than assuming it's negligible.

Situation: A senior stakeholder wanted to launch in two weeks, while Operations warned that the support team was not ready.

Task: I needed to reset expectations without slowing the business unnecessarily.

Action: I made the trade-off visible in a simple readiness review. I listed the risks, the likely customer impact, and the mitigation options. I also translated the concern into business language, not just process language. For example, instead of saying Operations was not ready, I showed that we would have limited training coverage and slower incident response if we launched immediately. Then I proposed two paths: launch with a phased rollout and extra monitoring, or delay one week to complete training and testing.

Result: Both sides could support the phased rollout because the risk was named clearly and the plan had guardrails. The stakeholder got speed, Operations got protection, and we agreed on a decision that balanced business urgency with operational readiness.

That experience reinforced that good trade-off decisions are rarely about winning an argument. They are about making the risk and impact clear enough for everyone to support the choice.

Direct answer

For the core text-editing path, choose between operational transformation (OT) and CRDTs (Conflict-free Replicated Data Types, data structures designed so two divergent copies can always be merged automatically without a central coordinator), both can correctly merge concurrent, even offline, edits, but they make different trade-offs between server dependence, storage, and implementation risk. Last-writer-wins (LWW), where the most recent write simply overwrites an earlier one, is not appropriate for concurrent body-text edits since it would silently discard one person's work, but it is fine for single-writer metadata like a document's title.

The three approaches

• Operational transformation (OT). Each incoming remote edit is transformed against whatever concurrent edits happened locally, so it can be applied correctly on top of a state that has since diverged. For example, if you inserted a character at position 5 while someone else concurrently deleted a character at position 2, OT rewrites your insert's target position to account for the shift caused by their delete, keeping both edits' intent intact. This is how early Google Docs-style collaborative editors worked.
• CRDTs for text (commonly a sequence CRDT such as an RGA, replicated growable array). Every character gets a stable, unique, immutable position identifier when it's inserted, so two replicas can merge their edit histories in any order, or after being offline for a while, and always converge to the same document without needing a central server to referee the merge.
• Last-writer-wins. For document editing specifically, LWW applied to the body text would let one person's entire concurrent edit simply overwrite the other's, an unacceptable loss of work; it only makes sense for metadata fields with a single natural owner, like "last editor" or the document title.

Comparison

Worked example

Two people edit the same paragraph while briefly offline from each other, one inserts a word near the start, the other deletes a sentence later in the same paragraph. A text CRDT assigns each character a stable position identifier at insert time, so when the two edit histories merge, both changes apply correctly without either needing to know about the other's edit in advance, insertions and deletions from both sides land in the right place relative to each other. The same scenario under OT routes both operations through a central server (or a peer acting as one), which transforms whichever arrives second against the one that arrived first. That part works even though both authors were offline at the same time: on reconnect each client sends its operations tagged with the server revision it last saw, and the server transforms them forward, which is exactly how offline mode in a server-backed editor is built. What OT does not give you is the same guarantee with no server in the picture at all, because peer-to-peer OT requires the transform function to satisfy a much stronger pairwise property (any two operations must transform to the same result no matter which order the peers apply them in), and that property is hard to prove and easy to get subtly wrong. The CRDT's advantage is therefore narrower than "it handles offline": both handle offline, and the CRDT is what handles offline without a sequencer.

Trade-offs and pitfalls

The most relevant trade-off for an offline-first or peer-to-peer product is that CRDTs handle the offline case naturally while classic OT generally assumes a central server is available to serialize operations; the cost is CRDTs' larger memory footprint from per-character metadata and tombstones, which needs its own garbage-collection strategy over time so it doesn't grow unbounded.

Direct answer

This is the multi-master conflict case: because replication was asynchronous and both regions kept accepting writes, there are now two divergent histories that both look locally valid. Detecting divergence means comparing state between regions, via hashes or version metadata, once connectivity returns, not waiting for a customer to report bad data. Reconciling means classifying each conflict by whether it's safely auto-mergeable or needs a compensating action; financial and other invariant-sensitive writes should never be silently overwritten.

Structured elaboration

Detecting divergence. Attach causal metadata to every write, a vector clock (an array with one counter per region, incremented only when that region processes a write, so comparing two vectors shows whether one write causally happened after the other, every counter at least as high, or whether the two happened independently) or at minimum a per-region monotonic sequence number plus timestamp, so that on reconnect two versions of the same key can be compared to tell, mathematically, whether one supersedes the other or whether they are genuinely concurrent, meaning both sides wrote independently during the partition.

sequenceDiagram
    participant RegionA
    participant RegionB
    participant Reconciler

    Note over RegionA,RegionB: Partition: both regions accept writes independently
    RegionA->>RegionA: Write key X, vector clock [A:5,B:3]
    RegionB->>RegionB: Write key X, vector clock [A:4,B:4]
    Note over RegionA,RegionB: Partition heals
    Reconciler->>RegionA: Fetch change log with vector clocks
    Reconciler->>RegionB: Fetch change log with vector clocks
    Reconciler->>Reconciler: Compare vector clocks, classify conflicts
    Reconciler->>Reconciler: Auto-merge commutative writes
    Reconciler->>Reconciler: Apply compensating transaction for invariant conflicts
    Reconciler->>RegionA: Apply resolved state
    Reconciler->>RegionB: Apply resolved state

Reconciling. Not every conflict resolves the same way:

Worked example: detecting a real conflict with vector clocks

Two regions independently update the same key, "user:42.email," during the partition. Region A's write carries vector clock $V_A = [A{:}5, B{:}3]$, Region B's write carries $V_B = [A{:}4, B{:}4]$.

A vector clock $V_1$ dominates $V_2$, meaning $V_1$ happened after $V_2$ and can safely overwrite it, only if every component of $V_1$ is greater than or equal to the corresponding component of $V_2$, with at least one strictly greater. Checking:

Neither vector dominates the other, A leads on its own component, B leads on its own, so this is a genuine concurrent conflict, not a case where one write simply supersedes the other, and it must go through the merge policy rather than being resolved by picking the higher-looking clock.

Worked example: scoping the reconciliation workload

Assume the partition lasted 10 minutes, each region accepted 150 writes/sec across a 5-million-key space:

Of 180,000 total writes across both sides, an estimated 1,620 keys, 0.9%, are true conflicts needing the merge policy; the rest merge by simple union. Sizing the reconciliation pipeline, and any manual-review queue, around this number, not around all writes during the partition, is what keeps the process fast.

Trade-offs & pitfalls

• Silently applying last-writer-wins to every conflict is the most common shortcut, and it's the wrong default for anything with a business invariant, balances, inventory counts: it loses data without a trace and without anyone knowing which write "won."
• Scanning the entire keyspace for divergence, instead of the range touched during the outage window, wastes time and delays recovery.
• A merge policy that isn't tested against the conflict-type classification before the incident means the policy is being designed live, under pressure, which is when mistakes compound.
• Compensating transactions must themselves be idempotent and auditable; a reconciliation "fix" that isn't traceable back to the original conflicting writes creates a second, harder-to-debug data quality problem.

Direct answer

RDS gives you a managed relational engine (normalized schema, joins, ACID transactions across arbitrary rows and tables) that you scale primarily by resizing the instance and adding read replicas. DynamoDB is a managed, schemaless key-value and document store designed around a fixed, known set of access patterns, and it scales horizontally by partitioning; it supports transactions, but they're scoped and costlier than single-item operations. Choose RDS when the workload needs ad hoc queries, joins, and transactional integrity across related entities (an order-management system with orders, line items, and inventory). Choose DynamoDB when you need predictable, low latency at very large or unpredictable scale with a small number of known access patterns (a session store, or a product catalog read by primary key).

Structured elaboration

Data modeling

• RDS: normalize the schema, model relationships with foreign keys, and let the query planner handle joins and ad hoc filters you didn't anticipate up front.
• DynamoDB: denormalize and design the table around the queries you'll actually run (partition key, sort key, and any Global/Local Secondary Indexes), because there is no general-purpose join; the access pattern has to be decided at design time, not query time.

Scaling approach, including the absorbed write-scaling comparison

• RDS/Aurora write scaling: a single writer per cluster. When a write-heavy workload outgrows it, you scale writes by sharding at the application layer, splitting data across multiple Aurora clusters by some key, which means you build and maintain your own routing logic (which shard owns which customer, how you rebalance later).
• DynamoDB write scaling: the table itself splits into physical partitions behind the scenes as throughput grows, with no application-level routing to build. The trade is that a poorly chosen partition key concentrates traffic onto a single physical partition, creating a hot partition that throttles writes to that key even though the table's overall provisioned or on-demand capacity looks sufficient. Aurora sharding trades operational complexity for keeping full SQL semantics within each shard; DynamoDB hot partitions trade a single unified table for a hard requirement to get the partition key design right up front.

Transactional guarantees

• RDS: full ACID transactions across any rows or tables within the database.
• DynamoDB: single-item writes are atomic by default; multi-item ACID transactions are available through TransactWriteItems/TransactGetItems, but are limited in item count and cost more than the equivalent single-item calls.

Consistency, including the absorbed cross-region angle

• Within a single region, DynamoDB offers a choice of eventually consistent (default, cheaper) or strongly consistent reads per request; RDS reads from the primary are always consistent with the latest committed write.
• Strict cross-region consistency is not something either service gives you for free. DynamoDB Global Tables replicate across regions asynchronously (eventually consistent between regions, with last-writer-wins conflict resolution), and Aurora Global Database also replicates to secondary regions asynchronously, with AWS characterizing typical lag as well under a second, but still not synchronous. If a workload genuinely requires strong, read-after-write consistency across regions, the practical answer with either service is to pin all writes for a given piece of data to a single primary region and treat cross-region reads as eventually consistent, rather than assuming a multi-region-active-active setup gives you strict consistency by default.

Backup and restore

• RDS: automated snapshots and point-in-time recovery via the transaction log.
• DynamoDB: on-demand backups and point-in-time recovery to a new table, plus Global Tables for cross-region replication as a DR mechanism, not just a read-scaling one.

Worked example

• RDS fits: an order-management system where an order references a customer, several line items, and an inventory row that must be decremented in the same transaction. The relationships are the point, and ad hoc reporting queries (this month's orders by region and status) are common and unpredictable in shape.
• DynamoDB fits: a mobile app's session store, keyed by session ID, read and written at very high volume with one access pattern (get session by ID, put session by ID) and no need for joins or ad hoc queries across sessions.

Trade-offs and pitfalls

• Picking DynamoDB because "it scales" without first enumerating the access patterns is the most common wrong turn; if the access patterns aren't known or keep changing, that flexibility cost gets paid back later as expensive table redesigns.
• Picking RDS for a workload that's really just high-volume key-value access adds operational overhead (instance sizing, read-replica management, vertical-scaling ceilings) for relational features the workload never uses.
• Assuming DynamoDB transactions behave like RDS transactions; they're real ACID transactions but bounded in scope and more expensive per item than single-item operations, so reach for them only when the specific operation genuinely needs multi-item atomicity.

Direct answer

Prefer a proxy (dedicated routing tier) over pure client-side routing or a "smart" ORM for most production systems: it adds one network hop of latency but centralizes the routing table so a mapping update, a stale-shard fix, or a rollout is one change in one place instead of a redeploy of every client. Client-side routing is the lowest-latency option (no extra hop) and fits when you control every client and can push routing-table updates to all of them reliably and fast; a smart ORM sits in between, convenient for a single-language stack, but it embeds routing logic in the application tier, which means every consuming service re-implements or depends on the same library staying correct and current.

Structured elaboration

Comparing the three approaches

Handling stale mappings

Every approach needs an answer to "the routing table changed, how does the reader find out," because a shard's owner can change (rebalance, resharding, failover) after a mapping was cached:

• Version the mapping: every routing-table snapshot carries a monotonic version number, so a cache can cheaply tell "is my copy current" without re-fetching the whole table on every request.
• Detect misroutes at the shard, not just the client: a shard receiving a request for a key range it does not own returns an explicit "moved" error (not a generic failure) that tells the caller specifically to invalidate and refetch, rather than making the caller guess.
• Prefer push over pure poll for propagation: a pub/sub invalidation notification on mapping change gets routers current within one round trip of the change; a poll-only cache is bounded by its TTL, meaning misroutes persist for up to that TTL after every change.

Safe configuration rollouts

Treat a routing-table update like any other production config change, not a special case: publish the new version behind a feature-flag-style staged rollout (a fraction of routers first), monitor the "moved" error rate as the leading indicator that the new mapping is wrong, and keep the previous version retrievable so a rollback is also just a version pointer flip. Never push a routing-table change that both changes the mapping AND removes the old shard's ability to serve the old mapping in the same step; that removes your rollback path exactly when you are most likely to need it.

Worked example

A proxy-based flow for one request that hits a stale cache entry, showing where each mechanism above fires:

flowchart LR
  Client([Client / app server])
  Router[Proxy router]
  Cache[(Local routing-table cache, versioned)]
  Directory[(Directory service, source of truth)]
  ShardA[Shard A]
  ShardB[Shard B]

  Client -->|"query(shard_key)"| Router
  Router -->|cache lookup| Cache
  Cache -->|hit: version v12| Router
  Router -->|route to owner| ShardA
  ShardA -->|"stale-mapping error"| Router
  Router -->|invalidate + refetch| Directory
  Directory -->|"fresh mapping, version v13"| Cache
  Router -->|retry with v13| ShardB
  Directory -.->|push invalidation on rollout| Cache

The client never sees the stale mapping at all, the proxy absorbs the "moved" error, refetches, and retries transparently; this is the concrete operational payoff of centralizing routing logic in the proxy tier rather than every client independently handling its own stale-cache retry logic.

Trade-offs and pitfalls

• The extra network hop in the proxy approach is a real, ongoing cost on every request, not a one-time migration cost; for a latency-critical hot path where every microsecond matters and the client population is small and tightly controlled, client-side routing with disciplined, fast rollout tooling can be the right trade.
• A smart ORM feels like it gets you the maintainability benefit of centralization "for free" because the logic lives in one library; the trap is forgetting that the DEPLOYMENT is still per-client, so a routing bug fix is only as fast as your slowest consumer's upgrade cycle, easy to overlook until an incident makes it obvious.
• A cache with no version check and only a TTL will keep routing to a stale shard for up to the full TTL after every rebalance; a shorter TTL trades staleness window for more refresh traffic, but a version check on every read (or a push invalidation) removes the trade-off entirely rather than just tuning it.
• The most common configuration-rollout mistake is coupling a mapping change with decommissioning the old shard in the same deploy; keep the old shard live and reachable for a bake period after any mapping change specifically so a bad rollout has somewhere safe to roll back to.

Direct answer

Split the catalog into three physically separate stores tuned for three different access patterns: a document store for the catalog's flexible product attributes (fast, cache-friendly reads), a relational store for inventory (strong consistency at checkout), and a search index for faceted browsing. Keep them in sync with an event stream fed by change data capture (CDC: reading a database's commit log and turning each change into an event other systems can consume), and route product-page reads through a cache in front of the document store so most requests never touch a database at all. A single database cannot deliver sub-100ms reads at catalog scale, flexible per-category attributes, and strict inventory correctness at checkout simultaneously without one of those requirements dragging the other two down.

Mapping requirements to stores

Data flow and duplication

flowchart LR
    Browser[Product page request] --> Cache[[Edge/CDN + read cache]]
    Cache -->|cache miss| Catalog[(Document store\nflexible attributes)]
    Browser --> Search[Faceted search query]
    Search --> Index[(Search engine index)]
    Catalog -->|CDC, near-real-time sync| Index
    Checkout[Checkout: reserve stock] --> Inventory[(Relational inventory store\nstrong consistency)]
    Catalog -->|product metadata, eventually consistent| Inventory
    Catalog -->|change events| Stream[[Event stream]]
    Inventory -->|change events| Stream
    Stream --> DW[(Analytics warehouse)]

The catalog is the source of truth for product attributes and is duplicated, deliberately, into the search index (denormalized into a flat, filterable document per product) and into a thin projection inside the inventory store (just enough product data, like name and SKU, to render a checkout confirmation without a cross-store call). Inventory counts themselves are never duplicated as the source of truth anywhere else: every other view of "how many are left" is a read-only, possibly stale copy, and only the relational store's row is authoritative at the moment of decrement.

Worked example: a 5-million-item catalog

Take a catalog of 5 million SKUs, 50,000 read requests per second at peak, and a write rate (price/attribute updates, new SKUs) of roughly 200 writes/sec.

skus = 5_000_000
reads_per_sec = 50_000
writes_per_sec = 200
cache_hit_ratio = 0.97  # typical for a catalog with a Zipf-like popularity skew

reads_hitting_document_store = reads_per_sec * (1 - cache_hit_ratio)
print(f"reads/sec that reach the document store after caching: {reads_hitting_document_store:.0f}")

# denormalized search document per SKU: ~2 KB average (name, attributes, price, facets)
avg_doc_size_kb = 2
index_size_gb = skus * avg_doc_size_kb / 1024 / 1024
print(f"search index size at 2 KB/doc: {index_size_gb:.1f} GB")

# CDC lag budget: search results are allowed to be eventually consistent (a price change
# can take a few seconds to appear in facets), inventory cannot be.
cdc_target_lag_seconds = 5
print(f"target CDC propagation lag into search index: {cdc_target_lag_seconds}s (acceptable for browsing, NOT used for checkout)")

reads/sec that reach the document store after caching: 1500
search index size at 2 KB/doc: 9.5 GB
target CDC propagation lag into search index: 5s (acceptable for browsing, NOT used for checkout)

At a 97% cache hit ratio, the document store only has to sustain 1,500 reads/sec, not 50,000, which is why sub-100ms reads are achievable even on a modest cluster: the cache does the heavy lifting, and the document store just has to keep the cache warm. The 9.5 GB search index comfortably fits in memory on a small search cluster, so faceted queries stay fast even with 5 million SKUs.

Trade-offs and pitfalls

• Never let the checkout path read stock from the search index or the cache. Those are for browsing. Checkout must read and decrement inventory from the relational store directly, inside a transaction, or two customers can both "successfully" buy the last unit.
• CDC lag is a feature to budget, not an accident to discover. Decide up front how stale a search facet or a cached price is allowed to be (seconds, not milliseconds is normal), and monitor that lag explicitly; a silently stalled CDC connector otherwise shows up as customers browsing phantom stock or old prices with no alert firing.
• Denormalization multiplies write fan-out. A single price update now has to land in the document store, the search index, and the cache invalidation path. Missing one of those three is the most common bug class in this architecture; a single event-driven pipeline that all three subscribe to (rather than three separate write paths) reduces the chance of them drifting apart.
• Don't over-apply polyglot persistence. For a catalog of a few thousand SKUs and modest traffic, a single well-indexed relational database with a cache in front can meet all five requirements, and adding a document store, search engine, and warehouse would be unjustified operational overhead. The split earns its complexity at the scale and read/write asymmetry shown above.