Query Paradigms, Storage Architectures, and Data Pipelines

2.1 Data Management: What It Is and Why It Matters

2.1.1 What Data Management Is

The session opens with a short children's story read aloud — a tale about sharing. A little girl named Candace wants everything her brother has: his big bite becomes her bite, his storybooks become her books, and his birthday cake becomes their cake. The lesson of the story: nothing works in isolation.

That idea maps directly onto data management. You can build a good data model and still fail because the machine learning code is poor. You can clean one table and still break the pipeline two steps later. If quality suffers anywhere, it drags down the machine learning program, the deep learning program, or any model you are trying to build. This is the garbage in, garbage out principle from the first session, restated: a model performs better when the data is good quality, the data model is good, the representation is good, and the format and pipeline are good.

There is strong connectivity among data representation, data processing, and data pre-processing: the quality, structure, and reliability of the data directly shape the performance and accuracy of a machine learning model, whether it is a decision tree model, a Bayesian classifier, or anything else. A few ideas get repeated on purpose. Repeating key points is called repeated assertion, and it builds a strong foundation.

2.1.2 Why Machine Learning Needs Data Management

The prerequisites and interrelated subjects for this course — a question someone asked in the previous session — are: basic databases, some data structures, and the query language SQL. Even a management person must know how to manage data and understand data formats (structured data, graph data, unstructured data, semi-structured data) and different data models. Data engineering, big data technology, and cloud computing systems are all connected to this subject.

Machine learning models depend completely on the data systems behind them — a database system, an ERP system, a legacy system — whatever storage you have. Poor data management leads to missing critical values, outdated data, and incorrect predictions; the ML model will not predict properly. Proper data management helps handle big data and different types of data, avoids data silos, and improves business outcomes.

2.1.3 From Raw Data to Wisdom

Data becomes more corrected, more consistent, and more refined as it reaches a certain level of wisdom: raw data → context information → derived knowledge → extreme knowledge with multiple levels. This is the data, information, knowledge, wisdom ladder seen in the previous session. Each rung adds meaning: raw numbers become information when they gain context, information becomes knowledge when patterns are derived from it, and knowledge becomes wisdom when it supports decisions at many levels.

The common abbreviations and terms to know:

Data is everywhere: internal data, confidential data, external data, lots of nulls, failing source connections, broken pipes, different charts, different data models, different storage systems, different data architectures. Data management as a practice covers ingesting, processing, securing, and storing organizational data. There is also a split into primary data — the data that is really critical to the business — and secondary data — supporting data used for strategic decision making and improvement.

Why do we need data management at all? Because data is growing: the cloud has come, and so have IoT, sensors, web ops, edge computing — even a fridge generates data; everything generates data. Big data is growing exponentially, and data systems are all growing too, so you need to know how to manage data effectively. Priorities change: if data is not generated or handled properly, there is a problem. Data is used to construct models, and you need a pipeline that transforms data into a model — which is where the processes ETL and ELT come in. ML system success is hard to measure and the direction of failures is difficult to identify; it depends fully on the underlying data management systems in terms of structure. Data management teaches you to understand the structure, what data will give better performance, and it helps improve the accuracy and reliability of predictions.

2.1.4 Industry Applications

Real-world: the fever detection model (time-series prediction over patient, date, time, and location data) is a daily-life use case of a data pipeline. Real-world: ERP systems, legacy systems, and cloud storage are the actual data systems ML models depend on in companies. Real-world: the exponential growth of big data from cloud, IoT, sensors, web ops, and edge computing — including a fridge generating data — is why organizations now invest in data management.

Where this fits in the field: data management is the foundation discipline under data engineering and machine learning operations. Every data scientist's notebook, every model registry, and every production recommendation system sits on top of the collect-process-store-secure chain described here. If that chain is weak, no amount of clever modeling recovers the lost accuracy.

2.2 Data Quality Check in Practice

2.2.1 The Data Quality Check Exercise

Before moving on, there is a quick data quality check for everyone. This is a preliminary or quick quality check, not a 100% full data quality check. Imagine you have been given a dataset that may arrive as a table, a CSV file, a JSON file, or any other file form, and it has come from multiple sources — source one might be a mobile device, source two an ERP system, source three a sensor. You are going to apply some data mining algorithm, a machine learning algorithm, or some modeling on it.

2.2.2 The Sample Table and Its Issues

