Engineering Flexibility in Semantic Layer Applications with Dynamic Granularities

Background

In recent years, the semantic layer has evolved from a convenience for BI tools into an application foundation. The semantic layer can serve as the ‘query engine’ for customer-facing analytics applications. Analytics teams can now build complete analytics apps on top of dbt-modeled tables, powered by semantic layer tools that translate metrics into dynamic, queryable APIs.

One of our clients is doing exactly that: their product is a self-service analytics platform. Their customers connect raw data and the platform aggregates the data into per-user metrics and cohort breakdowns using Cube’s semantic layer. The client’s engineering team built a service that converts UI inputs (such as date filters) into GraphQL queries, which are then sent to Cube to return data. The flow is something like:

User filters on the UI → Query Builder Service → Cube → Database → UI

A key capability that made this architecture possible is Cube’s support for dynamic data models written with Jinja or Python, which allows developers to parameterize schema logic and generate Cube definitions programmatically. This feature makes it possible to define complex, data-driven configurations such as the one in this case study.

(For reference, Cube’s documentation on dynamic data modeling is here.)

By default, Cube provides date aggregation for standard time buckets - day, week, month, quarter, year. At their customers’ request, our client wanted to develop a feature to give customers granular visibility into any number of weeks e.g. 2 weeks, 3 weeks, 4 weeks, etc. up to 10 weeks. This was a perfect case to use Cube’s custom granularity feature, which allows you to create arbitrary time windows outside of the default day, week, month, etc. This would effectively unlock unlimited date flexibility within their self-service analytics application, because they could easily extend this to any number of days, weeks, months, etc.

The Problem

Cube allows you to implement custom granularities in this way (example from their documentation):

Notice the origin parameter. This anchors the custom granularity to a particular date. Further, this anchors the custom granularity to a particular day of the week. This anchoring behavior introduces two subtle but important issues: fixed start dates and fixed weekdays.

The origin parameter anchors the custom granularity to a specific date. Suppose it’s set to Monday, October 13, 2025. If the user instead selects the following Monday, October 20, 2025, Cube still uses the original anchor and groups data starting from October 13. In practice, a user selecting a date filter of October 20, 2025 (Monday) would expect to see data from October 20 → November 03, but Cube instead grouped from October 13 → October 27. The chart displayed blank data for October 13–20, causing confusion.

Even if a user selects Tuesday, October 14, 2025, as their start date, Cube still groups data from the Monday before October 13th, because the granularity is Monday-based. From the user’s perspective, this feels inconsistent: they picked Tuesday, but their charts and aggregations are aligned to Mondays.

Designing the Solution

We needed to build functionality to handle these date boundaries so data is aligned to the exact start date selected by the user. We explored several different solutions to these issues. Cube doesn’t allow any dynamic variables in the origin parameter, so we couldn’t define a dynamic origin. We looked at returning the data in its non-aggregated form so we could handle the aggregations in the service layer. Ultimately, this approach was too complex and computationally expensive.

Thus, we needed to create a custom granularity for each day of the week and for each week offset between 0 and n-1 (where n is the total number of weeks in the aggregation window).

For example, if we want to bucket data by 3-week periods, we need to define:

monday_based_3_weeks, tuesday_based_3_weeks, wednesday_based_3_weeks, … sunday_based_3_weeks

and further, for each of the weeks between 0 and 3:

monday_based_3_weeks__offset_0, monday_based_3_weeks__offset_1, monday_based_3_weeks__offset_2, tuesday_based_3_weeks__offset_0, … sunday_based_3_weeks__offset_0, sunday_based_3_weeks__offset_1, sunday_based_3_weeks__offset_2

This table shows how each granularity corresponds to a different combination of start day and offset, ensuring consistent alignment regardless of user input.

The Solution (in code)

We can do this with loops! We can start by defining the non-looping case in Cube

Step 1 — Base case: single granularity

Here, we set a variable for the base Monday origin date to the epoch of the Unix time standard, 1970-01-05. This is arbitrary and could be any date as long as it’s a Monday.

Then we can create a list of days of the week and loop them to create granularities for each one.

Step 2 — Looping by weekday

And finally, we can add a granularity for each of the week offsets between 0 and n-1, where n is the number of weeks

Step 3 — Looping by weekday and offset

Because Cube’s YAML schemas can’t perform date arithmetic, we defined weeks_after() as a Python helper function that lives in the Cube repository to offset the origin by k weeks:

We also wrote a small Python utility to map users’ date filters to the correct granularity name. The script maps a given start date and number of weeks (e.g. start_date=2025-10-13, interval_weeks=3) to the corresponding granularity name (e.g. monday_based_3_weeks__offset_0). The script runs within the application layer, connecting the user’s date input and week interval into the appropriate Cube granularity name before constructing the GraphQL query. This mapping ensures the query sent to Cube always aligns with the user’s selected date boundary, regardless of which weekday they start on.

This is our final, working solution for creating limitless date aggregations in the semantic layer. This pattern—parameterizing granularity definitions through Jinja—is generalizable beyond dates. Teams could apply the same approach to create custom categorical dimensions (e.g. fiscal periods, membership tiers, etc.).

Outcome

After implementing this solution, our client was able to move forward developing new features with full-date flexibility. The success of this implementation reinforced the team’s decision to adopt Cube as the core semantic layer for the application.

The change required minimal application-level logic—just adding the Python utility script. This also maintained query performance, since all of the granularities are created when the Cube application is initialized, not at query time. The result: a more intuitive user experience and a more maintainable analytics layer.

Conclusion

This project demonstrates how extending the semantic layer can unlock product-level flexibility without complicating the data model. By combining Jinja logic and Cube’s custom granularities, teams can offer flexible time-based insights that adapt to user input dynamically.

As analytics apps continue to evolve, these kinds of semantic-layer design patterns will be key to balancing performance, maintainability, and flexibility.

If you’re looking to build your semantic layer, create flexible analytics features, or architect dynamic data models, Brooklyn Data is here to help. Reach out to discuss how we can design and implement a solution that fits your data.