I am going to try to be as clear as possible.
We provide an form builder that enables our users create forms which we dynamically generate and handle submissions for.
We are currently exploring a way to enable users query the data in the responses, and if possible, combine more that one form_id into cubes.
Currently, section A is how our data is modeled, by doing so we are able to groupby ROW_ID and provide responses in a tabular manner, we adopted this since we don’t know what questions the users could be asking. But in adopting Cubejs, I’ve realized that cubejs plays very well with tables, I have already explore materialized views by form_id, however there are a lot of behavioral concerns with that, which has to deal with how end users design a form.
So I am wondering is there a way to use the current table in section A with cubejs, such that we can query and retrieve data to look like section B.
I have tried queryRewrite DynamicSchema Generation, and using COMPILE_CONTEXT to pass relevant information to refresh schemas, however all these work only with Materialized Views and it’s very limiting, plus we also have no control over the inflow of data when a new submission is made.
Case in point
A user can create a form with few required fields, and later edit it to add more required fields
Also some composite data types are a bit of a snag.
So before maybe throwing the towel and looking at this problem from a low level perspective, I’m hoping there is a way we could leverage cubejs, I believe I could be missing something that is staring me in the face