A sample table was shown to the class, and the findings reported were:

Notice what this table teaches: a dataset assembled from multiple sources rarely arrives clean. Each row above is a different family of problem — an encoding mismatch, a missing field, an impossible number, a mixed unit, an invalid name. The skill being trained here is spotting each family and knowing which tool fixes it.

2.2.3 How to Resolve the Issues

Taken together, these rows are the classic issues of a dataset assembled from multiple sources: inconsistent encoding, missing values, outliers, mismatched units, and inconsistent identity data. What skills must you have for this? At least some domain knowledge about the data — you should think like a doctor or a scientist and look at the data from the quality aspect — plus knowledge of different data representations and data scaling.

2.2.4 Student Questions and Answers

2.3 Data Formats and the Relational Model

2.3.1 Data Formats

Data comes in many formats, and the format determines how a pipeline must treat it. Structured data is the regular, tabular data — rows and columns like a spreadsheet. Unstructured data is things like text, audio, video, and images. Semi-structured data sits in between — email data, HTML data, XML data.

2.3.2 The Relational Model and Normalization

The relational data model is the classic one — proposed by Edgar Codd in 1970, it organizes data into relations (called tables in SQL). In the relational model, everything is a tuple — and tuples are also called rows. A table is the visual representation of a relation, with rows and columns, and the data in it is unordered: the model makes no promise about which row comes first, so the database is free to store rows in any order that is efficient.

To keep such data healthy we use the normalization process: normalization removes anomalies and removes redundancies — we do not want redundancies, because they create data integrity problems. If the same customer address is stored in ten rows and one of them is corrected, which one is true? Redundancy is where contradictory data is born.

2.3.3 Industry Applications

Real-world: Oracle, Informix, and Sybase are the relational DBMSs you will meet in companies — the instructor has worked with about 20 different databases in industry — and relational databases are still the default for most transactional business systems. Database systems are named based on the data model they follow, which is why the next topic matters: once you can name the model, you can guess the system's behavior, its strengths, and its limits.

2.4 Data Models: Hierarchical, Graph, Document, and Key-Value

2.4.1 Data Models as Skeleton Boxes

The main families: the relational data model, the hierarchical model, the graph model, the document model, and the key-value model. Based on these models, database systems are called by the model they follow — a relational system, a graph system, a document system, and so on. Choosing the model is choosing the box shape, and that choice decides what questions the data can answer easily.

2.4.2 Hierarchical Model

The hierarchical model works with parent, child, sub-child relationships: everything starts with a parent — a root — and then goes to sub-levels, sub-sub-levels, like hierarchical directory structures on a computer. A directory contains folders, folders contain sub-folders, and sub-folders contain files; each node has exactly one parent, and the whole tree hangs from one root. This structure can be represented in XML — nested tags are a natural fit — and some organizations still use a hierarchical model. For fast lookup in such structures you need proper indexing structures: cluster indexes and non-cluster indexes.

CEO
 └── Delivery Director
      └── Project Manager
           └── Team Lead
                └── Engineer

2.4.3 Graph Model

The graph data model is what the world is highly using now — most people are moving into it. A graph consists of nodes and edges, where an edge represents a relationship between nodes: a customer purchases a product; a customer places an order. Instead of using an entity-relationship diagram, you can do a graph. Databases that store data using a graph structure are graph databases, and in a graph database the relationship is the priority — you can have multiple relationships, and each edge can carry its own label ("friend of", "worked at", "purchased").

Real-world: Neo4j, Neptune, and Gephi are graph databases; LinkedIn uses them for the proper connectivity of people, tagging people, and friend-of-friend recommendations; Facebook uses graph structure for social connections — a single graph holding people, places, events, and comments all connected by labeled edges.

2.4.4 Document Model

In the document data model, anything and everything is stored as a document, and documents can be encoded in JSON or XML files. In a traditional database system we call a record a tuple; here we call it a document, and the set of documents is called a collection. The document is self-contained: all the facts about one entity travel together, so fetching one document gets everything in one read.

{
  "title": "Data Management for Machine Learning",
  "author": "Data Management Team",
  "publisher": "Academic Press"
}

Real-world: Amazon DocumentDB, Apache CouchDB, Firebase, and Firestore are document databases. The document model is used for storing unstructured and semi-structured data: emails stored as evidence, logs, transaction logs, purchase logs, and all the event information — this date, this time, this event occurred, this error, this warning, what the source was. Each event is a document, and the collection of events is ready for later inspection.

