engineer

Transform tooling and table formats

• A transform framework (dbt, SQLMesh) earns its keep through model contracts on marts, unit tests on tricky logic, and source freshness checks — not just DAG ordering. dbt Core v2 / the Fusion engine is still alpha; confirm its status in devdocs before adopting it.
• Open table formats (Iceberg, Delta): idempotency comes from MERGE by key or partition overwrite; schedule compaction and snapshot expiry, or small files and stale snapshots degrade every reader. Schema evolution stays additive.
• DuckDB is a good default for local and medium-sized transforms, tests, and reading Parquet/Iceberg directly — a warehouse is not needed for every pipeline.

Postgres performance

• Diagnose with EXPLAIN (ANALYZE, BUFFERS) before touching anything (PostgreSQL 18 includes buffer counts with ANALYZE by default); the slow part is where the rows × loops are, not where intuition points.
• Indexes: b-tree default; composite ordered by equality-then-range (PostgreSQL 18 skip scan can use a composite index without the leading column, but a well-ordered index still wins); partial for skewed predicates (status = 'active'); covering (INCLUDE) for hot index-only scans. Every index taxes writes — indexes nobody scans get dropped.
• Batch big writes (thousands per transaction, not millions in one, not one per row); VACUUM/autovacuum health checked when bloat or wraparound symptoms appear.
• Partition by time when tables hit hundreds of millions of rows or retention-dropping is needed — partition pruning + DROP PARTITION beats DELETE by orders of magnitude.
• Connection pooling assumed; long-running transactions block vacuum and replication — flag them.
• Major-version upgrades: PostgreSQL 18 pg_upgrade carries planner statistics over; on older versions run ANALYZE right after upgrading or the planner flies blind.

Data quality tests (the non-negotiable four, then domain checks)

Not-null and unique on every declared key · referential integrity at layer boundaries · freshness (data newer than X per source) · volume anomaly (today within tolerance band of trailing average). Then domain asserts: totals reconcile to source (count AND sum on money columns), enum values in the allowed set, no future timestamps. A backfill or migration ends with a reconciliation query, not with "it ran".

Schema changes

Additive first (new column nullable → backfill → tighten). Renames/drops are two-phase with a deprecation window — something always still reads the old name. Contract changes announced to consumers before, not after. Migrations are forward-only scripts in version control with a tested rollback path.