Store it.
Query it.
Ship it.

ACID is the foundation of relational correctness. Atomicity: every statement in a transaction commits or none does — a partially written model evaluation result never appears. Consistency: constraints and triggers are always enforced, so a chunk referencing a deleted document will fail the FK check, not silently insert. Isolation: concurrent transactions cannot see each other's uncommitted writes by default (READ COMMITTED, the Postgres default). Durability: a committed row survives a crash because the WAL is flushed to disk before the commit returns. PostgreSQL offers four isolation levels. READ COMMITTED is the default and right for most AI logging pipelines — it prevents dirty reads and gives the best concurrency. REPEATABLE READ prevents non-repeatable reads within a transaction, useful when a batch scoring job must read a consistent snapshot across many queries. SERIALIZABLE additionally detects write conflicts and rolls back one of the conflicting transactions — use it for experiment slot reservation or credit deduction where a phantom row would break correctness. For most model inference logging, READ COMMITTED is the correct choice. Always commit or rollback explicitly; an idle-in-transaction connection blocks autovacuum on every table it has touched.

Index selection is the highest-leverage query optimisation in PostgreSQL. B-tree is the default and covers equality, range, ORDER BY, BETWEEN, LIKE 'prefix%', and NULL checks. Add a B-tree index on every foreign key column — PostgreSQL does not create FK indexes automatically, and a missing FK index turns JOINs into sequential scans. GIN (Generalised Inverted Index) is designed for values containing multiple searchable components: JSONB keys and arrays, full-text tsvectors, and pg_trgm trigrams. A GIN index on a jsonb metadata column turns operators like @> (containment) and ? (key existence) from full sequential scans into millisecond lookups. BRIN (Block Range Index) stores only the min/max value per range of disk pages — the index itself can be kilobytes on a billion-row table. It works well only when column values are physically correlated with storage order, such as an auto-increment ID or a timestamp column on an append-only log table. Partial indexes carry a WHERE clause, reducing index size and write overhead. A partial index on deleted_at IS NULL means only active chunks are indexed. Covering indexes with INCLUDE add non-key columns so the engine can satisfy a query entirely from the index without touching the heap (Index Only Scan).

JSONB is the correct column type for dynamic, semi-structured data: LLM response metadata, model configuration snapshots, per-tenant feature flags, or A/B test assignments. Unlike the text json type, JSONB parses on write and stores a binary format that supports indexing and efficient key access. Key operators: ->> extracts a text value by key, #>> extracts at a JSON path, @> tests containment (the row's column contains this sub-document), ? checks key existence. A GIN index on the JSONB column enables @> and ? to run in milliseconds. Connection pooling is non-negotiable for AI services. An LLM inference handler that spawns 200 concurrent threads will attempt 200 Postgres connections simultaneously. The default max_connections is 100–200. PgBouncer in transaction mode is the standard solution: the backend connection is released back to the pool immediately after each transaction commit, allowing 1000 application connections to share 20 backend connections. Session mode is needed only for session-scoped features like SET LOCAL, advisory locks, LISTEN/NOTIFY, or server-side prepared statements. For AWS Lambda and serverless functions, use RDS Proxy as a managed PgBouncer equivalent.

EXPLAIN (ANALYZE, BUFFERS) is the definitive source of truth for query performance. It runs the query and shows the actual execution plan: estimated versus actual row counts, loop count, per-node cost, and — with BUFFERS — the number of shared_buffers cache hits and disk reads. Key node types: Seq Scan reads the entire table (acceptable only on small tables or when most rows are returned). Index Scan uses an index but fetches each heap row individually. Index Only Scan reads entirely from the index — no heap fetch (fastest). Bitmap Heap Scan batches index lookups then fetches heap pages in bulk (good for range queries returning 1–5% of the table). Nested Loop is efficient for small outer sets with indexed inner joins. Hash Join builds an in-memory hash table on the smaller relation — good for medium tables but needs work_mem. Merge Join is good for large pre-sorted inputs. The most actionable signal in EXPLAIN output is the gap between rows= (estimate) and actual rows=. A large discrepancy means the planner chose a join strategy optimised for the wrong input size. Fix it with ANALYZE table_name to refresh column statistics. For persistently bad estimates, increase default_statistics_target for the column.

The N+1 problem is when code queries N items and then issues one additional query per item, producing N+1 total database calls instead of 1 or 2. In AI systems it appears most often as: loading all chunks for a list of documents (one SELECT per document_id in a Python loop), or fetching conversation history for each user in a batch scoring pipeline. N+1 is rarely noticeable in development (10 docs = 11 queries = undetectable). In production (10 000 docs = 10 001 queries = 5 minutes) it causes timeout cascades. pg_stat_statements is a PostgreSQL extension that normalises query text and accumulates call count, total execution time, mean time, and rows across the process lifetime. Enable it in shared_preload_libraries. Sort by total_exec_time to find the most expensive query patterns in aggregate — a query taking 0.5ms called 2 million times (1000 seconds total) is far more important to optimise than a 10-second query called once. Enable log_min_duration_statement = 1000 to also catch individual slow queries in the Postgres log. Fix N+1 by batching IDs with WHERE id = ANY(%s) or by rewriting with a single JOIN.

