Data Pre-Processing, Data Architecture, and Warehouse Schemas

This session starts with a recall quiz and the carpenter story, then moves into hands-on data cleaning in Google Colab with an AI assistant. From there it climbs to the framework layer: TOGAF, DAMA-DMBOK, PMBOK, and ITIL; structured prompting with the CoStar framework; categorizing data architectures; data warehouses, data marts, and master data management; the star and snowflake schemas; and ETL versus ELT. It closes with the course roadmap and hands-on goals.

3.1 The Carpenter's House and the Data Quality Mindset

3.1.1 The Week-Three Recall Quiz

We are in the third week of the course. The opening activity was a recall quiz: students type something they remember about the subject so far. The answers included four ideas that form the foundation vocabulary of data management:

• Garbage in, garbage out — bad input data produces bad results downstream, no matter how good the tools are.
• Data silos — isolated stores of data that cannot talk to each other, so each department sees only its own slice.
• Data lake — a large repository that keeps raw data in its original form until it is needed.
• Data warehouse — a central, organized store of data prepared for reporting and analysis.

That these four answers came up unprompted showed the group was making good progress. But the phrase that frames the entire session is garbage in, garbage out: if the data going into a pipeline is bad, everything downstream inherits the damage. No model, report, or dashboard built on dirty data can undo that damage later.

3.1.2 The Story: The Carpenter's Last House

The session's story was read aloud. An elderly carpenter was ready to retire. He told his employer about his plans to leave the house-building business and live a more leisurely life with his wife and extended family. He would miss the paycheck, but he needed to retire.

The contractor was sorry to see his good worker go and asked whether he could build just one more house as a personal favor. The carpenter said yes, but his heart was not in the work. He resorted to shoddy workmanship and used inferior materials — an unfortunate way to end a career.

When the carpenter finished and the builder came to inspect the house, the contractor handed him the front-door key. "This is your house," he said, "my gift to you."

What a shock, what a shame: the house he built so carelessly turns out to be his own, and now he has to live in it. If he had known he was building his own house, he would have done it all so differently. The moral: every job must be completed with expertise.

3.1.3 Student Question: Was the Carpenter Fired?

One student asked whether the carpenter was fired — a wrong conclusion reached by jumping ahead while discussing the story. The correction became a lesson in itself.

3.1.4 Takeaways for Data Engineers

The takeaways from the story and the exchange:

• Admitting mistakes. The student accepted the correction openly, and that act of acceptance is itself a lesson: accepting a data quality problem is a good quality of a data engineer. Admit if there is a problem; admit if there is a mistake. A data professional who hides a bad column, a wrong merge, or a broken load only delays the damage.
• "Success is not doing extraordinary things." Do your best every time, on every task, with the same energy as your first day — even in a notice period, even on your last task. There is a quote for it: till your last minute, you are your best.
• The carpenter's house is a picture of building a data pipeline. Shoddy work today becomes the house you live in tomorrow. Every job must be completed with expertise, and the last word of your work is part of your work.

3.2 Hands-On Kickoff: Google Colab and AI Assistants

3.2.1 Choosing a Coding Environment

Hands-on coding begins here. From this session to the last, each session includes one or two small, quality code snippets plus database tips. Any environment works: local Jupyter notebooks, Spyder, VS Code, or any of the many IDEs available. Because the participant count is large, the choice was Google Colab: go to colab.google.com, log in with your email, and create a new notebook. Colab runs in the browser, needs no local setup, and stores notebooks in your Google account, so everyone in a large class starts from the same place.

3.2.2 Gemini Inside the Notebook

On the right-hand side of a new Colab notebook sits a Gemini panel (the flash model) — an AI teaching assistant built into the notebook. The point is not to replace learning but to learn effectively: you can ask the assistant anything, from "how can I install a Python library?" to questions about the data you are looking at. This connects to a wider point about generative AI tools — Gemini, Claude, and NotebookLM are all available, and you need to use AI effectively to do your work.

3.2.3 Tools, Not Magic: The Plumbing Analogy

A data engineer needs many tools, and to use any tool you first have to set it up.

