Data Architecture

AI for Decision Makers | LAM Research

Jed Rembold, Ph.D.

jjrembold@willamette.edu

Willamette University

Lucas P. Cordova, Ph.D.

lpcordova@willamette.edu

Willamette University

July 18, 2026

0. Meet Your Instructors

Jed Rembold, Ph.D.

Jed Rembold

Clinical Associate Professor of Computer Science
Willamette University

jjrembold@willamette.edu

Astrophysicist, now happily in the data.

Background & Research

Trained as an astrophysicist. Ph.D. dissertation on near-Earth meteoroids, measured through lunar impact observations. Part of the Magdalena Ridge 2.4m telescope team before moving fully into computer and data science.

Teaching

Introductory programming, database management and storage, and Advanced Data Engineering. Works at the junction of the natural sciences and computing.

Education

Ph.D. Astrophysics, New Mexico Tech. B.S. Physics and Math, Linfield College.

Lucas P. Cordova, Ph.D.

Lucas Cordova

Associate Professor
Willamette University

lpcordova@willamette.edu

From building software to building software engineers.

Background & Research

Software engineer and team leader turned professor, developing the engineers I’d want on my team and studying what happens when AI becomes part of how we build, learn, and work.

Teaching

Teaching students to write great code, tame messy data, and solve hard problems.

Education

Ph.D. Software Engineering, M.S. Software Development & Management, B.S. Computer Science

Industry Experience

Hewlett-Packard, Elemental Technologies, SAIF Corporation

1. Pitfalls, Failures, and Governance

Learning Objectives

What You’ll Leave With

By the end of this segment, you will be able to:

Name where a data stack breaks, using a concrete symptom instead of “the data is a mess.”
Explain single source of truth, and point to one place yours is violated.
Define data lineage and say why it is the first thing to ask for when a number looks wrong.
Ask three questions of any team whose data your decisions depend on.

Part 1: The Monday Morning Disagreement

Two numbers. One company. Somebody is about to present the wrong one.

Yesterday You Were Michael Scott

Yesterday, you took Michael’s job at the Scranton branch.

You had three tasks:

Decide which reps deserve a raise.
Forecast next quarter’s sales by product.
Find the customer segments worth advertising to.

Every one of those starts with a number you trust. This morning is about what happens when you cannot.

Same Question, Two Answers

Michael needs one number for the board deck: what did Scranton sell?

The hand-kept tracker

The sheet Michael’s team actually maintains. 155 rows, updated by hand, lives on somebody’s desktop.

The system of record

sales_order_lines, the same file you opened in Excel yesterday. Built from orders that shipped and were invoiced.

Total: $43,020.24

Total: a different number.

Both are “the data.” Both are defended by someone with a job title. Which one goes in the board deck?

Look at the Actual Sheet

Five real rows from regional_manager_tracker.csv:

Date	Client	Rep	Qty	Total $	Notes
01/05/22	Whitmore Nonprofit Services	83	19	249.36	check w/ Dwight
09/02/22	Ridgeline Nonprofit Services	5	23	365.24	net-30
07/07/22	Cascade Nonprofit Inc	5	`~11`	107.03	net-30
06/28/22	Redwood Financial Goup	104	`~8`	136.40	net-30
02/10/22	Conerstone Retail Associates	161	15	230.01

Spot the Pitfalls

Nothing here is malicious. Every one of these is a person doing their best on a Tuesday.

Open regional_manager_tracker.csv.

Take 60 seconds. What is wrong with that sheet? Call them out.

The Pitfalls

Here is what is actually in there:

~11 and a dozen in a column that should be a number. 23 of 155 rows, 14.8%.
Goup, Conerstone, Summi. Client names that will never match your CRM.
01/05/22. Two-digit year. January 5th or May 1st?
Rep is 83. An ID, with no name attached.
No order number anywhere. You cannot match this back to anything.
And about 2 in 5 of those totals are simply wrong.

Part 2: Where It Breaks

Six pitfalls. You have met all of them.

This Is What Messy Looks Like

Yesterday, Prof. Kitada Smalley gave you tidy data: one variable per column, one observation per row, one value per cell. She also showed you the messy versions.

That sheet is the messy version, in the wild, feeding a board deck.

The Stack Underneath

Two paths out of the same three systems. They do not agree, and the disagreement is the deliverable that lands on your desk.

