Knowledge Hub

The Unified Star Schema (USS) is an advanced dimensional modelling approach that enhances traditional star schemas by introducing a central structure known as the Puppini Bridge. This bridge aligns facts and dimensions into a single, consistent analytical framework, making queries more reliable, intuitive, and resistant to common join errors.

The primary goal of USS is to provide a unified query entry point that eliminates ambiguity when working with multiple fact tables. It simplifies drill-across analysis and ensures business users receive consistent results without needing deep SQL expertise.

In a traditional dimensional warehouse, each business process, such as Loan Approvals, Repayments, or Customer Interactions, typically has its own fact table. While dimensions may be conformed across these stars, combining multiple fact tables in a single query can lead to common modelling pitfalls:

Yes, you are right, you might have not heard, or implemented HOOKS before. This is a new term introduced by Andrew Foad.

HOOK is a technique to Data Warehousing especially proper to Data Lakehouse and virtualized architectures, but having said that it can be applied on any SQL based database. It is a hybrid methodology that borrows features from other methodologies such as Data Vault and Kimball dimensional modelling.

Whereas Data Vault and Kimball methodologies have multiple table types (Hubs, Links, Satellites, Dimension, Facts, etc.), HOOKS has a single structure called a Bag, which is used to wrap existing data warehouse table and exposes Business Keys which align to Business Concepts define in the Enterprise Data Model (EDM).

Bags align with data ingested into the data warehouse from the operational system (source-aligned) and Business Concepts defined in the Enterprise Data Model (business-aligned).

Metadata Modelling is the discipline of designing structured frameworks that describe data about data. It defines how business, technical, and operational descriptors are captured, organized, and related so that data assets can be understood, discovered, traced, and governed consistently across the enterprise.

The primary goal of metadata modelling is to establish a structured and standardized approach to cataloguing and managing metadata. By doing so, organizations can improve compliance, strengthen data governance, enhance data quality, and increase trust in analytical and operational systems.

Metadata modelling focuses on descriptors such as:

• Business definitions
• Data ownership and stewardship
• Lineage (upstream and downstream data flow)
• Data source systems
• Refresh frequency
• Sensitivity classification
• Quality rules and thresholds
• Storage location and format

Unlike ontologies, which emphasize semantic meaning and relationships between business concepts, metadata modelling emphasizes the management attributes of data assets. It does not primarily answer “What does a Loan mean?” but rather “Where does the Loan data come from?”, “Who owns it?”, “How often is it refreshed?”, and “Is it sensitive?”

Security & Access Control Modelling is the practice of defining structured rules and frameworks that determine who can access what data, under which conditions, and in what manner. It formalizes data protection through roles, permissions, entitlements, and policies aligned with organizational security requirements and regulatory obligations.

The primary goal of this modelling approach is to ensure that sensitive information is accessible only to authorized users while still enabling legitimate business access. It balances protection and productivity by translating business security rules into enforceable system controls.

At its core, Security & Access Control Modelling defines entities such as:

• Users (individual identities)
• Roles (grouped responsibilities or job functions)
• Permissions (allowed actions, such as read, write, approve)
• Policies (conditional rules governing access)

Typically, users are assigned to roles, roles carry permissions, and policies define constraints such as time of access, geographic location, or device type.

AI / Machine Learning Feature Modelling is the discipline of designing, structuring, and governing features, the measurable variables used as inputs to machine learning models. It defines how features are created, catalogued, versioned, and reused so that model inputs remain consistent, explainable, and trustworthy across the enterprise.

The primary goal of Feature Modelling is to provide a standardized and governed framework for engineering and managing features. Instead of allowing every data scientist to independently create slightly different versions of the same metric, Feature Modelling ensures that features are defined once, documented clearly, and reused consistently across multiple AI use cases.

In practice, raw transactional or external data is rarely fed directly into machine learning algorithms. It is first transformed into structured features such as:

• Customer credit score
• Average repayment delay
• Loan utilization ratio
• Officer approval rate
• Customer income stability index

As the name says itself, these are traps. If we have Chasm or Fan Traps, then we have a serious problem as these can impact the whole data model.

‘Chasm and Fan Trap lead to Cartesian Data.’

Fan Trap: In Fan Trap, the model ends up having ‘One to Many’ relationship in more than two tables e.g., One Customer having Many Orders and One Order having many Order Products which is logically fine but if physical data model is created like 1) Customer with Order ‘One to Many’ 2) Order with Order Product also ‘One to Many’ then it’s not fine. It’s a Fan Trap.