The assistant can install packages automatically — you watch it do it — or hand you the exact command to run yourself. It also knows the common libraries: Pandas for data frames and NumPy for numeric work. Those two names will appear constantly in this course: Pandas gives you tables with labeled rows and columns, and NumPy gives you fast array math underneath.

3.2.4 Notebook Cells and the Learning Rhythm

Colab notebooks are built from cells. You add a code cell with the plus code button and a text cell with the plus text button; text cells are where you write explanations about what a cell does. The plan for the course: at least two hands-on activities per session related to data management for machine learning, starting simple and getting complex, taught for a mixed audience — the explanation assumes nothing, so everyone follows.

3.3 Data Pipelines and the Pre-Processing Workflow

3.3.1 What a Data Pipeline Is

The definition was read aloud in the session, and it is worth unpacking piece by piece:

A data pipeline architecture defines the structured flow of data from multiple sources through processing stages to its final destination for storage, analytics, or operational use. It ensures data integrity, scalability, and efficiency, while supporting both real-time and batch processing needs.

Walk through the parts: structured flow means the movement of data follows a designed route, not an accident. Multiple sources means the pipeline exists precisely because data arrives from more than one place. Processing stages are the steps in between — cleaning, transforming, validating. Final destination is where the data becomes useful: a database for storage, a dashboard for analytics, or a live system for operations. Data integrity, scalability, and efficiency are the three promises a well-built pipeline keeps: the data stays correct, the design copes with growth, and the work does not waste resources.

Modern architectures often follow ETL (extract, transform, load) or ELT (extract, load, transform) patterns. In ETL, transformation happens before loading, which is ideal for structured data. In ELT, data is loaded first and then transformed inside powerful cloud warehouses for flexibility and speed. Other models include stream processing pipelines for real-time analytics, batch pipelines for scheduled large-volume processing, and zero ETL for direct system-to-system integration.

3.3.2 From Many Sources to One Structured Frame

The first code of the session creates such a frame: sample data is defined in code (synthetic data), deliberately including various data quality issues — wrong values, missing values, inconsistencies — and then printed. In the real world the data could come from many places: a downloaded file, a Google file, social media data (Instagram), a URL feed, a sensor connection, or a database query. Data may be images, video, or streaming data. In this first example it comes from a single source: the code itself.

3.3.3 The Pipeline Big Picture

The standard workflow shown in the session: data understanding first, then data pre-processing, then clustering, association, classification algorithms, validation, and advanced topics. This course focuses on the first two stages plus data engineering. The reason is garbage in, garbage out: roughly 70% of machine learning models and pipelines live in the first two stages — data pre-processing, data cleaning, data engineering, ETL, and EDA.

3.3.4 The Four Data Pre-Processing Tasks

Data pre-processing is the process of making the data more suitable for data mining. There are four tasks:

1. Data cleaning — fill missing values, smooth noise, remove outliers, correct inconsistencies, drop duplicates.
2. Data transformation — change the data's form or scale (normalizing, aggregating, encoding).
3. Data reduction — shrink the data set to a manageable size without losing its essential information.
4. Data discretization — convert continuous values into intervals or categories.

These four were stressed strongly — "trust me" — because they are the workhorse vocabulary of the field; mastering them lets you do hundreds of other tasks.

3.4 Data Cleaning and Handling Missing Data

3.4.1 What Data Cleaning Is

The tasks in data cleaning: filling in missing data, smoothing out noisy data, removing outliers and artifacts, correcting inconsistent data, and removing duplicate data. The session then works through each one. Notice the pattern: every task is a repair — you are fixing problems that exist in the data before you can trust it, not adding anything new.

3.4.2 Four Ways of Handling Missing Values

When a value is missing, you have four classic choices:

