Platform to

As per our understanding, the following is the scope of the project, considered for our Solution Design 

• Design a data mart where files from Insurance carriers will be loaded into a Common Data Model (CDM).
  • If new columns are introduced that are not previously mapped or if there are issues during data load/mapping, the system will inform the uploading user and flag it for manual review by the administrator
• UI Portal to upload files, map data, view reports and perform analytics
• Meta data driven approach to map source-> target columns, identify new columns, etc.,
• Input files would be in Excel, CSV and PDF. Incident files will also be provided in Excel
• Files would be loaded every 6 months or can be on-demand at a higher frequency
• Format of the files will be standard for each carrier
• Each file would have information regarding the property, sub-property, claim details, include claim amount
• As column headings could be different, data mart to have a metadata approach to standardize/cleanse columns as far as possible (per carrier)
• Exception handler while data load which will have UI for various types of Exceptions:
  • Any file format that is not in Excel, CSV, PDF
  • If same # of columns are found but column names are different : this can be due to new column/replaced column
  • Wrong data is stored by data type (i.e., alpha numeric in data field)
  • Duplicate claims
• Though we can have loss run report for a property for very long duration, Data mart is expected to capture only for first 5 years (i.e., if the data comes for beyond 5 yrs, that can be handled like any other exception
• Store data that are not in the main data model (unmapped data, data beyond 5 yrs, etc.,) for future use

Out of Scope

• AWS account and infrastructure for Dev, Test, Staging and Production environments will be provided by Leonardo247
• Non-functional tests such as Performance and Security
• Backup and DR implementation and testing
• User Acceptance Testing (any defect fixes from UAT will be addressed)
• No new Identity and Access Management system will be designed for this platform – existing implementations such as Okta or Cognito will be re-used if possible

Qentelli will use the Well-Architected Framework published by AWS for the Technical Architecture and Development. Some of the best practices from our experience are also incorporated in the above Solution Architecture, as described below: 

• Use of AWS-native services as much as possible to reduce Infra & operational complexity
• Decentralized Data Management
• Smart Endpoints and Dumb Pipes
• Loose Coupling and High Cohesion
• Integrated Authentication
• Eventual Consistency
• High Fault Tolerance

Key Technology components used in the Architecture:

• Serverless architecture – the platform usage is not continuous and will rarely hit peak loads. During certain periods in the year, there might be a need to scale the architecture
• React-based UI for simplicity and extensibility. Can be extended for mobile apps easily in the future
• API gateway to invoke the right services as well as manage Authentication. Extensibility for normal microservices can also be achieved easily with this approach

A high-level functional architecture for the Solution is shown below:

Datamart Design Considerations 

• Create a Common Data Model with a common schema that can be extended dynamically
• Master list of columns to be created. For each column list, probability of mapping names to be created
• User will be able to configure column list for each insurance carrier from above master column list. At this point, it might be possible to add category/sub-category details at column/s level, so that user can use this for slicing and dicing in the future
• When a file from a specific carrier is loaded, source file columns’ will be compared
• If the columns are matching, it will consider that the “mapping” is successful. If column/s are not matching, it will move new column to the Exception log, where human intervention is expected to approve
• UI will be provided for user to intervene and approve any new columns (for each carrier). This will add to the Master list of columns as well as to the insurance carrier specific metadata
• Loading of files will have 2 stages. When the columns are 100% matches, the data will automatically be loaded. User intervention will be needed when there is any deviation

Workflow

A simplified workflow for a typical user is shown below:

Solution Architecture

Based on our initial understanding, an indicative Solution Architecture is visualized in the following diagram. Please note that as the project is started, there will be a more detailed evaluation of the needs and an updated Architecture will be developed. 

If an exception such as the below occurs, then the exception is routed through a separate handler and is displayed for the admin to determine what action should be taken. 

Potential types of exceptions:

1. Metadata changes – additions, deletions, updates to existing metadata (such as column names, order of columns etc.)
2. Value Changes – changes in how values are sent in the file (such as full values for state name instead of abbreviations)

For metadata changes, the admin will be able to add new columns from the UI. Deleted columns are typically ignored to avoid loss of data previously captured (no changes are made to the Common Data Model, but ingestion file is updated to ignore the missing columns).

The red lines/arrows represent a typical exception workflow.

The core considerations for the above architecture are:

• Python or similar language for file processing, data extraction and mapping to the storage schema
• Use of Lambdas for the following reason:
  • The data ingestion is typically done infrequently, so use of other architectures like an API model would be operationally expensive
  • The platform needs to perform Data-intensive processing with very less workflows and business logic, so a microservice architecture would not suitable
• Exception handling can be achieved via flat files with context or posting to SQS -> SNS. An exception handler will need to be wired to the UI for approvals
• All ingested files to be stored in an S3 Bucket for future reference before any processing
• Using AWS Aurora or other DBs preferred by Leonardo247 for Relational Data storage helps in reducing license costs and operational complexity, In future, it is easier to push the data into Redshift for large datasets

An indicative timeline based on the scope and complexity is outlined below. The timeline is liable to change during the project based on the complexity, changes in business requirements, technical requirements – as we complete workshops and review the user stories, we may present an updated timeline.

All necessary access to Leonardo systems, Detailed user stories [with Definition of done], along with all file types, Business rules, current and future reports, existing documentation and any other information identified by Qentelli will be provided before the project start date. Any delays from Leonardo in providing requirements, content, clarifications, approvals, UAT, defect prioritization or any other activity may lead to delays in the schedule – such delays will increase the estimated pricing as well. 

This section highlights our delivery approach and all the vital activities that we do in terms of phases. 

• A standard Team (squad) has members from Product/Business, Project Manager/Scrum Master, Architect, Developers, SDETs/QA and Ops 

• Members of this team can be from Leonardo or Qentelli, based on functional and technical knowledge 

• Qentelli will staff the Architecture, Engineering and QA roles, while Leonardo will provide Product Ownership/Functional SME, Infrastructure and Operational support 

The Team structure for this project from Qentelli 

Continuous Development and Integration  

At Qentelli our software developers with extensive knowledge on the latest technologies and are well versed in DevOps, Continuous Integration and Continuous Delivery. The image below describes the various activities that will be part of the development phase. We follow the below core principles for our development practices:

• Test-Driven Development
• Rigorous, regular refactoring
• Continuous integration
• SOLID principles for Design/Development
  • S - Single-Responsibility
  • O - Open-closed
  • L - Liskov substitution
  • I - Interface segregation
  • D - Dependency Inversion
• Pair programming
• Single Repository
• Secure Development Practices based on guidelines from Microsoft and OWASP
• Git based branching strategy

Pipeline Orchestration: 

A completely automated CI Pipeline can significantly reduce the time needed to move a unit of code through different stages and environments. The following diagram shows the typical Pipeline that is fully automated till deployment to Production. 

NOTE: Due to time and cost considerations, fully automated pipelines will not be available in Phase 1.  

The proposal will be executed in Time & Material delivery model with overall pricing estimated at:  

Note: 

• This proposal will be executed on Time and Material pricing model – as scope and design details are uncovered during the project, time and cost estimates will be updated accordingly
• Any major change to estimates of time and cost will be approved by Leonardo
• Offshore refers to Qentelli’s offices or remote based in India
• The above pricing is indicative, based on an 8-hour workday for 22 days a month on average. The monthly invoice submitted to Leonardo may vary each month based on the actual number of working days [holidays, PTO]
• The above pricing is for Professional services only – all licenses and infrastructure costs will be Leonardo’s responsibility