Pitfall 1: No Single Source of Truth

When the same question has two owners, it has two answers.

The tracker says $43,020.24. The system says something else. Neither is labeled “official.”

Single Source of Truth (SSOT) means: for any given number, exactly one system is authoritative, and everyone knows which.

You almost certainly do not have this. That is normal. Knowing where you do not have it is the actual skill.

Pitfall 2: Key Drift

To combine two systems you need a shared key. In practice, the key is missing or mangled.

In the Dunder Mifflin CRM export, of 1,923 rows:

97 rows (5.0%) have no Acct ID at all.
The rest match to the customer list perfectly.

So 5% of your customers silently vanish from any report built on that match. No error. No warning. The total is just quietly too low.

Pitfall 3: Format Chaos

The same fact, written five ways, is five facts as far as a computer is concerned.

What it should be	What it actually is
A number	`$141.58` (text, with a dollar sign)
A date	`10/13/2022` here, `01/05/22` there, ISO somewhere else
A quantity	`~11`, `a dozen`
A phone number	`(682) 486-2143`, `2953356785`, `209.577.2260`
A status	`FULFILLED` vs `Fulfilled`

Every one of these is a real value in the Dunder Mifflin exports. Sorting that AMT column puts $99 after $1,000.

Pitfall 4: Duplicates

Nobody sets out to double-count. It happens on export.

CRM export: 61 exactly duplicated rows.
ERP export: 93 exactly duplicated rows.

If you sum revenue off the raw ERP export, you have just billed 93 orders twice. Your number is too high, and it is too high in a way that looks completely reasonable.

Pitfall 5: No Lineage

Data lineage is the map of a number’s entire journey, from the system it was born in to the cell you are looking at.

Ask of any number on any dashboard:

Which system did this originate in?
What was done to it on the way here?
Who changed that, and when?

If nobody can answer in under a day, you do not have lineage. You have folklore.

Pitfall 6: Bus Factor

The tracker lives on one laptop. One person knows the cleanup steps. Those steps live in their head.

Bus factor of one. If they take a vacation, the report is late. If they leave, the report is gone, and so is any hope of explaining last quarter’s numbers.

Manual steps are not just slow. They are undocumented by construction.

🧠 Quick Quiz: Lineage

A regional revenue number on your dashboard looks 30% too high. What does asking for data lineage actually get you?

A. A backup of the old spreadsheet versions so you can recover the previous number.
B. The map of that number's journey from its origin system to the report.
C. The folder structure on the shared drive where the team's files live.
D. A list of which fields are numbers and which are text.

B. Lineage is the journey, not the storage. It is what lets you walk backward from the wrong number to the step that broke it, which in this case is probably those 93 duplicated ERP rows. A, C, and D are all real things. None of them tell you where a number came from.

Part 3: Three Questions to Ask

You will probably not fix most of this yourself. You will ask the team that can.

Most Data Failures Are Not Technical

The tracker is not broken because of bad code. It is broken because no one owns it, no one knows when it last updated, and no one checks it against anything.

That is governance, and governance is a management problem before it is an engineering one. You do not need to write the pipeline. You need to know what to ask the person who did.

The Three Questions

For any number your decision depends on:

Who owns this? One name. If the answer is “the team” or a shrug, that is your single source of truth problem and your bus factor problem in one sentence.
When did it last refresh? A figure nobody has touched since Tuesday is a different risk than a live one. Stale beats wrong, but only if you know which you are holding.
What is it reconciled against? If the answer is “nothing,” you are back to the tracker versus the system. Every number you can trust is checked against something.

Why These Three

Each question is a pitfall turned into a habit.

Question	The pitfall it catches
Who owns this?	No single source of truth, bus factor
When did it last refresh?	Silent staleness
What is it reconciled against?	No lineage, duplicates

Three questions, asked out loud in a meeting, will save you more bad decisions than any dashboard.

🧠 Quick Quiz: Where to Start

Your team wants a new dashboard. What should you do first?

A. Inventory every data source you currently have access to and map what is in them.
B. Ask IT which tables are already modeled so you can reuse existing work.
C. Write down the decision the dashboard is supposed to change.
D. Pick the visualization tool the company already licenses.

C. Start with the business question, specifically the decision that hangs on it. Starting from the data you happen to have is how you end up with a beautiful dashboard full of vanity metrics that nobody acts on. A and B are the second question. D is barely a question at all.

