A data engineer explains why DuckDB has become his go-to tool for tabular data processing, used almost exclusively from Python. He argues we're entering a simpler era where most data can be handled on a single machine, ending the need for complex clusters for all but the largest datasets. DuckDB's appeal lies in its simplicity, speed, and comprehensive feature set.

Unlike transactional databases like SQLite or Postgres, DuckDB is an in-process SQL engine optimized for analytics queries involving joins and aggregations. Performance differences are stark—queries can run 100 to 1,000 times faster. Its core use case involves batch-processing large files like CSV, Parquet, or JSON directly from disk, offering a lightweight alternative to heavyweight tools like Spark.

Key advantages include near-zero startup time, making it ideal for CI testing and rapid development. The SQL dialect includes modern features like `EXCLUDE`, `COLUMNS`, and `QUALIFY`, improving ergonomics. It handles diverse file types natively, even querying remote files via HTTP. Full ACID compliance for bulk operations sets it apart from other analytical systems, potentially rivaling lakehouse formats for medium-scale data.