1. Ignore the instance. If a record is very bad or a column is missing, you can ignore that data instance. It is not an effective method, but why carry the burden — better to not have the garbage at all. This is the fastest option and also the most wasteful: you throw away the good columns of a bad row along with the bad ones.
2. Fill in the missing value manually. Age missing → fill the age; car year missing → fill the year. Manual filling works but is very tedious and typically infeasible at scale; it takes lots and lots of time. It is the right tool only when the data set is tiny and the missing values are few.
3. Use a global constant. Country missing → put India; currency missing → fill with the currency; location missing → replace with some location. A single constant stands in for every missing value in the field. Simple, but it can quietly poison statistics — if you replace fifty missing countries with one constant, your data now claims fifty records from a country that may not be the real one.
4. Imputation — fill the missing value using the feature mean or the most probable value. This is the most used option because it preserves the rest of the record while supplying a plausible value.

3.4.3 Imputation Techniques in Detail

A terminology contrast first: the course is called data management for machine learning, but the reverse direction also exists — machine learning for data management.

The imputation techniques:

• Delete the missing observation. Deleting can lead to serious biases; if the missing share is small — say 5% or 10% of the sample — deletion is acceptable, but beyond that it starts to distort the data. The rule of thumb: when the missing share grows, the deleted rows stop being a random sample of your data, and everything you compute afterwards is built on a tilted picture.
• Hot-deck imputation: substitute the missing value with the value from the most similar cases. "Most similar" means a similarity method — a kind of analogy matching. The session first floated the term "cold check," then settled on the standard term hot-deck imputation: the donor value comes from a similar record inside the same data set. (The related cold-deck imputation variant draws donors from a different data set — the warm data set vs. the cold external one.)
• Distribution-based imputation: distribute values according to the observed distribution — for example, 20% or 15% shares based on previous observations. If 20% of your known records have a certain category, then 20% of the missing slots are randomly filled with that category, keeping the overall distribution intact.
• Statistical imputation with expectation-maximization (EM). EM works in two repeating steps. First it estimates the distribution of the data from what is observed (the expectation step). Then it fills the missing values in the way that is most likely under that distribution (the maximization step), and repeats until the estimates stop changing.
• Predictive imputation: write a code — for example a linear regression — that predicts the best value. Given a sequence like 16, 17, 18, missing, 20, 21, 22, the model fills the gap with the value it predicts.

3.4.4 Smoothing Out Noisy Data

Noise means a random error or variance in a measured variable — the reading wobbles around the true value. Three classic smoothing techniques:

• Binning — smooth a sorted data value by consulting its neighborhood, the values around it, with the sorted values distributed into a number of bins. A noisy value is replaced by its bin's summary (mean, median, or boundary), so local wobble is absorbed into the bin.
• Clustering — detect and remove outliers. Group the data into clusters; points that sit far from every cluster are treated as noise and removed.
• Regression — smooth by fitting the data into regression functions. A fitted line or curve gives each point a predicted value, and the predictions replace the noisy observations.

3.4.5 The Colab Walkthrough: Cleaning the Sample Data

The walkthrough cleaned the synthetic frame step by step. This is the full trace of the session's first hands-on:

1. np.nan appeared in the age column.

1. Task one — handling missing data by deleting the missing observation; the missing name was deleted.
2. The name column was filled with "Unknown" where values were missing (a global constant fill).
3. Age: missing values filled with the age mean — the Pandas fillna method with the mean value.
4. Height: missing values filled with the median; height shown in cm (this also fixes a unit inconsistency).
5. Inconsistent data corrected with a lambda function — a small inline function that rewrites the non-conforming values into one format.
6. Duplicate rows dropped.
7. Outliers removed — the age value of minus 160 (a negative age) was flagged as an outlier or incorrectly collected data and removed.

1. Noisy price data was smoothed by binning: the price column became a price bin with values bin 0, bin 1, bin 2. The sorted prices were cut into three bands, and each price was replaced by its band's label.
2. Final print of the data frame to confirm the cleaning. The cleaned frame now shows a price bin column and heights in cm, and no negative values.

3.5 Data Architecture Frameworks

3.5.1 Why Frameworks Exist

There are lots of data architecture frameworks available, and companies can also define their own. This is not theory pulled from a book: the trainer worked as a data architect on a British Telecom account and as a consultant for banking and Cisco projects dealing with data architecture.

