RDS vs NOSQL
The intent is for openIDL to be serviced and maintained by standard and junior resources. The majority of data oriented developers have worked with ANSI SQL for multiple years; with an eye on staffing a Relational Data Store (RDS) is recommended. A graph database can be loaded from RDS at a later date if relationships and interconnectivity of data elements becomes a strict requirement. ANSI SQL will satisfy all Regulatory Reporting use cases in an affordable and manageable way.
RDS Options
A full list of DBs can be found: https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
Top candidates are Postgres, MariaDB, MySQL
MIN ANSI Requirements
SQL operations cheatsheet, James Madison: http://www.qa76.net/sql_analytics
ANSI HISTORY: https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/#gref
ANSI SQL-92 (MIN): https://en.wikipedia.org/wiki/SQL-92; Advanced Data Types, Case statements, BASIC SQL
ANSI SQL-2003 (Satisfactory): https://en.wikipedia.org/wiki/SQL:2003; SQL with window functions
ANSI SQL-2016 (Best): https://en.wikipedia.org/wiki/SQL:2016 ; SQL with JSON
Modeling Considerations
DHS will be a multi layer cube optimized for error free loading. Model will also provide business level views to assist with Business Requirements.
This will be archived by utilizing a raw model, and materialized views will be built on top of raw tables for Business Users (BU)s.
Model Layers
- Physical Tables.
- Staging Tables
- Raw Tables
- Transaction JSON object.
- Rarely change, requires
- Standing View Layer
- Views to bring raw to logical
- Views to allow for BU analysis with less joins
- More will be added with time, less governance on adding.
- Dynamic View Layer
- Support Specific Extraction Patterns
- Persistent after deployed.
- Query Layer
- ?