The primary objective of this article series is to discuss the main professional, management, and organizational challenges around data warehousing today, and to provide a guide for data warehouse managers who want to view the impact of their daily work and the consequences of their decisions from a strategic perspective.
It is important to understand the impact of management-level decisions from an analyst and developer perspective and vice versa: the later impact of developers' coding conventions, shortcomings in development policies, or non-compliance therewith must also be examined, as felt in management decisions.
This article is primarily intended for data warehouse professional managers who
are looking for a path somewhere within the professional and political power field between developers and high-level IT managers,
want to fill the often-repeated term "Data Governance" with real substance,
want to expand their professional toolkit with a strategic approach that also affects data warehouses
with the aim of ensuring that the data loaded into the data warehouse remains a sustainable business value in the long term.
Development Standards
When a large enterprise decides to implement a data warehouse, it is typically the result of some strategic decision. Implementing a central data warehouse can significantly influence an organization's Data Governance strategy and internal IT environment over a 10-20 year horizon, so it is important that our data warehouse rests on stable foundations in the long term, preserving the strategic commitment of management. One element of this is to establish development standards among professional managers, architects, and lead developers that fundamentally frame the technological and organizational conditions for developers and analysts.
When implementing a new data warehouse, we can easily feel that the detailed elaboration of these policies and development guidelines, and the control of the development process, take up a disproportionate amount of time with seemingly little result. We should keep in mind that this work is worth doing exclusively on a greenfield basis, with a "measure twice, cut once" approach. Let us not forget: Our decision can have an impact over a horizon of up to 10-20 years, defining the work of data warehouse professionals, and thereby exerting a significant impact on the perception of our data warehouse from sister systems.
In this article, we do not cover data warehouses that are often positioned as out-of-the-box products by vendors (e.g., SAP BW), as their market penetration in Hungary is low.
Let's look at some examples:
Standardized Loaders
These are well-specified and version-controlled procedures that can play a fundamental role in our data warehouse ETL processes. The use of our standardized loading procedures is mandatory for all developers; deviating from this is justified at most in the early life cycle of our data warehouse. Their operating principles are roughly the same at all financial institutions. In organizations where developers use pre-written, tested, and universally callable table-loading procedures, the probability of error occurrence is significantly reduced, and the loading logic of technical fields as well as their names are predefined. Of course, some level of standardization is also possible and necessary in ETL processes developed/parameterized in developer or integration frameworks (ODI).
It may be practical to create these procedures separately at the Stage, DW, and DM levels, as this physically enforces at the schema level that a given procedure can only access the specific data warehouse segment. This can also reduce developers' unreasonable innovation ideas, or pressure from other business line managers, by citing our internal regulations. In the short term, this entails a level of rigidity that is acceptable, which in the long term takes the edge off the misconception that "the exception proves the rule."
What kind of table-loading procedures could these be? Without claiming completeness:
Tables containing snapshot data (e.g., balance) loaded with business validity start and end fields;
Tables loaded with technical validity start and end fields,
When the data warehouse learned about the business validity and its changes. This can play a role when a given system sends data valid for day T+n-1 on day T+n.
Timestamp-based storage when loading transaction tables;
Procedures creating dynamic tables (either snapshot or transaction).
By dynamic tables, we mean tables where, at the DM level, a new field is physically created based on a new value set element generated in a table at the DW level, using a predefined loading logic, without additional development. This could be the introduction and storage of a new product code at the DW level, followed by the automatic creation of product usage field(s) at the data mart level without additional development.
Etc.
Standardized Interfaces
Technically, communication between a data warehouse and sister systems can be set up in many different ways. Without claiming completeness, we can communicate via DB links, file transfer based on physical files, receive data in a push/pull manner, fetch data from the databases of sister systems, or use newer technologies like Apache Kafka and Nifi to our own liking. We can operate with traditional T+n day batch-based processing, or even near real-time. However, it is important not to confuse this with the concept of real-time data warehouses, which, due to the architecture characteristic of data warehouses, can actually never be real-time (at best reasonably fast, e.g., T + 2 min).
We cannot forget one thing: whatever we implement will determine the competency requirements of our developers and operators, the rebuilding cost and lead time of our environments, and even the database license costs in the long run.
It is important to adhere to a few basic principles:
When designing communication with the DW, it is worth considering the professional standards and technologies already in use recommended by internal IT. At the same time, in communication with sister systems, it is advisable for the sister systems to adapt, as probably none of them will receive data from as many sources as our data warehouse. Setting aside data warehouses created for special purposes (e.g., IFRS).
No matter how tight the deadline, if we feel professional responsibility for our data warehouse, we should not communicate with other systems via DB-links or interface schemas exposed per system. Sure, these seem like easy, quick solutions, especially during an initial data warehouse implementation where database developers do not come from a data warehousing background. The problem is that a poor solution implemented in the short term creates a precedent and can lead subsequent developments in the wrong direction. Because of the above, no matter how good a solution it seems:
Avoid serving the data warehouse with data loaded into an interface schema on the source system side, as this certainly represents a unique solution that cannot be applied in communication with other systems, where a monitoring procedure on the DW side needs to be developed and write permissions are required in the sister system's database. Rebuilding and copying environments is time-consuming, difficult, and expensive due to physically separated databases, and our data warehouse's architectural exposure to sister systems is high.
For similar reasons, it is worth avoiding having other systems write directly into our data warehouse tables, thereby implementing a push data delivery method. Of course, where a data warehouse PM, keeping the fast and cheap implementation of business requirements in mind, tries to take a stand on technological solutions in an environment with permissive standards, we are forced to manage the above risk.
Based on the above, we can see that our interfaces need to be designed in such a way that our data warehouse environments do not require essential integration with sister systems, and in the event of production errors, we can completely rebuild a snapshot table. In extreme cases, due to business validity handling, we might even need to reload several months of snapshot data.
Standardized DW Architecture
It can be a huge help if the responsibility for adhering to/enforcing professional integration standards is assigned to a role. In smaller organizations, however, we can hardly expect a dedicated data warehouse architect; we are much more likely to work with general system integration (solution) architects. In this case, it poses a risk if these colleagues are socialized in online data connections or (micro)service architectures, where the goal is fundamentally sending and receiving atomic-level data. The world of classic (batch) data warehouses is not like that.
In these systems, there are batch processes that run subject to prerequisites, where our goal is processing large volumes of data, often huge snapshot tables, on multiple threads. The two are conceptually different; their toolkits are about as similar as those of a car mechanic and a carpenter. Of course, a professional can be a successful car mechanic and carpenter at the same time, but it might be wiser to trust our house to someone who is exclusively a carpenter, and our car to someone who is exclusively a car mechanic.
Protecting our architecture fundamentally involves laying down rules that prevent retroactive correction of our data to the greatest extent possible. In many cases, of course, it is inevitable to modify our data retroactively. Although serving data correction requests may earn extra points with the business client in the short term, in the long term, it can undermine management's faith in our data warehouse. It is important, therefore, not to try to fix every source system data quality issue on the data warehouse side, even if this leads to customer satisfaction in the short term.
Instead, strive to establish data quality KPIs, the violation of which results in inconvenience on the sister system's side, possibly leading to financing or budget allocation tasks.
In extreme cases, deficiencies in development standards can also lead to the data warehouse starting to use hardware resources with decreasing efficiency: whether due to inappropriate use of keys and constraints, poor state of indexes, or obsolete developer conventions. However, most of these can be filtered out by using the Dexter application integrated into the release process.
Standardized Miscellaneous
Many DWH processes can be standardized; we have listed the most essential ones above. Naturally, schema, table, field naming, script naming, operation, DB link names, business, IT, and system specifications, operational documentation, and a thousand other things can be standardized.
In addition to and in harmony with development standards, it is also essential to lay down analytical standards. Imagine how much overhead is created if we do not include communication methods and middleware frameworks between systems in internal regulations. By not doing so, we put decisions affecting the boundaries of developer competencies into the hands of business analysts, which they do not really have a complete view of. It is important that communication between systems is not figured out ad-hoc following individual developments, but defined in regulations or architectural guidelines when the first tables of our data warehouse are created.
Maintaining specifications and their quality can also be a problem. We often find that table-loading logics for data warehouse segments are either non-existent or poorly defined. This means that only the modification is defined in the development requests, but in the absence of the AS IS state, this can only be done in poor quality. If it remains this way for long enough, a sort of refactoring will eventually be inevitable. In such cases, we can look for solutions in reorganizing our specification/request management processes, and/or in using Dalia, which is already used in several places in the banking and insurance sectors. By applying Dalia, it is possible to visualize Oracle data connections and, along certain metrics, analyze data connections and programmed relationships, thereby facilitating modification and redesign tasks. Thanks to the impact assessment module, we can also get a picture of the potential consequences of planned modifications.
The point is to rely on unique solutions to the least extent possible. Of course, this does not do much for the developers' need for professional challenges, but in return, our system can create a technological environment that is livable in the long term, where we have the opportunity to work with junior colleagues too, thanks to the low barrier to entry.
