Every dataset in this topic arrived as a file from a public agency: a SAS transport file from the National Center for Health Statistics, a CSV from the Census Bureau, a CSV exported from a government data portal. Someone designed those files, chose their formats and columns, built the systems that produce them, and keeps those systems running. In an organization that analyzes its own data, the corresponding work is data engineering: building and maintaining the systems that collect data, store it, move it, transform it into usable tables, and give analysts access to it.
Analysts rarely build these systems, but they work inside them every day, and the systems shape what arrives: which columns exist, what types they have, how fresh they are, how far they can be trusted. This article introduces data engineering from an analyst's point of view. It starts with files, which every analyst handles, measures what different layouts do with a real file, and moves outward to pipelines, warehouses and lakes, and distributed computation. It ends with the practice that connects them all to reproducible analysis: knowing exactly which data an analysis used.
Formats
CSV
Comma-separated values are the common denominator of data exchange. RFC 4180, which documented the format in 2005, begins by noting that "while this format is very common, it has never been formally documented" (Shafranovich, 2005). Its rules are few: one record per line, fields separated by commas, an optional header line, and double quotes around fields that contain commas, quotes or line breaks. Even the header is optional, and the RFC adds a MIME parameter so that a sender can say whether one is present.
That simplicity is CSV's strength and its main weakness. A CSV file stores text, with no types, no units, no distinction between a missing value and an empty string, and no record of what the columns mean. Every reader must infer them. Writing the 5,533 examined NHANES adults to CSV and reading the file back with pandas shows what survives:
| Column | Type written | Type read back |
|---|---|---|
| person number, age | integer | integer |
| systolic, diastolic reading | float | float |
| sex | category | text (object) |
| a date column (added for the demonstration) | datetime | text (object) |
| reading present | Boolean | Boolean |
| diastolic ÷ systolic | float | float, but 384 of 4,899 values changed |
The category and the date come back as plain text, and the type information must be supplied again at every read. Less obviously, the ratio column does not come back exactly. pandas writes each float with enough digits to identify it, but its default parser is not guaranteed to round-trip, and 384 of the 4,899 values come back different in the last binary digit (the largest difference is $8.3 \times 10^{-17}$). Passing float_precision="round_trip" to read_csv gives exact values (pandas development team, 2024). The difference is negligible for analysis but enough to make two files that should be identical compare unequal. Structuring Data shows two more inference failures: a state code 01 read as the number 1, and a whole column read as text because of one letter.
SAS transport
The NHANES files use the SAS transport (XPORT) format, a public specification from the 1980s still used for exchanging data with regulators and statistical agencies. Its records are 80 bytes long, variable names are at most eight characters, and "all floating-point numbers are stored using the IBM-style double", the representation of IBM mainframes, which readers must convert (SAS Institute, 2021). A self-describing binary format like this keeps more than CSV does (names, labels, numeric types) but less than a modern one: every number, including identifiers and codes, is a floating-point value, and the conversion can go wrong at the edges, as the zeros in Cleaning Data show.
Parquet and columnar storage
Most file formats store a table row by row: all of the first record, then all of the second. Analytical queries usually read a few columns of many rows, so a row layout forces them to read everything. A columnar format stores each column's values together. Melnik et al. (2010), describing Google's Dremel system, put the advantage in two parts: a column-striped layout "enables it to read less data from secondary storage and reduce CPU cost due to cheaper compression". Apache Parquet, the most widely used columnar file format, splits a table into row groups and stores within each a chunk per column, with the file's metadata (the schema, and the location of every chunk) written at the end so that a reader can "first read the file metadata to find all the column chunks they are interested in" (Apache Software Foundation, n.d.). Parquet files carry their schema, so types survive a round trip.
Both effects can be measured on the NCHS weekly death counts, a CSV of 2,441,167 bytes with 30 columns and 16,902 rows, using general-purpose zlib compression at its highest level:
Compressed as it stands, row after row, the file takes 375,208 bytes. Compressing each column's values separately, exactly as they are written in the file, takes 237,857 bytes in total, 37% less, because a column holds similar values next to each other: the suppression flag column for all-cause deaths, which is empty in every row, compresses to 40 bytes. And a query that needs only the all-cause count reads that column alone, 29,054 bytes, less than 8% of the compressed file. Parquet adds encodings designed for columns, such as dictionaries for repeated values, which do better than a general compressor; the point here is only the effect of the layout. (The script for this article uses only the Python standard library for this measurement, so the figures are for zlib, not for Parquet itself.)
Row layouts remain the right choice where whole records are written and read one at a time, which is the work of the transactional databases below.
Schemas and checks
A schema states what a table must look like: its columns, their types, and the rules their values obey. Databases enforce schemas when data are written; files like CSV do not, so analysts and pipelines check them when data are read. A schema written as code is a set of assertions:
| Rule (NHANES 2017–2018) | Violations |
|---|---|
| person number unique | 0 |
| sex is 1 or 2 | 0 |
| age between 0 and 80 | 0 |
| systolic readings even and between 60 and 260, or missing | 0 |
| diastolic below systolic in each reading pair | 0 |
| self-reported weight between 50 and 700 pounds, or missing | 113 |
The 113 violations are the refused and don't-know codes 7777 and 9999 of Cleaning Data. A schema check does not decide what to do with them, but it guarantees they are noticed, every time the data are loaded, rather than once by an analyst who happened to look. Pipelines run such checks automatically and stop or quarantine data that fail.
Pipelines: ETL and ELT
A pipeline moves data from where it is produced to where it is analyzed, on a schedule and without manual steps. The classic design is extract, transform, load (ETL): extract records from the operational systems, transform them in a separate staging area into clean, consistent tables, and load the result into an analytical database. Chaudhuri and Dayal (1997) describe the back end of a data warehouse as tools "for extracting, cleaning and loading data", including data cleaning, transformation, and the refreshing of the warehouse as sources change.
Extract, load, transform (ELT) reverses the last two steps: raw data are loaded as they are, and transformed inside the analytical system, usually by SQL queries. ELT became practical when analytical databases became cheap and fast enough to hold raw data and do the transformation themselves. It keeps the raw data available for transformations not yet thought of, at the cost of storing it.
ELT can be shown in miniature with Python's built-in SQLite database. Loading the weekly death counts as they are, 16,902 rows and 30 columns (with column names normalized: All Cause, with its two spaces, becomes all__cause), and then transforming them with SQL, one query sums the 2019 weeks by jurisdiction, folds New York City into New York and drops the national total:
select case when jurisdiction_of_occurrence = 'New York City' then 'New York'
else jurisdiction_of_occurrence end as name,
sum(all__cause) as deaths
from weekly_raw
where mmwr_year = 2019 and jurisdiction_of_occurrence not in ('United States')
group by 1
order by 1
The 52 rows it returns agree exactly with the same aggregation in pandas in Enriching Data. The Aggregate Functions and Queries in Python articles cover the SQL and the connection from Python.
Where data live
Databases
An operational, or transactional, database records an organization's activity as it happens: orders, payments, appointments. Chaudhuri and Dayal (1997) characterize this work, on-line transaction processing (OLTP), as "short, atomic, isolated transactions" that "require detailed, up-to-date data, and read or update a few (tens of) records accessed typically on their primary keys". Such databases are designed for consistency and fast updates, with tables normalized so that each fact is stored once (Relational Databases).
Data warehouses
A data warehouse collects data from many operational systems into one database designed for analysis. Chaudhuri and Dayal (1997) quote Inmon's definition: a "subject-oriented, integrated, time-varying, non-volatile collection of data that is used primarily in organizational decision making", maintained separately from the operational databases because analytical queries, which scan and aggregate large parts of the data, have different requirements from transactions. Warehouses hold historical and consolidated data, often far larger than any one operational system.
Most warehouses organize their tables as a star schema: "a single fact table and a single table for each dimension" (Chaudhuri and Dayal, 1997). The fact table holds the measurements, one row per event, with keys pointing to the dimension tables, which describe the who, when and what. The weekly death counts reorganized this way have a fact table of 219,726 rows (jurisdiction, week, cause, deaths) and three small dimension tables: 54 jurisdictions, 313 weeks with their dates, and 13 causes (all causes, natural causes and the eleven specific causes with their ICD-10 codes). Attributes that describe a jurisdiction, such as its population or region, are stored once in its dimension table instead of on every row of the facts. Analysis then joins the facts to the dimensions it needs, which is exactly the joining of Enriching Data with the keys designed in advance.
Data lakes and lakehouses
A warehouse requires data to fit its schema before loading ("schema-on-write"). That excludes data that do not fit tables, such as images, audio and free text, and data whose use is not yet known. A data lake stores files of any kind cheaply, in open formats, and imposes structure only when the data are read ("schema-on-read"). Armbrust, Ghodsi, Xin and Zaharia (2021) trace this history: first-generation platforms loaded data into warehouses with schema-on-write; second-generation platforms offloaded raw data into lakes, "low-cost storage systems with a file API that hold data in generic and usually open file formats, such as Apache Parquet and ORC", beginning with Hadoop's file system, and ETLed a subset onward into a warehouse for the most important reporting. They describe the result, two systems kept in step, as complex, prone to staleness and inconsistency, and costly.
Their proposal, the lakehouse, keeps the data in open columnar files in a lake and adds the features of a warehouse on top: transactions, schemas, indexing and fast SQL. It is worth knowing that the authors are from Databricks, a company that sells a lakehouse platform, and that the paper is an argument for an architecture as well as a description of one. Commercial cloud platforms, including Databricks, Snowflake, Google BigQuery and Amazon Redshift, offer warehouses, lakes or both, and their feature sets change from year to year; an analyst mainly needs to know which kind of system holds the data and whether it is queried through SQL, files or both.
Computing at scale
When data outgrow one machine, both storage and computation are spread over many.
Distributed storage. The Hadoop Distributed File System (HDFS) splits each file "into large blocks (typically 128 megabytes ...)" and stores each block on several machines, "typically three" (Shvachko, Kuang, Radia and Chansler, 2010). If a machine fails, every block it held still exists elsewhere, and capacity grows by adding machines. At the time of the paper, Yahoo's clusters had 25,000 servers storing 25 petabytes.
MapReduce. Dean and Ghemawat (2004) described Google's programming model for computing over such data: the user writes a map function that turns each input record into intermediate key and value pairs and a reduce function that "merges all intermediate values associated with the same intermediate key"; the system handles partitioning, scheduling across thousands of machines, and recovery from failures. It is split-apply-combine at the scale of a data center, as Wickham (2011) notes in the other direction: "the map step corresponds to split and apply, and reduce corresponds to combine". The annual death count by jurisdiction is a small MapReduce:
from collections import defaultdict
pairs = [(row.jurisdiction, row.deaths) for row in weekly_2019] # map: 2,808 pairs
groups = defaultdict(list)
for key, value in pairs: # shuffle: gather by key
groups[key].append(value)
totals = {key: sum(values) for key, values in groups.items()} # reduce: 54 totals
The 54 totals equal those of a pandas groupby. On a cluster the pairs would be produced on many machines and the shuffle would move each key's values to one machine to be reduced.
Spark. MapReduce writes intermediate results to disk between steps, which is slow for computations that pass over the same data many times, such as iterative model fitting. Zaharia et al. (2012) introduced resilient distributed datasets, collections partitioned across a cluster and kept in memory, which recover from failures by recomputing lost partitions from the operations that produced them. Their system, Spark, ran some iterative applications up to 20 times faster than Hadoop. Spark is now usually used through its DataFrame and SQL interfaces, including PySpark from Python, which look much like pandas but run on a cluster.
For the datasets in this topic, which fit comfortably in a laptop's memory, none of this is needed; pandas on one machine is simpler and faster. The point at which distributed systems pay for their complexity is far beyond the size of most analyses.
Reproducibility: knowing which data were used
An analysis is reproducible only if its inputs can be recovered exactly. Public data change: agencies correct files, add rows and regenerate exports. Wilson et al. (2017) put first among their practices "Save the raw data", and where a local copy is impractical, "record the exact procedure used to obtain the raw data, as well as any other pertinent information, such as an official version number or the date of download".
The scripts on this site go one step further. Every downloaded file is checked against a cryptographic hash, a SHA-256 digest of its bytes recorded in the script when it was written. The NHANES demographics file of 3,412,720 bytes, for instance, must hash to c0b46e03...b7ee73ec, and the blood pressure file of 1,466,000 bytes to f98f1d9a...38fb3423. If the agency replaces either file, the digest changes and the script stops with an error instead of producing different numbers silently. A good hash function makes any change visible: changing a single death count in the weekly table by one changes 130 of the 256 bits of its digest, about half, as expected of a hash with no relation between similar inputs.
Some sources cannot be pinned as files. The data portal that serves the weekly death counts regenerates its CSV export on request, so the file's bytes can change even when the data do not. For such sources the scripts hash the parsed table instead: the five columns used, sorted and written in a fixed text form, a canonical representation whose digest (10da050c...2dda270) changes only if the data do. This distinction, between the bytes of a file and the content of a table, is the same one that ETL pipelines make when they decide whether a source has changed.
Recording the code, the inputs' digests and the versions of the libraries used is enough to rerun an analysis years later and get the same numbers, or to find out exactly why they differ.
In Python
import hashlib, sqlite3
import pandas as pd
# pin an input file
digest = hashlib.sha256(open("DEMO_J.xpt", "rb").read()).hexdigest()
assert digest == "c0b46e0345ea19404928656277c8b0d10b0cca348a9b2fe4fc3c67e8b7ee73ec"
# read CSV with explicit types, and exact floats
df = pd.read_csv("data.csv", dtype={"STATE": str}, float_precision="round_trip")
# Parquet keeps types (needs the pyarrow or fastparquet package)
df.to_parquet("data.parquet")
df = pd.read_parquet("data.parquet", columns=["name", "deaths"]) # read only some columns
# load raw, transform in SQL (ELT)
con = sqlite3.connect("analysis.db")
raw.to_sql("weekly_raw", con, index=False, if_exists="replace")
annual = pd.read_sql_query("select ... group by 1", con)
The numbers in this article are computed by tools/experiments/data-science/data-engineering.py in the site's repository, which uses only pandas, NumPy and the Python standard library.
References
- Apache Software Foundation (n.d.). File format. Apache Parquet documentation. https://parquet.apache.org/docs/file-format/ record
- Armbrust, M., Ghodsi, A., Xin, R., and Zaharia, M. (2021). Lakehouse: A new generation of open platforms that unify data warehousing and advanced analytics. In Proceedings of the 11th Conference on Innovative Data Systems Research (CIDR '21). https://www.cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf record
- Chaudhuri, S., and Dayal, U. (1997). An overview of data warehousing and OLAP technology. ACM SIGMOD Record, 26(1), 65–74. https://doi.org/10.1145/248603.248616 record · 2 pages
- Dean, J., and Ghemawat, S. (2004). MapReduce: Simplified data processing on large clusters. In Proceedings of the 6th Symposium on Operating Systems Design and Implementation (OSDI '04). record · 2 pages
- Melnik, S., Gubarev, A., Long, J. J., Romer, G., Shivakumar, S., Tolton, M., and Vassilakis, T. (2010). Dremel: Interactive analysis of web-scale datasets. Proceedings of the VLDB Endowment, 3(1), 330–339. record
- pandas development team (2024). pandas.read_csv. pandas 2.2 API reference. https://pandas.pydata.org/pandas-docs/version/2.2/reference/api/pandas.read_csv.html record · 2 pages
- SAS Institute (2021). Record Layout of a SAS Version 5 or 6 Data Set in SAS Transport (XPORT) Format (technical paper). https://support.sas.com/content/dam/SAS/support/en/technical-papers/record-layout-of-a-sas-version-5-or-6-data-set-in-sas-transport-xport-format.pdf record · 3 pages
- Shafranovich, Y. (2005). Common Format and MIME Type for Comma-Separated Values (CSV) Files (RFC 4180). Internet Engineering Task Force. https://www.rfc-editor.org/rfc/rfc4180 record
- Shvachko, K., Kuang, H., Radia, S., and Chansler, R. (2010). The Hadoop distributed file system. In Proceedings of the 26th IEEE Symposium on Mass Storage Systems and Technologies (MSST). https://doi.org/10.1109/MSST.2010.5496972 record
- Wickham, H. (2011). The split-apply-combine strategy for data analysis. Journal of Statistical Software, 40(1), 1–29. https://doi.org/10.18637/jss.v040.i01 record · 3 pages
- Wilson, G., Bryan, J., Cranston, K., Kitzes, J., Nederbragt, L., and Teal, T. K. (2017). Good enough practices in scientific computing. PLOS Computational Biology, 13(6), e1005510. https://doi.org/10.1371/journal.pcbi.1005510 record · 2 pages
- Zaharia, M., Chowdhury, M., Das, T., Dave, A., Ma, J., McCauley, M., Franklin, M. J., Shenker, S., and Stoica, I. (2012). Resilient distributed datasets: A fault-tolerant abstraction for in-memory cluster computing. In Proceedings of the 9th USENIX Symposium on Networked Systems Design and Implementation (NSDI '12). record