Assignment 2 — Sensor Data into SQLite + DuckDB

Module: Week 02 · Released: L4 · Due: ~1 week later · 6 points on Canvas.

Goal

Design a relational schema for a real multi-sensor time-series dataset, load it into SQLite, and answer a set of engineering questions with SQL. Then store the same data as partitioned Parquet and query it with DuckDB, comparing effort and performance. You will experience firsthand the OLTP vs. OLAP / row-store vs. column-store trade-off.

No server, no Docker, no accounts. sqlite3 ships with Python; duckdb, pandas and pyarrow are one uv add away. Everything in this assignment runs in a directory on your laptop. L3 taught these ideas on PostgreSQL and every one of them — keys, foreign keys, normalization, indexes, query plans, window functions — is the same idea in SQLite. Where the spelling differs, this page gives you the SQLite spelling.

Learning outcomes assessed

• Model engineering time-series in a normalized relational schema with correct types.

• Write non-trivial SQL: joins, aggregates, window functions, time bucketing.

• Reason about indexing and query plans.

• Convert data to Parquet and query it with DuckDB.

• Articulate when a column store or embedded engine is the right tool.

Dataset

Intel Berkeley Research Lab sensor data — 54 motes logging temperature, humidity, light, and voltage roughly every 31 s for about a month, plus a mote location file. Get data.txt and mote_locs.txt from Intel Lab Data (plain HTTP; data.txt.gz is 34 MB compressed, 150 MB unpacked), or the same data.txt over HTTPS from the mirror the lecture demos use — the two files are byte-identical.

Read the file before you parse it. These are properties of the published data.txt, and your loader should reproduce them:

rows in the file	2,313,682
rows with an empty moteid	526
rows with a moteid outside 1–54	9,866
rows from a mote in the roster	2,303,290
rows carrying at least one empty channel field	93,879
readings in a long-format load	9,119,212

The delimiter is a single space, and every row splits cleanly into eight fields on it. A transmission that dropped a channel leaves that field empty, which is an absent measurement and not a zero one. Any parser that collapses runs of whitespace — line.split(), pandas.read_csv(sep=r'\s+'), awk — sees seven fields on those 93,879 rows, shifts the following values one column left, and produces rows that are well-formed, plausible and wrong. Nothing raises: pandas pads the short row with NaN and the row count is identical either way. The L3 demo notebook shows the two parses side by side; run that cell once. If your loader reports dropping ~93,000 “malformed” rows, that number is the fingerprint of this mistake, not a property of the file.

Fallback: UCI “Individual Household Electric Power Consumption” or the Wk1 Air Quality data if Intel Lab is unavailable. Document your choice in REPORT.md and adapt the schema; the rubric is unchanged, but the automated checks that quote Intel Lab counts will not apply and your TA scores those groups from your report instead.

The SQLite spellings you need

L3’s ideas, in this week’s dialect. Nothing here is more than one line.

L3 taught	In SQLite
timestamptz	there is no date type. Store the instant twice: ts TEXT as 'YYYY-MM-DD HH:MM:SS.sss' in UTC (sorts and compares correctly), and ts_unix REAL, seconds since the epoch, which is what a RANGE window frame needs.
the schema is a contract	add STRICT after CREATE TABLE. Without it SQLite will put the text 'hot' in a REAL column without complaining.
the foreign key is enforced	PRAGMA foreign_keys = ON, before you create any tables. It is off by default, and a REFERENCES clause with the pragma off is decoration.
COPY	executemany over batched rows inside one transaction. The catastrophe L3 warned about is a commit per row, not an INSERT per row.
date_trunc('hour', ts)	strftime('%Y-%m-%d %H:00:00', ts)
EXPLAIN ANALYZE	EXPLAIN QUERY PLAN, which prints SCAN readings or SEARCH readings USING INDEX …. It reports no timings, so time the query yourself with time.perf_counter().
RANGE BETWEEN INTERVAL '1 hour' PRECEDING	RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW, with ORDER BY ts_unix. It must be the numeric column. Ordering a RANGE frame by the ts text gives every row a window of one row — silently, with no error and no warning.

The next section takes the ones that actually bite — the timestamp, the window frame, the join, and the DuckDB side — a little slower, with links.

Tips on the parts that are new

L3 and L4 taught the ideas on PostgreSQL and pandas. These are the places where SQLite and DuckDB do it differently enough to cost you an afternoon if nobody says so first. Each one is a couple of lines of code, not a new concept.

Time, which SQLite does not have a type for

SQLite has no date type at all — only INTEGER, REAL, TEXT and BLOB (datatypes). Store the instant twice and the rest of the assignment gets easy:

