How do you reduce data redundancy and storage costs with optimized integration patterns?

Depending on the way your business manages data, an example for Data Warehouses is that we can choose to use the Star Schema model, which is a multidimensional model. This model has "fact" tables, which are "business facts and metrics" such as sales and contracts. To support the fact tables we have "dimension" tables, with data about products, customers and vendors. These dimension tables can be used with different fact tables, contributing to the goal of reducing storage costs and data duplication.

ETL by itself can help to reduce redundancy, storage costs, and aide in architecture optimization, but when combined with the Data Mesh principles, its more powerful. For example; E: Tailoring ETL processes only to extract necessary data. Each domain team being responsible for extracting relevant data from their source systems. T: Decentralized transformations enhance data quality by adhering to global standards. Domain-specific requirements built in. Minimizes duplication, and reduces redundant processing. L: Federated loading preserves ownership and minimizes duplication. Pre-aggregation in ETL yields efficient storage, offering valuable insights. Finding a balance between your storage efficiency and the scalability of insight is key.

One of the major objective is to control data redundancy with optimized integration pattern and if its though conventional ETL process; so during ETL the major processes or transformation need to be considered: 1. Data Deduplication Batch Processing: Run deduplication algorithms during batch processing to remove duplicates. Real-time Deduplication: Implement real-time checks to prevent duplicate data from entering the system. 2. Data Normalization Use normalization techniques to minimize redundancy in database tables. 3. Incremental Data Loading Delta Loads: Only load the changes (inserts, updates, and deletes) since the last load, rather than reloading the entire dataset.

Dimensional modeling in data warehousing simplifies database design by focusing on business processes, reducing redundancy. It often uses de-normalization to eliminate the need for multiple tables and joins, saving storage space. This model pairs well with columnar storage that employs high levels of data compression. It also allows for efficient use of aggregates and incremental data loads, further reducing storage needs. Logical partitioning strategies make data management more efficient, and the read-optimized nature of the model reduces storage overhead. Overall, dimensional modeling can significantly cut down on data redundancy and storage costs.

ETL is the backbone of the Data warehouse implementation within the organization. It helps to streamline the data ingestion processes from homogeneous and heterogeneous source systems into the data warehouse or data lake, as a result is a single source of truth for the data consumers. Data gets consumed and integrated into the visualization layer that helps the business users visualize, get insights and make better and faster decisions.

ETL offers a tried and tested approach to consolidating data into a centralized repository like a data warehouse. This prevents redundant copies and enables standardization. However, ETL’s batch-oriented nature incurs significant processing overhead and delays.

ELT provides a useful pattern when the data extracted from the source is unstructured or needs to be consumed by multiple target systems. It allows the extraction from an often high-cost source to happen once, storing the output quickly on an inexpensive storage medium, and then being able to perform transforms and ingestion into the target systems at a time and rate that is more convenient. This pattern is especially effective for very large amounts of data, since having an intermediary format allows for a decoupling of the Extract and Transform steps, which are often the most resource-intensive.

Where as delta load is more of a strategy or pattern for transferring only the changed data from source to target system. This often manually implemented and batch-oriented. CDC is a specific technology or feature for capturing and transferring changes in data. Usually automated and can be real-time. All modern databases and data integration tool now comes with CDC features, like, MongoDB has an oplog (operations log) that can be used to implement CDC. AWS DMS (Database Migration Service): Supports CDC as part of its data migration services.

CDC shouldn't be seen as a lone data integration pattern, but part of ETL/ELT. It can define your starting point, and gives you access to the the data that you need to work with. Where possible, you should always try have CDC as the starting point for ETL/ELT jobs, even if you can only use batch processing.

