Design Log Storage System

Design Log Storage System

What is this problem about?

The Design Log Storage System interview question asks you to build a system that can store logs with unique IDs and timestamps. You must also implement a function to retrieve all log IDs within a specific time range, based on a given "granularity" (Year, Month, Day, Hour, Minute, or Second). For example, if the granularity is "Day", you should ignore everything smaller than a day (hours, minutes, seconds) when calculating the range.

Why is this asked in interviews?

This problem is popular at Apple and Snowflake because it tests string manipulation and range-based querying. It evaluations how you handle hierarchical data (dates) and whether you can transform timestamps into a searchable format. It’s also a test of your ability to handle different levels of precision, a common requirement in data engineering and backend logging systems.

Algorithmic pattern used

This problem often uses an Ordered Set interview pattern or String normalization. A simple way to solve it is to convert every timestamp into a standard, fixed-length string (e.g., YYYY:MM:DD:HH:MM:SS). When a range query comes in, you "truncate" the start and end timestamps based on the granularity by replacing lower-order units with their minimum or maximum possible values. Then, you filter the stored logs based on these new string boundaries.

Example explanation

Suppose you store a log with timestamp 2023:05:15:12:00:00. A query comes in for the range 2023:01:01:00:00:00 to 2023:12:31:23:59:59 with granularity "Year".

1. Truncate start to year level: 2023:00:00:00:00:00:00.
2. Truncate end to year level: 2023:12:31:23:59:59:59. (Actually, for years, you'd just compare the "2023" part).
3. Any log whose timestamp string falls between these two inclusive strings is returned.

Common mistakes candidates make

• Manual Date Math: Trying to use calendar libraries or complex date arithmetic instead of simple string comparisons.
• Granularity Logic: Failing to correctly "pad" or "truncate" the strings for different granularities (e.g., ignoring seconds but including minutes).
• Performance: Using a simple list and scanning everything for every query. While $O(N)$ is often acceptable given the constraints, using a TreeMap (Ordered Map) allows for more efficient range searches.

Interview preparation tip

When dealing with timestamps in a fixed format, string comparison is your best friend because the lexicographical order matches the chronological order. Always ask the interviewer if the logs will be added in chronological order, as this might allow for binary search optimizations.