Ravindra BagaleCourses & study guides मराठी Track your progress

Project: Leaderboard with a Sparse GSI on DynamoDB

By , trainer LinkedIn Last updated:

Top-N without Scan. Seoul game PM Jisoo Park and Pune founder Vikram Patil ship BoardLite: sparse GSI only on scored players, padded score#user SK, weekly LB# partitions. Reuse GSI deep-dive; link DAX for hot read boards.

What you'll learn in this chapter

  • Explain sparse GSI
  • Design padded score SK
  • Query top-N descending
  • Weekly board keys
  • Lab + cleanup

1. Everyday example: school sports day board

Only runners who finished get a chalk line. Unranked kids are absent from the board — that is sparse GSI. Highest score at the top = Query descending Limit N.

2. Vertical steps

Patterns -> Sparse attrs -> Score updates -> Top-N Query -> CleanupPatternsSparse attrsScore updatesTop-N QueryCleanup
Cleanup

3. Why do we need this?

Why do we need this?

Scan-all-players for ranks dies at scale. Interviews expect sparse GSI + Query Limit, not “sort in app after Scan”.

4. What is it?

What is it?

BoardLite: base PK=PLAYER#id SK=PROFILE with optional lb_pk=LB#game1, lb_sk=0000009000#playerId. GSI Leaderboard on lb_pk/lb_sk — items without those attrs never appear.

5. How to do it (step by step)

How to do it (step by step)

  1. List patterns: upsert score, top-10, weekly board, unrank.
  2. Choose zero-padded score width (10 digits) + userId tie-break.
  3. Create lab-ddp-board with GSI pk=lb_pk sk=lb_sk.
  4. Put 20 players; set sparse attrs on 12 scorers.
  5. Query GSI PK=LB#game1 ScanIndexForward=false Limit=10.
  6. Remove lb_* from one player; confirm gone; delete table.

6. Key sketch

EntityPKSK
PlayerPLAYER#p1PROFILE
GSI boardLB#game10000009000#p1
WeeklyLB#game1#w450000008800#p2

7. Commands with output and meaning

aws dynamodb create-table --table-name lab-ddp-board \
  --attribute-definitions AttributeName=PK,AttributeType=S AttributeName=SK,AttributeType=S \
    AttributeName=lb_pk,AttributeType=S AttributeName=lb_sk,AttributeType=S \
  --key-schema AttributeName=PK,KeyType=HASH AttributeName=SK,KeyType=RANGE \
  --global-secondary-indexes '[{"IndexName":"Leaderboard","KeySchema":[{"AttributeName":"lb_pk","KeyType":"HASH"},{"AttributeName":"lb_sk","KeyType":"RANGE"}],"Projection":{"ProjectionType":"ALL"}}]' \
  --billing-mode PAY_PER_REQUEST --region ap-south-1
aws dynamodb update-item --table-name lab-ddp-board --key '{"PK":{"S":"PLAYER#p1"},"SK":{"S":"PROFILE"}}' \
  --update-expression "SET score = :s, lb_pk = :gp, lb_sk = :gs" \
  --expression-attribute-values '{":s":{"N":"9000"},":gp":{"S":"LB#game1"},":gs":{"S":"0000009000#p1"}}'
aws dynamodb query --table-name lab-ddp-board --index-name Leaderboard \
  --key-condition-expression "lb_pk = :g" --scan-index-forward --no-scan-index-forward \
  --limit 10 --expression-attribute-values '{":g":{"S":"LB#game1"}}'
# Meaning: descending top-N from sparse GSI only.

Omit lb_* to unrank (sparse). Weekly boards = different lb_pk. Hot read path may add DAX (link).

8. Industry stories

  • Gaming (Jisoo): global + clan boards.
  • EdTech (Sofia): quiz contests.
  • Sales (Omar): monthly revenue ranks.
  • Fitness (Diego): step challenges.
  • Support (Lena): agent CSAT.
  • DevRel (Priya): hackathon scores.

9. Lab

How to do it (step by step)

  1. Create lab-ddp-board + sparse GSI; score 12 players.
  2. Top-10 Query; unrank one; weekly key experiment.
  3. Delete table.

Cleanup (stop billing)

  1. Delete lab resources you created.
  2. Confirm with describe that names are gone.

