Under the Hood & What Can Go Wrong

ECON250 - Big Data Analytics | Week 2

Oleh Omelchenko

2026-02-10

Recap

Block 1: 5 minutes

Brief recap of Week 1 practice, then straight into new content.

What happens when the data itself is wrong?

Last week: connected to BigQuery, ran queries, saw bytes scanned, created personal datasets, explored partitioning.

Today: how BigQuery works under the hood, and what goes wrong when data isn’t clean.

Quick show of hands: “Who checked the bytes processed indicator last week?”

Most analytics mistakes come not from bad SQL but from bad data — the query runs fine, returns a result, and that result is wrong.

Transition: “You’ve used BigQuery. Now let’s look under the hood.”

Key idea 1: Columnar storage

Row storage (traditional DB)

[Anna, 25, Kyiv, 45000]
[Boris, 31, Lviv, 52000]
[Clara, 28, Odesa, 48000]

To compute AVG(salary) — must read every column of every row

Column storage (BigQuery)

name:   [Anna, Boris, Clara]
age:    [25, 31, 28]
city:   [Kyiv, Lviv, Odesa]
salary: [45000, 52000, 48000]

To compute AVG(salary) — reads only the salary column

Before showing this slide, ask: “When you SELECT just 2 columns from a table with 20 columns, does BigQuery read the whole table or just those 2 columns?”

Wait for hands. Most will guess correctly — they saw SELECT * scanned more bytes last week. Confirm, then show the slide as the explanation.

You already saw this

-- Scans ALL columns
SELECT * FROM `bigquery-public-data.thelook_ecommerce.order_items`
-- ~13 MB

-- Scans only 3 columns
SELECT order_id, sale_price, status
FROM `bigquery-public-data.thelook_ecommerce.order_items`
-- ~4.5 MB

With columnar storage, you only pay for the columns you read.

This is also why LIMIT doesn’t save money. BigQuery reads full columns first, then truncates.

Key idea 2: Distributed parallel execution

Traditional databases need indexes to avoid reading everything.

BigQuery does the opposite — distributes data across thousands of machines, scans everything in parallel.

Full table scans are not a problem in BigQuery — that’s the intended design.

This is Dremel’s design (2006): tree-based execution, leaves scan data, intermediate nodes aggregate, root returns result. Don’t go deeper than the one-liner — the concept is enough.

Key idea 3: Compute and storage are separate

Traditional warehouse

Storage + compute on same machines

More data → need more compute

More users → everything slows down

BigQuery

Storage and compute are independent

Store petabytes, pay storage only

Query → BQ assigns compute on demand, then releases it

“Slots” = BQ’s compute unit. You don’t manage them. On-demand pricing = you pay per bytes scanned, not per compute time.

What this means for how you write SQL

Traditional DB	BigQuery
Create indexes for speed	No indexes
SELECT * is fine on small data	SELECT * wastes money
LIMIT reduces work	LIMIT doesn’t reduce bytes scanned
Optimize for row lookups	Optimize for column selection

Transition: “So data lives in columnar storage. But how did it get there? That’s where problems start.”

How data gets into BigQuery

Data arrives via pipelines (ETL, ELT, CSV uploads). BigQuery enforces schema and types at load time.

But BigQuery doesn’t validate whether the data makes sense — if the source data has problems, BigQuery stores them as-is. Your queries will run fine and return results, but those results can be wrong.

Briefly mention EL/ETL/ELT if students ask — but the key point is simpler: data gets loaded by some automated process, and that process introduces problems. BigQuery won’t crash on bad data — it will happily store ‘29.99’ as a STRING if the CSV was loaded that way.

“The data you analyze was loaded by someone through some process. Understanding that process tells you what problems to look for.”

Transition: “So now you know how BigQuery works. Let’s talk about what goes wrong with the data inside it.”

What Can Go Wrong

Block 3: 25 minutes

Two sources of data quality problems: systems (pipelines, ingestion) and humans at scale (user-generated content). Both are different from the small-dataset typos students are used to.

Engagement option — Think-pair-share: “Turn to your neighbor. 60 seconds: what data quality issues have you encountered in coursework or projects?”

