Context & Current Setup We are developing a custom React application (built with Pilot / OSDK) that visualizes time series forecasting. Our underlying data contains millions of time series across tens of millions of rows. Currently, we expose this entire dataset through the Ontology layer so our frontend can query it via Ontology APIs / OSDK.
Key Requirements & Cost Challenge
-
High-Level Aggregations with Flexible Filtering: The app needs to render high-level aggregated forecast views across time series, while allowing users to dynamically filter by a large number of metadata columns/attributes.
-
Static Read-Only Data: Historical records and generated forecast outputs are strictly read-only (they do not trigger writeback Actions or complex Object link traversals).
-
High Infrastructure Costs: Indexing millions of rows into the Ontology solely for visual read queries and on-the-fly aggregations is driving up our compute and storage costs significantly.
Proposed Architectural Shift & Initial Testing We are evaluating a hybrid approach: moving static historical and forecast time series out of full Ontology indexing and querying the underlying Datasets directly via the Foundry SQL Query API to handle dynamic filtering and chart aggregations, while keeping core operational metadata in the Ontology.
Note: I have already tested the SQL API for our dynamic GROUP BY aggregations across millions of rows, and the response latency is definitely fast enough for our UX needs.
Known Trade-Off: Data Lineage Visibility A major downside we’ve identified is traceability. When querying datasets directly via the API, they do not register as application dependencies in Foundry’s Data Lineage graph, breaking downstream tracking for dataset owners.
Questions for the Community:
-
Cost Comparison: Given that performance isn’t an issue, is executing on-demand SQL queries on datasets substantially cheaper than paying for continuous Ontology indexing and Object Set Service storage for millions of rows? One thing that I miss is the possibility to see costs for each SQL query or ontology API request.
-
Experimentation & Alternatives: Has anyone else experimented with this hybrid idea (bypassing the Ontology for heavy, read-only time series)? Or is there a better alternative/established best practice for handling this scale of filtering and aggregation in custom apps?
-
Data Lineage Workarounds & Roadmap: How do you handle the loss of Data Lineage? Is there a recommended way to manually declare dataset consumption? Furthermore, does anyone know if there is a roadmap for Datasets to be natively linked to custom apps (or even Workshop modules) in the Data Lineage graph in the future?
We’d appreciate any insights or lessons learned from teams!