DEV Community: Ryan Giggs

Why Your AI Works in a Notebook But Fails in Production — LLM Zoomcamp Module 3

Ryan Giggs — Sun, 05 Jul 2026 13:58:10 +0000

Every developer has seen it: an AI prototype that impresses in demos but breaks the moment real users touch it. The problem is rarely the model. It's everything around the model, how context is prepared, how tools are called, how failures are handled, how costs are tracked.

Module 3 of LLM Zoomcamp 2026 addresses this gap head-on. Taught by Will Russell from Kestra, this module shifts the conversation from "how do I call an LLM" to "how do I run AI reliably at scale."

Here's what I learned and what most LLM tutorials never teach you.

The Experiment That Changed How I Think About AI

Try this yourself. Open ChatGPT and ask it to generate a Kestra workflow that loads CSV data into BigQuery. You'll get YAML that looks completely reasonable and doesn't work. Wrong plugin types, outdated parameters, invented task names.

Now try the same prompt in Kestra's AI Copilot. It generates a flow that runs.

Same model family. Same prompt. Completely different outcome.

The reason: Kestra's AI Copilot retrieves current plugin documentation before generating. The model has accurate, up-to-date context. ChatGPT is guessing from training data that's months or years old.

This is Context Engineering, the discipline of giving a model exactly the right information at the right time. It's more impactful than model size, fine-tuning, or prompt tricks.

RAG vs No RAG: Measured, Not Assumed

The module includes a concrete experiment with two pre-built flows:

Without RAG, the model invents plausible but fabricated Kestra 1.1 features with full confidence. It describes "Declarative I/O with Variables", "Enhanced Task Groups", and other features that sound real but aren't accurate to that release.

With RAG, the model retrieves the actual Kestra 1.1 release notes and returns real features: the No-Code Dashboard Editor, Multi-Agent AI Systems, Fix with AI, the Human Task for manual approvals, and improved air-gapped environment support.

One is a liability in production. The other is actually useful.

AI Agents: The LLM Takes the Wheel

The second half of the module moves from RAG to agents. The difference:

RAG pipeline: you decide when to search, what to search, how many results to retrieve
AI agent: the model decides all of that itself

In Kestra, agents are declared in YAML and the framework handles the agentic loop:

- id: research_agent
  type: io.kestra.plugin.ai.agent.AIAgent
  systemMessage: |
    You are a technical research assistant.
    Use your tools to find accurate information before answering.
  prompt: "{{ inputs.question }}"

The agent decides whether to call a tool, which tool, with what query, and when it has enough context to stop searching and answer. You get full observability, every tool call, every response, every token logged in the Kestra UI.

Tracking What Matters: Token Usage

Most tutorials skip this. The module doesn't.

Every agent task in Kestra exposes token usage on its output object. The module shows you how to log it explicitly:

- id: log_usage
  type: io.kestra.plugin.core.log.Log
  message: |
    Output tokens: {{ outputs.agent.tokenUsage.outputTokenCount }}

What I measured across runs:

Short summary: 63 output tokens
Long summary: 184 output tokens (~3x more)
3-sentence vs 1-sentence prompt: ~2x more tokens

Small wording changes in prompts have real cost implications when running thousands of executions. Monitoring from day one prevents surprises.

When Agents Are the Wrong Choice

This is the lesson most AI courses skip: agents are non-deterministic. The same input can produce different tool calls, different search strategies, different answers across runs.

For creative tasks, research, and open-ended workflows that flexibility is a feature.

For financial reporting, compliance workflows, audit trails, and regulated industries it's a liability. You need deterministic, repeatable, fully auditable pipelines where every step is predictable and logged.

Kestra supports both. Knowing when to use which is the actual engineering judgment.

My Full Solution

All flows and code are open source:

github.com/Derrick-Ryan-Giggs/llm-zoomcamp-2026

Take the Course Free

github.com/DataTalksClub/llm-zoomcamp

Module 4 covers Evaluation — how to measure whether your RAG system is actually working. Writing about that next.

I Built a Hybrid Search Engine From Scratch — Here's What I Learned (LLM Zoomcamp 2026, Module 2)

Ryan Giggs — Fri, 26 Jun 2026 11:49:10 +0000

I just completed Module 2 of the LLM Zoomcamp 2026 by @DataTalksClub — and this module completely changed how I think about search.

Module 1 taught me RAG and agentic pipelines. Module 2 taught me that the search step inside RAG matters far more than I realized — and that keyword search is only half the story.

Here's everything I built and learned.

What Is Vector Search and Why Does It Matter?

Traditional keyword search matches words. If you search for "enroll", it finds documents containing "enroll" — but misses documents about "joining", "signing up", or "registration" even if they mean exactly the same thing.

Vector search matches meaning, not words.

Every piece of text gets converted into a vector — a list of hundreds of numbers that captures its semantic meaning. Similar meanings produce similar vectors, so you can find relevant documents even when they use completely different words.

This is the foundation of modern AI-powered search, and it's what makes RAG systems actually work at scale.

What I Built in Module 2

1. Text Embeddings with a Lightweight ONNX Model

Instead of downloading the full PyTorch + CUDA stack (~2GB), I used a lightweight ONNX runtime embedder — same vectors, 30x smaller installation, runs on any CPU:

from embedder import Embedder

embedder = Embedder()  # loads Xenova/all-MiniLM-L6-v2 via ONNX
v = embedder.encode("How does approximate nearest neighbor search work?")
print(len(v))  # 384 dimensions

The model produces 384-dimensional vectors — each number represents a dimension of meaning in the text.

2. Vector Search From Scratch with NumPy

Before using any library, I implemented vector search by hand to understand what's happening under the hood:

import numpy as np

# cosine similarity — vectors are normalized, so dot product works directly
def cosine_similarity(a, b):
    return np.dot(a, b)

# score all chunks against a query
scores = X.dot(v)  # X is the matrix of all chunk embeddings
best_idx = np.argmax(scores)

This is exactly what vector databases like Qdrant and pgvector do internally — just much faster at scale using HNSW indexing.

3. Chunking Long Documents for Better Retrieval

Full pages are too long and dilute the embedding — a match buried deep in a 10,000-character page still pulls in the whole page. The fix is chunking:

from gitsource import chunk_documents
chunks = chunk_documents(documents, size=2000, step=1000)
# 72 pages → 295 overlapping chunks

Overlapping chunks (step < size) ensure sentences at boundaries don't get cut off. After chunking, retrieval becomes far more precise.

4. Vector Search with minsearch

minsearch now has a VectorSearch class that wraps the numpy math into a clean interface:

from minsearch import VectorSearch

vector_index = VectorSearch(keyword_fields=["filename"])
vector_index.fit(X, chunks)

results = vector_index.search(query_vector, num_results=5)

5. Comparing Keyword vs Vector Search

For the query "How do I store vectors in PostgreSQL?":

Keyword search — missed 08-pgvector.md entirely because "pgvector" wasn't in the query
Vector search — ranked 08-pgvector.md first because it understood the semantic connection between "store vectors" and "pgvector"

This is the key insight: vector search finds meaning, keyword search finds words.

6. Hybrid Search with Reciprocal Rank Fusion (RRF)

Neither approach is perfect on its own:

Vector search can miss exact terms, names, and rare keywords
Keyword search misses paraphrases and synonyms

The solution is hybrid search — run both and merge the results using RRF:

def rrf(result_lists, k=60, num_results=5):
    scores = {}
    docs = {}
    for results in result_lists:
        for rank, doc in enumerate(results):
            key = (doc["filename"], doc["start"])
            scores[key] = scores.get(key, 0) + 1 / (k + rank)
            docs[key] = doc
    ranked = sorted(scores, key=scores.get, reverse=True)
    return [docs[key] for key in ranked[:num_results]]

results = rrf([vector_results, text_results])

RRF ignores raw scores (which live on different scales) and only looks at rank position. A document that ranks well in both lists beats one that's only strong in a single list — even if it wasn't first in either.

Key Takeaways

1. Embeddings capture meaning, not words. "Enroll" and "join" produce similar vectors. "Pizza" and "enrollment" don't. This is what makes semantic search powerful.

2. Chunking is not optional. Full pages dilute embeddings. 2,000-character overlapping chunks dramatically improve retrieval precision and cut LLM input tokens by 3x.

3. Neither keyword nor vector search is best. Use hybrid search (RRF) in production. It consistently outperforms either approach alone.

4. ONNX makes embeddings practical anywhere. No GPU, no PyTorch, no CUDA. 67MB download, runs on a basic laptop. There's no reason not to use vector search even in constrained environments.

5. The right search approach depends on your data. Vector search wins for semantic queries. Keyword search wins for exact terms (names, codes, IDs). Hybrid wins most of the time — but measure to be sure.

My Homework Solution

All my code for Module 2 is open source:

github.com/Derrick-Ryan-Giggs/llm-zoomcamp-2026

It includes:

vector-search.ipynb — embeddings, Qdrant, and vector RAG pipeline
Vector Search Homework.ipynb

Want to Learn Too?

LLM Zoomcamp is completely free — no paywall, no certificate fees.

Are you working through LLM Zoomcamp 2026? Drop a comment — I'd love to compare notes.

Cloud Native Architecture Fundamentals: Building for the Modern Era

Ryan Giggs — Thu, 25 Jun 2026 09:06:34 +0000

Optimize your software for cost, efficiency, reliability, and faster delivery — by combining the right technological and architectural patterns.

Based on cloud native curriculum from the @Linux Foundation | CNCF

What Is Cloud Native?

The Cloud Native Computing Foundation (CNCF) defines cloud native as a set of practices that empower organizations to develop, build, and deploy workloads in computing environments — public, private, and hybrid cloud — to meet organizational needs at scale in a programmatic and repeatable manner.

Cloud native systems are characterized by being loosely coupled, interoperating in a manner that is secure, resilient, manageable, sustainable, and observable.

In practice, cloud native architectures typically consist of some combination of:

Containers
Service meshes
Microservices
Immutable infrastructure
Serverless functions
Declarative APIs
Multi-tenancy

And the ecosystem is thriving. According to the CNCF 2024 Annual Survey, cloud native adoption has reached an all-time high of 89% among surveyed organizations, with Kubernetes being used, piloted, or evaluated by 93% of those organizations.

Monolith vs. Cloud Native: Why the Shift?

Traditional monolithic applications bundle all functionality into a single deployable unit. This works fine at small scale — but as user demand grows and teams expand, a monolith becomes a bottleneck. Every change risks the whole system, scaling requires duplicating everything, and a single bug can bring the entire application down.

Cloud native architecture addresses this by breaking the application into smaller, independently deployable units — each with a clearly defined scope of function. These are called microservices.

Instead of one giant app, you could have:

A UI service rendering the frontend
A checkout service handling payments
A notifications service managing alerts
An inventory service tracking stock

Each microservice communicates with the others over a network, typically via REST APIs or message queues. They can be developed, deployed, scaled, and updated independently — by separate teams, on separate schedules.

Monolith → Microservices is not just a technical shift. It's a business transformation that directly impacts how fast you can ship, how resilient your product is, and how efficiently your teams operate.

Core Technologies in Cloud Native

Containers

Containers package your application and all its dependencies into a single portable unit. They ensure the app runs consistently across development, staging, and production environments, eliminating the classic "it works on my machine" problem. Docker popularized this model; containerd is now the standard runtime in production Kubernetes clusters.

Kubernetes (K8s)

Kubernetes is the de facto orchestration platform for containers. It automates deployment, scaling, and management of containerized applications. Advanced Kubernetes usage, including auto-scaling with the Horizontal Pod Autoscaler (HPA) and Vertical Pod Autoscaler (VPA), has been shown to increase deployment efficiency by 55% according to CNCF data.

Service Meshes

A service mesh manages secure, reliable communication between microservices — handling load balancing, retries, circuit breaking, and mutual TLS (mTLS) encryption. Tools like Istio and Linkerd abstract these concerns away from your application code.

2025 trend watch: Service mesh is evolving away from the resource-heavy sidecar-per-pod model. Istio's ambient mode now uses a node-level proxy (ztunnel) instead, dramatically reducing overhead and making mesh adoption more practical for teams previously deterred by operational complexity.

Serverless

Serverless takes abstraction further — you write code, the cloud provider handles all underlying infrastructure. Functions-as-a-Service (FaaS) platforms like AWS Lambda, Azure Functions, and Google Cloud Functions are ideal for event-driven, unpredictable workloads. Plans for serverless adoption are growing, with the share of companies running no serverless platforms expected to drop significantly through 2025.

Declarative APIs & Infrastructure-as-Code (IaC)

Rather than imperatively telling systems how to do something, you declare what the desired state should be. Tools like Terraform, Helm, and Crossplane allow teams to version-control infrastructure alongside application code — making environments reproducible and auditable.

Key Characteristics of Cloud Native Architecture

1. High-Level Automation

To manage all the moving parts of a cloud native system, automation is critical at every stage — from development to production. CI/CD pipelines (using tools like GitHub Actions, ArgoCD, or Flux) enable fast, reliable delivery and make disaster recovery dramatically easier — if you must rebuild your entire system, your pipeline can do it reproducibly from source.

A related practice worth knowing: GitOps, where Git is the single source of truth for both application and infrastructure state. GitOps-based tools like ArgoCD continuously reconcile your running environment against what's declared in a repository, reducing configuration drift and enabling fast, auditable rollbacks.

2. Self-Healing

Failures happen — this is expected and designed for. Cloud native applications embed health checks that allow orchestration platforms like Kubernetes to automatically detect unhealthy containers and restart or replace them, often without any human intervention. Your uptime doesn't depend on someone being awake at 3am.

3. Scalability