• Issue: In this scenario, Order Id (Pkey) has been added in Order Products tables as Fkey which means there will be Low Cardinality in Order products tables means there will be duplication of Product in Product’s table which is a wrong approach. In the Products table, Product ID should be unique.
• Solution: One Product can have Many Orders but not the other way. The correct approach is to have Product ID (Pkey) from Order Products table as Fkey in Orders table.

Entropy term is normally used in Data Science domain.

• Where finding unexpected outcomes is the aim
• Where surprises are welcomed
• Where results are analyzed based on probability ratio, the lower the probability the better it is.
• Where informative information is something which wasn’t known

Types

• High Entropy means, more surprises, more unexpected values are considered more informative.
• Low Entropy means, less surprises, less unexpected values are considered less informative.

‘Entropy is also called as Extreme Disorder of values.’

Referring to the image, we can see at the starting point all the signs are MINUS, then in the middle there are 50/50 signs of PLUS & MINUS and right at the end all the signs are PLUS. At those extremes left, middle and right corners, the Entropy is at the lowest, so no surprises are expected so nothing much for Data Scientists to predict right. But in 2^nd, 4^th, and 5^th circles, it’s difficult to say how many PLUSES(s) and MINUS(s), if values are not visible, at this points Entropy is at the highest.

Epistemology, a term rooted in the Greek word episteme, meaning “knowledge,” and logos, meaning “discourse” or “communication,” explores the concept of “justified knowledge.” It addresses questions like “What do we know?”, “How can we claim to know something?”, “What validates our beliefs?”, and “How do we ascertain the certainty of our knowledge?”

In the realm of data, these questions take on a new dimension, especially in the Big Data era, where we store vast amounts of raw data, structured, semi-structured, and unstructured. Data science teams apply machine learning or deep learning models to extract insights, turning raw data into knowledge. However, to ensure that these insights align with their origins, a “justification of knowledge” is essential, ensuring that what we extract truly reflects what was expected. This process emphasizes evidence-based validation, where qualitative research relies on the quality of design and data collected.

CDC and Real-Time, a couple of terms which people are confused with and assumes both are same but in the real world, both are different topics.

CDC: Full abbreviation of CDC is Change Data Capture, meaning the moment, the data is changed in database ONLY THE CHANGED DATA gets picked up by the CDC process and pushed to next layer. Changed data can be like 1) a new row can be added or 2) an existing row can be updated or 3) a previously added row can be deleted or dropped.

This whole process can be Real-Time but mostly can't as Real-Time + CDC needs a lot of memory processing which comes with heavy TCO, so it all depends on ROI for the related Business Case.

There are Four CDC methods:

1. Keep the previous data set in table1, get a new data set in table2. Compare both and extract rows that are deleted or are new or have changed since last time. This is the slowest method and requires the heaviest processing either on the source (not recommended) or on target system.
2. Source Tables have date columns that can identify the latest inserted or updated records. Using these columns, CDC data can be extracted easily. This is the easy approach, but it requires the Source Data Model to have date fields in each table which if not requires extensive development effort both at the backend database and frontend Application. It has a processing impact on the source system as well.
3. This method is mostly used where Transaction Logs are read. Transaction Log is generated when an insert/ update/ delete operation takes place on a table. In this method, the log is read instead of hitting the source tables so no impact on the source database but minimal impact on I/O [recommended and mostly used approach].
4. This is the most recent method and very few solutions are using it. In this method, the data is read at the application level, in other words when the Application writes in Database, at the same time it pushes the operation for CDC processing. No impact on source database or I/O.

ESP (Event-Stream Processing) is a term used along with CDC and Real-Time processing.

As we touched based on Real-Time processing in another topic, ESP is exactly what Real-Time is all about. In other words, the moment any activity has happened in source system or in source application which can be online portal, social media or a mobile etc., so the moment any activity takes place, the latest action is pushed to next stage. Next stage can be a Data Lake or Data Warehouse, or it can be intermediately solution in in-memory to prepare a response to send directly back to customer’s mobile.

‘Even-Streaming Processing is heavily used if a reaction is planned in Real-Time.’

For example, a customer buys a product from a shop in a mall, the moment he or she use their credit card for payment, along with OTP code another message comes to their mobile, telling them there is a sales or promotion going on in this mall, please have a look before making this payment. This is called Real-Time Even-Streaming Processing.