10. Ops checklist before you ship

  1. Confirm TTL attribute name and units (epoch seconds).
  2. Decide soft-delete vs hard-delete vs archive-to-S3.
  3. Enable PITR on any table you cannot rebuild from source.
  4. Document RPO/RTO and who runs restore drills.
  5. If multi-region: pick write stickiness and conflict rules.
  6. If DAX: prove cache-miss path and warm-up plan.
  7. Tag Owner + Expiry on every lab resource.
  8. Budget alert in the account before load tests.

11. Copy-paste DynamoDB CLI starters (batch 2)

export AWS_DEFAULT_REGION=ap-south-1

# TTL status
aws dynamodb describe-time-to-live --table-name ShopApp

# PITR / continuous backups
aws dynamodb describe-continuous-backups --table-name ShopApp

# On-demand backup
aws dynamodb create-backup --table-name ShopApp --backup-name lab-shop-$(date +%Y%m%d)

# List backups
aws dynamodb list-backups --table-name ShopApp

# Global table description (v2)
aws dynamodb describe-table --table-name ShopApp --query 'Table.Replicas'

12. More industry stories

  • Retail (Aisha Khan, Dubai): TTL clears abandoned carts; soft-delete keeps 30-day undo.
  • Bank (Lucas Meyer, Berlin): PITR on ledger table; restore drill every quarter.
  • Travel (Omar Haddad, Amman): Global Tables for catalog reads; inventory writes sticky.
  • Gaming (Carlos Rivera, Mexico City): DAX for hot profile Gets; never cache money ledgers.
  • Social (Priya Nair, Kochi): adjacency edges for follows; sparse GSI for unread inbox.
  • IoT (Mei Lin, Shenzhen): device#day PK + TTL 14 days; rollups to S3.

13. Glossary (batch 2)

TermMeaning
TTLTime to Live — attribute with epoch seconds; DynamoDB deletes later
Soft deleteFlag deleted=true; item remains until TTL/archive
PITRPoint-in-time recovery — continuous backups window
Global TableMulti-region replicated DynamoDB table
LWWLast-writer-wins conflict resolution on replicas
DAXDynamoDB Accelerator — in-memory cache cluster
Adjacency listEdge items modeling relationships in one table
Fan-outWrite once; push copies to many feeds/inboxes

14. Practice: explain out loud

  1. 60s: TTL vs soft-delete vs S3 archive.
  2. 60s: PITR vs on-demand backup.
  3. 60s: when Global Tables help and hurt.
  4. 60s: DAX consistency trade-off.
  5. 60s: CartLite checkout keys in one breath.
  6. 60s: follow edge vs feed item.
  7. Record once; cut filler; record again.

15. Safe lab hygiene

  • Use ap-south-1 labs with a budget alert.
  • Prefix lab tables with lab- and your initials.
  • Delete replicas, DAX clusters, and backups you created for labs.
  • Never enable Global Tables on production without a write plan.
  • Tag every resource Owner + Expiry.
  • Export/import labs can create large S3 bills — delete prefixes.

17. Delete / archive decision pad

NeedPreferWhy
Undo 7-30 daysSoft-delete flagUser restore
Hard expiry, no readTTL attributeAuto delete later
Compliance keep coldExport/archive to S3Cheap long store
Mistake recoveryPITR / backupTable-level restore

18. Ten oral drill questions

  1. What units does DynamoDB TTL use?
  2. Does TTL delete instantly?
  3. PITR window typical length?
  4. Global Tables conflict default?
  5. When sticky writes matter?
  6. DAX vs API Gateway cache?
  7. Cart PK for a shopper?
  8. Order lines under which PK?
  9. Follow edge SK example?
  10. Unread inbox sparse GSI idea?

19. RPO / RTO mini workshop

  1. Write the worst data loss you can accept (RPO) in minutes.
  2. Write how long restore may take (RTO) before business pain.
  3. Map: on-demand backup vs PITR vs multi-region replica.
  4. Schedule a restore drill on a lab table this month.
  5. Name the person who runs the drill.

20. Cost watch list (batch 2)

  • PITR storage and on-demand backup storage.
  • Global Tables: replicated WCUs in every region.
  • DAX node hours even at idle.
  • Export to S3 + S3 storage + Athena later.
  • Streams + Lambda still bill during TTL deletes.