Cloud native applications are designed to scale horizontally — spinning up more instances of a service under load, rather than upgrading a single server. Kubernetes' HPA can trigger this automatically based on real-time CPU, memory, or custom metrics. This elasticity means you always have the capacity to handle demand, without permanently over-provisioning.

Scaling goes both ways. Scaling down during low-traffic periods — combined with usage-based pricing from cloud providers — is a major source of cost savings.

4. Cost Efficiency

Orchestration systems like Kubernetes are excellent at bin-packing — placing workloads efficiently across your compute fleet to minimize waste. Combined with autoscaling and cloud pricing models, cloud native architectures allow you to pay for what you actually use, not what you might need in a worst-case scenario. Tools like OpenCost and Kepler (a CNCF project) now even provide visibility into Kubernetes spend and carbon consumption per workload.

5. Ease of Maintenance

Microservices make applications more portable, testable, and independently deployable. Small, well-scoped services are easier to understand, debug, and hand off between teams. This is especially valuable in organizations where multiple product teams need to move at their own pace without stepping on each other.

6. Secure by Default

Cloud environments are often shared across multiple teams and workloads, requiring layered security models. Cloud native security operates across several dimensions:

Network policies to restrict traffic between services
mTLS via service meshes to encrypt inter-service communication
RBAC (Role-Based Access Control) to limit what each workload can access
Supply chain security using tools like Trivy (image scanning) and KubeSec (Kubernetes manifest analysis)
Security zones to enforce isolation between tenants or environments

The CNCF Security Technical Advisory Group continuously develops guidance and tooling to address emerging threats across the cloud native landscape.

Observability: The Pillar You Can't Skip

In a distributed, microservices-based architecture, knowing what's happening inside your system is non-trivial. This is where observability becomes a first-class concern — not something bolted on after problems arise.

Cloud native observability rests on three pillars:

Metrics — quantitative data about system health (CPU, memory, request rates, error rates)
Logs — structured records of events within services
Traces — end-to-end tracking of a request as it flows across multiple microservices

Key tools in this space:

Tool	Role
Prometheus	Metrics collection and alerting
Grafana	Visualization dashboards
Jaeger / Zipkin	Distributed tracing
OpenTelemetry	Vendor-neutral instrumentation standard
Fluentd / Loki	Log aggregation

According to the CNCF Q3 2025 State of Cloud Native report, observability tools are now the second most widely adopted cloud native technology after Kubernetes, used by 28% of backend developers.

Immutable Infrastructure: Stop Patching, Start Replacing

Traditional operations involved SSH-ing into servers to apply patches, update configs, or fix issues. Cloud native systems take the opposite approach — immutable infrastructure means servers are never modified after deployment. When a change is needed, a new image is built, tested, and deployed, and the old instance is discarded.

This makes deployments predictable and repeatable, eliminates configuration drift (where production slowly diverges from your declared desired state), and simplifies rollback — you simply redeploy the previous image.

Cloud Native vs. Cloud-Based: Know the Difference

These terms are often used interchangeably, but they describe very different things:

	Cloud-Based	Cloud Native
Design	Legacy apps migrated to cloud	Built from the ground up for cloud
Scaling	Often manual or vertical	Automatic, horizontal
Deployment	May still be monolithic	Microservices, containers
Infrastructure	Often mutable	Immutable, declarative
Resilience	Basic backup/restore	Self-healing, auto-failover

What's Next: Cloud Native in the Age of AI

Cloud native is rapidly becoming the default platform for AI/ML workloads too. Kubernetes is emerging as the primary runtime for GPU-accelerated training and inference. Tools like Kubeflow, Argo Workflows, and Kserve bring MLOps discipline to model pipelines. The same principles — declarative configuration, autoscaling, observability, and GitOps — apply directly to AI infrastructure.

This convergence of cloud native and AI is reshaping how organizations think about infrastructure at scale.

Getting Started: A Practical Path

If you're new to cloud native, here's a pragmatic starting point:

Containerize a small, stateless service with Docker
Deploy it on Kubernetes (try Minikube or kind locally)
Set up a basic CI/CD pipeline with GitHub Actions
Add observability with Prometheus and Grafana
Explore GitOps with ArgoCD or Flux
Experiment with a service mesh (Istio or Linkerd) as you scale out

The CNCF Cloud Native Maturity Model is an excellent framework for understanding where you are and where to go next.

Final Thoughts

Cloud native architecture is not a single technology — it's a philosophy and a set of practices for building software that is resilient, scalable, and efficient by design. It demands a shift in how teams think about infrastructure, deployment, and failure. The payoff: faster delivery, lower costs, and systems that can grow as your users do.

The ecosystem is maturing rapidly, and now is an excellent time to build these skills — they're increasingly foundational to modern software engineering.

Learnings from the Linux Foundation's cloud native curriculum. The Linux Foundation is the parent organization of CNCF and one of the leading sources of open source and cloud native education globally.

I Just Built an Agentic RAG System From Scratch — Here's What I Learned (LLM Zoomcamp 2026, Module 1)

Ryan Giggs — Sun, 21 Jun 2026 12:17:44 +0000

I just completed Module 1 of the LLM Zoomcamp 2026 by @DataTalksClub — and honestly, this is the most hands-on AI course I've taken.

No fluff. No hand-holding. Just real code, real concepts, and a working system by the end.

Here's everything I learned — and why it matters if you're a data engineer or software developer trying to break into LLM applications.

What Is LLM Zoomcamp?

LLM Zoomcamp is a free, open-source course by Alexey Grigorev and DataTalks.Club that teaches you how to build production-ready LLM applications from scratch. No GPU required. No expensive API bills. Just Python, curiosity, and a willingness to build.

Module 1 is called Agentic RAG — and it covers everything from what an LLM is to building a fully autonomous AI agent that decides when and what to search.

What I Built in Module 1

1. A RAG Pipeline From Scratch

RAG stands for Retrieval-Augmented Generation. The idea is simple: instead of asking an LLM a question and hoping it knows the answer, you first search a knowledge base for relevant documents, then pass those documents as context to the LLM.

The result? Grounded, accurate answers instead of hallucinations.

I built the full pipeline in ~30 lines of Python:

def rag(question):
    search_results = search(question)          # 1. find relevant docs
    prompt = build_prompt(question, search_results)  # 2. build context prompt
    return llm(prompt)                         # 3. generate answer

Simple. Powerful. The foundation of every production RAG system.

2. Document Indexing with minsearch

I indexed 1,242 FAQ documents from the DataTalks.Club Zoomcamp courses using minsearch — a lightweight keyword search library built by Alexey himself. It uses the same concepts as Elasticsearch (text fields, keyword fields, boosting, filtering) but runs in pure Python with zero infrastructure.

3. Document Chunking for Better Retrieval

Long documents hurt retrieval precision. A match deep inside a 10,000-character page still pulls the whole page into the LLM context — wasteful and noisy.

The fix is chunking: split each document into smaller overlapping pieces and index those instead.

from gitsource import chunk_documents
chunks = chunk_documents(documents, size=2000, step=1000)

The result? ~3× fewer input tokens sent to the LLM — smaller, faster, cheaper, and more accurate.

4. Turning RAG Into an Agent with Function Calling

This is where it gets exciting.

A standard RAG pipeline is fixed: question → search once → answer. The developer controls the flow.

An agentic RAG system puts the LLM in charge:

question → LLM thinks → search? → LLM thinks → search again? → LLM thinks → answer

The LLM decides:

Whether to search at all
What to search for
How many times to search
When it has enough context to answer

I implemented this using function calling — giving the LLM a search tool it can invoke on its own. In one test, the agent autonomously made 3 different searches with progressively refined queries before generating a final answer. No hardcoding. No fixed flow.

Tech Stack

LLM: Groq API (llama-3.1-8b-instant) — free tier, blazing fast
Search: minsearch — lightweight keyword search
Chunking: gitsource chunk_documents
Environment: uv + Python 3.12
Knowledge base: DataTalks.Club Zoomcamp lesson pages (72 markdown files, 295 chunks)

Key Takeaways

1. RAG is just 3 functions. search() + build_prompt() + llm(). Everything else is optimization.

2. Chunking matters more than you think. Going from full documents to 2,000-character chunks reduced input tokens by 3× and improved answer quality significantly.

3. Agents are just loops. The "magic" of agentic AI is literally a while loop that keeps calling the LLM until finish_reason == "stop". Understanding this demystifies 90% of agent frameworks like LangChain and LlamaIndex.

4. You don't need OpenAI. I ran everything on Groq's free tier using Llama 3.1. The OpenAI-compatible API means zero code changes.

My Homework Solution

All my code for Module 1 is open source on GitHub:

github.com/Derrick-Ryan-Giggs/llm-zoomcamp-2026

It includes:

rag-intro.ipynb — the full RAG pipeline
agents.ipynb — function calling and the agentic loop
homework.ipynb — Module 1 homework solutions

Want to Learn Too?

LLM Zoomcamp is completely free. No paywall, no certificate fees, just open-source learning.

Module 2 is up next — Vector Search. I'll be writing about that too.

If you're a data engineer, ML practitioner, or software developer who wants to build real LLM applications — not just call ChatGPT — this course is for you.

Are you taking LLM Zoomcamp 2026? Drop a comment — I'd love to connect and compare notes.

I Built a Data Lakehouse to Map Kenya's Healthcare Inequality — Here's What I Learned

Ryan Giggs — Mon, 08 Jun 2026 17:36:51 +0000

A child born in Westlands, Nairobi has access to 7.23 health facilities per 10,000 people.

A child born in Embakasi North, also in Nairobi, has 0.62.

Same city. Same county. One-tenth the access.

That number did not come from a government report. It did not come from a consultant's PowerPoint. It came from a pipeline I built from scratch, joining three public datasets that nobody had systematically connected before — the Ministry of Health facility registry, the KNBS 2019 Census, and HDX county boundary GeoJSON.

This is the story of how I built it, why I made the architecture choices I made, and what I would do differently.

The Problem

Kenya has 47 counties and over 20,000 registered health facilities. The Ministry of Health publishes facility data through the Kenya Master Health Facility Registry (KMHFR). The Kenya National Bureau of Statistics publishes county population data. The Humanitarian Data Exchange publishes county boundary GeoJSON for choropleth mapping.

Three datasets. Three different sources. All public. None of them joined.

Without joining them, you cannot answer the most basic questions:

Which counties are most underserved relative to their population?
Which counties have no maternity facilities? No ART centres? No TB clinics?
How many facilities would Bungoma need to reach the national baseline of 3 per 10,000 people?
Has the facility landscape changed over time — are counties gaining or losing services?

The data to answer all of this exists. It just has not been assembled. That is the problem this pipeline solves.

The Stack

Here is what I built with, and more importantly, why:

Infrastructure as Code: OpenTofu — Provisions two MinIO buckets before the stack starts. OpenTofu is the community-maintained open-source fork of Terraform following HashiCorp's BSL license change.

Orchestration: Apache Airflow (CeleryExecutor + Redis) — Monthly DAG: three ingestion tasks run in parallel, a barrier gate waits for all three to complete, then dbt runs.

Data Lake: MinIO — S3-compatible object storage running in Docker. Raw NDJSON files land here first, partitioned by year/month/day.

Table Format: Apache Iceberg — ACID transactions, schema evolution, and time travel on top of files. The SCD2 snapshot tracks which facilities gained or lost maternity/ART/TB services month over month.

Catalog: Iceberg REST Catalog — SQLite-backed, persists across Docker restarts via a named volume.

Query Engine: Trino 480 — Federated SQL over Iceberg tables stored in MinIO.

Transformations: dbt Core + dbt-trino — Staging models, four mart models, one SCD2 snapshot, 30 passing data quality tests, one seed file.

Dashboards: Apache Superset 5.0.0 — Eight charts on one dashboard.

Everything runs in Docker Compose on my HP EliteBook 840. Zero cloud spend.

The Architecture Tradeoff I Need To Be Honest About

Here is where I am going to say something that might surprise you.

This stack is overkill for 20,000 rows of data.

Trino is a distributed query engine built for petabyte-scale federated queries across dozens of data sources. My biggest table has 20,391 rows. DuckDB — a single embedded analytics database that runs in a Python process — would query that in milliseconds without any infrastructure at all.

CeleryExecutor with Redis is designed for Airflow deployments with many concurrent tasks across multiple workers. I have three DAGs that run once a month. LocalExecutor handles that just fine with zero additional services.

The Iceberg REST catalog backed by SQLite is, genuinely, the worst of both worlds at this scale — you get all of Iceberg's operational complexity without most of its benefits.

So why did I build it this way?

Because this is the architecture I would run at scale — and I needed to prove I can operate it.

If this pipeline were ingesting facility data from all 54 African Union member states, with real-time updates from DHIS2, and serving 200 analysts querying simultaneously — you would need exactly this stack. Trino for federated querying. CeleryExecutor so ingestion tasks do not block dashboard queries. Iceberg for time travel and schema evolution as the data model matures.

The data volume is small. The architecture patterns are production-grade. I built it this way intentionally, because the goal of a portfolio project is not just to answer a data question — it is to demonstrate that you can operate the systems that answer data questions at companies where the stakes are real.

When I sit in a data engineering interview and someone asks "have you worked with Trino and Iceberg?" the answer is yes, in anger, debugging catalog persistence issues and permission conflicts at 11pm. That is worth more than a hypothetical answer.

What I Actually Found

After all of it was running, here is what the data showed:

National level:

Bungoma is Kenya's most underserved county: 1.88 facilities per 10,000 people, serving 1.67 million people
Samburu is the only county with a critical TB gap — just 1 TB facility for 310,327 people
Every county in Kenya has at least one emergency facility — that surprised me
Mandera has the highest raw ratio (6.26 per 10k) — but that is sparse population density, not good healthcare access. Ratios lie without context.

Nairobi sub-county drill-down:

Starehe (CBD): 7.23 facilities per 10,000 people
Embakasi North: 0.62 facilities per 10,000 people
The most underserved sub-county has eleven times fewer facilities per capita than the most served, within the same city

The Dashboard

Eight charts. One story. Here is what each one shows.

Chart 1 — Facilities per 10,000 People by County

The primary national scorecard. Every bar is one of Kenya's 47 counties. The spike you see around Lamu reflects its small population relative to its facility count — a reminder that raw ratios must be read alongside absolute population figures. The counties sitting below 2.0 on the left — Trans Nzoia, Kakamega — are large-population counties being severely underserved in absolute terms. The Y axis is facilities per 10,000 people. The X axis is sorted alphabetically; the ranked view lives in the next chart.

Chart 2 — Underserved Counties Ranking

All 47 counties ranked from most to least underserved. The severity score column is highlighted red for scores of 3 (critical) and pink for scores of 2 — Bungoma and Samburu both carry severity score 3. The facilities_needed_to_baseline column shows how many additional facilities each county would need just to reach a minimum of 3 per 10,000 people. Bungoma needs 21. The tb_gap_flag column confirms Samburu as the only county in Kenya with a critical TB service gap. This table is the most action-oriented output in the entire pipeline — it is a prioritised to-do list for the Ministry of Health.

Chart 3 — Maternity Coverage by County

Maternity facilities per 100,000 people across all 47 counties. The spike at Lamu (~52) is the small-population effect again. More meaningful are the counties between 10 and 17 on the left side — these are where maternal healthcare access is most constrained on a population-adjusted basis. Kenya's maternal mortality remains high in underserved counties, and this chart makes that structural gap visible and quantified for the first time in a reproducible pipeline.

Chart 4 — Service Coverage Rates by County

Four services compared side by side for every county: TB (blue), maternity (dark red), ART/HIV treatment (orange-red), and emergency care (amber). The tall blue TB bars dominate because TB diagnosis points are integrated into a wider range of facility types nationally. The very short amber emergency bars across most counties are a quiet crisis in themselves — emergency care coverage is thin across the country. This is the most information-dense chart in the dashboard and rewards close examination county by county.

Chart 5 — Nairobi Sub-County Facility Density

This is the chart that stops people. Embakasi North: 0.62 facilities per 10,000 people. Starehe: 7.23. Same city. The bar on the far right (Starehe/Westlands cluster) towers over everything else. Embakasi North, the short bar third from the left, has 291,760 people and only 18 health facilities. This chart is the most visceral data visualisation in the project — it shows that healthcare inequality is not just a rural versus urban story, but an intra-city story playing out at sub-county level inside Nairobi.

Chart 6 — Nairobi Sub-County Service Gaps

The full service breakdown for all 17 Nairobi sub-counties, sorted by facilities per 10k ascending — most underserved first. The table shows 2024 projected population (2019 census + 2.3% annual KNBS growth rate) alongside raw counts of maternity, ART, and emergency facilities per sub-county. What stands out is that every sub-county shows no_maternity_flag = false and no_emergency_flag = false. Nairobi's inequality is not a service absence crisis — it is a density crisis. Every sub-county has some maternity and emergency facilities. Not nearly enough of them.

Chart 7 — Total Counties Mapped

A single bold number: 47. All of Kenya's counties accounted for. This is not just cosmetic — a custom dbt test (assert_county_count_equals_47) enforces this in code. If any pipeline run produces fewer than 47 counties due to data quality issues, join failures, or ingestion gaps, the test fails and the pipeline stops before writing bad data to the mart tables.

Chart 8 — Kenya Facility Density Map

A full choropleth of all 47 Kenya counties, colour-coded by facility density. Light yellow is least served (0.9–1.8 per 10k). Dark maroon is most served (4.4–5.3 per 10k). The dense cluster of small coloured counties in the southwest reflects the high-population Rift Valley and central counties. The large pale northern counties — Turkana, Marsabit, Wajir, Mandera — are sparse in both population and facilities. Building this map required significant engineering: Superset's built-in Kenya map only has the pre-2013 provincial boundaries (8 provinces, not 47 counties), so I built a custom virtual dataset wrapping each county's raw GeoJSON geometry as a full Feature object to make deck.gl render it correctly.

The Engineering Problems That Nearly Broke Me

The Superset logout loop

Both Airflow and Superset were logging me out within 20 seconds of login. The root causes turned out to be three separate things simultaneously: multiple Airflow gunicorn workers not sharing session state, a Superset 5.0 regression breaking database session persistence, and Flask generating a random SECRET_KEY on every container restart — invalidating every session cookie on every docker compose restart.

Fix: AIRFLOW__WEBSERVER__WORKERS=1, a fixed SECRET_KEY in .env from openssl rand -hex 32, and a before_request Flask hook forcing session.permanent = True on every request.

The Iceberg catalog disappearing on restart

Every restart wiped all Trino schema and table knowledge. The REST catalog was using in-memory SQLite by default.

Fix: CATALOG_URI=jdbc:sqlite:/catalog/iceberg_catalog.db with a named Docker volume. Two config lines. One hour of debugging.

The dbt target/ permission conflict

The VS Code dbt extension creates dbt/target/ with host user ownership (UID 1000). Airflow runs as UID 50000. Every dbt run failed with Permission denied.

Fix: declare dbt-target as a named Docker volume mounted over the bind-mounted ./dbt directory. Named volumes are owned by the Docker daemon, not the host user.

The KMHFR API redirect

Original pipeline hit kmhfl.health.go.ke. That domain 301-redirects to kmhfr.health.go.ke, which returned just the URL path as plain text — not JSON. Three weeks of failed ingestion before finding the correct public API endpoint: api.kmhfr.health.go.ke/api/public/facilities/.

The Superset Country Map with 8 old provinces

Superset's built-in Kenya GeoJSON has the pre-2013 provincial boundaries — 8 provinces, not 47 counties. No ISO code mapping fixes this. The solution was switching to deck.gl Polygon with a custom SQL virtual dataset wrapping each county's geometry as a GeoJSON Feature object, with explicit CASE mapping for three county name mismatches across datasets.

What I Would Do Differently

Use DuckDB instead of Trino for a project of this scale. Use LocalExecutor for Airflow. Start with the data model before the infrastructure.

The data model is what creates value. The infrastructure is just plumbing. I spent more time debugging the plumbing than thinking about what the data should actually say.

What's Next

The pipeline runs. The dashboards are live. The data tells a real story.

The next step is getting it to the people who can act on it: Ministry of Health Kenya, UNICEF Kenya, Amref Health Africa, the Council of Governors, Code for Kenya. The data is public. The pipeline is open source. Anyone can run it.

If you work at any of those organisations and want to talk about this data — reach out.

Github : github.com/Derrick-Ryan-Giggs/kenya-health-pipeline

I Built a Real-Time Crypto Analytics Pipeline for $0.01/Month — Here's the Full Architecture

Ryan Giggs — Mon, 27 Apr 2026 08:39:51 +0000

How I combined Apache Flink, Redpanda, Airflow, dbt Cloud, and Grafana to track Bitcoin, Ethereum, Solana, BNB, and Cardano in real time — all running on Google Cloud for less than a cup of coffee per month.

If you've been learning data engineering, you've probably built pipelines that move CSV files from A to B. Nothing wrong with that — but real data engineering interviews are starting to ask a different question: Can you handle data that never stops arriving?

This article walks through CoinPulse, a project I built from scratch to answer that question. It's a hybrid streaming and batch crypto analytics pipeline that:

Consumes live trade events from Binance's WebSocket feed at ~1 tick per second per coin
Processes them with Apache Flink using 1-minute tumbling windows
Enriches the streaming data daily with CoinGecko market metadata via Airflow
Transforms everything with dbt Cloud
Serves a live, auto-refreshing Grafana dashboard — publicly accessible

Total cloud cost: ~$0.01/month.

Here's how I built it.

The Problem I Was Solving

Crypto market data has two distinct tempos:

Real-time (milliseconds to seconds): Price ticks from exchanges. You need stream processing here — you can't batch-load ticks after the fact and call it "live." The data volume is too high to dump raw into a warehouse row by row.

Daily (slow-moving context): Market cap, trading volume, rankings, OHLC candlesticks. These change once a day. You don't need Flink for this — a simple scheduled API call works fine.

The interesting engineering challenge is: how do you join these two tempos cheaply and reliably?

The naive answer — stream everything into BigQuery using the Streaming Insert API — costs money at scale. The smarter answer is what I built: stream to GCS first, batch-load to BigQuery for free, and join the two lanes in dbt.

Architecture Overview

Before diving in, here's the full picture:

STREAMING LANE
Binance WebSocket → Python Producer → Redpanda → PyFlink → GCS → BigQuery
                    (real-time ticks)  (Kafka)  (windows)  (JSONL) (free load)

BATCH LANE  
CoinGecko API → Airflow DAG (7 tasks) → GCS → BigQuery
                (daily @ 06:00 UTC)    (Parquet)  (free load)

TRANSFORMATION LAYER
BigQuery → dbt Cloud → crypto_staging.* → crypto_mart.*
           (daily @ 07:00 UTC)

VISUALIZATION
BigQuery → Grafana Cloud → 6 panels, auto-refresh 30s
           (BigQuery plugin, free)

Everything runs locally in Docker except the data warehouse (BigQuery) and transformation/visualization layers (dbt Cloud + Grafana Cloud). This is the "local-to-cloud" pattern — keep compute on your machine, use GCP only for storage and analytics.

The Streaming Lane: Binance → Redpanda → PyFlink → GCS

Why Binance Instead of CoinCap?

I originally planned to use CoinCap's WebSocket feed. Halfway through the build, I discovered CoinCap now requires an API key even for the free tier. Binance's WebSocket, however, is completely open — no account, no key, no rate limits on the trade stream.

The URL pattern is:

wss://stream.binance.com:9443/stream?streams=btcusdt@trade/ethusdt@trade/solusdt@trade/bnbusdt@trade/adausdt@trade

Each message looks like:

{
  "stream": "btcusdt@trade",
  "data": {
    "s": "BTCUSDT",
    "p": "76034.12",
    "q": "0.00150000",
    "T": 1776875894886
  }
}

The Python Producer

The producer is a Docker container running websockets + confluent-kafka. It connects to Binance, maps Binance pair names to readable coin names (BTCUSDT → bitcoin), and publishes JSON messages to a Redpanda topic:

message = {
    "symbol": "bitcoin",
    "price_usd": 76034.12,
    "event_timestamp": "2026-04-23T18:31:34+00:00"
}
producer.produce(topic="crypto-prices", key="bitcoin", value=json.dumps(message))

Why Redpanda Instead of Kafka?

Kafka requires Zookeeper. In a local Docker setup, that's three containers just for the message broker (Zookeeper, Kafka broker, Schema Registry if you want it). Redpanda is Kafka-API compatible — meaning PyFlink's Kafka connector works with it unchanged — but runs as a single binary, no Zookeeper. It starts in under 3 seconds and uses ~512MB RAM instead of Kafka's 2-3GB.

The PyFlink Job

The Flink job does the heavy lifting. It consumes from Redpanda, applies a 1-minute tumbling event-time window per coin symbol, and computes:

avg_price — average price within the window
min_price / max_price — price range
price_stddev — standard deviation (volatility proxy)
open_price / close_price — first and last tick
record_count — number of ticks received

Event-time windows (as opposed to processing-time) are important here. They use the timestamp embedded in the Binance message, so late-arriving messages (within a 10-second watermark) land in the correct window rather than the current one.

The output of each completed window is a JSONL file written to GCS:

gs://coinpulse-data-lake/streaming/2026/04/23/18/stream_bitcoin_20260423_183134.jsonl

A scheduled BigQuery Load Job (free) then loads these files into crypto_raw.stream_prices, partitioned by HOUR on event_timestamp and clustered by symbol.

The key insight here: I never use BigQuery's Streaming Insert API, which charges ~$0.01 per 200MB. Load Jobs are completely free. The data is 1 minute old in the warehouse instead of 0 seconds old — but for a dashboard refreshing every 30 seconds, nobody notices.

The Batch Lane: CoinGecko → Airflow → GCS → BigQuery

The Airflow DAG

The batch pipeline runs as a 7-task Airflow DAG at 06:00 UTC daily with two parallel branches:

fetch_coingecko → transform_to_parquet → upload_to_gcs → load_to_bigquery
fetch_ohlc      → upload_ohlc_to_gcs   → load_ohlc_to_bigquery

Markets branch hits CoinGecko's /coins/markets endpoint for all 5 coins in a single call, getting current price, market cap, 24h volume, price change percentage, rank, and fully diluted valuation.

OHLC branch hits /coins/{id}/ohlc?days=1 for each coin, getting candlestick arrays [timestamp, open, high, low, close].

Both branches serialize data to typed Parquet (using explicit schema definitions, not autodetect — more on why this matters below) and upload to GCS before loading to BigQuery.

The Schema War (And How I Won It)

This took me longer than I'd like to admit. The root cause: PyArrow (used by Pandas .to_parquet()) infers Python datetime objects as TIMESTAMP in Parquet metadata. But when BigQuery's autodetect reads a datetime.isoformat() string, it creates the column as STRING. When the same column arrives as a proper datetime object in the next run, BigQuery rejects the load because TIMESTAMP ≠ STRING.

The permanent fix: define explicit schemas in the BigQuery Load Job config and force all string columns to str dtype in Pandas before writing Parquet:

df["snapshot_date"] = df["snapshot_date"].astype(str)
df["ingested_at"]   = df["ingested_at"].astype(str)

job_config = bigquery.LoadJobConfig(
    source_format=bigquery.SourceFormat.PARQUET,
    schema=MARKETS_SCHEMA,  # explicit, no autodetect
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
)

Once I stopped fighting autodetect and defined schemas explicitly, the pipeline ran clean for weeks.

