Knowledge Hub

The concept of the Data Lakehouse emerged around 2020 as an architectural response to the limitations of both Data Warehouses and Data Lakes. In simple terms:

Data Warehouse + Data Lake = Data Lakehouse.

Rather than being an entirely new invention, the Lakehouse combines the strengths of existing paradigms, the structured, governed nature of the Data Warehouse and the flexible, large-scale storage of the Data Lake, enhanced by modern innovations in table formats and metadata management.

At its core, the Data Lakehouse addresses two long-standing challenges. Traditional Data Lakes allowed organizations to store vast amounts of raw data but lacked strong transactional guarantees, often leading to inconsistent or unreliable datasets. Data Warehouses, by contrast, provided consistency and governance but were limited in their ability to handle semi-structured and unstructured data at scale. The Lakehouse resolves this by offering:

As discussed in the topic i.e., Data Warehouse or Data Mart, Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach.

As per Ralph Kimball, we MUST use Dimensional Modelling for Data Warehousing and Data Marts consisting of Star and Snowflake Schemas.

In Star Schema, the model has a Fact table in the middle and all the Dimensions are joined with the Fact. For example, there is a Fact table called Sales and there can be many dimensions like Time, Location, Product, Categories etc. The Primary Key of the Fact table consists of all the foreign key columns coming from Dimensions. This way when a report or a query is created, there is always one direct join with any Dimension.

As discussed in topic i.e., Data Warehouse or Data Mart that Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach.

As per Ralph Kimball, we MUST use Dimensional Modelling for Data Warehousing and Data Marts consisting of Star and Snowflake Schemas.

In Snowflake Schema, the model also has a Fact table in the middle and Dimensions around it but here Dimensions can be normalized further in multiple Sub-dimensions which changes the query to create a report where there will be multiple joins to reach a conclusive result. For example, there is a Fact table called Sales and a Dimension i.e., Location, this location Dimension can be normalized further in State, City, and Postal Code etc.

As discussed in topic i.e., Data Warehouse or Data Mart that Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach. But what about Galaxy Schema?

You must be wondering that we have been hearing about Facts and Dimensions in both Star and Snowflake schema(s) but where are Conformed Dimensions, so here it is.

Galaxy Schema is also known as Fact Constellation Schema and is more complex than Star and Snowflake schema. It’s further divide Star Schema into small Star Schema(s) where there are more than one Fact Table and have Reusable Dimension to connect multiple Fact Tables.

These dimensions which can connect with multiple Fact tables across multiple Star Schema(s) are known as Conformed Dimensions.

Cubes are a very controversial element of OLAP world where most of the folks think, it not worth having those.

Cubes host data from facts and dimensions in one table so query can be super-fast, in Cubes multi-dimensional data is pre-loaded.

Disadvantages

• These are huge in size.
• Very difficult to refresh in real time.
• Housekeeping is a challenge.

Below is Star or Snowflake schema representation. When data is joined from Fact and Dimension and loaded on one table it is called an OLAP Cube.

There are multiple types of OLAP i.e.  ROLAP, MOLAP, HOLAP, HTAP, WOLAP, DOLAP and SOLAP.

• ROLAP [Relational On-Line Analytical Processing]: It sits directly on top of RDBMS which can be Star/ Snowflake Schema(s) and can be built using standard SQL. Another benefit, ROLAP doesn’t need additional storage as it runs on top of RDBMS.
• MOLAP [Multi-dimensional OLAP]: Cubes are the best example of MOLAP where multi-dimensional data can be loaded in a separate storage but in an extremely fast manner. Using Cubes, users can slice, dice, and drill up/ down, roll up and pivot the data within the same one cube eliminating the need to join with other tables. If a Cube has more than 3 dimensions of data in it, it’s called Hypercube.
• HOLAP [Hybrid]: This is a mixture of ROLAP or MOLAP.
• HTAP [Hybrid Transaction / Analytical Processing]: This is for in-memory data systems that do both online transaction processing (OLTP) and online analytical processing (OLAP).
• WOLAP [Web-enabled]: This refers to the only Web-Based OLAP with no connectivity with on-premises and is utilized through the internet browser.
• DOLAP [Desktop On-Line Analytical Processing]: In this OLAP, users can download a portion of OLAP on their machine for offline working.
• SOLAP [Spatial]: This is to tackle issues in the field of Geo-Business Intelligence.

Data Liberalization and Data Democratization are very confusing terms as both work together and most of the time mixed with each other.