Data quality issues you already know

From stats and econometrics courses:

• Missing values (NULLs, blanks)
• Typos (“Kyiv” vs “Kyv”)
• Wrong types (numbers as text)
• Outliers (salary of 999,999,999)

With smaller datasets, you could fix these by opening the file, scrolling through, spotting the problem.

This list should feel familiar — set up the contrast with what comes next.

Let’s share experience

What’s the worst data problem you’ve dealt with?

Think-pair-share: 3-4 minutes total. Let them actually discuss for a full 60 seconds — resist the urge to cut it short.

Then cold-call 3-4 pairs: “What did you two come up with?” Common answers: missing values, encoding issues, merged cells in Excel, wrong date formats, duplicates.

After collecting: “Good — you’ve all dealt with messy data. But it was small enough to see. Now imagine those same problems in a dataset with 1.4 million rows.”

What changes at scale

You can’t scroll through 1.4 million rows

At scale, new categories of problems appear that don’t exist in small datasets.

“In 100 rows, you spot a duplicate by scrolling. In 1.4 million rows, you need a query — and that query scans data you pay for.”

Small problems get buried

A 0.1% duplicate rate

• In 1,000 rows: 1 duplicate — easy to spot
• In 10,000,000 rows: 10,000 extra rows — invisible without a query

A misspelled category

• In 200 rows: obvious when you scan the column
• In 50,000 distinct values: buried

“10,000 phantom rows could shift your revenue report by hundreds of thousands of hryvnias.”

Where do problems come from?

From systems

Pipelines, ingestion processes, ETL jobs — automated processes that create errors on their own

From humans at scale

User-generated data: millions of people entering free-text values, each in their own way

Both are different from the small, hand-entered datasets you’ve cleaned before.

This is the key framing shift. Not just “systems cause problems” — user-generated data at scale has its own category of messiness. A real estate platform with 1.4M listings entered by thousands of different agents is a good example.

System problem: Ingestion duplicates

An ETL pipeline loads orders every night. On a network timeout, the pipeline retries automatically — and the same 500 orders get loaded twice.

• Every column is identical — no way to tell which copy is “real”
• Your revenue report double-counts those orders

“Any pipeline that retries on failure can produce duplicates. At one company, a nightly retry duplicated 3 days of transactions. Nobody noticed for 2 weeks — until the quarterly revenue didn’t match the bank account.”

What happened here?

A company’s quarterly revenue report:

Quarter	Revenue
2023 Q1	$4.2M
2023 Q2	$4.5M
2023 Q3	$4.8M
2023 Q4	$5.1M
2024 Q1	$4.3M
2024 Q2	$4.1M

The CEO asks: “Why is revenue declining?” What data problems could explain this?

Discussion: 2-3 minutes. “Talk to your neighbor — come up with at least two data-related explanations. 60 seconds.”

Collect answers from 3-4 pairs. Typical guesses: seasonal effects, lost customers, economic downturn — push back on these: “Those are business explanations. What if the business is fine and the data is wrong?”

Then move to next slide for data-level explanations.

Many possible data explanations

Explanation	What went wrong
Ingestion duplicates	2023 had duplicate rows inflating totals, later fixed
Schema drift	Column meant gross in 2023, net in 2024 after a migration
Source change	A data feed was added or dropped between years
Currency mixing	Some rows switched from USD to local currency mid-2024
Late-arriving refunds	2024 includes retroactive returns; 2023 doesn’t yet

Same symptom, five different root causes — and each needs a different fix.

The point is NOT to guess the “right” answer. The point is that declining revenue in a report could be any of these — and you can’t tell which one just by looking at the final numbers. You need to go back to the source data and profile it.

“The most dangerous data quality issue is the one where your query runs fine, returns a plausible answer, and that answer is wrong. No error, no warning — just a bad decision based on bad numbers.”

If students ask “which one was it?” — “In this example, it was schema drift. But in practice you won’t know until you investigate.”

Example: Schema drift

A column called revenue existed since 2023.

In 2024, after a system migration, it started meaning net revenue (after returns). Before, it was gross.

