Bigtable Row Key Design for Time-Series Data

PDE By islever Editorial Last updated

Designing for flexibility and portability. Building the pipelines.

Short answer

A Create one unique table for all of the indices, and then use the index and timestamp as the row key design.

Question

You are using Bigtable to persist and serve stock market data for each of the major indices. To serve the trading application, you need to access only the most
recent stock prices that are streaming in. How should you design your row key and tables to ensure that you can access the data with the simplest query?
  1. Create one unique table for all of the indices, and then use the index and timestamp as the row key design. Correct Answer
  2. Create one unique table for all of the indices, and then use a reverse timestamp as the row key design.
  3. For each index, have a separate table and use a timestamp as the row key design.
  4. For each index, have a separate table and use a reverse timestamp as the row key design.

Key Takeaways

Using a single table with a composite key (Index + Timestamp) distributes writes across tablets to prevent hotspots, whereas reverse timestamps alone concentrate writes.

Learn how to design Bigtable row keys for time-series stock data to avoid hotspots while enabling simple retrieval of the most recent records.

Common mistake: Choosing Option B (Reverse Timestamp) because it seems to order data correctly for reads, but ignoring that it concentrates all new writes into a single hotspot area, degrading performance.

Expert Analysis

The correct answer is A. This question tests Bigtable schema design principles, specifically balancing hotspot avoidance with query efficiency.

The trap lies in Option B (Single Table + Reverse Timestamp). While using a single table is generally preferred to avoid metadata overhead, a pure reverse timestamp key causes severe write hotspots. In Bigtable, rows are sorted by key. A reverse timestamp means all new writes go to the beginning of the range (the 'start' of the tablet space), concentrating load on a single node or small set of nodes. This violates the core Bigtable principle of spreading writes across tablets.

Option A solves this by prefixing the timestamp with the index identifier. This high-cardinality prefix ensures that writes for different indices are distributed across different parts of the key space (different tablets), effectively preventing hotspots. To retrieve the most recent prices for a specific index with the "simplest query," you can perform a readrows call starting from the specific index prefix and limit the results to the first row (or a small number of rows). Because the timestamp is part of the key, the data is ordered, and the most recent entry will be at the start of that index's range. This satisfies both the performance requirement (no hotspot) and the access requirement (simple, efficient read).

Options C and D suggest separate tables per index. This is an anti-pattern because it creates excessive metadata overhead and limits scalability. Additionally, using a plain timestamp (C) would still cause hotspots if not prefixed, and reverse timestamps (D) in separate tables would still cause hotspots within those tables.

Official Reference

https://cloud.google.com/bigtable/docs/schema-design

This link opens the official vendor documentation in a new tab.

Exam Strategy

When designing Bigtable schemas, always prioritize write distribution over read convenience if they conflict. If a pattern like reverse timestamps improves read order but concentrates writes, look for a high-cardinality prefix (like an entity ID) to spread the load. Never choose a solution that creates a known hotspot.

Frequently Asked Questions

Why not use a reverse timestamp alone?

A reverse timestamp causes all new writes to target the same region of the key space (the start), creating a massive write hotspot on a single node.

How do you retrieve the most recent record with Option A?

You scan the table starting from the specific index prefix. Since the timestamp is part of the key, the most recent record appears first in the sorted order, allowing you to stop after reading the first row.

More PDE FAQ →

Community Votes

B
41%
A
37%
D
22%

41% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 53 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 30 of 53

  1. John_Pongthorn 👍 17
    This is special case , plese Take a look carefully the below link and read at last paragraph at the bottom of this comment, let everyone share idea, We will go with B, C https://cloud.google.com/bigtable/docs/schema-design#time-based Don't use a timestamp by itself or at the beginning of a row key, because this will cause sequential writes to be pushed onto a single node, creating a hotspot. If you usually retrieve the most recent records first, you can use a reversed timestamp in the row key by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). With a reversed timestamp, the records will be ordered from most recent to least recent.
  2. Wasss123 👍 7
    Answer B : You can read in the same resource you provided : If you usually retrieve the most recent records first, you can use a reversed timestamp in the row key by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). With a reversed timestamp, the records will be ordered from most recent to least recent. So B is correct
  3. iooj 👍 6 Answered A
    Row keys that start with a timestamp (irrespective reversed or not) causes sequential writes to be pushed onto a single node, creating a hotspot. If you put a timestamp in a row key, precede it with a high-cardinality value (index in our case) to avoid hotspots. The ideal option would be: "use the index and reversed timestamp as the row key design".
  4. arien_chen 👍 6 Answered D
    Option B using reverse timestamp only, this is not the answer. the right answer should be using the index and revers timestamp as the row key. So, Option D is the only answer, because not A,B,C .
  5. jkhong 👍 5
    C, D -> Bigtable has a limitation of 1000 tables per instances. Since we are concerned of stock data, there has bound to be thousands... It is also an anti pattern to store similar schemas in separate tables. https://cloud.google.com/bigtable/docs/schema-design#tables Don't know why is everybody harping on the fact that timestamp or reverse timestamp is the only row key, or that they are the primary key. Im interpreting it as having timestamp as part of the design. We'll need reverse timestamp since we are only concerned with recent data so B, with only 1 unique table for all stock indexes
