Data Integration and Data Transformation

Data Integration and Data Transformation

This lecture closes out the data profiling work from earlier sessions, then moves into the two big themes of the day: data integration (bringing data from many sources into one place) and data transformation (reshaping raw data so models and reports can actually use it). Along the way the material covers data engineering, the patterns people use to organize transformation work, drift detection metrics, and a leadership story about honesty that ties back to data leakage.

Roadmap for this lecture:

• 9.1–9.4 — Data quality first. Profiling, the data quality dimensions, outlier detection, bias and fairness, and data leakage. This part answers the question: how do you know the data is worth feeding to a model?
• 9.5–9.6 — Why models decay. Data drift, model drift, concept drift, and the metrics used to detect them (the Population Stability Index and KL divergence).
• 9.7–9.8 — Profiling's role and the honesty story. Where data profiling sits in ML data readiness, plus the king's final test — a story that maps directly onto data quality and leakage.
• 9.9–9.12 — The engineering core. Data integration, data engineering, data transformation, and the layered patterns (medallion architecture and the DBT pattern) used to organize transformation work.
• Appendices — Exam guidance summary and key industry applications.

One theme runs through the entire lecture: in machine learning, most of the effort goes into the data, not the model. The same data set can be sliced many ways, and the quality of those slices — how complete, how consistent, how unbiased, how leak-free — decides whether the model built on top can be trusted at all.

9.1 Data Profiling and Data Quality Dimensions

9.1.1 Profiling: The CV of Your Data

Data profiling means systematically examining a data set — a customer table, a product list, a transaction log, or any kind of data — before it goes into a pipeline. The core analogy from the session is your CV. When you apply to a company, your profile talks about everything about you: leadership, team spread, technical skills, project management skills, process. The same holds for data. A person has multiple dimensions — a singer, a trust runner, a professor, a consultant, a trainer, an SME, a father, a son — and depending on what we are trying to do, we need to bring in more about the specific dimension that matters. Data is exactly the same: different situations need different slices of the same data set.

Why does profiling matter so much? The one mantra: garbage in, garbage out. People spend roughly 80% of their effort only on data — collecting it, cleaning it, validating it, understanding it — so data profiling and data validation are very, very important. This applies whether you build a data pipeline, a data warehouse, or a lakehouse: every layer downstream inherits whatever quality (or garbage) entered upstream.

Real-world context: organizations everywhere are adopting generative AI tools for data pipelines and for many other tools; everything is being automated with Gen AI. Banking teams were being trained on Gen AI adoption for data pipelines while the session was running — a sign that profiling and pipeline-building are being actively automated across the industry.

9.1.2 The Data Quality Dimensions

Before you profile any data, you check it across several quality dimensions — a quality dimension is nothing but a measurement of an attribute, a variable, or a feature; we assess it, interpret it, and improve it. The dimensions read out in the session were:

Timeliness, uniqueness, traceability, reliability, validity, completeness, accessibility, integrity, accuracy, and consistency.

Each dimension answers a specific question about the data:

Earlier in the course, this same checklist was applied to healthcare data — checking for duplicates, completeness, and issues — which shows the dimensions are a reusable checklist, not a one-off exercise.

9.1.3 Extended Dimensions: Informative, Covering, Real, Unbiased

Beyond the core list there are extended dimensions. Data should be informative — it should carry the correct information. It should have coverage — covering everything, all the details; does it cover the world as the model needs it? It should be real inputs — really reflecting the real inputs or real customer data, not a synthetic substitute. It should be accurate. And one of the very, very important quality dimensions, especially when dealing with AI, is unbiased — the data should be unbiased.

Labels also matter. Good data has consistent labels; inconsistency in labeling is very important when you talk about ETL process extraction — that is why we do proper labeling. The same idea applies to a person's profile: you give your profile under the right category, the right label. Suppose you have a certification — the certification goes into the certification category. You do not want to mix that certification somewhere into the experience section. If you talk about your leadership, leadership goes into leadership traits.

9.1.4 Enough Data and Generalization

Is the data big enough? Because we talk about big data and volume, good data must be big enough to allow generalization — the ability of a model learned from this data to behave correctly on data it has never seen. Generalization is a sampling question: the data set is a sample of the real world, and we need a good sample.

Recall from earlier in the course the model-fit picture: too little data, or data with weak signal, leads to underfitting — the model is too simple to capture the pattern, so it does poorly even on the training data. The important warning from the session: sometimes nothing can be done to increase the accuracy of the model. No matter how much data you throw at the learning algorithm, if the information contained in the data has low predictive power, accuracy will not improve — that is where the underfitting problem comes into the picture. More data cannot manufacture signal that is not there.

9.1.5 The Common Problems With Data

Each of these is something to watch when building a pipeline. The session framed them as a checklist of questions you ask about the data: what is the cost of generating the data, do we have any quality problems, is there any noise, is the data capable of only low predictive power, does it contain outliers, are there any leakages, is the data outdated?

• Noise — random fluctuations that hide the true signal.
• Low predictive power — the features simply do not carry enough information about the target (Section 9.1.4).
• Outliers — points that do not belong to the big bucket where most data sits (Section 9.2).
• Bias — systematic distortions in how the data was collected or labeled (Section 9.3).
• Leakage — information that should not have been available leaked into the data (Section 9.4).
• Outdated data — the world moved on; the data describes a population that no longer matches production (Section 9.5).

9.2 Outlier Detection

9.2.1 Outliers Can Be Good or Bad

An outlier — also called an anomaly — is data that does not belong to the big bucket where most of the data belongs. Most points cluster in one region; outliers sit far outside that region. The key message from the session: outliers are good as well as bad.

The professor's example: imagine you normally make transactions in one city. All of a sudden there is a transaction in London, then one in Amsterdam. Relative to your usual pattern, those transactions are outliers. But that outlier may produce a different revenue stream and a different marketing strategy for companies: the bank spots the travel pattern, gives you offers, and you start using that card more often. Use an international card and an Amazon card in Amsterdam, Belgium, and Germany — everywhere it is being used — and the next time, the company can increase your credit score, increase your credit card limit, and give you a better offer. The "outlier" behavior becomes a growth signal.

So the same unusual point can be a good outlier (a new revenue stream, a new behavior worth rewarding) or a bad outlier (fraud, a sensor failure, a data-entry error). Detection is the same; interpretation is not.

9.2.2 Box-plot Based Detection

A simple detection technique: box plot based outlier detection. The idea: find where most of the data lives, then treat whatever lives far outside as an outlier candidate. That is why analysts build a lot of charts — the five-point summary, histograms, distributions. We find out where most of the data belongs; if there is some data that does not belong to the one big bucket, we may put that under the outlier category.

The five-point summary names five landmarks of a data set: the minimum, the first quartile \(Q_1\) (the 25th percentile), the median \(Q_2\) (the 50th percentile), the third quartile \(Q_3\) (the 75th percentile), and the maximum. The box plot draws a box from \(Q_1\) to \(Q_3\), a line at the median, and whiskers toward the extremes.

