Comparing Various Data Repositories
As your data journey veers towards the Modern Data Stack, you will need a repository to store large amounts of data. In this section, we present three data repository solutions, Data Warehouse, Data Lake, and Data Lakehouse.
We first start off with a brief comparison of each data repository solution, followed by a walkthrough of each solution - starting with their basic definitions, what goes on under the hood, the benefits and disadvantages of each solution, and finally potential use cases.
Data Warehouse
Definition
A Data Warehouse (DW) is a centralised repository used to store large amounts of information from multiple sources within an organisation. Data flows into the DW from multiple sources such as transactional systems, relational databases, or other sources, typically on a regular cadence. The DW stores historical data by combining relational datasets from multiple sources, integrating, and filtering the relevant data for specific tasks. This makes the DW a single source of truth for several datasets, with a core reporting and business analytics component.
DWs also function on an ETL (Extract, Transform, Load) framework where data is extracted from multiple sources, transformed, cleaned, and loaded into the DW. Irrelevant data is discarded. As such, DWs are mostly used to support reporting and analytics workloads.
Under the Hood
The DW may contain multiple databases where datasets are structured in tables and columns within each database. Each column would define the description of the data and tables can be organised inside of schemas (think of “folders”). When the data is ingested, it is stored in various tables described in the schema. Query tools use the schema to determine which data tables are required for analysis.
A DW is typically made up of 3 tiers. The first (top) tier represents presentation tier. This tier presents results through reporting, data analysis, and data mining tools. The second (or middle) tier represents the access tier. This tier houses query tools that are used to access and analyse data. The (bottom) third tier would be the storage tier. This tier is where data is stored and automatically loaded. Data that is frequently accessed is stored in very fast storage while Infrequently Accessed (IA) data is stored in cheap object stores.
Benefits
- Datasets in DWs undergo additional transformations before being loaded into the warehouse. This ensures that datasets are maintained at a consistent quality once it enters the DW
- DWs makes it easier for researchers to navigate and work with data as not much transformation is required once data is loaded into the DW
- DWs allow data to be consolidated from different sources
- DWs increases the processing power and speed required in data analytics and business intelligence workloads
- Reduces time required to gather data
- DWs are separate from transactional systems. This allows DWs to focus towards analytics use cases
- DWs can serve as the data storage backbone for organisations, allowing them to make complex analysis on the data and use the results to make informed decisions
- DWs provide a single repository for current and historical data
- DWs provide secure access to authorised users. Business users who do not need access to the source data would not be allowed access to the DW, removing the potential threat vector
Disadvantages
- While DWs perform well with structured data, they do not typically perform as well with semi-structured or unstructured data
- Not suitable for machine learning use cases
- DWs can be expensive to implement and maintain
- DWs are typically not static and becomes outdated after some time, requiring regular maintenance
- DW’s ability to combine data from multiple sources means that the DW may contain different measurements/titles that denote the same data type if data governance is not enforced in the DW
- Increased requirements for system’s reliability
- Account for protection against information leakage and storage resistance to hacker attacks
When to use a Data Warehouse
- DWs do not need to follow the same data structures as OLTP DBs
- Datasets loaded into DWs undergo multiple transformations to ensure consistency and quality
- DWs makes it easy to access historical data from multiple locations
- DWs provide a centralised location using common formats/keys/data models
- DWs allow data to be quickly pulled from multiple structured sources across organisations by bringing it into one location to perform analysis and insights
- DWs that are built to support data streaming allow data to be analysed in real time, allowing decision makers to proactively address challenges, identify opportunities, and increase efficiency
Data Lake
Definition
A Data Lake (DL) is a centralised, highly flexible storage repository that stores large amounts of structured and unstructured data in their raw, original, and unformatted form. DLs are flexible, durable, and cost effective, while enabling organisations to gain advanced insights from unstructured data without fixed limitations.
DLs function on an ELT (Extract, Load, Transform) framework. DLs are often used to consolidate data in a single, centralised location, where it can be saved “as is” without the need to impose a schema. Data in all stages of the refinement process can be stored in the DL. Raw data can be ingested and stored alongside the organisation’s structured and unstructured data, as well as intermediate data tables generated during the data refinement process. This allows data scientists an unrefined view of the data residing in the DLs.
Under the Hood
DLs consist of multiple tiers. These are mainly the ingestion tier, distillation tier, the processing tier, insights tier, and the unified operations tier. The lower tiers represent data that is mostly at rest while the upper tiers show real-time transactional data.
The ingestion tier represents the connection between the DL and the data sources. Raw data could be loaded into the DL in batches or in real time. Data is organised into a logical folder structure and is not modified at this layer.
Following the ingestion layer is the distillation tier. This tier takes data from the storage ingestion tier and converts it into structured data for easier analysis. Structured data is stored in files and tables and data is made uniform in terms of encoding, data type, formatting after de-normalisation, cleanliness, and derivation.
The processing tier runs analytical algorithms and user queries with varying real time and interactions. Requests can be run in batches or real time.
The insights tier represents the research side where insights from the DL is used. This tier serves as the query interface where SQL, NoSQL queries, or even Excel could be used for data analysis. The interactivity at this tier allows for the display of data for data scientists.
Underlying all four tiers is the unified operations tier where system management and monitoring is handled. This includes auditing, proficiency management, data management, workflow management, and data governance. This tier serves to monitor and manage the system using workflow management, proficient management, and auditing to streamline processes and data flow among the different tiers.
Benefits
- DLs store both structured and unstructured data, eliminating the need to store both data formats in different environments
- DLs can serve as the central repository to store all types of organisational data
- DLs allow data to be stored in any format or medium without the need for a predefined schema
- Data could be stored in its native format, allowing for greater flexibility for analysis and could be formatted for future use cases
- Datasets can be accessed by users at a faster rate compared to DWs
- Transformation and cleaning is done after the datasets are loaded into the DL
- DLs are less expensive than traditional DWs
- Datasets in DLs are often in compressed formats (avro/parquet/orc), taking up less storage space
- DLs are designed to be stored on low-cost commodity hardware (HDFS storage)
- Data is stored in an open and raw format
- Easier to apply various ML algorithms to process the data and produce meaningful insights
Disadvantages
- Due to the unstructured nature of DLs, they can be disorganised
- Makes it hard to connect some data formats with business intelligence and analytics tools
- Additional pipelines are required to transform datasets for use in analytics
- There could be a lack of data consistency in DLs, making it difficult to enforce data reliability and security
- It could be challenging to implement proper data security & governance policies to cater to more sensitive data types due to the varying levels of consistency among datasets
- DLs run the risk of turning into Data Swamps as its ability to store raw data provides a high level of flexibility
- Foregoing all data governance and management principles can lead organisations to hoard massive amounts of data that may likely never be used
When to use a Data Lake
- DLs are a cost-effective and scalable solution to store and analyse large amounts of data
- Various types of data can be stored in DLs such as structured, semi-structured, and unstructured
- Data can be stored in DLs in their native format
- DLs enable vast data streams to be stored “as is” and be accessed on demand
- The DL architecture ensures that it can store massive amounts of historical data efficiently and safely
- DLs provide a platform where data scientists can access and exploit raw data for experimental analysis
- Data scientists can query data easily at the insights tier without relocating to other data sources
- Since DLs function on the ETL framework, data is stored with relative ease and can be easily accessed if a need arises
- If formatted data is corrupted, scientists can revert back to the native data format
- DL architectures ensure that massive data streams can be collected and stored efficiently and safely
- Organisations can achieve a single repo for all types of data and support for future data analysis
- DLs can be used as staging zones for DWs
- DLs can contain vast data formats that are not easily stored in DWs or are frequently accessed and queried by data scientists
- DLs can be separated from DWs via data virtualisation layers and accessed by federated queries
- Data Lakehouse is often used to represent the combination between DWs and DLs
Data Lakehouse
Definition
A Data Lakehouse is a storage architecture that combines both a DW and a DL. Data Lakehouses enable a single repository for all data types (structured, semi-structured, and unstructured) while enabling machine learning, BI, and streaming capabilities.
Data Lakehouses typically start as a data lake consisting of all data types. An additional layer is introduced to enable categorisation and classification of the data, by generating metadata to provide structure. This layer also enables ACID transactional processes onto the data within the DL, providing users a strong guarantee to the reliability and integrity of the data within.
Some possible lakehouse formats you can find is Apache Hudi, or Delta Lake, which provides this layer of abstraction in parquet file format. Like DLs, data is stored in a central repository in Data Lakehouses. However, just like a DW, data in Lakehouses are organised and optimised for querying.
Under the Hood
Similar to DLs and DWs, Data Lakehouses consist of different tiers. Data is first ingested from various sources such as DBs, applications, IoT devices, or logs. This data can be structured, semi-structured, or unstructured. The ingested data is then stored in a central repository in its raw format- without and pre-processing or formatting. Typical storage layers include HDFS or S3. Once data is ingested and stored, the data is catalogued and indexed at a separate data cataloguing layer. This makes it easier to discover, understand, and query various datasets. At this semantic layer, metadata is created for each dataset, including data lineage, data quality, and data access controls.
Following the cataloguing layer, data is then transformed into a format that is optimised for querying. The transformed data can be stored in a data warehouse layer. Formatted data can be queried using various tools such as Apache Hive, AWS Athena, or SQL. Data can also be analysed using tools such as Apache Spark, AWS SageMaker, or Jupyter Notebooks. Data analysts and data scientists can perform these queries and analysis to gain insights from the datasets. ML can also be utilised.
Data governance policies are also enforced in Data Lakehouses to ensure that the data is compliant and secure. Data access controls, data lineage, and data quality rules can be enforced to maintain the integrity of the data.
Benefits
- Data Lakehouses provide a single all-purpose data storage platform that can cater to all business data requirements
- Data Lakehouse makes use of the cost effective storage features of data lakes by utilising the low cost object storage options
- This eliminates the cost and time required to maintain multiple data storage systems
- Data Lakehouse supports the use of various tools for both queries and analysis such as SQL, Jupyter Notebooks, Apache Spark and Hive, and AWS Athena and SageMaker
- Use of open data formats such as parquet is also supported. Data can be analysed using APIs and ML libraries, along with Python/R
- Data Lakehouses ensures that schema is enforced, allowing for data integrity. This makes it easier to implement robust data security and governance within a single layer on the platform
Disadvantages
- Data Lakehouses can be complex to set up and manage
- Specialised engineers are needed to maintain and optimise the Lakehouses
- Cloud-native managed solutions are preferable due to the relative ease of setting up and maintaining
- Data is stored in its native format in Data Lakehouses, potentially increasing the complexity of data governance to account for all data formats
- Unstructured and semi-structured data run the risk of difficulty in ensuring data quality
- Data Lakehouses run the risk of becoming data silos if not managed properly
- Data silos can occur if multiple teams are managing Data Lakehouses deployed in multi-tenant environments
- This can lead to deduplication and fragmentation of data
- There may be limited tooling and support available compared to traditional DWs and DLs
When to use a Data Lakehouse
- Data Lakehouses are good for projects that need to store and analyse large amounts of data of varying formats
- Lakehouses can handle structured, semi-structured, and unstructured data while providing a unified view for analytics and ML
- Data Lakehouses are able to handle real time data ingestion and processing, enabling businesses to analyse and form policies based on the latest data
- Data Lakehouses are suitable for data scientists who need to handle large amounts of complex data
- Data scientists can perform exploratory data analysis and build ML models without worrying about data ingestion or data quality. Datasets of varying levels of quality can be used since native datasets are ingested and transformed at different tiers of the Data Lakehouse
- Data Lakehouses can handle real time data ingestion and processing, enabling teams to analyse and act on data as it is incoming
Conclusion
In this section, we present three solutions to hosting a data repository – Data Warehouse, Data Lake, and Data Lakehouse. We started by giving a brief comparison of each solution against each other. We followed by touching on the architectures, benefits, and disadvantages of each solution.
Data Warehouses are certainly suitable for structured data and analytical workloads, but the use cases they perform well on may make it unsuitable for some DE projects that requires more flexible datasets. Data Lakes are suitable for semi-structured or unstructured data and machine learning workloads. Data Lakehouses are suitable for both structured and unstructured datasets, enabling both analytical and machine learning workloads. However, with the higher complexity in terms of validating and governing large bodies of semi- or un-structured data, users should expect higher governance cost and engineering efforts when using Data Lake or Data Lakehouse.
The choice on which data repository solution to use is dependent on the use case of the DE Initiative. When selecting the data repository solution, engineers will need to make considerations based on a few general factors: the type of data stored, the effort required to both build and maintain the repository, and the likely overheads derived from governance and other factors. While some solutions may seem like optimal candidates for many projects, the costs borne towards the data repository may outweigh the benefits reaped.
