Skip to content
Search

Flat-file database

lastmod 2025-03-14created 2025-03-142 edits

A flat file database (Flat file database) is a general term for a way of encoding a single data model (usually a single table) as a plain text file, and for the kind of database that uses this approach.

In other words, a flat file database is a database that stores data in the form of pure text files.

About

Hard to define

  • Flat Files are mainly used when extracting data from a database for processing
  • Simple text files like CSV, where each row represents a record and fields are separated by commas
  • However, the definition of a "Flat File" is not clear-cut, and they share the following common characteristics:
    • Text-only: stores only text and numbers, not binary data (Blob)
    • Tabular format: one table per file, organized as row-based records
    • Portable: easy to print, edit, and process without special software
    • Unstructured: usually no hierarchy or relationships between records
    • No compression: no smart compression by default (though DuckDB, Spark, etc. accept zip files)
    • No index: no built-in index for finding specific records
    • No description: usually no metadata or schema information
  • JSON and YAML fall outside this definition because they can include hierarchy and schemas
  • The fact that they can serve a very wide range of use cases does not really help either
    • CSV: data storage and exchange format
    • JSON: large-scale data exchange, configuration management, API return format
    • YAML: configuration management and pipeline definition files

Types of Flat Files

  • Various types exist depending on how data is organized and stored
  • How fields and records are organized
    • Unstructured Flat File: single-table structure, like CSV
    • Structured Flat File: JSON, YAML, XML, etc. have a hierarchy
  • Field delimiter types
    • Each row represents one record, with fields separated by a delimiter
    • Comma (,), tab (\t), pipe (|) are used
  • Fixed-width vs. non-fixed-width formats
    • In fixed-width formats, fields have a constant length → faster processing
    • Non-fixed-width formats offer more processing flexibility
  • Data delimiters
    • CSV can store tabs and newlines (with escaping)
    • TSV cannot store tabs or newlines
  • Human readability
    • Flat Files are often human-readable
    • Some platforms also classify Excel files as Flat Files
  • Whether metadata is included
    • Files like CSV have no metadata
    • JSON can include its own metadata and schema

Use cases for Flat Files

  • Storage and exchange
    • Data exchange: CSV and JSON are useful for exchanging data between different platforms
    • Data integration in ETL: Flat Files are often used as source and target data in ETL
    • Archiving and backup: being text-based, Flat Files are well suited to long-term storage
  • Utility uses
    • Configuration management: YAML, JSON, INI, etc. are used for environment variables, database connections, and so on
    • Data pipeline definitions: JSON, YAML, etc. are used to define pipeline structure
    • Dataset metadata: JSON can define transformations and validation for CSV

See also

Favorite site

author Zer0 sitemap