The standing advice for students without a computer science background: breathe in, breathe out, relax — computer science is a common sense. You use your common sense and your knowledge; the frameworks just make the thinking explicit. Nobody expects you to memorize frameworks by heart; the frameworks are scaffolding that shows you what to look at.

The frameworks introduced in this session: TOGAF, DAMA-DMBOK, PMBOK, and ITIL.

3.5.2 Process, Project, and Product

Before the frameworks, three words need clean definitions:

• Process — a repeatable sequence of steps that turns inputs into outputs. Processes build products.
• Project — a time-boxed effort with a purpose and a timeline — three months, six months, one year, depending on the company's needs. Projects are done for a specific purpose: a migration project, a consolidation project, a data management project.
• Product — the outcome the process and project deliver.

Every process must have a process owner — somebody owns it; not everything can be done by everybody. Every process needs inputs, follows constraints, produces an output, has tasks that must be executed, and depends on other processes. This five-part shape — inputs, constraints, outputs, tasks, dependencies — is the same shape you will see inside every framework below.

3.5.3 PMBOK and ITIL

PMBOK explained in one sentence: the reference manual of project management. One participant was a PMP-certified project manager, so PMBOK got a live definition from someone who works with it daily. PMBOK organizes the work of projects — scoping, initiating, planning, executing, controlling, closing — into processes with named activities, so a project team anywhere speaks the same language.

ITIL works the same way for operations. In change management, an incident becomes a problem, the change is approved, then the change must be checked against availability — do we have the tools — and capacity — do we have the capacity. Lots and lots of processes are interconnected: a change in one place ripples into availability, capacity, and incident processes elsewhere.

3.5.4 TOGAF and DAMA-DMBOK

TOGAF — The Open Group Architecture Framework, a standard of The Open Group and one of the most widely used architecture frameworks today. TOGAF gives you the different types and sources of data, the logical data sets, the physical data sets, and the data management resources available. In short: TOGAF is the framework for architecting the enterprise, and data is one of its layers.

DAMA-DMBOK is like PMBOK for data. It identifies the data needs of the enterprise, regardless of structure, and designs and maintains the master blueprint.

3.5.5 Why Data Architecture Matters Today

Data requirements change rapidly, and new tools and practices keep appearing — what is current now becomes obsolete within months or a year. So we study the frameworks to understand business needs, know the trade-offs, and translate requirements properly.

Data architecture addresses the functional requirements of what needs to happen related to people, process, and technology — who uses the data, what processes work with it, what technologies relate to it. At the operational level it outlines how data is ingested, stored, transformed, and served along the data engineering lifecycle. Every project has a lifecycle, and data engineering has its own: how data is ingested, stored, transformed, mapped, converted, delivered, and disseminated.

3.6 Prompt Engineering with the CoStar Framework

3.6.1 Prompt versus Prompt Engineering

A plain prompt — for example "what is DMBOK" — returns a plain answer. A structured prompt returns a structured answer.

3.6.2 The CoStar Framework

The CoStar framework structures a prompt into six parts, and it is itself a procedure you follow every time you need a high-quality answer from an AI assistant.

3.6.3 Worked Example: The Mainframe-to-Linux Modernization Prompt

The worked prompt was built live for this domain. Task: DMBOK architecture and mainframe to open system modernization, financial industry.

The real-world stack in the context field is the classic mainframe estate — COBOL programs, VSAM files, DB2 data, plus separate reporting systems. The trainer knew the same pattern from State Street, where lots of data was pulled from mainframes and legacy systems were rebuilt on Linux and Unix systems.

3.6.4 The Skill-Building Follow-Up Prompt and Its Roadmap

From the blueprint you can take any single slice. The slice chosen: skills. The follow-up prompt, run in Perplexity or Copilot: "I want only skills to be given and a roadmap for skill building using the above prompt. Skills and techniques to be learned. Roadmap using the above prompt."

The response was a personal capability development plan: it is going to take four years to become a successful person at this level. The skills domains to master:

• Enterprise data architecture aligned with DMBOK
• The DAMA-DMBOK framework and all 11 knowledge areas
• The three levels of data architecture — conceptual architecture, logical architecture, physical architecture
• Capability mapping and maturity models
• Banking-specific data domains
• Mainframe systems and open systems
• Data governance and data stewardship
• Integration (including graph query language)
• Reporting
• Securities and risks
• AML (anti-money-laundering) readiness
• Feature engineering pipelines

3.6.5 Hands-On Exercise: Build Your Own Structured Prompt

The exercise for the session: open ChatGPT, take your own domain and your own work, change the prompt fields, and build a structured CoStar prompt for yourself. Paste it into a notepad, put it in the chat box, and validate it. Then use Perplexity or Copilot to extract a skills roadmap from it.

3.7 Categorizing Data Architectures

Data architectures can be categorized by centralization, by storage, by governance, and by processing. The image used: DAMA-DMBOK is the biryani — the full dish — and now we look at what is inside the content, the ingredients.

3.7.1 By Centralization: Centralized, Distributed, Hybrid

Centralized data architecture: one central database server, and every system feeds everything into that server. Advantages: a single point of control, simplified management — good for banking, healthcare organizations, and regulatory environments, where one authoritative store makes compliance and control straightforward.

Distributed data architecture: each unit works within its own thing — banking systems work separately, retail systems work separately; each unit takes care of its own sources, its own aggregations, its own reporting, like a group aggregation. It is good for many different sources and many different aggregations; insurance companies may use this approach, and master data management (MDM) is often decentralized because customer data, product data, bank data, and application data live in different places.

Hybrid data architecture: combine a few centralized and a few decentralized parts and mix based on need — the data structure mixes the two, some done centrally, some done decentralized, depending on your infrastructure and how you exchange data.

When to pick which: choose centralized where control and compliance dominate; choose distributed where units genuinely need autonomy; choose hybrid when both needs exist and you can afford the coordination.

3.7.2 By Storage: Warehouse, Lake, Lake House, and Data Mart

By storage you can have a data warehouse, a data lake, or a data lake house.

• Data warehouse — a structured store, organized around subjects, optimized for reporting and analysis; data is cleaned and modeled before it serves queries.
• Data lake — a repository that keeps raw data in its original form until it is needed; cheap to fill, flexible later, but the cleaning burden moves to whoever queries it.
• Data lake house — combines both: lake-scale storage of raw data with warehouse-style table support, schema management, and update/delete capabilities.
• Data mart — a subset of a warehouse, built for one department, team, or use case.

The earlier session covered the data lake and the data warehouse; this session adds the data mart. The healthcare example: clinical data passes from one hospital to another (the example named Kaveri hospital in Chennai sharing with its other branches), research data and clinical operations data get exchanged, and different activities use the data.

3.7.3 By Governance and Processing: Mesh, Fabric, Lambda, Kappa

Governance-wise you have data mesh and data fabric:

• Data mesh — decentralize by domain: each team owns and serves its own domain data as a product. It inverts the monolithic platform: instead of flowing data into a centrally owned lake, domains host their own datasets and consume each other's. Key principles: domain-oriented ownership, data as a product, self-serve infrastructure, federated governance.
• Data fabric — a centralized metadata and integration layer that connects data wherever it lives, providing a unified view without moving everything into one store.

Processing-wise you have lambda processing and kappa processing:

• Lambda — run two parallel paths: a batch layer for accurate historical results plus a speed layer for near-real-time results, merged for the final answer.
• Kappa — one streaming path only: treat everything as a stream and replay it for batch-style results, avoiding the double logic of lambda.

3.8 The Mutual Fund Warehouse Story

3.8.1 The Original Warehouse

The story is a real one from practice. The team built a data warehouse for a global mutual fund business — the mutual fund business engine — covering 10,000 funds. A fund is related to many stocks: short-term funds, mid-cap funds, tech funds, and so on.

There was lots and lots of mainframe data. Mainframe pushed the data into a staging area; the team wrote extraction, transformation, and loading (ETL) jobs to get the data, massage and clean it, and load it into Oracle and Informix databases (the architect had also worked with DB2 and other databases). Databases were built on a nightly basis with a lot of batch processing. Reporting went to Crystal Reports and Business Objects, plus other front-end and development tools.