The standard rule for flagging outliers with a box plot uses the interquartile range (IQR), the distance between the quartiles:

\[ \mathrm{IQR} = Q_3 - Q_1 \]

Any value below the lower fence or above the upper fence is an outlier candidate:

\[ \text{lower fence} = Q_1 - 1.5 \times \mathrm{IQR}, \qquad \text{upper fence} = Q_3 + 1.5 \times \mathrm{IQR} \]

9.2.3 Proximity-Based Outlier Detection

Proximity-based outlier detection is a very famous technique, and it is very simple in spirit: we try to find the distance between the data points. Points that sit close to many other points are normal; points that sit far from everything are outliers.

Step 1 — build the distance matrix. A distance matrix holds the distance between every data point and every other point. For \(m\) points, the matrix is \(m \times m\): entry \((i, j)\) holds the distance between point \(i\) and point \(j\). There are many methods to find the distance — Euclidean distance, Manhattan distance, and lots and lots of other techniques. Simple math: find the distance.

The Euclidean distance between two points \(\mathbf{x} \in \mathbb{R}^n\) and \(\mathbf{y} \in \mathbb{R}^n\), where \(x_i\) and \(y_i\) are the \(i\)-th coordinates and \(n\) is the number of dimensions, is the length of the straight line joining them:

\[ d(\mathbf{x}, \mathbf{y}) = \sqrt{\sum_{i=1}^{n} (x_i - y_i)^2} \]

This is the standard definition of Euclidean distance — the session named the technique, and the formula is the textbook one. It is a direct generalization of Pythagoras: in two dimensions it is exactly \(\sqrt{(x_1 - y_1)^2 + (x_2 - y_2)^2}\).

The Manhattan distance uses absolute differences instead of squared ones, so it measures distance along a grid — like walking city blocks instead of flying:

\[ d(\mathbf{x}, \mathbf{y}) = \sum_{i=1}^{n} |x_i - y_i| \]

Again, this is the standard definition. The name "Manhattan" comes from the street grid of Manhattan: you can only move north-south or east-west, never diagonally. For two-dimensional points, Euclidean distance is the "as the crow flies" length; Manhattan distance is the "taxi cab" length.

A second mini-example shows the same idea in two dimensions: point A = (1, 2) and point B = (4, 6). The coordinate differences are \(4 - 1 = 3\) and \(6 - 2 = 4\). Euclidean: \(\sqrt{3^2 + 4^2} = \sqrt{9 + 16} = \sqrt{25} = 5\). Manhattan: \(|3| + |4| = 7\). The two distances differ (5 vs 7) because the crow flies straight while the taxi must travel along the grid. In high-dimensional data, the choice of distance changes which points count as "neighbors," so the metric is part of the outlier definition.

9.2.4 Density-Based Outlier Detection

Density-based outlier detection is another very famous technique. We use a set of data points, and there is a formula available for calculating the density of the data points — some points live in a high-density region (many points around them), some in a low-density region (few points around them). Based on that density, we detect the outlier: a point sitting in a sparse region while its neighbors sit in dense regions is an outlier.

The session did not state the formula, so here is the standard density-based construction (the basis of the Local Outlier Factor, LOF, method):

For a point \(p\), let \(N_k(p)\) be the set of its \(k\) nearest neighbors, and let \(\text{reach-dist}_k(p, o)\) be the "reachability distance" from \(p\) to a neighbor \(o\) — at least the \(k\)-distance of \(o\), so that points in dense regions do not get unreasonably small distances:

\[ \text{reach-dist}_k(p, o) = \max\left( k\text{-distance}(o), \; d(p, o) \right) \]

The local reachability density of \(p\) is the inverse of the average reachability distance to its neighbors:

\[ \text{lrd}_k(p) = \left( \frac{1}{|N_k(p)|} \sum_{o \in N_k(p)} \text{reach-dist}_k(p, o) \right)^{-1} \]

A high lrd means the point's neighborhood is tight (dense region); a low lrd means the point is isolated. The Local Outlier Factor compares a point's density with its neighbors' densities:

\[ \text{LOF}_k(p) = \frac{\frac{1}{|N_k(p)|} \sum_{o \in N_k(p)} \text{lrd}_k(o)}{\text{lrd}_k(p)} \]

• \(\text{LOF}_k(p) \approx 1\) — the point's density matches its neighbors: normal.
• \(\text{LOF}_k(p) > 1\) — the point is sparser than its neighbors: outlier candidate.

Why does density matter when proximity already works? Because proximity alone misreads clusters of different tightness. Imagine a dense village cluster and a loose countryside cluster. A point at the edge of the village is close to its village neighbors in absolute distance, but its neighborhood is still much sparser than the village core — density-based scoring catches that; a plain nearest-neighbor distance can miss it.

9.2.5 What to Do With Outliers

Outliers are good as well as bad, so we cannot blindly delete them. The session gave the two standard options:

1. Exclude the outliers from the training data — for the bad outliers: measurement errors, data-entry mistakes, broken sensors, fraud noise. Removing them keeps the model from learning garbage.
2. Use machine learning algorithms and models that are robust to outliers — instead of removing data, choose methods that are not dragged around by extreme points. Median-based statistics and robust models resist the pull of a few wild values, which matters when you cannot safely delete the points (for example, when the outlier might be the fraud you are trying to detect).

9.3 Bias and Fairness

9.3.1 Bias and Fairness Matter

Bias and fairness are very, very important: your data should be unbiased. There can be many reasons for bias — sampling bias (the sample does not represent the population), instrument bias (the measuring device skews the readings) — a number of reasons; many biases are available. The lesson is not to memorize a list, but to understand that bias enters data at every stage: at collection, at selection, at labeling, and at reporting.

Bias here means a systematic distortion — not random noise. Random noise averages out across many examples; bias pushes the data consistently in one direction, so the model learns the distortion as if it were truth. A model trained on only one region's data does not just have "some error" — it has learned that the world looks like that one region.

9.3.2 The Types of Bias

The types of bias read out in the session:

Omitted variable bias, sponsorship or funding bias, prejudice or stereotype bias, systematic value distortion, experimenter bias, labeling bias, reporting bias, automation bias, selection bias, group attribution bias, implicit bias, and confirmation bias.

Not all biases may be applicable to every data set — the list is a checklist, and the skill is recognizing which ones threaten your particular data. Here is one compact picture of what each one does:

9.3.3 What Each Bias Looks Like

• Labeling bias: people like to label data in a certain way; because of that they may lose the credibility of the data. If the labeling guidelines nudge workers toward one label, the whole label distribution shifts.
• Selection bias: when people select some data, they may select only the data from one particular region — for example, only data from Mumbai — and then the data is biased. The model learns Mumbai's patterns and calls them universal.
• Group attribution bias: when people do attribution by group, there could be a bias — judging a person by their group's average instead of their own record.
• Reporting bias: the data does not report what it should — instead of megahertz it gives gigahertz; instead of a kilometer it gives miles. While reporting, automation could also lead to calculation problems — automated systems may create errors, so the error rates matter.
• Selection from the population: how you select the data in terms of a population — what is important, what is not important — based on your experience. Sometimes that may lead to a problem; your personal sense of importance silently shapes the data set.
• Implicit bias: a preference you hold without noticing. One person likes a technique and always uses it; another calls only a few people. If someone always calls the people who immediately respond and starts talking to them, that is a default behavior depending on the situation — the quick responders get over-represented and the slow responders disappear from the picture.
• Confirmation bias: some biases are based on facts and evidences — we believe in something and then we confirm it. The belief came first; the evidence hunt follows.

