1. Overview
Data processing is a core daily responsibility for DevOps engineers and Site Reliability Engineers (SREs). Whenever you parse structured application logs, aggregate metrics, transform API payloads, or prepare configuration files for system import, you are performing data processing.
The primary goal of operational data processing is to establish reliable Extract $\rightarrow$ Transform $\rightarrow$ Load (ETL) workflows: extracting raw data from a source, shaping and validating its structure, and loading it into downstream systems where it can be queried or consumed. In modern Linux infrastructure, mistakes in data processing lead directly to broken monitoring dashboards, failed database imports, or silent data corruption. Consequently, schema validation and reproducible pipelines are just as important as rapid, single-line shell commands.
+------------------+ +-------------------+ +------------------+
| Extract Source | ---> | Transform Engine | ---> | Load Target |
| (APIs, Logs, S3) | | (jq, Miller, SQL) | | (DB, Warehouse) |
+------------------+ +-------------------+ +------------------+
Common Data Formats
Data formats trade human readability, schema enforcement, binary compactness, and tool ecosystem support. On Linux virtual machines, text-based formats are essential for ad-hoc inspection and immediate repair, while high-scale analytics engines rely on binary columnar storage.
- CSV (Comma-Separated Values): Plain text organized in rows and columns. Ubiquitous for database dumps, spreadsheets, and vendor exports. Limitations include ambiguous quoting rules, embedded commas or newlines, lack of support for nested structures, and character encoding issues (UTF-8 vs. Latin-1/ISO-8859-1). Always inspect the delimiter, headers, and row consistency.
- JSON (JavaScript Object Notation): The default format for web APIs, Kubernetes manifests, CI/CD pipeline artifacts, and structured log shippers (e.g., Fluent Bit, CloudWatch). Supports typed values (strings, numbers, booleans, null, arrays, objects) and deeply nested structures. Parsing requires path navigation (
.items[].metadata.name), filtering (select()), and strict syntax validation. - Parquet: An open-source columnar binary storage format engineered for analytical querying. Provides high compression ratios and rapid column scanning. Standard in Apache Spark, DuckDB, BigQuery, and cloud data lakes. Requires dedicated tools (
parquet-tools, DuckDB, Pythonpyarrow) to inspect, as schemas are embedded directly in file metadata. - Avro: A compact, row-based binary format featuring embedded or schema-registry-backed schemas. Common in streaming architectures such as Apache Kafka. Supports formal schema evolution rules, allowing producers and consumers to evolve independently.
- Protocol Buffers (Protobuf): Google’s language-neutral binary serialization framework defined via
.protointerface definition files. Heavily used in gRPC microservices and internal telemetry collectors. Highly efficient over the wire with strict field numbering, requiring generated stubs or reflection tools (protoc --decode) for decoding.
Data Format Comparison
| Format | Human-Readable on VM | Nested Data Support | Analytics at Scale | Primary DevOps / SRE Use Case |
|---|---|---|---|---|
| CSV | Yes | Poor (flattened or embedded JSON) | Heavy / Inefficient | Quick exports, legacy logs, reports |
| JSON | Yes | Native (Objects and Arrays) | Moderate (heavy RAM footprint) | REST APIs, K8s manifests, log shipping |
| Parquet | No (Requires tools) | Native | Excellent (Columnar compression) | Data lakes, metric archives, long-term analytics |
| Avro | No (Requires schema) | Native | Good (Row-based streaming) | Event streaming (Kafka), schema registries |
| Protobuf | No (Requires .proto) |
Native | Good (RPC payloads) | Microservice RPCs (gRPC), telemetry agents |
2. Architecture and Mental Model
To effectively diagnose data pipeline failures, an SRE must maintain a clear mental model of data flow, execution models, and storage architectures.
+-----------------------------------------------------------------------------------+
| ETL vs ELT ARCHITECTURE |
| |
| ETL: [ Extract ] ---> [ Transform (App Server) ] ---> [ Load (Target DB) ] |
| |
| ELT: [ Extract ] ---> [ Load Raw (Data Lake) ] ---> [ Transform (Data DB) ] |
+-----------------------------------------------------------------------------------+
ETL vs. ELT Processing Patterns
Data pipelines ingest data, alter its representation, and deliver it to destination systems:
- Extract: Pull raw data from heterogeneous endpoints, including SQL/NoSQL databases, REST/gRPC APIs, system log files (
/var/log), cloud object storage (AWS S3, GCP GCS), or streaming brokers (Kafka, RabbitMQ). - Transform: Clean invalid records, deduplicate key space, cast data types, flatten nested JSON objects, join reference tables, and apply business logic or redactions.
- Load: Write the output into targeted storage platforms like PostgreSQL, ClickHouse, Snowflake, BigQuery, or local file archives.
In ELT (Extract, Load, Transform), raw data is loaded directly into scalable data storage first, deferring transformation steps to SQL engines inside the warehouse platform. This separates ingestion failure domains from transformation logic.
Batch vs. Streaming Execution Models
Pipelines execute across two distinct operational paradigms:
- Batch Processing: Processes bounded, finite datasets on fixed schedules (e.g., hourly log rotations, 2 AM cron jobs, daily Spark batch runs). Batch jobs trade freshness for simplicity: they feature higher latency, simpler error-handling guarantees, and straightforward replayability upon failure.
- Stream Processing: Processes unbounded streams of continuous events in real time (e.g., Kafka event logs, socket streams, Prometheus metric ingestion). Streaming provides low latency (sub-second to seconds) but adds operational complexity due to out-of-order events, network partitions, and state maintenance.
Architectures combining both include Lambda Architecture (running parallel batch and speed layers) and Kappa Architecture (a stream-only pipeline where historical backfills reprocess events directly from log brokers).
+-------------------------------------------------+
| BATCH PROCESSING MODEL |
| [ Source ] -> ( Hourly Buffer ) -> [ Transform ]|
+-------------------------------------------------+
|
v
+-------------------------------------------------+
| STREAM PROCESSING MODEL |
| [ Source ] -> [ Event Stream ] -> [ Real-time ]|
+-------------------------------------------------+
Data Lakes vs. Data Warehouses
- Data Lake: Low-cost object storage (S3/GCS) holding raw, semi-structured, and structured datasets in open formats (Parquet, JSON) organized by path partitions (e.g.,
s3://bucket/raw/events/year=2026/month=11/). Schemas are applied at query time (“schema-on-read”) using engines like DuckDB, Trino, or Spark. - Data Warehouse: Curated, highly optimized storage with enforced schemas (“schema-on-write”) engineered specifically for fast SQL analytics.
3. Core Concepts and Terminology
Understanding foundational data engineering concepts helps prevent silent failure modes in shell scripts and automated production pipelines.
- Schema: The defined specification of column names, data types (string, integer, timestamp), and nullability constraints. Schemas must be validated at pipeline boundaries to prevent “garbage-in, garbage-out” failures.
- Schema Evolution: The capacity of a data serialization system to handle additions, deletions, or modifications of fields over time without breaking existing producers or downstream consumers. Avro and Protobuf excel at schema evolution via strict field-id indexing.
- Idempotency: A property of a pipeline step where running it multiple times with identical inputs produces the exact same output state without creating duplicate entries. Idempotent loads utilize unique key constraints (
INSERT OR REPLACEorMERGEstatements). - Data Quality and Validation: The continuous verification of incoming records against integrity constraints (e.g., expected row counts, non-null primary keys, valid date ranges). Bad data should either halt execution (fail-fast) or be routed to a dead-letter quarantine queue.
- Partitioning: Segmenting large datasets into smaller directory structures based on high-cardinality keys (such as
date,region, ortenant_id). Partitioning enables partition pruning, allowing query engines to scan only relevant subsets of data rather than reading multi-terabyte datasets. - Columnar vs. Row-Oriented Storage: Row-based formats (CSV, Avro) store all values for a single record contiguous on disk, making them ideal for single-record writes. Columnar formats (Parquet) store all values for a single column contiguous on disk, enabling extreme compression and rapid aggregates across specific attributes.
- Data Lineage: The auditable trace mapping data provenance—tracking which source file, pipeline run, or transformation script generated a specific target dataset.
- Delivery Semantics: Guarantees provided by data transport engines:
- At-least-once: Records are guaranteed to be delivered, but duplicates may occur during network retries.
- Exactly-once: System guarantees each record affects the destination state exactly once.
4. Installation and Version Management
Managing data processing tools requires configuring repositories and installing light CLI utilities for text manipulation alongside single-binary SQL engines for local VM analytics.
Installing Command-Line Utilities
On Debian / Ubuntu Systems
# Update package index and install essential CLI tools
sudo apt-get update
sudo apt-get install -y \
jq \
python3 \
python3-pip \
csvkit \
miller \
sqlite3 \
protobuf-compiler \
gettext-base
On RHEL / Fedora / Rocky Linux Systems
# Install EPEL repository and processing utilities
sudo dnf install -y epel-release
sudo dnf install -y \
jq \
python3 \
python3-pip \
miller \
sqlite \
protobuf-compiler
Installing DuckDB (Standalone Binary)
DuckDB acts as an in-process, zero-dependency SQL engine optimized for querying local CSV, JSON, and Parquet files directly.
# Download and install DuckDB binary
curl -LO https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
sudo mv duckdb /usr/local/bin/
rm duckdb_cli-linux-amd64.zip
Installing Python Parquet Inspection Tools
# Install parquet-tools via pip
pip3 install parquet-tools
Tool Version Verification
Verify that installed CLI utilities execute correctly:
jq --version
mlr --version
duckdb --version
protoc --version
python3 -m csvkit --version
Installation Path Reference
| Tool | Executable Path | Primary Responsibility |
|---|---|---|
| jq | /usr/bin/jq |
JSON parsing, filtering, and reshaping |
| miller (mlr) | /usr/bin/mlr |
CSV/JSON/TSV stream transformations |
| duckdb | /usr/local/bin/duckdb |
Single-node SQL queries on CSV/JSON/Parquet files |
| sqlite3 | /usr/bin/sqlite3 |
In-memory and file-backed relational data processing |
| csvcut / csvstat | /usr/bin/csvcut |
CSV schema analysis and column extraction |
| iconv | /usr/bin/iconv |
Character set encoding conversions (e.g., ISO-8859-1 to UTF-8) |
5. Configuration Files and Directory Structure
Data processing utilities rely on local environment settings, configuration paths, and temporary buffer directories during pipeline execution.
Directory Layout
| File / Directory | Purpose | Operational Details |
|---|---|---|
~/.jq |
User-defined custom jq function library |
Contains reusable filtering routines loaded automatically by jq. |
/etc/environment |
System-wide locale and encoding configuration | Must define LANG=UTF-8 and LC_ALL=UTF-8 to prevent encoding errors. |
/tmp/ |
Default temporary file buffer | Used for sorting, uncompressing files, and storing transient chunks. |
/var/tmp/ |
Persistent temporary buffer | Preferred for large pipeline chunking scripts (split) to prevent RAM exhaustion. |
Environment Variables
- **
LANG/LC_ALL**: Specifies character encoding semantics. If set to non-UTF-8 values (e.g.,CorPOSIX), tools processing multibyte JSON characters may corrupt output or fail with encoding exceptions. TMPDIR: Overrides the default location for temporary scratch files generated bysort,jq, orduckdb. Set to a directory on a large volume when processing multi-gigabyte files to avoid filling up root filesystems.
# Example setting environment variables for data processing jobs
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
export TMPDIR=/var/tmp/pipeline_scratch
mkdir -p $TMPDIR
6. Service Management and Common Operations
While CLI tools operate directly on streams, data pipelines frequently execute as system daemons, cron jobs, or containerized workers.
Managing Pipeline Daemons via systemd
# Check status of log processing agent (e.g., Fluent Bit or Vector)
systemctl status vector.service
# Reload log pipeline configuration without restarting service
sudo systemctl reload vector.service
# Restart pipeline daemon after schema or output path changes
sudo systemctl restart vector.service
Monitoring Active File Processing Operations
# Inspect running pipeline processes and memory consumption
ps aux | grep -E 'jq|mlr|duckdb|python'
# Monitor dynamic system memory and swap utilization during heavy jobs
free -h
top -p $(pgrep -d',' -f 'duckdb|jq|mlr')
7. Common Administrative Tasks and Workflows
Task 1: Validating and Cleaning Malformed JSON Logs
Objective
Validate raw JSON application log streams, drop malformed lines, and extract specific error messages for incident analysis.
Step-by-Step Procedure
- Verify JSON Syntax: Use
jq emptyto quickly validate the file’s syntax.
jq empty /var/log/app/stream.json
- Isolate Syntax Failures: If
jqreports errors, filter line-by-line using Python’sjson.toolto isolate bad records.
python3 -c '
import sys, json
for idx, line in enumerate(sys.stdin, 1):
try:
json.loads(line)
except Exception as e:
print(f"Line {idx} invalid: {e}")
' < /var/log/app/stream.json
- Filter and Extract Valid Log Records: Extract key values from valid records while ignoring invalid JSON payloads.
jq -r 'select(.level == "ERROR") | [.timestamp, .service, .message] | @tsv' /var/log/app/stream.json > errors.tsv
Validation
Confirm the extracted TSV file contains expected attributes:
head -n 5 errors.tsv
Task 2: Ingesting and Querying Large CSV Files via DuckDB
Objective
Query a 5 GB server access log in CSV format without loading the entire file into RAM or setting up a database server.
Step-by-Step Procedure
- Inspect CSV Headers: Check the CSV file’s header structure.
head -n 1 /var/log/nginx/access_combined.csv
- Execute In-Memory SQL Query: Use DuckDB to infer column types, perform aggregations, and calculate status code distributions.
duckdb -c "
SELECT
status,
COUNT(*) AS total_requests,
AVG(bytes_sent) AS avg_bytes
FROM read_csv_auto('/var/log/nginx/access_combined.csv')
GROUP BY status
ORDER BY total_requests DESC;
"
- Export Results to Parquet: Convert the raw CSV into compressed Parquet format for long-term archiving.
duckdb -c "
COPY (
SELECT * FROM read_csv_auto('/var/log/nginx/access_combined.csv')
) TO '/var/log/nginx/2026-11-03_access.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
"
Validation
Verify the Parquet file schema and file size reduction:
duckdb -c "DESCRIBE SELECT * FROM '/var/log/nginx/2026-11-03_access.parquet';"
ls -lh /var/log/nginx/access_combined.csv /var/log/nginx/2026-11-03_access.parquet
8. CLI Command Reference and Cheatsheet
1. JSON Processing (jq)
| Command | Purpose | Important Flags / Arguments | Practical Example | Explanation |
|---|---|---|---|---|
jq . |
Pretty-print JSON | None | jq . config.json |
Formats raw single-line JSON into indented, human-readable structure. |
jq empty |
Syntax Validation | None | jq empty data.json |
Validates JSON structure; exits with status 0 if valid, non-zero if invalid. |
jq '.path' |
Extract Field | -r (raw text) |
jq -r '.items[].name' data.json |
Extracts target key value, removing surrounding quotes. |
jq 'select()' |
Filter Objects | None | jq 'select(.status=="error")' log.json |
Filters JSON stream to keep only objects matching predicate. |
jq -s |
Slurp Files | -s (read into array) |
jq -s 'add' *.json |
Reads all incoming JSON objects into a single array before processing. |
Key Syntax Examples
# Extract Kubernetes pod names from live cluster output
kubectl get pods -o json | jq -r '.items[].metadata.name'
# Flatten nested JSON structure into key-value keypaths
jq -r 'paths(scalars) as $p | [($p | map(tostring) | join(".")), getpath($p)] | @tsv' input.json
2. CSV & Tabular Text Processing
| Command | Purpose | Flags / Options | Example | Explanation |
|---|---|---|---|---|
head |
Inspect Headers | -n (line count) |
head -n 5 records.csv |
Displays top lines to verify column delimiters and header names. |
wc -l |
Count Rows | -l (lines) |
wc -l records.csv |
Counts lines (header + data rows). |
cut |
Field Extraction | -d (delimiter), -f (fields) |
cut -d, -f1,3 input.csv |
Cuts specified numeric fields; fails if commas exist within quotes. |
csvcut |
Named CSV Extraction | -c (column names) |
csvcut -c id,email data.csv |
Extracts columns safely by header name, handling RFC 4180 quotes. |
csvgrep |
Filter CSV Rows | -c (col), -m (match string) |
csvgrep -c status -m active data.csv |
Retains rows matching search pattern. |
csvstat |
Column Statistics | None | csvstat data.csv |
Analyzes column data types, null rates, ranges, and unique counts. |
awk |
Numeric Aggregation | -F (field separator) |
awk -F, '$3 > 100 {s+=$3} END {print s}' input.csv |
Aggregates numeric values across rows. |
3. Miller (mlr) Transformations
Miller processes record-oriented data formats (CSV, TSV, JSON) using field names, functioning like awk, sed, cut, and sort combined.
| Command / Verb | Input/Output Flags | Example | Purpose |
|---|---|---|---|
mlr filter |
--csv, --json |
mlr --csv filter '$status == "active"' input.csv |
Filters records by logical expression. |
mlr put |
--json, --csv |
mlr --json put '$total = $qty * $price' orders.json |
Calculates new field values dynamically. |
mlr join |
--csv |
mlr --csv join -l id -r uid then values.csv keys.csv |
Performs relational joins on datasets matching specified key columns. |
4. Direct SQL Querying on Files
# SQLite: Import CSV file into temp in-memory DB and query
sqlite3 :memory: <<'EOF'
.mode csv
.import users.csv users
SELECT country, COUNT(*) FROM users GROUP BY country;
EOF
# DuckDB: Query CSV and Parquet files directly from bash
duckdb -c "SELECT * FROM 'sales.csv' WHERE amount > 100 NULLS LAST;"
duckdb -c "SELECT * FROM 'lake/events.parquet' LIMIT 10;"
5. Binary Serialization Inspection
# Inspect Parquet File Metadata and Schema
duckdb -c "DESCRIBE SELECT * FROM 'metrics.parquet'"
parquet-tools meta metrics.parquet
# Decode Protobuf Binary stream using `.proto` schema file
protoc --decode=SensorReadout schema.proto < payload.bin
9. Logs, Monitoring and Diagnostics
When troubleshooting data processing jobs, SREs use a 3-step diagnostic workflow to isolate failure points before applying fixes.
+-----------------------------------------------------------------------------+
| 3-STEP DATA DIAGNOSTIC WORKFLOW |
| |
| Step 1: Identify Format & Sample ---> file data.* |
| head -n 3 data.csv |
| jq . data.json 2>&1 | head -n 20 |
| |
| Step 2: Validate Structure ---> jq empty data.json |
| csvstat data.csv |
| wc -l data.csv |
| |
| Step 3: Isolate Transformation ---> jq 'select(...)' raw.json | wc -l |
| sqlite3 :memory: "SELECT COUNT(*)..."|
+-----------------------------------------------------------------------------+
Step 1: Identify Format and Sample Input
Determine file encoding, MIME type, and structural composition:
file data.*
head -n 3 data.csv
jq . data.json 2>&1 | head -n 20
Step 2: Validate Structure and Integrity
Examine line counts, check syntax, and generate column statistics:
jq empty data.json
csvstat data.csv
wc -l data.csv
Step 3: Isolate Transformation Steps
Run pipeline components independently to isolate row-drop issues or logic bugs:
# Isolate extraction stage row count
jq 'select(.status == "PROCESSED")' raw.json | wc -l
# Test load count in temporary memory table
sqlite3 :memory: ".import --csv t data.csv" "SELECT COUNT(*) FROM t"
10. Troubleshooting Guide and Common Failure Scenarios
Problem 1: Invalid JSON / Syntax Parse Error from jq
Symptoms
jq terminates with non-zero exit code, returning errors such as: jq: parse error: Invalid numeric literal at line 45, column 12.
Possible Causes
- Trailing commas after final object fields (
{"a": 1,}). - Single-quoted keys or values (
{'key': 'value'}). - Non-standard JSON values (
NaN,Infinity). - An upstream HTTP API returned an HTML error page (e.g.,
502 Bad Gateway) instead of expected JSON. - Truncated payload download caused by connection drop.
Investigation and Diagnosis
- Execute
jq emptyto check for syntax validity. - Print the first 200 bytes of the payload to check for HTML error pages or garbage headers:
head -c 200 payload.json
- If inspecting an HTTP response, check the status code and
Content-Typeheader before piping tojq:
curl -s -D headers.txt https://api.internal/v1/metrics -o body.json
cat headers.txt | grep -i 'content-type'
Root Cause Identification
If head -c 200 displays <html><body>504 Gateway Time-out, the root cause is upstream API gateway failure, not bad JSON generation.
Resolution
Fix payload generation at the upstream source. If processing malformed JSON lines with single quotes, sanitize using Python’s ast.literal_eval before sending to jq—do not rely on fragile regular expressions.
Verification
jq empty payload.json && echo "JSON Valid"
Prevention
Enforce payload schema validation at API gateways and fail ingestion early if Content-Type is not application/json.
Problem 2: jq Returns null or Empty Output for Expected Fields
Symptoms
Transformation commands run without throwing syntax errors but return empty strings or null values.
Possible Causes
- Incorrect key paths specified in the
jqfilter expression. - Key casing mismatch (e.g.,
.namevs.Name). - Unhandled array nesting (e.g., querying
.items.nameinstead of.items[].name). - API version updates changing response schemas dynamically.
Investigation and Diagnosis
- Inspect available keys on a sample record:
jq 'keys' payload.json
- Check the data type of the root node:
jq 'type' payload.json
- Dump all keypaths in the document to discover the correct path:
jq 'paths' payload.json | head -n 20
Resolution
Update path expressions to reflect the correct schema structure. Use optional path chaining (.field?) to prevent pipeline crashes when optional keys are absent.
# Correcting access to an array of objects
jq -r '.items[]?.metadata?.name' payload.json
Verification
jq '.[0] | keys' payload.json
Prevention
Maintain explicit schema definitions and log warnings when payload structure changes across API versions.
Problem 3: Misaligned CSV Columns and Ragged Rows
Symptoms
CSV parsing fails, data is shifted into incorrect columns, or row-count errors occur downstream.
Raw CSV Line: 1001,John Doe,"New York, NY",Active
Naively Split: Field1=1001 | Field2=John Doe | Field3="New York | Field4=NY" | Field5=Active <-- CORRUPTED
Possible Causes
- Unquoted commas inside field values.
- Embedded newline characters within long string fields.
- Mismatched column delimiters (e.g., European exports using semicolons
;instead of commas,).
Investigation and Diagnosis
- Inspect raw CSV headers and early rows:
head -n 5 input.csv
- Determine the expected column count from header line 1:
head -n 1 input.csv | tr ',' '\n' | wc -l
- Run AWK to flag rows where the column count differs from expected value $N$:
awk -F, -v N=4 'NF != N {print "Line " NR ": " $0}' input.csv
- Run
csvstatormlr --csv checkfor comprehensive structure reports.
Resolution
Re-export the dataset from the source system using strict RFC 4180 quoting rules. Avoid using simple line-splitting tools like cut or split(',') on CSVs with complex fields. Instead, process streams using dedicated parsers like csvkit or miller.
# Using Miller to safely clean and re-format CSV data
mlr --csv clean-whitespace input.csv > cleaned.csv
Verification
Re-run column count verification:
awk -F, -v N=4 'NF != N' cleaned.csv
Prevention
Validate incoming CSV files against expected column counts using csvstat prior to triggering ETL database ingestion.
Problem 4: Encoding Mismatch and Garbled Characters (Mojibake)
Symptoms
Non-ASCII characters appear corrupted (e.g., München instead of München). CSV or JSON processing tools fail with encoding error messages.
Possible Causes
Source files exported using legacy code pages (e.g., ISO-8859-1 / Latin-1 or Windows-1252) processed by system pipelines configured for UTF-8. Per the standard, JSON must be encoded in UTF-8.
Investigation and Diagnosis
- Inspect file charset using
file:
file -bi export.csv
# Output: text/plain; charset=iso-8859-1
- Search for non-UTF-8 sequence markers:
iconv -f UTF-8 -t UTF-8 export.csv > /dev/null
Resolution
Convert file encoding to UTF-8 using iconv before passing data downstream:
iconv -f ISO-8859-1 -t UTF-8 export.csv -o export_utf8.csv
Verification
Confirm file encoding is now recognized as UTF-8:
file -bi export_utf8.csv
# Output: text/plain; charset=utf-8
Prevention
Configure exporting application environments with explicit LANG=en_US.UTF-8 and LC_ALL=en_US.UTF-8 settings.
Problem 5: SQL Import or Direct File Query Failure
Symptoms
SQLite .import commands or DuckDB direct SQL queries fail with syntax or schema casting errors.
Possible Causes
- Header line imported as data record.
- Unescaped double quotes inside values.
- Numeric fields formatted as strings (e.g., containing currency symbols like
$1,200.00).
Investigation and Diagnosis
Check column data types inferred by DuckDB:
duckdb -c "DESCRIBE SELECT * FROM read_csv_auto('sales.csv');"
Resolution
Clean string formatting prior to running SQL casts, or specify explicit column schemas during CSV load operations:
# Using SQL string replacements to clean numeric columns during ingestion
duckdb -c "
SELECT
id,
CAST(REPLACE(REPLACE(price, '$', ''), ',', '') AS DECIMAL(10,2)) AS clean_price
FROM read_csv_auto('sales.csv');
"
Verification
Confirm data types in the generated target table match expected specifications.
Problem 6: Out of Memory (OOM) Errors on Large Datasets
Symptoms
jq or Python scripts processing multi-gigabyte log files crash or get terminated by the Linux Kernel OOM Killer (Out of memory: Kill process).
Possible Causes
Attempting to load entire multi-gigabyte JSON files or CSV arrays into memory simultaneously (e.g., running jq -s or json.load() on a 10 GB file).
Investigation and Diagnosis
Check system resource utilization and log messages:
dmesg -T | grep -i oom
ls -lh large_input.json
Resolution
Process files in streaming mode (line-by-line) rather than buffering full datasets in memory.
- For JSON, convert to NDJSON (JSON Lines) and process iteratively with
jq -c. - For CSV, use Miller’s streaming engine (
mlr --csv stream). - Split large files into smaller chunks using
split:
# Split a 50GB CSV file into 100,000-line chunks
split -l 100000 --additional-suffix=.csv big_data.csv chunk_
# Process each chunk sequentially or in parallel using xargs
ls chunk_*.csv | xargs -P 4 -I {} mlr --csv filter '$amount > 50' {} > output_{}
Verification
Confirm memory usage remains stable during processing:
free -m
Problem 7: Duplicate or Missing Rows After ETL Run
Symptoms
Row counts at target destinations do not match source extracts, or duplicate keys appear following job retries.
Possible Causes
- Non-idempotent pipeline steps inserting duplicate rows during retries.
- Overly restrictive filter logic dropping records containing
nullvalues. - Timezone mismatch across partition dates (e.g., UTC vs. EST) dropping boundary records.
- Inner joins dropping rows that lack corresponding key matches in reference tables.
Investigation and Diagnosis
Compare record counts at each stage of the pipeline:
# 1. Source Extract Count
wc -l raw_extract.csv
# 2. Post-Transform Count
mlr --csv filter '$status != "EXCLUDED"' raw_extract.csv | wc -l
# 3. Destination Database Load Count
sqlite3 analytics.db "SELECT COUNT(*) FROM events;"
Resolution
Implement primary key constraints and use idempotent load logic (INSERT OR REPLACE or MERGE) to safely handle retries. Change strict inner joins to left outer joins when reference keys may be missing.
Verification
Re-run execution and verify destination row counts equal transform output counts.
Problem 8: Silent Failure via Schema Drift
Symptoms
Upstream API or log updates add, rename, or drop fields. Downstream jobs continue running without throwing errors, but critical data columns end up populated with null values.
Possible Causes
Upstream service teams modifying response structures without versioning endpoints or notifying data consumers.
Investigation and Diagnosis
Compare current payload keys against historical reference schemas:
diff <(jq -r 'keys[]' old_sample.json | sort) <(jq -r 'keys[]' new_sample.json | sort)
Resolution
Enforce strict schema validation rules at pipeline ingestion boundaries. Fail pipelines explicitly if required keys are missing or if total document counts drop significantly.
# Assert required field existence in jq pipeline
jq 'if has("user_id") then . else error("Missing required field: user_id") end' input.json
Problem 9: High Processing Latency in Batch Jobs
Symptoms
Nightly data processing jobs miss SLA windows, running for hours instead of minutes.
Possible Causes
- Full table or unpartitioned disk scans searching through historical directories.
- Processing uncompressed text files across network mounts.
Investigation and Diagnosis
Inspect disk read I/O efficiency and directory scanning flags:
iostat -xz 1 10
Resolution
Implement partition pruning so jobs read only relevant date partitions rather than full dataset histories. Convert raw CSV log archives into compressed Parquet files to reduce disk I/O bottlenecks.
# Querying specific partition paths in DuckDB to minimize data scan overhead
duckdb -c "
SELECT COUNT(*) FROM 's3://lake/events/year=2026/month=11/*.parquet';
"
11. Security and Best Practices
- Sensitive Data Redaction: Sanitize logs and data files to strip out Personally Identifiable Information (PII), secret tokens, and passwords prior to storing or transforming them.
# Redact authorization bearer tokens from incoming raw JSON logs
jq '.headers.authorization = "[REDACTED]"' access_log.json > sanitized.json
- Restrict Access Permissions: Set strict file permission masks (
chmod 600) on intermediate scratch files stored in/tmpor/var/tmpto prevent unauthorized local users from reading sensitive data. - Validate Untrusted Input: Never pipe unvalidated input directly into shell execution strings or unescaped SQL commands, preventing shell and SQL injection vulnerabilities.
- Encryption at Rest and in Transit: Ensure data files copied across network boundaries utilize TLS (
curl -s https://...), and encrypt long-term data lake storage volumes.
12. Performance and Resource Optimisation
- Stream Large Inputs: Avoid loading multi-gigabyte datasets entirely into RAM. Use streaming modes (
jq -c,mlr --csv stream) or single-node columnar query engines like DuckDB. - Adopt Columnar Storage: Store long-term analytics data in Parquet format rather than raw JSON or CSV. Parquet drastically reduces file size through columnar compression and accelerates read queries by scanning only required columns.
- Parallel Processing via Chunking: Divide massive text files using
splitand process chunks across multiple CPU cores usingxargs -Por GNUparallel.
# Parallel processing across 4 CPU cores
ls chunk_*.csv | xargs -P 4 -I {} mlr --csv filter '$status == "ERROR"' {} > output_{}
13. Backup, Recovery and Maintenance
- Pipeline Idempotency: Design pipeline scripts so re-running a failed job across the same input partition safely overwrites incomplete outputs without duplicating records.
- Schema & Script Versioning: Store transformation scripts (
.jqfiles, Miller scripts, SQL queries) and schema files (.proto,.avsc) in Git repositories. - Scratch Space Cleanup: Implement regular maintenance routines to delete temporary file chunks (
chunk_*) and clear transient staging databases from/var/tmp/after jobs finish.
# Automated cleanup script for scratch directories
find /var/tmp/pipeline_scratch -type f -mtime +2 -delete
14. Automation and Integration
Integrate text manipulation tools into standard Linux automation mechanisms such as systemd timers and crontab entries.
Automated Daily Log Ingestion Script
#!/usr/bin/env bash
set -euo pipefail
# Configuration Parameters
LOG_DIR="/var/log/app"
TARGET_DIR="/data/lake/events"
DATE=$(date -d "yesterday" +%Y-%m-%m)
# Step 1: Validate input log format
if ! jq empty "${LOG_DIR}/events_${DATE}.json" 2>/dev/null; then
echo "CRITICAL: Malformed JSON log on ${DATE}" | mail -s "ETL Alert" sre@example.com
exit 1
fi
# Step 2: Extract, Clean, and Convert JSON logs into Parquet format via DuckDB
mkdir -p "${TARGET_DIR}/year=${DATE:0:4}/month=${DATE:5:2}"
duckdb -c "
COPY (
SELECT
event_id,
user_id,
event_type,
epoch_ms(CAST(timestamp AS BIGINT)) AS event_time
FROM read_json_auto('${LOG_DIR}/events_${DATE}.json')
WHERE event_type IS NOT NULL
) TO '${TARGET_DIR}/year=${DATE:0:4}/month=${DATE:5:2}/events_${DATE}.parquet'
(FORMAT PARQUET, COMPRESSION ZSTD);
"
echo "Successfully processed and loaded events for ${DATE}"
15. Practical Labs and Exercises
Lab 1 (Beginner): Parse and Extract Web Server Logs with jq
Objective
Parse a JSON application log file, filter out non-error records, and export a formatted TSV text report containing timestamps and URL paths.
Environment Setup
Create a sample log file named access.json:
{"timestamp": "2026-11-03T10:00:00Z", "status": 200, "request": {"method": "GET", "path": "/health"}, "bytes": 120}
{"timestamp": "2026-11-03T10:01:05Z", "status": 500, "request": {"method": "POST", "path": "/api/v1/checkout"}, "bytes": 450}
{"timestamp": "2026-11-03T10:02:10Z", "status": 404, "request": {"method": "GET", "path": "/favicon.ico"}, "bytes": 0}
{"timestamp": "2026-11-03T10:03:15Z", "status": 503, "request": {"method": "POST", "path": "/api/v1/checkout"}, "bytes": 220}
Instructions
- Validate syntax using
jq empty. - Write a
jqfilter to select records wherestatus >= 500. - Extract
timestamp,request.method,request.path, andstatusas tab-separated values.
Expected Command
jq -r 'select(.status >= 500) | [.timestamp, .request.method, .request.path, .status] | @tsv' access.json
Validation Output
2026-11-03T10:01:05Z POST /api/v1/checkout 500
2026-11-03T10:03:15Z POST /api/v1/checkout 503
Lab 2 (Intermediate): Reshape and Join CSV Datasets via Miller (mlr)
Objective
Join a raw CSV transaction export with a user reference keymap file using Miller (mlr), and compute total transaction values per user.
Environment Setup
Create transactions.csv:
tx_id,user_id,amount
101,u1,45.50
102,u2,120.00
103,u1,15.00
Create users.csv:
uid,username
u1,alice
u2,bob
Instructions
- Execute a join operation matching
user_idintransactions.csvtouidinusers.csv. - Calculate total spending per username.
Expected Command
mlr --csv join -f transactions.csv -l user_id -r uid then stats1 -a sum -f amount -g username users.csv
Validation Output
username,amount_sum
alice,60.50
bob,120.00
Lab 3 (Advanced Troubleshooting): Resolve Pipeline Out-Of-Memory and Encoding Errors
Objective
Diagnose and repair a failing data pipeline job encountering character encoding exceptions and RAM exhaustion on a multi-gigabyte CSV input.
Failure Scenario Setup
A nightly legacy log dump (legacy_dump.csv) encoded in ISO-8859-1 causes downstream UTF-8 parsers to fail. Furthermore, running jq -s on the converted JSON output crashes the shell due to memory exhaustion.
Instructions & Resolution Steps
- Inspect file charset encoding:
file -bi legacy_dump.csv
- Convert file to UTF-8 using
iconv:
iconv -f ISO-8859-1 -t UTF-8 legacy_dump.csv -o utf8_dump.csv
- Avoid buffering full files in memory. Convert CSV stream directly into Parquet using DuckDB stream processing:
duckdb -c "
COPY (
SELECT * FROM read_csv_auto('utf8_dump.csv')
) TO 'cleaned_dataset.parquet' (FORMAT PARQUET);
"
- Confirm target Parquet file exists and query row counts efficiently:
duckdb -c "SELECT COUNT(*) FROM 'cleaned_dataset.parquet';"
16. Quick Reference and Cheat Sheet
Configuration Files & Tool Paths
| Utility / Path | Responsibility | Notes |
|---|---|---|
/usr/bin/jq |
JSON processing & syntax validation | Always check syntax with jq empty |
/usr/bin/mlr |
Stream transformer for CSV, TSV, & JSON | Format-aware CLI stream processor |
/usr/local/bin/duckdb |
Direct SQL query engine for files | High performance on CSV/Parquet |
/usr/bin/iconv |
Character set converter | Fixes Latin-1 vs UTF-8 encoding errors |
/etc/environment |
Locale configuration | Ensure LANG=UTF-8 is exported |
Important Log & Inspection Locations
| Target | Diagnostic Command | Operational Use Case |
|---|---|---|
| JSON Files | jq empty file.json |
Validates syntax integrity before ingest |
| CSV Headers | `head -n 1 file.csv | tr ‘,’ ‘\n’` |
| Parquet Schema | duckdb -c "DESCRIBE SELECT * FROM 'f.parquet'" |
Displays metadata, fields, and types |
| File Encoding | file -bi file.csv |
Identifies MIME type and character encoding |
Common CLI Command Reference
| Task | Command Example | Context |
|---|---|---|
| Pretty-print JSON | jq . input.json |
Formats JSON for human inspection |
| Raw Field Extraction | jq -r '.items[].name' input.json |
Extracts unquoted values |
| Filter CSV by Value | mlr --csv filter '$status == "active"' data.csv |
Filters CSV records by criteria |
| SQL Query on CSV | duckdb -c "SELECT * FROM 'd.csv' WHERE total > 50" |
Queries CSV files directly using SQL |
| Convert Encoding | iconv -f ISO-8859-1 -t UTF-8 in.csv -o out.csv |
Converts character set to UTF-8 |
Troubleshooting Quick Reference
| Symptom | Initial Diagnostic Command | Primary Cause |
|---|---|---|
jq: parse error |
head -c 200 payload.json |
HTML error page returned or trailing commas in JSON |
jq returns null |
jq 'keys' payload.json |
Incorrect path or key casing mismatch |
| Ragged CSV columns | awk -F, 'NF != N {print NR, $0}' data.csv |
Unquoted commas or embedded newlines |
Garbled text (é) |
file -bi data.csv |
Non-UTF-8 encoding (e.g., ISO-8859-1) |
| Process OOM Killed | ls -lh input.json |
Buffering full file in RAM instead of streaming |
| Duplicate DB rows | wc -l extract.csv vs DB query |
Non-idempotent job re-execution |
17. Further Reading and References
- jq Manual: jqlang.org/manual
- Miller (mlr) Documentation: miller.readthedocs.io
- DuckDB Documentation: duckdb.org/docs
- Apache Parquet Documentation: parquet.apache.org
- Apache Airflow Documentation: airflow.apache.org/docs
- Apache Spark Documentation: spark.apache.org/docs
- SadServers Interactive Scenarios: Hands-on Linux data processing scenarios tagged with
json,csv, andsql.