The Transformation Layer: dbt Cloud

Why dbt Cloud Over dbt Core

I already had Redpanda, Flink, Airflow, and PostgreSQL running locally — approximately 13GB RAM. Adding a dbt Core Docker container would push that higher for no functional gain. dbt Cloud's free developer plan gives a managed scheduler, visual lineage graph, and test runner at zero cost and zero local RAM.

The Model Structure

Staging layer (views, zero storage cost):

stg_stream_prices — cleans and type-casts streaming data, filters nulls and zero prices
stg_coingecko_markets — adds market_cap_category (large/mid/small/micro cap) derived column
stg_ohlc_candles — computes candle_range, candle_change_pct, and candle_direction (bullish/bearish)

Mart layer (incremental tables, insert_overwrite):

mart_crypto_prices — joins streaming aggregations with latest daily market metadata
mart_volatility — computes hourly composite_volatility_score combining streaming stddev with OHLC candle metrics

The mart models use insert_overwrite with hour-level partitioning and an incremental filter that scans only the last 2 hours of streaming data per run. This means dbt never full-scans the streaming table — it touches only the partitions it needs to update.

The dbt Deploy Job

A scheduled dbt Cloud production environment job runs daily at 07:00 UTC — one hour after Airflow — ensuring mart tables always have fresh data before the dashboard refreshes.

After some initial failures (a common dbt Fusion syntax issue with accepted_values requiring arguments: nesting), the deploy job has run cleanly every day since April 19, 2026. Each run completes in under 45 seconds.

The Dashboard: Grafana Cloud

Why Grafana Over Looker Studio

Looker Studio is the natural choice for BigQuery — it has a native connector and no setup friction. But it has one fatal flaw for streaming analytics: it doesn't auto-refresh.

Grafana Cloud's free tier includes the BigQuery plugin (one-click install, no credit card), supports 30-second auto-refresh, has proper time series panels with logarithmic Y-axis (essential when plotting BTC at $76K alongside ADA at $0.25 on the same chart), and produces significantly more polished visualizations.

My prior projects (Sovereign Debt Observatory, Tech Ecosystem Observatory) both used Looker Studio, so using Grafana here also demonstrates range across tooling.

The 6 Dashboard Panels

Panel 1 — Live Crypto Prices (Streaming)
A treemap showing the latest price for each coin from crypto_raw.stream_prices, colour-coded green/red by price direction. BTC at $76K, ETH at $2.3K, SOL at $86, BNB at $630, ADA at $0.249.

Panel 2 — Price Trend Over Time (Streaming)
A time series chart with logarithmic Y-axis showing avg_price per 1-minute window for all coins. The log scale handles the 300,000x price spread between BTC and ADA.

Panel 3 — Hourly Volatility Score (Streaming)
A gradient bar chart (green → red) showing the composite_volatility_score per coin from mart_volatility. Cardano leads at 0.566 (most volatile relative to its price), Binance Coin lowest at 0.264.

Panel 4 — Market Cap Rankings (Batch)
BTC dominates at $1.57T, ETH at $289B. The scale difference is immediately visible and gives context to the streaming data.

Panel 5 — Daily OHLC Candles (Batch)
A table panel showing raw candle data from coingecko_ohlc_candles with candle_direction colour-coded — bearish rows highlighted in red.

Panel 6 — 24h Price Change % (Batch)
ADA leads at +0.563%, ETH is the only negative at -0.132%.

Public URL: https://derrickryangiggs.grafana.net/public-dashboards/81560968e15140f08f65b52d78a4b252

Infrastructure: Terraform for Everything

All GCP resources are defined in Terraform — nothing clicked in the console. Running terraform apply provisions:

GCP project with billing account linked
Required APIs (BigQuery, BigQuery Storage, Cloud Storage, Resource Manager)
Service account with least-privilege IAM roles (BQ Data Editor, BQ Job User, GCS Object Admin)
GCS bucket with 90-day lifecycle rule
BigQuery datasets and tables with explicit schemas, partitioning, and clustering

Nothing is hardcoded. All values flow from .env via TF_VAR_ prefixed variables. This is non-negotiable — hardcoded project IDs in .tf files are a code review failure waiting to happen.

The Cost Breakdown (For Real)

Service	Details	Monthly Cost
Redpanda	Local Docker	$0.00
PyFlink	Local Docker	$0.00
Apache Airflow	Local Docker	$0.00
Google Cloud Storage	~200MB of Parquet + JSONL	~$0.01
BigQuery	<1GB data, Load Jobs only	~$0.00
dbt Cloud	Developer plan	$0.00
Grafana Cloud	Free tier	$0.00
Total		~$0.01/month

The $0.01 is literally the GCS storage bill. Everything else is free.

The three decisions that make this possible:

Use GCS + Load Jobs instead of BigQuery Streaming Inserts. Streaming Inserts charge per row. Load Jobs are free. The trade-off is 1-minute data freshness instead of real-time — acceptable for a dashboard refreshing every 30 seconds.
Run compute locally. Cloud Composer (managed Airflow) starts at $300/month. Dataproc (managed Flink) is similarly expensive. Running on your own machine costs nothing except electricity.
Use free tiers everywhere else. dbt Cloud developer plan, Grafana Cloud free tier, CoinGecko Demo API free tier, Binance WebSocket (no auth required). Every external service in this pipeline has a genuinely free tier sufficient for this workload.

Hard-Won Lessons

1. Flink's WindowFunction.apply() signature changed in 2.2.0
In PyFlink 1.18, apply(self, key, window, inputs, collector) uses a collector argument. In 2.2.0, the signature is apply(self, key, window, inputs) and you use yield instead. This isn't documented prominently. I lost an afternoon to this.

2. Read env vars inside functions, not at module import time
My Airflow DAG originally read GCS_BUCKET_NAME = os.environ["GCS_BUCKET_NAME"] at the module level. When Airflow's scheduler reloads the module after a container restart, GCS_BUCKET_NAME is empty — the env var isn't available at import time in the scheduler process. Moving all os.environ reads inside the task functions fixed this permanently.

3. Never use autodetect for BigQuery Load Jobs if you care about schema stability
autodetect=True infers schema from the first file it sees. If that file has a datetime object, BQ creates a TIMESTAMP column. If the next file has an .isoformat() string, BQ rejects it as a STRING / TIMESTAMP mismatch. Define explicit schemas in the LoadJobConfig and cast DataFrame columns explicitly before writing Parquet.

4. The Flink TaskManager won't connect if jobmanager.rpc.address in config.yaml doesn't match your Docker container name
The baked-in config.yaml in the PyFlink image has jobmanager.rpc.address: jobmanager. My container is named coinpulse-flink-jobmanager. The FLINK_PROPERTIES env var doesn't always override config.yaml. The fix: add a sed command in the Dockerfile to rewrite the address in config.yaml directly.

5. Redpanda's rpk topic create command arguments must be on one line
Multi-line bash in Docker Compose entrypoint with > YAML block scalar doesn't handle line continuations the way you'd expect. The --brokers, --partitions, and --replicas flags ended up being interpreted as separate commands. Fix: use ["bash", "-c", "single long command string"] format instead.

What I'd Add Next (The "Circuit Breaker" Enhancements)

After sharing the project, I got feedback suggesting three production-grade improvements. Here's my assessment:

1. dbt-expectations data contracts
Add guardrails so the pipeline fails loudly if bad data arrives — negative prices, null coin symbols, impossible price spikes. In dbt, this is two lines per test in your schema YAML. The dbt-expectations package brings expect_column_values_to_be_between and similar assertions. Highly recommended, zero cost.

2. BigQuery ML ARIMA_PLUS price forecasting
Train a time series model directly in BigQuery SQL to forecast the next 7 days of prices per coin. The first 10GB of CREATE MODEL data processed monthly is free — both projects' datasets are well under that threshold, making this effectively free. It turns the dashboard from "here's what happened" into "here's what might happen."

3. GitHub Actions CI/CD for Terraform
A terraform plan on every PR to the repo, ensuring infrastructure changes are reviewed before applying. Standard practice in production environments, takes about 20 lines of GitHub Actions YAML to implement.

The Full Stack, Summarised

Layer	Tool	Why
IaC	Terraform	Reproducible, version-controlled infrastructure
Message Broker	Redpanda	Kafka-compatible, no Zookeeper, runs in one container
Stream Processing	PyFlink 2.2.0	Stateful windowed aggregations, event-time semantics
Orchestration	Apache Airflow	Full end-to-end DAG, retry logic, monitoring
Object Storage	GCS	Staging layer between compute and warehouse
Data Warehouse	BigQuery	Serverless, free Load Jobs, columnar, partitioned
Transformations	dbt Cloud	Incremental models, lineage graph, scheduled runs
Visualization	Grafana Cloud	Auto-refresh, BigQuery plugin free on all tiers

Try It Yourself

The full project is open source:

GitHub: https://github.com/Derrick-Ryan-Giggs/coinpulse

Live Dashboard: https://derrickryangiggs.grafana.net/public-dashboards/81560968e15140f08f65b52d78a4b252

The README has step-by-step reproduction instructions — clone, fill in .env, run terraform apply, start the Docker stacks, and you have a running pipeline within about 20 minutes. All you need is a GCP account (free tier works), a CoinGecko Demo API key (free, no card), dbt Cloud account (free developer plan), and Grafana Cloud account (free forever).

What This Project Taught Me

Building this reinforced something I suspected but didn't fully appreciate: the hardest problems in data engineering aren't the algorithms — they're the plumbing.

Getting PyFlink to connect to a Redpanda broker with the right container hostname was a day of debugging. Getting the Airflow DAG to read env vars correctly on scheduled runs (not just manual triggers) was another. Getting BigQuery to accept the same schema consistently across runs required understanding how PyArrow serialises Python types to Parquet column metadata.

None of these problems are glamorous. But solving them is what separates a "pipeline that worked once on my laptop" from a "pipeline that runs reliably every day."

That's what CoinPulse is — a pipeline that runs reliably every day, for $0.01/month.

If you found this useful or have questions about any part of the implementation, feel free to reach out. I write regularly about data engineering, cloud infrastructure, and GCP on Medium, Dev.to, and Hashnode.

Oracle GoldenGate 23ai: Powering Distributed AI with Real-Time Data Replication

Ryan Giggs — Sat, 18 Apr 2026 11:10:46 +0000

The database world has always needed a reliable bridge between operational systems and analytical or AI workloads. For decades, Oracle GoldenGate has served that role — silently moving terabytes of transactional changes across databases, clouds, and continents with minimal latency and near-zero impact on source systems. With the release of GoldenGate 23ai in 2024, that bridge now carries a new payload: AI vector embeddings.

This post covers GoldenGate's core use cases, the Microservices Architecture that underpins modern deployments, and the powerful new distributed AI capabilities that make GoldenGate 23ai a critical component of enterprise GenAI pipelines.

What Is Oracle GoldenGate?

At its heart, GoldenGate is a Change Data Capture (CDC) and real-time replication engine. Rather than querying the source database directly — which would impose load — GoldenGate reads the database's redo/transaction logs, captures only the changed deltas (inserts, updates, deletes), and delivers them to one or more target systems with sub-second latency.

This log-based approach makes GoldenGate extremely low-impact on production systems while supporting highly demanding replication topologies across heterogeneous environments: Oracle to PostgreSQL, Oracle to Kafka, MySQL to BigQuery, and many more.

Common GoldenGate Use Cases

GoldenGate's flexibility makes it applicable across a broad range of enterprise scenarios:

1. Multi-Active, High Availability, and Cross-Region Deployments

GoldenGate enables active-active (bidirectional) replication, where multiple database instances in different regions accept both reads and writes simultaneously. Built-in conflict detection and resolution ensures data consistency across sites. This architecture powers mission-critical applications that require zero planned downtime and regional failover capabilities — with GoldenGate's ExaPortMon-style awareness ensuring continuous replication even under network disruptions.

2. Data Offloading and Data Hub

Rather than running analytical queries against production OLTP databases, GoldenGate streams only the changed data to a dedicated analytics database, data warehouse, or data lake in real time. This eliminates the traditional "nightly CSV batch" pattern and gives analysts access to data that is seconds, not hours, behind production.

3. Migrations and Upgrades

GoldenGate is the gold standard for zero-downtime database migrations — moving from on-premises to cloud, upgrading Oracle Database versions, or switching from Oracle to PostgreSQL. The approach: synchronize old and new systems in parallel using GoldenGate, then cut over the application connection once replication lag reaches zero. Even migrations of hundreds of terabytes are reduced to a switchover window measured in minutes.

4. Analytics Data Feeds

GoldenGate feeds real-time change streams into analytics platforms — data warehouses, Apache Kafka topics, Oracle Streaming Service, Confluent Cloud, and cloud object stores. This underpins event-driven architectures and real-time dashboards that would be impossible with batch-based ETL.

5. Stream Analytics

With GoldenGate Stream Analytics, change events flowing through GoldenGate can be enriched, filtered, and analyzed in motion — before they land in a target system. Integrations with Oracle Machine Learning (OML) and ONNX (Open Neural Network Exchange) enable actionable AI/ML directly from streaming pipelines, turning raw CDC events into predictive signals.

GoldenGate Microservices Architecture

When Oracle introduced the Microservices Architecture (MA) in GoldenGate 12.3 (August 2017), it represented a fundamental modernization of how GoldenGate is deployed, managed, and integrated. The classic command-line-driven architecture (GGSCI) was complemented by a fully API-driven, web-based platform.

Five Core Components

The Microservices Architecture is built around five independently manageable services:

Service Manager — acts as a watchdog, managing and monitoring all other services within a deployment
Administration Server — the central control entity for managing Extract, Replicat, and all replication processes
Distribution Server — a high-performance data distribution agent that routes trail files to one or more targets over HTTPS/WebSockets
Receiver Server — handles all incoming trail files from remote Distribution Servers
Performance Metrics Server — collects and exposes real-time performance data for all GoldenGate processes via REST API or JSON

Key Benefits of Microservices Architecture

REST API-driven management: Every GoldenGate operation — starting a replicat, checking lag, configuring a pipeline — is exposed via a standardized REST API, enabling automation with Python, Terraform, and CI/CD pipelines.
Renewed web UI in 23ai: GoldenGate 23ai ships with a completely redesigned graphical interface, with more logical, guided assistant steps for setting up integrations — a marked improvement over the previous look and feel.
TLS encryption and OAuth 2.0 authentication out of the box, with integration into external identity providers.
Simpler patching and upgrades: Each microservice can be updated with minimal disruption. Upgrading from GoldenGate 21c to 23ai, for example, requires only downloading the new software and updating the OGG_HOME directory parameters.
Deployment flexibility: Runs on-premises, as a fully managed service in OCI (OCI GoldenGate), or on third-party clouds including AWS, Azure, and GCP.

GoldenGate Version Upgrade Path

For teams still running older GoldenGate versions, the supported upgrade sequence through the modern microservices era is:

12.1 → 12.2 → 12.3 (Microservices introduced) → 18c → 19c → 21c → 23ai → 26ai (next LTS)

Note: Classic Architecture support for Oracle Database is deprecated in 23ai. All new deployments should use Microservices Architecture. GoldenGate 26ai has been announced as the next Long-Term Support (LTS) release, with further AI and Data Mesh enhancements planned.

Distributed AI Processing with Vector Replication

This is where GoldenGate 23ai breaks genuinely new ground. The release adds first-class support for the Oracle Database 23ai VECTOR data type — the foundational building block of AI Vector Search and RAG (Retrieval-Augmented Generation) pipelines.

What Is a Vector Embedding?

When AI models process documents, images, or other unstructured data, they convert that content into vector embeddings — high-dimensional numerical representations that encode semantic meaning. These vectors are stored in vector databases and searched using similarity algorithms (cosine distance, dot product, Euclidean distance) to retrieve contextually relevant content.

Keeping vector stores synchronized with the operational databases that hold the source data is a significant engineering challenge — and exactly what GoldenGate 23ai solves.

Key Vector Replication Capabilities

Migrate Vectors into Oracle Vector Database Without Downtime

GoldenGate can migrate vector embeddings stored in one database into Oracle Database 23ai (or Oracle AI Database 26ai) without any service interruption. This enables seamless transitions from on-premises environments to OCI, or between cloud providers, while keeping AI-powered applications running continuously.

Replicate and Consolidate Vector Changes in Real Time

As source data changes — a customer record is updated, a product description is revised — GoldenGate captures those changes and propagates updated vectors to all target vector stores in real time. This ensures that RAG pipelines are always querying fresh, consistent embeddings rather than stale snapshots.

Heterogeneous Vector Replication

GoldenGate 23ai can replicate vectors both homogeneously (Oracle to Oracle) and heterogeneously across different vector stores, provided the embedding algorithm, type, and dimension are consistent across source and target. If the source and target use different embedding algorithms, GoldenGate can replicate the raw source data instead, allowing the target system to re-vectorize it with its own model.

Support extends beyond Oracle: GoldenGate 23ai also supports capture and delivery of the pgvector extension for PostgreSQL and its derivatives, as well as the VECTOR datatype for MySQL 9.0+.

Multi-Cloud, Multi-Active Oracle Vector Database

GoldenGate enables active-active vector database architectures spanning multiple cloud regions or providers. AI applications in different regions can write to and read from local vector stores while GoldenGate keeps all instances synchronized — enabling both low-latency AI inference and high availability for vector workloads.

Stream Changes to Search Engines

GoldenGate can stream vector and document changes in real time to Elasticsearch or OpenSearch compatible search indexes, enabling hybrid semantic + keyword search architectures without manual synchronization pipelines.

GoldenGate Data Streams: Pub/Sub for Database Events

A major new capability in GoldenGate 23ai is the Data Streams service, which provides simplified, event-driven access to database change records via AsyncAPI over WebSocket connections. This enables developers to subscribe to database change events — including vector updates — in a pub/sub pattern, without needing to manage trail files or configure traditional Replicat processes. Combined with Oracle Database 23ai's JSON Relational Duality views, this means document-side changes can be captured and propagated as structured events in real time.

Generative AI with Your Own Business Data

GoldenGate 23ai directly enables three patterns for building AI applications on private enterprise data:

1. From-Scratch LLMs (Custom Foundation Models)

Organizations with truly unique proprietary datasets — medical records, legal case files, financial transaction histories — may opt to train custom Large Language Models from the ground up on that data. GoldenGate ensures the training datasets fed into these models are continuously fresh and complete, sourced in real time from operational databases rather than periodic exports.

This is an advanced, resource-intensive approach suited to well-resourced organizations with data assets that are sufficiently distinctive to justify the investment.

2. Fine-Tuned LLMs (Domain Adaptation)

A more practical approach for most enterprises: take a pre-trained foundational LLM (such as Llama, Mistral, or an OCI Generative AI model) and fine-tune it on a private domain dataset. GoldenGate replicates the relevant operational data into a training pipeline, keeping the fine-tuned model aligned with current business reality as data evolves over time.

3. RAG (Retrieval-Augmented Generation) — The Most Common Pattern

RAG is currently the dominant architecture for enterprise GenAI. Instead of baking private knowledge into model weights (which requires expensive retraining), RAG retrieves relevant documents at query time and injects them into the LLM's prompt as context.

GoldenGate 23ai is purpose-built to power better RAG pipelines:

Keeps vector stores current: As source data changes, GoldenGate propagates those changes to the vector index within seconds — not hours.
Enables a consolidated vector hub: Rather than maintaining separate, potentially inconsistent vector stores for each application, GoldenGate can consolidate vectors from multiple source databases into a single Oracle AI Database vector hub.
Reduces hallucination risk: Stale vector indexes are one of the primary causes of LLM hallucinations in RAG systems. Fresh data = more accurate, grounded responses.
Supports heterogeneous sources: GoldenGate can pull data from Oracle, PostgreSQL, MySQL, SQL Server, DB2, and more — vectorize it, and deliver it to Oracle AI Database, all in a unified pipeline.

Why GoldenGate 23ai for AI: The Summary

Capability	Impact
Real-time vector replication	RAG pipelines query fresh embeddings, not stale snapshots
Zero-downtime vector migration	Move AI workloads to cloud without service interruption
Multi-cloud active-active vectors	Low-latency AI inference globally with high availability
Heterogeneous source support	Unify vectors from Oracle, PostgreSQL, MySQL, and more
Data Streams (AsyncAPI)	Event-driven AI pipelines without complex trail file management
Stream to search engines	Elasticsearch/OpenSearch integration for hybrid search
Fine-tuning data pipelines	Keep domain-adapted LLMs current with live business data

The underlying thesis is the same one Oracle argues for Exadata AI Storage: bring AI to the data, not data to the AI. GoldenGate 23ai extends this philosophy to the replication layer — ensuring that wherever your AI application runs, it has access to consistent, real-time data from your authoritative operational systems.

As enterprise AI moves from experimentation to production, the quality of data pipelines will increasingly determine the quality of AI outcomes. GoldenGate 23ai is Oracle's answer to that challenge.

Are you using GoldenGate for AI pipelines or RAG architectures? What challenges have you run into keeping vector stores synchronized? Share in the comments below.

Building the Sovereign Debt Observatory: An End-to-End ELT Pipeline on World Bank Debt Data for Low and Middle-Income Countries

Ryan Giggs — Thu, 09 Apr 2026 10:02:52 +0000

Introduction

Global sovereign debt is one of the most consequential datasets in existence. It shapes foreign policy, determines credit ratings, drives IMF bailout decisions, and affects the daily lives of billions of people in developing countries. The World Bank publishes this data openly — 130+ countries, 27 years of history, updated quarterly — yet there is no ready-made analytical layer on top of it.

If you want to answer a question like "which African countries have the highest ratio of private nonguaranteed debt to total external debt, and how has that changed since 2010?", you have to manually download Excel files from multiple World Bank portals, clean inconsistent column names, handle missing values, and stitch everything together in a spreadsheet. Every time the data updates, you do it again.

That is the problem this project solves.

The Sovereign Debt Observatory is an end-to-end ELT pipeline that ingests World Bank external debt data, lands it in a cloud data lake, transforms it in BigQuery using dbt Cloud, orchestrates everything quarterly with Apache Airflow, and surfaces the answers in a Looker Studio dashboard.

This article walks through every layer of the pipeline — the architecture decisions, the technical challenges, and how I solved them.

The Five Questions This Pipeline Answers

Before writing a single line of code, I defined the analytical questions the pipeline needed to answer. This kept every decision grounded in purpose rather than technology for its own sake.

How is gross external debt distributed across public, publicly guaranteed, private nonguaranteed, and multilateral sectors per country?
Which countries carry the highest short-term external debt exposure and how has that changed since 2010?
What share of external debt is foreign-currency denominated and where is that ratio worsening?
How has regional external debt stock evolved from 1998 to 2025 across Africa, Latin America, East Asia, South Asia, Europe and Central Asia, and the Middle East?
Which countries face the heaviest debt service pressure relative to their total debt position?

Architecture Overview

The pipeline follows a modern ELT pattern — extract and load first, transform inside the warehouse.

World Bank API (IDS source 2)
        |
        v
PySpark job (Docker container)
        |
        v
Google Cloud Storage — raw Parquet, partitioned by extracted_date
        |
        v
BigQuery external tables (raw dataset)
        |
        v
dbt Cloud — staging views + mart tables
        |
        v
Looker Studio dashboard
        |
Orchestrated by Apache Airflow on Docker Compose
Infrastructure provisioned by Terraform

Why ELT and not ETL?

In ETL, transformation happens before loading — your Spark job does the cleaning, aggregation, and business logic before writing to the warehouse. In ELT, raw data lands untransformed and the warehouse does all the heavy lifting.

For this project, ELT is the right choice for three reasons. First, raw data is preserved in GCS indefinitely — if analytical requirements change six months from now, I just write a new dbt model without re-running the ingestion layer. Second, BigQuery is optimized for analytical SQL transformations at scale — it is far better at this than PySpark running in a Docker container on a local machine. Third, dbt gives us version-controlled, tested, documented transformations that are readable by anyone with SQL knowledge.

PySpark's job in this pipeline is purely mechanical: hit the API, paginate, write Parquet. No business logic. No aggregations. Pure extract and load.

Data Sources

International Debt Statistics (IDS) — World Bank source 2

The IDS database is the flagship World Bank debt dataset. It covers external debt stocks and flows for low and middle income countries, with annual data going back to 1998. I access it through the wbgapi Python library, which wraps the World Bank Indicators API v2.

I ingest nine series:

Series Code	Description
DT.DOD.DECT.CD	Total external debt stocks
DT.DOD.DLXF.CD	Long-term external debt
DT.DOD.DPNG.CD	Private nonguaranteed debt
DT.DOD.MIBR.CD	PPG IBRD loans
DT.DOD.DPPG.CD	Public and publicly guaranteed debt
DT.DOD.DIMF.CD	IMF credit
DT.DOD.PVLX.CD	Present value of external debt
DT.DOD.MWBG.CD	IBRD loans and IDA credits
DT.DOD.MIDA.CD	PPG IDA loans

One important lesson from building this: the World Bank has multiple API source databases, and not all of them support the standard wbgapi query format. Sources 22 (QEDS SDDS), 23 (QEDS GDDS), and 54 (JEDH) all return JSON decode errors when queried programmatically — they use a separate DataBank backend. Only source 2 (IDS) reliably supports the Indicators API. This cost me several hours of debugging.

Quarterly External Debt Statistics SDDS (QEDS)

QEDS provides quarterly debt payment schedule data broken down by sector and maturity. Unlike IDS, QEDS does not support programmatic API access in the standard format. The World Bank provides bulk Excel downloads for each supplementary table instead.

I download five Excel files directly:

Table 1.5 — Net external debt position by sector
Table 3 — Debt service payment schedule by sector
Table 3.2 — Debt service by sector and instrument
Table 2.1 — Foreign currency and domestic currency debt
Table 1.6 — Reconciliation of positions and flows

Infrastructure as Code with Terraform

Every GCP resource in this project is provisioned by Terraform. The three core resources are a GCS bucket for the data lake and three BigQuery datasets — raw, staging, and mart.

resource "google_storage_bucket" "data_lake" {
  name          = var.gcs_bucket_name
  location      = var.region
  force_destroy = true

  lifecycle_rule {
    action { type = "Delete" }
    condition { age = 90 }
  }

  versioning { enabled = true }
}

resource "google_bigquery_dataset" "raw" {
  dataset_id                 = "raw"
  location                   = var.location
  delete_contents_on_destroy = true
}

The 90-day lifecycle rule on GCS automatically deletes old partitions, keeping storage costs near zero. The entire GCP footprint for this project costs less than $0.05 per month — BigQuery's free tier covers 1 TB of queries and 10 GB of storage, which is far more than this dataset requires.

A setup_gcp.sh script handles the one-time bootstrap — creating the GCP project, enabling APIs, creating the service account, and granting IAM roles. The billing account ID is passed as an environment variable so it never appears in version-controlled files.

BILLING_ACCOUNT=your-billing-id bash scripts/setup_gcp.sh

Ingestion Layer — PySpark on Docker

JEDH / IDS Ingestion

The IDS ingestion script uses wbgapi to fetch all nine series across all available countries from 1998 to 2025. The API returns data in wide format — one row per country per series, with year columns as separate fields. PySpark writes this to GCS as Snappy-compressed Parquet, partitioned by extraction date.