9.3.4 Fairness and Responsible AI

Data should be fair, and AI and ML — deep learning — tools should work with fairness. When we talk about any data, any data pipeline, machine learning, or deep learning, we are talking about responsible AI: AI should be responsible. Decisions should be fair — suppose a parent has two children, a son and a daughter: the parent should be unbiased and treat them as a pair, making fair decisions. Data should have fairness. AI should be human design; data should be secure, reliable, and compliant.

Responsible AI also means antagonistic thinking: challenge the assumption. Why was somebody biased with something? Why are you using a particular tool, or a particular cloud, or something else? People may go with some assumptions — you have to challenge the assumption, identify the weakness. That is where the human in the loop gets involved. Fairness is very, very important — every AI and ML solution should be fair enough, and this is especially important for critical AI — the systems where a wrong or unfair decision has real consequences for people's lives, health, or money.

9.3.5 Explainable AI and Responsible Evaluation

AI should be explainable — you should be able to say why a decision was made. You can use explainable AI packages like SHAP and LIME, or a few other packages, to make sure the AI gives fairness. SHAP (SHapley Additive exPlanations) and LIME (Local Interpretable Model-agnostic Explanations) both explain individual predictions: they show which features pushed the prediction up and which pushed it down. When the output should be fair, then the data profile feeding it should be fair too — an explainable model over biased data just makes the bias visible; it does not remove it.

Evaluating a machine learning model responsibly requires doing more than just calculating a loss metric. It is not enough to find the predicted value and the actual value and say: "90 minus 50 — oh my God, I have this much of loss." Fairness must be checked too — maybe the loss comes from the data collection, maybe from the data itself. So we need to identify the bias. The loss number says how wrong the model is; it never says why — and if the why is a biased sample or a biased label, the loss hides it.

9.3.6 Handling Problematic Feature Values

For missing feature values we use the binning technique — we normalize and put the values into a proper bucket. We also ask: what are the unexpected feature values — when an unexpected feature value comes, how are we going to deal with it? And when the data is skewed — lower tile or higher tile — how do we deal with it? We need to ensure the data is not under-representing or over-representing relative to the real population.

Binning (also called discretization) is grouping continuous values into a small number of buckets, so that missing values and extremes can be handled at the bucket level and the model sees stable categories instead of raw, noisy numbers.

9.4 Data Leakage

9.4.1 What Data Leakage Is

Data leakage — also called target leakage — is often due to the data pipeline. In supervised learning, data leakage is the unintentional introduction of information: something is introduced that should not have been made available, but it was available. Because of that, the data is contaminated. This question came from a student in an earlier session — "what is this data leakage?" — because some data could cause contamination and create different results.

The key phrase is unintentional introduction. In supervised learning the model learns to map features \(x\) to a target \(y\). Leakage happens when information that would only be known at prediction time — or information derived from \(y\) itself — is present in the features during training. The model then memorizes the answer instead of learning the pattern.

9.4.2 Why Leakage Is Dangerous

Training on contaminated data leads to overly optimistic expectations about the model performance. The model looks better in training and validation than it will ever be in production — and nobody discovers the leak until it is too late.

Why "too late"? Because every reported accuracy number was measured on data the model had secretly seen. The inflated numbers look like success, so the model ships; the production traffic arrives without the leaked information; and performance falls to the level the honest features actually support. The model cannot replicate its validation performance on genuinely new data — the information it was using is simply not there anymore.

9.4.3 The Causes of Leakage

The typical causes named in the session:

• The target being a function of a feature — you send some data to some mathematical function, some algorithm, and the function is not properly done, so you have leakage. The feature contains the target's value because some computation that should not have touched the target did.
• Feature hiding the target — a feature that silently encodes the answer (like "chargeback_processed" above, or a "total_amount_refunded" column in a churn model).
• Feature coming from the feature — downstream or derived values creeping back upstream, so the feature is built from information that only exists after the event the model is trying to predict.

Classic real-world leak paths that follow these causes:

This is exactly where the conversation moves into data skew and data drift (Section 9.5): a model that quietly saw forbidden information, or that was trained on a world that has since changed, both produce the same symptom — the production world no longer matches what the model learned.

9.5 Data Drift, Model Drift, and Concept Drift

9.5.1 What Does Drift Mean

The session opened this topic with a direct question-and-answer on what drift means:

A drift happens when something moves away from its benchmark. For data, the benchmark is the distribution the model was trained on: the way the data was distributed then has changed with the course of time. For a model, the benchmark is the weight computation that worked: the weights are computed for some time, and suddenly the weight computation is completely off. And the two are connected — model drift is often caused by data drift, because the model's weights were learned from a distribution that no longer exists.

9.5.2 Drift After Deployment

Once you build a model and deploy it into production, the model usually performs well for some time — a certain model quality, everything looks fine. Often the cause of an error is explainable by the fairness or fitness of the training data: traditional training samples probably solidify the model. However, in many practical scenarios, the model starts to make errors. The training data is a frozen snapshot; the production world keeps moving.

Real-world example from the session: Amazon uses a model for its recommendation system to predict whether a user will like things on the website. Over time, the preferences may start to change — perhaps due to aging: one user liked one actor's films for some time, now likes different actors; preferences change. If the model keeps returning the same type of results, the results may mislead — the user has moved on, the recommendations have not.

One of the biggest differences in the data set: no Kaggle dataset is static — but in reality, production data is changing; that is data dynamics. Kaggle competitions give you a fixed snapshot to train on, but production data streams in continuously. Drift happens because of the dynamics of people and the dynamics of data generation.

Speed of decay: data can decay. Past performance is no guarantee of future results — model decay is a natural occurrence in ML models. The speed of decay can vary greatly: sometimes it takes years to get adjusted, sometimes a few days. Some models drift over decades (demographic models), others over days (ad-click models reacting to a viral trend).

9.5.3 Schema Skew and Distribution Skew

Data drift means a skew grows between the training data and the serving data: the production data that the model receives for scoring has diverged from the data set that was used earlier for training, tuning, and evaluating the model — so it may not replicate the data pattern. The discrepancy between the training data and the serving data can be classified as:

• Schema skew — very important. The production product schema or customer schema, and whatever related tables, may have inconsistent labels and inconsistent feature types. The column that held "customer_id" as an integer now holds strings; a feature that was a date became a timestamp with a different time zone; a column was renamed and the pipeline did not catch it. The structure itself no longer matches what the model expects.
• Distribution skew — the data distribution may not be correctly sampled; there are new trends and patterns. The columns look the same, but the values have shifted: the age distribution tilted younger, the product mix changed, a new segment appeared that was never in training.