ts       TEXT NOT NULL,   -- '2004-03-15 09:31:16.027850', UTC
ts_unix  REAL NOT NULL    -- 1079343076.02785, seconds since the epoch

ISO-8601 text is worth using because lexicographic order is chronological order, so ORDER BY ts, WHERE ts >= '2004-03-15' and strftime all work on it. Any other spelling — 03/15/2004, or a local-time string — breaks all three silently. ts_unix exists because of the window frame below.

L3’s point about timestamptz still applies, it just has nowhere to live: pick UTC, convert once where you parse the file, and never think about it again. The type system will not carry that decision for you here, so a comment in your loader has to.

The window frame, which needs a number

SQLite has window functions and the RANGE frame L3 taught, but the offset is a plain number of seconds rather than an INTERVAL:

avg(value) OVER (PARTITION BY sensor_id ORDER BY ts_unix
                 RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW)

ORDER BY ts_unix, not ORDER BY ts. This is the sharpest trap in the assignment. SQLite accepts a RANGE offset over a text column and then gives every row a window containing only itself — no error, no warning, and a “rolling average” column that is an exact copy of the value column. If your rolling average looks suspiciously like your raw values, this is why. Print count(*) OVER w beside it; on this data it should range from 1 into the hundreds, not sit at 1.

Kinds of join, which we never spelled out

L3 used exactly one join, JOIN sensors USING (sensor_id), and never said what kind it was. It is an inner join, and the word matters here because query (b) turns on it.

An inner join keeps only rows that match on both sides. A row on either side with no partner simply is not in the result — it does not appear with blanks, it disappears, and nothing warns you. That is usually what you want and occasionally catastrophic.

Written	Keeps	Use it when
JOIN / INNER JOIN	rows that match on both sides	the default; you want readings and their sensor
LEFT JOIN	every row of the left table, with NULLs where the right has no match	the left table is the list you want to be complete — a roster, a calendar, a catalogue
CROSS JOIN	every combination of both sides	rarely on purpose; ours is CROSS JOIN LATERAL-style unpivoting

RIGHT JOIN and FULL OUTER JOIN exist and SQLite has had them since 3.39.0 (June 2022); a RIGHT JOIN is a LEFT JOIN with the tables swapped, so you will rarely need one.

Two consequences worth knowing before you debug them:

• USING (sensor_id) and ON a.sensor_id = b.sensor_id mean the same thing, except that USING collapses the two columns into one in the output. Use USING when the column names match on both sides.

• After a LEFT JOIN, count(*) and count(some_right_column) are different numbers. A left row with no partner still produces one output row, so count(*) counts it; count(col) skips it, because the column is NULL and aggregates ignore nulls. This is L3’s three-valued logic showing up somewhere you did not expect it, and it is how you count “how many of these had nothing to match”. LEFT JOIN … WHERE right.key IS NULL is the idiom for only the rows that matched nothing.

Where this lands in the assignment. Query (b) asks which sensors dropped the most readings. A query built from the readings table alone can only rank sensors that produced readings, and you have a roster of every mote that was deployed — which is not the same list. Decide which of those two tables your query should start FROM, and say in your report what you counted and why. There are several defensible answers and the reasoning is what earns the points.

Common table expressions (WITH … AS) keep this readable: compute the gaps in one, aggregate per sensor in the next, then join the roster to that.

Read: PostgreSQL’s joins tutorial is the clearest short explanation with worked examples, and it is in the dialect L3 used; its joined-tables reference is the precise version. SQLite’s own SELECT documentation is the authority on what this week’s engine accepts. All three agree on everything you need here.

Foreign keys and types are opt-in

Both of these are off by default, and both are the difference between a schema that is a contract and one that is a suggestion:

conn = sqlite3.connect("lab.db", isolation_level=None)
conn.execute("PRAGMA foreign_keys = ON")     # BEFORE any CREATE TABLE

Foreign key support is per-connection and silently does nothing if you issue the pragma inside a transaction, so put it in the one function that opens connections and call that everywhere. Add STRICT after each CREATE TABLE or SQLite will happily store the text 'hot' in a REAL column. The evidence script runs PRAGMA foreign_key_check on your database, so a key you declared but never enforced shows up as orphan rows.

Loading: one transaction, not two million

There is no COPY. Use executemany over batches of a few hundred thousand rows, inside one explicit transaction. The thing that turns twenty seconds into an afternoon is not an INSERT per row, it is a commit per row — each one is an fsync. isolation_level=None puts the driver in autocommit mode so the BEGIN and COMMIT are yours to place.

Reading the plan