3.8.2 The Ontario Teachers' Pension Board Request

After about a year the warehouse became so big and huge that processing specific funds was taking a long time. Then a real client arrived: the Ontario Teachers' Pension Board, which wanted to have its own mutual fund. The pension board was investing in only some of the funds — for the example, 90 funds out of the 10,000.

3.8.3 The Data Mart Solution

The data model did not change: same data, same tables, same structure. The team ran one more process — extraction, filtering, loading — creating an exact miniature that mimics the structure.

Delta processing followed: whenever a fund changes, a stock changes, or the market changes on the mainframe, the delta is loaded nightly into the mart. Data warehouses are usually about eight hours behind the live systems (some are near-time, some five to six hours behind).

The advantage: portfolio administrators and banking people can build reports — this fund performed like this over one month, last week it was like this, yesterday like that — and show them to customers. Customers give inputs on changes; trading happens in real time; the trades go back into the mainframe; and the cycle repeats.

3.9 Master Data Management

3.9.1 What Master Data Is

Master data is defined as data that is rarely changing, permanent, and central; you find those data and manage them as master data. The class discussion added that master data does not change often and is made of attributes. In practice, master data is the shared truth about the things your organization keeps referring to — the customers, products, employees, and applications that many systems reference again and again.

3.9.2 MDM in Practice: Finding New Lines of Business

Master data management (MDM) can be decentralized to accommodate the needs of different units, since customer data, product data, bank data, and application data all live in different places.

The trainer is IBM InfoSphere MDM certified, so the example comes from practice: a new business unit head joins an insurance company and wants to run a new business. The existing systems are scattered, so first you build data pipelines from the different insurance data, collect everything, and build a master data. Once the master data exists, you have information about everything the company does. Then you can find out whether a new line of business (LOB) or a new vertical can be created from it, and build new systems or change existing ones. MDM practice finds new lines of business this way.

3.10 Data Warehouse Fundamentals

3.10.1 The Classic Definition

That one sentence is the classic definition of the data warehouse, and every word in it is a design decision. Four of its terms deserve separate treatment, because together they distinguish a warehouse from any ordinary database.

3.10.2 Subject-Oriented and Integrated

Subject-oriented: the data is organized around the subject you are looking for — accounts data, sales data, customer churn data — the subject being the product or the customer. A warehouse is not organized around applications ("what the order-entry system stores") but around subjects ("everything we know about the customer").

Integrated: data comes from multiple sources, from multiple locations, and is brought together for that particular subject. It is the same integration the story showed: data coming from different topics and many sources is combined — that is how big data evolves and how streaming data evolves.

3.10.3 The Integration Example

3.10.4 Non-Volatile, Time-Variant, Read-Only

Non-volatile means it does not change. Volatile means it is changing. Once data enters the warehouse, it is never removed. Regular, real-time, and transaction systems let you insert, change, update, delete, and replace — all kinds of things. The warehouse is different: load, extract, transform, load — then read-only access is given. You never "edit history" in a warehouse; you add new snapshots.

Time-variant: you want the data from old to now, with the timing — the data carries its time dimension. Every warehouse record knows when it was true, so you can ask "what did sales look like last quarter?" and get the state as it was then, not as it is now.

There is also a classic list of 12 rules of data warehousing (from Bill Inmon) worth reading on this topic — the four-part definition here is the compressed version of those rules.

3.10.5 Data Warehouse Architecture

The typical architecture: lots of operational data comes from multiple sources; you select, extract, transform, and integrate, and build the data warehouse and the metadata. From the warehouse you create subsets of data, which are the data marts. From the marts you use APIs, AWS calls, web service calls — and you build your reporting systems on top.

Sources → Extract/Transform/Integrate → Warehouse + Metadata → Data marts → Reporting (APIs, web services)

3.10.6 Data Warehouse versus Data Mart

Sometimes a dynamic data mart is created just to show somebody something strategic — and then dismantled. Data marts are tactical; the warehouse is the strategic asset.

3.11 Warehouse Schemas: Star and Snowflake

3.11.1 Facts and Dimensions