9.5.4 Concept Drift

Concept drift is about the interpretation of the relationship changing. In data drift, the inputs change; in concept drift, the meaning of the relationship between inputs and outputs changes — the same input value now deserves a different answer.

Think of a movie-watching pattern: you normally go and search movies, and you like horror movies or some fiction movie — you select it. All of a sudden you may change your mind: you don't like science anymore, you don't like fiction anymore, you want some humor, some comedy, some fun — then the pattern has changed. If the service keeps giving you a list of fiction movies, you get irritated. The features (movies watched) did not change structure, and the user did not change how the service works — the concept "what this user wants" changed.

So the interpretation may change; people tend to change things. You need to take care of the data skewing between training and actual production data — the model may behave differently, the concepts may change. That is why we cannot depend on a static data pipeline or static machine learning code: we need some dynamism — that is why we talk about agility.

9.5.5 Agility

Agile practices: Scrum meetings, sprints, and backlogs — the instructor attended all the Scrum calls while working at Cisco for seven years. Agility is the practical answer to drift: if the world changes continuously, the pipeline and the model must be changeable continuously.

There is a YouTube talk on agile software project management that discusses the power of agile — a recommended watch, mentioned in the session as an extra resource shared with the class.

9.6 Data Drift Detection Metrics

9.6.1 Population Stability Index (PSI)

There are a lot of algorithms for drift monitoring: supervised monitoring (when you can compare predictions against actual labels) and unsupervised monitoring (when actuals are not available, so you compare distributions instead). When we have population samples, we ask: between the times and dates, do we have stability in the data? That is what PSI measures.

Interpretation: with a training data set we did everything; then we come into a new data set for production, real-time, or testing. What kind of distribution changes? A high PSI score indicates a significant change in the population distribution; a low PSI score indicates stability — the model is stable when detecting data drift, ensuring consistency.

How PSI works, step by step: take the feature (or score) you want to monitor, cut its range into \(n\) bins (e.g., deciles of the training distribution), and compute the share of records that fall into each bin in the training data (the expected share) and the share that falls into the same bin in the production data (the observed share). For each bin, compare the two shares; then sum the comparisons across all bins.

The PSI formula, with \(\mathrm{observed}_i\) being the observed share (fraction) of the population in bin \(i\) of the new data, \(\mathrm{expected}_i\) the expected share in bin \(i\) of the training data, summed over \(n\) bins, and \(\ln\) the natural logarithm:

\[ \mathrm{PSI} = \sum_{i=1}^{n} \left( \mathrm{observed}_i - \mathrm{expected}_i \right) \times \ln\left( \frac{\mathrm{observed}_i}{\mathrm{expected}_i} \right) \]

Why does this formula make sense? Each bin contributes two factors:

• The difference \((\mathrm{observed}_i - \mathrm{expected}_i)\) measures how much the share moved.
• The log ratio \(\ln(\mathrm{observed}_i / \mathrm{expected}_i)\) measures how surprising the move is in relative terms: a shift from 2% to 4% (ratio 2) is a bigger deal than a shift from 40% to 42% (ratio 1.05), and the log captures that.

A bin where observed equals expected contributes \(0 \times \ln(1) = 0\). Only bins where the shares actually moved contribute — so the sum is zero exactly when the two distributions are identical, and it grows as they separate. You can use a Python program to calculate this — find the score, low or high, and interpret the drift from it.

Real-world: banks use PSI to monitor the stability of their credit and risk populations between training and production windows — the same index the instructor was teaching to banking professionals in Malaysia.

9.6.2 KL Divergence

The other metric is KL divergence — the Kullback–Leibler divergence, also called relative entropy.

Entropy means the degree of disorder — uncertainty in the data. So we look at it like: I use a normal distribution, now I am using a binomial distribution — was there any relevance, and is there a divergence, what sort of divergence between the distributions? We can use that as a drift metric as well. KL divergence asks: how many extra bits of information do you need to describe the data if you use the reference distribution instead of the true one? The larger that extra cost, the more the two distributions differ.

The KL divergence between a distribution \(P\) (the observed distribution) and a reference distribution \(Q\) (the expected/training distribution), over all values \(x\) that the variable can take, with \(P(x)\) and \(Q(x)\) the probabilities each distribution assigns to \(x\):

\[ D_{\mathrm{KL}}(P \parallel Q) = \sum_{x} P(x) \ln\left( \frac{P(x)}{Q(x)} \right) \]

This is the standard definition of KL divergence — the session gave the definition in words, and the formula is the textbook one. Notice the structure: every term \(P(x) \ln(P(x)/Q(x))\) is the probability under the observed distribution times the log ratio of the two probabilities. When \(P(x) = Q(x)\) for all \(x\), every log ratio is 0 and the divergence is 0. When the distributions differ, each mismatched value adds a positive amount.

9.7 ML Data Readiness and the Role of Data Profiling

9.7.1 Data Profiling as a Process

ML data readiness is where data profiling comes in: data profiling is the process of analyzing the data set systematically to evaluate its quality. That is where all the data quality dimensions come into the picture — completeness, reliability, accuracy, integrity, and so on — plus the structure of the data and the content: what type of content it is carrying.

The process does three jobs:

1. Provides insights into the metadata — the data about the data: column names, types, ranges, formats, lineage (where each value came from). Metadata is what makes a data set discoverable and auditable.
2. Evaluates the data integrity — the references hold together, the keys are unique, the values obey their rules.
3. Supports data governance — profiling produces the evidence base that governance policies (who can see what, what quality is guaranteed, how data is documented) are built on.

Most organizations are focusing on data profiling — it is the cheapest place to catch problems before they become pipeline failures, model errors, or compliance incidents.

9.7.2 Single-Field vs Multi-Field Profiling

Data profiling is single-field or multi-field. The two modes answer different questions:

• Single-field profiling examines one column at a time. For a single field — like age or sale — we look at the summary statistics of the data (mean, median, min, max, percentiles), the data type, the data value, and the distribution (histogram shape, skew, cardinality, missing-value rate).
• Multi-field profiling examines how columns relate. For multiple fields, we look at the dependencies and keys: how a score or sale value is related to other values — we can visualize the relationship (scatter plots, correlation, foreign-key checks between tables).

9.7.3 Data Profiling on the Comprehensive Exam

9.8 The Leadership Story: Honesty and Continuous Effort

9.8.1 The King and His Final Test

A king — a very nice guy, friendly, and as a leader of the kingdom he needed to be a good person for everyone: no bias, fairness, integrity for the country. He wanted to decide who would be the next king for the kingdom. He announced to the entire kingdom a set of competitions — muscle wrestling, bow and arrow, elephant riding, and more — to select the power of the young man or young girl who would be the next king or queen.

With all the tests, only three people got selected. The three stood the best: they were good in astronomy, good in the Veda, good in martial arts, good in the history of the country, good in the geography of the country, everything. After three months of struggle, the three came to the palace. The king was confused — he thought only one person would come, but three came. How do I select the next king? The first two guys said: "Maharaj, no worries — I can rule for one year, he can rule for the second year, we alternate." The third person was very quiet: "I am okay with whatever you are saying."

