DZone Spotlight

The Symptom I spent months building a 2-bit quantization scheme for Qwen3. The model went from 8 GB to 2.6 GB, a 4.5x reduction. Then I measured throughput. It was barely faster than the FP16 baseline. That did not add up. Token decoding in an LLM is memory bound, not compute bound. You read every weight once per token, and the arithmetic per byte read is tiny. This is well established: Pope et al. identify the memory traffic needed to load parameters and KV cache from high-bandwidth memory as a primary constraint on generative inference [1], and the AWQ authors frame model size as raising both a memory-size barrier for serving and a memory-bandwidth barrier for token generation [2]. If the weights are four times smaller, you read four times less, and you should go meaningfully faster. Something was eating the gain. The Obvious Suspect My first assumption was that my own quantization was at fault: dequantization cost, no fused kernel, irregular memory access. That is a fair suspicion. Extreme quantization schemes pay a decode cost on every weight, and if you have not written a fused kernel, you are materializing full-precision weights back into memory before every matmul, which defeats the entire point of compressing them. The suspicion was partly correct. It was also wrong in an expensive way, because it kept me staring at my own code for weeks. Profile Before You Optimize The procedure that eventually found the problem: Time a full forward pass at the top level. That is your baseline.Time each component separately: attention, MLP, embeddings, and the output projection.Build a memory-traffic model. Count the bytes that must move per token, then divide by your device's achievable bandwidth. That is the floor you are trying to reach.If you are far from the floor, the gap is not where you think it is. Step 2 is the one people skip, and the reason is structural. You optimized the quantized layers, so you profile the quantized layers. The parts you did not touch are, by construction, the parts you are not looking at. When I finally instrumented everything, the quantized layers were fine. The time was going somewhere else. The Culprit Qwen3-4B has a hidden size of 2560 and a vocabulary of 151936 tokens. The output projection, lm_head, is a 151936 x 2560 matrix. In bf16: Plain Text 151936 x 2560 x 2 bytes = 778 MB This is the largest single tensor in the model. It is also, in most setups, left in full precision, because quantizing the output projection tends to cost more in quality than it saves in memory. In candle 0.9.2, the logits were computed as a matmul against the transposed weight. Transposing produces a non-contiguous tensor. The matmul path did not accept that layout, so it materialized a contiguous copy first. 778 MB copied. Per token. Not read, copied: 778 MB read plus 778 MB written, roughly 1.5 GB of memory traffic, purely to reorder bytes before any arithmetic happened. On every token generated. The large-vocabulary head, being the one that quietly dominates, is not a new phenomenon. Wijmans et al. show that on the training side, growing vocabularies shifted the memory footprint disproportionately onto the cross-entropy layer, to the point where it can account for the large majority of training memory, and they solve it with a fused kernel that never materializes the full logit matrix [3]. Different mechanism, same structural cause: the vocabulary dimension is large, and anything that touches it in full is expensive. Quantifying It You do not need a benchmark to see the shape of the problem. The architecture is public, so the traffic is arithmetic. Qwen3-4B has 3.63B parameters in its 36 transformer blocks and 389M in the tied embedding and output projection. Per decoded token, weight traffic is the block weights at whatever precision you quantized them to, plus the lm_head at bf16, plus the copy. Two things worth noting. First, the FP16 column comes out at 8.045 GB, which matches the 8.04 GB I actually measured serving the model. The model is sound. Second, look at the red band. The copy is a fixed 1.5 GB regardless of how hard you compress, so its share climbs from 16% at FP16 to 48% at 2 bits. That has a direct consequence for the speedup you can achieve. If decode is bandwidth-bound, throughput is inversely proportional to traffic, which gives a ceiling: At 2 bits, the copy caps you at 2.96x when the traffic model allows 4.77x. You lose 38% of the available gain, and you lose it to a memory copy that does no arithmetic at all. The Fix The GEMM does not need the copy. cuBLAS, and every serious BLAS, takes transpose flags for its operands. A transposed matrix is not a different matrix; it is the same bytes with different strides. The transpose should be free. The fix routes the operation, so the transposed weight reaches the GEMM directly instead of being materialized into a fresh contiguous buffer. That work is upstream: Issue: huggingface/candle#3871PR: huggingface/candle#3872 If you are running candle, you already have it. What It Was Worth About half of the end-to-end throughput improvement I had been attributing to my quantization work came from this fix. Half. Months of work on lattice quantization, and a comparable share of the measured speedup came from deleting a memory copy in code I did not write and had not thought to look at. Note that the analytical model above predicted 48% at 2 bits, before I had measured anything. That convergence is the useful part. This is not an anecdote about a single bug in a single framework; it is a predictable consequence of compressing part of a model while leaving the rest alone. What Generalizes Fixed costs grow in relative terms as you compress. Quantization shrinks what you quantized. Everything else stays the same size and becomes a larger share of your runtime. If you are working on extreme quantization, this is not a footnote; it is the next problem. Past a certain compression ratio, the uncompressed components are your bottleneck by definition, and no further work on the quantization scheme will move your throughput. Layout bugs hide well. A transposed copy is functionally correct. Tests pass. Output is bit-identical. Nothing is broken; it is just slow, and slowness does not throw. These survive a long time in mature codebases because everyone assumes the framework handles it. Count bytes moved, not FLOPs. This is the central lesson of FlashAttention, which got its speedup not by reducing arithmetic but by avoiding materializing a large intermediate in HBM, and which actually performs more FLOPs than the standard implementation while running several times faster [4]. The transposed copy is the same failure mode in miniature: zero arithmetic, all traffic. Do not trust your priors about where time goes, least of all in your own project. I had every reason to believe the bottleneck was in my quantization code. I had just written it; it was the novel part, and it was where all my attention was. That is precisely the bias profiling exists to correct. Checklist Before optimizing an inference pipeline: Build the traffic model first. Bytes moved per token divided by achievable bandwidth. Know your floor before you start.List every tensor above 100 MB. For each, ask what precision it is in and whether it is copied on the hot path.Profile the components you did not modify.Look for .t(), .transpose() or .permute() immediately before a matmul. Any of them can trigger a materialized copy.Recompute your ceiling after every compression step. The bottleneck moves as you compress. The bottleneck is rarely where the interesting work is. That is what makes it a bottleneck. Notes on the Figures Both figures are analytical, derived from the published Qwen3-4B configuration, not measured. They count weight traffic only and exclude the KV cache, activations, norms, and biases, so they are a lower bound on real traffic. They assume decode is fully bandwidth-bound, which is the standard regime at batch size 1. The script that produces them is a few dozen lines and reproduces from the config file alone. References [1] R. Pope, S. Douglas, A. Chowdhery, J. Devlin, J. Bradbury, A. Levskaya, J. Heek, K. Xiao, S. Agrawal, J. Dean. Efficiently Scaling Transformer Inference. MLSys 2023 (Outstanding Paper Award). arXiv:2211.05102 [2] J. Lin, J. Tang, H. Tang, S. Yang, W.-M. Chen, W.-C. Wang, G. Xiao, X. Dang, C. Gan, S. Han. AWQ: Activation-aware Weight Quantization for On-Device LLM Compression and Acceleration. MLSys 2024 (Best Paper Award), Proceedings of MLSys 6:87-100. arXiv:2306.00978 [3] E. Wijmans, B. Huval, A. Hertzberg, V. Koltun, P. Krähenbühl. Cut Your Losses in Large-Vocabulary Language Models. ICLR 2025. arXiv:2411.09009 [4] T. Dao, D. Y. Fu, S. Ermon, A. Rudra, C. Ré. FlashAttention: Fast and Memory-Efficient Exact Attention with IO-Awareness. NeurIPS 2022. arXiv:2205.14135

By Pier-Jean MALANDRINO CORE