INSERT ... ON CONFLICT DO UPDATE (UPSERT) is the idempotent write primitive for tables that can receive the same record multiple times: embedding caches, deduplication tables, idempotency key stores, and feature store snapshots. The ON CONFLICT clause must specify either a unique constraint name or a set of columns matching a unique index. EXCLUDED refers to the row that would have been inserted — use it to reference new values in the SET clause. RETURNING retrieves column values from the affected rows in the same statement, eliminating a second SELECT query and closing the race window between write and read. Always use RETURNING for INSERT to get a server-generated primary key or default timestamp. VACUUM reclaims disk space from dead tuples created by UPDATE and DELETE under MVCC. Autovacuum runs automatically but uses conservative defaults: it waits until 20% of rows are dead before triggering. On write-heavy tables (inference logs, embedding caches), this means bloat accumulates for days before cleanup. Tune autovacuum_vacuum_scale_factor to 0.01 for these tables. Monitor n_dead_tup in pg_stat_user_tables and alert when it exceeds 5% of n_live_tup.

Approximate Nearest Neighbour (ANN) indexes return the k most similar vectors without scanning every row — the approximation is controlled and measurable via recall@k. IVFFlat partitions the vector space into nlist Voronoi cells using k-means clustering at index build time. A query searches the nprobe nearest cells, skipping the rest. Build time is fast (minutes on 1 M vectors), but recall degrades sharply if nprobe is too low for the nlist chosen. IVFFlat requires training data — build it only after the majority of vectors are loaded. HNSW (Hierarchical Navigable Small World) constructs a multi-layer proximity graph. Each node connects to its M nearest neighbours. Queries traverse graph edges from an entry point, navigating toward the query vector. HNSW achieves higher recall at lower latency than IVFFlat on most workloads and supports incremental inserts without retraining. Trade-offs: HNSW uses more memory (roughly M × 8–16 bytes per vector) and has slower build time. For production RAG: start with HNSW M=16, ef_construction=64. Set ef_search=100 for standard recall, increase to 200 for high-recall requirements. Benchmark recall@10 against exact brute-force search on a representative 10K-query sample before locking in parameters.

pgvector adds a native vector(N) column type, HNSW and IVFFlat index types, and three distance operators to PostgreSQL. Install with CREATE EXTENSION vector. The column type is vector(N) where N is the embedding dimension: 1536 for text-embedding-3-small, 3072 for text-embedding-3-large, 768 for most open-source models (BGE, E5). pgvector supports up to 2000 dimensions for indexed search; for higher dimensions, use half-precision (halfvec) or binary (bit) columns. Three distance operators: <-> Euclidean (L2) distance, <=> cosine distance (1 − cos θ), <#> negative inner product (−a·b). For unit-normalised embeddings, cosine distance and negative inner product produce identical rankings. Always use the same distance metric at index creation and query time — mixing them bypasses the index entirely and falls back to an exact scan of all rows. Partial HNSW indexes with WHERE deleted_at IS NULL keep the index small and fast as documents are soft-deleted over time. Use vector_cosine_ops, vector_l2_ops, or vector_ip_ops as the operator class to declare which distance metric the index supports.

Qdrant is a vector database written in Rust, optimised for high-throughput production search. Core concepts: Collection (equivalent to a table), Point (a vector with a numeric/UUID ID and an optional JSON payload), Payload (arbitrary metadata for filtering). Qdrant's key advantage over pgvector is pre-filtering: payload filters are applied before ANN graph traversal rather than after. This means filtering on tenant_id, category, or date range does not degrade recall — you always get k results from the filtered set. Create a payload index on every field you filter by. Without a payload index, Qdrant falls back to linear scan of payloads before ANN — effectively an exact search with metadata overhead. Quantization: scalar INT8 reduces memory 4× with <2% recall loss. Binary (1-bit) reduces 32× but requires rescoring with original float vectors to maintain quality. Use scalar quantization as the default when memory is constrained. Comparison: pgvector for SQL-native apps needing JOINs and transactions. Qdrant for high-QPS vector-first workloads with rich metadata filtering. Weaviate for built-in vectorizer modules and GraphQL API. Pinecone for zero-ops serverless with less control over indexing.

