1. Overview and Main Features¶
RED is an extension for Exasol databases that provides a complete, end-to-end data warehouse solution. With DWH-as-Code, the whole warehouse is defined declaratively in metadata: loading, historisation, security and monitoring need no hand-written pipelines and no external scheduler. Projects go live faster, run with less effort, and every change stays traceable.
1.1. At a Glance¶
Fast projects: a new source is onboarded by filling in metadata, not by building pipelines.
Near real-time loading from databases, files and REST APIs, without an external scheduler.
Complete bitemporal historisation: reconstruct data as it was valid on any day.
Fine-granular security on object, row and column level, with tenant separation and role management via Active Directory or other identity management systems.
Complete data lineage on column level, from the source to the dashboard.
Monitoring of the cluster, the database and the framework itself.
GDPR anonymisation of historised personal data.
No vendor lock-in: standard Exasol tables, readable metadata, plain SQL views.
Runs on premises or in any cloud.
The following sections describe the main features in more detail.
1.2. Shortest Project Turnaround Times¶
Loading a new source with complete historisation into the analytical database is simple and only takes filling out the following metadata in the relevant tables:
Define the location of the source
Define a unique key (Business Surrogate Key (BSK)) and the type of historisation (event or period data)
Define load cycles (calendar logic)
Define security (on object, row and column level)
Activate the loading
Each of these steps is explained in more detail in the following chapters.
This declarative approach with its built-in calendar control and implicit dependency analysis eliminates the need for elaborate job designs and custom code writing. The data is automatically transferred 1:1 with unchanged structures, but completely and traceably historised, and is stored in tables. It is directly available to the department via a view layer; if, for example, a BI tool requires it, further views can be added.
1.3. Automated Operation¶
In operation, the extraction of data sources takes place near real time via direct connections to the data source or via file load. The loading is highly parallel and asynchronous. No jobs need to be defined and dependencies within the data are recognised implicitly.
After the automated, complete historisation of the data, it is stored in the Persistent Staging Area. Conversions or transformations of the data are usually not necessary. However, if these are needed in special cases, they can be integrated via another view layer.
1.4. Complete Historisation¶
Historisation allows the reconstruction of data as it was valid at any point in the past. In other words, the data status that existed at a certain point in time, e.g. when a report was executed, can be queried. Historisation also makes it possible to analyse the complete historical development of individual data records.
To meet these requirements, all data is subject to strict bi-temporal, two-dimensional historisation. There is a functional and a technical validity period for each data set. The functional period results from the functional requirements, while the technical period reflects the time of storage. Another benefit of this type of historisation is that it enables a partial restore on table level.
1.5. Fine-Granular Security¶
Security is a central component: Personalised user accounts receive access rights on object, row and column level.
On the first level, classic access rights are granted to groups of objects.
On the second level, it can be decided for each object on the row level which data should be visible for which users. Tenants separate the users within the same data pool.
On the third level, sensitive columns and the respective user access can be defined. The declarative assignment of permissions ensures that this high level of detail remains maintainable. Administration is kept to a minimum as automated processes take over complex activities in the background.
Users and their rights can be managed automatically via Active Directory or another identity management system (see RED Authorisation Concept).
The business department has access via virtual layers (view layers), which are placed on top of the data tables. The business department does not have direct access to the base tables. The view layer also maps the access security at object, row and column level.
1.6. Complete Data Lineage¶
RED records lineage on column level, from the source system through all layers up to the BI dashboard. This shows at once which reports are affected by a change in a source, and it supports audits. See Data Lineage.
1.7. Monitoring, Triggers and Integration¶
Monitoring: a complete monitoring interface covers the cluster, the database and the framework itself. All control and status information is available in the web GUI RED Vision and in database views (see Monitoring).
Triggers: once an application reaches a defined data status, RED can send notifications, start third-party processes and export data (see Triggering Actions).
REST API: the REST API gives access to the framework, including a generic SQL endpoint for Exasol (SQL over REST).
1.8. Flexibility for the Business¶
Secure sandboxes: data scientists and analysts work in their own schemas with the data they are allowed to see, without affecting production (see Sandboxes).
Client tool-agnostic: users choose the tools that suit them best, as the data is available via standard Exasol access. The security rules apply independently of the tool.
1.9. AI Use Cases¶
RED provides a fully historised data pool with secure sandboxing, an ideal basis for AI projects. In RED Vision, the assistant AskWally answers questions about RED and the state of the system, explains lineage and the impact of changes, and helps to write SQL for questions about your data. The user runs this SQL with their own access rights. The language model is configurable, from cloud providers to a locally operated model.
1.10. Support for Regulatory Frameworks¶
RED supports compliance with
NIS-2
GDPR
DORA
BCBS 239
Complete historisation, column-level lineage, personalised access control and GDPR anonymisation provide the evidence and controls that regulated industries need.
1.11. No Vendor Lock-In¶
Choosing a framework should not mean losing control of your own data logic. RED is built so that you can leave at any time, without a rebuild project and without depending on us.
Why this is so:
Your data stays in standard tables. All data lives in ordinary Exasol tables in your own database. RED does not use a proprietary storage format.
Your rules are readable. All metadata is stored in Exasol and can be queried with plain SQL. This covers sources, keys, load cycles, security and the history of changes. No configuration is hidden in a tool, a file or a vendor-owned repository (see Monitoring).
Your transformations are plain SQL views. Business logic is not buried in jobs, scripts or a graphical pipeline. It sits in views that anyone with SQL knowledge can read, test and reuse (see the layered structure).
The ELT logic is readable. All transformations are plain SQL, readable in the database. RED contains no procedural transformation logic.
What this means for your organisation:
Migration is possible at any time. The ELT logic can be taken over by another solution, because it is plain SQL and its metadata is readable.
No dependency on a single supplier. Your knowledge and your rules stay in your company, in a standard database.
Lower long-term risk. You can decide on RED based on its benefits, not because leaving would be too expensive.
Note
RED is licensed software. However, its ELT logic is plain SQL that you can read in your database at any time. After a migration, only the scheduling and orchestration, which RED performs on its server (see the Automation Module), would have to be replaced by another solution.
1.12. Provider and Availability¶
RED is developed and continuously improved by Sphinx IT Consulting in Vienna, Austria, and has been on the market since 2020. RED and Exasol run on premises and in any cloud.
1.13. Summary¶
Central features are:
Shortest project lead time and easy handling in operation
Highly parallelised, asynchronous loading without the need to define jobs
Complete historisation of data - traceability of all data changes
Fine-granular authorisation concept with personalised access
Complete data lineage on column level
Monitoring, triggers and REST API for integration
Secure sandboxes and freedom of choice of client tools
Use of existing well-known data structures without adaptation
High performance out-of-the-box
Supports compliance with NIS-2, GDPR, DORA and BCBS 239
No vendor lock-in: readable metadata and plain SQL views, all ELT logic stays in your database