The king discussed with the minister and got an idea. This is the last — but not least — final test. He ordered big jute sacks — the kind used to carry rice and wheat. Three big sacks, one for each candidate. "Take this, collect as much as possible food items. I promise that I won't check what is inside. The country has a problem — scarcity, no rain. Collect as much as possible; whoever collects more, I may decide and give the role as king. Go — but don't mix yourselves." The three were sent into different, different directions, and the king said: "I don't monitor you also."

9.8.2 The Three Contestants

Contestant one — the decorator. "What the hell — I have been doing this three months, six months of competition, and now the King is giving this work?" For a couple of days he did good work — collecting some peanuts, some bananas, trying to find something — then he became so tired. The bag was almost 25% filled. He thought: "We have time — one week he gave. The last day is there, we will find out something; next Sunday we will do it." So he took rest and enjoyed his time off. On the last day he went and looked at dead grass and dead leaves, and filled all of it in the bottom — up to 100%. On top of it he put the vegetables and fruits he had collected — bananas, peanuts — so the bag looked full: uneatable dead grass below, and 25% of genuine vegetables and a little bit of fruits on top.

Contestant two — the fixer. "Oh my god, this is the ridiculous thing. Let me try something later at the end." He was a wealthy fellow — a big, big guy with a chain. "Worst case, I can use the money to buy — because the King said he is not going to check, no monitoring, no sensors, nothing." For the first five days he collected nothing and took rest. In the last two days he gave his chain to a shop and bought rice — he filled the bag 50%, and all his money was gone. "Oh my god, what should I do? Only 50% of the bag is full." Then his crooked mind started thinking: he went around all the villages and streets where people throw damaged fruits, damaged vegetables, contaminated fruits; he took them, cleaned them, and dumped them into the bag. Now good items and bad items got mixed up.

Contestant three — the steady collector. He started collecting every day something — some peanuts, some bananas, some grapes, whatever he was able to find out. He went around, took rest, collected; took rest, collected. He could not fill the bag — it was about 30% — but he did his best.

9.8.3 The Jail Test

Now the three went to the palace, each carrying a heavy bag, each saying "mine is bigger." The king welcomed them: "Very good, you have done your final job." Then he ordered the soldiers — the security guard — to put the three people in separate jail chambers. No water, no food — that's it.

The first guy, who collected 25% plus decoration, survived for some days — three, four days. He ate all the vegetables, then he started taking the leaves; he started feeling thirsty; he was finding it hard to find food — and he died.

The second guy — the good food and bad food got mixed up. He got diarrhea, bacterial infections from all the food items, because it was all mixed up. He washed it, but damage being damaged — data leakage already created — so he started giving him leakages. He died in seven days.

Only the third person survived: he collected only a little items, he was able to manage, eat slowly — he got some banana — and he survived on the eighth day. Next week they opened the chamber and announced the third person — the one who collected the little — was finally the king.

9.8.4 The Data Lesson Inside the Story

Success is not doing one thing, one time, great. Success is doing it continuously. Honesty is the best policy, especially for good leaders. There is no shortcut to get success.

The story maps onto data quality — each contestant is a data strategy:

9.9 Data Integration

9.9.1 Why Data Must Be Integrated

The opening question of the new topic: why does data have to be integrated?

Three reasons stand out in the answer:

1. Data is fragmented. One source holds orders, another holds customers, another holds events — and each source is only a partial picture. Integrating gives the correct context.
2. Decisions need the full picture. Each source says something; the joined data is what makes a decision informative rather than lopsided.
3. Metadata is separate. The schema, the lineage, and the meaning of fields often live outside the data itself — integration is also about connecting data to its metadata.

9.9.2 What Data Integration Is

Data integration combines various types and formats of data from many sources across an organization into a data lake or a data warehouse to provide a unified fact base for analytics. The main purpose of data integration is to give a better insight and a good unified fact. Each source says something; finally we need to make a better decision, align the departments to work together, and serve the customer better.

It means consolidating the data from multiple sources into a single set for consistent business analytics — and at the end of the day you may use it for data engineering, data analytics, or business intelligence. To move the data from one place to another, we need to understand the structure and meaning of the data — you cannot integrate what you do not understand. That is why profiling (Section 9.1) and integration go hand in hand: profiling supplies the understanding of structure and meaning that integration needs.

9.9.3 Data Ingestion vs Data Integration

Data ingestion is where data is moved from one source into another system — normally taking from one source and putting into another system. But data ingestion could involve extended activities: data cleansing, data sorting, data enrichment, beautifying the data, data massaging, data scrubbing, web scraping, and other processes to make the data ready for use at its final destination. Sometimes this happens before the data is stored — that is why we call it ETL or ELT. Sources can be an OLTP database, a SaaS API, or AWS S3 buckets and containers. There is a data ingestion layer and a data integration layer.

Disparate means the data is not properly uniform — one source may use sensors, another may use something different — it is not uniform; it may be diversified, stratified sources. So we apply transformation, we do some normalization, we manage it — for example, a customer table with a customer ID as an integer column — we transform it and make some kind of formulas. That is why we use a lot of ETL tools.

9.9.4 The Bronze, Silver, Gold Layers

Integration work is organized into layers — bronze, silver, gold — and gold is what we are really targeting.

Your bronze consists of multiple things: we just ingest the data and put it there — the client table, the apps table, static data. In the silver layer we integrate everything: more normalized, more aggregated, a good clean table. Then the gold layer is the aggregated, normalized, clean result. (There is an Oracle product called Oracle GoldenGate — the joke in the session was that maybe that is why it is called the Golden Gate.)

The layers are a ladder of increasing quality and structure:

9.9.5 Tools for Data Integration

For data integration there are many ETL tools: DataStage, Informatica, Oracle Warehouse Builder, ODI — a lot of tools are available. For data ingestion you don't need a tool — you just take the data and put it there in the target. The distinction from Section 9.9.3 shows up in the tooling: integration is complex enough to warrant dedicated ETL platforms; plain movement is not.

9.9.6 Challenges of Modern Data Integration

The challenges with modern data integration: the explosion of data and data structures, combined with radical innovation and the demand for continuous, real-time data — that is why we need streaming. And with DevOps and agile practices being followed, we need to make sure we meet the increasing demand for always-on, self-service data.

Four pressures in one sentence: more data, more varied data, faster data (real-time, streaming), and more demanding consumers (always-on, self-service). Integration is no longer a quarterly batch job — it is a continuously running, continuously monitored system.

9.9.7 The Three Waves of BI

This world is moving towards third wave BI. Real-world: use your Fitbit app or any mobile banking app. Olden days — if you wanted a report, you had to go and beg the bank; two weeks, even two years ago, if you wanted to know something there was no way to get a PDF. Now they show charts — what is the spending, what is the amount in hand — and give you a self-customizable portal. That means data should be available to the customer: that is third wave BI.

