Part one ended with the table grown 40× and the preview outgrown. Act two opens on the harder truth of scale: not every question has a key — and not every mistake has a preview. This chapter you recover from the one error no guard can catch, then build the search surface that finds everyone it touched.
You'll learn
Turn a BACKUP into a RESTORE — materialize a snapshot as a new table, without touching the live one
Build the door: provision a scoped OpenSearch projection of one entity type, with the whole setup disclosed before consent
Walk through it: search free text with MATCH, read HIGHLIGHT fragments, and let a FACET surface a trend
Know what a derived, eventually consistent projection is — and when to go back to the table of record
The mistake no preview can catch
Sam's job was careful by the book: the WHERE was scoped, the background job re-proved it on every row, the count matched. But the SET itself was wrong — a normalization template that mangled flat numbers on ~1,900 orders. A correct predicate applied a wrong value, and no preview or per-item guard can know your intent. This is exactly the mistake the chapter-three ritual exists for, and Sam kept the ritual: one BACKUP TABLE before the job, ARN saved.
sql
-- Sam's seatbelt, clipped before the job — the chapter-3 ritualBACKUP TABLE "BeanAndBark" AS 'beanandbark-pre-addressfix';-- this morning: materialize that snapshot as a NEW tableRESTORE TABLE "BeanAndBark-pre-addressfix" FROM BACKUP 'arn:aws:dynamodb:eu-west-1:…:table/BeanAndBark/backup/…';
RESTORE TABLE never overwrites. It lowers to RestoreTableFromBackup and builds a new table from the snapshot; the target name must not already exist, and the live table is untouched.
It runs in the background. DynamoDB rebuilds the table from the snapshot; the Background Tasks panel tracks it to ACTIVE. Plan for minutes, not seconds.
Recovery is then an ordinary read. With BeanAndBark-pre-addressfix live, the pre-job ShipTo values are one keyed query away, and the fix-up write goes through the same preview-first ritual as any other.
The restore, as the studio runs itLowered
Materialize Friday's snapshot next to production, so the correct addresses can be read back without touching the live table.
sql
RESTORE TABLE "BeanAndBark-pre-addressfix" FROM BACKUP 'arn:aws:dynamodb:eu-west-1:…:table/BeanAndBark/backup/…'
Executes asRestoreTableFromBackup · new table BeanAndBark-pre-addressfix · source table and backup unchanged
A read-only prod stage refuses both statements — backup and restore are control-plane writes, unlocked the same conscious way as chapter three's UPDATE.
The addresses are recoverable. That's the seatbelt paying out: one line on Friday turned this morning from a disaster into a chore. But recovering the data isn't the same as recovering the customers — every one of them who noticed is now a free-text note in the support pile.
Eighteen thousand notes, three ways to say "never arrived"
Support cases landed in the same table this year — EntityType = 'SupportCase', with a free-text Message. Finding the affected customers means searching prose, and the table has no door for that. A Scan with contains(Message, 'never arrived') reads — and bills — every item, matches only that exact substring, and ranks nothing: "package never showed", "no delivery", and "where is my order" all slip through. A GSI can't fix this either — an index on words isn't an attribute lookup. Free-form text belongs in a derived search projection.
sql
CREATE OPENSEARCH INDEX "bean-and-bark-support" ON "BeanAndBark" PROJECT (CaseId, CustomerName, Message, Status, Carrier, CreatedAt) MAPPING (Message SEARCH, Status FILTER, Carrier FILTER, CreatedAt FILTER) ON INSERT, MODIFY, REMOVE WHERE EntityType = 'SupportCase';
Read the statement as a contract. PROJECT shapes each indexed document to the six fields support triage needs. MAPPING declares intent per field: Message SEARCH becomes analyzed full text; Status, Carrier and CreatedAt get FILTER keyword fields so exact matches, sorting and facets work. ON INSERT, MODIFY, REMOVE propagates all three stream events. And the partial WHERE scopes the projection to support cases only — an item that ever changes out of scope is deleted from the index, so the projection never keeps a ghost copy of something it shouldn't hold.
The preconditions are checked for you — the table needs Streams (NEW_IMAGE or NEW_AND_OLD_IMAGES) and PITR for the seed export, and the stage needs its one-time Search binding (⌘K → Connect Search).
The plan is disclosed before consent — the preview names the PITR export that seeds existing cases, the OpenSearch Ingestion pipeline that keeps the projection current, the IAM and S3 pieces, and the recurring cost lines. Consent comes after the disclosure, never before.
Readiness is visible — the seed export runs, the stream catches up, and the pipeline reaches ACTIVE before the registration goes live. The projection is ready — and stays honestly labelled as eventually consistent.
Walk through the door
sql
SELECT CaseId, CustomerName, Status, Carrier, CreatedAtFROM OPENSEARCH "bean-and-bark-support"WHERE Message MATCH 'never arrived' AND Status = 'OPEN'ORDER BY CreatedAt DESCLIMIT 50HIGHLIGHT (Message)FACET Carrier
Every open "never arrived", newest firstLowered
Find each open case whose message says a delivery never came — in any of the ways customers actually say it — with the matched fragment highlighted and a per-carrier breakdown in the footer.
sql
SELECT CaseId, CustomerName, Status, Carrier, CreatedAtFROM OPENSEARCH "bean-and-bark-support"WHERE Message MATCH 'never arrived' AND Status = 'OPEN'ORDER BY CreatedAt DESCLIMIT 50HIGHLIGHT (Message)FACET Carrier
Executes asOpenSearch Query DSL · match Message · term Status · sort CreatedAt desc · highlight Message · terms facet Carrier
MATCH is analyzed search, not a substring: "package never showed" and "no sign of my delivery" rank alongside the literal phrase.
Exact Status filtering, CreatedAt sorting, and the Carrier facet all work because those fields carry FILTER mappings.
The highlights tell the story you expected: mangled street addresses, bounced couriers, Sam's ~1,900 orders. One of them is Dana — the very first customer you ever looked up, back when the whole company fit in one afternoon. Nadia sends the apology and the credit personally.
The facet tells the story nobody was looking for: over half the open "never arrived" cases predate Friday's job entirely — and they cluster on a single carrier. The bad batch sent you into the inbox; the term buckets found an operational problem that was hiding in prose all along. That's what a search projection is for: not just finding what you came for, but seeing the shape of what's there.
Recap
RESTORE TABLE materializes a backup as a new table — the live table and the snapshot are untouched, and recovery becomes an ordinary keyed read. The seatbelt you clip before is the whole reason there's an after.
Free-form text doesn't belong in a Scan with contains — it belongs in a derived OpenSearch projection, scoped with a partial WHERE, shaped by PROJECT, and typed by MAPPING.
Provisioning is disclosed before consent: the seed export, the ingestion pipeline, the IAM and S3 wiring, and the recurring cost — then readiness is tracked until the pipeline is ACTIVE.
MATCH finds meaning-adjacent phrasings, HIGHLIGHT shows why each case matched, and a FACET can surface a trend you didn't come looking for. The projection finds; the table of record decides.
Search-side check2
1Why not just Scan the table with contains(Message, 'never arrived')?
2A support case is edited so EntityType is no longer 'SupportCase'. What happens in the scoped index?
Try it yourself 2
1Find the damaged bags
Write the search for open cases about beans arriving damaged or torn, newest first, with the matched fragments visible and a status breakdown.
Same door, different words — MATCH takes the phrase, HIGHLIGHT shows why each case matched, and FACET buckets any FILTER-mapped field.
Show solution
MATCH handles the phrasing variants ("bag was torn", "arrived damaged"); Status is FILTER-mapped, so it works as both an exact filter and a facet.
sql
SELECT CaseId, CustomerName, Status, CreatedAtFROM OPENSEARCH "bean-and-bark-support"WHERE Message MATCH 'damaged torn bag'ORDER BY CreatedAt DESCLIMIT 50HIGHLIGHT (Message)FACET Status
2Scope a projection of your own
Bean & Bark's wholesale team wants to search only wholesale order notes — items with EntityType = 'WholesaleOrder' — without indexing the rest of the table. Sketch the CREATE statement.
PROJECT the fields the team reads, give the free-text field a SEARCH mapping and anything you'll filter, sort, or facet a FILTER mapping, and let the partial WHERE hold the scope.
Show solution
The partial WHERE keeps the projection to one entity type, and the out-of-scope-deletion rule keeps it honest over time.
sql
CREATE OPENSEARCH INDEX "bb-wholesale-notes" ON "BeanAndBark" PROJECT (OrderId, Account, Notes, Status, CreatedAt) MAPPING (Notes SEARCH, Status FILTER, CreatedAt FILTER) ON INSERT, MODIFY, REMOVE WHERE EntityType = 'WholesaleOrder'