What We Just Did

You now have language for a thing you already knew was broken.

Symptom you have lived	What to call it
“Our numbers do not match theirs”	No single source of truth
“Half the accounts fell out of the report”	Key drift
“It sorted wrong” / “the dates are backwards”	Format chaos
“Revenue looks too high”	Duplicates
“Where did this number come from?”	No lineage
“Only Dave knows how to run it”	Bus factor

The left column gets you sympathy. The right column gets you a fix.

The Big Ideas

Two numbers for one question is not a data problem. It is an ownership problem wearing a data costume.
Broken matchs fail silently. They do not error, they just quietly return less. Duplicates fail loudly the other way, and the two can cancel out.
Lineage is the first ask, not the last. “Where did this come from” beats “can you re-run it.”
Governance is three questions. Who owns it, when did it refresh, what is it checked against.
Name the pitfall, not the feeling. “The CRM has no key on 5% of rows” gets engineering help. “The data is a mess” gets you a shrug.

Up Next

Next you put this to work. You will take one report you actually own and run it through the same questions, in writing, with the group.

2. Making it Personal

Making it Personal

The pitfalls are generic. Yours are specific.

Process Debriefing

What are your main take-aways from this activity?
This was the work necessary to evaluate a single report or dashboard. How does this scale to multiple reports or dashboards?

BREAK TIME!

3. Big Picture Data Pipelines

Big Picture Data Pipelines

The thing that breaks, and what it is actually made of.

What even is a Data Pipeline?

Given the potential pitfalls, a good data pipeline is built to combat or insulate our data from these effects
- A clear, clean, source of truth with well determined lineage
To maintain this, most tasks are usually automated
- The idea is to take data in a raw form and move it through the pipeline into a state where downstream users can easily use it to answer business questions
As such, a pipeline:
- Ingests information from various sources
- Cleans it up/standardizes it, and combines it
- Organizes for relevant business decisions

Mission Objective #1

Zoomed out, creating, modifying, and managing company-wide data pipelines is likely outside your responsibilities
Nonetheless, you likely have data or want access to data flowing through those pipelines
May also have your own data desires or needs to communicate to the IT or data team
So understanding a bit of the overall architecture and vocabulary used for these large-scale pipelines can be useful for communication

ETL and ELT

The letters E, T, and L stand for:
- Extract: pulling the data that you want to work with
- Transform: cleaning up and standardizing that data
- Load: moving that data into a single source of truth database, commonly called a warehouse
Historically, ETL was also the common ordering for doing these operations, with cleaning and standardizing happening before loading to the warehouse
More recently, the pattern has largely shifted to ELT: first loading the raw data into the warehouse, and then cleaning and transforming it there
- Largely driven by easily accessible (and scalable) compute in cloud warehouses

Modern Pipelines

Movement Timings

Each arrow in the previous diagrams represents a movement of data
How often should data be moved through the pipeline?
- Batch loading transports entire blocks of information through the pipeline together.
  - Run on some interval, e.g. hourly or daily
  - Dashboards or reports do not get updated until the end of each interval
- Stream loading transports individual pieces of information through the pipeline as they arrive or are generated
  - Depending on data throughput, generally more computationally expensive, and aggregates can be trickier
  - Dashboards and reports are as close to live as they can be

A Question of Schema

Databases, unlike spreadsheets, maintain a strict schema, on a per-table basis
Each column can generally hold only a single type of data
All columns must contain the same number of rows
Pros:
- Downstream consumers of the data know what to expect
- Helps eliminate egregious data cleanliness issues
Cons:
- Data sometimes changes form, requiring schema migrations
- Can be inflexible if a piece of information can take on multiple forms

Some Quick Shopping

Data Warehouses are terrific for serving as a single source-of-truth for an entire company or division, but can be unwieldy or potentially face governance issues of who is allowed to see what information
A data mart is a small slice of the data warehouse, potentially lightly customized for use by a specific team
- Populated after the warehouse has been updated
Gives smaller teams a coherent and manageable source-of-truth for their specific requirements and needs

Interacting with a Database

So, IT has granted you and your team access to a data mart customized for your needs. How do you use it?
For relational databases, the primary way of interacting with the database is through the scripting language SQL
SQL is a declarative language, so it reads more like plain-English than many scripting languages, but can still have a lot of special keywords to understand
Keywords in SQL are followed by extra information that provides context or details to the keyword
The keywords are not doing anything magical! Mostly just similar operations to what you might do in a spreadsheet program like Excel

