Project 3: Messy CSV Triage Clinic

Build a Livebook intake clinic that turns a deliberately dirty billing CSV into accepted data, traceable rejections, reconciled counts, and share-safe evidence.

Quick Reference

Attribute Value
Difficulty Level 2 — Intermediate
Time Estimate 10–16 hours
Language Elixir (alternative: Python only for an interoperability comparison)
Prerequisites Projects 1–2 or equivalent; pattern matching; tagged results; lists/maps; basic file and CSV concepts
Key Topics Boundary contracts, CSV parsing, error accumulation, Decimal money, date semantics, reconciliation, redaction, provenance

1. Learning Objectives

By completing this project, you will:

  1. Distinguish file-level, header-level, row-structure, field, and domain-policy failures.
  2. Decide deliberately which defects invalidate an entire import and which can be quarantined per row.
  3. Transform raw CSV text into trusted billing records without silent coercion or row loss.
  4. Accumulate multiple independent reasons for one rejected row while counting unique rejected rows correctly.
  5. Use decimal arithmetic and one declared rounding policy for financial values.
  6. Define unambiguous date, timezone, interval, duplicate, empty-field, and normalization policies.
  7. Reconcile input, accepted, and rejected row counts and explain why reason frequency may exceed rejected rows.
  8. Redact sensitive data before rendering or exporting, then prove it with seeded canary scans.
  9. Produce sanitized accepted data, rejection evidence, and a provenance manifest that replay from a clean runtime.

2. Theoretical Foundation

2.1 Core Concepts

Validation layers. A bulk intake pipeline has distinct failure scopes. A file-level failure such as unsupported encoding, oversized input, or truncated bytes may make all rows untrustworthy. A header-level failure means columns cannot be mapped to the expected contract. A row-structure failure concerns field count or quoting. Field validation parses and checks individual values. Domain validation evaluates relationships such as end_date >= start_date. Keeping these layers separate prevents a local defect from masquerading as a global one, or a global defect from producing misleading partial totals.

Boundary versus domain representation. CSV has strings, delimiters, rows, and optional headers; it does not contain domain types. The text "2026-07-01" is not yet a billing interval, and "19.90" is not yet money. A trusted subscription record is created only after normalization, parsing, validation, and policy checks. Raw source identity must remain attached so every accepted or rejected result can be traced back without retaining unnecessary sensitive content.

Fail-fast versus error accumulation. Fail fast when continuing cannot be interpreted safely: invalid encoding, unparseable CSV structure, absent required headers, or a contract version mismatch can invalidate the file. Accumulate independent row errors when the rest of the file remains meaningful. A row with a missing customer ID, invalid amount, and inverted dates should report all three, reducing repair cycles.

Unique rows versus reason frequencies. If 77 rows are rejected, reason counts may sum to more than 77 because one row can have multiple reasons. The report must distinguish count(unique rejected row identities) from count(all reason occurrences). A reconciliation invariant uses unique rows: rows read = accepted rows + rejected rows. Error-frequency charts use reason occurrences.

Decimal money and rounding. Binary floating-point cannot exactly represent many decimal fractions. Repeated arithmetic can expose small differences that become cents after rounding. Parse money into a decimal representation, preserve declared scale, and round once according to a documented currency rule. Keep tax, discount, and total formulas explicit rather than converting back to floats for convenience.

Date and interval semantics. A date without time may be adequate for billing periods; a timestamp requires an offset or named timezone policy. You must define whether intervals are closed, open, or half-open, which timezone determines a day boundary, and whether an end date equal to a start date is valid. These choices affect duplicates, overlap, duration, and reconciliation.

Duplicate identity. Duplicate detection is a domain policy, not merely “identical lines.” A stable invoice ID may define uniqueness. If it is absent, a compound normalized key may be needed, but normalizing case or whitespace can merge legitimately different identities. Detect duplicates after the fields required for the duplicate key are valid, and document whether all copies or only later copies are quarantined.