A warehouse schema ensures you have tables, keys, and relationships — the skeleton that holds the warehouse together. Every schema has two kinds of tables:

• Fact table — holds quantitative data, measurable facts — like sales, revenue, amount, and units. Each row is one measurable event (one sale, one order line) and the values are numbers you can add, average, and sum.
• Dimension tables — hold context data, descriptive information like product names, customer information, or dates. Each dimension describes one way of slicing the facts: by product, by customer, by time, by city.

The two words map to the two kinds of data: qualitative data describes — it is not numbers and not measurable (product name, brand, color); quantitative data is anything measurable (price, units, sales amount). Product data — product, brand, category, color, size — is mostly descriptive, qualitative; customer data in the customer dimension is qualitative too. In a schema, facts answer "how much?" and dimensions answer "about what?".

3.11.2 The Star Schema

The star schema places the fact table at the center, with dimension tables attached around it. The example: dimensions city, product, period, and customer; facts price, amount, sales amount, and units — a product-sales fact table. (The session audio transcribed the facts as "pages, amount, sales amount and units"; price is the natural reading for a product-sales fact table, and that is the form used here.)

              city (dimension)
                    |
product (dimension) ---  FACT table  --- period (dimension)
   (price, amount, sales amount, units)
                    |
             customer (dimension)

The star schema has single-level dimensions: each dimension is one flat table. A dimension can be extended further for convenience: a product dimension can split into electronic and non-electronic products; cars can split into e-cars versus non-e-cars. That is called a denormalized design — the extra category levels are stored inside the dimension table itself rather than in separate tables.

Advantages: quick analysis, faster query performance, easy to understand — every query is one join from the center out, and the schema reads like the business story. The drawback: because of denormalization there is redundancy — the same category names repeat across many product rows — and that is where the snowflake schema comes in.

3.11.3 The Snowflake Schema

The snowflake schema is an extension of the star schema.

                  city (dimension)
                       |
product --> product_category --> FACT table --> period --> date_level
  (sub-dimension)               (sub-dimension)
                       |
             customer (dimension)

Dimension tables are split into smaller sub-dimensions to keep data more organized and detailed, just like snowflakes. It is a multi-level dimensional table with normalization for storage efficiency, suitable for complex data environments. Each repeated attribute (category, region, date part) moves out of the flat dimension into its own normalized table.

Advantages: less data redundancy, efficient storage — each fact is stored once, each category once, so nothing repeats. Drawback: queries can be slow because you have to go through multiple joins — reaching the fact table now crosses dimension to sub-dimension to sub-sub-dimension.

When to pick which: choose the star for fast, simple analysis on most warehouses; choose the snowflake when storage efficiency and organized detail outweigh join cost.

3.12 ETL versus ELT

3.12.1 The Two Patterns

ETL (extract, transform, load): transformation occurs before loading, ideal for structured data. The pipeline pulls data from sources, cleans and shapes it on a separate transformation system, and only then loads the finished product into the warehouse.

ELT (extract, load, transform): data is loaded first, then transformed inside powerful cloud warehouses, for flexibility and speed. The pipeline pulls raw data and loads it as-is; the transformation happens later, inside the warehouse itself, with the warehouse's own compute.

Related models: stream processing pipelines for real-time analytics, batch pipelines for scheduled large-volume processing, and zero ETL for direct system-to-system integration.

3.12.2 Why ELT Wins

The trend in practice: companies use ELT because it transforms during the loading itself — one load, multiple transformations — and it supports multiple pipelines and real-time dashboards. If one place fails in ETL, the whole flow can fail; ELT gives flexibility. That is why ELT is considered more powerful than ETL today.

When to pick which: choose ELT wherever the warehouse is powerful and flexibility matters — the modern default; choose ETL where transformation must happen outside the warehouse, for example under strict compliance or with limited warehouse compute.

3.13 Course Roadmap and Hands-On Goals

3.13.1 The Three Practical Goals

The course roadmap has three hands-on goals. One: Python-based data engineering, connecting data management and machine learning. Two: working with a database — running effective queries, building a data warehouse and a data mart. Three: sharing best practices from experience, starting with structured prompting. On top of that: the Hadoop ecosystem for big data systems, and, if time permits, statistical and visualization techniques that help with effective data management.

