Domain 8Data InfrastructureINTERMEDIATE

Data Processing for DevOps and SRE: Shell Utilities, ETL Pipelines, and Troubleshooting Guide

A comprehensive Linux SysAdmin and SRE guide to data processing, covering text and binary data formats (JSON, CSV, Parquet, Avro, Protobuf), CLI streaming utilities (jq, Miller, DuckDB, csvkit), ETL/ELT architecture, and production troubleshooting.

Domain: Domain 8: Data Infrastructure•Updated: 2026-11-03
#linux#sysadmin#data-processing#devops#jq#csv#parquet

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, Python pyarrow) 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 .proto interface 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:

  1. 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).
  2. Transform: Clean invalid records, deduplicate key space, cast data types, flatten nested JSON objects, join reference tables, and apply business logic or redactions.
  3. 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 REPLACE or MERGE statements).
  • 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, or tenant_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., C or POSIX), tools processing multibyte JSON characters may corrupt output or fail with encoding exceptions.
  • TMPDIR: Overrides the default location for temporary scratch files generated by sort, jq, or duckdb. 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

  1. Verify JSON Syntax: Use jq empty to quickly validate the file’s syntax.
jq empty /var/log/app/stream.json
  1. Isolate Syntax Failures: If jq reports errors, filter line-by-line using Python’s json.tool to 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
  1. 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

  1. Inspect CSV Headers: Check the CSV file’s header structure.
head -n 1 /var/log/nginx/access_combined.csv
  1. 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;
"
  1. 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

  1. Execute jq empty to check for syntax validity.
  2. Print the first 200 bytes of the payload to check for HTML error pages or garbage headers:
head -c 200 payload.json
  1. If inspecting an HTTP response, check the status code and Content-Type header before piping to jq:
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 jq filter expression.
  • Key casing mismatch (e.g., .name vs .Name).
  • Unhandled array nesting (e.g., querying .items.name instead of .items[].name).
  • API version updates changing response schemas dynamically.

Investigation and Diagnosis

  1. Inspect available keys on a sample record:
jq 'keys' payload.json
  1. Check the data type of the root node:
jq 'type' payload.json
  1. 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

  1. Inspect raw CSV headers and early rows:
head -n 5 input.csv
  1. Determine the expected column count from header line 1:
head -n 1 input.csv | tr ',' '\n' | wc -l
  1. 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
  1. Run csvstat or mlr --csv check for 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

  1. Inspect file charset using file:
file -bi export.csv
# Output: text/plain; charset=iso-8859-1
  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 null values.
  • 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 /tmp or /var/tmp to 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 split and process chunks across multiple CPU cores using xargs -P or GNU parallel.
# 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 (.jq files, 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

  1. Validate syntax using jq empty.
  2. Write a jq filter to select records where status >= 500.
  3. Extract timestamp, request.method, request.path, and status as 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

  1. Execute a join operation matching user_id in transactions.csv to uid in users.csv.
  2. 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

  1. Inspect file charset encoding:
file -bi legacy_dump.csv
  1. Convert file to UTF-8 using iconv:
iconv -f ISO-8859-1 -t UTF-8 legacy_dump.csv -o utf8_dump.csv
  1. 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);
"
  1. 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

  1. jq Manual: jqlang.org/manual
  2. Miller (mlr) Documentation: miller.readthedocs.io
  3. DuckDB Documentation: duckdb.org/docs
  4. Apache Parquet Documentation: parquet.apache.org
  5. Apache Airflow Documentation: airflow.apache.org/docs
  6. Apache Spark Documentation: spark.apache.org/docs
  7. SadServers Interactive Scenarios: Hands-on Linux data processing scenarios tagged with json, csv, and sql.