Redaction by construction. Sensitive values should not enter a shareable evidence representation. Masking a displayed table while exporting raw domain records creates two security paths and almost guarantees a leak. Derive a sanitized evidence record with only allowed fields, stable surrogate identity, safe fragments, and reason codes. Use that same representation for UI, logs, and exports.

Provenance and replay. Record file bytes or checksum, detected/declared encoding, delimiter, header mapping, contract revision, package versions, policy revision, counts, and artifact checksums. A replay against the same committed snapshot should produce matching accepted/rejected identities and output checksums, excluding deliberately nondeterministic metadata.

2.2 Why This Matters

Real data is messy at boundaries: exports change column names, spreadsheet tools alter encodings, amounts use regional separators, dates are ambiguous, and duplicate deliveries occur. A pipeline that stops at the first bad row delays useful work. A pipeline that quietly “fixes” everything produces unreliable business decisions. Quarantine with explicit evidence provides a third path: useful valid records continue, while invalid records remain traceable and repairable.

The project practices core Elixir strengths. Pattern matching makes boundary shapes explicit. Tagged data keeps expected defects out of exception flow. Immutable transformations preserve the raw, normalized, validated, and sanitized stages. Livebook turns quality counts, sample errors, policies, and artifacts into a reviewable intake runbook.

2.3 Historical Context / Background

CSV predates modern schema-rich data formats and remains ubiquitous because it is easy to produce and inspect. Its apparent simplicity hides dialect differences in delimiter, quote, newline, encoding, null representation, and header conventions. RFC 4180 describes a common format but does not make every CSV producer compliant or every domain field unambiguous.

Data engineering evolved from monolithic extract-transform-load jobs toward explicit contracts, quarantine paths, lineage, and observable quality metrics. Columnar formats such as Parquet improve typed analytical exchange after validation, but they do not repair an ambiguous source automatically. This project treats CSV as an untrusted transport and produces typed, sanitized artifacts only after the contract is satisfied.

2.4 Common Misconceptions

  • “CSV is just split each line on commas.” Quoted delimiters, embedded newlines, escaped quotes, alternate delimiters, byte-order marks, and encodings make manual splitting unsafe.
  • “If parsing succeeded, the data is valid.” Syntax success says nothing about domain meaning, date order, money policy, or duplicate identity.
  • “Failing fast is always safer.” It is appropriate for file-wide ambiguity, but per-row fail-fast hides repairable evidence and causes repeated intake cycles.
  • “Reason totals must equal rejected-row totals.” Multiple reasons per row make reason frequency greater than or equal to unique rejected rows.
  • “Masking the UI protects exports.” Only a single sanitized evidence path protects every renderer and artifact consistently.
  • “A checksum proves the data is correct.” It proves byte identity, not semantic correctness; the contract and verification results supply meaning.

3. Project Specification

3.1 What You Will Build

Create csv-triage-clinic.livemd and committed training fixtures. The notebook accepts a file through a Kino file input, resolves it through Livebook-managed file semantics, and processes it through explicit validation layers.

The finished clinic contains:

  • A source card with logical file name, bytes, checksum, encoding/dialect decision, detected headers, and contract revision.
  • File and header diagnostics that stop unsafe partial processing.
  • A row pipeline that preserves stable source identity and accumulates field/domain reasons.
  • An accepted-record table using trusted types.
  • A rejected-evidence table containing multiple reason codes and redacted safe context.
  • Quality metrics for unique rows and reason occurrences.
  • A reconciliation card and invariant results.
  • Sanitized accepted output, rejected evidence, and a manifest.
  • A PII canary scan over every rendered/exported artifact.

