SQL queryable views similar to the ODS/API

[bradbanister]

The purpose of this discussion is to clarify requirements and brainstorm ideas around Tanager presenting SQL queryable views similar to the ODS/API in a datastore.

For context, there are production systems that directly query the ODS/API datastore for things like validation and reporting. In the Tanager paradigm this kind of data integration would be performed downstream of the Tanager datastore. For example, document change events could be streamed to a data lake where the SQL queries would be made. However, there is a desire in the community to reuse existing ODS/API datastore integrations as much as possible. While data lake SQL queries would follow directly from the structure of API documents, the queries would be fairly different from ones against the ODS/API's unique relational structure.

[bradbanister]

A general question: Are we talking here about creating an official Ed-Fi Specification for SQL views? It's an interesting idea but the ODS/API's schema would be my last choice as a template for that if we were starting from scratch. Just raising this as a note of caution.

[stephenfuqua]

The challenge here is that there are large installations, particularly at the state level, who have built critical tools and reporting on the ODS database, at significant expense. They will be extremely reluctant to adopt the Data Management Service if they do not have the same (or nearly so) relations to query as they do today. Might even be impossible, if they do not have the money to invest in rewriting their applications.

Will these be official specifications for relations? I probably wouldn't publish as such. No more official than today's ODS databases. The Alliance does not offer migration tools between versions today, and we allow there to be some breaking changes in the database structure. Not much typically. The burden is on the consumer to adjust for that. But those changes are typically quite small, unlike the change to "three tables" envisioned in this project.

[audreyshay]

At the state level, we don't do reporting and validation off of the transactional ODS/API database itself as it is far too busy to make that a good idea. We have a separate, Ed-Fi based multi-year database that drives all of our applications. We ETL directly from the ODS/API database to that separate multi-year database. Switching from the direct database ETL to an API based ETL process, while not unreasonable, is a significant undertaking. SQL views would provide some breathing room in that switchover.

We do, though, use the ODS/API database as a starting model for our multi-year database schemas with new major versions. We also use schema comparisons to see the exact database changes that we need to adjust for in our downstream systems when we take updates. The complete loss of the relational database model would result in every implementation needing to regenerate it independently as we all need a relational database.

[stephenfuqua]

At minimum - and I'm sure we will do more than this - we can continue to publish the SQL schema that would be used by the ODS/API. Since we will continue using the tool that builds those .sql files (MetaEd), and we do not anticipate radical changes in the Data Standard, it will not be a burden to publish those SQL files. I will be sure to create a ticket incorporating that into our planned work.

Beyond that, we will help with the transition between systems. It may look like views directly in the DMS database, starter ETL process that pulls from the API and transforms into destinations "ODS-like" tables, or my preferred process (if states like WI are willing to run Kafka): a stream listener that transforms and persists data as they are received. All options are on the table at this point. Helping states succeed with the replacement to the ODS/API Platform is essential to the Ed-Fi Alliance's success.

[bradbanister]

I think the Tanager way of doing this would be as a separate datastore downstream from the Tanager datastore. Call it the "Legacy View" Tanager module. It would be something that subscribes to the change event stream and populates the datastore based on that.

I think the codebase would basically be ODS/API-lite and could reuse some of the original ODS/API code as a starting point. You'd only need the JSON -> SQL portion of the ODS/API codebase. Leveraging that codebase means there would still be codegen but it would only be a subset. As a standalone Tanager module, it would be easier for the community to choose to use or not, not impact Tanager performance either way, and be easier to contribute to and maintain. It would still require MetaEd support for SQL and ApiModel generation, but slimmed down versions.

[audreyshay]

As it currently stands, I essentially have two copies of the data- one in the ODS/API database and one in my downstream multi-year database. The API is constantly churning with modifications, but my multi-year database is updated incrementally from the API database using change queries to allow for a somewhat stable set of data to validate against.

If the proposal is to have effectively a third copy of the data for most implementations (one in the 3-table ODS/API database, one in a streamed relational database and then another in a multi-year database) that is going to be a hard configuration to sell. If, however, this is primarily used as a proof for how to move from the 3-table model you want to the relational database model that we need, we would probably be able to adapt it to our needs. It would essentially be replacing a portion of our ETL.

[brittoa-ewg]

I think this "Legacy View" approach would work for implementations that has built integrations and reports straight to the current ODS database. It can be a separate ODS db from the data management service db and can be populated by an event stream subscriber. That gives the flexibility for implementors to choose whether they want to use it or not.

The Legacy View db has to be populated consistently and without fail. Retry logic and dependency ordering needs to be in place to pull this off as well. A bit of lag from when data hits the Tanager API and when data is saved in this db is acceptable, I think.

[NovaLogicDev]

(one in the 3-table ODS/API database, one in a streamed relational database and then another in a multi-year database)

@audreyshay Perhaps instead of 3 databases the "legacy view" could be this multi-year database? Part of the DMS implementation for the "legacy view" would include built in components to ETL the data into the "legacy view" with the option to use one (legacy)database across years. Theoretically, you could even hook up to existing databases to use as your "legacy view" target, but that may have engineering challenges.

[audreyshay]

Perhaps instead of 3 databases the "legacy view" could be this multi-year database?

I'm open to the multi-year database as being the the legacy view and essentially substituting the DMS for a portion of our current ETL process. It would need to function as a temporary staging area for updates that need to be accepted into the main reporting tables and would need to track the deletes if we went that route.

[stephenfuqua]

Thank you all for the comments. We'll review this comment thread carefully when we work on this epic: Populating ODS-like views or tables