One critical issue I hit: the World Bank API returns year columns as bare integers (1998, 1999, etc.). BigQuery rejects column names that start with numbers. The fix was to prefix all year columns with year_ before writing:

combined.columns = [
    f"year_{col}" if str(col).isdigit() else col
    for col in combined.columns
]

This produces clean column names like year_1998, year_1999 that BigQuery accepts without complaint.

QEDS Ingestion

The QEDS ingestion downloads five Excel files from World Bank DataBank, reads all sheets from each file using pandas.read_excel, and concatenates them into a single DataFrame.

The Excel files have messy column names — spaces, brackets, special characters. A clean_column_name function normalizes everything:

def clean_column_name(col):
    col = str(col)
    col = re.sub(r'\s*\[.*?\]', '', col)  # remove [YR2021Q4] suffixes
    col = col.strip()
    col = re.sub(r'[^a-zA-Z0-9_]', '_', col)
    col = re.sub(r'_+', '_', col)
    col = col.strip('_')
    if col and col[0].isdigit():
        col = 'q_' + col  # prefix quarter columns: q_2021q4
    return col.lower()

I also hit an OOM error trying to write the QEDS data through PySpark — 76,835 rows with 90+ columns across 214 sheets was too much for the JVM heap in the Docker container. The fix was to bypass Spark entirely for QEDS and write directly to GCS using the google-cloud-storage Python client:

buffer = io.BytesIO()
df.to_parquet(buffer, index=False, engine="pyarrow")
buffer.seek(0)

client = storage.Client()
bucket = client.bucket(GCS_BUCKET)
blob = bucket.blob(output_path)
blob.upload_from_file(buffer, content_type="application/octet-stream")

No JVM, no Spark executor, no OOM. The lesson: use the right tool for the job. PySpark is excellent for large distributed datasets. For a 76K-row DataFrame from an Excel file, plain pandas and the GCS Python client is simpler and more reliable.

Docker Image

The ingestion image is built on eclipse-temurin:17-jdk-jammy rather than a plain Python image. This is because PySpark requires Java, and the python:3.11-slim base image uses Debian Trixie which does not carry openjdk-17-jdk in its default repositories. The Temurin image ships Java 17 out of the box, which is exactly what Spark 3.5.1 needs.

The GCS connector JAR is downloaded at build time:

RUN curl --progress-bar -L \
    https://storage.googleapis.com/hadoop-lib/gcs/gcs-connector-hadoop3-latest.jar \
    -o ${SPARK_HOME}/jars/gcs-connector-hadoop3-latest.jar

Credentials are never baked into the image. They are mounted at runtime as a volume:

docker run --rm \
  -v /path/to/key.json:/app/credentials/key.json \
  -e GOOGLE_APPLICATION_CREDENTIALS=/app/credentials/key.json \
  sovereign-debt-ingestion:v1 python3 extract_jedh.py

Orchestration with Apache Airflow

Airflow runs on Docker Compose using the official apache/airflow:2.9.2 image. The stack includes a Celery executor with Redis as the message broker and Postgres as the metadata database.

The DAG runs quarterly — on the 1st of January, April, July, and October at 06:00 UTC:

schedule_interval="0 6 1 1,4,7,10 *"

Task flow:

extract_load_jedh >> extract_load_qeds

Both tasks use the DockerOperator to spin up the ingestion container on the host machine, which means Airflow itself does not need PySpark, Java, or any data dependencies — it just tells Docker to run the job.

The Docker socket problem

The DockerOperator communicates with the host Docker daemon through the Unix socket at /var/run/docker.sock. This requires two things: the socket must be mounted into the Airflow worker container, and the worker must have permission to use it.

The socket mount goes in docker-compose.yml under the common volumes section:

volumes:
  - /var/run/docker.sock:/var/run/docker.sock

The permission fix requires adding the Docker group ID to the Airflow worker. On my machine the Docker group ID is 984:

group_add:
  - "984"

Without the group add, the worker can see the socket but gets PermissionError(13, 'Permission denied'). This is a common Airflow + Docker-in-Docker gotcha that took several debugging sessions to resolve.

Transformation with dbt Cloud

Staging Layer

The staging models are materialized as views — no storage cost, no latency, just a SQL lens over the raw external tables.

stg_jedh does the heavy lifting: it unpivots the wide year-column format into long format using BigQuery's UNPIVOT operator:

unpivot(
    value for year in (
        year_1998, year_1999, year_2000, ..., year_2025
    )
)

Then extracts the year integer from the column name:

cast(replace(year, 'year_', '') as int64) as year

And adds a human-readable series description via a CASE statement, turning DT.DOD.DECT.CD into Total external debt stocks.

The result is a clean long-format table: one row per country per series per year.

stg_qeds is simpler — it selects the clean columns, uses SAFE_CAST to convert quarter values to float64, and filters out null countries and series codes. SAFE_CAST is preferable to CAST here because it returns NULL on failure rather than throwing an error, which is the right behaviour for messy Excel data.

Mart Layer

The mart models are materialized as tables with partitioning and clustering for query efficiency.

mart_debt_stocks is partitioned by year and clustered by country_code and series_code. It enriches the staging data with YoY percentage change and debt as a percentage of total external debt per country per year — both computed using window functions:

lag(b.debt_value_usd) over (
    partition by b.country_code, b.series_code
    order by b.year
) as prev_year_value,

safe_divide(
    b.debt_value_usd - lag(b.debt_value_usd) over (
        partition by b.country_code, b.series_code
        order by b.year
    ),
    lag(b.debt_value_usd) over (
        partition by b.country_code, b.series_code
        order by b.year
    )
) * 100 as yoy_change_pct

mart_regional_debt assigns countries to six World Bank regions using a CASE statement on ISO3 country codes, then aggregates total, average, max, and min debt stocks per region per series per year. This powers the regional trajectory time series on the dashboard.

mart_debt_service computes total annual debt payments and average quarterly payments per country from the QEDS payment schedule data, enabling debt service pressure analysis.

dbt Tests

All models have not_null tests on primary dimension columns. Running dbt test after every model change ensures data quality is enforced at the transformation layer rather than discovered downstream in the dashboard.

The Dashboard

The Looker Studio dashboard has two pages, both connected directly to the BigQuery mart tables.

Page 1 — Global Debt Overview answers Q4 at a glance. A time series chart using mart_regional_debt shows six regional debt trajectories from 1998 to 2024. Africa's trajectory is notably steeper post-2010. A bar chart shows the top 20 countries by total external debt for the selected year. A scorecard shows total global external debt.

Page 2 — Country Deep-Dive answers Q1, Q2, and Q5. A stacked bar chart shows debt composition by sector over time for any selected country. A line chart shows short-term vulnerability trends. A table sorted by total 2021 payments shows which countries face the most acute debt service pressure.

Live dashboard: https://lookerstudio.google.com/reporting/7fc18e9e-a5c6-4616-b920-b5b4bddf2264

Key Technical Lessons

1. Know which World Bank API sources support programmatic access. Only source 2 (IDS) reliably supports the Indicators API v2 via wbgapi. Sources 22, 23, and 54 use a different DataBank backend and return JSON decode errors. For QEDS data, use the bulk Excel downloads instead.

2. BigQuery rejects column names starting with numbers. Prefix them before writing Parquet. A simple list comprehension handles this: f"year_{col}" if str(col).isdigit() else col.

3. Use the right tool for the data size. PySpark is excellent for large datasets but overkill for a 76K-row Excel file. The GCS Python client with pandas and PyArrow is simpler, faster, and doesn't OOM.

4. Docker-in-Docker with Airflow requires explicit socket mounting and group permissions. Mount /var/run/docker.sock and add the Docker group ID to the worker container. Without the group add, you get a silent permission error.

5. ELT separates concerns cleanly. When the analytical questions evolved during development, I only needed to update dbt models — never the ingestion layer. This separation is the most valuable architectural decision in the project.

What I Would Do Differently

If I were building this again, I would use the World Bank DataBank bulk download API to get historical QEDS time series data instead of point-in-time Excel files. The current QEDS data only has a handful of quarters because the Excel files are snapshots of the latest publication. A proper time series would require downloading and archiving each quarterly release.

I would also add a load_to_bigquery step that loads Parquet directly into native BigQuery tables rather than using external tables. External tables work well but they require manually updating the source URI list each time a new partition is added. A native table with partitioning handles this automatically.

Conclusion

The Sovereign Debt Observatory took the World Bank's raw debt data from scattered Excel files and API endpoints to a fully automated, tested, and documented analytical pipeline. Every component is reproducible — Terraform provisions the infrastructure, Docker packages the ingestion environment, Airflow schedules the runs, and dbt documents and tests the transformations.

The full source code is on GitHub: https://github.com/Derrick-Ryan-Giggs/sovereign-debt-observatory

If you have questions about any part of the implementation, drop them in the comments. I am happy to go deeper on any layer.

Ryan Derrick Giggs is a data engineering practitioner and technical writer based in Nairobi, Kenya. He is currently completing the DataTalksClub Data Engineering Zoomcamp 2026.

LinkedIn: https://linkedin.com/in/ryan-giggs-a19330265
GitHub: https://github.com/Derrick-Ryan-Giggs

Exadata AI Storage: A New Era of AI-Powered Database Infrastructure

Ryan Giggs — Mon, 06 Apr 2026 07:41:04 +0000

Oracle's Exadata platform has always been synonymous with extreme database performance. But with the release of Exadata System Software 24ai and 25ai, alongside the debut of Oracle Exadata X11M in January 2025, Oracle has taken a decisive step into the AI era. Exadata is no longer just a high-performance OLTP and analytics machine — it is now purpose-built to accelerate AI vector search, in-database machine learning, and mixed enterprise workloads, all on a single converged platform.

This post breaks down the key new features across software, infrastructure, high availability, monitoring, and security — and explains why they matter.

What Is Exadata AI Storage?

At its core, Exadata AI Storage refers to Oracle's strategy of pushing intelligence deeper into the storage layer. Rather than offloading AI computation to separate, purpose-built vector databases or GPUs, Oracle brings AI operations — particularly AI Vector Search — directly to the storage servers themselves. This means vector index builds, similarity searches, and distance calculations happen closer to where data lives, dramatically reducing the data movement that kills performance in distributed architectures.

The result? Key vector search operations running up to 30x faster with Exadata System Software 24ai, and further accelerated on X11M with in-memory vector index (HNSW) scans running up to 43% faster on database servers and up to 55% faster on storage servers compared to the previous generation.

Key New Software Features

1. AI Smart Scan

AI Smart Scan is an extension of Exadata's legendary Smart Scan technology, now purpose-built for AI workloads. It offloads compute-intensive AI Vector Search operations — including vector index builds and similarity queries — directly to Exadata's intelligent storage servers. This eliminates the need to ship raw data up to the database tier for processing.

Critically, AI Smart Scan enables thousands of concurrent AI vector searches in multi-user environments. This is a significant differentiator for enterprise RAG (Retrieval Augmented Generation) pipelines and AI applications that need to serve many users simultaneously, not just batch processes.

With the latest release (Exadata System Software 25ai / 25.1), Adaptive Top-K Filtering further extends this: each storage server maintains a running Top-K result set, reducing data returned to the database servers by up to 4.7x. Similarly, VECTOR_DISTANCE() calculations are now projected from storage, delivering up to 4.6x faster distance-based queries.

2. Exadata RDMA Memory (XRMEM)

XRMEM replaces the persistent memory (Intel Optane PMem) that earlier generations used, adapting to changes in the memory vendor landscape. It is built on DDR5 DRAM and accessed via RDMA (Remote Direct Memory Access) over Converged Ethernet (RoCE), bypassing the OS, I/O, and network software stacks entirely.

The practical benefit: ultra-low read latency as low as 14 microseconds — a 21% improvement over prior generations — with scan throughput of up to 500 GB/s from XRMEM alone. Each Exadata X11M Extreme Flash Storage Server contains 1.25 TB of XRMEM, which sits as an acceleration tier in front of the Smart Flash Cache.

XRMEM is particularly impactful for OLTP workloads that require sub-20-microsecond response times, and it now also accelerates AI vector index reads transparently.

3. On-Storage Processing

This is the foundational principle that makes all the above possible. Exadata storage servers are not passive disk arrays — they are intelligent compute nodes in their own right. SQL filtering, column projection, decompression, encryption/decryption, bloom filters, and now AI vector operations all execute on the storage servers, not the database servers.

This dramatically reduces the volume of data sent over the internal network and processed by database-tier CPUs. For analytics workloads, this "push-down" processing model is why Exadata consistently delivers 10–100x better throughput than general-purpose storage.

4. In-Memory Columnar Speeds for JSON Queries

Exadata's In-Memory Columnar Cache on storage servers (also called Columnar Cache) stores a columnar representation of row-oriented data directly in flash cache. When queries access this data, they get the performance of columnar analytics without requiring data to be reformatted or migrated.

With Oracle Database 23ai — which is required to unlock the full Exadata Exascale feature set — JSON documents stored natively benefit from this columnar acceleration. Oracle Database 23ai's JSON Relational Duality views, which expose the same data as both JSON and relational tables simultaneously, can be queried at columnar memory speeds on Exadata, collapsing the performance gap between document and relational workloads.

5. Transparent Cross-Tier Scan

Exadata's multi-tier storage hierarchy — XRMEM → Smart Flash Cache → disk — is managed automatically and transparently. When a Smart Scan or AI Smart Scan runs, Exadata intelligently sources data from whichever tier contains it, combining reads from memory, flash, and disk in parallel.

This means administrators and developers never need to manually partition hot vs. cold data or tune tier placement. The system continuously tracks access patterns and moves data to the appropriate tier based on usage, keeping the hottest data in XRMEM and the next hottest in flash. The database never sees these tiers explicitly — it simply issues a SQL or vector query and receives results.