3.2 Functional Requirements

  1. Bound file intake: Accept only documented extensions, byte limits, and one declared encoding/dialect policy.
  2. Identify source: Record logical name, bytes, SHA-256 checksum, processing time window, and contract revision.
  3. Validate file structure: Detect unsupported encoding, malformed quoting, inconsistent record structure, and missing required headers as file/header failures.
  4. Map headers explicitly: Normalize allowed aliases but reject ambiguous duplicates or unknown required mappings.
  5. Preserve row identity: Attach source record number and a stable non-sensitive evidence ID.
  6. Parse typed fields: Convert amount with Decimal, parse dates with declared format, and normalize identifiers according to written policy.
  7. Accumulate row reasons: Report every independent actionable defect without converting invalid rows into domain records.
  8. Detect duplicates: Apply one explicit duplicate key and handling policy after key fields are valid.
  9. Reconcile counts: Prove rows read = accepted unique rows + rejected unique rows.
  10. Separate metrics: Display rejected-row count and reason-frequency count independently.
  11. Redact by construction: Prevent full email, payment reference, or seeded canary values from entering UI/export evidence.
  12. Export artifacts: Produce sanitized accepted data, rejected evidence, and a manifest with stable checksums.
  13. Replay: Reprocess the same snapshot in a fresh runtime and match declared stable outputs.

3.3 Non-Functional Requirements

  • Safety: Raw sensitive values are not rendered, logged, or exported outside the explicitly protected raw input.
  • Correctness: No source row disappears or contributes to both accepted and rejected sets.
  • Explainability: Every rejection has stable row identity, field, reason code, and safe corrective message.
  • Performance: The 1,250-row training fixture processes in under two seconds after setup; memory remains bounded by file-size policy.
  • Reliability: File-wide failure produces a clear blocked state and no partial “success” totals.
  • Portability: File references are resolved through Livebook/Kino; no author-specific absolute path is embedded.
  • Reviewability: Policies, schemas, fixtures, and expected counts are committed and readable.

3.4 Example Usage / Output

After selecting the valid-but-dirty training file, the clinic displays:

Subscription Billing Intake
Rows read: 1,250
Accepted: 1,173
Rejected: 77
Reconciliation: 1,250 = 1,173 + 77  [PASS]

Top rejection reasons
invalid_decimal_amount ........ 31
missing_customer_id ........... 22
end_before_start .............. 18
duplicate_invoice_id .......... 14

Export: sanitized-accepted.parquet
Evidence: triage-manifest.json
PII scan: PASS

An unsupported-encoding fixture stops before row totals:

INTAKE BLOCKED — FILE CONTRACT FAILED
file=subscription-billing-legacy.csv
failure=unsupported_or_invalid_utf8
bytes_read=4096_before_detection_failure
row_processing=NOT_STARTED
partial_totals=NOT_AVAILABLE
recommended_action=export_source_as_UTF-8_and_retry

3.5 Real World Outcome

Open the notebook and read the contract before uploading anything. It names accepted encoding, delimiter, headers, money scale, date format, interval convention, duplicate policy, and forbidden evidence fields. Select the 1,250-row training CSV using the file input. The first output is a source card, not a result table, so you can verify that the intended snapshot is being processed.

When processing finishes, a reconciliation card dominates the top of the result area. It shows source, accepted, and unique rejected counts with PASS/FAIL. Tabs or sequential sections then show: a bounded accepted sample with typed columns; a rejected sample with safe row identity and multiple reasons; a frequency table; and policy/invariant evidence. The rejected table masks email and payment references before they reach the renderer.

Choose a multi-defect fixture and observe one row list all seeded reasons. Choose the malformed-encoding fixture and observe a file-level blocked panel with no partial totals. Export the accepted and evidence artifacts, then search them for seeded canary strings. The PII scan reports PASS only when every artifact is clean. Reconnect the runtime, select the same committed fixture, and reproduce the counts and stable checksums.

4. Solution Architecture

4.1 High-Level Design

┌───────────────────────┐
│ Kino file input       │
│ logical file reference│
└───────────┬───────────┘
            v
┌───────────────────────┐      BLOCK ┌──────────────────────────┐
│ File gate             │───────────▶│ File-level error panel   │
│ bytes • encoding • CSV│            │ no partial row success   │
└───────────┬───────────┘            └──────────────────────────┘
            │ valid stream
            v
┌───────────────────────┐      BLOCK ┌──────────────────────────┐
│ Header contract       │───────────▶│ Header error panel       │
│ required • aliases    │            │ ambiguous/missing fields │
└───────────┬───────────┘            └──────────────────────────┘
            │ mapped records + source identity
            v
