# 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](https://db.csail.mit.edu/labdata/labdata.html) (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](https://raw.githubusercontent.com/linsea423/Intel_Lab_Data/master/data.zip) — 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](https://www.sqlite.org/datatype3.html)). Store the instant **twice**
and the rest of the assignment gets easy:

```sql
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`](https://www.sqlite.org/lang_datefunc.html) 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](https://www.sqlite.org/windowfunctions.html) and
the `RANGE` frame L3 taught, but the offset is a plain number of seconds rather
than an `INTERVAL`:

```sql
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 `NULL`s 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](https://www.sqlite.org/releaselog/3_39_0.html) (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](https://www.sqlite.org/nulls.html). 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](https://www.sqlite.org/lang_with.html) (`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](https://www.postgresql.org/docs/current/tutorial-join.html)
is the clearest short explanation with worked examples, and it is in the dialect
L3 used; its [joined-tables reference](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-JOIN)
is the precise version. SQLite's own
[SELECT documentation](https://www.sqlite.org/lang_select.html) 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:

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

[Foreign key support](https://www.sqlite.org/foreignkeys.html) 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`](https://www.sqlite.org/stricttables.html) 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`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.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`](https://www.sqlite.org/eqp.html) 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](https://www.sqlite.org/queryplanner.html) 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](https://duckdb.org/docs/stable/core_extensions/sqlite) attaches
`lab.db`, and one
[partitioned write](https://duckdb.org/docs/stable/data/partitioning/partitioned_writes)
does the export:

```sql
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`](https://duckdb.org/docs/stable/data/partitioning/hive_partitioning),
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](https://duckdb.org/docs/stable/data/parquet/overview), and DuckDB's
[`GROUP BY ALL`](https://duckdb.org/docs/stable/sql/query_syntax/groupby) 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:

```bash
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:

```bash
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:

```bash
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`.