21. Project Definition of Done

  1. Access patterns written and peer-reviewed.
  2. Sample items drawn for happy path + one failure.
  3. Lab table created, exercised, deleted (or tagged Expiry).
  4. At least one conditional write or transaction exercised.
  5. Cleanup commands pasted in your notes.
  6. One interview answer recorded out loud.
  • TTL hygiene → Backup/PITR for “oops” recovery.
  • Global Tables → sticky writes from capacity/hot lessons.
  • DAX → only after Query shapes are correct.
  • P1 CartLite reuses modeling + transactions.
  • P2 Social reuses adjacency + Streams ideas.

23. Quick self-check (yes/no)

  1. I can explain TTL delay vs instant DeleteItem.
  2. I know when soft-delete beats hard delete.
  3. I can start PITR and say what restore costs in time.
  4. I can name one reason NOT to use Global Tables.
  5. I know DAX does not fix a bad partition key.
  6. I can sketch CartLite USER# and ORD# keys.
  7. I can sketch a follow edge item.
  8. I delete lab replicas/DAX/backups the same day.

24. Common errors and fixes

Each row is a real mistake students and juniors hit. Read the fix before you panic.

Error / symptomFix
Scan for top-NGSI Query Limit desc.
Number SK sort surprisesZero-padded string score.
Dense GSI all playersOnly set lb_* when scored.
Ascending top-NscanIndexForward=false.
Tie chaosPad + append userId.
Hot single game PKShard LB#game#bucket.
Stale CDN boardShort TTL / invalidate.
Leave lab tableDelete same day.
Rewrite GSI chapterLink GSI deep-dive.

25. Troubleshooting checklist

  1. Wrong order: check scanIndexForward=false.
  2. Missing players: they lack lb_* attrs (sparse OK).
  3. Padding length mismatch: fix width everywhere.
  4. GSI throttle end of tournament: on-demand + smooth writes.
  5. Need realtime: DAX/Redis read path (link).

26. FAQ

Q. Should I always use a single table?

Often yes for related access patterns; not a religion. Separate tables are fine for unrelated domains or different capacity/security needs.

Q. Do I still need RDS?

Yes for heavy joins, multi-row SQL reports, and strong relational constraints. See the intro DynamoDB vs RDS chapter.

Q. Is Scan evil?

Scan is a tool for rare admin jobs and small tables. Design so hot paths use Query/GetItem.

Q. Where are Put/Get command details?

In the CRUD survey, CLI and SDK chapters — this deep set links them instead of copying.

Q. Sparse vs ALL projection?

Sparse = missing keys; projection separate choice.

Q. Negative scores?

Offset into unsigned pad range.

Q. Season reset?

New LB#game#season key.

Q. Pagination?

ExclusiveStartKey on GSI Query.

27. Interview Q&A

Q. (B) Sparse GSI?

Only items with GSI keys appear.

Q. (B) Top-N?

Query desc Limit N.

Q. (M) Ties?

paddedScore#userId.

Q. (M) Unrank?

REMOVE lb_pk/lb_sk attrs.

Q. (S/MNC) Million QPS reads?

Cache (DAX/Redis); DynamoDB SoR.

Q. (MNC) Weekly boards?

LB#game#weekN partition.

28. Tips in English, Marathi and Hindi

Ravindra Bagale's Tip (EN)

Sparse GSI + padded score SK + Query Limit — Scan is not a leaderboard.

Ravindra Bagale's Tip (MR)

Sparse GSI + pad + Query Limit.

Tip (HI)

स्पार्स GSI, पैड स्कोर, Query Limit — Scan नहीं।

Try at home

  1. Pad 5 sample scores.
  2. Lab top-10 Query.
  3. Design weekly key.

In short

  1. BoardLite = sparse lb_* GSI.
  2. Padded score#user SK.
  3. Query desc Limit = top-N.
  4. Cleanup labs.

Revision, interview and comparison cards

Revise this lesson in a few minutes. Save the cards on your phone or download them as PNG files. The QR code on each card opens this lesson.