┌────────────────────────────────────────────────────────────────────┐
│ Row pipeline                                                       │
│ structure -> normalize -> parse -> field rules -> domain rules     │
│                      -> duplicate policy                            │
└──────────────────────┬──────────────────────────┬──────────────────┘
                       │ trusted record           │ rejected row + reasons
                       v                          v
             ┌────────────────────┐      ┌────────────────────────┐
             │ Accepted records   │      │ Sanitized evidence     │
             │ typed domain values│      │ safe fields/reason codes│
             └─────────┬──────────┘      └───────────┬────────────┘
                       └──────────────┬───────────────┘
                                      v
                        ┌───────────────────────────┐
                        │ Reconciler + quality stats│
                        │ unique rows vs occurrences│
                        └─────────────┬─────────────┘
                                      v
             ┌────────────────────────┼──────────────────────────┐
             v                        v                          v
   accepted artifact       rejected evidence artifact      manifest
             └────────────────────────┼──────────────────────────┘
                                      v
                              PII canary scan

4.2 Key Components

Component Responsibility Key Decisions
File intake Resolve selected file and enforce size/type policy Treat resolved path read-only; no absolute paths in source
Dialect/encoding gate Establish whether rows can be interpreted safely File-wide ambiguity blocks all row processing
Header mapper Match required canonical fields and allowed aliases Reject duplicate/ambiguous mappings
Row decoder Preserve record identity and structural evidence Never use business identifier as the only source identity
Field parsers Parse Decimal, date, and normalized identifiers Tagged results; retain safe rejected context
Domain validator Check dates, amounts, required relationships Accumulate independent reasons
Duplicate detector Apply declared key and duplicate disposition Run only when key fields are valid
Sanitizer Derive share-safe evidence One path for UI, logs, and exports
Reconciler Count unique input/accepted/rejected identities Reason occurrences are a separate measure
Artifact writer Produce typed accepted data, evidence, and manifest Stable schemas and checksums

4.3 Data Structures

SourceDescriptor = {
  logical_name,
  byte_count,
  sha256,
  encoding,
  delimiter,
  header_mapping,
  contract_revision
}

RawRow = {
  source_record_number,
  raw_fields_by_canonical_header
}

BillingRecord = {
  source_record_number,
  invoice_id,
  customer_surrogate,
  amount_decimal,
  currency,
  start_date,
  end_date,
  normalized_status
}

RejectionReason = {
  field_or_scope,
  code,
  safe_message,
  rejected_value_class
}

SanitizedRejectionEvidence = {
  evidence_id,
  source_record_number,
  safe_invoice_fragment,
  masked_customer_hint,
  reasons: non_empty_list_of_RejectionReason
}

TriageResult = {
  source,
  rows_read,
  accepted_records,
  rejected_evidence,
  reason_frequencies,
  reconciliation_checks,
  output_artifacts
}

4.4 Algorithm Overview

Key Algorithm: Layered Triage with Reconciliation

  1. Resolve the Livebook file reference and enforce byte/type bounds before full allocation.
  2. Hash the original bytes and establish the encoding and CSV dialect.
  3. Parse and map the header against one versioned contract.
  4. Enumerate data records with stable source-record numbers.
  5. For each row, validate structure, normalize fields, parse types, and accumulate field/domain reasons.
  6. Build duplicate keys for rows whose key components are valid; apply the documented duplicate policy.
  7. Partition rows into accepted domain records and rejected evidence exactly once.
  8. Sanitize rejection evidence before any rendering or export.
  9. Calculate unique row counts and separate reason-occurrence frequencies.
  10. Verify reconciliation and domain invariants.
  11. Write artifacts from accepted records and sanitized evidence; scan all outputs for canaries.
  12. Write a manifest containing source, policy, counts, versions, and artifact checksums.

Complexity Analysis:

  • Parsing and validation: O(n × f) for n rows and f fields.
  • Duplicate detection: expected O(n) with a map/set; O(n log n) if sorted evidence is preferred.
  • Space: O(n + e) for records and e reason occurrences in this bounded training design.
  • Canary scan: O(b) over total artifact bytes b.

