File formats: CSV vs JSON vs Parquet, compression and schema evolution
Measure how CSV, JSON Lines and Parquet store the same Harbourline transactions, understand row groups, column statistics and compression codecs, handle schema evolution safely, and see why Parquet makes Athena faster and cheaper.
Assumes: .NET 10 LTS · Parquet.Net 6.1.0 · Python 3.12 · pyarrow 26.0.1 · DuckDB 2.0.0 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Explain how row-oriented (CSV, JSON) and column-oriented (Parquet, ORC) formats lay out data, and why that matters for analytical queries.
- Read a Parquet file's metadata (row groups, column chunks, min/max statistics) and explain column pruning and predicate pushdown.
- Choose a compression codec (Snappy, ZSTD, GZIP) and know which combinations are splittable.
- Map Harbourline's schema to Parquet types correctly, especially DECIMAL(18,2) for money and DATE/TIMESTAMP for time.
- Evolve a schema (add, rename, retype a column) without breaking Athena or Glue readers.
- Convert CSV to partitioned Parquet in C# with Parquet.Net and in Python with pyarrow, and measure the difference on your own machine.
Prerequisites
- S3 data lake design — zones and partitions
- Tools: Docker (for the Python script) or Python 3.12 with
pip; .NET 10 SDK for the C# converter - The samples in
datasets/samples/(1,012 transactions, seed20261117)
Why this matters in banking
Harbourline's analysts run the same question many times a day: "card spend by merchant category for the last 30 days". On CSV, Athena must read every byte of every file in the range, because a CSV file has no idea where a column starts or which values it contains. At USD 5 per TB scanned, a 2 TB CSV history makes each query cost about USD 10; a dashboard refreshed every 15 minutes during business hours costs hundreds of dollars a month, for one chart.
The same data as partitioned Parquet typically scans a few percent of those bytes. The finance director sees the Athena bill drop, the dashboard becomes faster, and nobody changed a line of SQL.
File formats also decide correctness. CSV has no types: 0123 loses its leading zero when a tool guesses "number", 03/11/2026 means two different dates depending on who reads it, and 1,234.50 breaks column alignment. JSON types numbers as floating point by default, which is wrong for money. Parquet stores the declared type with the data.
The idea in plain language
A table has rows and columns. A file must put those values in some order on disk.
- Row-oriented (CSV, JSON Lines): all values of row 1, then all values of row 2, and so on. Great for writing one record at a time and for reading whole records. Terrible for "sum one column over a million rows", because you must read everything to find that column.
- Column-oriented (Parquet, ORC): all values of column 1 for a block of rows, then all values of column 2, and so on. A query that needs 3 of 15 columns reads only those 3. Similar values sit together, so they compress very well. Each block records the minimum and maximum of each column, so a reader can skip blocks that cannot match a filter.
Analogy: A CSV file is a stack of paper forms, one customer per sheet. To total the "amount" box you flip through every sheet. A Parquet file is the same information re-filed as ledgers: one ledger per field, with a label on the cover saying "dates in this ledger run from 1 to 15 November". To total amounts for 16 November you pick up only the amount and date ledgers, and you skip any whose cover says they cannot contain that date.
What problem the AWS service solves
Athena, Glue (Spark), Redshift Spectrum and EMR all read Parquet natively, write it efficiently, and use its metadata to skip data. On S3 you pay for storage by the byte, and in Athena you pay for bytes scanned (USD 5 per TB in us-east-1, rounded up to the nearest MB with a 10 MB minimum per query). Columnar formats reduce both.
The formats you will meet
| Format | Layout | Types | Schema in file | Splittable | Typical lake use |
|---|---|---|---|---|---|
| CSV | Row | None (all text) | Header row only | Yes, if uncompressed or compressed with a splittable codec | Raw zone: partner files, legacy extracts |
JSON Lines (.jsonl) | Row | String, number, boolean, null, nested | Per record (repeated keys) | Yes, if uncompressed | Raw zone: API payloads, events, Firehose output |
| Apache Parquet | Columnar | Rich: INT, DECIMAL, DATE, TIMESTAMP, nested | Yes, in the footer | Yes, by row group, whatever the codec | Curated and gold zones |
| Apache ORC | Columnar | Rich | Yes | Yes | Hive-heavy estates; equivalent role to Parquet |
| Apache Avro | Row, binary | Rich | Yes, in the header | Yes | Kafka/MSK messages with a schema registry; CDC |
| XML (ISO 20022) | Hierarchical | Via XSD | External schema | No (needs a specialised reader) | Raw zone: pain.001, pacs.008, camt.053; flatten to Parquet in curated |
Open table formats (Apache Iceberg, Delta Lake, Apache Hudi) sit on top of Parquet: they add a metadata layer for transactions, deletes and time travel. They are covered in Lake vs warehouse vs lakehouse.
Inside a Parquet file
- Row group: a horizontal slice of rows (Spark writes up to about 128 MB per row group by default; the Harbourline generator uses 100,000 rows).
- Column chunk: one column's values within a row group, compressed, made of pages, often dictionary-encoded (repeated values like
GBPstored once and referenced by a small integer). - Footer: the schema and statistics. Readers read the footer first, then fetch only the chunks they need with S3 range requests.
Two optimisations follow:
- Column pruning (projection): read only the columns the query references.
- Predicate pushdown: skip row groups whose min/max statistics cannot satisfy the
WHEREclause. It works best when data is sorted or clustered on the filter column. Partitioning skips whole files; statistics skip blocks inside files.
Measured on Harbourline's sample
The script in Complete code converts transactions.csv (1,012 rows) and prints this on the author's machine (your bytes may differ slightly by library version):
| Encoding | Size | vs CSV |
|---|---|---|
| CSV | 215.5 KB | 100% |
| JSON Lines | 446.6 KB | 207% |
| CSV + GZIP | 73.2 KB | 34% |
| Parquet, uncompressed | 134.3 KB | 62% |
| Parquet + Snappy | 105.8 KB | 49% |
| Parquet + ZSTD | 67.7 KB | 31% |
And the column-chunk sizes inside the Snappy file show where the bytes go:
| Column | Compressed bytes | Why |
|---|---|---|
idempotency_key | 37,945 | Random UUID v4: no repetition, compresses poorly |
transaction_id | 33,117 | UUID v7: time-ordered prefix helps a little |
reference, created_at, amount | 5–7 KB each | Mixed values |
currency | 93 | Two distinct values: dictionary plus run-length encoding |
status | 213 | Four distinct values |
A query that touches only booking_date, amount and currency needs 6,663 of 104,948 bytes (6%). That is the effect Athena bills you for. On 1,012 rows the absolute numbers are tiny; on the 1M-row large dataset (about 350 MB of CSV) the same ratios decide whether a query scans 350 MB or about 20 MB.
Why is JSON so large?
JSON Lines repeats every key name on every row ("transactionId":, "accountId": …). That is why it is twice the size of CSV before compression. It is still the right choice for raw API payloads and nested events; convert it to Parquet in curated.
Compression codecs
| Codec | Speed | Ratio | Splittable as a whole-file codec (CSV/JSON) | Inside Parquet/ORC | Notes |
|---|---|---|---|---|---|
| Snappy | Very fast | Moderate | No (not used for whole text files in practice) | Yes, per page | Spark and Glue default for Parquet |
| ZSTD | Fast | High | No | Yes | Best general choice for curated Parquet when readers support it (Athena engine v3 does) |
| GZIP | Slow | High | No | Yes | Common for partner files; a 2 GB .csv.gz is read by one task only |
| BZIP2 | Very slow | High | Yes | Rare | Splittable but CPU-heavy |
| LZ4 | Very fast | Low–moderate | No | Yes | Occasionally used for hot data |
The important rule: compression inside Parquet never prevents parallelism, because Parquet is split by row group. A single large GZIP-compressed CSV, on the other hand, cannot be split, so Spark or Athena reads it with one worker. If partners send large .csv.gz files, convert them to Parquet early.
Type mapping for Harbourline
| Field | Wrong choice | Right Parquet type | Athena / Glue type | C# type |
|---|---|---|---|---|
amount | DOUBLE / FLOAT | DECIMAL(18,2) | decimal(18,2) | decimal |
rate (FX) | DOUBLE | DECIMAL(18,6) | decimal(18,6) | decimal |
booking_date | STRING | DATE (INT32 days) | date | DateOnly |
created_at | STRING | TIMESTAMP(MILLIS, isAdjustedToUTC=true) | timestamp | DateTime (UTC) |
merchant_category_code | INT (loses leading zeros, e.g. 0742) | STRING | string | string |
account_id, IBANs | — | STRING | string | string |
Keep merchant_category_code as a string: codes such as 0742 (veterinary services) start with zero.
Schema evolution
Sources change: a new column appears, a column is renamed, a type widens. How each format copes:
| Change | CSV | JSON | Parquet in Athena | Iceberg |
|---|---|---|---|---|
| Add a column at the end | Works (new column null in old files) | Works | Works | Works |
| Add a column in the middle | Breaks: columns are read by position | Works (read by name) | Works (Athena reads Parquet by name by default) | Works |
| Rename a column | Breaks silently or shifts data | Old files return null for the new name | Old files return null for the new name | Works: Iceberg tracks columns by ID |
| Drop a column | Breaks if not the last | Works | Works | Works |
Widen int → bigint | n/a | Usually works | Often works; other changes fail with HIVE_BAD_DATA | Supported promotions only |
Change decimal(18,2) → decimal(20,2) | n/a | n/a | Supported widening in recent engines ⚠ VERIFY for your engine | Supported (precision widening) |
Practical rules:
- Treat schema as a contract. Producers announce changes; the pipeline validates the header (the lab's
RAW_COLUMNScheck raisesSchemaErroron an unexpected header and quarantines the whole file). - Add columns, don't rename or retype. If you must rename, add the new column, back-fill, and drop the old one later.
- Use Iceberg for curated tables when you expect regular schema change; it was designed for it.
- Version the contract (for example
schema_versionin the file name or metadata) for partner files.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Clustered columnstore index in SQL Server | Parquet/ORC files in S3 | Same columnar idea, but it is a file you can move and read with many engines |
| SSIS flat-file connection manager (column types per column) | Glue table definition or explicit schema in the reader | Schema-on-read: the file itself has no types for CSV |
Synapse serverless OPENROWSET(... FORMAT='PARQUET') | Athena over Parquet | Billed per byte scanned in both cases |
| ADF copy activity "CSV to Parquet" | Glue job, Athena CTAS, or Firehose record format conversion | Firehose converts JSON to Parquet/ORC on delivery |
System.Text.Json with decimal | JSON numbers parsed as decimal explicitly | Many JSON parsers default to double; the lab's Python uses parse_float=str then Decimal |
| Delta Lake in Fabric/Databricks | Apache Iceberg (first-class in Athena, Glue, Redshift, S3 Tables) | Delta is readable on AWS too; Iceberg has the broadest AWS support |
Architecture
Where each format lives in the Harbourline lake:
Raw keeps the original bytes (evidence). The transform is where text becomes typed data. Quarantine uses JSON Lines because rejected rows may not fit the typed schema.
Step-by-step implementation
Run the comparison script locally (free)
Save the Python script from Complete code as compare_formats.py in an empty working folder, then from that folder in Git Bash:
MSYS_NO_PATHCONV=1 docker run --rm --memory 1g \
-v "$PWD:/w" -v "C:/path/to/AWS-tutorial/datasets/samples:/data:ro" -w /w python:3.12-slim \
sh -c "pip install -q pyarrow==26.0.1 duckdb==2.0.0 && python compare_formats.py /data/csv/transactions.csv /data/json/transactions.jsonl /w/out"Compare your output with the tables above.
Read the Parquet metadata
Look at the per-column output: codec, compressed bytes, min and max. Note that booking_date min/max covers the whole period because the sample is one row group sorted by account, not by date. Sorting by booking_date before writing makes statistics far more selective.
Convert to partitioned Parquet in C#
Run the C# converter. It reads the CSV with explicit types, groups rows by booking date and writes one file per year=/month=/day= partition, with DECIMAL(18,2) amounts and ZSTD compression.
Query both with SQL
The script's last step runs the same aggregate with DuckDB over CSV and Parquet and prints identical results. Athena will do the same in the Athena lesson, where you will compare bytes scanned in the console. Lab 7.2 (CSV to partitioned Parquet) repeats this with PySpark on the 100k-row medium dataset.
Break the schema on purpose
Make a copy of the CSV, insert a column channel_detail between channel and counterparty_name, and run the lab's Python validator or your own header check. CSV readers that map by position now put channel_detail values into counterparty_name. That is exactly why the pipeline checks the header before reading.
AWS Console instructions
Nothing to deploy for this lesson. Two read-only places to look if you have the lake from the previous lesson:
- Console → S3 → hl-m07-raw-… → an object → Properties: shows size, storage class, encryption and checksum. S3 does not know or care about the format.
- Console → S3 → object → Actions → Query with S3 Select has been closed to new customers since 2024 ⚠ VERIFY whether it appears in your account; use Athena or DuckDB instead.
AWS CLI
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
# Upload the converted Parquet partition to the curated zone (if the lake from the previous lesson exists)
aws s3 cp out/curated/transactions/ "s3://hl-m07-curated-$ACCOUNT/transactions/" --recursive \
--exclude "*" --include "*.parquet"
# Compare object sizes per format
aws s3 ls "s3://hl-m07-curated-$ACCOUNT/transactions/" --recursive --summarize --human-readable
# Read only the last 8 bytes of a Parquet file (footer length + "PAR1") with a range request;
# this is what engines do first
aws s3api get-object --bucket "hl-m07-curated-$ACCOUNT" \
--key "transactions/year=2026/month=11/day=16/part-00000.zstd.parquet" \
--range bytes=-8 footer.bin && xxd footer.binThe range request shows the trick that makes Parquet efficient on S3: engines fetch the footer, then only the byte ranges of the column chunks they need.
Infrastructure as Code
Formats are chosen in job code and table definitions, not in infrastructure. Two places where IaC does set the format:
using Amazon.CDK;
using Amazon.CDK.AWS.Glue;
using Constructs;
namespace Harbourline.DataLake.Infra;
// A Glue table that declares the curated Parquet schema explicitly (L1 construct).
public sealed class CuratedTransactionsTable : Construct
{
public CuratedTransactionsTable(Construct scope, string id, string curatedBucket) : base(scope, id)
{
_ = new CfnTable(this, "Table", new CfnTableProps
{
CatalogId = Aws.ACCOUNT_ID,
DatabaseName = "hl_m07_curated",
TableInput = new CfnTable.TableInputProperty
{
Name = "transactions",
TableType = "EXTERNAL_TABLE",
Parameters = new Dictionary<string, string> { ["classification"] = "parquet" },
PartitionKeys = new object[]
{
new CfnTable.ColumnProperty { Name = "year", Type = "string" },
new CfnTable.ColumnProperty { Name = "month", Type = "string" },
new CfnTable.ColumnProperty { Name = "day", Type = "string" },
},
StorageDescriptor = new CfnTable.StorageDescriptorProperty
{
Location = $"s3://{curatedBucket}/transactions/",
InputFormat = "org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat",
OutputFormat = "org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat",
SerdeInfo = new CfnTable.SerdeInfoProperty
{
SerializationLibrary = "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe",
},
Columns = new object[]
{
new CfnTable.ColumnProperty { Name = "transaction_id", Type = "string" },
new CfnTable.ColumnProperty { Name = "account_id", Type = "string" },
new CfnTable.ColumnProperty { Name = "booking_date", Type = "date" },
new CfnTable.ColumnProperty { Name = "value_date", Type = "date" },
new CfnTable.ColumnProperty { Name = "amount", Type = "decimal(18,2)" },
new CfnTable.ColumnProperty { Name = "currency", Type = "string" },
new CfnTable.ColumnProperty { Name = "type", Type = "string" },
new CfnTable.ColumnProperty { Name = "channel", Type = "string" },
new CfnTable.ColumnProperty { Name = "counterparty_name", Type = "string" },
new CfnTable.ColumnProperty { Name = "merchant_category_code", Type = "string" },
new CfnTable.ColumnProperty { Name = "reference", Type = "string" },
new CfnTable.ColumnProperty { Name = "status", Type = "string" },
new CfnTable.ColumnProperty { Name = "created_at", Type = "timestamp" },
},
},
},
});
}
}The full table (with tokenised IBAN, lineage and data-quality columns) and the database are built in Glue Data Catalog and crawlers.
The other IaC-level format setting is Amazon Data Firehose record format conversion (JSON in, Parquet or ORC out), covered with streaming ingestion in the module overview.
Complete code
Python: measure the formats
"""Compare CSV, JSON Lines and Parquet for Harbourline's fictional transactions.
Usage: python compare_formats.py <transactions.csv> <transactions.jsonl> <out_dir>
"""
import gzip
import shutil
import sys
from pathlib import Path
import duckdb
import pyarrow as pa
import pyarrow.csv as pacsv
import pyarrow.parquet as pq
csv_path, jsonl_path, out = Path(sys.argv[1]), Path(sys.argv[2]), Path(sys.argv[3])
out.mkdir(parents=True, exist_ok=True)
# 1. An explicit schema: the data contract, not a guess.
schema = pa.schema([
("transaction_id", pa.string()),
("account_id", pa.string()),
("booking_date", pa.date32()),
("value_date", pa.date32()),
("amount", pa.decimal128(18, 2)),
("currency", pa.string()),
("type", pa.string()),
("channel", pa.string()),
("counterparty_name", pa.string()),
("counterparty_iban", pa.string()),
("merchant_category_code", pa.string()),
("reference", pa.string()),
("status", pa.string()),
("idempotency_key", pa.string()),
("created_at", pa.timestamp("ms", tz="UTC")),
])
table = pacsv.read_csv(
csv_path,
convert_options=pacsv.ConvertOptions(column_types=schema, strings_can_be_null=True),
)
table = table.select(schema.names) # keep the contract's column order
# 2. Write the same rows in several encodings.
gz_path = out / "transactions.csv.gz"
with open(csv_path, "rb") as src, gzip.open(gz_path, "wb") as dst:
shutil.copyfileobj(src, dst)
for codec in ("none", "snappy", "zstd"):
pq.write_table(table, out / f"transactions.{codec}.parquet", compression=codec)
def size(p: Path) -> str:
return f"{p.stat().st_size / 1024:8.1f} KB"
print(f"rows: {table.num_rows}")
print(f"CSV {size(csv_path)}")
print(f"JSON Lines {size(jsonl_path)}")
print(f"CSV gzip {size(gz_path)}")
for codec in ("none", "snappy", "zstd"):
print(f"Parquet {codec:<9} {size(out / f'transactions.{codec}.parquet')}")
# 3. What is inside a Parquet file: row groups, column chunks, min/max statistics.
meta = pq.ParquetFile(out / "transactions.snappy.parquet").metadata
print(f"\nrow groups: {meta.num_row_groups}, columns: {meta.num_columns}")
rg = meta.row_group(0)
for i in range(rg.num_columns):
col = rg.column(i)
stats = col.statistics
rng = f"min={stats.min} max={stats.max}" if stats is not None and stats.has_min_max else ""
print(f" {col.path_in_schema:<24} {col.compression:<7} {col.total_compressed_size:7d} B {rng}")
# 4. Column pruning: how many bytes would a query touching 3 columns read?
needed = {"booking_date", "amount", "currency"}
pruned = sum(rg.column(i).total_compressed_size for i in range(rg.num_columns)
if rg.column(i).path_in_schema in needed)
total = sum(rg.column(i).total_compressed_size for i in range(rg.num_columns))
print(f"\nbytes for 3 of 15 columns: {pruned} of {total} ({100 * pruned / total:.0f}%)")
# 5. The same query with DuckDB on CSV and on Parquet gives the same answer.
q = """SELECT currency, COUNT(*) AS n, SUM(amount) AS net
FROM {src} WHERE status = 'booked' GROUP BY currency ORDER BY currency"""
con = duckdb.connect()
print("\nCSV :", con.sql(q.format(src=f"read_csv('{csv_path.as_posix()}', header=true, types={{'amount':'DECIMAL(18,2)'}})")).fetchall())
print("Parquet:", con.sql(q.format(src=f"read_parquet('{(out / 'transactions.snappy.parquet').as_posix()}')")).fetchall())Expected last lines (both engines agree, to the penny):
bytes for 3 of 15 columns: 6663 of 104948 (6%)
CSV : [('EUR', 60, Decimal('6031.27')), ('GBP', 907, Decimal('158106.43'))]
Parquet: [('EUR', 60, Decimal('6031.27')), ('GBP', 907, Decimal('158106.43'))]C#: CSV to partitioned Parquet with Parquet.Net
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
<RootNamespace>Harbourline.DataLake.ToParquet</RootNamespace>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Parquet.Net" Version="6.1.0" />
</ItemGroup>
</Project>using System.Globalization;
using Parquet;
using Parquet.Schema;
namespace Harbourline.DataLake.ToParquet;
public sealed record Txn(string TransactionId, string AccountId, DateOnly BookingDate, decimal Amount,
string Currency, string Type, string Status, DateTime CreatedAt);
public static class Program
{
// Explicit Parquet schema: DECIMAL(18,2) for money, DATE and UTC TIMESTAMP(MILLIS) for time.
private static readonly DataField<string> IdField = new("transaction_id");
private static readonly DataField<string> AccountField = new("account_id");
private static readonly DateTimeDataField BookingField =
new("booking_date", DateTimeFormat.Date, isAdjustedToUTC: true, isNullable: false);
private static readonly DecimalDataField AmountField =
new("amount", 18, 2, forceByteArrayEncoding: true, isNullable: false);
private static readonly DataField<string> CurrencyField = new("currency");
private static readonly DataField<string> TypeField = new("type");
private static readonly DataField<string> StatusField = new("status");
private static readonly DateTimeDataField CreatedField =
new("created_at", DateTimeFormat.Timestamp, isAdjustedToUTC: true, unit: DateTimeTimeUnit.Millis, isNullable: false);
private static readonly ParquetSchema Schema = new(
IdField, AccountField, BookingField, AmountField, CurrencyField, TypeField, StatusField, CreatedField);
public static async Task<int> Main(string[] args)
{
if (args.Length != 2)
{
Console.Error.WriteLine("usage: ToParquet <transactions.csv> <outDir>");
return 2;
}
var rows = ReadCsv(args[0]).ToList();
foreach (var day in rows.GroupBy(r => r.BookingDate).OrderBy(g => g.Key))
{
var dir = Path.Combine(args[1], "curated", "transactions",
$"year={day.Key:yyyy}", $"month={day.Key:MM}", $"day={day.Key:dd}");
Directory.CreateDirectory(dir);
var path = Path.Combine(dir, "part-00000.zstd.parquet");
// Sort inside the file so min/max statistics on account_id are selective.
var batch = day.OrderBy(r => r.AccountId).ThenBy(r => r.CreatedAt).ToArray();
await WriteAsync(path, batch);
Console.WriteLine($"{path}: {batch.Length} rows, {new FileInfo(path).Length} bytes");
}
return 0;
}
private static async Task WriteAsync(string path, Txn[] batch)
{
await using var stream = File.Create(path); // overwrite: idempotent per partition
await using var writer = await ParquetWriter.CreateAsync(Schema, stream);
writer.CompressionMethod = CompressionMethod.Zstd;
using var group = writer.CreateRowGroup();
await group.WriteAsync(IdField, new ArraySegment<string>(batch.Select(r => r.TransactionId).ToArray()));
await group.WriteAsync(AccountField, new ArraySegment<string>(batch.Select(r => r.AccountId).ToArray()));
await group.WriteAsync(BookingField, new ReadOnlyMemory<DateTime>(
batch.Select(r => r.BookingDate.ToDateTime(TimeOnly.MinValue, DateTimeKind.Utc)).ToArray()));
await group.WriteAsync(AmountField, new ReadOnlyMemory<decimal>(batch.Select(r => r.Amount).ToArray()));
await group.WriteAsync(CurrencyField, new ArraySegment<string>(batch.Select(r => r.Currency).ToArray()));
await group.WriteAsync(TypeField, new ArraySegment<string>(batch.Select(r => r.Type).ToArray()));
await group.WriteAsync(StatusField, new ArraySegment<string>(batch.Select(r => r.Status).ToArray()));
await group.WriteAsync(CreatedField, new ReadOnlyMemory<DateTime>(batch.Select(r => r.CreatedAt).ToArray()));
}
private static IEnumerable<Txn> ReadCsv(string path)
{
using var reader = new StreamReader(path);
var header = reader.ReadLine()!.Split(',');
int Col(string n) => Array.IndexOf(header, n) is var i and >= 0
? i : throw new InvalidDataException($"missing column {n}");
var (id, acc, book, amt, ccy, type, status, created) = (Col("transaction_id"), Col("account_id"),
Col("booking_date"), Col("amount"), Col("currency"), Col("type"), Col("status"), Col("created_at"));
while (reader.ReadLine() is { } line)
{
var f = SplitCsv(line);
yield return new Txn(
f[id], f[acc],
DateOnly.ParseExact(f[book], "yyyy-MM-dd", CultureInfo.InvariantCulture),
decimal.Parse(f[amt], NumberStyles.AllowLeadingSign | NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture),
f[ccy], f[type], f[status],
DateTime.ParseExact(f[created], "yyyy-MM-dd'T'HH:mm:ss'Z'", CultureInfo.InvariantCulture,
DateTimeStyles.AdjustToUniversal | DateTimeStyles.AssumeUniversal));
}
}
// RFC 4180: quoted fields may contain commas and doubled quotes.
private static string[] SplitCsv(string line)
{
var fields = new List<string>();
var current = new System.Text.StringBuilder();
var quoted = false;
for (var i = 0; i < line.Length; i++)
{
var c = line[i];
if (quoted && c == '"' && i + 1 < line.Length && line[i + 1] == '"') { current.Append('"'); i++; }
else if (c == '"') quoted = !quoted;
else if (c == ',' && !quoted) { fields.Add(current.ToString()); current.Clear(); }
else current.Append(c);
}
fields.Add(current.ToString());
return fields.ToArray();
}
}Run it against the clean sample (the messy one fails parsing by design):
dotnet run --project ToParquet -- datasets/samples/csv/transactions.csv out/Code explained line by line
Python script
pa.schema([...])— the contract. Without it, pyarrow would guess types: amounts becomedouble, MCCs become integers, and a date column with one bad value becomes text.pa.decimal128(18, 2)— exact money.SUM(amount)returnsDecimal('158106.43'), not a floating-point approximation.pa.timestamp("ms", tz="UTC")— millisecond UTC timestamps, matching the generator and Athena's millisecond precision.strings_can_be_null=True— empty CSV fields become nulls (for examplecounterparty_ibanon card payments), as the dataset README specifies.pq.write_table(..., compression=codec)— the same table with three codecs, so the size difference is due only to compression.pq.ParquetFile(...).metadata— reads only the footer.row_group(0).column(i)exposes codec, compressed size and statistics: what Athena reads before deciding which bytes to fetch.- The "3 of 15 columns" calculation — a direct estimate of column pruning.
- DuckDB queries — the same SQL over both formats returns identical, exact results: Parquet changes cost and speed, not answers.
C# converter
DecimalDataField("amount", 18, 2, forceByteArrayEncoding: true, ...)— writes a ParquetDECIMAL(18,2)logical type, mirroring the generator's own writer indatasets/generator/src/Harbourline.Datasets/Tabular/ParquetTableWriter.cs.DateTimeDataField(..., DateTimeFormat.Date, ...)— a true ParquetDATE, which Athena reads asdatewithout casting.DateTimeTimeUnit.MilliswithisAdjustedToUTC: true— timestamps are instants in UTC.rows.GroupBy(r => r.BookingDate)— one partition per business date,year=/month=/day=with zero padding, matching the lake layout.OrderBy(r => r.AccountId)— sorting inside a file makes min/max statistics selective for account filters (predicate pushdown without over-partitioning).File.Create(path)— overwrite per partition: re-running a day replaces it (idempotent).writer.CompressionMethod = CompressionMethod.Zstd— ZSTD gives the smallest files in the measurement above; Snappy is the safe default if a reader lacks ZSTD support.group.WriteAsync(field, ...)— Parquet.Net writes column by column, the natural shape of a columnar file:ArraySegment<string>for reference types andReadOnlyMemory<T>for value types, as in the generator.Col(...)with an exception for a missing column — a header check; never assume column positions.NumberStyles.AllowLeadingSign | AllowDecimalPoint— rejects thousands separators like1,234.50, which the messy SFTP file contains deliberately.
Security considerations
⚠ Learning shortcut — not for production
The local scripts write curated files that still include counterparty_iban (Python) and keep everything on your disk unencrypted. In production, curated Parquet holds a tokenised IBAN and a masked display value (see harbourline_etl/masking.py), is written by the pipeline role only, and lands in a bucket encrypted with the curated CMK.
- Parquet does not hide data. Column statistics in the footer contain real min and max values. In the sample,
counterparty_namemin/max andcounterparty_ibanmin/max are visible to anyone who can read the footer. Tokenise or drop sensitive columns before writing curated files; do not rely on "nobody reads metadata". - Parquet modular encryption (column-level encryption in the format itself) exists in the Parquet specification but is not broadly supported by AWS engines ⚠ VERIFY; use KMS at the object level plus Lake Formation column permissions instead.
- Untrusted files. Partner files can be malformed or huge. Parse with limits (maximum line length, maximum columns) and quarantine whole files that fail the header check.
- Encoding. Accept UTF-8 only in contracts; detect and quarantine (or repair with a warning) mixed encodings, as the lab's validator does.
Banking use case
Harbourline's partner bureau sends payroll files as .csv.gz up to 2 GB at month end. Previously, an Athena table pointed directly at raw, and every month-end query read the full compressed CSV with a single reader and scanned 2 GB. The Data Platform team changed the flow:
- Raw keeps the original
.csv.gz(evidence, never modified). - A Glue job decompresses, validates the header and trailer (record count and control sum), types the columns with the contract schema, tokenises IBANs and writes ZSTD Parquet partitioned by execution date, sorted by debtor account.
- Analysts and the reconciliation job query curated Parquet; month-end queries now scan tens of MB.
- The quarantine zone receives rejected lines as JSON Lines with reason codes; operations resolves them with the partner.
Common mistakes
- Letting a reader infer types →
doublemoney, integer MCCs, dates as strings → always declare the schema. - Gzipping large CSVs and querying them directly → one reader per file, slow and costly → convert to Parquet early.
- Thousands of tiny Parquet files (one per micro-batch) → per-file overhead dominates → compact to roughly 128 MB–1 GB files.
- Renaming columns in Parquet → old files return nulls under the new name → add new columns instead, or use Iceberg.
- Assuming Parquet statistics are private → min/max leak sensitive values → tokenise before writing.
- Mixing timestamp units or time zones → off-by-hours errors around midnight → UTC milliseconds everywhere, convert for display only.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
Athena HIVE_BAD_DATA: Field amount's type BINARY in parquet is incompatible with type decimal(18,2) | Files written with different types for the same column | Rewrite the odd files, or align the table type with the files |
Athena HIVE_CANNOT_OPEN_SPLIT / "not a Parquet file" | A non-Parquet object (e.g. _SUCCESS, .crc, a CSV) in the table location | Keep only data files under the table prefix |
| All values null after adding a column | Table read by position (CSV) or name mismatch | Check column names and order; for CSV, only append columns at the end |
Amounts like 158106.42999999 | Read or written as double | Use DECIMAL(18,2) end to end |
pyarrow ArrowInvalid: In CSV column #4: CSV conversion error to decimal128(18, 2) | Bad value in the input (messy file) | Validate first, or read raw as strings and type in the transform |
Parquet.Net throws on DateOnly.ParseExact | Messy dates in input | Run on the clean sample, or add validation/quarantine before conversion |
| Query reads many more bytes than expected | SELECT *, or filters on unsorted columns | Select needed columns; sort on common filter columns; partition by date |
Cost and cleanup
Cost: free. Everything runs locally. If you uploaded Parquet to the curated bucket, storage for a few KB is effectively USD 0.00.
rm -rf out/ footer.bin compare_formats.py
# If you uploaded files to S3 and are not continuing today:
aws s3 rm "s3://hl-m07-curated-$ACCOUNT/transactions/" --recursive --profile learn-sandboxBucket teardown is in S3 data lake design.
Exercises
- 🟢 Guided: Run the Python script and record the six sizes. Then sort the table by
booking_datebefore writing (table.sort_by("booking_date")) and compare thebooking_datemin/max and file size. - 🟡 Challenge: Generate the
mediumdataset with--format csv,parquet(seedatasets/README.md), and use DuckDB'sEXPLAIN ANALYZEto compare a query filteringbooking_date = DATE '2026-10-15'on the CSV and on the generator's Parquet. Explain the difference in rows and bytes read. - 🔴 Live problem: After a partner's release, Athena queries on
curated.paymentsreturnNULLforamountfor November only. The partner says "we just renamedamttoamountand moved it aftercurrency". The curated job writes Parquet with whatever columns arrive. Diagnose the problem and design a fix that prevents it recurring.
Assignment
Add a format policy to your design note: which formats are allowed in each zone, codecs, target file size, required Parquet types for money and time, rules for schema changes, and how a producer announces one. Commit the Python script and your measurements table for the small and medium datasets.
Solutions
Sorting by booking_date makes the column's statistics tight per row group. With a single row group (1,012 rows) the min/max still covers the whole period, so you see little difference. The effect appears when there are many row groups: write with row_group_size=100 to simulate it, and each group's date range becomes a few days. File size usually shrinks slightly because sorted values compress better. Why: predicate pushdown depends on data clustering, not just on the format.
On CSV, DuckDB must read and parse the whole file to evaluate the filter. On Parquet it reads the footer, skips row groups whose booking_date range excludes 2026-10-15, and reads only the columns referenced. EXPLAIN ANALYZE shows far fewer rows scanned and a much lower time for Parquet. In Athena the same effect shows up as Data scanned in the query stats, which is what you pay for. Partitioning by date (year=/month=/day=) would skip whole files before the reader opens them.
Cause: the curated job writes files with the partner's new column name or position, and the table definition and older files disagree. If the raw file is CSV and the job maps by position, amount values may be landing in the wrong column; if it maps by name, the old amt column is now missing. Fix: (1) quarantine November's files and re-run them after mapping amt → amount in a versioned contract; (2) validate the header against the expected contract before reading (as RAW_COLUMNS does) and fail the file with SCHEMA_COLUMN_COUNT or a schema-version error; (3) agree a data contract with the partner: schema version in the file name, notice period for changes, and a test file; (4) consider Iceberg for curated so renames are tracked by column ID. Also add a data-quality rule "amount is not null" so this is caught on the first run, not by an analyst.
Knowledge check
Answer: Athena bills by bytes scanned. Parquet is compressed and columnar, so Athena reads only the columns in the query and can skip row groups using min/max statistics; CSV must be read in full.
Answer: The Parquet file. Parquet is split by row group whatever the codec. A GZIP stream cannot be split, so one reader processes the whole CSV.
Answer: DECIMAL(18,2). It is exact; floating-point types introduce rounding errors that break reconciliation.
Answer: CSV is read by position, so every column after the insertion point shifts and data appears under the wrong names. Only append columns at the end of CSV schemas, or use a self-describing format.
Answer: Column statistics include actual minimum and maximum values, so a reader of the footer can see sample values of sensitive columns. Tokenise or remove them before writing.
Interview questions
- "Parquet or ORC?" — Both are columnar with statistics and good compression. On AWS, Parquet has the broadest support (Athena, Glue, Redshift Spectrum, Firehose conversion, Iceberg default). Pick one per lake for consistency; Parquet is the usual default.
- "How do you handle schema changes from upstream?" — Treat schema as a versioned contract, validate it at ingestion, allow additive changes, quarantine incompatible files, and use Iceberg for tables that evolve often. Never let a writer silently change a curated table's types.
- "Why are many small files bad?" — Each file costs a request and per-file overhead in planning and opening; engines spend more time on metadata than data. Compact to large files.
Certification practice
A data engineer stores 5 TB of daily CSV files in S3. Analysts run Athena queries that select 4 of 60 columns and filter on transaction date. Costs are high. Which change reduces cost MOST with the least effort for analysts?
A. Compress the CSV files with GZIP. B. Convert the data to Parquet partitioned by transaction date and update the table definition. C. Move the data to S3 Glacier Instant Retrieval. D. Increase the Athena workgroup's per-query data limit.
Answer: B. Columnar Parquet reads only 4 columns, and date partitions skip files outside the filter. A reduces bytes but still reads every column and makes large files unsplittable. C changes storage cost, not scan cost (and adds retrieval charges). D does not reduce cost; it only caps it.
A Glue job converts JSON transaction events to Parquet. Reconciliation shows totals differ by fractions of a penny from the source system. What is the MOST likely cause and fix?
A. Snappy compression is lossy; switch to GZIP.
B. Amounts are parsed as floating point; cast them to DECIMAL(18,2) before writing.
C. Parquet cannot store negative numbers; store debits separately.
D. The Glue job needs more DPUs.
Answer: B. JSON numbers are commonly parsed as doubles; casting to decimal preserves exact values. A: Snappy and GZIP are both lossless. C is false. D affects speed, not precision.
A nightly job processes one 50 GB GZIP-compressed CSV file with Spark. Adding workers does not reduce run time. Why, and what helps?
A. Spark cannot read GZIP; decompress first. B. GZIP files are not splittable, so one task reads the whole file; have the producer send multiple smaller files or convert to Parquet before processing. C. The S3 bucket is in the wrong storage class. D. Spark needs a larger driver.
Answer: B. A single GZIP stream must be read sequentially by one task. A: Spark reads GZIP, just not in parallel. C and D do not address the bottleneck.
Completion checklist
- I can explain row vs columnar layout with the ledger analogy
- I measured CSV, JSON Lines and Parquet sizes on the sample
- I read Parquet footer statistics and estimated column-pruning savings
- I wrote partitioned Parquet with
DECIMAL(18,2)from C# - I can list which schema changes are safe for CSV, Parquet and Iceberg
Key takeaways
- Raw keeps source formats; curated and gold use Parquet (or Iceberg on Parquet) with explicit types.
- Columnar storage plus compression plus partitions plus sorting is what makes Athena fast and cheap.
- Parquet compression never blocks parallelism; GZIP on large text files does.
- Money is
DECIMAL(18,2), dates areDATE, timestamps are UTC milliseconds, codes with leading zeros are strings. - Schema evolution is a contract problem first and a format problem second; Iceberg handles renames safely.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
S3 data lake design: zones, partitions, naming, lifecycle and encryption
Design Harbourline's S3 data lake with raw/bronze, curated/silver and consumption/gold zones, Hive-style partitions, predictable naming, lifecycle rules and KMS encryption, then deploy it with CDK (C#) or Terraform and land a file from C#.
Data lake vs warehouse vs lakehouse: Apache Iceberg, time travel and when Redshift still wins
Compare data lakes, data warehouses and lakehouses, create an Apache Iceberg table for Harbourline's curated transactions in Athena, correct late-arriving data with MERGE, query history with time travel, and decide when Amazon Redshift or S3 Tables is the better answer.