Mission Objective #2

While you likely don’t need to often write or generate SQL yourself, you may need to view or verify written SQL from time to time
As such, it is useful to understand how to parse different SQL queries in a context that you better understand: Excel
So let’s unpack some Excel analogs to SQL keywords, and see how they’d appear in a query

ExSQL?

SQL Keyword	Description	Excel Equivalent
`SELECT`	Chooses the columns to show	Hiding columns or choosing table fields
`FROM`	Tell the database what table to look in	Selecting a tab name
`WHERE`	Filters the rows based on specific conditions	Clicking the drop-down filter on a column header
`JOIN`	Merging tables together using a matching ID	Running a `VLOOKUP` or `XLOOKUP`
`GROUP BY`	Collecting and collapsing similar values to summarize	Creating a pivot table
`ORDER BY`	Ordering the rows by a particular column	Sorting a table by a column
`AS`	Rename a column or table	Renaming a column

Unpacking a Query

Suppose we then have the following query. What might its equivalent actions be in Excel?
```
SELECT
  state, 
4  AVG(sq_ft) AS avg_ft
1FROM branches
2WHERE region = 'Northeast'
3GROUP BY state
5ORDER BY avg_ft DESC
```
1

Pull data from the branches table

2

Only keep rows that have Northeast as the region

3

Make a pivot table where each row will be a state

4

Compute the average sq_ft of all branch buildings across each state and rename to avg_ft

5

Order rows by decreasing size, so largest at top

SQL Ordering

You may notice that the top-down ordering of an SQL query is unfortunately not really the order you would undertake those actions
Main SQL keywords “execute” in the order of: FROM → JOIN → WHERE → GROUP BY → SELECT → ORDER BY
The most fundamental thing to understand here is that rows are filtered before any sorts of aggregates are computed

Your Turn

Yesterday, you were introduced to the Dunder-Mifflin simulated data set.
For this exercise, you’ll want to open the employees.csv and branches.csv into their own tabs in Excel
Can you compute the same results as the following SQL queries but in Excel?

SQL 1

SELECT 
    branch_name,
    salary
FROM branches
JOIN employees
    ON branches.manager_employee_id = employees.employee_id
WHERE 
    branches.state = 'NY'

branch_name	salary
Corporate HQ	168000
Rochester	63146
Albany	50186
Buffalo	65147
Utica	63639
Yonkers	61574

SQL 2

SELECT
    department,
    AVG(salary) AS dept_avg_sal
FROM employees
WHERE bonus_eligible = TRUE
GROUP BY department

department	dept_avg_sal
Sales	58957.4565
Warehouse	48910.5
Accounting	61051.1282
Corporate	251250.0
Human Resources	61463.5540
Management	85333.3333

Big To Small

While full, large-scale pipelines tend to use SQL and perhaps a bit of Python to shepherd data from location to location, that doesn’t have to be the only example of a data pipeline
We can also shrink things down to more local pipelines where we want the same sorts of effects:
- A clear source of truth
- Systematic and consistent cleaning and formatting conventions
- A clear data lineage that showcases exactly where each piece of information comes from
Going forward, we’ll play around with some tools that you already have at your disposal that may assist with this smaller, local pipelines

4. Building Mini-pipelines

Learning Objectives

What You’ll Leave With

By the end of this segment, you will be able to:

Map Excel’s Power Query’s three panes onto the ETL vocabulary.
Build a small repeatable pipeline that loads, cleans, and combines two messy CSV exports.
Distinguish a Merge from an Append, and say which one you need out loud.
Read the Applied Steps pane as data lineage, and refresh the whole chain with one click.
Decide when a mini-pipeline should stop being yours and become IT’s.

Get the Data

Same Dunder Mifflin data you downloaded yesterday. We’ll be in the session2 folder. If you don’t have it, you can grab it from Canvas → Modules.

File	Rows	What it is
`crm_customer_export.csv`	1,923	Customer list out of the CRM. Messy.
`erp_orders_export.csv`	4,665	Orders out of the ERP. Messy.
`regional_manager_tracker.csv`	155	Michael’s hand-kept sheet. Very messy.
`customers.csv`	2,172	The clean customer list. Join target.
`sales_order_lines.csv`	15,337	The system of record for revenue.