• 2023 revenue: gross (higher)
• 2024 revenue: net (lower)
• “Revenue dropped 15%” ← wrong

This is the concrete example for the table on the previous slide. The column name didn’t change, the data type didn’t change, the query didn’t change — only the meaning changed. And nobody documented it.

“Same column name, same type, different definition. No error message will save you from this.”

System problem: Type coercion at ingestion

CSV files have no type information — everything is text. When you load a CSV, BigQuery’s auto-detection infers column types, but it doesn’t always get them right.

Transition into the real example they experienced.

You already experienced this

In practice, one group loaded a CSV where a numeric column contained "NA" values.

BigQuery couldn’t infer it as INT64 or FLOAT64 — so it loaded the entire column as STRING.

SELECT price * 1.2 AS with_vat FROM orders
-- Error: No matching signature for operator *
-- Found: STRING * FLOAT64

Fix: SAFE_CAST(price AS FLOAT64) — converts valid numbers, returns NULL for "NA".

This is a real example from their own class. “One ‘NA’ string in a column of 10,000 numbers is enough to make BigQuery treat the whole column as text.”

Human problem: User-generated data at scale

Imagine a real estate platform — thousands of agents posting listings.

Each agent enters the address, price, and property details their own way:

• Addresses: same region spelled differently depending on source language and abbreviation style
• Prices: some in UAH, some in USD, some in EUR — all in one price column
• Listing types: hundreds of distinct values mixing Ukrainian, Russian, and English

This directly references the flatfy dataset they’ll work with in practice. Real numbers: 76% USD, 24% UAH, 0.2% EUR. 322 distinct section values including “оренда” (UA), “аренда” (RU), “daily rent” (EN). Four spellings of Kyiv region alone.

“Nobody typed the wrong value on purpose. But when you have thousands of people entering data with no standardization, you get thousands of variations of the same thing.”

Human problem: Multi-source conflicts

Same region, same dataset, different conventions:

Variant	Source convention
Киевская область	Full Russian name
Киевская обл.	Abbreviated Russian
Київська область	Full Ukrainian name
Киевская	Shortened form

One GROUP BY produces four rows instead of one. How many listings are you miscounting?

These are all real variants from the flatfy dataset. Actual counts: “Киевская область” (362K), “Киевская” (64K), “Киевская обл.” (24K), “Київська область” (16K) — 466K total, fragmented across four rows. Students will discover these exact numbers in practice.

“After a company merger or data migration, you get multiple conventions in the same table. Here it happened because the platform aggregated listings from different regional sites.”

Late-arriving changes

Scenario: E-commerce platform. Order placed and shipped in January.

Customer requests a refund in March. Chargeback processed in April.

• Your January revenue report was correct — at the time
• After the refund, January’s numbers change retroactively
• How far back do you restate?

This is more relatable than mobile app events. Refunds, chargebacks, returned goods — all modify historical data after the fact.

“If you run the same ‘January revenue’ query in February and in May, you’ll get different numbers. Not a bug — just late-arriving corrections.”

Also applies to insurance claims, tax adjustments, logistics corrections (shipment marked delivered but actually returned).

Empty strings aren’t NULL

-- This finds NULLs
SELECT * FROM orders WHERE city IS NULL
-- Returns 0 rows ✓

-- But...
SELECT * FROM orders WHERE city = ''
-- Returns 2,847 rows!

Empty strings are not NULL. They pass IS NOT NULL checks.

Your completeness report says “no missing cities” — but 2,847 are blank.

“Always check for both. A column can have zero NULLs and still be full of empty strings, ‘N/A’, ‘unknown’, and 0-as-placeholder.”

Before you analyze anything

Every new dataset gets the same treatment:

1. Profile — what do we actually have?
2. Clean — fix what’s broken
3. Analyze — answer the question

Skipping steps 1 and 2 is how wrong numbers end up in reports.

Transition: “So how do you profile a dataset you can’t look at? SQL — and a few tools built into BigQuery.”

Profiling with SQL

Block 4: 20 minutes

Core profiling patterns. Students apply these in practice — here we introduce the concepts and show the BQ tooling.