5. Implementation Guide

5.1 Development Environment Setup

Recommended current baseline: Livebook 0.19.8, Kino 0.19.0, Explorer 0.12.0, and KinoExplorer 0.1.25. Use a compatible Decimal release selected by your dependency solver and record it in the manifest.

$ elixir --version
$ livebook --version
$ git --version
$ livebook server

Use only synthetic training data. Seed obvious canaries such as unique email/payment strings so scans can prove whether unsafe values escaped. Do not substitute real customer exports.

5.2 Project Structure

csv-triage-clinic/
├── csv-triage-clinic.livemd
├── fixtures/
│   ├── billing-dirty-1250.csv
│   ├── billing-multi-error-10.csv
│   ├── billing-invalid-encoding.bin
│   └── expected-quality-manifest.fixture
├── artifacts/
│   └── .gitkeep
├── evidence/
│   ├── canary-scan.txt
│   └── clean-replay.txt
└── README.md

Generated artifacts should normally be ignored unless a small sanitized reference artifact is deliberately committed for teaching.

5.3 The Core Question You’re Answering

“How can a bulk workflow remain useful in the presence of bad data without silently accepting it or failing at the first defect?”

Your answer must name the failure scope, disposition, evidence, and reconciliation rule for every class of defect. “Skip bad rows” is not an adequate policy because it loses both meaning and accountability.

5.4 Concepts You Must Understand First

  1. CSV dialect and encoding
    • Why are delimiter, quoting, newline, and character encoding separate concerns?
    • Which ambiguity must block the whole file?
    • Primary Reference: RFC 4180.
  2. Boundary and domain models
    • At what step does a row become a trusted subscription-billing record?
    • Why should raw strings never be accepted by the cost or export logic?
    • Book Reference: Domain Modeling Made Functional, Chapters 4–6.
  3. Decimal and date semantics
    • Where is scale declared and rounding allowed?
    • Is the billing interval closed, open, or half-open?
  4. Accumulated errors
    • Which validations are independent and useful together?
    • Why is malformed file structure different from an invalid field?
    • Book Reference: Elixir in Action, 3rd ed., Chapter 4.
  5. Redaction and provenance
    • Which values are prohibited from every shareable representation?
    • What checksum, policy revision, and artifact metadata make replay meaningful?

5.5 Questions to Guide Your Design

  1. Which file defects invalidate all records, and why?
  2. What exact header aliases are accepted, and how is ambiguity rejected?
  3. Which normalization happens before duplicate detection?
  4. Are empty string, missing column, and explicit null semantically different?
  5. Which timezone and interval convention govern start/end validation?
  6. What happens to all copies of a duplicated invoice ID?
  7. How is one row with three defects counted in row and reason metrics?
  8. Which fields are safe in rejection evidence, and how is that safety tested?
  9. What does a second identical run need to match: counts, identities, bytes, checksums, or all of these?
  10. Can a file-level failure accidentally leave a previous successful result looking current?

5.6 Thinking Exercise

Before implementation, create a ten-row prediction matrix:

row  structure  customer  amount  dates  duplicate  expected_disposition  expected_reasons
1    valid      valid     valid   valid  no         accepted              none
2    valid      missing   invalid valid  no         rejected              two
3    valid      valid     valid   inverted no       rejected              one
4    valid      valid     valid   valid  yes        policy-dependent      duplicate
... six more deliberate combinations ...

Predict rows read, accepted, unique rejected, and every reason frequency. Check that reason-frequency sum may exceed unique rejected count. Then design a separate malformed-encoding fixture and explain why it has no trustworthy row count.

5.7 The Interview Questions They’ll Ask

  1. “When should validation fail fast versus accumulate errors?”
  2. “Why should financial data avoid binary floating-point?”
  3. “How do you distinguish malformed encoding from a domain error?”
  4. “What invariants detect silent row loss?”
  5. “How do you make a rejection report safe to share?”
  6. “How would you define and enforce duplicate identity?”
  7. “Why can error-frequency totals exceed rejected-row totals?”