6. Caching Enhancements

Several caching improvements ship with the latest Exadata System Software releases:

Automatic KEEP Object Load into Exadata Flash Cache: Objects tagged with the KEEP storage clause in Oracle Database are automatically and proactively loaded into Exadata Smart Flash Cache — even before they are first accessed — ensuring zero cold-start latency for critical tables and indexes.
Write Back Flash Cache: Database block writes are cached in flash, eliminating disk I/O bottlenecks for large OLTP and batch workloads.
Cell-to-Cell Rebalance preserving XRMEM and Flash Cache: When data rebalances across storage servers (due to maintenance or failure), both the XRMEM and flash cache contents are also rebalanced, preserving performance levels rather than causing a cold-cache performance dip.

7. Columnar Smart Scan at Memory Speed

When Oracle Database In-Memory is enabled, Exadata automatically stores data in columnar format within Flash Cache and XRMEM if it will improve query performance. This brings columnar analytics performance — historically associated only with in-database memory (DRAM on database servers) — to storage-resident data, enabling analytics at memory speeds even when datasets exceed what fits in database-server DRAM.

A single Exadata X11M rack can deliver up to 100 GB/s of flash throughput and 500 GB/s from XRMEM for the hottest data, far exceeding what traditional storage arrays can achieve even with flash added.

8. Exadata Cache Observability

Exadata System Software 24.1 introduced ecstat (Exadata Storage Cache Statistics), a real-time utility that provides per-storage-server statistics on Smart Flash Cache usage, XRMEM hits, and I/O performance. This was a long-standing gap — DBAs previously had to rely on AWR snapshots to understand cache behavior.

In Exadata System Software 25.2, this was extended with CellSQLStat, which provides real-time, per-storage-server insights into active Smart Scan operations: CPU and memory usage, Storage Index and Columnar Cache I/O savings, flash and XRMEM hit rates, scan rates, and more. Both ecstat and CellSQLStat data are automatically included in ExaWatcher collections, making them available for historical analysis.

Infrastructure Improvements

Increased Number of Virtual Machines

With Exadata Exascale (the new intelligent storage architecture introduced in 2024 and available on X11M), the limit on Virtual Machine clusters per database server increases dramatically. Traditional Exadata with ASM supported 4, 8, or 12 VMs per database server. Exascale raises this ceiling to 50 VMs per database server, enabling far greater consolidation of Oracle Database workloads on a single Exadata system without sacrificing isolation or performance.

Exascale also centralizes VM storage in the shared Exascale storage pool rather than on individual database servers, increasing flexibility and simplifying management.

Secure Boot for KVM Virtual Machines

Exadata System Software now supports Secure Boot for KVM guest VMs, ensuring that only cryptographically signed and trusted OS images can boot on Exadata database servers. This closes a significant attack vector in virtualized deployments and aligns Exadata's on-premises security posture with cloud-native security standards. It complements existing features like Trusted Partitions for Oracle Linux Virtualization.

High Availability and Network Resilience

Improved RoCE Network Resilience: ExaPortMon

Every Exadata database and storage server connects to the internal network via dual 100 GbE RoCE ports for an aggregate of 200 Gbps. If a RoCE leaf switch port becomes stalled — appearing online but unable to pass traffic — it can cause cluster instability without triggering a clean failure.

ExaPortMon (introduced in Exadata System Software 24ai) solves this. It continuously monitors both RoCE ports on every server. When it detects a stalled port, it automatically migrates the IP address to the healthy port, keeping network traffic flowing and preventing outages. When the stalled port recovers, ExaPortMon automatically returns the IP address to its original port. No manual intervention required.

Enhanced RoCE Network Security: Exadata Secure RDMA Fabric Isolation

Exadata Secure RDMA Fabric Isolation (Secure Fabric) provides strict network isolation between VM clusters sharing the same physical Exadata infrastructure. It prevents database servers in one VM cluster from communicating with those in another over the RoCE fabric, eliminating lateral movement risk in consolidated and multi-tenant deployments.

Starting with Exadata System Software 25.1, Secure Fabric is automatically selected by default for all new on-premises deployments with X8M and newer hardware — bringing on-premises deployments into alignment with cloud deployments, which have always used Secure Fabric.

Monitoring and Management

AWR & SQL Monitor Enhancements

Oracle Automatic Workload Repository (AWR) on Exadata is enhanced with Exadata-specific storage server metrics alongside standard Oracle wait event data. AWR now collects and reports on XRMEM, Flash Cache, and HDD device performance, enabling DBAs to correlate database wait events with storage-tier behavior in a single report.

SQL Monitor is similarly enhanced, providing end-to-end visibility into query execution that includes storage offload statistics, Smart Scan I/O savings, and flash cache hit rates — all tied to the specific SQL statement being analyzed.

JSON API for Management Server

Exadata's Management Server (MS) now exposes a JSON REST API, enabling programmatic access to Exadata management and monitoring functions. This is a significant modernization of Exadata's management interface, making it easier to integrate Exadata health metrics, alerts, and configuration into modern observability stacks (Grafana, custom dashboards, CI/CD pipelines) without relying solely on traditional CLI tools like cellcli or Enterprise Manager.

Security Enhancements

SNMP v3 Security

Exadata System Software 24.1 introduced mandatory SNMP security improvements across all storage servers and database servers. The key changes:

SNMP v3 is now the recommended and encouraged standard, supporting SHA-256, SHA-384, and SHA-512 authentication protocols for strong authentication and encryption.
All SNMP subscriber definitions now require the connection type to be explicitly specified — administrators can no longer leave it ambiguous.
SNMP v1 remains available but triggers an explicit warning discouraging its use.
Default community strings like public and private are actively discouraged by the system, prompting administrators to set strong, unique values.

This tightens Exadata's management plane security, closing a common vulnerability in enterprise infrastructure where SNMP v1 with default community strings is still widely used.

Why This All Matters: The Convergence Thesis

The strategic bet Oracle is making with Exadata AI Storage is one of convergence over fragmentation. The enterprise AI market has seen an explosion of purpose-built vector databases (Pinecone, Weaviate, Qdrant, Milvus, Chroma), and many organizations are building RAG pipelines that shuffle data between separate systems: a relational database for operational data, a vector database for embeddings, an object store for documents.

Exadata offers a fundamentally different architecture: bring the AI to the data, not the data to the AI. With Oracle AI Database 23ai (now succeeded by Oracle AI Database 26ai as the long-term support release), all of this runs in a single converged engine — relational queries, vector similarity search, JSON document queries, graph traversals, and full-text search — executed as optimized SQL on Exadata hardware. Advanced AI features including AI Vector Search are included at no additional charge.

And with Exadata Exascale reducing the entry cost for Exadata Database Service by up to 95% and enabling organizations to start with as little as 300 GB of storage, the platform is no longer exclusively for Fortune 500 database estates. It is increasingly accessible to organizations of any size that need to build AI applications on enterprise-grade, governed, transactionally consistent data.

Summary Table

Category	Key Feature	Benefit
AI Workloads	AI Smart Scan + Adaptive Top-K	Up to 30x faster vector search; 4.7x less data to DB servers
Memory	XRMEM (DDR5 + RDMA)	14µs read latency; 500 GB/s scan throughput
Caching	Auto KEEP Load, Write Back, Columnar Cache	Zero cold-start for critical objects; analytics at memory speed
Observability	ecstat + CellSQLStat	Real-time per-cell Smart Scan and cache monitoring
Infrastructure	Exascale VM limit increase	Up to 50 VMs per DB server (up from 12)
Security	Secure Fabric default on-prem	Automatic lateral-movement isolation for VM clusters
Network HA	ExaPortMon	Auto-failover between RoCE ports; no manual intervention
Security	SNMP v3 enforcement	SHA-512 auth; eliminates default community string risk
Management	JSON API for Management Server	Programmatic integration with modern observability stacks

Exadata AI Storage represents Oracle's clearest articulation yet of its "converged data" strategy: a single platform that handles OLTP, analytics, and AI workloads without requiring organizations to build and manage a fragmented ecosystem of specialized tools. With Exadata System Software 25ai, the X11M generation, and the Exascale architecture now generally available across OCI, multicloud (AWS, Azure, Google Cloud), and on-premises, there has never been a better time to evaluate what Exadata can do for your AI application stack.

The numbers speak for themselves — but the architecture is the real story.

Have you worked with Exadata AI Storage or Oracle Database 23ai/26ai AI Vector Search? Share your experience in the comments.

Tech Ecosystem Observatory: How I Built a Cloud-Native Data Pipeline to Track Global Tech Layoffs vs YC Startup Activity

Ryan Giggs — Mon, 30 Mar 2026 07:44:40 +0000

Just completed my DEZ Zoomcamp 2026 capstone project — the Tech Ecosystem Observatory

Built a full cloud-native batch data pipeline from scratch that answers: which industries are shedding the most jobs, and how does that correlate with YC startup activity?

Here's what the pipeline looks like end to end:

✅ Terraform — provisioned GCS bucket and BigQuery datasets as infrastructure as code

✅ Docker — containerized all ingestion scripts into a portable image

✅ Kestra — orchestrated a 4-task batch DAG running weekly every Monday 6AM UTC

✅ Google Cloud Storage — raw JSONL data lake storing layoffs and YC company data

✅ BigQuery — partitioned by date (monthly) and clustered by industry/country for optimized queries

✅ dbt Cloud — built staging views and mart tables (mart_monthly_layoffs + mart_tech_ecosystem)

✅ Looker Studio — 2-page interactive dashboard with layoff trends, geo maps, ecosystem stress ratios

📊 Data: 4,317 layoff events (2023–2024) + 5,690 YC-backed companies

🔗 Live dashboard: https://lookerstudio.google.com/reporting/b1620cae-97cb-4911-82b8-dd0c46ee8acb

💻 GitHub: https://github.com/Derrick-Ryan-Giggs/tech-ecosystem-observatory

Huge thanks to @DataTalksClub and Alexey Grigorev for building and maintaining this incredible free course. If you're serious about data engineering, this is where you start 👇

https://github.com/DataTalksClub/data-engineering-zoomcamp/

Who else is building data pipelines? Drop your projects below 👇

Streaming with PyFlink and Redpanda

Ryan Giggs — Mon, 16 Mar 2026 13:34:19 +0000

Week 7 of Data Engineering Zoomcamp by @DataTalksClub complete

Just finished Module 7 - Streaming with PyFlink. Learned how to:

Set up Redpanda as a Kafka replacement
Build Kafka producers and consumers in Python
Create tumbling and session windows in Flink
Analyze real-time taxi trip data with stream processing

Here's my homework solution: https://github.com/Derrick-Ryan-Giggs/Streaming-Homework

You can sign up here: https://github.com/DataTalksClub/data-engineering-zoomcamp/

Oracle Database 23ai: Vector Similarity Search - Exact, Approximate, and Multi-Vector Strategies

Ryan Giggs — Mon, 16 Mar 2026 08:57:31 +0000

Oracle Database 23ai's AI Vector Search provides multiple strategies for finding similar vectors, each with different trade-offs between accuracy, speed, and resource usage. Understanding when to use exact search, approximate search, or multi-vector search—and knowing the essential vector functions—is crucial for building high-performance semantic search applications.

Understanding Similarity Search Types

1. Exact Similarity Search (Flat Search)

Exact similarity search calculates a query vector's distance to all other vectors. It's also called flat search or exhaustive search because every vector in the dataset is compared.

Characteristics:

Gives the most accurate results
Perfect search quality (100% recall)
Involves potentially significant time as dataset grows
No indexes required
Suitable for small to medium datasets (thousands to hundreds of thousands of vectors)

SQL Example:

SELECT 
  product_id,
  product_name,
  VECTOR_DISTANCE(embedding, :query_vector, COSINE) AS similarity
FROM products
ORDER BY similarity
FETCH FIRST 10 ROWS ONLY;

When to Use:

Small datasets where performance is acceptable
When perfect accuracy is required
Development and testing phases
Benchmarking approximate search results

2. Approximate Similarity Search

Approximate similarity search uses vector indexes to dramatically speed up searches with minimal accuracy loss. Instead of checking every vector, it leverages specialized data structures to narrow the search space.

Key Requirements:

You must enable vector pool in the SGA for HNSW indexes (in-memory neighbor graph indexes).

ALTER SYSTEM SET vector_memory_size = 800M SCOPE=SPFILE;
-- Restart required

Characteristics:

Can be more efficient but less accurate than exact search
Uses target accuracy setting (typically 90-99%)
Requires vector indexes (HNSW or IVF)
Scales to millions or billions of vectors
Typical accuracy: 95%+ with proper configuration

SQL Example:

-- Using FETCH APPROXIMATE
SELECT 
  product_id,
  product_name
FROM products
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

Performance Comparison:

According to Oracle benchmarks, exact search on 50,000 vectors took 1.50 seconds versus 0.47 seconds with HNSW index—over 3x faster with the same top-10 results.

Vector Index Types

Oracle AI Vector Search supports two main types of vector indexes:

HNSW (Hierarchical Navigable Small World)

In-Memory Neighbor Graph vector index that creates a navigable graph structure for ultra-fast similarity search.

Creating HNSW Index:

CREATE VECTOR INDEX products_hnsw_idx
ON products(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

Characteristics:

Fully built in-memory in the vector pool
Extremely fast queries (sub-100ms)
Requires substantial memory
No DML operations allowed after creation
Not available in RAC environments

Memory Calculation:

Formula: 1.3 times number_of_vectors times number_of_dimensions times dimension_byte_size

Example for 1M vectors, 768 dimensions, FLOAT32 (4 bytes):

1.3 × 1,000,000 × 768 × 4 = 3.99 GB

IVF (Inverted File Flat)

Neighbor Partition vector index built on disk with blocks cached in the buffer cache.

Creating IVF Index:

CREATE VECTOR INDEX products_ivf_idx
ON products(embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 95;

Characteristics:

Disk-based, blocks cached in buffer cache
More scalable for very large datasets
Supports DML operations (may require periodic rebuild)
Works in RAC environments
Slightly slower than HNSW but still very fast

How IVF Works:

The index partitions vectors into clusters based on similarity. By default, the number of partitions equals the square root of the dataset size. During search, only relevant clusters are examined.

3. Multi-Vector Similarity Search

Multi-vector similarity search is usually used for multi-document search where documents are split into chunks, and chunks are embedded individually into vectors.

Use Cases:

Long documents split into paragraphs or sections
Product catalogs with multiple descriptions
Research papers chunked for semantic search
Multi-modal data (text + images)

How It Works:

Documents are split into chunks
Each chunk is embedded individually into a separate vector
Chunks are stored with partition keys linking them to parent documents
Search retrieves top chunks across all documents
Results can be aggregated by document

Uses partitions to organize chunks belonging to the same document.

Table Structure Example:

CREATE TABLE document_chunks (
  doc_id NUMBER,
  chunk_id NUMBER,
  chunk_text CLOB,
  embedding VECTOR(768, FLOAT32),
  PRIMARY KEY (doc_id, chunk_id)
) PARTITION BY RANGE (doc_id) (
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Multi-Vector Search Query:

-- Find top chunks across all documents
SELECT 
  doc_id,
  chunk_id,
  chunk_text,
  VECTOR_DISTANCE(embedding, :query_vector, COSINE) AS score
FROM document_chunks
ORDER BY score
FETCH APPROXIMATE FIRST 20 ROWS ONLY;

Aggregating by Document:

-- Get top documents based on best chunk match
SELECT 
  doc_id,
  MIN(VECTOR_DISTANCE(embedding, :query_vector, COSINE)) AS best_score
FROM document_chunks
GROUP BY doc_id
ORDER BY best_score
FETCH FIRST 10 ROWS ONLY;

Narrowing Search Results

Attribute Filtering with WHERE Clause

Use the WHERE clause to filter results based on metadata or business attributes. You are not limited by the use of the ORDER BY clause.

This powerful combination enables hybrid search: semantic similarity (vector search) plus exact filters (traditional SQL).

Example: Filter by Category and Date

SELECT 
  product_id,
  product_name,
  category,
  COSINE_DISTANCE(embedding, :query_vector) AS similarity
FROM products
WHERE category = 'Electronics'
  AND launch_date greater-than DATE '2024-01-01'
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

Example: Price Range Filter

SELECT 
  product_id,
  product_name,
  price,
  L2_DISTANCE(embedding, :query_vector) AS distance
FROM products
WHERE price BETWEEN 100 AND 500
  AND in_stock = 'Y'
ORDER BY embedding less-than hyphen greater-than :query_vector
FETCH APPROXIMATE FIRST 5 ROWS ONLY;

Best Practices for Filtering:

Apply filters in WHERE clause before vector search
Use indexed columns for better performance
Combine multiple conditions as needed
Leverage partition pruning for partitioned tables

Testing Other Distance Functions

Oracle provides shorthand distance functions that simplify syntax and improve code readability.

Distance Function Equivalents

L1_DISTANCE(v1, v2) is similar to Manhattan distance

SELECT L1_DISTANCE(
  VECTOR('[1, 2, 3]'),
  VECTOR('[4, 5, 6]')
) AS manhattan_dist
FROM DUAL;

L2_DISTANCE(v1, v2) is similar to Euclidean distance

SELECT L2_DISTANCE(
  VECTOR('[3, 0]'),
  VECTOR('[0, 4]')
) AS euclidean_dist
FROM DUAL;
-- Result: 5.0

COSINE_DISTANCE is the same as cosine

SELECT COSINE_DISTANCE(
  VECTOR('[1, 0]'),
  VECTOR('[1, 1]')
) AS cosine_dist
FROM DUAL;

INNER_PRODUCT(v1, v2) is the same as dot product

SELECT INNER_PRODUCT(
  VECTOR('[2, 3]'),
  VECTOR('[4, 5]')
) AS dot_product
FROM DUAL;
-- Result: 2*4 + 3*5 = 23

Note on DOT vs INNER_PRODUCT:

The VECTOR_DISTANCE function with DOT metric returns the negated inner product, while the INNER_PRODUCT function returns the actual dot product.

-- These are NOT equivalent:
SELECT VECTOR_DISTANCE(v1, v2, DOT) FROM table;     -- Returns -1 * dot_product
SELECT INNER_PRODUCT(v1, v2) FROM table;             -- Returns dot_product

Other Essential Vector Functions

1. Vector Constructors

TO_VECTOR() converts a string or a character large object (CLOB) to a vector

-- From string
SELECT TO_VECTOR('[1.5, 2.3, 4.1]') AS vec FROM DUAL;

-- From CLOB
DECLARE
  my_clob CLOB := '[0.1, 0.2, 0.3, 0.4]';
  my_vector VECTOR;
BEGIN
  my_vector := TO_VECTOR(my_clob);
END;
/

TO_VECTOR also takes another vector as input, adjusts its format, and returns the adjusted vector as output.

VECTOR() converts a string or CLOB into a vector

SELECT VECTOR('[1, 2, 3]', 3, FLOAT32) AS vec FROM DUAL;

TO_VECTOR and VECTOR are synonymous—they perform the same function.

2. Vector Serializer

VECTOR_SERIALIZE() converts a vector into a string or a CLOB

SELECT VECTOR_SERIALIZE(embedding) AS vec_string
FROM products
WHERE product_id = 1;

-- Result: '[0.12, 0.45, 0.78, ...]'

Use Cases:

Exporting vectors for external processing
Debugging and inspection
Logging and auditing
Integration with non-Oracle systems

3. Vector Norm

VECTOR_NORM() returns the Euclidean norm of a vector—the distance from the origin to the vector.

Also known as magnitude or length, it's calculated as the square root of the sum of squared components.

Formula:

norm = square_root(x1-squared + x2-squared + ... + xn-squared)

SQL Example:

SELECT VECTOR_NORM(TO_VECTOR('[3, 4]')) AS magnitude
FROM DUAL;
-- Result: 5.0 (because square_root(9+16) = 5)

Practical Use: Normalizing Vectors

-- Normalize vector to unit length
SELECT 
  embedding / VECTOR_NORM(embedding) AS normalized_vector
FROM products
WHERE product_id = 1;

Normalized vectors have a magnitude of 1.0, which is required for meaningful dot product similarity comparisons.

4. Vector Dimension Count

VECTOR_DIMENSION_COUNT() returns the number of dimensions of a vector

SELECT VECTOR_DIMENSION_COUNT(embedding) AS dimensions
FROM products
WHERE product_id = 1;

-- Result: 768 (for BERT-style embeddings)

Use Cases:

Validating embedding dimensions
Debugging dimension mismatches
Dynamic schema inspection
Migration validation

5. Vector Dimension Format

VECTOR_DIMENSION_FORMAT() returns the storage format of the vector

SELECT VECTOR_DIMENSION_FORMAT(embedding) AS format
FROM products
WHERE product_id = 1;

-- Possible results: 'INT8', 'FLOAT32', 'FLOAT64', 'BINARY'

Use Cases:

Schema documentation
Storage optimization analysis
Migration planning
Model compatibility verification

Complete Working Example

Here's a comprehensive example demonstrating all three search types:

-- Create table
CREATE TABLE research_papers (
  paper_id NUMBER PRIMARY KEY,
  title VARCHAR2(500),
  abstract CLOB,
  category VARCHAR2(100),
  publish_date DATE,
  embedding VECTOR(768, FLOAT32)
);

-- Insert sample data
INSERT INTO research_papers VALUES (
  1,
  'Advances in Vector Search',
  'This paper explores efficient algorithms...',
  'Computer Science',
  DATE '2024-06-15',
  VECTOR('[0.1, 0.2, ...]', 768, FLOAT32)
);
COMMIT;

-- 1. EXACT SIMILARITY SEARCH
-- Most accurate, slower for large datasets
SELECT 
  paper_id,
  title,
  COSINE_DISTANCE(embedding, :query_vector) AS similarity
FROM research_papers
WHERE category = 'Computer Science'
ORDER BY similarity
FETCH FIRST 10 ROWS ONLY;

-- 2. CREATE VECTOR INDEX for approximate search
CREATE VECTOR INDEX papers_hnsw_idx
ON research_papers(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

-- 3. APPROXIMATE SIMILARITY SEARCH
-- Faster, 95%+ accuracy
SELECT 
  paper_id,
  title,
  publish_date
FROM research_papers
WHERE category = 'Computer Science'
  AND publish_date greater-than DATE '2024-01-01'
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

-- 4. MULTI-VECTOR SEARCH (for chunked documents)
-- Create chunked version
CREATE TABLE paper_chunks (
  paper_id NUMBER,
  chunk_id NUMBER,
  chunk_text CLOB,
  embedding VECTOR(768, FLOAT32),
  PRIMARY KEY (paper_id, chunk_id)
);

-- Find best matching chunks
SELECT 
  paper_id,
  chunk_id,
  L2_DISTANCE(embedding, :query_vector) AS distance
FROM paper_chunks
ORDER BY distance
FETCH APPROXIMATE FIRST 20 ROWS ONLY;

-- Aggregate to document level
SELECT 
  p.paper_id,
  p.title,
  MIN(COSINE_DISTANCE(c.embedding, :query_vector)) AS best_match_score
FROM research_papers p
JOIN paper_chunks c ON p.paper_id = c.paper_id
GROUP BY p.paper_id, p.title
ORDER BY best_match_score
FETCH FIRST 10 ROWS ONLY;

Best Practices

1. Choose the Right Search Strategy

Exact search: Less than 100K vectors, need perfect accuracy
Approximate with HNSW: 100K to 10M vectors, need sub-100ms latency, have memory available
Approximate with IVF: 10M+ vectors, limited memory, can tolerate slightly higher latency
Multi-vector: Document chunks, multi-modal data, detailed granularity needed

2. Optimize Index Configuration

-- For high accuracy (slower but better results)
CREATE VECTOR INDEX idx1 ON table(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 99;

-- For speed (faster but slightly lower accuracy)
CREATE VECTOR INDEX idx2 ON table(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 90;

3. Use Attribute Filtering Effectively

Combine vector similarity with traditional filters to narrow results:

-- Efficient: Filter first, then search
SELECT * FROM products
WHERE price less-than 100              -- Traditional filter
  AND category = 'Electronics'         -- Traditional filter
ORDER BY embedding less-than equals greater-than :query_vector  -- Vector search
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

4. Monitor and Tune Performance

-- Check if index is being used
EXPLAIN PLAN FOR
SELECT * FROM products
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

5. Handle Dimension Mismatches

-- Validate dimensions before comparison
SELECT 
  VECTOR_DIMENSION_COUNT(embedding) AS dims,
  VECTOR_DIMENSION_FORMAT(embedding) AS format
FROM products
WHERE VECTOR_DIMENSION_COUNT(embedding) != 768;

Common Pitfalls and Solutions

Pitfall 1: Forgetting to Enable Vector Pool

Problem: HNSW index creation fails with memory error

Solution: Configure vector pool before creating HNSW indexes

ALTER SYSTEM SET vector_memory_size = 1G SCOPE=SPFILE;
-- Restart database

Pitfall 2: Using Wrong Index Type

Problem: Slow performance despite having an index

Solution: Match your workload to index type:

HNSW for read-heavy, memory-available scenarios
IVF for write-heavy or memory-constrained scenarios

Pitfall 3: Metric Mismatch

Problem: Index not being used despite proper syntax

Solution: Ensure query metric matches index metric

-- Index uses COSINE
CREATE VECTOR INDEX idx ON table(embedding)
DISTANCE COSINE;

-- Query must also use COSINE
-- Correct: Uses less-than equals greater-than (COSINE operator)
ORDER BY embedding less-than equals greater-than :query_vector

-- Wrong: Uses less-than hyphen greater-than (EUCLIDEAN operator)
-- This will NOT use the index!
ORDER BY embedding less-than hyphen greater-than :query_vector

Pitfall 4: Not Using FETCH APPROXIMATE

Problem: Query is slow despite having vector index

Solution: Use FETCH APPROXIMATE to enable index usage

-- Without APPROXIMATE - performs exact search
FETCH FIRST 10 ROWS ONLY;

-- With APPROXIMATE - uses index
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

Oracle Database 23ai provides a comprehensive vector search framework with multiple strategies to fit different use cases:

Search Strategies:

Exact: Perfect accuracy, suitable for smaller datasets
Approximate: 95%+ accuracy with dramatic speed improvements using HNSW or IVF indexes
Multi-vector: Chunk-level granularity for document search

Key Requirements:

Enable vector pool for HNSW indexes: vector_memory_size
Use FETCH APPROXIMATE for index-accelerated queries
Match distance metrics between index and query

Essential Functions:

Constructors: TO_VECTOR(), VECTOR()
Serializer: VECTOR_SERIALIZE()
Utilities: VECTOR_NORM(), VECTOR_DIMENSION_COUNT(), VECTOR_DIMENSION_FORMAT()
Distance Functions: L1_DISTANCE(), L2_DISTANCE(), COSINE_DISTANCE(), INNER_PRODUCT()

Best Practices:

Choose search strategy based on dataset size and accuracy requirements
Combine vector search with WHERE clause filters for powerful hybrid queries
Monitor index usage with EXPLAIN PLAN
Normalize vectors when using dot product similarity

By understanding these search strategies and vector functions, you can build high-performance semantic search applications that scale from thousands to billions of vectors while maintaining excellent accuracy and query speed.