2.4.5 Key-Value Model

The key-value model stores data as a collection of key-value pairs. Each pair is exactly two parts: a key that names the item and a value that holds the content. Key-value stores are used for caching data and in in-memory databases; JSON objects themselves can be used as key-value pairs.

There is a subtle difference between a document database and a key-value store: in a document database, anything can be stored — you do not have to follow a rigid structure — but in a key-value pair, you must follow the key and the value. The document is a rich, self-describing object that can be queried by its inner fields; the key-value pair is a plain lookup where the value is opaque to the store. If you need to search inside the content, you need a document store; if you only need "give me the value for this key, fast", a key-value store is enough.

2.4.6 SQL and NoSQL: Two Families

In general there are two types of data models: the SQL type model and the NoSQL type model — and you must know both. The SQL family covers relational systems (Oracle, MySQL, PostgreSQL) built around tables and joins; the NoSQL family covers the rest — document, key-value, graph, and column-oriented systems — that trade the fixed table for flexibility and scale.

2.4.7 Industry Applications

Real-world: Neo4j, Neptune, and Gephi power graph use cases at LinkedIn and Facebook. Real-world: Amazon DocumentDB, Apache CouchDB, Firebase, and Firestore handle document workloads such as logs, purchase logs, and event streams. Real-world: MongoDB clusters are increasingly AI-driven — the cluster writes the queries for you from a plain description. Across all of them, the pattern is the same: name the box, and you know what the system is good at.

2.5 Declarative and Imperative Query Languages

2.5.1 Two Ways to Ask for Data

An imperative approach gives a declaration of every step, in order: open the door, go to the car parking, open the car, check if the bag is not empty, take the bag, finally bring the bag from the car to me if the bag is not empty. That is many expensive steps — and you, not the system, are responsible for each one. The declarative approach is a simple statement of the result you want: get all the animals where the family belongs to sharks — one condition, and you get all the values.

SELECT * FROM animals WHERE family = 'Sharks';

You can relate this to prompt engineering: you can write a big story in ChatGPT and generate a one-line prompt that gets you so many things. The one-line prompt declares the outcome you want; the model figures out the steps.

2.5.2 The Declarative Advantage

2.6 Query Optimization and Storage Formats

2.6.1 The Database Optimizer

Instead of leaving the computational load to the user, give it to the database — specifically the optimizer. The database uses an optimizer, and the optimizer understands the data and the access paths. Think of how Google Maps works in deciding your traffic plan — sometimes it even changes your direction, because your brain optimizes your next step: when there is a slow-moving car, you automatically reduce your acceleration and your leg goes to the brake. That is optimization.

In the database, the optimizer decides what access path to choose and what to do. The user never spells out the plan; the optimizer builds one, and it can rebuild a better one when the data changes.

2.6.2 Worked Example: Distributing Fruits into Bags

2.6.3 Execution Plans That Learn

Once you run the task, the database produces an execution plan — the concrete route it chose: which indexes, which scans, which join order. The next day you do the same task, and the plan is remembered — like your brain: you took your bike or car to the office, saw a traffic jam or road construction on the way, changed your access path, changed your route, and the next day you automatically take the changed path. We always update the brain, update the optimizer, update the execution plan. That is very, very important: the optimizer is not a static thing — it learns from what it sees and revises the plan for the next run.

2.6.4 Row vs Column Storage

Data is stored either in row format or column format. Row format stores and retrieves the data row by row; column format stores and retrieves it column by column.

Real-world: Amazon Redshift (a hosted columnar analytics system) and Parquet (the columnar file format used across Hadoop, data lakes, and warehouses) are the two names you will meet everywhere in analytics pipelines. Row-oriented MySQL and PostgreSQL remain the defaults for transactional applications.

2.7 Data Serialization

2.7.1 What Serialization Is

Serialization is the process of converting an object or data structure into a format that can be stored — for example, in a database or a file — and later reconstructed back. In simple terms, it is converting one data form to another, for example from text to binary format.

2.7.2 Why Serialize

Real-world scenario: someone works in a system dealing with local CSV files, but when the data has to go to Amazon, the cloud, or another customer's system, the customer does not want it in CSV format. So you convert it into some other format — maybe a text format, maybe XML, or you store it directly into their own format. That is where you use serialization: the two systems agree on one byte format, and the data travels in that shape instead of each side inventing its own.