Data Federation (DF) has a unique synergy with Data Mesh, and is emerging as a go-to strategy. DF provides a single view of data from diverse sources without duplication. Aligning with domain-centric ownership, DF maintains data quality and governance, reducing inconsistency risk. Federated data empowers consumers and allows them to query from multiple sources. DF prevents the duplication of data across sources, efficiently utilizing storage resources and lowering your TCO. Data virtualization pairs well with federated data governance and ensures non-redundant data access within Data Mesh. This enables the most efficient data utilization.

While federation can keep reduce data replication , it also come at the cost of more compute and network resources. Also, if you are going to virtualize your data, the computations required need to be audited and versioned because as the business changes or as you discover bugs and cases that need to change the process, you might still need to replicate the process at a certain point in time and that snapshot in time requirement would add a lot of complexity that you need to account for. This is specifically relevant to the cloud, since storage has a cost and so does compute and network resources, then it becomes a real tradeoff to make sure you are reducing the overall cost.

In order for federation to make sense, the distributed entities lose some autonomy in order to conform to standards and make that a priority in order to be true distributed sources. Examples of features that need conformance: 1. Keeping a historical record. 2. Format and metadata might need to be provided to the services doing the queries e.g. Timezone info. 3. An SLA in terms of service performance may be required (Some queries may require large compute resources and not be very common) 4. Usually queries that need to retrieve data from multiple sources would require key fields that allow for mapping and relating the data to other sources, and those keys might not matter or be relevant to the source service.

Data federation and data virtualization are not solutions currently suited to anyone but the wealthiest companies. Possibly plan to implement in 10 years when technology has matured and costs have come down.

Data replication principles intertwined with Data Mesh, is an emerging strategy. The integration is a very cost-effective data architecture. Increasing the reliability of recovery, while reducing redundancy. Since data storage is already optimized within data mesh, you are not synching duplicated data. Whenever we think about data replication, we want to minimize the data movement and duplication. We only want to copy data that is specific and necessary. Replication would also align with decentralized ownership, preserving integrity, and reducing duplication. Data replication can be flexible as well using methods like differential, incremental synchronous/asynchronous replication. This allows the redundancy strategy to be tailored.

Data replication through CDC is a common pattern for RDBMS (databases) to achieve consistency, and if you are using a database, then this is the easiest method for the start of your ETL/ELT jobs.

It really depends on the operating model, for example in a data mesh architecture. Data Source and Integration patterns can be grouped by source aligned domains, data production domain, consumption common domains, distributed domains or aggregated domains. Each will reduce the complexity and noise of the integration pattern. Another point is standardizing the storage layer, some domains might use different cloud providers so the best way is to standardize an open table format to reduce vendor costs and allow objects portability.

In addition to data integration patterns, it's common and crucial to align data retention and purging strategies. When business needs dictate, we must establish a pattern for archival, transition storage patterns, and eventually implement data purging. Furthermore, optimizing storage costs can be achieved by considering alternative data storage types—files, relational databases, or NoSQL databases. We should explore if these cost-effective strategies can be leveraged while maintaining performance and ensuring ease of data access and use.

I would say we all know that everything is a compromise. In this context, reducing storage cost will come at the cost of compromising something else such as speed of data access, complexity of the ingestion process, ability to audit changes in data and the speed of rolling back changes or reverting to a previous state. Before I set a goal of reducing storage costs, I have to know if doing that in the current operating model will not come at a larger cost. Examples of costs I would consider are maintenance and effort in different scenarios, the scalability of current compute resources and the cost of compute, ingress and egress costs of moving data when you want to consolidate for reporting.

More modern approaches for managing centralised data storage but multiple workloads working off of it are model cloud native data platforms such as snowflake. Scaling different types of work loads such as batch, streaming have been a challenge on traditional data warehouses, however, these modern cloud native data platforms takes all the complexity away and make it much easier to manage.

From my point of view (and from our experience) the most important point is to be aware of your goals and for what you are collecting and using the data for. You first! need to define the right business questions in combination with your goals. Just collecting data and not knowing what you need it for is a waste of time, ressources and money.