A production chunk schema must support four operations that many teams only consider after launch: delta re-embedding (only re-embed chunks whose content changed), model migration (switch from one embedding model to another without downtime), chunking strategy updates (change overlap or size without full re-parse), and audit trails (which version of what content produced each embedding). Mandatory columns: chunk_id (UUID PK), document_id (FK with ON DELETE CASCADE), content (the text shown to users), content_hash (SHA-256 of content — computed at write), embedding (vector(N)), embedding_model (e.g. 'text-embedding-3-small'), embedding_dim, chunk_index, chunk_total, chunking_ver (version of chunking logic), tenant_id, created_at, updated_at, deleted_at. For model migration: the new and old embeddings exist in different vector spaces — cosine similarity between an ada-002 embedding and a text-embedding-3-small embedding is meaningless. Strategy: (1) add a partial HNSW index WHERE embedding_model = 'new-model', (2) background job re-embeds all chunks updating embedding and embedding_model, (3) A/B test retrieval quality on both indexes, (4) swap routing to the new index, (5) drop the old partial index.

Multi-tenant access control must be enforced at the database layer, not the application layer. PostgreSQL Row-Level Security (RLS) guarantees this: after enabling RLS, every SELECT, UPDATE, and DELETE automatically appends the policy predicate as a WHERE condition, regardless of how the SQL was written. Even a raw psql connection or an ORM that constructs arbitrary queries cannot return another tenant's rows — the database enforces it. Enable RLS with ALTER TABLE chunks ENABLE ROW LEVEL SECURITY. Write a policy: CREATE POLICY tenant_isolation ON chunks USING (tenant_id = current_setting('app.current_tenant_id')::UUID). The application sets the session parameter via SET app.current_tenant_id = '...' on connection setup. Critical: RLS is NOT enforced for the table owner (superuser). Create a dedicated application role with DML privileges only — never connect as postgres from application code. For metadata filtering performance: partial indexes per tenant give the best throughput for single-tenant queries (each search only traverses a small index). In Qdrant, create a payload index on tenant_id and include a mandatory filter on every search call. For post-filtering (ANN then filter), retrieve a candidate pool at least 5–10× larger than k to ensure enough results survive the filter.

Dense embedding search captures semantic similarity but fails on exact terms: a query for "invoice INV-2026-1049" should return the exact document, not semantically similar invoices. Sparse BM25 search captures exact matches but misses paraphrases and synonyms. Hybrid retrieval combines both and consistently outperforms either alone on diverse production query distributions. The standard fusion method is Reciprocal Rank Fusion (RRF). For each candidate document: rrf_score = Σ 1/(k + rank_i) across all result lists where k=60 is a smoothing constant. RRF is robust because it works on ranks, not raw scores — BM25 and cosine similarity use incompatible scales that RRF sidesteps entirely. The key implementation detail: accumulate scores by chunk_id in a dictionary; a chunk appearing in both result lists receives contributions from both ranks and naturally ranks higher than single-list results. After RRF fusion, a cross-encoder reranker (Cohere Rerank, BGE Reranker) can further improve top-k precision. Rerankers score query-chunk pairs jointly (not separately), capturing interaction patterns. Always retrieve a larger candidate pool for reranking (k=40 for reranker input, return top-5 to the LLM).

Redis provides eight distinct data structures, each optimised for a specific access pattern. Strings (SET/GET/INCR) handle simple caches, counters, and distributed locks (SETNX). Hashes (HSET/HGET/HMGET) store field-value collections — ideal for conversation memory, user session state, and model configuration snapshots where individual field updates are common. Lists (LPUSH/RPOP/LRANGE) are ordered sequences useful for conversation history queues and simple task queues (though Streams are better for durability). Sets (SADD/SISMEMBER/SMEMBERS) handle unique membership: tracking which documents have been embedded, deduplicating user IDs. Sorted Sets (ZADD/ZRANGEBYSCORE/ZRANK) store members with a float score — the right structure for rate limiting (sliding window with timestamps as scores), recommendation ranking (item scores), and leaderboards. HyperLogLog (PFADD/PFCOUNT) estimates cardinality of large sets in 12 KB regardless of set size — use it for unique daily active users or distinct query counting without storing every ID. Streams (XADD/XREAD/XGROUP) are append-only logs with consumer groups — the correct structure for async LLM inference pipelines where message durability and at-least-once processing matter.

Cache-aside (lazy loading) is the most common pattern for AI systems: the application checks the cache on read, on miss fetches from the database and populates the cache, on write updates the database and invalidates or updates the cache entry. Simple to implement and resilient (cache miss = slower, not broken), but prone to cache stampede: when a popular key expires, many concurrent requests simultaneously find a miss and all hit the database at once. Write-through: every write goes to both the cache and the database synchronously. The cache is always consistent with the database. Simpler to reason about but doubles write latency and wastes cache space for write-heavy keys that are never read. Write-behind (write-back): writes go to the cache immediately, and the cache asynchronously flushes to the database. Lowest write latency but risks data loss if Redis crashes before the flush. Only use write-behind for non-critical data (analytics counters, view counts) where loss is acceptable. Cache stampede prevention: (1) Lock pattern — SET key:lock NX PX 5000 to acquire a distributed lock; the winning request populates the cache, others wait. (2) Probabilistic Early Recomputation (PER) — proactively regenerate a value slightly before its TTL expires, proportional to time remaining and re-computation cost. (3) TTL jitter — add random 0–10% variance to TTL so keys that were populated together don't all expire at the same moment.