2.7.3 Worked Example: Serializing a User Object

'Alice|25|Chennai'

2.7.4 Python and Java Serialization

In Python the same thing is done with the JSON module. A JSON file is a structured format — name: Alice, age: 25, and so on.

import json

user = {"name": "Alice", "age": 25, "city": "Chennai"}
serialized = json.dumps(user)      # serializes: one long string
restored = json.loads(serialized)  # deserializes: back to a dictionary

json.dumps(user) converts the data into a serialized string, printing the entire thing as one string; json.loads deserializes it back. So: dumps serializes, loads deserializes. In Java there is something called a Serializable interface: take the same example — a user class with Alice, 25, Chennai — implement the interface, and convert the object into a string; similarly you can deserialize it.

2.7.5 Formats, Tools, and Streams

The process overall: data is produced, data is consumed, and in between data is transferred — so you serialize the data, transfer it, and deserialize it. You can take any format to any format: text to CSV, binary, and so on. There is a stream of data, especially in sensors and IoT, where a lot of serialization tools are used: JSON files to BSON (the binary format), binary formats, text formats, and human-readable formats. Even Hadoop uses the Parquet format. There are a lot of tools, interfaces, and APIs available — no need to worry about which one: the pattern is the same everywhere, serialize on one side, transfer, deserialize on the other.

2.7.6 Student Questions and Answers

2.8 OLTP and OLAP

2.8.1 OLTP: Real-Time Transactions

When data is processed, there are primarily two types of processing: OLTP and OLAP. OLTP (online transaction processing) is your traditional system: you go to withdraw money, you do a GPay payment, you transfer money to somebody — all done instantly, and every one of them generates a transaction. OLTP systems are built to provide real-time performance and real-time access to people, and they serve millions and millions of users. In a bank, for example, many operators are there taking care of the day-to-day work.

2.8.2 OLAP: Historical Analysis

OLAP (online analytical processing) is when you look back at old data, historical data: for example, you want to know which payments you made to a person above 5,000 or above 6,000. In a bank, when you want a two-year bank statement, you have to give a request — they process it and go to another system. So normally OLTP systems and OLAP systems are separated, and OLAP has very, very minimal users compared to OLTP's millions: historical data, consolidated data, mostly read-only data.

The instructor has built five OLAP systems and three big OLTP systems in the US (Boston, over seven years), including mutual fund databases and portfolio administration applications. The lesson from those builds: the two kinds of systems answer different questions, and each needs its own home.

2.8.3 Worked Example: The OLAP Sales Cube

2.8.4 OLAP Operations: Roll-Up, Drill-Down, Slice, Dice, Pivot

OLAP provides multiple operations and multi-dimensional analysis with multiple views. You can drill down, you can sum up, and you can rotate — rotate the cubes row by column or column by row. You can look at a particular category, say the electronics category, and drill down further. You can roll up, you can join multiple dimensions, and you can do a slice or a dice. A dice is like throwing two dice — with multiple dimensions you combine; a slice is taking one and cutting it further: suppose there is Greater Noida — Noida can be divided into two, Greater Noida and maybe suburban Noida. You can rotate, you can pivot, you can drill down, you can roll up.

You already know this from Excel: in Excel you do a lot of group by, sum up, sum and average, and you want to do a drill down. The same operations exist as operators in Oracle, MySQL, MariaDB, and DB2 — operators for performing pivot operations, roll-up, combining, aggregating, joining, moving averages, growth, and all those things.

2.8.5 Architecture: From OLTP to Data Marts

The current systems are the real-time OLTP systems; the next generation systems are ODS (operational data stores), OLAP systems, and data warehouse systems. The picture, in order:

1. Operational systems — OLTP systems, real-time systems, ERP systems, inventory systems, payment gateways — all give the information.
2. The ETL process (extract, transform, load) takes that information into the warehouse system.
3. The warehouse is split into sub-systems called data marts.
4. In the data marts you do the online analytical processing — the roll-ups, drills, slices, and pivots of the previous part.

For all the reporting, OLAP queries, and data mining we do, we need a warehouse — warehouse systems are the home of OLAP, and they are where the historical, consolidated, read-mostly data lives. In a nutshell, the user numbers are very, very minimal for OLAP: a handful of analysts, exploring the same data that millions of users create every day through OLTP.