5.8 Hints in Layers

Hint 1: Validate in layers. File → header → row structure → normalized fields → domain policy → duplicate policy.

Hint 2: Preserve original row identity. A record number or safe stable ID keeps evidence traceable even when business identifiers are invalid.

Hint 3: Keep raw and trusted shapes different. If the same map type flows through every stage, invalid strings can reach downstream logic accidentally.

Hint 4: Reconcile unique identities. Accepted plus rejected uses rows; reason charts use occurrences.

Hint 5: Redact before rendering. Build one sanitized evidence representation and feed it to every output path.

Hint 6: Seed canaries deliberately. A security property without a known detection target is difficult to prove.

5.9 Books That Will Help

Topic Book Chapter
Domain validation Domain Modeling Made Functional, Scott Wlaschin Chapters 4–7
Data encoding and evolution Designing Data-Intensive Applications, 2nd ed. Chapter 4
Elixir failure modeling Elixir in Action, 3rd ed. Chapter 4
Data quality and provenance Fundamentals of Data Engineering Data generation, ingestion, and data-quality chapters
Security and least data Foundations of Information Security Privacy and access-control chapters

5.10 Implementation Phases

Phase 1: Foundation (3–4 hours)

Goals: Define the contract, fixtures, validation scopes, and expected evidence.

Tasks:

  1. Write the encoding, dialect, header, money, date, duplicate, and redaction policies.
  2. Create the 10-row prediction fixture and calculate expected metrics by hand.
  3. Create the 1,250-row synthetic fixture and seed canary sensitive values.
  4. Define raw, domain, rejection, sanitized evidence, and manifest schemas.

Checkpoint: Another reader can classify every seeded defect and predict its scope/disposition before execution.

Phase 2: Core Functionality (4–7 hours)

Goals: Build the layered pipeline, reconciliation, and safe views.

Tasks:

  1. Implement file/dialect/header gates with blocked states.
  2. Implement row parsing and accumulated field/domain validation.
  3. Add duplicate handling and exact accepted/rejected partitioning.
  4. Build sanitized evidence, quality summaries, and invariant checks.

Checkpoint: The ten-row fixture matches every predicted identity/reason; the reference fixture matches 1,173 accepted and 77 rejected.

Phase 3: Polish & Edge Cases (3–5 hours)

Goals: Produce artifacts, prove redaction, and verify replay.

Tasks:

  1. Export sanitized accepted and rejection evidence plus manifest.
  2. Scan UI-compatible data and every artifact for seeded canaries.
  3. Test malformed encoding, quoting, missing header, zero rows, duplicates, and decimal/date edges.
  4. Reconnect and compare stable checksums and counts.

Checkpoint: All artifacts pass canary scans and the clean-runtime replay matches the reference manifest.

5.11 Key Implementation Decisions

Decision Options Recommendation Rationale
Parser Manual split, CSV parser library Proven CSV parser Correctly handles quoting, delimiters, and embedded newlines
File ambiguity Best-effort rows, block file Block file Prevents misleading partial totals
Row errors First error, accumulated errors Accumulate independent reasons Gives actionable repair evidence
Money Float, integer minor units, Decimal Decimal with declared scale Matches input semantics and explicit rounding policy
Duplicate disposition Keep first, reject later, quarantine all Choose and document; default quarantine later copies Preserves one canonical record while exposing duplicates
Evidence path Mask per renderer, shared sanitized model Shared sanitized model Prevents UI/export divergence and leaks
Output format CSV only, Parquet plus JSON/text manifest Typed accepted artifact plus readable manifest Preserves schema while keeping provenance inspectable

6. Testing Strategy

6.1 Test Categories

Category Purpose Examples
File-contract tests Verify global safety gates invalid UTF-8, oversized file, malformed quoting
Header tests Verify schema mapping missing required header, duplicate alias, reordered columns
Field parser tests Verify typed boundaries decimal separators, blank ID, invalid date
Domain tests Verify relationships inverted dates, unsupported currency, duplicate invoice
Reconciliation tests Detect row loss/double count accepted + rejected = input unique identities
Redaction tests Prevent sensitive output seeded email/payment canaries absent everywhere
Replay tests Prove provenance same snapshot, same policies, stable output checksums