All Scranton, 2022. Yesterday you saw how these tables connect. Today you make the connection trustworthy.

Part 1: Doing ETL with Excel

Turns out you already have an ETL tool. It has been sitting in the Data ribbon this whole time.

The Vocabulary You Just Learned, In A Tool You Already Have

Yesterday you heard ETL: Extract, Transform, Load. It probably sounded like something IT does in a cold server room.

Excel’s Power Query is those three letters with a mouse attached. Same concepts, same vocabulary, no cold server room.

Why Not Just Do It By Hand?

You already clean data. You do it with copy, paste, find-and-replace, and a column of =TRIM().

Yes, that works.

But then, next month’s file arrives.

😱

Two Ways to Clean Data

By hand

You do all of it again. From memory. Slightly differently.

No record of what you did.

Build a Mini-pipeline ⭐

Next month’s file arrives. You click Refresh.

Every step is written down, in order.

Where It Lives

Excel: Data ribbon → Get Data → From File → From Text/CSV.

Excel’s Power Query

🧠 Quick Quiz: ETL

In data engineering, what do the letters E, T, and L stand for?

A. Execute, Transfer, and Log
B. Extract, Transform, and Load
C. Export, Transition, and Link
D. Encryption, Troubleshooting, and Licensing

B. Extract, Transform, Load. When IT says “the ETL job failed,” they mean one of those three steps broke, and you now have a vocabulary for asking which.

Part 2: Three Files, Three Different Cleans

Each mess is a different lesson. That is not an accident.

File 1: The CRM Export

crm_customer_export.csv, 1,923 rows. Here is what is wrong with it.

Problem	Evidence	The fix
Inconsistent case	`riverside manufacturing llc`	`Transform` → `Format` → `Capitalize Each Word`
Stray whitespace	`Keystone Retail Associates`	`Transform` → `Format` → `Trim`
Duplicate rows	61 exact duplicates	`Home` → `Remove Rows` → `Remove Duplicates`
Missing key	97 rows (5.0%) have no `Acct ID`	Filter them out, or flag them. Decide on purpose.
Phone chaos	`(682) 486-2143`, `2953356785`, `209.577.2260`	Split, or strip non-digits
Missing fields	200 blank `Segment`, 109 blank `City`	Replace with `Unknown`, do not leave null

The 97 Rows Are The Lesson

The duplicates and the casing are annoying. The 97 missing keys are dangerous.

Join CRM to customers.csv on Acct ID and you get a clean result. Excel reports no error. Your PivotTable renders beautifully.

And 97 customers are not in it. Your revenue is quietly 5% light and nothing anywhere says so.

This is why “did it error?” is the wrong question. The right question is “how many rows went in, and how many came out?”

File 2: The ERP Export

erp_orders_export.csv, 4,665 rows. Different mess, different fixes.

Problem	Evidence	The fix
Money as text	`$141.58`	`Replace Values` to strip `$` and `,`, then set type `Decimal`
US date strings	`10/13/2022`	Set type `Date` using Locale → English (United States)
Shouting status	`FULFILLED`, `RETURNED`, `CANCELLED`	`Format` → `Capitalize Each Word`
Blank ship dates	175 blanks	These are the cancelled ones. Keep the blank, it means something.
Duplicate rows	93 exact duplicates	`Remove Duplicates`

Sum that AMT column as-is and you get zero. It is text. Excel is not being difficult, it is being correct.

Using Locale Is The Whole Ballgame

10/13/2022 is unambiguous. 01/05/22 is not.

Is that January 5th or May 1st? The file will not tell you. Power Query will guess based on your machine’s regional settings, and it will guess silently, and it may guess differently on your colleague’s laptop in a different region.

Transform → Data Type → Using Locale... → pick the locale on purpose.

File 3: The Tracker

regional_manager_tracker.csv, 155 rows. This one does not have a clean fix, and that is the point.

Problem	Evidence
Free text in a number column	`~11`, `a dozen`. 23 of 155 rows, 14.8%
Typo’d names	`Redwood Financial Goup`, `Conerstone Retail`, `Summi Hospitality`
Two-digit ambiguous dates	`01/05/22`
No order number at all	There is no key. None.
Totals that are wrong	About 2 in 5 disagree with the system of record

You cannot join this to anything by key, because there is no key. Welcome to every hand-kept spreadsheet in your company.