Change data capture (CDC) pipelines look straightforward on paper: capture database changes, publish them to Kafka, and update downstream systems. The difficulty starts when events are duplicated, consumers restart, projections drift, or a team needs to replay months of history without corrupting the state it is trying to recover. A reliable CDC design has to account for those failure modes from the beginning. That means combining Kafka and Debezium with idempotent writes, deterministic projections, controlled replay workflows, reconciliation checks, and enough recovery evidence to explain what happened when something goes wrong. The architecture: The goal is not only to move inventory changes quickly. The goal is to make replay safe enough that operators can rebuild and explain the derived state after failure. This article builds one concrete pattern: The important detail is that replay safety is not a single feature. It is the result of several boring decisions lining up correctly. Data Model The data model should separate the aggregate state, the classification state, and the transaction history. PLSQL CREATE TABLE inventory_stock_on_hand ( sku VARCHAR(64) PRIMARY KEY, stock_on_hand BIGINT NOT NULL, updated_at TIMESTAMP NOT NULL ); CREATE TABLE inventory_bucket ( sku VARCHAR(64) NOT NULL, bucket_type VARCHAR(32) NOT NULL, location_id VARCHAR(64) NOT NULL, quantity BIGINT NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY (sku, bucket_type, location_id) ); CREATE TABLE inventory_transaction ( event_id VARCHAR(128) PRIMARY KEY, sku VARCHAR(64) NOT NULL, seller_id VARCHAR(64) NOT NULL, delta_quantity BIGINT NOT NULL, event_time TIMESTAMP NOT NULL, accepted_at TIMESTAMP NOT NULL ); CREATE INDEX idx_inventory_transaction_sku_time ON inventory_transaction (sku, event_time); CREATE INDEX idx_inventory_bucket_sku_bucket ON inventory_bucket (sku, bucket_type); The transaction table is the recovery anchor. If the availability projection drifts, the system needs a history to explain the projection. Do not rely only on the mutable aggregate table. inventory_stock_on_hand is useful for fast reads, but it is not enough for recovery. If the aggregate is wrong, it cannot explain how it became wrong. The accepted transaction history gives replay something durable to reason from. Ingestion Event Use an event ID that can survive retries and replay. JSON { "event_id": "mkt-evt-8f11a", "sku": "1231241", "quantity": 100, "operation": "I", "event_time": "2026-06-19T18:23:11Z", "seller_id": "seller-42" } The consumer should perform an idempotent write. One pattern is to insert the transaction first using event_id as the primary key. If the insert fails because the event already exists, skip the duplicate and emit a duplicate-suppression metric. Java public InventoryWriteResult apply(InventoryEvent event) { try { transactionRepository.insert(event.toTransactionRow()); } catch (DuplicateKeyException duplicate) { metrics.increment("inventory.duplicate_event"); return InventoryWriteResult.duplicate(event.eventId()); } stockRepository.incrementStockOnHand(event.sku(), event.quantity()); bucketRepository.incrementBucket(event.sku(), "SELLABLE", event.quantity()); return InventoryWriteResult.accepted(event.eventId()); } In production, the accepted transaction insert and the aggregate updates should be part of the same database transaction. A useful shape is: PLSQL BEGIN; WITH accepted AS ( INSERT INTO inventory_transaction ( event_id, sku, seller_id, delta_quantity, event_time, accepted_at ) VALUES ( :event_id, :sku, :seller_id, :delta_quantity, :event_time, now() ) ON CONFLICT (event_id) DO NOTHING RETURNING sku, delta_quantity ) INSERT INTO inventory_stock_on_hand (sku, stock_on_hand, updated_at) SELECT sku, delta_quantity, now() FROM accepted ON CONFLICT (sku) DO UPDATE SET stock_on_hand = inventory_stock_on_hand.stock_on_hand + EXCLUDED.stock_on_hand, updated_at = now(); COMMIT; That ON CONFLICT clause is not just a database convenience. It is part of the replay contract. It ensures that retrying the same business event does not apply the same inventory delta twice. Debezium Configuration Enable PostgreSQL logical decoding and configure Debezium to emit CDC topics for the inventory tables. JSON { "name": "postgres-inventory-connector", "config": { "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "database.hostname": "<POSTGRES_HOSTNAME>", "database.port": "5432", "database.user": "<POSTGRES_USER>", "database.password": "<POSTGRES_PASSWORD>", "database.dbname": "<POSTGRES_DBNAME>", "topic.prefix": "inventory_source", "plugin.name": "pgoutput", "slot.name": "debezium_inventory_slot", "publication.autocreate.mode": "filtered", "table.include.list": "public.inventory_stock_on_hand,public.inventory_bucket,public.inventory_transaction", "snapshot.mode": "initial", "heartbeat.interval.ms": "10000", "tombstones.on.delete": "false", "key.converter": "org.apache.kafka.connect.json.JsonConverter", "value.converter": "org.apache.kafka.connect.json.JsonConverter", "key.converter.schemas.enable": "true", "value.converter.schemas.enable": "true" } } Debezium gives you history, but not recovery confidence. The confidence comes from how you key, project, replay, and reconcile that history. For replay work, track these connector facts in your runbook: Connector name and versionReplication slot namePublication name and included tablesSnapshot mode used for initial loadTopic prefixLast processed LSNConnector lagSchema history topic When a connector interruption happens, those details tell you whether you can resume normally, need a bounded replay, or need a new snapshot plus downstream reconciliation. Partition-Aware Routing The partition key should be chosen from the business ordering boundary. Java public class SkuPartitioner implements Partitioner { @Override public int partition( String topic, Object key, byte[] keyBytes, Object value, byte[] valueBytes, Cluster cluster) { InventoryEvent event = (InventoryEvent) value; String orderingKey = event.getSku(); int partitionCount = cluster.partitionCountForTopic(topic); return Math.floorMod(orderingKey.hashCode(), partitionCount); } } Partitioning is not merely a throughput setting. If the projection depends on entity-local ordering, the entity belongs in the key. Kafka Streams Topology A simplified topology might rekey CDC records by SKU, materialize source tables, and compute availability. Java StreamsBuilder builder = new StreamsBuilder(); KTable<String, StockOnHand> stock = builder.table("inventory_source.public.inventory_stock_on_hand", Consumed.with(Serdes.String(), stockSerde)); KTable<String, InventoryBuckets> buckets = builder.table("inventory_source.public.inventory_bucket", Consumed.with(Serdes.String(), bucketSerde)); KTable<String, AvailabilityProjection> availability = stock.join( buckets, (stockRow, bucketRows) -> AvailabilityProjection.compute(stockRow, bucketRows), Materialized.<String, AvailabilityProjection, KeyValueStore<Bytes, byte[]>>as("availability-store") .withKeySerde(Serdes.String()) .withValueSerde(availabilitySerde) ); availability .toStream() .filter((sku, projection) -> projection.isPublishable()) .to("inventory.availability.v2", Produced.with(Serdes.String(), availabilitySerde)); The projection function should be deterministic. If replaying the same accepted history does not produce the same projection, the topology is not replay-safe. Recovery Contract Attach a Recovery Contract to the flow. YAML recovery_contract: flow: inventory-availability-projection tuple: "<H, O, I, F, S, Q, E>" history: source: - inventory_transaction - debezium.inventory_transaction order: key: sku idempotency: key: event_id duplicate_policy: skip_and_report function: name: compute_sellable_availability deterministic: true scope: supported: - by_sku - by_time_window - by_partition checks: - stock_on_hand_matches_transactions - sellable_quantity_non_negative - projection_event_time_valid evidence: - replay_scope - events_processed - duplicates_skipped - projections_changed - reconciliation_failures - confidence_status Treat this file as executable architecture documentation. A service should fail fast if the contract is incomplete for a critical flow. Java public final class RecoveryContractValidator { public void validate(RecoveryContract contract) { requireNonEmpty(contract.flow(), "flow"); requireNonEmpty(contract.history().source(), "history.source"); requireNonEmpty(contract.order().key(), "order.key"); requireNonEmpty(contract.idempotency().key(), "idempotency.key"); requireNonEmpty(contract.function().name(), "function.name"); requireTrue(contract.function().deterministic(), "projection must be deterministic"); requireNonEmpty(contract.scope().supported(), "scope.supported"); requireNonEmpty(contract.checks(), "checks"); requireNonEmpty(contract.evidence(), "evidence"); } private void requireNonEmpty(Object value, String field) { if (value == null || value.toString().isBlank()) { throw new IllegalArgumentException("Missing recovery contract field: " + field); } } private void requireTrue(boolean value, String message) { if (!value) { throw new IllegalArgumentException(message); } } } That validator does not make the system correct by itself. It prevents a more common failure: discovering during an incident that nobody defined the replay scope, idempotency key, or reconciliation checks. Replay Workflow Replay should be treated as a controlled workflow. Plain Text 1. Identify incident scope. 2. Select replay scope by SKU, time window, or partition. 3. Read authoritative history. 4. Rebuild deterministic projection. 5. Run reconciliation checks. 6. Emit recovery evidence. 7. Republish only if checks pass. The output should be an evidence report. JSON { "recovery_id": "rec-2026-06-19-001", "flow": "inventory-availability-projection", "events_processed": 1842, "duplicates_skipped": 17, "projection_rows_changed": 11, "reconciliation": { "stock_on_hand_matches_transactions": true, "sellable_quantity_non_negative": true, "projection_event_time_valid": true }, "confidence_status": "trusted" } A replay runner can keep the workflow explicit: Java public RecoveryEvidence replay(ReplayRequest request) { RecoveryContract contract = contracts.load(request.flow()); validator.validate(contract); ReplayScope scope = scopeResolver.resolve(request, contract); List<InventoryEvent> history = historyReader.read(contract.history(), scope); ReplayResult result = projector.rebuild(history, contract.function()); ReconciliationResult reconciliation = reconciliationRunner.run(contract.checks(), scope, result); RecoveryEvidence evidence = RecoveryEvidence.builder() .recoveryId(UUID.randomUUID().toString()) .flow(request.flow()) .scope(scope) .eventsProcessed(history.size()) .duplicatesSkipped(result.duplicatesSkipped()) .projectionsChanged(result.changedRows()) .reconciliation(reconciliation) .confidenceStatus(reconciliation.passed() ? "trusted" : "review_required") .build(); evidenceStore.write(evidence); if (request.publish() && reconciliation.passed()) { publisher.publish(result.projections()); } return evidence; } The replay runner should support dry runs. Dry runs let operators answer "What would change?" before republishing availability, billing, or detection outputs. Operational Metrics Track ordinary health and recovery confidence separately. Ordinary health: Consumer lagConnector lagTask restartsDLQ countEnd-to-end latency Recovery confidence: Replay durationReplay scope sizeDuplicate suppression countProjection rows changedReconciliation failuresConfidence status Example metric names: Plain Text inventory_ingest_events_total{result="accepted|duplicate|rejected"} inventory_cdc_connector_lag_seconds{connector="postgres-inventory-connector"} inventory_stream_projection_lag_seconds{topology="availability"} inventory_replay_duration_seconds{flow="inventory-availability-projection"} inventory_replay_events_processed_total{flow="inventory-availability-projection"} inventory_replay_duplicates_skipped_total{flow="inventory-availability-projection"} inventory_reconciliation_failures_total{check="stock_on_hand_matches_transactions"} inventory_recovery_confidence_status{status="trusted|review_required|failed"} Alert on disagreement, not only lag. A good pipeline can be caught up and still be wrong. YAML alerts: - name: InventoryProjectionReconciliationFailure expr: inventory_reconciliation_failures_total > 0 severity: page - name: InventoryReplayRequiresReview expr: inventory_recovery_confidence_status{status="review_required"} > 0 severity: ticket - name: InventoryConnectorLagHigh expr: inventory_cdc_connector_lag_seconds > 300 severity: ticket Reconciliation Queries Reconciliation should be executable, not just a diagram in a runbook. Start with invariants that are simple enough to automate. Example: Stock-on-hand should match accepted transaction deltas for a replay window. PLSQL WITH accepted_delta AS ( SELECT sku, SUM(delta_quantity) AS expected_delta FROM inventory_transaction WHERE accepted_at BETWEEN :from_time AND :to_time GROUP BY sku ), actual_delta AS ( SELECT sku, stock_on_hand - :baseline_stock_on_hand AS observed_delta FROM inventory_stock_on_hand WHERE sku = :sku ) SELECT a.sku, a.expected_delta, b.observed_delta, (a.expected_delta = b.observed_delta) AS matches FROM accepted_delta a JOIN actual_delta b ON a.sku = b.sku; Example: Sellable inventory should never be negative. PLSQL SELECT sku, location_id, quantity FROM inventory_bucket WHERE bucket_type = 'SELLABLE' AND quantity < 0; These queries are not academically exciting, but they are operationally powerful. They turn "the replay finished" into "the replay finished and the invariants passed." Replay Endpoint Sketch A replay workflow should be explicit and permissioned. One possible internal API: HTTP POST /internal/recovery/replay Content-Type: application/json { "flow": "inventory-availability-projection", "scope": { "type": "sku_and_time_window", "sku": "1231241", "from_event_time": "2026-06-19T18:00:00Z", "to_event_time": "2026-06-19T19:00:00Z" }, "dry_run": false, "requested_by": "sre-oncall", "reason": "projection drift after stream task restart" } The response should not just say 200 OK. JSON { "recovery_id": "rec-2026-06-19-001", "status": "trusted", "events_processed": 1842, "duplicates_skipped": 17, "projections_changed": 11, "reconciliation_failures": 0, "evidence_uri": "<RECOVERY_EVIDENCE_URI>" } The response is the operational artifact. It gives the team something to attach to an incident timeline and something to compare against later recovery runs. Tests for Replay Safety Replay safety should be tested before production incidents. Java @Test void replayingSameHistoryDoesNotChangeProjectionTwice() { List<InventoryEvent> history = List.of( event("evt-1", "SKU-1", 10), event("evt-2", "SKU-1", -2), event("evt-1", "SKU-1", 10) // duplicate ); AvailabilityProjection first = projector.replay(history); AvailabilityProjection second = projector.replay(history); assertThat(first).isEqualTo(second); assertThat(first.sellableQuantity()).isEqualTo(8); assertThat(first.duplicatesSkipped()).isEqualTo(1); } Also test late events, schema versions, partition rebalance, connector restart, and partial replay by entity. If replay is part of your recovery model, it deserves the same test discipline as the happy-path pipeline. Add failure injection tests that mirror production recovery: Java @Test void lateEventTriggersReviewWhenItChangesPublishedAvailability() { ReplayScope scope = ReplayScope.forSkuAndWindow( "SKU-1", Instant.parse("2026-06-19T18:00:00Z"), Instant.parse("2026-06-19T19:00:00Z") ); history.append(event("evt-1", "SKU-1", 10, "2026-06-19T18:01:00Z")); history.append(event("evt-2", "SKU-1", -3, "2026-06-19T18:59:00Z")); history.appendLate(event("evt-3", "SKU-1", -2, "2026-06-19T18:30:00Z")); RecoveryEvidence evidence = replayRunner.replay( ReplayRequest.dryRun("inventory-availability-projection", scope) ); assertThat(evidence.eventsProcessed()).isEqualTo(3); assertThat(evidence.projectionsChanged()).isGreaterThan(0); assertThat(evidence.confidenceStatus()).isEqualTo("review_required"); } Failure Injection Matrix Use a small matrix before every major release of the pipeline. Duplicate Event Injection: Send the same event_id twice.Expected evidence: duplicates_skipped > 0; no double-counted stock.Late Event Injection: Delay event arrival until after the projection has already published output.Expected evidence: late event count, changed projections, and review status if the output changes.Connector Pause Injection: Stop the Debezium connector for several minutes.Expected evidence: connector lag, replay scope, and reconciliation status.Offset Rewind Injection: Reprocess a known event range.Expected evidence: deterministic replay agreement.Schema Change Injection: Replay old and new schema versions.Expected evidence: schema versions recorded in the recovery evidence.Bad projection deploy Injection: Publish an incorrect derived state, then replay.Expected evidence: projections changed; reconciliation passes after rebuild. The point is not to create chaos for its own sake. The point is to practice the exact recovery motion before a real incident. Production Hardening Checklist Before relying on replay in production, confirm: The authoritative history has retention longer than the largest expected recovery window.The idempotency key is stable across producer retries.The Kafka partition key matches the business ordering boundary.The projection function is deterministic for the supported replay scope.The contract names every source topic, source table, check, and evidence field.The replay endpoint supports dry runs.Republish requires reconciliation success.Evidence is written to durable storage.Evidence records include schema versions and replay input bounds.Operators can find the runbook from the alert.The DLQ is treated as an input to recovery, not as the recovery plan itself. For high-value flows, make this checklist part of the architecture review. It is much cheaper to define replay semantics while designing the pipeline than to invent them under pressure. Common Mistakes Treating CDC topics as transient integration messages instead of durable recovery history.Choosing partition keys for infrastructure convenience rather than business ordering.Allowing stream processors to perform hidden non-idempotent side effects.Measuring lag but not correctness.Resetting offsets without a reconciliation plan.Assuming exactly-once semantics removes the need for recovery evidence. Conclusion Replay-safe CDC pipelines require more than Kafka, Debezium, and stream processing. They require explicit recovery semantics. Recovery Contracts give teams a compact way to define those semantics. Confidence-carrying replay gives operators evidence that the recovered state can be trusted. That is the difference between a pipeline that resumes and a platform that actually recovers.

I needed a job to run once a day, remember what it did yesterday, and cost nothing to operate. The obvious answer is a small VM with cron, or a Lambda plus DynamoDB. I did not want to pay for either, and I did not want a server to patch. So I pushed the whole thing onto GitHub Actions and used a JSON file committed back to the repo as the database. It has now run 139 times in production on the free tier, tracking just over 1,000 records, and the operating bill is still zero. Here is the part that took the most thought: keeping state across runs that are, by design, completely stateless. "The daily digest the pipeline sends, with new postings badged." The Constraint That Shapes Everything GitHub Actions gives you a cron trigger for free: Shell on: schedule: - cron: "0 16 * * *" # 09:00 EST daily workflow_dispatch: # manual button That solves scheduling. It does not solve memory. Every run starts on a fresh ubuntu-latest runner with a clean checkout. Anything you write to disk during the run is gone when the job ends. For my use case (a daily digest that must not re-send jobs it already sent), that is the entire problem. The script has to know what it saw yesterday. The standard fix is an external store. But for a workload that writes a few kilobytes once a day, standing up a database is more operational surface than the actual task. The repo is already there, the runner already has a checkout, and the workflow already has a token. So the store is the repo. Git as the Database The pattern is three lines at the end of the workflow: stage the state files, commit if they changed, push. Shell permissions: contents: write # the default token is read-only; you must opt in # ... run the script, which writes seen_links.json and job_history.json ... - name: Commit updated history files run: | git config user.name "GitHub Actions Bot" git config user.email "[email protected]" git add seen_links.json job_history.json 2>/dev/null || true git diff --staged --quiet || git commit -m "Update job history [skip ci]" git push One automated commit per day. The repo's own history is the database, and the audit log comes for free. Two details here are not optional, and I learned both the slow way. First, permissions: contents: write. The GITHUB_TOKEN handed to a workflow is read-only by default. Without this block, the git push fails with a 403, and the failure is at the very end of the run, after the real work succeeded, so it looks like everything worked until you check tomorrow and the state never persisted. Second, git diff --staged --quiet || git commit. This commits only when something actually changed. Committing an unchanged tree is an error, and a daily job that finds nothing new is a normal Tuesday. The || makes "nothing to commit" a no-op instead of a red X. The result is that the database lives in git history. Every state change is a commit. I can read yesterday's seen_links.json by checking out yesterday's commit. That is free audit logging I did not have to build. The Infinite-Loop Trap Here is the gotcha that will bite anyone who copies this pattern: a workflow that pushes a commit can trigger a workflow that runs on push, which pushes a commit, which triggers the workflow. The guard is the [skip ci] token in the commit message: git commit -m "Update job history [skip ci]" GitHub treats [skip ci] in a commit message as "do not start workflows for this commit." My scheduled workflow uses it. I also had a second, older workflow file in the repo whose commit message was a plain "Update seen links" with no skip token. Because that workflow only ran on schedule (not on push), it never actually looped, but it was one: push line away from a runaway. If your state-committing workflow has any push trigger, the skip token is the difference between a daily job and a billing incident. Put it in from the start. Decoupling "New" From "Still Worth Showing" The other decision I am glad I made early was separating two ideas that look like one: a record being new today, and a record being relevant today. A naive version sends only what is new since the last run. That breaks the moment a run finds nothing, or the moment the user skips a day. So state is two files with two jobs. seen_links.json is a flat set of every URL ever processed, used purely for deduplication. job_history.json is a rolling window: each entry carries a first_seen timestamp, and a record stays in the window for ten days regardless of how many runs happen in between. Shell def cleanup_old_jobs(history, max_days): today = datetime.now().date() cleaned = {} for category, jobs in history.items(): cleaned[category] = [] for job in jobs: first_seen = job.get("first_seen") seen_date = datetime.fromisoformat(first_seen).date() if (today - seen_date).days <= max_days: cleaned[category].append(job) return cleaned So "new" is computed per run (anything not in seen_links.json), and "relevant" is the trailing ten-day window. The daily output is never empty, nothing is ever sent twice, and a record ages out on a fixed schedule instead of vanishing the first quiet day. Two files, two responsibilities. Trying to make one structure do both is where this kind of project usually rots. The Dependency I Refused to Add The source data is two different table formats from upstream pages: one uses GitHub-flavored markdown tables, the other uses raw HTML tables inside the same document. The clean answer is a parsing library. I chose regex and the standard library instead, and I want to be honest about why and what it costs. The script tries markdown first, then falls back to HTML: Shell parsed_jobs = parse_markdown_table(text) if len(parsed_jobs) == 0: parsed_jobs = parse_html_table(text) # SimplifyJobs uses HTML The upside is a requirements.txt with exactly one line (requests), which means the install step on a cold runner is near-instant, and there is no transitive dependency that can break a 9 a.m. job. The downside is real, and I will not pretend otherwise: regex table parsing is brittle. When an upstream source changed its column layout, my parser silently returned zero rows for that source. It did not crash. It just quietly stopped finding jobs from one feed, which is the worst failure mode because nothing alerts you. For a personal tool with one user, that trade is fine: I notice within a day and patch a regex. For anything with real users, I would add a parser and, more importantly, a "parsed zero rows from a source that normally returns dozens" alarm. The lesson is not "regex bad." It is that a zero-result parse should be treated as a failure signal, not a valid empty result. Cheap Correctness Wins Two small filters do more work than their size suggests. Deduplication is a set membership check, which makes the whole pipeline idempotent. Running the workflow twice in one day produces the same output as running it once, because the second pass finds everything already in seen_links.json. For a cron job that you will inevitably trigger manually while debugging, idempotency is what lets you mash the button without consequences. Link quality is an allowlist of known applicant-tracking domains (Greenhouse, Lever, Workday, Ashby, and friends). Upstream rows mix real application links with company homepages and image badges. Filtering to known ATS hosts drops the noise without trying to validate every URL: Shell JOB_HOST_HINTS = ("greenhouse.io", "lever.co", "myworkdayjobs.com", "ashbyhq.com", "smartrecruiters.com", "icims.com", ...) def looks_like_job_link(url): return any(h in url.lower() for h in JOB_HOST_HINTS) An allowlist is the right default here because the failure mode is asymmetric. Letting through a dead homepage link wastes a click; an allowlist that occasionally drops a valid but unusual ATS is a one-line addition when I notice it. I would rather under-include than ship dead links. What it Actually Costs The numbers from production: 139 scheduled runs committed back to the repo, 1,062 unique links tracked in the dedupe set, three Python files, one runtime dependency, and one YAML workflow. Infrastructure cost is zero, because GitHub Actions' free tier covers a once-a-day job comfortably and Gmail's SMTP handles the delivery. There is no server, no database, no secret rotation beyond an app password, and nothing to wake up to at 3 a.m. When is This Pattern the Right Call? Reach for git-as-a-database when the write volume is low (you are committing on a human timescale, not a request timescale), the state is small and serializable, a single writer is doing the writing (the scheduled job), and you actively want the change history. A daily digest, a status snapshot, a slowly-changing config, a scoreboard: all good fits. Do not reach for it when you have concurrent writers (two runs racing to push will collide and one will fail the non-fast-forward push), when the state is large enough to bloat the repo, or when you need sub-minute reads or transactions. At that point you have outgrown the trick and a real datastore earns its keep. For everything in the first bucket, the calculus is hard to beat: the scheduler, the runtime, the storage, and the audit log are all things you already have for free. The only code you write is the part that does the work.

In high-volume data platforms, hardcoding validation logic into individual processing pipelines creates significant operational drag. As an enterprise data asset footprint grows, maintaining manual checks for hundreds of tables inevitably leads to mounting technical debt, silent schema drift, and a fragmented audit trail. To achieve data governance at scale, data architects must decouple validation rules from the execution engine. By utilizing a centralized metadata repository to dynamically generate validation suites, organizations can transform data quality from a reactive, script-based bottleneck into a configuration-driven infrastructure asset. The Metadata-Driven Architecture Instead of embedding validation constraints directly inside an ETL/ELT pipeline, this pattern isolates validation rules inside a centralized relational database schema. The orchestration engine programmatically queries this metadata at runtime, constructs the validation suites on the fly, executes them against target tables, and routes the evaluation metrics to an observability layer. This architecture provides three primary engineering advantages: Decoupled Governance: Data stewards can alter business rules or add expectations via simple DML updates without modifying or redeploying production application code.Schema Drift Resilience: The engine dynamically adapts to structural variations by programmatically evaluating target datasets against rules defined at the column level.Centralized Observability: Every rule execution generates a standardized, traceable metric payload, laying a consistent foundation for real-time data auditing and data lineage maps. 1. Defining the Metadata Schema (DDL) To implement this framework in an enterprise Lakehouse ecosystem, the metadata table must act as an immutable source of truth for constraints. Below is the production DDL required to initialize the control directory in Snowflake or Databricks: SQL CREATE TABLE data_quality_rules ( rule_id INT IDENTITY(1,1), table_name VARCHAR(255) NOT NULL, column_name VARCHAR(255) NOT NULL, expectation_type VARCHAR(255) NOT NULL, expectation_kwargs VARIANT NOT NULL, -- Stored as JSON object is_active BOOLEAN DEFAULT TRUE, updated_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(), CONSTRAINT pk_rule_id PRIMARY KEY (rule_id) ); -- Seed metadata rules for execution tracking INSERT INTO data_quality_rules (table_name, column_name, expectation_type, expectation_kwargs) VALUES ('CUSTOMERS', 'CUST_ID', 'expect_column_values_to_not_be_null', '{}'), ('CUSTOMERS', 'AGE', 'expect_column_values_to_be_between', '{"min_value": 18, "max_value": 60}'), ('ORDERS', 'ORDER_ID', 'expect_column_values_to_not_be_null', '{}'); 2. Implementation: The Programmatic Execution Engine The core execution wrapper leverages Python and Great Expectations (gx) to programmatically turn rows of metadata into active validation suites. This script establishes a secure database connection via SQLAlchemy, harvests active constraints, generates runtime batch requests, and triggers structured checkpoints. Python import os import json import logging from datetime import datetime import pandas as pd from sqlalchemy import create_engine import great_expectations as gx from great_expectations.core.batch import RuntimeBatchRequest # Configure structured logging for production auditing logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') logger = logging.getLogger(__name__) class MetadataDataQualityEngine: def __init__(self, connection_string: str): self.engine = create_engine(connection_string) # Initialize Great Expectations ephemeral context for programmatic runtime control self.context = gx.get_context(context_root_dir=None) def fetch_active_metadata(self) -> pd.DataFrame: """Harvests active validation configurations from the centralized store.""" query = """ SELECT table_name, column_name, expectation_type, expectation_kwargs FROM data_quality_rules WHERE is_active = TRUE """ try: df = pd.read_sql(query, self.engine) logger.info(f"Successfully harvested {len(df)} active validation rules.") return df except Exception as e: logger.error(f"Failed to query metadata repository: {str(e)}") raise def compile_expectation_suite(self, table_name: str, rules_df: pd.DataFrame): """Assembles validation rules into a Great Expectations suite on the fly.""" suite_name = f"{table_name}_suite" suite = self.context.add_or_update_expectation_suite(expectation_suite_name=suite_name) # Filter metadata constraints for the specific target asset table_rules = rules_df[rules_df['table_name'] == table_name] for _, row in table_rules.iterrows(): # Parse JSON kwargs configuration gracefully kwargs = row['expectation_kwargs'] if isinstance(kwargs, str): kwargs = json.loads(kwargs) kwargs['column'] = row['column_name'] # Programmatically map string values to structured GX expectation objects expectation_config = gx.core.ExpectationConfiguration( expectation_type=row['expectation_type'], kwargs=kwargs, meta={"notes": f"Automated constraint enforcement for column: {row['column_name']}"} ) suite.add_expectation(expectation_config) self.context.add_or_update_expectation_suite(suite=suite) return suite def execute_quality_checkpoint(self, table_name: str, target_df: pd.DataFrame): """Builds a runtime batch request and evaluates data against the generated suite.""" timestamp = datetime.utcnow().strftime("%Y%m%dT%H%M%SZ") suite_name = f"{table_name}_suite" checkpoint_name = f"{table_name}_checkpoint" # Unique runtime composite signature prevents processing trace collisions batch_request = RuntimeBatchRequest( datasource_name="lakehouse_runtime_datasource", data_connector_name="runtime_data_connector", data_asset_name=f"{table_name}_{timestamp}", runtime_parameters={"batch_data": target_df}, batch_identifiers={"table_name": table_name, "execution_timestamp": timestamp} ) # Register and fire a dynamic checkpoint execution self.context.add_or_update_checkpoint( name=checkpoint_name, config_version=1, class_name="SimpleCheckpoint", validations=[{ "batch_request": batch_request, "expectation_suite_name": suite_name }] ) logger.info(f"Launching data quality checkpoint for table: {table_name}") return self.context.run_checkpoint(checkpoint_name=checkpoint_name) # Production Loop Execution Pattern if __name__ == "__main__": SF_CONN = "snowflake://<user>:<pass>@<account>/<db>/<schema>?warehouse=COMPUTE_WH&role=SYSADMIN" dq_engine = MetadataDataQualityEngine(connection_string=SF_CONN) metadata_rules = dq_engine.fetch_active_metadata() distinct_target_tables = metadata_rules['table_name'].unique() for current_table in distinct_target_tables: try: # Stage current batch dataset from target engine raw_data_df = pd.read_sql(f"SELECT * FROM {current_table}", dq_engine.engine) # Step 1: Build suite dynamically from relational rules dq_engine.compile_expectation_suite(table_name=current_table, rules_df=metadata_rules) # Step 2: Validate batch data and extract metrics payload eval_result = dq_engine.execute_quality_checkpoint(table_name=current_table, target_df=raw_data_df) if not eval_result["success"]: logger.warning(f"Data Quality anomalies detected on asset: {current_table}") else: logger.info(f"Asset {current_table} successfully cleared all metadata expectations.") except Exception as err: # Fault isolation ensures an asset failure never crashes cascading pipeline steps logger.error(f"Processing loop interrupted on asset {current_table}: {str(err)}") continue 3. Production-Grade Engineering Guardrails Building a dynamic system requires putting structural guardrails around the execution engine to prevent it from failing under enterprise pressures. Fault Isolation and Pipeline Resilience: Never let a validation failure on an upstream or non-critical business table halt your entire orchestration loop. Wrapping individual target assets in localized try-except blocks ensures that a failure on a secondary table (like CUSTOMERS) does not block downstream transactional tables (like ORDERS) from completing their validation lifecycles.Idempotency and Batching Identifiers: Every unique quality run must be traceable back to a specific moment in time to avoid overwriting or colliding results in your metadata tracking layer. Pair the table_name with an immutable execution_timestamp (such as a UTC ISO string) as a composite batch identifier. This guarantees an explicit audit trail across parallel streaming windows or backfilled data runs.Metadata-as-Code Frameworks: Treat the validation matrix table with the same operational rigor as production application code. Changes, additions, or deprecations of quality thresholds must follow a strict GitOps progression. Use schema migration version control tools (like Flyway or Liquibase) to manage, track, and deploy DML changes safely across staging and production clusters.Proactive Alerting Integration: Local HTML docs are insufficient for zero-downtime platforms. The metadata evaluation output dictionary must be integrated directly into cloud native alerting systems. Configure Webhook integrations or cloud alerting channels to route failed validation metrics directly to Slack channels or PagerDuty schedules. This ensures on-call engineers are proactively notified the moment a metric payload trends outside acceptable operational thresholds. Summary: The Architectural Impact Transitioning to a metadata-driven approach shifts data quality from a reactive "clean-up" task to an integrated, proactive engineering asset. By treating validation criteria as configurable metadata parameters rather than hardcoded script directives, architects eliminate technical debt and bridge the gap between business semantics and computing layers. This decoupled architecture provides the strict governance framework required to support high-stakes analytics and downstream machine learning layers, ensuring that every data element hitting your warehouse is automatically and transparently vetted before reaching production consumers.

Before getting into the architecture, I want to address the first objection I hear from every platform team: "We already have Kong / NGINX / AWS API Gateway — can't we just plug AI calls through that?" Short answer: no. Longer answer: it depends on what you mean by "plug in," but also still no. Traditional API gateways are stateless request routers. They handle auth, rate limiting, and load balancing on the assumption that requests are roughly uniform in cost, latency, and risk. None of those assumptions hold for LLM traffic. The core problem: A single GPT-4o completion call can cost anywhere from $0.003 to $0.40 depending on context length. It can take 200ms or 45 seconds. It might include PII your compliance team would rather not send to a third-party API. And the "correct" model to route to changes week by week as providers update pricing. Traditional gateways know nothing about any of this. What you actually need is a gateway with semantic awareness — one that understands what's being asked, not just that a request arrived. That distinction matters enormously in production. The Four Pillars of a Production AI Gateway When I talk to architects at other fintechs — and I talk to a lot of them, because everyone is quietly comparing notes right now — the pattern that keeps emerging has four core components. Not three, not seven. Four. Let me walk through each one with enough specificity to actually be useful. 1. Semantic Caching This is the one most teams skip, and it's the one that pays for everything else. Semantic caching means: before you forward a request to an LLM, compute a vector embedding of the prompt, check it against a cache of recent completions, and if a semantically similar prompt was answered recently, return the cached response. It sounds obvious. It's almost never implemented. Why? Because traditional HTTP caching on exact-match request hashes handles zero percent of LLM traffic — users phrase things differently every time. You need cosine similarity against a vector store, with a configurable similarity threshold, not a string equality check. "Semantic caching cut our GPT-4o call volume by 41% in the first week. Not because users were asking identical questions — they never do. Because they were asking equivalent questions." Our threshold ended up at 0.92 cosine similarity after a week of tuning. Below 0.88, too many semantically different questions were getting collapsed, and users noticed. Above 0.95, the cache hit rate dropped below 10%, and it wasn't worth the overhead. Your mileage will vary by domain — financial queries have a much narrower semantic space than general-purpose assistants, which makes caching significantly more effective in fintech specifically. Python # Simplified semantic cache lookup — production version adds TTL, # namespace isolation per service, and Redis cluster support async def semantic_cache_lookup( prompt: str, cache_store: VectorStore, threshold: float = 0.92 ) -> Optional[CachedCompletion]: embedding = await embed_prompt(prompt) results = await cache_store.query( vector=embedding, top_k=1, score_threshold=threshold, ) if not results: return None hit = results[0] await metrics.increment( "ai_gateway.cache_hit", tags={"service": hit.source_service, "model": hit.model} ) return hit.completion 2. Cost Attribution and Budget Enforcement This is the one that gets finance off your back. The premise is simple: every LLM call flowing through the gateway gets tagged with the originating service, team, cost center, and environment. Token counts — prompt tokens and completion tokens separately — are recorded. At the end of the month, the AI operations bill is automatically disaggregated by team. Sounds administrative. It fundamentally changes behavior. Once the fraud team sees that their experimental model evaluation accounted for 34% of last month's AI spend, they start batching their calls. Once the product team realizes that their customer-facing chat feature costs $0.0018 per conversation at current token lengths, they start thinking about response truncation. Visibility creates accountability. The gateway is where that visibility lives. Budget enforcement is the enforcement half of this. Each service gets a monthly token budget. When they hit 80%, an alert fires. When they hit 100%, calls start being routed to a cheaper model. When they hit 120%, calls are queued or rejected with a structured error that tells the engineer exactly what happened and who to contact. No surprises. No $4,200 Tuesday invoices. 3. PII Detection and Scrubbing This one is non-negotiable in regulated industries. Full stop. Sending raw customer prompts to a third-party LLM API without PII scrubbing is a GDPR Article 28 problem, a CCPA problem, and in a financial services context, a potential GLBA problem. Your legal team will discover this at the worst possible time if you don't build it into the gateway layer first. The implementation has two stages. Pre-flight scrubbing runs a named-entity recognition model against the prompt before forwarding — replacing detected PII (SSNs, account numbers, phone numbers, names in certain contexts) with structured placeholders like [ACCOUNT_NUMBER_1]. Post-flight restoration optionally rehydrates placeholders in the completion for cases where the downstream service needs the original values. The key is that nothing identifiable ever leaves your network perimeter in readable form. 4. Circuit Breakers and Intelligent Fallback OpenAI's API goes down. Not often, but it does. And when it does, every service that's calling it directly fails simultaneously, visibly, and often in ways that produce thoroughly confusing error messages to end users ("Something went wrong" when the real issue is a 503 from a third-party API your customer has never heard of). The AI gateway implements circuit breakers at the provider level. When error rates from a given provider exceed a threshold — we use 15% over a 60-second window — the circuit opens and traffic is automatically rerouted to the fallback provider chain. For us that looks like: GPT-4o → Claude Sonnet → Gemini Pro → local Llama 3.3 deployment, in that order of preference. Each model in the chain has a defined capability tier, so the gateway can make routing decisions based on task complexity, not just availability. Build vs. Buy: The Honest Accounting You have three options here. Build it yourself, use an open-source gateway (LiteLLM, PortKey, Traefik AI), or buy a managed solution (Apigee AI extensions, AWS Bedrock Gateway, Kong AI Gateway). I've done all three. Here's what I learned. My honest opinion: start with LiteLLM behind a thin wrapper you control, and plan a migration path to a fully owned solution if your compliance requirements tighten — which in financial services, they will. The trap is trying to build everything custom on day one. You will spend six months building infrastructure instead of shipping features, and by the time you're done, three better open-source options will have appeared. The Metrics That Actually Matter in Production Every observability vendor will try to sell you fifty dashboards. The AI gateway team at a major payments processor I've advised runs on six numbers. These six numbers. If they're green, everything is fine. If one turns red, you know exactly where to look. What "Quietly Standardizing" Actually Means I want to be precise about the headline here, because I've seen it misread. When I say JPMorgan and Stripe are standardizing on this pattern, I don't mean they've published a spec you can download. I mean: engineers who've left those organizations are showing up at mid-size fintechs and immediately building AI gateways, because that's what they built at their last job. The pattern is diffusing through engineering talent, not through documentation. JPMorgan's LLM COE — their internal Center of Excellence for AI — has been running something functionally identical to this architecture since at least early 2025, according to multiple engineers who presented at FinTech DevCon. They call it their "AI traffic control layer." The components are the same: centralized routing, semantic cache, PII scrubbing pipeline, cost ledger per business unit. How to Get From Here to There Without a Rewrite The migration question is always: how do we adopt this pattern when seventeen services are already calling OpenAI directly, and we have zero appetite for a multi-month refactor? The answer is DNS. Specifically: deploy your gateway, update your internal DNS to resolve api.openai.com to your gateway IP, and configure the gateway to proxy through to OpenAI by default. From day one, all your existing services are routing through the gateway with zero code changes. You get visibility immediately. Then, service by service, you opt into semantic caching, PII scrubbing, and cost attribution at whatever pace your team can manage. We did this migration in four weeks with a team of three. Week one: deploy the gateway, enable DNS redirect, establish baseline observability. Week two: enable cost attribution tagging — this required adding a service identifier header to each client, which was a one-liner change per service. Week three: PII scrubbing in logging mode (detect but don't block, so you can tune the entity model without breaking anything). Week four: enable semantic caching, tune the similarity threshold, deploy budget enforcement in warning-only mode. The Tradeoffs Nobody Mentions I want to be honest about where this pattern has real costs, because the breathless "AI gateway will solve everything" takes that have appeared over the past year are exhausting to read. Latency. The gateway adds overhead. Our p50 overhead is about 12ms; p99 is 28ms. For customer-facing real-time applications, that matters. If you're building a trading platform where sub-10ms matters, the centralized gateway pattern may not be the right call for your latency-critical paths. Build a hybrid — gateway for asynchronous workloads, direct for ultra-low-latency paths, strict manual governance for the latter. Semantic cache consistency. A 0.92 cosine similarity threshold means you'll occasionally return a cached response that's slightly wrong for a slightly different question. We've seen this cause issues in dynamic financial contexts — "What's the risk on my open AAPL position?" at 9:30am and at 3:30pm are semantically similar but factually require different answers. Cache TTLs and domain-specific exclusion lists are your mitigation here, but they require ongoing tuning. This is not a set-it-and-forget-it component. Single point of failure. Yes, the gateway is a SPOF. This is why you deploy it across multiple availability zones with automatic failover, health checks that your load balancer actually uses, and a documented break-glass procedure for direct LLM access if the gateway cluster fails entirely. Treat it like your auth service: make it reliable enough that SPOF isn't actually the risk it sounds like.

By Dinesh Elumalai CORE

When someone uploads an image to your application, it might look perfectly fine at first glance. It might open correctly, pass file validation, and avoid triggering any obvious red flags. But that still doesn’t necessarily mean the file is trustworthy. In modern systems, especially marketplaces, identity verification flows, insurance submissions, academic portals, and editorial pipelines, how an image was created can matter just as much as what that image shows. A synthetic, AI-generated image can be technically valid and "safe" while still being completely inappropriate for the context in which it’s used. AI image detection offers your system a way to make better decisions about the content it accepts. It's not a final authority; rather, it's a form of content moderation that ensures you have clear visibility into and governance over the content you process and share. In this article, we’ll walk through why AI detection is challenging, how to design a practical workflow around it, and how to implement AI image analysis in C# using an image recognition API. Why AI Image Detection is Difficult You might've noticed that the quality of AI-generated images has improved dramatically in recent years. The early giveaways (extra fingers, warped text, strange lighting, etc.) are becoming less common. If you ask strangers on the street to pick out AI-generated images from a lineup of otherwise authentic photos, you're unlikely to get a consistent answer. That's because today's generative models are designed to mimic real-world photography and illustration patterns. That means even a perfectly normal-looking image might still be synthetic, and a slightly odd-looking image might still be completely real. This creates a considerable challenge. We live in a world where there's no single visual feature that reliably, reproducibly separates real from synthetically generated content. A bit unnerving, right? Metadata doesn’t always solve the problem either. While images can contain useful provenance information, that often gets stripped out during editing, compression, or platform uploads. Even something as simple as re-saving an image can remove the original creation context entirely. And of course, it's trivial to change file names without affecting the underlying pixels, so you're unlikely to catch anyone red-handed with an AI platform staring you in the face. Because of this, modern AI detection systems rely instead on probabilistic models. Instead of saying “this image is AI-generated,” they try to estimate how likely that is to be the case based on learned patterns from large datasets of real and synthetic images. There's an important distinction to make here: in AI detection, we’re looking for confidence rather than absolute truth. If we want to benefit from AI detection services, it's essential that we embrace their uncertainty. Here's what that means in practice: high confidence scores justify content review or restriction, mid-range scores indicate uncertainty, and low scores reduce concern but do not guarantee authenticity. AI detection works best as one layer in a broader content validation strategy. Designing a Practical Detection Workflow Before thinking about how to invoke an AI detection service, which we'll look at later in this article, it’s useful to step back and consider where it fits within a broader image-handling workflow. In practice, AI detection is just one stage in a normal pipeline that begins the moment a user submits an image. As we'll see in our demonstration later on, the service handling AI detection might be exposed as an API, but it could just as easily be a background job, a message queue consumer, or even an internal library call if you're ambitious about building internal tools. The important idea isn't how you invoke that service, but when and why it gets called in your system. A typical content upload workflow starts with basic validation. That means checking whether a file is present and readable, whether the format is supported (such as JPEG or PNG), whether the file size falls within acceptable limits, and whether the actual file type matches its extension. These checks are important because they protect your system from malformed or malicious inputs before any deeper analysis happens. That's not unique to the AI detection topic, of course, but it's critical nonetheless. Once a file is validated, the next step is normalization. In this case, the idea isn't to alter the image content; rather, it's to standardize how the image is handled as it moves through the system. The goal here is to maintain consistency regardless of how the detection service is implemented. This includes keeping the original image data intact, passing it through a stream, buffer, or temporary storage layer, and ensuring the data is correctly positioned and accessible for downstream processing. This matters because even small transformations like resizing or re-encoding can change the very signals you’re trying to analyze. So, in most analysis workflows (that is, pipelines that prioritize preserving signal integrity), you should defer any modification unless it's explicitly required. At this point, the image is finally ready to be evaluated by an AI detection system. This evaluation can be triggered through your existing processing pipeline, using whichever execution model your system is already built around. Regardless of the mechanism, the role of the detection step is the same: produce a structured assessment of whether the image is likely to be AI-generated or manipulated. From there, your application can apply its own business rules. There isn't a "one size fits all" way to do this: the exact thresholds for AI detection should always reflect your use case. For example, a social media avatar and a legal document shouldn't necessarily be treated the same way; there's a bit more at stake if the latter is fabricated. Where AI Image Detection Fits in an Application AI image detection should run as early as possible in your content ingestion pipeline. That means at upload, submission, or intake; sometime before content is trusted or passed downstream. There are two general scanning approaches here that make sense in different contexts: synchronous and asynchronous detection. In synchronous detection, the system scans content right away and waits for a result before continuing. This approach is simple and provides immediate feedback, but it does add latency to the whole workflow. It’s usually best for controlled flows where users expect to get instant validation. In asynchronous detection, images are accepted first and then analyzed in the background. This approach generally scales better, and it avoids blocking users, which makes it the ideal choice for high-volume (and especially non-urgent) workflows. Most enterprise-scale workflows will probably be asynchronous. Ultimately, the core rule for both approaches is the same: don’t trust the image until it's been thoroughly evaluated. Detecting AI-Generated Images With C# Now that we've covered some of the biggest factors involved in detecting AI-generated images, we'll go ahead and explore one way to implement this functionality in C#. In this example, we'll use an image recognition API. The SDK boils the process down to two steps: submit an image and receive a structured detection result. If you’re considering other options for AI image detection, you might want to look into Hive AI Detector alternatives, CLIP-based classifiers, or locally hosted models from Hugging Face. First, we install the package: C# Install-Package Cloudmersive.APIClient.NETCore.ImageRecognition -Version 2.2.0 Next, we import the required namespaces: C# using System; using System.IO; using Cloudmersive.APIClient.NETCore.ImageRecognition.Api; using Cloudmersive.APIClient.NETCore.ImageRecognition.Client; using Cloudmersive.APIClient.NETCore.ImageRecognition.Model; Now we configure the API key and prepare the image stream: C# var configuration = new Configuration(); configuration.AddApiKey("Apikey", "YOUR_API_KEY"); var apiInstance = new AiImageDetectionApi(configuration); Now we can open the image stream and call the detection endpoint: C# try { using (var imageFile = new FileStream( @"C:\temp\input-image.png", FileMode.Open, FileAccess.Read)) { ImageAiDetectionResult result = apiInstance.AiImageDetectionDetectFile(imageFile); if (result == null || !result.AiGeneratedRiskScore.HasValue) { Console.WriteLine( "The image could not be conclusively evaluated."); } else { Console.WriteLine( $"Clean result: " + $"{result.CleanResult?.ToString() ?? "Unknown"}"); Console.WriteLine( $"AI risk score: " + $"{result.AiGeneratedRiskScore.Value}"); Console.WriteLine( $"Possible AI source: " + $"{result.AiSource ?? "Unknown"}"); } } } catch (Exception e) { Console.Error.WriteLine( "Exception when calling " + "AiImageDetectionApi.AiImageDetectionDetectFile: " + e.Message); } This example keeps error handling simple for clarity, but in a production system you’ll obviously want more granular handling for scenarios like invalid input, network failures, API rate limits, etc. Note that a failed detection should never silently pass the image through. It should instead result in a clear “unverified” or “pending review” state (or something similar). Interpreting the Detection Result You get three response fields: CleanResult is a quick yes/no check. true means no AI-generated content was detected; false means there might be a match. This result is based on the risk score. AiGeneratedRiskScore runs from 0.0 to 1.0. Higher scores mean a higher chance the content was AI-generated. Scores above 0.8 are high risk and trigger CleanResult: false. AiSource is the final field, and it may show which specific AI content generation tool likely generated the content. It’s intended to be useful context, but it won’t always be available, so it obviously shouldn't be relied on. Together, these give you a quick result, a risk score, and optional context. None of them is absolute proof, but they can help you make a more informed decision. Turning the Result into an Application Decision Once you have a risk score, you can map it to a simple decision model (that's what I would do). Here’s one quick example of that: C# public enum ImageDecision { Accept, ManualReview, Reject, Unverified } public static ImageDecision EvaluateImage( ImageAiDetectionResult result) { if (result == null || !result.AiGeneratedRiskScore.HasValue) { return ImageDecision.Unverified; } double riskScore = result.AiGeneratedRiskScore.Value; if (riskScore > 0.8) { return ImageDecision.Reject; } else if (riskScore > 0.5) { return ImageDecision.ManualReview; } else { return ImageDecision.Accept; } } This structure is (intentionally) simple, but the meaning behind each outcome is flexible. For example, in a lot of real-world systems, “Reject” might actually mean "hold for review". “ManualReview” might "trigger a human workflow", and “Accept” might still be logged for auditing. It's also worth noting that in this example code, we aren't directly using the CleanResult response when making the decision. We are bypassing that completely, only using the risk score from the broader detection result. If you wanted to, you could inspect CleanResult as part of your application logic; for example, to distinguish between a clean result, a flagged result, or an inconclusive response. You could then use that information alongside the risk score when deciding whether to accept, review, or reject an image. Conclusion AI image detection doesn’t give you certainty, but it does give you something extremely valuable in today's world of increasingly indistinguishable AI content: a structured way to reason about uncertainty. By combining file validation, careful input handling, and probabilistic AI detection, you can build workflows that are both practical and resilient. In C#, integrating an image recognition API gives you a straightforward way to evaluate images at the point of entry, interpret risk scores flexibly, and apply consistent business rules without over-relying on one individual signal. The API approach makes sense because it keeps the recognition logic focused, reusable, and easier to update as models and requirements change, while allowing the rest of the application to work with a clear, stable interface. The key takeaway is ultimately pretty simple: AI detection is necessary for modern content systems, but it should be treated as guidance rather than a judgment. When used judiciously, it will become a powerful part of a broader trust and verification strategy.

By Brian O'Neill CORE

The Moment It Gets Real At some point in the last year, every data engineer had the same experience. You opened a copilot tool, typed a rough description of what you needed, and watched it generate a working ETL pipeline in about thirty seconds. Not a skeleton. Not pseudocode. Actual, runnable PySpark with joins, transformations, and a DAG scaffold. And for a moment, the question that the industry had been treating as hypothetical became very concrete: if AI can do this, what exactly am I here for? That question deserves a serious answer — not the dismissive "AI is just a tool" reassurance, and not the catastrophist "engineers are obsolete" take. The honest answer is more nuanced, more interesting, and more actionable than either of those. What AI Can Actually Do Today Let's be precise about what has changed, because the hype runs in both directions. AI copilots in 2026 are genuinely impressive at a specific class of data engineering tasks. Give a well-prompted model a schema and a business requirement, and it will produce SQL that would have taken a competent engineer thirty minutes to write. Ask it to scaffold a dbt model with tests and documentation, and it delivers something you can actually work from. Point it at a slow query and ask for optimization suggestions, and it identifies the right indexes and join strategies most of the time. The work that once defined the day-to-day of data engineering — writing transformations, building pipeline boilerplate, generating unit tests, documenting schemas — is now legitimately acceleratable by an order of magnitude. That compression is real. A pipeline that took a week to build from scratch now takes a day. A day's worth of dbt model work now takes a morning. The cycle time has collapsed, and pretending otherwise is not a useful position. But Would You Actually Deploy It? Here is where the honest conversation has to happen. AI generates code that looks production-ready. It compiles. The DAG runs. The transformations return the right rows on the test dataset. And then you look closer. There are no retry semantics. There is no idempotency guarantee — run it twice, and you get duplicates. There are no data quality checks, no row count assertions, no schema drift detection. Observability is absent. The error handling catches exceptions and logs them to nowhere. Governance controls do not exist because the model has no idea what your data classification policies are. The code is impressively correct at the logic layer and completely unprepared for production reality. And that gap — between "AI generated it" and "it is actually deployable" — is not a small gap. It represents most of what makes data engineering genuinely hard. This is not a criticism of AI tooling. It is a precise description of where the boundary currently sits. And that boundary is exactly where the value of a skilled data engineer now concentrates. The Three-Bucket Reality Not all data engineering work is equally automatable, and the honest framework is to split it into three categories based on where AI sits today. What AI handles well. SQL and transformation generation, dbt model scaffolding, unit test generation, schema documentation, query explanation, code refactoring, and first-draft pipeline boilerplate. These tasks are high-volume, pattern-heavy, and well-represented in training data. AI performs them at a level that meets or exceeds what most engineers produce under time pressure. What AI assists but cannot own. Pipeline architecture decisions, root cause analysis on production failures, performance tuning for complex distributed jobs, and data modeling judgment for novel domains. AI is genuinely useful here as a thought partner and accelerant, but the decisions require context, business knowledge, and judgment that models do not reliably carry. What remains fundamentally human. Trade-off evaluation with real organizational constraints, governance and compliance decisions, architecture choices with long-term consequences, and anything requiring accountability. These require not just the right answer but the right answer for this company, this data, this regulatory environment, this team. That is irreducibly human work. The critical observation is that the boundary between these buckets is not static. Tasks that sat in the second bucket eighteen months ago have migrated into the first. The direction of travel is clear. Engineers who have concentrated their value entirely in automatable work are already exposed. Engineers who have built depth in judgment, architecture, and systems thinking are in an increasingly strong position. The Workflow Has Already Changed The before and after is not theoretical. It is visible in how high-performing data engineering teams actually operate today. The traditional workflow moved linearly through extraction, transformation, loading, and serving — each stage measured in hours to days, the full cycle measured in weeks. It was plagued by boilerplate, manual testing, documentation that was always out of date, and context-switching that fragmented deep work. The AI-enhanced workflow runs the same stages but with a fundamentally different time signature. StageTraditionalAI-EnhancedExtractHours — manual SQL, custom connectorsMinutes — AI-generated queries, auto connectorsTransformDays — dbt models, Spark jobsHours — AI-assisted modeling, auto schema detectionLoadHours — DAG authoring, schedulingMinutes — auto DAG generation, smart schedulingServeDays — dashboard building, documentationHours — auto documentation, natural language query The total cycle time compresses from weeks to days. That compression does not come from removing the engineer. It comes from removing the repetitive execution work so the engineer can focus on the decisions that actually require human judgment. What the Collaboration Actually Looks Like The AI-native data engineer workflow is not "prompt and deploy." It is a structured collaboration with a clear division of responsibility. AI accelerates the build. The engineer ensures it is correct, reliable, observable, and production-ready. The accountability for what ships belongs to the engineer, not the model. That accountability is not a burden — it is the source of professional value. The engineers who treat AI output as a draft to be critically evaluated and hardened will consistently outperform those who either ignore the tools entirely or treat generated code as finished work. Both of those failure modes are common. Neither is sustainable. The Skill Set Reorganizes, Not Disappears The skills required to be an excellent data engineer are shifting, but they are not evaporating. They are reorganizing around three pillars. Technical depth now centers on evaluating AI-generated code rather than writing all code from scratch. This requires strong fundamentals — you cannot spot the subtle join fanout in AI-generated SQL if you do not understand join semantics. It also means investing in observability, reliability engineering, and prompt crafting as first-class technical skills. A well-constructed prompt that produces deployable output in one iteration is genuinely more valuable than the ability to write the same code manually from scratch. Systems thinking becomes the primary differentiator. Architecture decisions, data modeling judgment, trade-off evaluation, and problem framing are tasks that compound in value as AI handles more execution work. The engineer who can look at a generated pipeline and immediately identify the three ways it will fail at scale is providing something no current model reliably provides. Engineering leadership expands to include guiding AI usage within a team, establishing review standards for AI-generated code, owning governance controls, and setting the quality bar that separates production-ready from impressive-looking. This is not a soft skill add-on — it is a core engineering responsibility in an environment where the output volume of any individual engineer has increased dramatically. The role is shifting from execution to judgment. That is an upgrade, not a downgrade, for engineers willing to make the transition deliberately. How to Actually Evolve The path forward is concrete, not abstract. Start by integrating AI into your daily work right now — not as an experiment but as a workflow change. Use it for SQL drafting, pipeline scaffolding, and test generation. Build the muscle of critically evaluating what it produces. Develop prompting habits that consistently get you to a usable first draft rather than something you have to rewrite from scratch. Level up by investing deliberately in the areas AI does not cover well. System design. Distributed systems fundamentals. Reliability and observability patterns. Data modeling for complex domains. These skills appreciate in value as AI handles more of the execution layer — the relative scarcity of strong systems thinkers increases as the supply of generated boilerplate becomes effectively infinite. Lead by taking ownership of AI quality standards on your team. Be the person who defines what "production-ready" means for AI-generated pipelines, who establishes review checklists, who sets governance guardrails. This is influence that compounds over time and is not replicable by a model. The Honest Bottom Line AI will not replace data engineers. But data engineers who treat their value as residing primarily in writing code — rather than in the judgment, architecture, and reliability thinking that makes code worth deploying — are taking a position that becomes harder to defend with each model release. The opportunity is real, and it is now. The engineers who learn to work with AI as a genuine collaborator, who develop the critical evaluation skills to close the gap between generated and production-ready, and who invest in the systems thinking that AI cannot replicate — those engineers are not threatened by this transition. They are the ones who define what data engineering looks like on the other side of it. Evolve deliberately. The alternative is not standing still — it is falling behind at an accelerating rate.

By Janani Annur Thiruvengadam CORE

Hi everyone! This is Mikhail Polivakha, tech lead of the Axelix project (btw, give us a star!). In my experience consulting teams that build enterprise applications, I keep getting asked: What about natural keys in the database? Say I have a column that lets me explicitly identify a record, should I use it as the Primary Key? And over my years of designing enterprise systems, and over the time spent designing Axelix, I've come to a conclusion: just never use natural keys, ever. When you feel the urge to do it, step outside, take a walk, get some fresh air, and it'll pass. I understand this answer is categorical, so I'll add a couple of clarifications about what to do if you do happen to have a unique discriminating column that, as it seems to you, lets you uniquely identify a record in a database table. The full, nuanced answer is of course more complicated, but if I have to give you a straight TL;DR: When designing new systems, in my opinion, you should always use surrogate primary keys. Now let's get into why I think so. Rules Written in Blood Rules like the one above are usually born out of getting burned on real projects several times. I have an absolutely perfect story straight from Open Source Axelix. The source code is on GitHub, so if you feel like it, go and check for yourself. I won't go too deep into the details, but so that you grasp the depth of the problem, I'll give you a bit of context. In some places I'll also deliberately simplify the parts I consider non-essential to understanding the problem. At its core, Axelix consists of two components. The first is Master, a standalone application that acts as the "brain" of the system. It's deployed either in a K8S cluster, or launched as a separate Docker container, or even just run as a plain JAR. Master aggregates information from your Spring Boot services and stores it in its database. This information is later used to understand the "maturity" of your ecosystem, the distribution of versions of key components (for example, Spring Boot or Java versions), tracking known tech-debt issues, and so on. Axelix Architecture If we picture a typical company, they usually have a K8S/OpenShift cluster where their production runs. Almost always, a given application is deployed to production not as a single copy, but as a set of Instances (a K8S Deployment + a configured HPA, and so on). So in practice we have one logical application that is physically a set of different containers. Now I think we have enough context to discuss the problem. The Beginning. Natural Keys: Sure, Why Not! As I said, Axelix stores data in its database to understand the overall state of your application. Let's call this table "Application" (in reality, this abstraction is named differently in Axelix, but again, I'm simplifying heavily). This is where application-level data lives. Master can collect data from Spring Boot microservices via both a push and a pull model, but regardless of the model, it collects data at the Instance level, not at the Application level, i.e., not for the whole application. So Master polls each Instance, and it's then Master's job to somehow figure out that all those Instances belong to the same application. Understanding Instances The question is: How is Master supposed to do that? How does it figure out that these Instances belong to the same application? (Don't forget: Axelix isn't always deployed in K8S. Relying on ClusterIP services and the like is not an option.) Actually, if you think about it a little, the solution is right on the surface: we can just aggregate information at the level of the GroupID/ArtifactID pair from the GAV coordinates (the standard format of a Maven distribution). After all, all the Instances are required to have the same GroupID/ArtifactID, right? Aggregating Instances Information Broadly speaking, yes, that's true. Some might think we could key off other things, for example spring.application.name or similar, but unfortunately that won't work, for a number of reasons. That's another story, though, and it's not important right now. So imagine we're designing such a relation in the database. Here's my question for you: what primary key would you want for a table like this? When we designed the "Application" entity, it seemed right to make the {groupId/artifactId} pair the primary key, i.e., a Natural Composite Key. And it's so convenient! When information about some Instance arrives in Axelix Master (whether via the push or the pull model): We can update the data with a simple ANSI SQL MERGE or INSERT ... ON CONFLICT DO ..., because artifactId/groupId is the primary key! Spring Data JDBC (which we use as the ORM in Axelix Master) in 4.1 finally learned how to do UPSERTs on the primary key, and now we can just do this via JdbcAggregateTemplate: Java @Transactional public void reloadCurrentState(BasicRegistrationMetadata metadata) { Application application = converter.currentSnapshot(metadata); jdbcAggregateTemplate.upsert(application); } And how nicely it works out for the front-end! And here we arrive at the fact that a natural key carries business meaning by itself! That, by the way, is one of the genuinely nice properties of natural keys. What do I mean? For example, in a situation where we just need to display the name of our "Application", and the name alone is enough, we can use the artifactId, i.e., a part of the composite natural key. No need to "fetch anything extra", and so on. So where's the problem? Given everything I've said so far, is this problem really so critical that I claim you shouldn't use natural keys at all? Yes, it's that serious. And here's why. So What's the Deal? A Bit of Philosophy The older a person gets, the more prone they are to doubting various things (for example, my claim in this article! And that's okay!). This is because people accumulate experience. People who have been doing engineering for a good while accumulate experience and come to understand just how much everything changes, and how much they still don't know (experienced engineers understand me 100% right now). A vendor comes and goes. So does an employee. The uniqueness of a natural key... Ray Dalio (an amazing person and macro investor, I highly recommend reading him) wrote in his book "Principles:" Sincerely believe that you might not know the best possible path and recognize that your ability to deal well with "not knowing" is more important than whatever it is you do know. This is incredible wisdom. The idea is to accept the fact that your knowledge of the outside world is limited, and it will always be many times smaller than the set of things you don't know but which nonetheless affect your life/system/etc. And the most important thing in such a situation is to be able to work WITH YOUR OWN NOT-KNOWING of something, to hedge risks. How does this relate to natural keys? Very simply: if some discriminator seems like an obvious key in the moment, just remember that the scope of your knowledge is incomparably small next to what you don't know. And that "invariant" you're pinning your hopes on, the one you think will be unique: it can very easily stop being unique half a year later. What's more, the scope of your knowledge will keep growing. Over time, you (yes, you, my friend) grow as an engineer. After a while, you'll look at this code, or at the design of this system, and say: How on earth!? How could I have done this? This crap is just awful; it was obvious this key would break uniqueness in case X! And it'll be obvious to you. But later. When you become wiser. By the way, if you don't have these moments of "enlightenment" in your career, where you scold yourself for your own past decisions, that's a very strong warning sign that you've stopped growing as a specialist. Back to Engineering Let's get back a bit closer to the technical side. The main point of the previous section is that what seems like the uniqueness of a natural key today can easily stop being unique later. Now let's think like engineers: how bad is that, really? How bad is it that we'll be wrong about our natural key (composite or not, doesn't matter right now) turning out not to be unique? The truth is that a record's primary key must always (!) have (among others) the following two distinguishing properties: 1. It Must Be Immutable When we assign a record some key by which we identify it, we then have no right to change it. Why? Because the outside world that depends on our system stores exactly this ID, this primary key, to identify the record. It stores a reference, not the record itself. For example, imagine you have a third-party service that stores user profiles: user-service. And there they decided to use email as the natural key. You write a service that orchestrates users' subscriptions to various services within the ecosystem. And now you need to fetch a user's profile from that user-service system for your operations. How will you fetch it? By email, of course! It's the "unique key", after all. And now imagine that the Product Owner comes along and says: In our service we want to let a user change the email tied to their account. That is, effectively, an already-existing record in the database will have its identity changed. By changing a record's ID in this user-service, any other system, including yours, can no longer find the profile it needs. That would be a mass incident. That's why an ID must always be immutable. 2. It Must Uniquely Identify a Record at Any Moment in Time Now imagine that, all of a sudden, the folks from the team that develops user-service get a requirement. They're told: Hey, we sometimes run into a situation where a user once created an account and tied their email to it. And now they want to somehow delete the old account (which they created some 10 years ago) and create a new one, and attach the same email to it. We wouldn't want to delete the old account (in enterprise, for various reasons, hard deletes are rarely done). So, shall we do it? Here the problem is even more obvious. Not only can your system, which depends on user-service, no longer find the right user profile (there could be several of them now!), all existing contracts break, and to "fix" them you'll have to "re-define" the ID (it's no longer unique, and you can't rely on the ID alone anymore). And if we have to change the ID, then see the section above. Cause of Death: Natural ID Mistakes come in varying degrees of severity. There are mistakes that have a local effect and can be fixed relatively quickly and easily. But keys that identify data in distributed systems are something that spreads across the entire distributed system into its most varied corners. So the moment you suddenly realize with horror that the natural id is no longer unique, the so-called blast radius will be fantastic, especially in modern microservice architecture. So, friends, people die of different causes. Someone died of cancer, someone died of heart failure. And someone simply chose a Natural ID as their primary key, and then received an email in their inbox, or suddenly heard at a daily standup that the uniqueness assumption of this key was about to be shaken. I suggest a moment of silence before reading on, in memory of those engineers who paid the price for choosing Natural ID as their primary key… Thank you. The Axelix Case Let's get back to the real case we had at Axelix. We haven't hit GA yet (we're actively working on it), but we already have several Milestone releases. We embed with various companies to gather feedback, potential bugs, problems, and so on. And one company tells us: You know, it just so happens that we essentially have two services: service A and service B. They're basically identical, just deployed in different network segments. They have the same groupId and artifactId. Nevertheless, service A is maintained by this team, and service B by that team. I've simplified all the details, but this is the general message. So, we have a problem in this case - we can no longer identify an application the way we wanted, via the artifactId/groupId pair. This is exactly what typically happens after a while, when the system is already deployed in production. Remember Ray Dalio! ... What exists within the area of "not knowing" is so much greater and more exciting than anything any one of us knows. It's precisely because of situations like this that you need to ask users to provide Axelix with the information about what the unique ID of a given application is themselves (for example, in application.yaml). But Natural IDs Do Have Advantages... In my experience, the fact that a Natural ID carries business meaning that can be used somewhere (for example, displaying an application's name on the UI as the artifactId, as I already showed with the Axelix example) is solved simply by designing your API. In other words, even with surrogate keys, you can design your API so that you don't have to fetch extra data from the backend; it's not a big problem (for example, get some metadata, put it into the state manager on the front-end, and so on; there are plenty of ways). What's really important about natural keys is that they force you to think about the invariants of your data. That is, for example, logically, if your email is unique, then it makes sense to create an index on it (which is, for instance, what Postgres does when you ask it to create a Primary Key). And to avoid having two different indexes, why not make email the primary key, since in that case there would be just one index, only on email? That's a broadly valid argument, but I'll put it this way: it's not worth it. If you don't use email as a Natural ID, then whether or not to create a unique index on email is a decision to make case by case. I'd say that for 95%+ of cases the answer is definitely yes, and there won't be any problems with it. That said, for large write-heavy systems with a lot of data, this may create a certain overhead, but again, usually negligible at the scale of the system. And finally, regarding MERGE / INSERT ON CONFLICT operations. You can perfectly well do them not on the primary key, but on any constraint, for example, on a UNIQUE constraint that you explicitly define in a migration. Conclusions Based on my experience, I can tell you one thing: remember that the scope of your not-knowing is by nature far larger than the scope of your "knowing". That's why it's very dangerous to build an assumption that a Natural ID, which seems unique to you for a given record in the moment, will make a good Primary Key. That said, it's worth acknowledging that the main advantage of a Natural ID is that it forces you to think about what invariants your data has in general. And these invariants should give you insights into how to model your data access and storage patterns, for example, defining unique b+tree indexes for the email column. Remember: indexes and things like that can later be removed without consequences for the whole system. Changing primary keys, on the other hand, is a dead end.