6.2 Critical Test Cases

  1. Reference dirty file: Reads 1,250 rows, accepts 1,173, rejects 77, and passes reconciliation.
  2. Multi-error row: One record produces three reason occurrences but one rejected-row identity.
  3. Malformed encoding: Blocks the file before row processing and never displays partial success totals.
  4. Decimal edge: Values awkward in binary floating-point retain expected decimal scale and rounding.
  5. Date edge: Equal boundaries, leap day, and inverted range follow the written interval policy.
  6. Duplicate key: Reordered rows produce the documented deterministic duplicate disposition.
  7. Canary leak: Full email/payment canaries are absent from tables, logs, accepted export, rejection evidence, and manifest.
  8. Clean replay: Stable input and policy revisions reproduce counts, identities, and checksums.

6.3 Test Data

FILE reference_dirty
rows=1250 accepted=1173 rejected_unique=77 reconciliation=PASS
reason invalid_decimal_amount=31
reason missing_customer_id=22
reason end_before_start=18
reason duplicate_invoice_id=14
note reason_sum_may_exceed_77

ROW multi_error
source_record=7 customer_id="" amount="19,9x" start=2026-07-20 end=2026-07-01
expected disposition=rejected
expected reasons=[missing_customer_id, invalid_decimal_amount, end_before_start]

FILE invalid_encoding
bytes=malformed_training_sequence
expected scope=file
expected row_processing=NOT_STARTED

CANARIES
email=DO_NOT_EXPORT_EMAIL_CANARY
payment_reference=DO_NOT_EXPORT_PAYMENT_CANARY
expected appearances_in_shareable_artifacts=0

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Pitfall Symptom Solution
Naive comma split Columns shift on quoted delimiter/newline Use a CSV parser with explicit dialect
Mixed failure scopes Malformed file still shows partial totals Block before row processing and clear prior-current status
Float money A valid-looking amount changes by a cent Parse Decimal and round once by policy
Reason/row confusion Category totals appear to “break” reconciliation Report unique rows separately from reason occurrences
Duplicate after bad normalization Different customers collapse to one key Validate and document normalization before key construction
UI-only masking Export leaks PII canary Export only from the sanitized evidence model
Lost source identity Rejection cannot be repaired Preserve source record number and safe evidence ID

7.2 Debugging Strategies

  • Return to the 10-row matrix: Small predicted data exposes count and reason mistakes.
  • Render stage counts: Record how many identities enter and leave each layer without rendering raw sensitive values.
  • Inspect raw bytes safely: For encoding failures, inspect bounded hexadecimal metadata rather than decoding or printing customer text.
  • Diff identity sets: Compare input, accepted, and rejected stable identities to find loss or overlap.
  • Search for canaries: Scan every serialized artifact and prepared UI dataset, not only visible HTML.
  • Freeze policy revision: If replay differs, compare contract, parser/package, and policy versions before inspecting totals.

7.3 Performance Traps

Reading an unbounded upload into memory defeats the clinic’s safety model. Enforce byte limits before parsing. Avoid rendering all 1,250 rows; show bounded samples and aggregate counts. Repeatedly converting between row maps and DataFrames can duplicate memory. For larger extensions, stream parsing and chunked quarantine writing are preferable, but reconciliation must still operate on stable identities.

8. Extensions & Challenges

8.1 Beginner Extensions

  • Add a correction-hints column mapped from stable reason codes.
  • Add a policy comparison showing how “reject later duplicate” differs from “quarantine all duplicates.”
  • Add an empty-file fixture with an intentional, non-success state.

8.2 Intermediate Extensions

  • Write accepted data as Parquet with a declared schema and read it back for equivalence checks.
  • Add property-based generators for valid/invalid decimal and date fields.
  • Compare Explorer and a row-oriented parser while keeping one domain contract.