3.13.2 The Upcoming Database Hands-On

The database exercise uses XAMPP — a bundle containing Apache, PHP, and MySQL — with MySQL or MariaDB. You download the software from a shared Google Drive, build a data warehouse and a data mart, run the queries, and see the OLAP-style operations. Simple reporting first, then it can be taken into Power BI or other dashboards.

3.13.3 The HR Recruitment Data Mart Example

A data mart example from planning: HR wants to see how recruitment is performing, so they build a separate data mart. The warehouse holds summary data, raw data, and metadata. Data sources — flat files, operational systems, database systems, mainframe systems — all stage into a staging area, then ETL processing builds the warehouse, and the warehouse splits into small miniature data warehouses called data marts for specific purposes, like data mining.

3.13.4 Structure of the Course

The course structure: 16 sessions, 4 webinars, 2 quizzes, and 1 assignment. Exam notes:

• Quiz 1 is worth 5 marks, opens on 24 May and closes on 28 May — no extensions; the dates were finalized with the other faculty, and four days of flexibility were already built in. Quiz 2 follows later, also worth 5 marks.
• The assignment: groups of 3 to 4 members, formed by the operations team. The announcement described it as simple and worth a handful of marks — the wording of the marks was garbled in the session, but the shape (group assignment, formed by operations) is clear.
• Webinar topics include: AWS account creation; working with AWS tools; building the modern data stack ELT architecture; and data extraction using AWS Databricks.
• Next session covers a full EDA and feature engineering — "trust me, that will take you to the next level."
• The session's material covered about 80% of the planned content; the remainder continues next time.
• The DAMA-DMBOK introduction is a must-read; the material will be uploaded to the course folder.

Exam Guidance Summary

• Two quizzes, 5 marks each. Quiz 1 opens 24 May and closes 28 May; no extensions (final dates agreed with the other faculty, with four days of flexibility already included).
• One assignment in groups of 3 to 4 members, formed by the operations team; simple and worth a handful of marks.
• Course structure: 16 sessions, 4 webinars, 2 quizzes, 1 assignment.
• Webinar topics: AWS account creation, working with AWS tools, building the modern data stack ELT architecture, and data extraction using AWS Databricks.
• Practical hands-on expected: Python data engineering in Google Colab, database queries on MySQL and MariaDB with XAMPP, structured prompting with the CoStar framework, the Hadoop ecosystem, and statistical and visualization techniques.
• Next session: a full EDA and feature engineering — promised to take your skills to the next level.
• The four data pre-processing tasks (data cleaning, data transformation, data reduction, data discretization) were stressed as core vocabulary.
• Normalization techniques come in later sessions.
• The DAMA-DMBOK introduction is a must-read.

Key Industry Applications

• Real-world: about 70% of machine learning project time goes into data understanding and pre-processing — garbage in, garbage out drives industry practice.
• Real-world: nightly batch ETL into enterprise warehouses with Oracle, Informix, and DB2, with Crystal Reports and Business Objects on top — the classic warehouse pattern.
• Real-world: data marts for specific clients — the 10,000-fund warehouse and the 90-fund Ontario Teachers' Pension Board mart, 8 terabytes down to 90 gigabytes.
• Real-world: mainframe estates (COBOL, JCL, VSAM, DB2, RACF) being modernized to open systems on Linux for better reporting — the basis of the CoStar worked prompt.
• Real-world: master data management (IBM InfoSphere MDM) in insurance and finance to find new lines of business.
• Real-world: healthcare networks exchanging clinical data between hospital branches.
• Real-world: generative AI assistants (Gemini, ChatGPT, Perplexity, Copilot, NotebookLM) as standard data engineering tools.
• Real-world: cloud warehouses and ELT for flexibility, multiple pipelines, and real-time dashboards.
• Real-world: Power BI and Tableau for analytics and visualization on top of warehouses; XAMPP (Apache, PHP, MySQL) for database work; Hadoop for big data systems.