So What Do You Do With It?

Power Query has an answer, and it is a little bit magic: fuzzy matching.

Home → Merge Queries → check Use fuzzy matching to perform the merge → set a similarity threshold.

Summi Hospitality Co will match Summit Hospitality Co at about 0.9 similarity.

Fuzzy matching is a triage tool, not a fix

It will also confidently match the wrong thing. Use it to find the rows a human needs to look at, never to silently repair data you then report on. If you fuzzy match your way to a revenue number, you have invented a revenue number.

🧠 Quick Quiz: Which Clean?

Your AMT column contains $1,234.56 and every attempt to sum it returns 0. What happened?

A. The column has duplicate rows that cancel each other out.
B. The values are text, not numbers, because of the dollar sign and comma.
C. Excel's calculation mode is set to manual.
D. The column contains blank rows that break the SUM range.

B. A leading $ makes the whole value text. SUM politely ignores text and returns zero. Strip the $ and , with Replace Values, then set the type to Decimal Number. This is the single most common “the spreadsheet is broken” support ticket in existence.

Part 3: Combine

Two ways to put data together. Managers who can name the difference get better help from IT. 🧠

The Only Two Options

Merge = wider

Match rows across tables on a shared key. Adds columns.

“Attach each order to its customer’s segment.”

SQL calls this a JOIN.

Append = taller

Stack rows from tables with the same shape. Adds rows.

“Combine Q1, Q2, Q3, and Q4 into one year.”

SQL calls this a UNION.

How do you Decide?

Merge Has A Trap

When you Merge, Power Query asks for a Join Kind. The default is Left Outer.

That default is usually right, and it is also how you lose those 97 keyless rows without noticing.

After every Merge, do exactly one thing: look at the row count in the status bar. Did it change? Did it change the way you expected?

If your 1,923 rows became 1,826, you just silently dropped 97 customers. If they became 2,400, you just duplicated something.

🧠 Quick Quiz: Merge or Append

You have twelve monthly sales exports, all with identical columns, and you need one table for the year. Which operation?

A. Merge, joining each month to the next on the date column.
B. Append, stacking all twelve into one table.
C. Merge with fuzzy matching, since the file names differ.
D. Neither. You need IT to build a database first.

B. Same shape, different rows, so you stack them. Append. Better still, point Power Query at the folder with Get Data → From Folder and it appends every file in there automatically, including next month’s when you drop it in.

Part 4: Hands-On

Twenty minutes. Build the thing.

Try It: Load and Clean the CRM

Get crm_customer_export.csv into Excel and make it trustworthy.

Target: a clean customer table with no duplicates, consistent names, and a decision made about the missing keys.

Steps

Data → Get Data → From File → From Text/CSV → pick the file → Transform Data (not Load).
Select Account Name → Transform → Format → Trim, then Format → Capitalize Each Word.
Home → Remove Rows → Remove Duplicates. Watch the row count: 1,923 drops to 1,862.
Set Acct ID type to Whole Number. The blanks become null.
Right-click Acct ID → Remove Empty or add a filter. Either way, you decided. Note how many you dropped.
Home → Close & Load To... → Only Create Connection.

You just built an ETL pipeline. That is Extract, Transform, and Load, in six clicks.

Try It: Merge to the Customer List

Attach each CRM row to its canonical record in customers.csv, and find out what falls out.

Hint: you need both tables loaded as queries before you can merge them.

Steps

Load customers.csv the same way. Close & Load To... → Only Create Connection.
Data → Get Data → Combine Queries → Merge.
Top table: your CRM query. Bottom: customers. Click Acct ID and customer_id to pair them.
Join Kind: Left Outer. OK.
Expand the new column (the ⇄ icon) and pick segment and region.
Filter the expanded segment column for null.

Those nulls are your keyless rows. They are in your report, contributing nothing, and if you had summed revenue by segment you would never have seen them.

Try It: Reconcile the Tracker

The hard one. Michael’s sheet says Scranton did $43,020.24. Does the system of record agree?

Hint: there is no key. You will need fuzzy matching, and you will not fully succeed. That is the finding.

Steps

Load regional_manager_tracker.csv. Look at Qty. Set it to Whole Number and watch 23 rows turn into errors.
Those errors are ~11 and a dozen. Keep Errors to see them. This is your data quality report.
Merge the tracker to customers on Client ↔︎ customer_name, with fuzzy matching on.
Drop the threshold to 0.8. Watch Summi Hospitality Co find Summit Hospitality Co.
Now try to check the totals against sales_order_lines. You cannot, cleanly. There is no order number to join on.