2.9 Data as an Asset and as a Liability

2.9.1 Data as an Asset

Real-world: at Netflix, efficient and actionable data-informed perspectives help the company think critically and differently about its business and ultimately improve services. Data is an important asset. For a successful business model, data is a key — you need to transform data into a successful business.

2.9.2 Data as a Liability

How does data become a liability? If it goes into the wrong hands, if it is misused — for example, data being used for generating ads even though you did not consent. Beyond misuse: whoever passes on information is liable for it — if a wrong definition is passed on, boom, it can cause catastrophic failure and create damages. Exposure of unexpected shades of collected data can harm the provider as well as the end users. If medical records are exposed unnecessarily, they may create problems for the hospital: how come this hospital gave this medicine? How come this hospital charges this much money? It creates unnecessary questions.

2.9.3 The Biggest Myth: More Data Is Always Better

The biggest myth: more data, better. The other myth: this stuff is easy. There are challenges. When we collect data, the system should be liable and the sources should be liable — you must pick data from reliable sources. There are challenges when applying for something — all those agreements — and PII (personally identifiable information) is an explicit concern, along with deletion criteria. Nowadays all the people who send mail, including Google and all the advertisement companies, also send you an unsubscribe option — otherwise they cannot send advertisements anymore. When you are part of a subscription model, they should also give you an option to unsubscribe. Sometimes, if you are not careful, an app like Yathal will charge you a lot of money — you should know to go and look for the subscription and how to do the unsubscription and the deletion criteria.

2.9.4 Restrictions and Compliance

There are restrictions in data collection — you need to ensure what you should get and what you shouldn't get, and that is a challenging part. We need to follow the compliances. Can an employer store all the employee data outside the location of the data origin? The European Union data cannot exit the region — it cannot move out of the region; that is why there is a green site concept: anyone working with UK or British people has a green site, and you have to log into their system only — you cannot have their data over here. There are data board restrictions: permissions to store data, how the storage happens, what type of permissions you have, access to the data provider — somebody providing your data through Google or Instagram — how it is provided, when, and why. There are rules: how the rules are imposed, how regulatory compliance is followed, what regulatory and industry practices are followed. All are important.

2.9.5 Student Questions and Answers

2.10 Access Control, Anonymization, and Data Recovery

2.10.1 Granular Access to Stored Data

There are challenges at the storing phase — restrictions. Should you have read access? Should you have write access? What access do you have? Consider an orders table with millions of orders from multiple locations — London, Chennai, Mumbai, Bangalore, Hyderabad. What sort of access privileges should people have? Should they have select access to all the orders data across locations, or only a particular location? Can Chennai people insert data into the London location? No. Insert, deletion, update — can they modify something? All those things are to be considered. So the storage space has access restrictions: we want to provide granular restrictions and logging to know who made the change.

2.10.2 DML Activity and Auditing

If everybody is given all access — insert, delete, update, modification — a person can go and delete all the order records of Mumbai, to show that Mumbai's orders are lesser than Pune's, and that creates a problem. How do you handle that? With logging, tracing back, and auditing. This is called DML activity — data manipulation activities. Every DML activity can be audited, tracked, and logged: every transaction is logged — from where, date, time, what, who intended, what objects they have access to — all those things are tracked.

2.10.3 Anonymization and Masking

Anonymization of data reduces the data access and auditing burden. Example: a shopper comes to a shopping mall and purchases something — you don't need to show that "Rahman came and purchased it"; you replace the private identifier with another. But how do you reverse it back?

Some data is personal, private data — so you restrict or you do masking: restrict at all levels, row level. The mantra is RBAC — role based access control: based on your role, you get the access control. There are also other discriminatory access controls, and single sign-on access.

2.10.4 Data Deletion and Recovery

When you delete the data, the user mentions a specific time period — when the organization no longer needs the data. A lot of portals retain your data for some time, say six months; after that you have to recreate your profile in the portal. Governing policies here include the GDPR. Real-world: when a system crashes, how does the database recover? It is not that easy. War story: the instructor has been to many data recoveries, in the US as well as Singapore, and once recovered about two days of transactions — it took a lot of time. Now it is easy: lots of advancements with replication and SAN (storage area network) technology — you can do block-level recovery quickly — but that was not the case ten years ago, when it was very difficult. So recovery is a big problem.