• First wave BI: only the database administrator can do something — pull the data, run some SQL queries, create one big report: what was the amount summary five years ago.
• Second wave BI: you go to the bank, there is a portfolio administrator or bank administrator; you put a request, they have ready-made queries, they may go and pull the data from some other system and give it to you.
• Third wave BI: you have everything — you configure whatever you want, you customize whatever you want.

That is why data transformation and data integration play a vital role in the modern world: the third wave runs on integrated, transformed, reliable data.

9.10 Data Engineering

9.10.1 Science vs Engineering

A question the instructor always asks when teaching any analytics or engineering subject: what is the difference between science and engineering?

The instructor's own perspective: science gives the facts, the rules, the findings, information about a lot of things, how something should work — like Ohm's law, \(V = IR\), pressure, biology, geology — all the formulas, inventions, laws, and facts. Engineering puts that into an action: makes it a product, makes it a solution. Engineering provides a structured, disciplined methodology — a well-defined framework; we follow a framework, a structure, a method, a step-by-step procedure.

9.10.2 What Data Engineering Is

Data engineering means engineering the data. Doctors deal with the anatomy of a human — a doctor knows your entire anatomy just by checking your pulse when he touches your hand. Similarly, a data engineer — or a data scientist, with little variations between the two — applies an engineering way of dealing with the data.

That is data engineering: using data integration by technical users to develop, manage, and optimize data pipelines, mostly for analytical use cases, in a systematic way — delivering modern data management architecture, self-service data integration, operational data integration, and supporting AI projects.

9.10.3 The Core Data Engineering Steps

The core data engineering steps:

Data generation, data ingestion, data storage, data processing, data pre-processing, data transformation, data management, data governance, data analytics, data visualization, data monitoring.

Everywhere we follow a systematic approach, until the data is monitored and governed. The loop starts where data is born (generation) and only ends where data is watched (monitoring) — with governance over the whole lifecycle. The data integration tools support extraction, ingestion, and delivery — bulk data, batch data — and simple data transformation: sync to text, text to number, number to text — doing some encoding, data encoding.

9.10.4 What Integration Tools Must Provide

The tool is supposed to provide data engineering, take care of any data architecture, take care of self-service data integration, take care of operational data integration, and of course support AI — any data integration tool may be SaaS-based or cloud-based, deployed on premises, public or private cloud.

An EDA code — an exploratory data analysis script shared in the session — gives you a lot about data engineering on the data: what features are we expecting, what is augmentation — how we expand this further — how we are managing the metadata, and data governance. Data engineering provides all the sophisticated techniques for data operations:

• Pipeline design — what sort of pipeline we are going to build, how it is going to be integrated.
• Automated testing — how we are automatically testing the data.
• Orchestration — scheduling and coordinating pipeline steps.
• Continuous integration, continuous delivery — the CI/CD pipeline that ships pipeline changes safely.
• FinOps — the allocation of resources: what type of costs are involved for every data generation — you can attach the cost operations to the data operations.

And there are AI-focused features: any kind of text and NLP, vector databases, any kind of LLM or RAG — for sentiment analysis or bulk context — plus images, device information, and IoT data, where we may use machine learning packages like computer vision and neural networks, depending on the nature of the data you are trying to process.

9.11 Data Transformation

9.11.1 What Data Transformation Is

The flow: you have a source, you can ingest; go to a stage; we do a transformation; we transform the data and move that into a destination. Data transformation is the process of converting raw, unstructured, inconsistent data into a clean, usable format for future analytics, reporting, and machine learning. It ensures the data is standardized, accurate, and aligned with your business keys — the identifiers your business actually uses to refer to entities (customer IDs, product codes, order numbers).

The source → stage → transform → destination picture is the pipeline skeleton: data enters, gets reshaped in the middle, and leaves in a form downstream systems can consume.

9.11.2 The Seven Types of Transformation

The session opened this topic with two questions that pinpoint where transformation starts:

Where do these map to? The seven types of transformation:

1. Data cleaning — removing duplicates, handling missing values, correcting typos, and validating formats. The first pass over the data: every row and field checked for the basic failures.
2. Data standardization — converting units of measurement, aligning date-time formats, and organizing naming conventions. Example: I have DDMMYY; I change it to year, month, date, because US customers look for month and date.
3. Data summarization (mostly aggregation) — summarizing granular data to high-level insights: quarter one, quarter two, quarter three; weekly sales, monthly sales — we call it the aggregated value; we sum it up.
4. Data enrichment — enhancing the data set by adding new information: derived features, extended features.
5. Data integration — combining data from multiple sources into a unified view: I get all the customer data from local, internal, external sources and combine it for a unified view.
6. Data normalization — structuring the data to minimize redundancy and improve integrity.
7. Data filtering — narrowing down the data set based on conditions or rules; in Excel we do a filtering.

Units conversions: kilometers to miles, kilograms to pounds, liters to gallons. Each conversion is a standardization rule with a fixed factor: 1 mile ≈ 1.609 kilometers, 1 kilogram ≈ 2.205 pounds, 1 gallon ≈ 3.785 liters. So 5 km becomes 5 / 1.609 ≈ 3.1 miles; 10 kg becomes 10 × 2.205 = 22.05 lb; 4 liters becomes 4 / 3.785 ≈ 1.06 gallons. The session also mentioned a spoken phrase that was hard to make out — "learning deformer" — which in the context of the seven types is best read as a format transformation (reformatting): changing how a value is represented (text ↔ number ↔ date layout) rather than what it means, which is precisely what standardization does for date-time and numeric formats.

9.11.3 The Transformation Process Loop

The data transformation process consists of:

1. Data discovery — discover the data, where the data is coming from.
2. Data mapping rules — how we are going to map from field one to field two, source one to target.
3. Transformation logic — what sort of transformation logic we are going to do.
4. Execute the process — run the transformation.
5. Validation and testing — check the results.
6. Documentation and monitoring — record what was done and watch it.
7. Repeat — the process starts again for the next cycle.

9.11.4 Data Discovery

Before any transformation begins, the team must understand the data they are working on: understand the data, look at the data profiling, look at the data quality dimensions — what the data is, where the data comes from, what the dimensions of the data are, what the data is all about.

Discovery covers the structure, types, volumes, and quality of the data — the quality dimensions from Section 9.1. Discovery helps identify inconsistency, anomalies, and areas where transformation is necessary. We also gather metadata: where the data has come from — key to automation and lineage tracking, and to detecting any contamination. Discovery is the profiling step inside the transformation loop: you cannot map or transform data you have not examined.

9.11.5 Data Mapping and Surrogate Keys

Data mapping is where we define the source-to-target mapping rules: how one field corresponds to another field — CUS ID to customer underscore ID or CID. Choose the right values so they end up in the right place across different schemas — for example, customer first name, how you are mapping it. Clear mapping is crucial for schema alignment when we integrate multiple sources, because the naming convention is very, very important. Every field needs a rule: which source field feeds which target field, and what conversion happens on the way.

A surrogate key is a system-generated sequence.