‘Data Liberalization ideally is a prerequisite for Data Democratization.’

The aim for Data Democratization is to remove the fear factor about data. Allow everyone to ask analytical questions. To have data access availability for everyone. Allow everyone to raise questions about data and then suggest resolutions, if possible.

Data Liberalization is about providing data in such a way that it should be reusable by users. It can be in any formal i.e., via API, via BI tools, via DaaS etc.

When we say, ‘Aim is to have data access availability for everyone.’ doesn’t mean show everything, of course with access control. Data Liberalization completes the cycle to make sure whatever user is accessing, is USABLE for them rather a pile of junk data.

Data Driven Organization concept can’t be completed just by Data Democratization, Data Liberalization is also a very crucial component to achieve.

Self-Service and Data Enrichment are mostly confused with each other where data enrichment is just one part of one of the types of self-services.

Self-Service is about empowering business users and decision makers to conduct activities by themselves to shorten the end-to-end delivery time. At the same time, it’s too risky to allow people to go and make changes if they don’t understand data. So, I would say, Data Democratization is a pre-requisite for any type of Self-Service. There are many types, but we will only discuss below.

Types

• Data Self-Service: In this kind of self-service capability, data friendly environment is made available for users, where users are trained on data, users are allowed to question data accuracy and integrity, users are allowed to manipulate and extend the context of data via Data Enrichment process where first-hand data is enriched with second-hand and third-party datasets for better decision making.
• Analytics Self-Service (data citizen): This self-service mostly involves machine learning, data modelling or in other word data science related activities. Data Citizen are major users of this self-service where they run data science or data mining models using low or no code tools. Data Enrichment can also be done using this self-service capability.
• BI Self-Service: As the name explains itself, the users are allowed to connect to any source systems either internal or external, of course based on the access level, and create reports and dashboards on their own. This is majorly to reduce the dependency on IT to create reports for business users. In this era, the paradigm of reports and dashboards creation has moved from IT to Business users.

These terms are the core of the Data Pipeline and are normally confused by each other.

Data Integration is to connect with one or more source systems to pull data whereas Data Blending is to merge all datasets into one viewable and understandable presentation view to answer business KPIs. In other words, Data Integration is the prerequisite process for Data Blending, if you can't get data from source systems, you can't blend it to present management for decision making.

Don't forget either Data Science, Business Intelligence or any kind of Analytics all work toward ONE BIG FAT REALITY, to achieve one or the other Business KPI(s).

Coming back to blending and integration. Don't forget in an organization, we have many systems with different objectives where any ONE kind of data could be residing in different patterns in different systems. For example, a product can be sold via multiple channels like from a shop or an online website or maybe from a mobile application etc. When product data is pulled from all these systems in one Data Warehouse solution to report product selling behavior, it's of utmost importance to gather all product selling transactions from all systems into one system which in our example is a Data Warehouse.

Data Mashup is normally confused with Data Mesh (explained separately), but both are totally different.

Data Mashup comes from the idea of doing self-service on a single source of truth. Dashboard or a Web-Page data point coming from multiple sources in a single view is called Data Mashup. Yes, nowadays, we all do this daily.

We have dashboards with data coming from multiple sources which can be Databases, Data Lakes, social media etc. In the Big Data era, we have graphs, pictures, videos all on one page. At the organization level it’s also called Enterprise Mashup. It is also very useful for Data Analysts to fetch data from different sources, make sense of the data and build meaningful data for business decision-making.

Data discovery involves the collection and evaluation of data from various sources and is often used to understand trends and patterns in the data. But understanding trends and patterns cannot be simply done based on data coming from sources such as.

• Data can have Null values.
• Data can have different types of date formats.
• Data can have spelling mistakes.
• Data can have special characters.
• Data can have different codes for the same product e.g., a Glass code can be GLS in one source system and GS in another. So, it's very important to streamline the data sets before presenting them to end users.
• Data can have duplicates.
• And many more....

Data Discovery is different for different roles i.e., for Data Scientist or Data Analyst or Business End User or for C-Level.

• Data Scientists mostly prefer raw data without any data cleaning, cleansing, and scrubbing for Data Dredging, Snooping, p-hacking, and Fishing [there are separate topics on these terms].
• Data analysts mostly prefer data after it’s at least cleaned and cleansed.
• Business End Users and C-Level expect data to be cleaned, cleansed, scrubbed, wrangled, and munged [there are separate topics on these terms] before it reaches them for decision making. Well, a fair expectation.