EXPLAIN QUERY PLAN gives one line instead of PostgreSQL’s tree, and the word at the front is the whole story: SCAN readings means it is reading every row, SEARCH readings USING INDEX … means it is seeking. There is no ANALYZE to bolt on and no timing in the output, so time the query yourself with time.perf_counter(), warm, and take a median. The query planner overview explains why the column order of a composite index decides whether it can be used at all.

DuckDB reads your SQLite file directly

You do not have to move data through Python to get Parquet. The sqlite extension attaches lab.db, and one partitioned write does the export:

INSTALL sqlite; LOAD sqlite;
ATTACH 'lab.db' AS lab (TYPE sqlite, READ_ONLY);
COPY (SELECT *, CAST(substr(ts, 1, 10) AS DATE) AS date FROM lab.readings)
  TO 'parquet/readings' (FORMAT parquet, PARTITION_BY (date));

Reading it back needs hive_partitioning = true, or the date column vanishes — it exists only as a directory name, and without that flag DuckDB has no idea the directories mean anything. If your DuckDB query cannot filter by date, this is why. read_parquet is documented here, and DuckDB’s GROUP BY ALL saves retyping the grouping columns (it is DuckDB’s, not standard SQL).

DuckDB also has real TIMESTAMP and DATE types, so ts::TIMESTAMP and strftime(ts::TIMESTAMP, '%Y-%m-%d %H:00:00') work there. That is a small reminder worth noticing: the row store was carrying a convention where the column store carries a type.

Tasks

1. Create the database. sqlite3.connect('lab.db'), PRAGMA foreign_keys = ON first, then your DDL.

2. Design a normalized schema. At minimum a sensors dimension and a readings fact table with a foreign key and correct types. Both shapes taught in L3 are acceptable: long format readings(sensor_id, ts, variable, value), or a typed readings with one column per channel. Choose one and say why in your report — naming the trade you made is what the schema points are for. What is not acceptable is a wide table with one column per mote.

3. Load the data with batched executemany in one transaction, not a commit per row. Handle malformed rows, bad timestamps, and dying-battery voltage anomalies, and document your cleaning rules.

4. Write SQL answering at least these: (a) hourly average temperature per sensor; (b) the 5 sensors with the most missing/dropped intervals; (c) a rolling 1-hour average voltage per sensor using a window function; (d) detect readings outside a plausible physical range.

5. Add an index supporting time-range-per-sensor queries. Show EXPLAIN QUERY PLAN and your own timing before and after, and report the difference.

6. Export to date-partitioned Parquet, then reproduce query (a) and (c) in DuckDB directly against the Parquet files. DuckDB can read your SQLite file directly (INSTALL sqlite; ATTACH 'lab.db' AS lab (TYPE sqlite);) and write the Parquet in one COPY … TO … (PARTITION_BY (date)), which is the shortest path.

7. Run one analytical query in pandas, SQLite, and DuckDB; record lines of code and wall-clock time for each.

Names to use

The script that builds your submission has to find your work, and a grader has to read it.

Thing	Name
Database	lab.db
Schema DDL	sql/schema.sql
Load script	load.py, or src/…/load.py
The four queries	sql/queries.sql (a notebook is allowed)
Parquet export + DuckDB	any .py, .sql or notebook
Parquet output	parquet/readings/date=…/
Report	REPORT.md
Raw data	data/, git-ignored

If you built it under other names the script goes looking rather than giving up, and its first page prints what it found. If that is not what you meant, say so and run it again:

uv run --no-project a02-evidence.py --andrew-id yourid --db warehouse.sqlite --queries analysis.ipynb

What goes in REPORT.md

Group 6 is your report, and it is a person who reads it. Two pages maximum, five sections, in this order:

1. Schema. Your tables, types and keys, and the trade you made: long format against typed columns, what each buys, why you chose yours. A choice with no stated reason gets about half.

2. Load and cleaning. Your cleaning rules, each with the count of rows or readings it affected. State whether you deleted or flagged the implausible readings, because query (d) has to be able to find them.

3. Indexing. The plan and your timing before and after, and what it means in your own words. A pasted plan with no interpretation earns nothing here. The magnitude of your speedup is not graded; the reasoning is.

4. The three-way comparison. One table — pandas, SQLite, DuckDB — with wall-clock time and lines of code for each, and a sentence or two saying what the table shows. Say what you timed.

5. When you would choose each store. The OLTP/OLAP reasoning used as an explanation, not a label: why a row store must read every column of every row to average one. And — the half most reports miss — what SQLite is better at, which is not nothing. A report whose conclusion is “just use DuckDB” has landed on the trap L4 named explicitly.

Plus one line, anywhere, disclosing generative-AI use per the syllabus.

What you hand in