2.10.5 Industry Applications

Real-world: GDPR governs deletion and data retention (six-month portal retention is a common practice). Real-world: RBAC and single sign-on are standard access-control practices in enterprise systems. Real-world: replication and SAN technology now make block-level database recovery fast — the same war story that once took days now takes minutes, because the recovery is built into the storage design.

2.11 Data Pipelines and ML Sensitivity

2.11.1 The Pipeline Picture

A pipeline is connectivity — a connector, with many connectors and many stages, something like a water pipeline: from your tank, water goes into the filter and then through pipes until it reaches your water bottle. The water comes from the Cauvery river or some other source, but finally it comes to your tank. The data pipeline works the same way: data comes from multiple sources and finally reaches the machine learning model, which processes it.

2.11.2 Scenario: Predicting New Year's Eve Sales

Here is the scenario to reason through. An e-commerce company wants to predict New Year's Eve sales — the sales on December 31st — from all the countries, regions, and languages. They also want to study trends based on language. Due to a technical or human error, they lost the data from a particular country, region, or language. Take the example of Apple: Apple phones are available everywhere — all countries, regional languages. Something has happened: the data pipeline was still executing normally, while the ML pipeline was behaving so differently. The data pipeline was still executing because it is just doing its work — but the ML can go wrong.

2.11.3 Why the ML Model Failed While the Data Pipeline Ran Fine

The logic behind the answer: a data pipeline moves data, and moving a smaller volume of data is still normal work — the pipes never complained. An ML pipeline is different: it includes a model, and the model learned its patterns from the training data, including the language categories. When whole regions vanish from the input, the model must answer for people it never saw — Iran and India had huge sales, and the model was trained on a world without them. The result is an underfit model: accuracy on the familiar cases stays okay, while false positives and false negatives multiply. Omission results in incorrect prediction.

2.11.4 What ML Needs From Data

Here is the key statement — flagged as very, very, very important: normal data pipelines are sensitive to the volume of input data and the correctness of input data; ML pipelines are sensitive to changes to the distribution of data, the volume of input data, and the correctness of input data.

2.11.5 Handling These Scenarios

How do you handle those scenarios? Can you create some buffer data? Can you do some masking? Can you do some extra filling? There is a process, and a couple of programs will be shown. Python knowledge helps — if you know Python it is going to be easy — but even if you do not, don't worry: a Google Colab notebook with a Gmail account will be used, code will be shared, and one complete data engineering process will be done together. Even without Python knowledge, you just follow the steps — it is very easy.

2.11.6 Sample Sizes, Underfit, and Overfit

To buy a shirt — for yourself, or to give to hundreds of friends — take the best sample: good sample sizes of height, weight, size — say five samples for 150 people — and as the good sample sizes increase, the volumes increase, and you get a better prediction. But if the sample size is very, very small — a very small shirt — it is going to be the underfit: it may not fit another person. If you wear one shirt which is extra-large, it may be an overfit. You need to have the best fit.

When you train on more and more data, you can improve model performance. But the likelihood of data issues or problems starts low and rises with very large data sizes — meaning a bigger dataset increases the chance of data quality problems: noise, inconsistency. That is why you are studying this subject — what do I do about it?

2.11.7 Distributed Data Characteristics

Data is not in one place. Data at rest is in one place, but data is distributed across multiple places. Sometimes data is lost, so we need to make copies. Data changes frequently — how much time does the data take to travel, how fast should the data be available to get an accurate answer, is the data available as and when we need it? These lead to the characteristics of data: durability of data, consistency of data, version control — what version we need. We will study big data characteristics, performance, and availability: do we need all this data available 24 by 7? How fast should the data be available? What level of data do I want, when, at what time, what data to preserve? Do we need consistent data — the same data for all the copies? If one node goes wrong, do we have durability — is it distributed in other systems? Integrity issues arise because of varying version controls and consistency — the copies may be different, and you may get into integrity issues.

2.12 ETL, Data Warehouses, and Data Lakes

2.12.1 The ETL Process

Raw data comes from sources like web APIs, Instagram feeds, Twitter tweets, application APIs that give the number of clicks, mobile applications, IoT devices, forms, and surveys. Once ingested — can we use the data as such? No, not possible. We retrieve and verify data from various sources, process and organize, and extract the data so it is usable; then we transform — some massaging of the data, organization — and finally the data moves to a data repository.