That is the answer. The sheet cannot be reconciled, because it was never built to be. About 2 in 5 of its totals are wrong and there is no way to prove which ones from the file alone.

Debrief

Hands up. Who got a different row count than the person next to them?

That is the entire reason Applied Steps exists.

Part 5: Refresh and Lineage

The part where it stops being a chore and starts being infrastructure.

Your Steps Were Being Written Down

Look at the right-hand pane. Applied Steps. Every click you made is there, in order, named.

Click any step. The preview jumps back to how the data looked at that moment. You can insert a step in the middle. You can delete one. You can rename them so a human can read them.

This Is Lineage

In Potential Data Pitfalls we said lineage is the map of a number’s journey from origin to report, and that if nobody can trace it, you have folklore.

Applied Steps is that map. For this one pipeline, you can answer all three questions:

Where did it come from? The Source step names the file.
What was done to it? Six steps, in order, each named.
Who changed it and when? The workbook, and its version history.

You did not build a cleaned spreadsheet. You built a documented, re-runnable, auditable cleaned spreadsheet. Those are different objects.

The Refresh

Next month, Scranton sends a new export. Same columns, new rows.

You drop it in the same path and click Refresh.

Trim, capitalize, dedupe, merge, expand. All of it. In about a second. Exactly the way you did it last month, because it is the same steps, not your memory of them.

That is the “automate” half of the objective. And it is why your February number is comparable to your January number, which is a thing your current process almost certainly cannot promise.

Part 6: When Do You Hand This to IT?

Know when you have outgrown your own pipeline.

You Built a Pipeline. Now What?

Congratulations. Now, when should it stop being yours?

Three Signals

Stakes. Volume. Ownership. If any one of those trips, it is time.

Not The Signals

Things that do not mean you should hand it off:

❌ “The file got past 50,000 rows.” Row counts are not a governance policy.
❌ “IT told us to stop using Excel.” That is a turf memo, not an engineering decision.
❌ “I want it fully automated so nobody ever checks the data again.” Nobody should ever want this.

The real trigger is always about consequence, not size.

Volume: Let’s Be Honest

The full Dunder Mifflin sales_order_lines is 215,485 rows. Excel’s limit is 1,048,576.

So it fits. It fits fine.

Now put a VLOOKUP in a column next to it and try to scroll. Now refresh it. Now email it to somebody.

“It fits” and “it works” are different claims. The limit is not the row count, it is the moment your laptop fan becomes part of the meeting.

🧠 Quick Quiz: The Handoff

At what point does a mini-pipeline in Power Query become something you hand to the data or IT team?

A. As soon as the file exceeds 50,000 rows or needs more than 10 columns altered.
B. Only when IT explicitly mandates that local Excel usage stop.
C. When the report drives high-stakes decisions, handles volumes that freeze local machines, or has no backup owner if its creator leaves.
D. When you want it fully automated so no human ever has to check data quality again.

C. Stakes, volume, ownership. A is arbitrary. B is politics. D is the fantasy that gets companies into trouble, because someone always has to check the data. The honest test is: if this breaks while I am on vacation, what happens?

What To Actually Say To IT

You are not asking them to “fix your spreadsheet.” You are handing over a specification.

You can now say:

“I have a mini-pipeline that extracts the CRM and ERP exports, transforms them by trimming names, deduping 61 rows, and coercing the AMT text into decimals, then merges them to the customer dimension on Acct ID with a left outer join. About 5% of rows have no key and currently drop out. It drives the regional revenue number the VP sees monthly, and I am the only person who knows how to run it.”

That is a requirements document. You just wrote it by clicking around in Excel for twenty minutes.

Technique Summary

Technique	What It Does
`Get Data` → `From Text/CSV`	Extract. Start of every pipeline.
`Transform Data`	Opens the editor. Not `Load`.
`Trim` / `Capitalize Each Word`	Kills whitespace and casing drift
`Remove Duplicates`	Kills double-counting
`Replace Values` + `Decimal Number`	Turns `$141.58` into money
`Data Type` → `Using Locale`	Makes ambiguous dates unambiguous, on purpose
`Merge Queries`	Join on a key. Wider.
`Append Queries`	Stack same-shaped rows. Taller.
Fuzzy matching	Triage for typo’d keys. Never for silent repair.
`Get Data` → `From Folder`	Appends every file in a folder, forever
Applied Steps	Your lineage, written down
`Refresh`	Runs the whole chain again, identically