Profiling = asking structured questions about the data

Six categories of profiling questions:

1. Shape & completeness — what’s here, what’s missing?
2. Distributions — what values exist, how often?
3. Uniqueness & duplicates — is there a primary key?
4. Types & formats — is the data what it claims to be?
5. Value ranges & sentinels — are the numbers reasonable?
6. Cross-column logic — do related columns agree?

1. Shape & completeness

-- How complete is each column?
SELECT
  COUNT(*) AS total,
  COUNTIF(price IS NULL) AS null_price,
  COUNTIF(area_total IS NULL) AS null_area,
  COUNTIF(area_kitchen IS NULL) AS null_kitchen,
  COUNTIF(ceiling_height IS NULL) AS null_ceiling
FROM listings

Not all columns have the same NULL rate — some are 0%, others can be over 90%. A column that’s almost entirely NULL may not be worth analyzing.

One query tells you which columns have gaps — and what kind of gaps.

The key pattern: COUNTIF(column IS NULL) / COUNT(*) gives you NULL percentage per column.

Actual NULL rates from the flatfy dataset: price 0%, room_count 14%, area_total 9.3%, floor 42%, area_kitchen 54%, area_living 58%, ceiling_height 97.8% (only 32K out of 1.4M rows). At that point any aggregation on ceiling_height represents a tiny biased sample.

Always check for both NULLs and empty strings. Also check for placeholder values: “N/A”, “unknown”, 0, -1.

2. Distributions

Run this on every categorical column:

SELECT
  currency,
  COUNT(*) AS frequency
FROM listings
GROUP BY currency
ORDER BY frequency DESC

What to look for:

• Long tails — thousands of values appearing once (typos?)
• Near-duplicates — “UAH” and “uah” and “грн”
• Unexpected values — a currency of “test” or a blank

GROUP BY + COUNT + ORDER BY DESC — run this on every categorical column.

“If a category has exactly 1 row, that’s either a legitimate rare value or a typo. You need context to decide.”

3. Uniqueness & duplicates

-- First signal: do these match?
SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT listing_id) AS unique_listings
FROM listings
-- If total_rows > unique_listings → duplicates exist

-- Find them
SELECT listing_id, COUNT(*) AS copies
FROM listings
GROUP BY listing_id
HAVING COUNT(*) > 1

It gets harder when the same key has different values. Which row is correct?

“Exact duplicates are easy — just DISTINCT them away. The hard case is when the same business key has different values: one row says price is 50,000, another says 55,000. Which is right? You need timestamps or business logic.”

4. Types & format checks

Check column types in the BigQuery web UI: click the table → Schema tab.

Then verify the data actually matches:

-- How many values fail to convert to numbers?
SELECT
  COUNTIF(SAFE_CAST(price AS FLOAT64) IS NULL
          AND price IS NOT NULL) AS bad_prices
FROM listings

SAFE_CAST returns NULL on failure instead of crashing. Good for diagnosis.

The Schema tab in BigQuery’s web interface shows column names, types, and descriptions. Students can see this without needing INFORMATION_SCHEMA access.

SAFE_CAST is a diagnostic tool: “How many values in this ‘numeric’ column aren’t actually numbers?”

Engagement option — Error spotting: “This query uses CAST instead of SAFE_CAST. What happens if even one value isn’t a valid number?” (Answer: the whole query crashes.)

Find the bug

This query should count listings with invalid prices:

SELECT COUNT(*) AS bad_prices
FROM listings
WHERE CAST(price AS FLOAT64) IS NULL
  AND price IS NOT NULL

What’s wrong?

Error spotting: 2-3 minutes. Let them think individually or in pairs.

Take 2-3 guesses, then reveal: CAST (not SAFE_CAST) will crash the entire query the moment it hits a non-numeric value. It won’t return NULL — it throws an error. The query never finishes.

Fix: replace CAST with SAFE_CAST. “One word difference. CAST crashes, SAFE_CAST diagnoses.”

This sets up the cleaning section — they’ve felt the problem before seeing the tool.