There are a lot of ETL tools available: DataStage, Informatica, Cognos, and many more.

2.12.2 Why ETL Is Not Enough

Is data always structured? No. Can ETL cater to the growing velocity? No way — because big data comes into the picture. Data is coming at high speed, high velocity; there are seven characteristics of big data, which will be discussed shortly. Can ETL work with real-time data? Data streaming is happening, and the existing ETLs have shortcomings. That is why we need to build different models and pipelines — we need to get into big data systems like Hadoop and distributed systems. We need to filter the data, merge the data, and aggregate the data, depending on what model we are going to build: should we classify the data, predict new data, or cluster the data? Data can be stored before or after data processing. What is the type of the data? What was the purpose of the data? Do we want a data warehouse or a data lake?

2.12.3 Data Warehouse and Star Schema

The data warehouse requires a defined schema — for example, the star schema (star model). The skeleton: four dimension tables — a product table, a customer table, a time/period table (the date and time information), and a location/city table — all connected to the facts. With the multi-dimensional connections you can do roll-up, slice, dice, pivot, rotate — anything and everything is possible, because it follows a schema and has a specific skeleton.

Real-world: PostgreSQL relational database systems (RDBMS) are used; the instructor has built data warehouses for a mutual fund business and for telecom accounts, optimized for high-performance analytics on structured data. Data analytics dashboards, business intelligence tasks, and data visualizations all use the warehouse.

2.12.4 Data Lake and Lake House

Many, many organizations are moving towards the data lake. Is a data lake and a data warehouse the same or different?

That is sometimes called a data lake house — cleaner, but it supports the unstructured and streaming side: the warehouse predominantly supports structured data, while the lake handles a lot of streaming data, real-time streaming.

2.12.5 Industry Applications

Real-world: ETL tools DataStage, Informatica, and Cognos run enterprise data integration. Real-world: Hadoop and distributed systems handle big data that ETL cannot. Real-world: ICICI-style customer churn analysis is a standard ETL-driven business task, and warehouses built for mutual funds and telecom are typical industry use cases — the pond-or-lake choice comes down to whether the business needs governed structure or raw flexibility.

2.13 Data Governance, Metadata, and Data Platforms

2.13.1 Data Governance

Who does all of this? There are data governance councils: they align taxonomies — the structures that ensure what should go — and define metadata consistently across all the data sources. They define the roles and responsibilities. For data security, we define roles and responsibilities to help ensure that data access is provided appropriately — controlled access to protect the customer data, to ensure there are no threats, no cyber criminals, no data loss: more encryption, more data masking.

2.13.2 Data Architecture

Data architecture provides the infrastructure for the storage, integration, and use of data throughout the organization. The architectural-level components are defined here: am I going to use a particular vendor system for storage? Streaming storage? How am I going to do the integration layer — storage, big data, or something else? All those things are defined at the data architecture component level.

2.13.3 Metadata

Metadata is data about the data. Suppose you have 20 tables: metadata holds the details about the 20 tables — what the table is, where it is stored, what columns it has, how many rows it has, what index it has. It is like a dictionary being maintained of all the critical attributes used in the entire systems.

2.13.4 The Data Management Framework

A framework is basically a high-level structure. When we build any data management component, the component should have a proper framework: what process we are going to follow, what policies, what data stewardship mechanisms, what standards, what best practices. Data governance provides the overarching support to data management through stewardship policies, processes, standards, and adherence to leading practices. Data stewardship is part of it, and there is something called master data management (MDM) — a certification area (InfoSphere) that does a lot of stewardship.

2.13.5 Data Platforms

A data platform is basically a package — a software suite, built with Python, Java, and existing API tools — that an organization uses to process and manage large amounts of data quickly and efficiently. It manages the data, normalizes the data, transforms the data, and applies it to a given data product. It requires a central repository and a processing house, and it has lots and lots of data pipelines to collect, ingest, store, and transform the data — everything put together as a platform. In the olden days we called it front end and back end; now we have the data platform in between — earlier we had a middle tier — with delivery to a variety of destinations, and the data can be consumed by different enterprise groups. The tools require strong data and computing infrastructure that scales to meet demand without impacting performance.

2.13.6 Big Data and the Road Ahead