The surrogate key will correspond to all the other things: you define some rules, and only when everything is done — this done, this done, this done — a unique transaction ID is generated. If I have four values, four dimensions, all combined, then I generate a fact, then I create one sequence value — a screen ID, or a surrogate key — or simply a customer ID.

9.11.6 Transformation Logic Design and Execution

In transformation logic design we decide what type of logic we are going to do: which one we are trying to convert; what type of regular-expression-based logic; how we split a big name into first name, last name, and middle initial — what type of parsing. We can use SQL transformation — NVL, substring, aggregation. All those logics are defined in the transformation — ETL or ELT. We can write the transformation rules using Python or SQL; normally we do it in stored procedures — not triggers, mostly stored procedures. We execute that: once the transformation logic is defined, we execute it as a batch job or a streaming job.

In the traditional ETL pipeline the data is extracted, transformed, and loaded into the destination system — transformation happens before the loading. In ELT, transformation happens after the loading — the raw data lands in the target storage first, and transformation runs inside the target environment (which is why the DBT pattern in Section 9.12 is an ELT pattern). Execution engines can be built in-house or managed through a toolkit — there are tools like DBT and Apache Beam, the standard names for the transformation frameworks the session pointed to.

9.11.7 Industry War Stories

The instructor's approach from 12–15 years ago: before building a new product or consolidating products, build a big repository — an enterprise data information store — a very big metadata model, a very big inventory that serves as a knowledge base, so you don't miss anything. Then create the rules, derive the rules: "I want to integrate everything, I want to create a new business line." You can use the Lambda approach, or materialized views, or write views, or create stored procedures, or use some AI tools, or run Python programs — it just goes and pulls the data and tries to create a new workflow; you can create a new database or data tables on the fly, and your application will be built on the fly. Once everything succeeds, you spin from this one and create a new product. That's how the world is moving.

And depending on the nature of the data: if the knowledge is content-driven, context-driven, or involves a lot of sentiments, you can start building a large language model on top — an ML pipeline on top of it. If it is more images, device information, or IoT data, you may use machine learning packages like computer vision or neural networks. Stored procedures may not work — but AI will work: AI works with text, audio, video, and image data.

9.11.8 Validation and Testing

Finally we need validation and testing. We discover the data, we build the mapping, we did the transformation, we define the rules, the rules are executed through some tools — DBT, Apache Beam, Oracle, or your own tools — and then it is the duty of validation and testing to make sure the data is properly transformed and validated.

Teams run a lot of tests:

• Regression tests — check that new transformation logic did not break what previously worked.
• Integrity tests — check that references and keys still hold after transformation.
• System tests — check the whole pipeline end to end.

The point is to confirm all the data is properly transformed: data quality checks, schema validation, row counts, making sure data integrity is maintained. That is very important — a transformation that silently drops 10% of the rows has "succeeded" by the code's logic and failed by the data's reality. Row counts before and after, plus schema validation, catch that class of failure.

9.11.9 Documentation, Monitoring, and the Repeat Loop

Finally we start documenting the whole process, and then monitor, monitor, monitor. Documentation records the mapping rules and logic so the next team (or the next run) can reproduce the work; monitoring watches the executed jobs so a silently failing transformation gets caught, not ignored.

The instructor illustrated the loop with a time-loop joke — a famous Tamil movie dialogue where one person came to a meeting, was shot, became dead, and then it repeats — and the Doctor Strange example, where he keeps fighting the evil Dormammu for years together. The idea: whenever you think of this data transformation process, remember this — discover the data, map the data, build the rule, execute the rule, validate and test, document and monitor, and repeat the process.

9.12 Data Transformation Patterns

9.12.1 Why Patterns Matter

There are a lot of patterns out there — design patterns, architecture patterns. We use a pattern to repeat some process, to simplify some process. Software architecture is worth studying: computer science is common sense — think and relate. In olden days, to develop software and mobile applications, we had patterns:

• MVC — model, view, controller: most mobile applications follow this pattern. There is a model, there is a view, there is a controller. For example, playing a game: the game model is there — a database; who is a player, what is the score, everything is modeled. The view is what I am playing — I am playing with a bike, I am playing with a car. And the controller has all the power.
• Publisher–subscriber: another pattern — producers publish events; consumers subscribe; the two never need to know each other directly.
• Pipe and filter: another pattern — data flows through a chain of processing stages, each one transforming the stream.
• Layered architecture style: an architecture style — systems organized into stacked layers, each with a defined role.
• Broker architecture style: an architecture style — a central broker mediates communication between components.
• Adapter pattern: when I want to send some data to another system, we need an adapter. Suppose I am sending an Excel file, but that other system cannot accept the Excel file — then I need a CSV file; use the adapter. This side may be something, the other side may be something else.

Patterns provide benefits that simplify the process — repeatability, structure, and shared vocabulary — and similarly, there are patterns available for data transformation: that is what we are going to study.

9.12.2 Medallion Architecture

Medallion architecture is a data design pattern used to logically organize data in a lakehouse, with the goal of incrementally and progressively improving the structure and quality of the data as it flows through each layer of the architecture — from bronze to silver to gold.

It is basically a component-based architecture: instead of taking everything into the target BI system, we have multiple levels, multiple layers. Each layer has one job, and quality improves step by step as data moves through.

• Bronze — the landing zone for raw data. No schema needed. We ingest the raw data and put it there; you can use any technology for raw integration and ingestion, and you can use AI to stage everything. Nothing is filtered at the door — the raw data is preserved, which means nothing is ever lost.
• Silver — filtered and cleaned. Correct documents, cleaned documents. Augmented means extra: you augment the data, add some more — beautification — for better visualization. Data should be visualizable and complete; sometimes raw data doesn't provide everything. When there are standards — for example, HIPAA standards in healthcare — if the data is not falling into that, the duty of the silver layer is to ensure that all the extra information is fitted in. Here we enforce a schema and evolve the schema as needed. Filtered only means we took only the data we need — like a data mart — not all the raw data from bronze; we take a segment of the data, and we can build multiple systems.
• Gold — the deliverable. Aggregated, normalized, clean tables. Here you can build your CI/CD pipeline — continuous integration, continuous update — and from there you give it to your BI system, to a new system, maybe a .NET system, new products, new mobile applications, BI reports — Crystal Reports, Power BI, Tableau — or you run your machine learning to perform some pattern. Deliver continuously updated, clean data to downstream users.

The Oracle GoldenGate naming joke from the session: the gold layer — "that's why Oracle called this a Golden Gate, maybe." That led to the ColdFusion story: a product from the past that took HTML, CSS, and dynamic HTML, and with the click of a button generated nice pages — people fused things and came up with names. And the etymology: Oracle comes from the Sanskrit word Akashwani — a voice from above, from the divine — there is a story of a Rishi; it can carry a lot of data, because something comes from above with so much in it. (Two takeaway names to remember: Oracle GoldenGate is the data replication product, and the gold layer is the medallion deliverable.)

9.12.3 The DBT (ELT) Pattern