The Big Ideas

You already own an ETL tool. Extract, Transform, Load has been in the Data ribbon the entire time, and now you can name what it is doing.
Check the row count after every join. A broken join does not error. It just returns less, and your report renders anyway.
Merge is wider, Append is taller. Say the right word to IT and get help in an hour instead of a fortnight.
Applied Steps is lineage. The pipeline documents itself, which is the only kind of documentation that ever survives contact with a deadline.
A sheet with no key cannot be reconciled. Not by you, not by Power Query, not by IT. That is a process fix, upstream, not a data fix.
Hand it off for stakes, volume, or ownership. Never for row count, never because of a memo.

Where This Started

This morning opened with two numbers that disagreed and no way to tell which was right.

You can now clean the exports that feed them, merge them on a key, watch the row count, and read the Applied Steps back like a receipt. The disagreement is still possible. It is no longer a mystery.

5. References and Resources

Resources

Dunder Mifflin Paper Company dataset. https://dataengineering.education/files/workshop_data.zip
Wickham, H. (2014). “Tidy Data.” Journal of Statistical Software 59(10). The tidy rules from Session 1, and what messy data looks like.
Kimball, R. and Ross, M. The Data Warehouse Toolkit, 3rd ed. Wiley, 2013. Chapter 1, on why a single source of truth is a design decision rather than an accident.
Microsoft. “Data lineage in Microsoft Purview.” https://learn.microsoft.com/en-us/purview/concept-data-lineage
Microsoft. “Power Query documentation.” https://learn.microsoft.com/en-us/power-query/
Microsoft. “Merge queries overview.” https://learn.microsoft.com/en-us/power-query/merge-queries-overview
Microsoft. “Fuzzy merge.” https://learn.microsoft.com/en-us/power-query/fuzzy-matching
Microsoft. “Import data from a folder with multiple files.” https://learn.microsoft.com/en-us/power-query/connectors/folder

Data Architecture

0. Meet Your Instructors

Jed Rembold, Ph.D.

Background & Research

Teaching

Education

Lucas P. Cordova, Ph.D.

Background & Research

Teaching

Education

Industry Experience

1. Pitfalls, Failures, and Governance

Learning Objectives

What You’ll Leave With

Part 1: The Monday Morning Disagreement

Yesterday You Were Michael Scott

Same Question, Two Answers

The hand-kept tracker

The system of record

Look at the Actual Sheet

Spot the Pitfalls

The Pitfalls

Part 2: Where It Breaks

This Is What Messy Looks Like

The Stack Underneath

Pitfall 1: No Single Source of Truth

Pitfall 2: Key Drift

Pitfall 3: Format Chaos

Pitfall 4: Duplicates

Pitfall 5: No Lineage

Pitfall 6: Bus Factor

🧠 Quick Quiz: Lineage

Part 3: Three Questions to Ask

Most Data Failures Are Not Technical

The Three Questions

Why These Three

🧠 Quick Quiz: Where to Start

What We Just Did

The Big Ideas

Up Next

2. Making it Personal

Making it Personal

Think-Pair-Share: Pitfalls

Think-Pair-Share: Lineage

Think-Pair-Share: Health Checks

Process Debriefing

BREAK TIME!

3. Big Picture Data Pipelines

Big Picture Data Pipelines

What even is a Data Pipeline?

Mission Objective #1

ETL and ELT

Modern Pipelines

Movement Timings

A Question of Schema

Some Quick Shopping

Interacting with a Database

Mission Objective #2

ExSQL?

Unpacking a Query

SQL Ordering

Your Turn

SQL 1

SQL 2

Big To Small

4. Building Mini-pipelines

Learning Objectives

What You’ll Leave With

Get the Data

Part 1: Doing ETL with Excel

The Vocabulary You Just Learned, In A Tool You Already Have

Why Not Just Do It By Hand?

😱

Two Ways to Clean Data

By hand

Build a Mini-pipeline ⭐

Where It Lives

🧠 Quick Quiz: ETL

Part 2: Three Files, Three Different Cleans

File 1: The CRM Export