TTL (time-to-live) controls when a key is automatically deleted. SET key value EX seconds sets TTL on write. EXPIRE key seconds adds or updates TTL on an existing key. TTL key returns remaining seconds (-1 = no TTL, -2 = key does not exist). EXPIREAT sets an absolute Unix timestamp expiry. For AI caches: embedding caches (hours to days), model prediction caches (minutes to hours depending on staleness tolerance), rate limit windows (seconds to minutes). Eviction policies control what Redis deletes when it reaches maxmemory. noeviction (default) rejects new writes with an error when full — never correct for a cache. allkeys-lru evicts the least-recently-used key across all keys — the standard choice for caches where all keys are equally likely to be accessed. allkeys-lfu evicts the least-frequently-used key — better than LRU for workloads with a stable hot set (popular embedding models, frequent system prompts) because frequently accessed keys survive longer. volatile-lru and volatile-lfu apply only to keys with a TTL set; keys without TTLs are never evicted. volatile-* is appropriate when Redis holds both a cache (with TTLs) and durable data (without TTLs) in the same instance — though separating them into different Redis instances is cleaner. Set maxmemory as a fraction of available RAM (leave 20–30% headroom for overhead). Use MEMORY USAGE key to check per-key memory. Monitor used_memory_rss and mem_fragmentation_ratio in INFO memory.

Redis Streams are an append-only log with consumer groups built into Redis. Sub-millisecond publish latency. Consumer groups enable multiple workers to share a stream without duplicate delivery. Persistent with AOF/RDB. Use for: async LLM inference queues at moderate volume (<500K msg/day), background embedding jobs, real-time event forwarding between microservices. Limitation: single-node throughput ceiling; no multi-partition horizontal scaling. Apache Kafka is a distributed commit log. Millions of messages per second per partition, configurable retention (hours to weeks), replay from any offset, rich consumer group semantics, Kafka Connect for CDC and database integration. Kafka Streams and ksqlDB for stream processing. Operational complexity is high (ZooKeeper or KRaft, broker management, partition tuning). Use for: ML training data event pipelines, CDC from Postgres to the feature store, high-volume inference logging that must be replayed for model debugging. AWS SQS is a managed message queue with zero operational overhead. Standard queue: at-least-once, best-effort ordering. FIFO queue: exactly-once within a deduplication window, 3000 TPS. Built-in dead-letter queue. 256 KB message limit (store large payloads in S3, pass S3 key in SQS). Use for: asynchronous model invocations in AWS-native architectures, decoupling services without operating a broker.

Delivery guarantees describe what happens when a consumer crashes between receiving a message and acknowledging it. At-most-once: the message is acknowledged before processing. If the consumer crashes after ACK but before completing the work, the message is lost. Suitable for metrics and analytics where occasional loss is acceptable. At-least-once: the message is acknowledged after processing. If the consumer crashes after processing but before ACK, the message is redelivered. The consumer may process it twice — consumer must be idempotent. At-exactly-once: idempotent producers + transactional consumers ensure each message is processed exactly once. Complex and expensive. In practice: make consumers idempotent (same message processed twice = same outcome) and use at-least-once delivery — this is effectively exactly-once at lower cost. Kafka commit strategies: auto-commit (enable.auto.commit=true) ACKs messages on a timer regardless of processing status — risks message loss if the consumer crashes after auto-commit but before completing work. Manual synchronous commit: commitSync() after each batch — safe but slower. Manual async commit: commitAsync() for throughput — no guarantee of ordering on retry. The correct production pattern: process a batch, commitSync after all messages in the batch are successfully written to the destination. Idempotency key: a unique identifier per message that the consumer stores in a processed-IDs set before committing. On redelivery, check the set and skip if already processed.

A dead-letter queue (DLQ) receives messages that have failed processing N times. Without a DLQ, a poison message (malformed payload, unexpected data that always raises an exception) blocks the queue permanently as the consumer retries it in an infinite loop. In AWS SQS, set maxReceiveCount on the source queue's Redrive Policy to route to a DLQ after N failures. In Kafka, use a topics-based DLQ pattern: after N retries, produce the message to a dead-letter topic with error metadata. In Redis Streams, move the message to a separate dead-letter stream after exceeding a delivery count threshold. Retry strategy: exponential backoff with jitter prevents retry storms. Base delay × 2^attempt + random jitter. Cap at a maximum delay (e.g., 5 minutes). Jitter distributes retries across time so all workers don't retry the same message simultaneously. Backpressure: the producer is signalled to slow down when the consumer cannot keep up. Kafka: consumer lag (measured by the difference between the latest offset and the committed offset) drives auto-scaling — high lag triggers new consumer pod creation. SQS: no native backpressure; implement at the API layer by checking ApproximateNumberOfMessages before accepting new requests. Redis Streams: XLEN on the stream gives queue depth; a producer can check this and block or rate-limit if depth exceeds a threshold.