Show 25 more comments
  1. datapassionate 👍 5 Answered B
    B is a correct answer because "you need to access only the most recent stock prices" "If you usually retrieve the most recent records first, you can use a reversed timestamp in the row key by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). With a reversed timestamp, the records will be ordered from most recent to least recent." https://cloud.google.com/bigtable/docs/schema-design#time-based
  2. Teraflow 👍 5 Answered A
    Indexes need to be unique, reverse timestamp is not necessarily unique
  3. AWSandeep 👍 4 Answered A
    A. Create one unique table for all of the indices, and then use the index and timestamp as the row key design. If you put a timestamp in a row key, precede it with a high-cardinality value like a stock ID to avoid hotspots. Also, store datasets with similar schemas in the same table, rather than in separate tables. Sending requests to many different tables can increase backend connection overhead, resulting in increased tail latency. Having multiple tables of different sizes can disrupt the behind-the-scenes load balancing that makes Bigtable function well. You can simulate a reverse scan in BigTable to get the most recent additions to the table. References: https://cloud.google.com/bigtable/docs/reads https://cloud.google.com/bigtable/docs/schema-design
  4. Prudvi3266 👍 4 Answered A
    yes reverse time stamp is recommended to prevent hot spot. But our query pattern is we need most recent record the is easy when you use Timestamp and Also option a stating that our row key not starting with time stamp which is index#timestamp and which is the most efficient way for this scenario.
  5. shangning007 👍 4 Answered D
    I don't think any answer is correct. A lot people upvote for B, but based on https://cloud.google.com/bigtable/docs/schema-design#time-based, "As with any timestamp, avoid starting a row key with a reversed timestamp so that you don't cause hotspots."
  6. Amey_Yeole 👍 4
    B is correct. By reversing the timestamp, you can design a row key where the most recent event appears at the start of the table instead of the end. As a result, you can get the N most recent events simply by retrieving the first N rows of the table.
  7. Augustax 👍 4 Answered A
    Agree A is the best option because: 1. Multi-tenancy solution 2. As with any timestamp, avoid starting a row key with a reversed timestamp so that you don't cause hotspots.
  8. Ryannn23 👍 3 Answered A
    Vote A, as explained by Augustax: Agree A is the best option because: 1. Multi-tenancy solution 2. As with any timestamp, avoid starting a row key with a reversed timestamp so that you don't cause hotspots.
  9. kastuarr 👍 3 Answered D
    D will avoid hot spotting , allow a very fast query and the reverse time stamp will allow recent records to be retrieved easily
  10. niketd 👍 3 Answered B
    The key here is "only the most recent stock prices". Doesn't talk about accessing a specific index - so answer should be B
  11. Remi2021 👍 3 Answered B
    reversing the timestamp
  12. Ryannn23 👍 2
    According to the link you provided: If you usually retrieve the most recent records first in your queries, a pattern to consider is using reversed timestamps in the row key. This pattern causes rows to be ordered from most recent to least recent, so more recent data is earlier in the table. --------------------- READ CAREFULY -------------------------------------------------------------------------------- As with any timestamp, avoid starting a row key with a reversed timestamp so that you don't cause hotspots. ----------------------------------------------------------------------------------------------------------------------------------- You can get a reversed timestamp by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). Hence, starting row key with timestamp should be avoided (normal or reversed). That leads to answer A, which is a best practice.
  13. TVH_Data_Engineer 👍 2 Answered B
    B. One unique table for all indices, reverse timestamp as row key: A single table for all indices keeps the structure simple. Using a reverse timestamp as part of the row key ensures that the most recent data comes first in the sorted order. This design is beneficial for quickly accessing the latest data. For example: you can convert the timestamp to a string and format it in reverse order, like "yyyyMMddHHmmss", ensuring newer dates and times are sorted lexicographically before older ones.
  14. maci_f 👍 2
    But that only applies to using only the plain timestamp. With the reversed timestamp, the situation is different -- reversing the timestamp prevents creating hotspots. For the very same reason, the article also recommends to avoid sequential IDs as row-keys, but recommends using reversed IDs: "A safer approach is to use a reversed version of the user's numeric ID, which spreads traffic more evenly across all of the nodes for your Bigtable table."
  15. ToiToi 👍 2 Answered D
    Why other options are not as suitable: A and B (One table for all indices): Storing all indices in a single table can lead to performance issues as the table grows larger. It also makes it harder to scale individual indices independently. C (Timestamp as row key): Using a regular timestamp would place the most recent data at the end of the table, making it less efficient to retrieve the latest prices.
  16. ajdf 👍 2 Answered B
    https://cloud.google.com/bigtable/docs/schema-design#time-based If you usually retrieve the most recent records first, you can use a reversed timestamp in the row key by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). With a reversed timestamp, the records will be ordered from most recent to least recent.
  17. kelvintoys93 👍 2
    How does reversing timestamp help in query speed? its still gonna scan through the whole table to output the result. According to Google, "It's important to create a row key that makes it possible to retrieve a well-defined range of rows. Otherwise, your query requires a table scan, which is much slower than retrieving specific rows." Therefore I go for A.
  18. Atnafu 👍 2
    plus If you usually retrieve the most recent records first, you can use a reversed timestamp that is not row key design Reverse is with domain on schema design If you're storing data about entities that can be represented as domain names, consider using a reverse domain name (for example, com.company.product) as the row key.
  19. devaid 👍 2
    2nd tought: I'm agree with B, but the stock symbol as part of the row key is needed, like: "stock#reverseTimeStamp". So, in that case makes more sense D because it create one table for every index, but that is not efficient on Spanner so i keep with B ...
  20. vaga1 👍 2 Answered B
    the answer relieves on whether the application need to access the whole indexes at the same time or not. If yes then is B, if no is A. in mind the answer is yes, so B makes more sense: I retrieve all the list at the same time.
  21. DiegoGonL 👍 2 Answered B
    I think its B because it ask that we need to ensure that you can access the data with the simplest query, not to create the rowkey as the best practices (which should include the index first and then the timestamp)
  22. Thobm 👍 2 Answered B
    B avoids hotspots ("major indices" is a small number, so we'd have a couple of hotspots otherwise), and follows BigTable best practices for one single table: https://cloud.google.com/bigtable/docs/schema-design
  23. SamuelTsch 👍 2 Answered D
    Option B and Option D are both from my point of view correct. It depens on the situation. If there is need to get the information from each stock index, then D is more suitable. Otherwise B.
  24. mayankazyour 👍 2 Answered D
    1. Reverse Timestamp for most recent stock prices 2. Having different table for each stock is more efficient, improves the query performance and option B doesn't specify stock in row key.
  25. Lanro 👍 2 Answered B
    https://cloud.google.com/bigtable/docs/schema-design#row-keys - If you usually retrieve the most recent records first, you can use a reversed timestamp B it is.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

Monitoring Cloud Bigtable for Cluster Scaling Decisions

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

← Back to PDE Study Guide