Side note you can mention: for visual profiling, BQ can send query results to Looker Studio (Explore Data → Explore with Looker Studio). Quick histograms, outlier spotting. No setup needed.

5. Value ranges & sentinels

SELECT MIN(price), MAX(price) FROM listings

What if MIN is 1 and MAX is in the billions?

• Price = 1 often means “contact us” — the field was required, so the agent typed something
• Price in the billions — typos, test data, or someone typing random digits
• Area of 9,999,999,999 m² — a classic “fill with nines” placeholder

These pass IS NOT NULL, > 0, and even basic range checks.

Sentinel values are the hardest to catch automatically. In the flatfy data: 1,856 listings with price = 1, 85 with price > 100M, max price = ₴380 billion. One listing has price = 1234567890 — someone literally typed digits in order. area_total max = 9,999,999,999.

“Your WHERE price IS NOT NULL AND price > 0 filter won’t catch price = 1. You need to think about what values are reasonable for the domain.”

6. Cross-column logic

-- Apartment on floor 8 of a 5-story building?
SELECT COUNT(*) FROM listings
WHERE floor > floor_count

-- Total area smaller than living + kitchen area?
SELECT COUNT(*) FROM listings
WHERE area_total < area_living + area_kitchen

Each column looks reasonable on its own — you only see the problem when you compare them.

Cross-column validation is the step most people skip. In the flatfy data: 935 rows where floor > floor_count (physically impossible), 9,606 rows where area_total < area_living + area_kitchen (1.7% of rows where all three fields exist).

“Profiling isn’t just one column at a time. When columns have logical relationships, check them.”

What does this number actually mean?

SELECT AVG(price) FROM listings
-- Returns a number. What does it mean?

If the price column contains apartment sales in USD alongside monthly room rentals in UAH, the average is meaningless — it mixes currency, deal type, and property type in one AVG().

Profiling tells you what’s being mixed before you aggregate.

This is the most dangerous kind of data problem — it produces a result that looks reasonable. In the flatfy data, AVG(price) = 471,097. That could sound like a real average property price. But it’s averaging apartment sales in Kyiv (USD, median $42K) with monthly room rentals in Lutsk (UAH, median ₴5,500).

“Before you aggregate anything, you need to know what’s being mixed together. That’s what profiling is for.” This slide ties together all the profiling techniques: you needed distributions (to see multiple currencies), value ranges (to spot outliers), and shape checks (to understand what deal types exist) — all before writing a single AVG().

You just ran a profiling query. Now what?

| city      | listings | avg_price |
|-----------|----------|-----------|
| Киев      |    4,980   |   42,300  |
| Київ      |    2,809   |   39,800  |
| киев      |    109   |   41,500  |
| NULL      |    40   |   35,200  |

Same city, three rows — Russian, Ukrainian, lowercase Russian. The GROUP BY splits what should be one group into three.

Open discussion: 2-3 minutes. Hands up, take answers.

The actual numbers (don’t reveal — students will find these in practice): “Киев” (308K), “Київ” (12K), “киев” (373). The avg_price values are illustrative.

Expected observations:

• Three spellings of one city (case + language variants)
• NULL row — genuinely missing data
• avg_price looks plausible for each row, so you might not notice the split unless you examine row counts

The Kyiv region field is even worse: four variants totaling 466K rows. Students will discover the full extent in practice.

“Every one of those has a specific SQL tool. Let’s go through them.”

Cleaning Patterns

Block 5: 10 minutes

Brief overview. Students apply all of these in practice. Here we name the tools and connect each to a problem from Block 3.

Cleaning tools

Problem	Tool
Strings that should be numbers	CAST() / SAFE_CAST()
Inconsistent categories	CASE WHEN, LOWER(), TRIM()
Missing values	COALESCE(), IFNULL()
Duplicates	DISTINCT or ROW_NUMBER()
Sentinel values (price = 1)	WHERE + domain logic
Cross-column conflicts	WHERE floor > floor_count

“You’ll use every one of these in practice. For now, know which tool matches which problem.”

Sentinel values and cross-column checks don’t have a dedicated function — they require domain knowledge translated into WHERE clauses. That’s why profiling comes first: you need to understand the data before you can write the right filters.