One PDF, uploaded to Canvas, generated from your project by a script we provide. Keep your repository on your own machine; there is nothing to push. The PDF records what your project actually does — it profiles the raw data file, reads your SQL, opens your lab.db and runs its own EXPLAIN QUERY PLAN — then embeds your schema, loader, queries, DuckDB script and REPORT.md, syntax-highlighted so they can be read. Grading reads that one document.

From your project root, with lab.db built:

uv run --no-project https://kitchingroup.cheme.cmu.edu/f26-06763/a02-evidence.py \
    --andrew-id yourid --name "Your Name"

That is the whole thing: uv run fetches the script and runs it, so there is nothing to download first and no copy of it left in your project. It writes evidence.pdf. Upload that.

If you would rather have the file — to read it, or to run it repeatedly without re-fetching — the two-step form is identical:

curl -O https://kitchingroup.cheme.cmu.edu/f26-06763/a02-evidence.py
uv run --no-project a02-evidence.py --andrew-id yourid --name "Your Name"

Nothing is installed either way: the script imports nothing outside the standard library, so uv is only supplying an interpreter. python3 a02-evidence.py ... works too, on the downloaded copy.

Keep --no-project. A plain uv run syncs your project before running anything, so a stale uv.lock or one unresolvable dependency would leave you with no report at all — one unrelated mistake taking down a submission that is otherwise fine. --no-project skips that, which is safe precisely because this script needs nothing from your environment.

It opens your database read-only and every statement it sends is a SELECT, a PRAGMA or an EXPLAIN.

Read the PDF before you upload it. It prints your score out of 6, group by group, decided from the output rather than asserted, so you see exactly what the grader sees. A report with one weak group is worth far more than no report, so a failing check is a reason to fix it and rerun, never a reason not to submit.

The report prints the sha256 of the script that produced it, and the same checksum is published at https://kitchingroup.cheme.cmu.edu/f26-06763/a02-evidence.py.sha256. Running straight from the URL makes those match by construction, which is one more reason to prefer it; if you downloaded a copy, compare the two lines. It also cross-checks itself: the row counts in your report are compared against the raw file it parsed, and the plan in your report against the one it got from your database.

Rubric (6 pts)

Six groups. Five are scored by the script; the sixth is your report, read by your TA. Within a group the checks are equally weighted, so a group is worth the fraction of its checks you pass — three of four checks in group 1 is 0.75 of 1.0.

#	Group	Pts	Decided by	Checks in the group
1	Schema and types	1.0	script	a sensors dimension with a REFERENCES from readings; PRAGMA foreign_keys = ON; a primary key or unique constraint over the natural key; no column named after an individual mote
2	Load and cleaning	1.0	script	data.txt split on a single space; a batched/executemany load rather than a commit per row; the roster and range rules applied, with the row counts landing near the file’s own; cleaning rules written down
3	Queries	1.5	script	(a) hour bucketing; (b) missing intervals; (c) a window function with a numeric RANGE frame; (d) a plausible-range check; all four run against your lab.db
4	Index and plan	0.75	script	an index leading with the sensor column; the plan changes from SCAN to SEARCH … USING INDEX; a before/after in the report
5	Parquet and DuckDB	0.75	script	a date-partitioned Parquet tree; DuckDB reads it directly; (a) and (c) reproduced; a timed three-way comparison
6	REPORT.md	1.0	your TA	the five sections above, and the AI-use line

A check the script genuinely cannot decide is marked and its share of the group is held for your TA rather than lost.

How this is graded

5 of the 6 points come from the checks, decided by the script from your project rather than from your description of it. The last point is your report. Your TA also reads what the PDF embeds — your schema, your SQL, your loader — and can adjust when something is clearly better or worse than the checks can see, in either direction. Finding mote 5, or noticing why the two engines’ last decimal places disagree, is the kind of thing that earns an adjustment upward.

A check that fails costs you its share of one group and nothing else. The report is built so that one mistake stays one mistake.

Allowed tools & AI-use note

Generative-AI allowed with disclosure per syllabus; state usage in REPORT.md, which is embedded in the PDF you submit. Editing the generated report by hand is falsifying a submission, and it is also more work than rerunning the script. You must be able to explain your schema choices, any SQL query, and your query-plan interpretation on request.

Stretch (optional, not graded)

• Query your SQLite file from DuckDB with ATTACH … (TYPE sqlite) and join it to the Parquet in one statement, with no export at all.

• Join your DuckDB and SQLite results and report the largest disagreement, rather than eyeballing the first few rows.

• Do the whole thing again against PostgreSQL from the L3 compose file, and compare EXPLAIN ANALYZE to EXPLAIN QUERY PLAN.