The DBT pattern is another layered data engineering pattern — again a type of medallion, layered architecture. Here we call the layers: staging, then an intermediate, then a data model (mart).

The DBT tool is available and it does ELT — extract, load, transform: during the transformation, we stage it, then we put it into the intermediate — which is like a silver data mart; we build multiple data marts. Staging is prior to the data warehouse. The flow: from landing we go to staging, then from intermediate we can go to the mart.

• Staging layer — we have all the column-level transformations: type conversions, renaming, cleaning at the column level.
• Intermediate layer — the aggregations, all the views and tables.
• Data mart — the final layer, useful for your packs and dimensions and further reports.

You can use medallion architecture and the ELT DBT pattern together — combine them like a hybrid model:

9.12.4 What to Check in Validation and Testing

For validation and testing we can validate manually, we can build our own data quality checks — what can go wrong with the data — and perform testing on whether the transformation logic worked. Why do we want to test? We want to make sure:

• data is converted properly;
• strings are manipulated properly;
• date and time is transformed properly — is the reverse time zone transferred properly;
• data is aggregated properly;
• data is normalized properly;
• data is standardized properly;
• data is filtered correctly;
• data enrichment is applied — lookup;
• how we handle the null value;
• how the data is being split;
• how the data is merged;
• error and outlier handling.

We check how the attribute is constructed, how the data is aggregated, how the data is discretized — that's where the binning technique comes into the picture — how we are splitting the data into multiple groups, how the data is generalized, how data is integrated, how data is normalized. Normalization examples: social security number, or the mobile number, or especially the patient ID, hospital ID, or clinical ID — that's why sometimes we use curated codes and data smoothing, and we remove the outliers. The test list is a checklist of everything that can go wrong in transformation — and every item on the seven-types list from Section 9.11 has a corresponding "was it done right?" check here.

9.12.5 Best Practices for Data Transformation

In detail:

1. Document every rule. Maintain a clear transformation mapping sheet linking source fields, transformation logic, and target fields. Always have a checklist: what is the source field; what is the logic — we are converting from this to this, maybe we are increasing some value or decreasing some value; what is going to be the target field.
2. Automate validation. Use SQL scripts, Python tests, or ETL tool validations for repetitive checks — repetitive tests — and test with a realistic volume; don't just do the normal data, test with realistic volume. A transformation that passes on a 100-row sample can collapse on a 100-million-row run.
3. Version control. Keep everything properly — Git, SCCS, RCS, VCS, PVCS, ClearCase — use any version control system, so that every model, every version is checked in. When a transformation changes behavior, version control tells you what changed, who changed it, and how to go back.
4. Test the edge cases. Include the missing data — don't underestimate — test outliers, extreme values. The boundary value analysis below is the systematic way to do this.

9.12.6 Boundary Value Analysis

In software testing there is a very nice technique called boundary value analysis (BVA) testing, and equivalence partitioning testing. The instructor was a tester and validation engineer at Honeywell Aerospace in 2010–2011, where boundary value testing was very, very important.

The idea behind BVA: bugs live at the edges. A condition like "age between 25 and 65" fails most often exactly at 25, 26, 64, 65, 24, and 66 — not at 40. Equivalence partitioning is the companion idea: group all inputs that the logic treats the same way into one "equivalence class," and test one representative from each class instead of every possible value.

Real-world: boundary value testing was central to validation work at Honeywell Aerospace — aviation-grade systems cannot afford an off-by-one at the boundary of a safety condition.

9.12.7 What Comes Next

The next session will show a data transformation code — the instructor will generate some source data and run a data transformation code. There is also an insurance company exercise to prepare: what type of transformation would we do, and what could the data be? Think about it with the seven types of Section 9.11 in hand: cleaning (dirty claim fields), standardization (dates, units), enrichment (adding derived features), filtering (claims meeting criteria), aggregation (summaries by policy).

Exam Guidance Summary

Everything below was stated explicitly in the session. Use this list as the final revision checklist before the comprehensive exam.

• Data profiling question (stated explicitly as examinable): expect a question related to data profiling — making some decisions in drift, model drift, and data drift, and then the role of data profiling. Prepare the full chain: what profiling is, the quality dimensions, its role in ML data readiness, and how the drift decisions (data, model, concept) connect back to it.
• Drift detection metrics: know the Population Stability Index (PSI) and its formula — observed minus expected times the natural log of observed over expected, summed — and KL divergence (relative entropy). PSI was flagged as "one of the very, very important indices." Be ready to interpret scores (low = stable, high = significant change) and to compute a small example by hand.
• Data transformation patterns: medallion architecture (bronze, silver, gold) and the DBT (ELT) pattern (staging, intermediate, mart) — including the hybrid mapping where bronze = staging, silver = intermediate, gold = data mart.
• Data transformation: the seven types of transformation (cleaning, standardization, summarization, enrichment, integration, normalization, filtering) and the process loop — discover, map, build rule, execute, validate, document, monitor, repeat. Remember the vocabulary correction: duplicates/nulls/typos belong to cleaning; units and date-time alignment belong to standardization.
• Outliers: the three detection families (box plot with IQR fences, proximity with distance matrix and KNN, density-based), the worked numbers 50, 67, 90, 80, 55, 167, and the rule that outliers can be good or bad.
• Leakage and drift concepts: the definition of data leakage (unintentional introduction of information), its three causes, and the difference between data drift, model drift, and concept drift (cholesterol example).
• Next session: a data transformation code demonstration with generated source data, plus an insurance company exercise — think about what type of transformation we would do and what the data could be.
• Pacing guidance: it is acceptable to complete only 50–60% of planned content; depth on important topics beats rushing.

Key Industry Applications

Where the lecture's concepts show up in the real world:

• Population Stability Index — used by banks to monitor population stability between training and production data; the instructor taught it to banking professionals in Malaysia.
• Generative AI adoption — organizations are adopting Gen AI tools for data pipelines and automating many data engineering tasks.
• Amazon and Netflix — recommendation systems that suffer model drift and concept drift as user preferences change in production.
• Mobile banking apps and Fitbit — third wave BI: self-service, customizable analytics available directly to customers.
• Lorenzo — a healthcare integration system built with materialized views, replication logic, and stored procedures to unify pharmacy, lab, and patient management systems.
• Oracle EHR — a modern healthcare transformation using change data capture (CDC), materialized views, and Lambda architecture, with FDA reporting and zero-compromise safety on missing data.
• Insurance adjudication — bronze/silver/gold (medallion) architecture used for certificates of coverage, coverage documents, and CPT codes, with government programs drawing from the gold layer.
• ETL tools — DataStage, Informatica, Oracle Warehouse Builder, ODI.
• Execution engines — DBT and Apache Beam.
• Explainable AI — SHAP and LIME packages for fairness in critical AI.
• BI reporting — Crystal Reports, Power BI, Tableau.
• Y2K — date and year handling at massive scale; one of the biggest challenging projects in banking.
• Honeywell Aerospace — boundary value analysis used in validation engineering.
• Agile at Cisco — Scrum, sprints, and backlogs as the practice behind "agility" for data teams.