SAFE_CAST: one bad row shouldn’t crash your query

-- CAST fails on ANY invalid value
SELECT CAST(price AS FLOAT64) FROM listings  -- Error on "NA"

-- SAFE_CAST returns NULL for invalid values
SELECT SAFE_CAST(price AS FLOAT64) FROM listings  -- Works

Remember the "NA" problem from practice? This is the fix.

SAFE_ prefix is a BigQuery pattern: SAFE_CAST, SAFE_DIVIDE, etc. Use SAFE_ variants when working with data you haven’t fully profiled.

CASE WHEN: standardize inconsistent values

SELECT
  CASE
    WHEN LOWER(TRIM(city)) IN ('kyiv', 'kiev', 'київ') THEN 'Kyiv'
    WHEN LOWER(TRIM(city)) IN ('lviv', 'львів', 'lwów') THEN 'Lviv'
    ELSE city
  END AS city_clean
FROM listings

LOWER() + TRIM() handles case and whitespace. CASE WHEN maps all the variants to one standard spelling.

“CASE WHEN is the workhorse of data cleaning. Same logic applies to product categories, status codes, any free-text field that should be categorical.”

COALESCE: pick the first non-NULL value

SELECT
  COALESCE(area_total, area_living + area_kitchen, 0) AS area
FROM listings

Tries each argument left to right. Returns the first non-NULL.

What to do with missing values:

• Fill → sensible default exists
• Filter → row is useless without the value
• Flag → keep, but mark as incomplete

Week 3 goes deeper on how NULLs affect aggregations — SUM, AVG, COUNT all treat NULLs differently.

Cleaning with CTEs

-- Each CTE handles one cleaning concern
WITH fix_types AS (
  SELECT *, SAFE_CAST(price AS FLOAT64) AS price_num FROM raw_listings
),
standardize AS (
  SELECT *,
    CASE WHEN LOWER(TRIM(city)) IN ('kyiv', 'kiev') THEN 'Kyiv'
         ELSE city END AS city_clean
  FROM fix_types
),
deduplicated AS (
  SELECT * EXCEPT(row_num) FROM (
    SELECT *, ROW_NUMBER() OVER (
    PARTITION BY listing_id ORDER BY insert_time DESC
    ) AS row_num
    FROM standardize
  ) WHERE row_num = 1
)
SELECT * FROM deduplicated

Each CTE is one cleaning step. You can read each one separately and debug it on its own. This is the structure they’ll build in practice.

ROW_NUMBER for dedup: window functions are Week 4, but this recipe is too useful to skip. “For now, read it as: for each listing_id, keep the most recent row.”

Save the result as a VIEW (always re-runs cleaning, always current) or a TABLE (snapshot, faster to query, but stale).

Wrap-up

Block 6: 5 minutes

What we covered

BigQuery architecture:

• Columnar storage → SELECT only what you need
• Parallel execution → no indexes, full scans by design
• Separate compute and storage → serverless, scales on demand

Data quality at scale:

• Problems from systems (ingestion, schema drift) and from humans at scale (user-generated data)
• You profile with SQL, not your eyes
• Profile → Clean → Analyze

Practice sessions

You’ll work with a real dataset: ~1.4 million real estate listings from a Flatfy.

User-generated data with real quality issues — your task is to profile and clean it.

Practice 1: Profile — what’s actually in this data?

Practice 2: Clean — build a CTE pipeline, save a cleaned view

Some students may have worked with this dataset (flatfy) in Python before. If anyone mentions it: “Good — you know it’s messy. Now you’ll clean it with SQL instead of pandas. Same problems, different tools, much larger scale.”

For those who used pandas: “In Python, you’d do df.describe(), df.isnull().sum(), df.duplicated(). In SQL, we do the same thing — just with different syntax. The thinking is identical.”

Reminders

• Assignment 1 deadline this week (8+2 pts)
• Readings: BQ DG Ch3 (data types, functions), SQL DA Ch2 (data preparation)
• Next week: Aggregations, business metrics, and NULL handling

Questions?

o_omelchenko@kse.org.ua