Revision card: Sparse GSI. Indexing every player wastes writes and complicates unrank. Only scored players set lb_pk/lb_sk; omit to unrank. Create GSI → set attrs on scorers → Query top-N → remove attrs to unrank. Jisoo Park keeps unscored players off the index.
Revision card: Sparse GSI · Download PNG · Open card page
Revision card: Top-N Query. Scan-all then sort dies at scale. Query GSI desc Limit N with padded score#userId SK. Pad scores → UpdateItem → Query forward=false Limit 10 → paginate ESK. Priya Nair never Scans for hackathon boards.
Revision card: Top-N Query · Download PNG · Open card page
Comparison: Scan then sort vs GSI Query Limit. Scan then sort: Load all players then sort in app. GSI Query Limit: Sparse GSI Query descending Limit N.
Comparison: Scan then sort vs GSI Query Limit · Download PNG · Open card page
Comparison: Dense GSI all vs Sparse scorers only. Dense GSI all: Force every profile into the board GSI. Sparse scorers only: Set lb_* only when scored; omit to unrank.
Comparison: Dense GSI all vs Sparse scorers only · Download PNG · Open card page
Interview card: Pad scores. Number SK lex sort surprises rankings. Zero-pad score string width; append #userId. Fix width=10 → UpdateItem → Query verify order.
Interview card: Pad scores · Download PNG · Open card page
Interview card: Descending Limit. Ascending Query shows bottom ranks first. scanIndexForward=false with Limit N. Top-10 call → compare to sorted sample → paginate.
Interview card: Descending Limit · Download PNG · Open card page
Interview card: Unrank sparse. Deleting the player just to leave the board is heavy. REMOVE lb_pk/lb_sk so sparse GSI drops the item. Score → appear → remove attrs → Query confirms gone.
Interview card: Unrank sparse · Download PNG · Open card page
Interview card: Weekly board. Overwriting one global board loses history. LB#game#weekN (or season) as GSI PK partitions. Write week45 + week46 → Query each → keep old readable.
Interview card: Weekly board · Download PNG · Open card page

More cards: Ravi Sir's Cards.

Practice Tasks

About this scenario

Amravati Agro Market is a agriculture business in Amravati. Anjali Thakre looks after its IT and cloud work. They count money in INR and run their AWS resources in the ap-south-1 Region. In the tasks below you do real jobs for them using what this lesson teaches. All names, account IDs, IP addresses and numbers are made up for practice.

0 / 16

Easy Sparse GSI optional keys — 1

Why: Def.

What to do: Enter 1 if sparse GSI only indexes items that set GSI keys.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy Top-N = Query Limit desc — 1

Why: Query.

What to do: Enter 1 if top-N uses GSI Query descending Limit N.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy Pad score strings — 1

Why: Sort.

What to do: Enter 1 if score SK uses zero-padded strings.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy Tie-break with userId — 1

Why: Stable.

What to do: Enter 1 if SK appends userId for ties.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy Describe board table

Why: CLI.

What to do: Describe table ShopApp

How:
  1. Read the task and decide which AWS service it is about.
  2. Write aws, then the service name, then the operation (the action word).
  3. Add each needed flag (--name) followed by its value from the task.
  4. Flags can be in any order. Click Check answer.

Easy Board GSI YAML

Why: Sketch.

What to do: YAML: lb_pk: LB#game1, lb_sk: 0000009000#p1.

How:
  1. Start from the top-level keys the task names.
  2. Indent child keys with 2 spaces (no tabs).
  3. Fill in each property with the value from the task.
  4. Click Check answer. Key order does not matter.

Easy Do not force all players into GSI — 1

Why: Sparse.

What to do: Enter 1 if unscored players omit GSI keys.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium Unrank removes lb attrs — 1

Why: Sparse.

What to do: Enter 1 if unrank removes lb_pk/lb_sk.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium scanIndexForward false — 1

Why: Order.

What to do: Enter 1 if high scores need scanIndexForward=false.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium Weekly board new PK — 1

Why: Partition.

What to do: Enter 1 if weekly boards use LB#game#week PK.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium Do not Scan ranks — 1

Why: Anti.

What to do: Enter 1 if Scan is wrong for leaderboards.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium Top query YAML

Why: Flags.

What to do: YAML: forward: false, limit: 10.

How:
  1. Start from the top-level keys the task names.
  2. Indent child keys with 2 spaces (no tabs).
  3. Fill in each property with the value from the task.
  4. Click Check answer. Key order does not matter.

Hard Shard hot game boards — 1

Why: Scale.

What to do: Enter 1 if hot games shard LB#game#bucket.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Hard Hot reads may use DAX — 1

Why: Read.

What to do: Enter 1 if hot boards may add DAX/Redis.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Hard Delete lab same day — 1

Why: Hygiene.

What to do: Enter 1 if lab-ddp-board should be deleted same day.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Hard Link GSI deep-dive — 1

Why: Reuse.

What to do: Enter 1 if this project links the GSI deep-dive chapter.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

See all practice tasks in the Task Hub →