8.3 Advanced Extensions

  • Process a much larger synthetic file in bounded chunks while preserving exact reconciliation.
  • Add contract-version migration with separate evidence for transformed legacy rows.
  • Build a durable quarantine workflow with repair status, immutable raw hash, and reprocessing lineage.

9. Real-World Connections

9.1 Industry Applications

  • Billing and payment imports: Protect money semantics, duplicates, and sensitive evidence.
  • CRM migrations: Quarantine malformed customer records while continuing valid data.
  • Healthcare and regulated exchange: Separate source retention from minimum-necessary shareable evidence.
  • Data contracts: Make producer/consumer assumptions versioned, observable, and testable.
  • Explorer: Typed DataFrames and columnar data processing in Elixir.
  • NimbleCSV: Efficient configurable CSV parsing.
  • Decimal: Arbitrary-precision decimal arithmetic for Elixir.
  • Kino: File inputs, rendering, and Livebook interaction.

9.3 Interview Relevance

  • Data contracts: Explain syntax, field, domain, and file-wide failure scopes.
  • Correctness: Demonstrate reconciliation and multi-error accounting.
  • Security: Describe redaction by construction and seeded canary tests.
  • Data modeling: Explain why raw strings and trusted domain values need distinct representations.

10. Resources

10.1 Essential Reading

  • Domain Modeling Made Functional by Scott Wlaschin — Chapters 4–7 for type-driven validation workflows.
  • Designing Data-Intensive Applications, 2nd ed., by Martin Kleppmann — Chapter 4 for encoding and schema evolution.
  • Elixir in Action, 3rd ed., by Saša Jurić — Chapter 4 for failures as explicit outcomes.
  • Fundamentals of Data Engineering by Joe Reis and Matt Housley — ingestion and data-quality lifecycle.

10.2 Video Resources

10.3 Tools & Documentation

11. Self-Assessment Checklist

Before considering this project complete, verify:

11.1 Understanding

  • I can classify a defect as file, header, row-structure, field, domain, or duplicate-policy scope.
  • I can explain when fail-fast and error accumulation are each appropriate.
  • I can explain why unique rejected rows and reason occurrences are different metrics.
  • I can defend the Decimal, date, timezone, interval, and duplicate policies.
  • I can explain why redaction must precede rendering and export.

11.2 Implementation

  • File, header, row, field, domain, and duplicate layers are distinct.
  • Multiple row errors are accumulated with stable source identity.
  • Source equals accepted plus unique rejected for every row-processable fixture.
  • File-wide failures expose no partial success totals.
  • UI and artifacts contain none of the seeded sensitive canaries.
  • Manifest records source checksum, schema/policy revision, versions, counts, and artifact checksums.
  • Clean-runtime replay reproduces stable outputs.

11.3 Growth

  • I documented one ambiguous source convention and the explicit policy chosen for it.
  • I can describe how the design would scale beyond in-memory training data.
  • I can explain the reconciliation and redaction evidence in a technical interview.

12. Submission / Completion Criteria

Minimum Viable Completion:

  • A Livebook file input processes the committed 10-row and 1,250-row training fixtures.
  • File-wide ambiguity blocks processing; row-local defects are quarantined with accumulated reasons.
  • Accepted and unique rejected identities reconcile exactly with source rows.
  • Rejected evidence masks sensitive fields before rendering.

Full Completion:

  • All minimum criteria plus:
  • Decimal, date, timezone, interval, duplicate, empty-field, and header policies are documented and tested.
  • The reference fixture produces 1,173 accepted and 77 rejected rows with declared reason frequencies.
  • Sanitized accepted data, rejection evidence, and manifest are exported with stable schemas/checksums.
  • Seeded canary scans and clean-runtime replay pass.

Excellence (Going Above & Beyond):

  • Property-based tests generate boundary rows and verify partition/reconciliation properties.
  • A chunked large-file extension demonstrates bounded memory without weakening evidence.
  • A contract-version migration preserves original hash and transformation lineage.
  • A second reviewer can repair the 10-row fixture using only safe rejection evidence.

This guide was generated from LEARN_LIVEBOOK_ELIXIR_DEEP_DIVE.md. For the complete learning path, see the parent directory.