Real-world: an IBM study asked CEOs about their top priorities: enhance security and risk management — and "who has the most advanced generative AI." If you want to take advantage of generative AI, consider the tool named AntiGravity: it generates customer data about transactions — electronic gadgets, mobiles, smart devices — and shows a simple data pipeline in Python to classify high-sale orders and no-sale orders. It uses models, data pipelines, and vector databases. CEOs want security, proper risk management, and better decision making, and they are thinking of using generative AI, because of the volume of data, data silos, different data type formats, and datasets that are always complex and inconsistent. Data management is the practice of collecting, processing, and using data securely and efficiently for a better business outcome, so a strong data management strategy — good data ingestion, data processing, data storage, data governance, and data security — is important.

Big data systems provide an ecosystem — a data management framework you can build your own company's data management framework from, using the technology.

Coming up: the phases in the data lifecycle — generation, ingestion — with examples, and a hands-on data engineering exercise in the class. It is an exhaustive portion, taken step by step. Think of the Eiffel Tower or the Burj Tower: now we are seeing the tower from afar; we will go one by one and slowly everything will come together — a top-down approach, not a bottom-up approach.

2.13.7 Student Questions and Answers

Exam Guidance Summary

• Data quality check question. Expect a question in the style of the in-class exercise: you must be able to identify at least five data quality issues in a sample table (missing values, outliers, mismatched units, inconsistent encoding, inconsistent names) and explain how to resolve them. Coding is not necessary, but you should know what sort of issues exist and how you would resolve them.
• Both data model families. The SQL type model and the NoSQL type model are both required material — you must know both. A MongoDB cluster demonstration (including the AI-driven query generation) is planned around the midterm.
• Normalization. The normalization document is shared material to study: first normal form, second normal form, and third normal form, and BCNF — be ready to apply the normal forms to a given table.
• Binding and imputation. The binding technique and data imputation (filling missing data with constants, min/max, mean, median, or boundary values) are flagged: they will be covered in a later session, and they are the core fixes for missing values.
• Pipeline versus model sensitivity. The statement that normal data pipelines are sensitive to the volume and correctness of input data, while ML pipelines are also sensitive to changes in the distribution of data, was flagged as very, very, very important — this is the key conceptual question of the lecture.
• Hands-on exercise. A hands-on data engineering exercise is planned in class using Google Colab with Python — even without Python knowledge you can follow the steps, so don't skip it.
• OLTP versus OLAP. Expect the OLTP versus OLAP contrast (real-time transactions versus historical analysis, user counts, warehouse dependency) and the data warehouse versus data lake contrast (defined schema versus no schema) as core conceptual material.
• The framework. The data management framework components — governance, architecture, metadata, data quality, lifecycle, analytics platform, privacy — are the framework for thinking about the whole subject.

Key Industry Applications

• GPay, bank withdrawals, and money transfers are everyday OLTP systems; two-year bank statements and payment histories above a threshold are OLAP queries over warehouses.
• Netflix uses data-informed perspectives to think critically about its business and improve services — data as an asset.
• LinkedIn and Facebook use graph databases (Neo4j, Neptune, Gephi) for connectivity, tagging people, and friend-of-friend recommendations.
• Amazon DocumentDB, Apache CouchDB, Firebase, and Firestore are document databases used for logs, purchase logs, and event information.
• Oracle, Informix, Sybase, and PostgreSQL are relational (SQL) systems; Amazon Redshift and Parquet are columnar formats used in analytics.
• MongoDB is the NoSQL demonstration system — its cluster is AI-driven and writes queries from a plain description.
• Google provides the unsubscribe option for ads (compliance), and Google Colab is used for the class data engineering exercise.
• Apple's global sales across countries, regions, and languages is the scenario for how losing one region's data breaks ML predictions.
• ICICI-style customer churn analysis is a classic ETL task; DataStage, Informatica, and Cognos are the ETL tools in use.
• IBM's CEO study links data management to security, risk management, and generative AI; the AntiGravity tool generates customer data and builds Python data pipelines with vector databases.
• Healthcare data is governed by HIPAA and HL7 standards; GDPR governs EU data (which cannot leave the region) and the green-site practice for UK data.
• Hadoop and distributed systems handle big data that ETL tools cannot, including Parquet format storage.
• Twitter and Instagram APIs, web APIs, IoT devices, forms, and surveys are the raw data sources feeding pipelines.
• Yathal is the cautionary example of a subscription app that charges if you don't unsubscribe in time.