A simple access check uncovered something alarming: several dashboards still showed employee compensation based on an organizational hierarchy that was no longer relevant. Our row-level security framework stopped synchronizing because of a Workday HCM API timeout issue, but meanwhile, the entitlements on those dashboards did not get adjusted to reflect the new organization setup. No permission changes were made. Despite changes in the hierarchy, the access layer was unable to adapt to the changes. This is the reason why this project became necessary. First of all, we completely redesigned the access layer that sits behind all BI tools, Adaptive Planning models, and Snowflake shares used by our FP&A and risk departments. It was done not using any static table with roles, but with the help of tracking constantly changing hierarchies of organizations, cost centers, legal entities, and products that include deal closures and divisions' separations. Let me explain how it was accomplished: first, I will describe the process of keeping hierarchies up-to-date in the ingestion layer; then I will explain the visibility engine and its policies of providing access at a row level. Finally, I will show how to keep access scopes the same in Power BI, Tableau, and Adaptive Planning. Overview The system obtains hierarchical information from Workday HCM, Salesforce, and the internal table of the legal entities in order to reconcile these three conflicting lists into a single tagged entity graph. Visibility between users and the entities is calculated upon any change of the hierarchy edges, and not after a periodic re-access review as in previous attempts. The visibility enforcement occurs on the Snowflake row level, guaranteeing that Power BI, Tableau, and custom SQL queries obtain the same limited results set, which helps avoid discrepancies because of individual permission tables per tool. Consistent entity scope is provided for the Workday Adaptive Planning security groups; as a result, there will be no mismatches between allowed viewers of the planning sheets and the dashboards. Orphan nodes in the hierarchy are identified before their contribution to visibility gaps appears. Components The framework is made up of four main parts: hierarchy ingestion layer, dynamic security mapping engine, Snowflake row-level access control, and synchronization layer that relays outcomes to BI and planning software applications. These parts produced practical insights during difficult experiential learning. Hierarchy Ingestion Layer Four sources have been used for this hierarchical workflow. They include Workday HCM organizational hierarchy and cost centers hierarchy, location level hierarchy that groups individual branches/plants/facilities according to regions, account hierarchy of Salesforce accessed using the Bulk API and a legal entities reference table maintained manually in Snowflake. The four hierarchical sources have been transformed into a single closure table having fields as entity_id, parent_id, hierarchy_type, and effective_date. While one of the first considerations was to use only the organizational hierarchy in Workday HCM, the reason was mainly because of its completeness. But it is simply impossible to rely on just this hierarchy because there could be instances where a single cost center reports itself to two legal entities due to allocation of services. Additionally, the location-level hierarchy of any branch does not have anything to do with the organizational hierarchy position of its manager – it could be a branch within one region reporting to a manager in another region. So, maintaining each hierarchy separately and ensuring that configurations are retained helps. One of the difficult aspects of this entire process was the fact that a complete extract of the Workday HCM hierarchy was extremely slow, such that it caused issues with the synchronization to adaptive planning. To avoid this problem, it was necessary to implement change detection instead. Dynamic Security Mapping Engine This process goes through the closure table starting at each user’s home node and translates all the descendant entity_ids into a flatter structure of USER_ENTITY_ACCESS, along with the effective dates. Key to the design is the fact that a user’s home node is not a simple identifier but a collection of (user, hierarchy_type, home_node). Thus, the same user can have a User + Location Hierarchy as well as a User + Organization Hierarchy, each handled in the same fashion. Access was previously managed through manually maintained grants in Snowflake for each business unit. That worked fine until some organizational change would occur, at which time many grants had to be updated. Moving to the recursive resolution from a single source-of-truth hierarchy allowed this to become unnecessary; there is no grant to maintain when a cost center moves. We found that the effect of one edge change could be more far-reaching than expected. As an example, when a cost center moved to a different regional vice president, the map engine would do a traversal of the closure table starting at this node and then down the whole chain and then write out all the affected USER_ENTITY_ACCESS entries for anyone who mapped back to this node. In one typical move, over two thousand downstream entries in the USER_ENTITY_ACCESS table were affected by changing just one edge in the hierarchy. To solve this problem, the original map engine did a full recalculation of all the accesses in the table for each pass through the process. This was a brute force solution but one that put enough of a strain on the system that it locked up the table in business hours. The second map engine solves this issue by updating only the affected subtree. Foe example, one engine solves any hierarchy, not just one. Think about a regional operations manager. Their User + Location Hierarchy assignment specifies the regional level home node, meaning that all locations below that region within the location hierarchy become part of their assignment – all branches and facilities belonging to that region. The User + Org Hierarchy assignment is a distinct and separate home node, further down the chain: exactly their own team and subordinates within it because their responsibility is not over all employees within that region but those reporting to them directly. Both assignments live in the same USER_ROLE_ASSIGNMENT table, distinguished only by hierarchy_type. They are both solved by the very same recursive procedure. Start from the home node, go over all descendants in that hierarchy chain within the closure table, and write into the USER_ENTITY_ACCESS table. This technique also allows modeling of hierarchies that do not exist yet – say, Product Line hierarchy – because we simply add another hierarchy type to the closure table, specify a home node for it, and the same engine will recognize it on next runs. Snowflake Row-Access Enforcement In Snowflake, a policy is added on the fact tables: GL detail, planning actuals, and HR costs, joining them against USER_ENTITY_ACCESS by the current session’s user ID mapped into the tenant. Hence, irrespective of who queries the data using what software tool, the result set will be automatically filtered for authorized rows only. Another option considered included creating a secure view per each business unit, running into the centralized control plane problem described by GFT's Azure Synapse Analytics – New Insights Into Data Security on DZone. The view-per-unit solution would not scale above dozens of units and required having a different Power BI dataset for each view. Adding a single row-access policy allowed setting up proper restrictions at the table level without making any changes in consumer applications. The design principle used for access restriction at runtime based on an explicit row filter by identity context and not through application-layer WHERE clauses is not exclusive to Snowflake. Another example of implementing similar logic in PostgreSQL by means of its native row-level security and session-wide tenant identifier can be found in Multi-Tenant Data Isolation and Row Level Security on DZone. Even though the approach differs from a closure table self-join, the idea behind it is the same: enforce data security in the database itself, not in the application code of consumers. A technical problem emerged with the initial policy function that used entity codes. Once Finance renamed a number of those, some of the codes were retained in cached data extracts and silently caused access restrictions due to missing codes. Therefore, a surrogate keys level was implemented to avoid invalidating already computed access rights after renames. For instance, the process illustrates that a certain policy works consistently throughout all tiers of roles: simply ask the fact table by means of an arbitrary user in the corresponding role tier and compare the entity count with the scope for that particular tier. Cost center managers, for example, would not have any access beyond their own cost center, which includes only the subordinates of that cost center; access beyond those two scopes would be considered a flaw in the policy, and not a change in the business itself, unless proven otherwise. BI and Planning Sync Layer The sync process takes USER_ENTITY_ACCESS and passes it to three recipients: Power BI Row-Level Security (RLS) role membership, Tableau user filters, and security groups of Workday Adaptive Planning. This process guarantees the matching of the access control scope between a planning sheet and a dashboard built using the very same data. In the past, each BI team kept the maintenance of its RLS roles separate. Thus, there was an inherent possibility of the situation described in the introduction of this document, when Power BI roles were delayed compared to the Snowflake policy, causing the misalignment period and the related risks. The centralization of all pushes into one mapping table reduced the likelihood of such an incident. One issue came up during the development phase of the project because of the need for trial-and-error solutions. Namely, the Adaptive Planning API sets the rate limit for security group updates, making the mass updates impracticable. The successful implementation of the batch update method, where two hundred users could be updated in one request, became evident after trying the five hundred per request solution and failing because of the throttle errors. Prerequisites Read access to Workday HCM reports' web service, restricted by organization and using a special service account.Profile-level access to Salesforce bulk API that queries account hierarchy fields.Snowflake database edition enabling row access policies, along with a role capable of creating and attaching them.Credentials for Workday Adaptive Planning Integration API, with write access to security groups.Python version 3.10 or later, the snowflake-connector-python library, and job orchestration, where we used a scheduled container, but any other scheduler could have done the job.A concise list of hierarchy types required for the job. We had wasted much time dealing with hierarchy types nobody used further downstream. Overall, the flow is straightforward and linear – hierarchy sources are responsible for populating the closure table, which gets flattened by the mapping engine to produce the access table, followed by enforcing it through row access policy and expanding the scope through the sync layer. Figure 5 illustrates the entire process briefly. Design issues that continually arise in the context of such modeling include where each hierarchy comes from, and how an organizational level maps to a set of defined entities. These are two questions that can better be addressed by example. For instance, choosing a hierarchy graph vs. choosing a tree graph: The organizational level hierarchy describes supervisory reporting lines within Workday HCM, in which an individual contributor reports to a manager, who in turn reports to a director, and so on, independent of their actual geographical location. The cost-center hierarchy can be maintained within Workday HCM but follows a financial reporting line instead of the personnel management line, thus allowing for a cost center to be assigned to two legal entities if different allocations need to be created by shared services. There is yet another hierarchy, which is called the location level hierarchy, according to which each individual branch, plant, or facility consolidates into a region and eventually into a business unit, although often a branch can be geographically located in one region while being organizationally assigned to a completely different region via a regional manager. Finally, there is a legal entity hierarchy kept by Corporate Finance that describes corporate regulations and taxes, and not reporting lines. As mentioned before, there is also the account hierarchy coming from Salesforce, which comes from how RevOps divides accounts during the closure process. Each node in the closure table is marked with its domain membership rather than assuming the same hierarchy for all domains. It is precisely the latter that does not work when a branch/cost center/account needs to have different rollups depending on its hierarchy type. The following example illustrates how role tier relates to scope. The scope of the executive role is restricted to the entire organization and multiple tiers down within the organization. This allows for visibility over thousands of entities. On the other hand, the regional vice president's role allows for a scope restriction to a particular region at the location level as well as the entire organizational hierarchy below the individuals within that region. Hence, this leads to a large scope but still within limits, and one which is not the entire organization but rather a subset thereof. Similarly, the cost center manager's role limits scope to the cost center he is in as well as any individuals reporting directly to him. In turn, the analyst's role has a scope that is limited only to certain cost centers that have been defined in advance and does not inherit scope from any of the two hierarchies. Troubleshooting / Lessons Learned The closure table going stale without anyone noticing This document explains the failure mode created at the beginning of the document: The API timeout made the closure table go out of date, and none of the components downstream had any way to check whether the data they were using was up to date before they started using it. To solve this problem, a mechanism was built to prevent synchronization after the closure table has aged past a certain point to alert the operation staff that outdated entitlements would not be served without notifying anyone first. Any aging at all in the closure table may create a picture of an organization that does not exist anymore due to a quick reorganization. Orphaned nodes from in-flight Salesforce account merges Merges may create the problem whereby the child is tied to an expired parent ID during the final propagation. During a normal run, this type of problem could involve from a few to several tens of entries, based on the level of mergers made prior to the cycle run. This type of problem creates the risk of attribution of wrong parents; thus, all the records involving this problem are isolated in a reviewing table. The full-rebuild job locking the access table during business hours In the first approach, the whole access table would be recomputed each time, which is very effective but leaves the USER_ENTITY_ACCESS locked for long enough to queue the BI refreshes after it. Since in the second approach recomputation occurs only in the subtree concerned—as shown by the cost center example—locks become almost negligible, even in cases of thousands of changes due to reorganization. Adaptive Planning throttling the security group push Individually pushing updates to the security groups became unsustainable due to the large number involved. This is as previously discussed, where a relatively small-sized batch was successful while significantly larger ones experienced errors of being throttled by the Integration API. Conclusion This article will describe the design of the dynamic multi-hierarchy security model that is based on Workday HCM, Salesforce, Snowflake, Power BI, Tableau, and Workday Adaptive Planning. This methodology allows one to reconcile the hierarchical data coming from four different sources, such as the organization hierarchy, cost center hierarchy, location hierarchy, and legal entity hierarchy, in addition to the account hierarchy coming from Salesforce, into a tagged graph rather than trees, which can be inconsistent. Moreover, visibility recomputation is achieved due to any actual change within these hierarchies and not depending on periodic reviews as in most cases. It will also be proven that having a single permission table per Snowflake row rather than permission tables for each BI tool helps reduce the visibility gap, which caused the initiation of this project.

