AGENT SKILL

duckdb-architect

Architect-level design with DuckDB and embedded analytics — when a single-node engine beats a warehouse, file formats and partitioning for scan efficiency, extension use (Parquet, Iceberg, Postgres), concurrency limits, and the pipelines that refresh local analytical stores. Use when analytics should run close to the data instead of in a cluster.

License 许可
MIT
最近更新
2026-11-13
Stars
★ 0
Forks
0

01 前言 Front Matter

name
duckdb-architect
license
MIT
metadata
{ "crusor-path": "data-engineering-architect/storage-architect/duckdb-architect", "crusor-domain": "data-engineering-architect", "crusor-origin": "original", "crusor-sources": "duckdb.org/docs; duckdb.org/docs/data/parquet; duckdb.org/docs/guides/performance", "crusor-triggers": "duckdb, embedded analytics, single node analytics, parquet query, arrow, local warehouse, motherduck, olap in process", "crusor-verified-on": "2026-10-30" }
description
Architect-level design with DuckDB and embedded analytics — when a single-node engine beats a warehouse, file formats and partitioning for scan efficiency, extension use (Parquet, Iceberg, Postgres), concurrency limits, and the pipelines that refresh local analytical stores. Use when analytics should run close to the data instead of in a cluster.
compatibility
DuckDB, Parquet, Arrow, SQLite, MotherDuck

03 SKILL.md 正文

DuckDB Architect

DuckDB is a columnar engine inside your process. For up to a few hundred GB it replaces a warehouse, a cluster, and a lot of cost — if the file layout is sane.

When to use

  • Analytical workloads under ~100–500 GB.
  • Per-tenant or per-customer analytics shipped with a product.
  • Local development and CI pipelines that mirror warehouse SQL.
  • Ad-hoc analysis over Parquet in object storage.

When NOT to use

  • Many concurrent writers or high-concurrency serving.
  • Multi-terabyte joins needing distributed compute.
  • Transactional OLTP requirements.

Decision 1 — Where it runs

Shape Use
in-process (library) CLI tools, services, tests
embedded in an app per-customer analytics
server (MotherDuck/managed) shared access, modest concurrency
CI/dev mirror fast tests without a warehouse
  • One process, one writer: DuckDB is not a multi-writer database. Serialise writes through a queue or a single service.
  • Concurrency for reads is limited by the process; scale by shipping the file to the client or by multiple processes on read-only copies.

Decision 2 — Data layout

  • Query Parquet directly for read-heavy analysis; ingest to DuckDB tables when repeated access justifies it.
  • Partition files by the filter column (year=, month=) so the scan prunes.
  • Avoid millions of tiny files; DuckDB's file-open cost dominates.
  • Column pruning is automatic — select only the columns you need anyway, for I/O.

Decision 3 — Extensions

  • Parquet/JSON/CSV readers, Iceberg/Delta readers for lakehouse access, Postgres scanner for federation, httpfs for object storage.
  • Federation is powerful for small lookups; do not build a distributed join across a remote Postgres.
  • Pin extension versions in production images; behaviour changes between releases.

Decision 4 — Performance

  • Set PRAGMA threads to available cores; memory limit explicitly to avoid OOM on large joins.
  • Sort/order data by common filter columns for zone-map skipping.
  • Materialise expensive intermediate results as tables, not CTEs re-evaluated repeatedly in older versions.
  • Measure with EXPLAIN ANALYZE; the plan shows scan and join costs directly.

Decision 5 — Pipelines

  • Build .duckdb files as artefacts: reproducible, versioned, refreshable.
  • Refresh from source with idempotent loads; treat the file as derived state.
  • Ship read-only files to clients where the data is per-tenant and small.
  • For BI serving, put a thin API over queries rather than exposing SQL.

Failure modes at scale

  • One DuckDB file written by four services; lock errors and corruption risk.
  • 40 GB of 2 MB Parquet files; scan overhead exceeds the actual work.
  • DuckDB used as an OLTP store for a web app.
  • Extension version drift between dev and production image; query behaviour differs.
  • Memory limit unset; a big join OOMs the host process.

Verification receipts

  1. Workload sizing: data volume, query shapes, concurrency requirement.
  2. Query timings on the chosen file layout versus a naive one.
  3. Concurrency test showing the writer-serialisation plan.
  4. Memory and thread limits set in the deployment configuration.
  5. Refresh pipeline demonstrated to produce an identical file from source.

04 同仓库更多 Skills

SriAkhilSJ
SriAkhilSJ/PulseAIRepo

interaction-patterns-architect

Architect-level interaction design in code — choosing established patterns over invented ones, feedback and latency behaviour, destructive-action safeguards, navigation and overlay conventions, and the state model behind every interactive surface. Use when a screen has to behave, not just look.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

design-system-ops-architect

Architect-level operation of a design system in code — token pipelines and versioning, component API and variant policy, the catalogue as the contract, adoption metrics, and the governance that stops a system from forking into three. Use when a component library must survive several teams.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

design-handoff-architect

Architect-level design handoff — what must be specified before implementation starts, tokens over raw values, responsive and content-limit contracts, state matrices, and the review loop that catches drift between design and code. Use when a design is about to become code.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

content-microcopy-architect

Architect-level UI content — where strings live, voice and terminology consistency, error and empty-state copy as part of the interface, translation-ready authoring, and the content model a product needs before it multiplies screens. Use when the words in the interface matter as much as the pixels.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

ui-ux-implementation-architect

Architect-level direction for turning a design into working UI — the handoff contract, component ownership versus screen-level layout, interaction and state conventions, design QA gates, and the review habits that keep an implementation faithful without frozen pixels. Use before any specific UI implementation skill.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

test-strategy-architect

Architect-level test strategy — allocating assertions across unit, integration, contract and end-to-end layers, designing for testability through dependency boundaries, fixture and data strategy, and the flake and coverage policies that keep a suite trustworthy. Use when defining how a codebase is tested.

★ 0