ETL (Extract-Transform-Load) transforms data before loading it into the destination. The transform step runs in custom code — Python, Spark, or Glue jobs. Advantages: sensitive data can be masked or filtered before landing in the warehouse. Disadvantages: business logic is scattered across scripts, hard to audit, and expensive to reprocess when logic changes. ETL is appropriate when you cannot store raw data due to PII or cost. ELT (Extract-Load-Transform) loads raw data first, then transforms it inside the analytical warehouse or lakehouse using SQL. The modern default: cloud storage is cheap, BigQuery and Snowflake can process petabytes with SQL, and transformation logic in version-controlled SQL is auditable, testable, and reproducible. dbt (data build tool) is the ELT transformation layer: SQL SELECT statements become tables or views, with built-in tests, documentation, and column-level lineage. Materializations: view (re-runs query on every read, always fresh), table (snapshot, fast reads, stale until refreshed), incremental (appends or merges new rows only — critical for AI/ML feature pipelines that grow daily), and ephemeral (subquery, no storage). For ML feature engineering: use incremental materializations with a unique_key for idempotent upserts and an incremental predicate on updated_at to only process new rows.

Apache Airflow orchestrates pipeline workflows as Directed Acyclic Graphs (DAGs). Each node is a Task with a specific operator: PythonOperator (run a Python function), BashOperator (run shell commands), SQLExecuteQueryOperator (run SQL), KubernetesPodOperator (run a container), S3Sensor (wait for a file to appear). Dependencies are set with >> or set_downstream. Idempotency is the critical design constraint: a task must produce the same output when run multiple times on the same input. The most common mistake is using NOW() or datetime.utcnow() inside a task — backfills fail because they compute values for today instead of the historical execution_date. Always use context['logical_date'] (formerly execution_date) for time-bounded queries and file paths. Example: a daily feature extraction task should query WHERE event_date = '{{ ds }}' (the DAG's logical date), not WHERE event_date = CURRENT_DATE. XCom (cross-communication) passes small values between tasks via the metadata database. For large data (>100 KB) use S3/GCS paths as XCom values. Never pass DataFrames through XCom — the metadata database is not designed for large binary objects and will degrade performance. Pass file paths and let downstream tasks read directly from S3.

Change Data Capture (CDC) captures every insert, update, and delete from a source database in real time, without modifying application code. The canonical stack: PostgreSQL logical replication → Debezium Kafka Connect source connector → Kafka topic → downstream consumers (feature store, search index, analytics warehouse). Postgres CDC requires: wal_level = logical in postgresql.conf, a replication slot, and a publication. Each CDC event is a JSON message with op (c=create, u=update, d=delete), before (previous row state), after (new row state), source (LSN, table, timestamp). Before field is null for inserts; after is null for deletes. Process the event to determine what changed. Replication slot management is critical: a replication slot accumulates WAL since the last consumer-acknowledged LSN. If the Debezium connector stops for hours, WAL accumulates indefinitely — Postgres disk fills and the database can crash. Monitor pg_replication_slots.confirmed_flush_lsn and alert on slots that are falling behind. Drop the slot if the connector is permanently stopped. Schema registry (Confluent, AWS Glue) serialises CDC event schemas to Avro or Protobuf. Consumers deserialise using the schema ID in each message. Without a registry, a source table column addition breaks all consumers immediately.

A feature store separates feature computation from model training and serving, ensuring the same feature values are available in real time (online store) and in historical batch form (offline store). The online store is optimised for low-latency lookups (1–10ms) during model inference: Redis Hash or DynamoDB, keyed by entity ID (user_id, item_id, session_id). The offline store is optimised for high-throughput historical retrieval during training data generation: S3 + Parquet, BigQuery, Redshift, or DuckDB. The critical constraint: both stores must use the same feature computation logic. If the online store computes "average purchase value in the last 30 days" using one code path and the offline store uses another, the model trains on different features than it serves. This is training-serving skew. Feast is the dominant open-source feature store framework. Core concepts: FeatureView (how to compute a feature from a data source, plus TTL), Entity (the primary key — user_id, item_id), FeatureStore (the client orchestrating reads/writes). Materialisation pushes computed features from the offline source into the online store. get_historical_features() for training data, get_online_features() for inference.

Point-in-time correctness means that when building a training dataset, each feature value is the value that was available at the time of the label event — not the current value, not a value from after the event. Violating this causes target leakage: the model trains on information it would not have had access to in production, producing inflated eval metrics that collapse in production. Example: a fraud detection model's label is "transaction flagged as fraud on 2026-03-15". The feature "user_account_balance_7d" should be the balance in the 7 days before 2026-03-15, not the current balance. If the feature table is joined without a timestamp constraint, and the account balance was updated after the fraud event (e.g., account was frozen, balance went to 0), the model trains on the post-fraud balance — perfect predictive signal that does not exist at inference time. The offline AUC is 0.98; production AUC is 0.62. Implementation: entity_df must include an event_timestamp column per row. The feature store performs an as-of join: for each (entity, timestamp) row, find the most recent feature value with feature_timestamp ≤ event_timestamp. In Feast, this is get_historical_features(entity_df, features) where entity_df has event_timestamp.

Training-serving skew is any difference between the feature distribution the model was trained on and the distribution it receives at serving time. It is the most common and most insidious cause of silent model degradation. Root causes: (1) Different code paths — Python preprocessing in the training notebook vs SQL in the serving pipeline; (2) Different data sources — training on a DB snapshot, serving from a live API; (3) Online store staleness — materialisation job falls behind, serving stale features; (4) Feature drift — real-world distribution shifts over time (concept drift); (5) Null handling differences — Python returns 0 for missing values, SQL returns NULL, model predicts differently. Prevention: define all feature transformations in a single reusable function or dbt model. Both the training pipeline and the serving pipeline import and call the same function. Log feature values at serving time, compute distribution statistics (mean, std, quantiles), and compare to the training distribution using Population Stability Index (PSI) or Kullback-Leibler divergence. Alert when PSI > 0.2 for any feature. Monitor not just the model outputs but the model inputs.

DuckDB is an embedded, in-process OLAP database — no server, no separate process, no cluster. It runs inside Python, R, or a CLI and can query Parquet files on S3, local CSV, Delta Lake, and Iceberg tables without loading them into memory first. The execution model is columnar and vectorised: queries read only the columns needed, apply SIMD operations on batches of values, and leverage file-level and row-group statistics for aggressive predicate pushdown. The result is analytical query performance competitive with Spark for single-node workloads. For AI/ML feature engineering: DuckDB can read all Parquet files in an S3 prefix with a wildcard path, join them, apply window functions, and write the result to a new Parquet file — in minutes, from a notebook, with full SQL. This replaces entire Spark jobs for teams without dedicated data infrastructure. Key capabilities: full SQL (window functions, CTEs, ASOF JOIN for point-in-time joins), Parquet metadata pushdown (skip row groups by min/max statistics), parallel execution on all CPU cores, and a Python API that integrates directly with pandas and Arrow.

BigQuery and Snowflake are the two dominant cloud data warehouses for AI/ML teams operating at scale. BigQuery pricing: on-demand charges per TB of data scanned ($5/TB); reservation slots provide predictable capacity. Cost optimisation levers: partition tables by DATE or TIMESTAMP (require a partition filter in queries to prevent full scans), cluster by high-cardinality filter columns (clustering reduces bytes scanned by pre-sorting within partitions), use materialized views for recurring aggregations (automatically refreshed), cache query results (same query within 24 hours returns cached results at no cost). Snowflake pricing: virtual warehouses (compute) billed per second of activity; storage billed separately. Scale compute independently of storage. Zero-copy cloning creates instant snapshots of tables/schemas without duplicating data — ideal for creating isolated training datasets or experiment branches. Time travel (up to 90 days by default) lets you query historical data states: SELECT * FROM table AT(TIMESTAMP => '2026-01-01'). Snowpark runs Python/Scala/Java directly inside Snowflake's compute engine — useful for model inference close to the data. For ML feature pipelines: partition feature tables by feature_date in BigQuery, cluster by entity_id. Use materialized views for pre-aggregated features that are expensive to recompute. Monitor slot utilization and query costs in the BigQuery Information Schema.

Columnar storage stores all values for a column together on disk, rather than row-by-row. This enables two key optimisations: column pruning (a query on 3 of 50 columns reads ~6% of the data) and high compression (consecutive similar values in a column — dates, user IDs, status enums — compress far better than interleaved row data). Common encoding schemes: Run-Length Encoding (RLE) stores "value repeated N times" rather than N copies — great for low-cardinality columns (status = 'active' × 10 000). Dictionary encoding replaces high-frequency strings with integer codes and a dictionary table — reduces string column storage 80–90%. Delta encoding for timestamps (store differences rather than absolute values). Parquet file structure: file → row groups (default 128 MB) → column chunks → pages. Each column chunk has min/max statistics and a Bloom filter. Query engines read the footer first (microseconds), use statistics to skip entire row groups, then read only required column chunks. This is predicate pushdown. ORC vs Parquet: ORC is optimised for Hive/Hadoop/Spark writes and has better ACID support (row-level inserts/updates in Hive ORC transactions). Parquet is the standard for the Arrow/Iceberg/Delta/BigQuery ecosystem — use Parquet as the default for all new AI/ML data lake work. Small-file problem: many tiny Parquet files (< 10 MB) have high S3 LIST API overhead and poor compression. Target row groups of 128–512 MB per file.

Delta Lake (Databricks, open-sourced) adds a transaction log (_delta_log) to a directory of Parquet files, giving ACID semantics on S3 or ADLS. Every write is an atomic transaction recorded in a JSON log entry. Reads use the log to determine which Parquet files constitute the current table state. ACID operations: INSERT OVERWRITE (idempotent partition replace), MERGE INTO (upsert — insert new rows, update existing rows by key, delete removed rows in one atomic operation), DELETE WHERE (row-level deletes), and UPDATE SET (row-level updates). Concurrent writers use optimistic concurrency: each writer reads the current log version, writes its Parquet files, and attempts to commit a new log entry — if another writer committed first, the log entry fails and the writer retries or aborts. Apache Iceberg (Netflix, now Apache top-level) is an open table format specification: any engine that understands Iceberg metadata (Spark, Flink, Dask, Trino, BigQuery, DuckDB, Athena) can read and write the same table. Hidden partitioning: Iceberg tracks partition evolution in metadata, so queries never need to know the partition scheme — the query planner handles partition pruning automatically. Both support time travel, schema evolution, and partition evolution. Delta is tightly integrated with Databricks. Iceberg is the better choice for multi-engine open environments.

Time travel allows querying a table as it existed at a previous point in time. Delta Lake: SELECT * FROM delta.`s3://path/` VERSION AS OF 5 or TIMESTAMP AS OF '2026-01-01'. delta-rs: DeltaTable(...).load_as_version(5). Iceberg: SELECT * FROM catalog.table FOR SYSTEM_VERSION AS OF 12345 or FOR SYSTEM_TIME AS OF '2026-01-01'. Both retain old Parquet files for the configured retention period (Delta: 7 days by default via VACUUM; Iceberg: via expiry snapshots). Use cases: audit a model's training data as of a specific run, restore data accidentally deleted by a bug, compare two historical states, reproduce a specific experiment's feature values. Schema evolution enables adding, renaming, or (carefully) dropping columns without rewriting the table. Safe changes: add a nullable column (old Parquet files return NULL for the new column — fully backward-compatible), widen a type (INT32 → INT64, FLOAT → DOUBLE). Unsafe changes that break existing readers: rename a column (old files have the old name; readers expecting the new name get NULL), drop a non-nullable column (old files still have it; Parquet schema mismatch), narrow a type. Iceberg handles column renaming transparently via column IDs in metadata — the physical Parquet files use numeric IDs, not column names, so renames are metadata-only operations. Delta Lake requires explicit schema migration steps.

The medallion architecture organises a lakehouse into three quality tiers: Bronze (raw), Silver (validated and cleaned), and Gold (business-ready and ML-ready). Bronze is an exact, append-only replica of the source — raw JSON, CSV, CDC events, API responses. No transformations, no business logic, maximum fidelity. Bronze enables reprocessing from source at any time without re-ingestion. Silver applies schema enforcement, deduplication, null handling, type casting, and light normalisation. Gold applies business logic: joins, aggregations, ML features, denormalised fact tables. Each layer has a stronger quality guarantee than the previous. Partition strategy: Bronze by ingestion_date (when it arrived), Silver by event_date (business date of the event), Gold by feature_window (the computation window relevant to model training). Write strategy: INSERT OVERWRITE or MERGE for idempotency — writing the same Bronze partition twice produces the same result. Landing zone: a transient area (S3 prefix, GCS bucket) where raw files are dropped before Bronze ingestion. Bronze ingestion is triggered when the landing zone has new files. Landing zone is never queried directly. For ML feature pipelines: Gold tables should be versioned (feature_version column or partition) so models trained on Gold v1 can be traced back to the exact computation logic that produced them.

Great Expectations (GX) is a Python data validation framework built around expectations — declarative assertions about the shape, content, and distribution of data. An Expectation Suite is a collection of expectations grouped for a dataset or pipeline stage. A Checkpoint runs a suite against a batch of data and produces a validation result and Data Docs (auto-generated HTML report). Core built-in expectations: expect_column_values_to_not_be_null (null rate = 0), expect_column_values_to_be_in_set (enum membership), expect_column_values_to_be_between (range check), expect_table_row_count_to_be_between (freshness proxy), expect_column_mean_to_be_between (distribution check), expect_column_unique_value_count_to_be_between (cardinality check). Severity: result_format='COMPLETE' and raised=True blocks the pipeline on failure; raised=False logs a warning without stopping. Use blocking expectations for structural violations (null PK, wrong type) and warning expectations for distribution drift. For AI/ML pipelines: run a GX checkpoint at every ETL/ELT stage boundary — after Bronze ingestion (schema check), after Silver cleaning (null rate and row count), before Gold feature computation (distribution checks). Integrate with Airflow via a GXValidateOperator task that fails the DAG if critical expectations fail.

A data contract is a formal agreement between a data producer and data consumer specifying the schema, semantics, SLAs, and ownership of a dataset. Without contracts, schema drift is discovered when a downstream model fails — often weeks after the change. With a contract, the producer gets an automated test failure before merging a breaking change. Pydantic provides runtime schema validation for Python: define a model with typed fields, and model.model_validate(dict) raises a ValidationError if the input violates the schema. Use strict mode (model_config = ConfigDict(strict=True)) to disallow implicit type coercions (e.g., "123" → int). Custom validators with @field_validator enforce business rules: user_id must be a valid UUID, event_timestamp must be in the past, feature_value must be non-negative. For Kafka pipelines, a schema registry (Confluent Schema Registry, AWS Glue) serialises schemas to Avro or Protobuf and enforces compatibility modes: BACKWARD (new schema can read old data), FORWARD (old schema can read new data), FULL (both). For AI/ML systems: validate at every system boundary — API ingress, message queue consumption, feature store writes, model input. The cost of a Pydantic validation is microseconds; the cost of training a model on corrupted data is weeks.

Data freshness SLA: the maximum acceptable age of data at consumption time. For a real-time recommendation model, features must be fresher than 15 minutes. For a daily batch scoring job, features can be up to 26 hours old. Freshness checks: query MAX(event_timestamp) from the feature table and compare to NOW(). Alert if the gap exceeds the SLA threshold. Completeness: the fraction of expected records that are present. For a feature table expected to have one row per active user (500 000 users), a completeness check verifies row_count ≥ 490 000. Row count drops > 10% from the previous day are anomalies. Null rate drift: if the feature column avg_session_duration has historically <0.5% nulls and suddenly has 15% nulls, a data source change has likely broken the computation. Cardinality drift: the number of distinct values in a categorical column. If the feature model_name column had 5 distinct values yesterday and has 50 today, a new model was deployed and the feature pipeline picked it up — or a bug introduced random strings. Z-score on rolling metric history detects sudden changes. Population Stability Index (PSI) detects gradual distribution drift. Alert thresholds: PSI > 0.1 for informational, > 0.25 for critical. Null rate: alert if current null rate / baseline null rate > 3.0.

Data lineage tracks the provenance of data: where it came from, what transformed it, and where it goes. Table-level lineage shows which tables feed which tables — sufficient for impact analysis at coarse granularity. Column-level lineage tracks which source columns produce which output columns — critical for AI/ML where you need to trace a model input feature back to the exact source column and transformation applied to it. OpenLineage is an open specification for lineage events. Each event has a run (job + run_id), inputs (datasets read), and outputs (datasets written), with facets for schema, statistics, and custom metadata. Emitting OpenLineage events from dbt, Spark, Airflow, and custom Python jobs makes lineage aggregatable across the entire data stack. Marquez is the reference OpenLineage server — it collects events and provides a lineage graph API and UI. dbt generates lineage automatically from model dependencies: SELECT from source A and model B → dbt draws an edge from A and B to the current model. dbt docs generate a navigable lineage DAG. For ML: trace any Gold feature back to its source columns in Bronze, identify all downstream models consuming a Gold table, and perform impact analysis when a source schema changes — "if raw.events.user_revenue is renamed, which models break?".

Data observability extends monitoring beyond predefined rules to automated anomaly detection across freshness, volume, schema, distribution, and lineage. The key distinction from traditional monitoring: observability platforms learn normal baselines from historical data and alert on deviations without requiring manual threshold tuning for every column and table. Monte Carlo: managed SaaS, automatically profiles all tables and fields, detects anomalies using ML on query logs and table metadata, provides circuit breakers to block downstream pipelines when anomalies are detected, and maps issues to affected assets via lineage. DataHub: open-source data catalog + lineage + observability. Supports metadata ingestion from 50+ sources, lineage from dbt/Spark/Airflow/BigQuery, and basic data quality assertions. Elementary: dbt-native observability — runs as a dbt package, collects test results and model run statistics, and generates an observability dashboard using dbt artifacts. ML input monitoring: extend observability to the model serving layer. Log feature distributions from every inference request. Compare the rolling serving distribution to the training distribution using PSI or KL divergence. Alert when model inputs drift — this is the early-warning signal for model performance degradation, detectable weeks before business metrics decline.

ML experiment reproducibility requires knowing exactly which dataset, which code version, and which model code was used to produce a specific model artifact. Months after training, a compliance audit or model regression investigation may require reproducing the exact training conditions. DVC (Data Version Control) adds git-like versioning to large data files and datasets. dvc add registers a file or directory, storing a hash in a .dvc file that is committed to git. dvc push uploads the actual data to a remote (S3, GCS, Azure). dvc pull downloads the exact version checked out in git. This makes dataset versions reproducible: git checkout model-v2-branch && dvc pull restores the exact training data used for that branch. MLflow Datasets API logs dataset metadata (name, digest/hash, schema, source) with each training run: mlflow.log_input(). On reproduction, the logged digest identifies the exact dataset version. Combined with DVC (for the actual file) and git (for the code), this creates a three-way reproducibility anchor. For feature stores: record the Feast offline feature retrieval job parameters (as_of_timestamp, entity_df hash) with the MLflow run. For Delta Lake training data: record the table version number. These form a complete provenance chain: code version + data version + feature version = reproducible model.