Stolen credentials served as the entry point in 22% of breaches last year, and in attacks on basic web applications that figure climbs to 88%. Those numbers describe a password problem, and databases sit at the end of nearly every attack path. A username and password prove nothing about the machine presenting them. Mutual TLS closes that gap by requiring both sides of a connection to present certificates and prove who they are before a single query runs. Securing production database connections has convinced me that enterprises implement mutual TLS using readily available tools and established certificate management practices. What Certificate-Based Authentication Actually Closes Off A password travels, gets shared, gets phished, and gets left behind in a script. A certificate bound to a specific client does none of those things easily, which is why mutual authentication blunts three familiar attack patterns: stolen credentials replayed from an unfamiliar host, spoofed clients impersonating an application server, and lateral movement after an attacker gains an initial foothold. Enterprises usually maintain password rotation policies that are triggered after a set period or upon an employee's exit from the team. Certificate-based authentication safeguards the data in case a team misses rotating those credentials, because the password alone no longer grants entry. I ran into this while setting up an open-source alerting tool, where the password had to live in a config file or a session variable. The session variable fails when the tool auto-restarts during maintenance, leaving a hardcoded password or a decryption utility to mask it. A certificate addresses that problem because its lifetime can be governed by the organization's security policies. Once the certificate expires, a password alone is no longer sufficient to authenticate the client. Machine identities now outnumber human identities by more than 80 to 1 in the average organization, and each database connection string is one of them. The Rollout Decisions That Matter Most An mTLS program stands on three design choices. The first is the certificate authority, where an internal CA gives the team full control over issuance and revocation for database traffic that never leaves the estate. The second is cipher selection, which deserves more attention than it gets, because Oracle, MySQL, and MongoDB each negotiate TLS differently, and a cipher suite that works on one engine can fail the handshake on another. The third is rotation, and this is where programs die quietly. 81% of organizations have suffered at least two outages caused by expired certificates in a two-year window. Six months to a year is an ideal certificate lifetime across a polyglot estate, though the organization's security baselines govern, and estates handling critical PII or PCI data, or carrying past breach attempts, can justify a reduced lifetime. Renewing very frequently creates its own outages, because some systems require a reboot to bring new certificates into effect, a real challenge for heavily used 24/7 applications without a high availability solution ready. Keeping lifetimes consistent across Oracle, MongoDB, MySQL, and PostgreSQL helps manage the rotations, but databases are not all created in a single day, so expiry timelines differ and an inventory or dashboard tracking every expiry becomes essential. Above all, automating renewal wherever possible reduces the risk of downtime from an expired certificate. Securing the Monitoring Layer Itself Monitoring an encrypted estate raises a question teams often skip, which is how to keep the monitoring path from becoming the weak point. Many organizations use Prometheus to collect database metrics, with more than two-thirds of organizations running it in production, yet exporters may be deployed with unencrypted scrape endpoints if they are not configured to use TLS. One common deployment approach is to run the database exporter process on the database server itself. In Prometheus-based environments, configuring both the client connection (--config.my-cnf) and the exporter (--web.config.file) to use client certificates allows the monitoring pipeline to follow the same mutual authentication model as the database it monitors. This helps ensure that metrics are collected over authenticated, encrypted connections rather than introducing a weaker path into the environment. Choosing the Right Approach Organizations can implement mutual TLS using either commercial certificate management platforms or open-source tooling. The right approach depends on factors such as certificate volume, compliance requirements, auditing needs, and the operational resources available to manage the environment. Early in my career, I assumed enterprise-licensed products were the default choice for every deployment. Over time, I found that decision is more nuanced. Large environments managing tens of thousands of certificates may benefit from centralized lifecycle management, auditing, and governance features, while many organizations can successfully implement mTLS using open-source tools that meet their operational requirements. The priority should be selecting an approach that supports reliable certificate issuance, rotation, and revocation while integrating with existing security processes. Regardless of the tooling, a well-managed certificate lifecycle is what ultimately strengthens database authentication and reduces operational risk.