<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Cheikh Badiane on Medium]]></title>
        <description><![CDATA[Stories by Cheikh Badiane on Medium]]></description>
        <link>https://medium.com/@cheikhbadiane99?source=rss-1447748d95d3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*-SNPpY5sY4YC8YS3OQ6X5Q.jpeg</url>
            <title>Stories by Cheikh Badiane on Medium</title>
            <link>https://medium.com/@cheikhbadiane99?source=rss-1447748d95d3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 00:34:30 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@cheikhbadiane99/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Building a Production-Style Medallion Architecture with Talend and BigQuery: What Actually Broke…]]></title>
            <link>https://cheikhbadiane99.medium.com/building-a-production-style-medallion-architecture-with-talend-and-bigquery-what-actually-broke-5d5cb7cd6589?source=rss-1447748d95d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/5d5cb7cd6589</guid>
            <category><![CDATA[medallion-architecture]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[talend]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data-transformation]]></category>
            <dc:creator><![CDATA[Cheikh Badiane]]></dc:creator>
            <pubDate>Wed, 12 Aug 2026 11:31:04 GMT</pubDate>
            <atom:updated>2026-08-12T11:31:04.336Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6x0apUj4HIhyP6Hsp4dFPg.png" /><figcaption>Medallion Architecture on GCP</figcaption></figure><h3>Building a Production-Style Medallion Architecture with Talend and BigQuery: What Actually Broke (and How I Fixed It)</h3><p><em>A hands-on data engineering project: 18 Talend jobs, Terraform-provisioned infrastructure, centralized logging, and a fully orchestrated Bronze → Silver → Gold pipeline on Google BigQuery.</em></p><h3>Why I Built This</h3><p>Most Talend tutorials show you a happy path: drag three components onto a canvas, click Run, watch the green checkmarks appear. That’s fine for learning the UI, but it doesn’t prepare you for what actually happens when you build something end-to-end — a real data source, a real cloud warehouse, real infrastructure-as-code, and a real orchestrator that has to run 18 jobs in the correct order without falling over.</p><p>I wanted a portfolio project that would hold up to technical scrutiny in an interview: something where I could talk not just about what I built, but about the five or six things that broke along the way and how I diagnosed them. This article is that story.</p><p><strong>The goal</strong>: implement a complete medallion architecture (Bronze → Silver → Gold) using Talend Open Studio for Big Data as the ETL engine and BigQuery as the warehouse, provisioned with Terraform, versioned in Git, with centralized logging and one-click orchestration.</p><h3>The Data</h3><p>I used Google’s public dataset bigquery-public-data.thelook_ecommerce — a simulated clothing e-commerce platform with seven tables: users, orders, order_items, products, inventory_items, distribution_centers, and events (clickstream data). Together they total roughly 3.2 million rows, which is small enough to iterate quickly but large enough to hit real performance and memory constraints — which turned out to matter a lot later.</p><p>I picked this dataset specifically because it forces you to touch every major transformation pattern: joins, aggregations, deduplication, slowly changing dimensions, pivots, and window functions. If your dataset only needs a SELECT * and a WHERE clause, you’re not really testing your pipeline.</p><h3>Architecture at a Glance</h3><pre>bigquery-public-data.thelook_ecommerce<br>                │<br>                ▼<br>      BRONZE (7 jobs) — raw ingestion + ingestion metadata<br>                │<br>                ▼<br>      SILVER (5 jobs) — cleaning, SCD2 history, enrichment<br>                │<br>                ▼<br>      GOLD (5 jobs) — business aggregates, ready for BI<br>                │<br>                ▼<br>   run_full_pipeline_orchestration — 18 chained tRunJob components</pre><p>Infrastructure — BigQuery datasets, a dedicated service account with a custom least-privilege IAM role, and staging buckets — is provisioned entirely through Terraform, organized as reusable modules (bigquery, iam, secrets) instantiated per environment (dev, preprod, prod).</p><h3>Setting Up the Infrastructure</h3><p>Rather than clicking around the GCP console, everything lives in Terraform:</p><pre>module &quot;bigquery&quot; {<br>  source     = &quot;../../modules/bigquery&quot;<br>  project_id = var.project_id<br>  region     = var.region<br>  env        = &quot;dev&quot;<br>}</pre><pre>module &quot;iam&quot; {<br>  source     = &quot;../../modules/iam&quot;<br>  project_id = var.project_id<br>  env        = &quot;dev&quot;<br>}</pre><p>The bigquery module creates the four datasets (bronze, silver, gold, and a technical _metadata dataset holding a job_logs table). The iam module creates a dedicated service account per environment with a <strong>custom IAM role</strong> — not roles/bigquery.admin — scoped down to exactly the permissions a Talend job needs: read/write on data, create/run BigQuery jobs, nothing more. That’s the least-privilege principle in practice, not just in a slide deck.</p><p>One real wrinkle: since I was working with a single personal GCP project rather than three separate ones for dev/preprod/prod, I had to add a Terraform variable (additional_staging_bucket_envs) that lets one service account’s IAM condition cover multiple staging buckets. In a proper multi-project setup you’d never need this — each environment gets its own isolated service account on its own bucket — but it’s a realistic compromise worth documenting rather than hiding.</p><p>terraform apply provisions everything in about a minute. From there, the DDL for all three layers is applied via bq query.</p><h3>Building the Jobs</h3><h3>Bronze: Getting Data In Without Lying to Yourself</h3><p>Each Bronze job follows the same skeleton: tBigQueryInput (a cross-project query against bigquery-public-data) → tMap (adds ingestion metadata) → tBigQueryOutput (loads into the target dataset via GCS staging).</p><p>The first real gotcha showed up almost immediately: <strong>BigQuery TIMESTAMP columns come back from </strong><strong>tBigQueryInput as raw Unix epoch strings with decimals</strong> — something like &quot;1784515965.6902039&quot; — not as a formatted date string. Declaring the column as Date with a pattern in the schema throws a NumberFormatException the moment you try to run it, because Talend’s date parser expects a formatted string, not a number.</p><p>The fix: declare these columns as String in the input schema, then convert explicitly inside the tMap:</p><pre>row1.created_at == null ? null : new java.util.Date((long)(Double.parseDouble(row1.created_at) * 1000))</pre><p>Note the null check — several columns like delivered_at or sold_at are legitimately empty (an order that hasn’t shipped yet), and skipping that check gets you a NullPointerException on your very first successful run through the happy-path rows.</p><h3>The Silent Bug That Taught Me the Most</h3><p>Here’s the one I’m most glad happened, because it’s the kind of bug that never shows up in a tutorial. While building bronze_order_items_ingestion, I mis-wired the tMap: the output column product_id ended up mapped to row1.order_id instead of row1.product_id. Both are integers, so <strong>nothing crashed</strong>. No compile error, no runtime exception. The job ran, reported success, and moved on.</p><p>I only caught it downstream, in Silver, when a join between order_items and products produced a suspiciously low match rate — 42,165 rows out of an expected ~181,000. A quick diagnostic query told the story:</p><pre>SELECT COUNT(DISTINCT product_id) FROM bronze.order_items<br>-- returned 125,153 — nearly identical to the order count</pre><p>product_id had, in effect, become a copy of order_id. The lesson generalizes well beyond Talend: <strong>a mapping error between two columns of the same type produces no technical error at all</strong> — it just quietly corrupts data. The only defense is a sanity check downstream (distinct-value counts, referential checks against a known dimension) rather than trusting a green “Exit code = 0.”</p><h3>Silver: Where I Abandoned the “Visual” Pattern on Purpose</h3><p>The original design called for Silver transformations built as tMap chains with lookups against reference tables — the canonical Talend pattern. In practice, once I started writing joins, SCD2 logic, and aggregations, I found it dramatically faster and more debuggable to push that logic directly into SQL executed via tBigQuerySQLRow, a component that runs an arbitrary query against BigQuery without going through a row-by-row dataflow or GCS staging at all.</p><p>This is worth calling out explicitly: <strong>the “correct” Talend pattern isn’t always the right tool.</strong> A SQL statement can be tested in isolation in the BigQuery console before it’s ever pasted into a component, which turned out to matter enormously for debugging speed.</p><p>silver_users_scd2 implements a full Slowly Changing Dimension Type 2 in two SQL steps:</p><pre>-- Step 1: close out changed versions<br>UPDATE silver.users_scd2<br>SET valid_to = CURRENT_TIMESTAMP(), is_current = FALSE<br>WHERE is_current = TRUE AND user_id IN (<br>  -- users whose tracked attributes differ from the current bronze snapshot,<br>  -- compared with IS DISTINCT FROM to handle NULLs correctly<br>)</pre><pre>-- Step 2: insert new current versions<br>INSERT INTO silver.users_scd2 (...)<br>SELECT ... FROM bronze.users s<br>LEFT JOIN silver.users_scd2 cur ON cur.user_id = s.id AND cur.is_current = TRUE<br>WHERE cur.user_id IS NULL</pre><p>The elegance here is that step 2 doesn’t need to distinguish “brand new user” from “user whose attributes changed” — after step 1, both cases simply have no is_current = TRUE row, so the same WHERE cur.user_id IS NULL clause catches both.</p><h3>Gold: Window Functions Doing the Heavy Lifting</h3><p>The Gold layer produces five aggregate tables, and this is where BigQuery’s window functions genuinely earn their keep:</p><ul><li><strong>RANK() OVER (PARTITION BY category ORDER BY total_revenue DESC)</strong> ranks products within each category without a separate self-join.</li><li><strong>MIN() OVER (PARTITION BY user_id)</strong> computes each customer’s cohort month (their first order month) inline, again without an extra join.</li><li><strong>LAG() OVER (PARTITION BY event_date ORDER BY step_order)</strong> powers the daily conversion funnel by pulling the previous step’s session count into the same row, wrapped in SAFE_DIVIDE to avoid crashing on a zero denominator.</li></ul><p>The pivot table (sales by category and month) doesn’t use an actual PIVOT clause — instead it’s conditional aggregation, SUM(CASE WHEN category = &#39;X&#39; THEN sale_price ELSE 0 END) repeated per category. Less elegant than a native pivot, but portable and easy to reason about.</p><h3>Centralized Logging: Making the Pipeline Observable</h3><p>Every job — all 17 of them — generates a UUID run_id at the start (via a tJava component writing to Talend’s shared globalMap), and writes a row to a central _metadata.job_logs table at the end, with job name, layer, environment, status, and duration.</p><pre>globalMap.put(&quot;run_id&quot;, java.util.UUID.randomUUID().toString());<br>globalMap.put(&quot;start_time&quot;, System.currentTimeMillis());</pre><p>This sounds trivial and mostly was — except for a recurring class of bug worth naming: <strong>string-concatenation SQL and Java escaping are a minefield.</strong> A single misplaced backslash-quote produces a Talend compile error that points at a line number but gives you no visual cue about what’s actually wrong:</p><pre>// broken — stray escape, malformed quote sequence<br>+ &quot;  CURRENT_TIMESTAMP(), NULL, TRUE, &quot;\&quot;&quot; + (String)globalMap.get(&quot;run_id&quot;) + &quot;\&quot;&quot;&quot; &quot;</pre><pre>// correct<br>+ &quot;  CURRENT_TIMESTAMP(), NULL, TRUE, \&quot;&quot; + (String)globalMap.get(&quot;run_id&quot;) + &quot;\&quot; &quot;</pre><p>After the third time chasing an invisible character, I adopted a rule: when a concatenated SQL line throws a syntax error, <strong>delete the whole line and retype it by hand</strong> rather than hunting for the offending character. It’s faster than it sounds, and it sidesteps whatever invisible artifact copy-pasting introduced.</p><h3>Orchestration: When “It Works Alone” Isn’t Enough</h3><p>With all 17 jobs individually validated, the next step was a parent job — run_full_pipeline_orchestration — chaining 17 tRunJob components in dependency order (Bronze first, silver_users_scd2 before the other Silver jobs since they join against it, then Gold last).</p><p>The first full run crashed with java.lang.OutOfMemoryError: Java heap space, but only on the largest job — bronze_events_ingestion, ingesting 2.4 million clickstream rows — and <em>only</em> when run inside the orchestration, never standalone. The reason: by default, tRunJob executes the child job <strong>inside the same JVM process</strong> as the parent. Memory from every prior sub-job accumulates until the heaviest job in the chain finally tips it over.</p><p>The fix is a single checkbox — <strong>“Use an independent process to run subjob”</strong> — which launches each child in its own JVM with its own memory allocation. The tradeoff is a few extra seconds of JVM startup time per job, which is a trivial cost against a hard crash.</p><p>A full orchestrated run — infrastructure already provisioned, all 18 jobs, cold JVM start on each — takes roughly 25–30 minutes end to end.</p><h3>A Grab Bag of Smaller Lessons</h3><p><strong>tBigQueryOutput uploads to the root of the GCS bucket under the local filename, regardless of what you configure in the </strong><strong>File field.</strong> If those two don’t match, you get a confusing Not found: URI gs://.../wrong-path.csv error even though the file is sitting right there under a different name. Match them exactly and the problem disappears.</p><p><strong>PowerShell doesn’t support </strong><strong>&lt; for input redirection.</strong> Coming from Bash, I hit this constantly. Get-Content query.sql | bq query ... is the correct pattern. And if your SQL needs backticks to qualify a `project.dataset.table` reference, PowerShell here-strings require <em>doubling</em> the backtick, since it’s PowerShell’s own escape character.</p><p><strong>CURRENT_TIMESTAMP() in BigQuery is UTC.</strong> I spent ten confused minutes convinced an orchestration run had silently failed, because a verification query filtered on local time and found nothing — the data was there, just two hours earlier in UTC than I expected.</p><p><strong>On Component Ok vs. On Subjob Ok</strong> — a genuinely confusing Talend distinction until you internalize the rule: On Subjob Ok is only offered from the <em>first</em> component of a flow (one with no incoming Row link); everywhere else, including the vast majority of this project’s linear job chains, it’s On Component Ok.</p><h3>What’s Actually in the Repo</h3><ul><li>Terraform modules for BigQuery, IAM, and Secret Manager, instantiated per environment</li><li>18 exported Talend jobs (Bronze/Silver/Gold/Orchestration), organized in a matching folder structure</li><li>A shared Context Group with per-environment variables</li><li>DDL and data-quality-check SQL, versioned independently of the jobs that use them</li><li>A standalone Python reconciliation script that validates row counts across layers and can gate a CI/CD pipeline</li><li>A .gitignore that treats <em>any</em> .json, .pem, or .key file as a credential by default — because the discipline of “I’ll remember not to commit this” doesn’t scale</li></ul><p>Everything is on GitHub, with the Talend project exported via <strong>Export Items</strong> (not something obvious if you’ve never needed to version a Studio project before — the job source lives in Talend’s internal workspace, not as loose files, until you explicitly export it).</p><h3>What I’d Do Differently</h3><ul><li><strong>Separate GCP projects per environment</strong>, rather than the shared-project compromise I documented above. It’s the right long-term design; I just didn’t want to pay for three projects on a personal account.</li><li><strong>Capture actual row counts</strong> (NB_LINE) from each BigQuery component into the logging table — currently rows_written is a placeholder column I never wired up.</li><li><strong>Wire up the GitLab CI pipeline</strong> that’s sitting in the repo as a reference but was never connected to a live runner.</li><li><strong>Add a failure path to logging</strong> — right now every log row says SUCCESS because I never built the On Component Error branch that would capture and log an actual failure message.</li></ul><h3>Closing Thought</h3><p>None of the interesting parts of this project were the parts that worked on the first try. The value was in the debugging: tracing a silent data-corruption bug back through three layers to a single mis-clicked column in a tMap, understanding <em>why</em> an orchestrator crashes on a job that runs fine alone, learning that a Talend compile error pointing at line 946 might actually be a stray character three concatenations earlier. That’s the kind of pattern recognition that doesn’t show up in a five-minute YouTube tutorial, and it’s exactly what I wanted this project to leave me with.</p><p>If you’re building something similar, my one piece of advice: don’t just make the pipeline work — break it on purpose, or let it break naturally and resist the urge to just delete-and-redo. Diagnosing <em>why</em> is where the actual learning lives.</p><p><em>The full source — Terraform modules, exported Talend jobs, SQL, and documentation — is available on </em><a href="https://github.com/cheikhb/talend-medallion-thelook"><em>GitHub</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5d5cb7cd6589" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[✦In-Depth Learning Tutorial · AI Engineering]]></title>
            <link>https://cheikhbadiane99.medium.com/in-depth-learning-tutorial-ai-engineering-a72e086ea371?source=rss-1447748d95d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/a72e086ea371</guid>
            <category><![CDATA[agentic-ai]]></category>
            <category><![CDATA[langchain]]></category>
            <category><![CDATA[rags]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[langgraph]]></category>
            <dc:creator><![CDATA[Cheikh Badiane]]></dc:creator>
            <pubDate>Wed, 20 May 2026 12:43:21 GMT</pubDate>
            <atom:updated>2026-05-20T12:49:27.870Z</atom:updated>
            <content:encoded><![CDATA[<p>✦In-Depth Learning Tutorial · AI Engineering</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RFXeK8WxRLvBFpaf5iMeBw.png" /><figcaption>Image generated by Gemini for illustration</figcaption></figure><h3>Building a Travel AI Agent<br>with LangChain, LangGraph<br>&amp; Streamlit</h3><p>A complete end-to-end guide — from tools and architecture to a 4-layer evaluation framework aligned with RAG &amp; Agentic AI best practices.</p><p>Cheikh Badiane</p><p><a href="/service/mailto:cheikhbadiane99@gmail.com">cheikhbadiane99@gmail.com</a> · <a href="https://github.com/cheikhb">@cheikhb</a></p><p>⏱ 12 min read</p><p><em>Imagine a travel concierge available 24/7 — one that searches flights, compares hotels, builds a day-by-day itinerary, handles bookings, and answers support questions in real time, all through natural conversation. That’s Voyager AI, and in this article I’ll show you exactly how I built it.</em></p><h3>🧭Why a Travel AI Agent?</h3><p>Planning a trip today means juggling dozens of tabs: Google Flights, Booking.com, TripAdvisor, travel blogs, visa websites, currency converters… The information exists, but it’s fragmented across the entire internet.</p><p>The goal was simple: consolidate all of that intelligence into a single conversational agent — and measure every response it produces.</p><p>The agent needed to cover the full travel planning lifecycle:</p><ul><li>Understand traveller preferences — budget, style, dates, group type</li><li>Search for flights and hotels in real time</li><li>Recommend curated activities based on travel style</li><li>Generate a personalised day-by-day itinerary</li><li>Handle bookings with confirmation references</li><li>Answer support questions — weather, visa requirements, currency conversion</li><li>Evaluate every single response with a structured metrics framework</li></ul><h3>🛠️Tech Stack</h3><p>ComponentTechnologyRoleLLMOpenAI GPT-4oAgent brain — reasoning &amp; languageAgentLangGraph create_react_agentTool orchestration (ReAct loop)MemoryLangGraph MemorySaverThread-based conversation memoryToolsLangChain BaseTool8 business-logic toolsUIStreamlitChat interface + metrics dashboardLive dataSerpAPIGoogle Flights &amp; HotelsEvaluationGPT-4o-mini (LLM Judge)Qualitative response scoringConfigpython-dotenvSecure API key management</p><h3>Why LangGraph instead of the old AgentExecutor?</h3><p>LangChain 1.x underwent a major architectural overhaul: AgentExecutor was removed in favour of LangGraph, a state-graph framework that offers:</p><ul><li>Thread-based persistent memory across conversation turns</li><li>Fine-grained control over execution steps</li><li>Built-in support for supervision and checkpointing</li><li>A foundation for complex multi-agent systems</li></ul><pre>from langgraph.prebuilt import create_react_agent<br>from langgraph.checkpoint.memory import MemorySaver</pre><h3>🏗️System Architecture</h3><p>User (Streamlit browser)</p><p>│</p><p>▼</p><p>app.py — Streamlit Interface</p><p>├── Sidebar : user preferences</p><p>├── Chat : conversation history</p><p>└── Dashboard : evaluation metrics</p><p>│</p><p>▼</p><p>agent.py — TravelAgent (LangGraph)</p><p>├── GPT-4o as the reasoning core</p><p>├── create_react_agent as orchestrator</p><p>└── MemorySaver for conversation memory</p><p>│</p><p>▼</p><p>tools.py — 8 LangChain Tools</p><p>├── search_flights ← SerpAPI or simulated</p><p>├── search_hotels ← SerpAPI or simulated</p><p>├── search_activities ← curated simulated data</p><p>├── build_itinerary ← day-by-day generator</p><p>├── make_booking ← confirmation reference</p><p>├── get_weather_forecast ← simulated forecast</p><p>├── convert_currency ← static exchange rates</p><p>└── get_travel_advisory ← visa &amp; safety info</p><p>│</p><p>▼</p><p>metrics.py — Evaluation Layer (fully decoupled)</p><p>├── RetrievalMetrics</p><p>├── GenerationMetrics</p><p>├── AgenticMetrics</p><p>└── LLMJudgeMetrics</p><p>Key design principle: the evaluation layer (metrics.py) is completely decoupled from the business logic. It never modifies the agent&#39;s behaviour — it observes and measures it after each conversation turn.</p><h3>🔧Step-by-Step Build Guide</h3><p>1 Define the Tools</p><p>Each agent capability is encapsulated in a LangChain tool — a class inheriting from BaseTool with a precise description (the prompt the LLM uses to decide when to call it) and a _run() method.</p><p>2 Build the Agent with LangGraph</p><p>Wire the LLM, tools, and memory together using create_react_agent. The ReAct loop lets the model reason → act → observe → reason again until it produces a complete response.</p><p>3 Build the Streamlit Interface</p><p>Create a chat UI with a preference sidebar, styled message bubbles, and quick-start prompts. Load API keys automatically from .env — no manual input in the UI.</p><p>4 Integrate the Evaluation Framework</p><p>Implement 4 metric layers as a fully decoupled observer. Call the evaluator after each agent response — never inside it.</p><p>5 Wire Everything Together</p><p>Connect agent → evaluator → UI in app.py, and expose the metrics dashboard below the chat.</p><h4>Step 1 in detail — Defining a Tool</h4><pre>from langchain.tools import BaseTool<br>from pydantic import BaseModel, Field<br>from typing import ClassVar, Dict, Optional, Type</pre><pre>class FlightSearchInput(BaseModel):<br>    origin: str = Field(description=&quot;Origin city or airport code&quot;)<br>    destination: str = Field(description=&quot;Destination city or airport code&quot;)<br>    departure_date: str = Field(description=&quot;Departure date YYYY-MM-DD&quot;)<br>    passengers: int = Field(default=1)<br>    cabin_class: str = Field(default=&quot;economy&quot;)</pre><pre>class FlightSearchTool(BaseTool):<br>    name: str = &quot;search_flights&quot;<br>    description: str = (<br>        &quot;Search for available flights between two cities. &quot;<br>        &quot;Returns a list of options with prices, airlines, and schedules.&quot;<br>    )<br>    args_schema: Type[BaseModel] = FlightSearchInput<br>    serpapi_key: Optional[str] = None</pre><pre>    def _run(self, origin, destination, departure_date, **kwargs) -&gt; str:<br>        if self.serpapi_key:<br>            return self._live_search(...)   # Real data via SerpAPI<br>        return self._simulated(...)         # Realistic simulated data</pre><h4>The 8 Tools at a Glance</h4><p>ToolDescriptionData Sourcesearch_flightsFlights between two citiesSerpAPI / simsearch_hotelsHotels in a destinationSerpAPI / simsearch_activitiesTours &amp; experiencesSimulatedbuild_itineraryDay-by-day travel planGeneratormake_bookingBooking confirmationSimulatedget_weather_forecastWeather + packing tipsSimulatedconvert_currency15+ currency pairsStatic ratesget_travel_advisoryVisa, health, safetySimulated</p><h4>Step 2 in detail — The LangGraph Agent</h4><pre>from langgraph.prebuilt import create_react_agent<br>from langgraph.checkpoint.memory import MemorySaver<br>from langchain_openai import ChatOpenAI</pre><pre>class TravelAgent:<br>    def __init__(self, openai_api_key, serpapi_key=None):<br>        self.llm = ChatOpenAI(model=&quot;gpt-4o&quot;, temperature=0.4,<br>                              api_key=openai_api_key)<br>        self.tools  = build_tools(serpapi_key=serpapi_key)<br>        self.memory = MemorySaver()        # thread-based memory<br>        self.agent  = create_react_agent(  # ReAct loop<br>            model=self.llm,<br>            tools=self.tools,<br>            checkpointer=self.memory,<br>        )</pre><pre>    def run(self, user_message):<br>        config = {&quot;configurable&quot;: {&quot;thread_id&quot;: &quot;session-001&quot;}}<br>        result = self.agent.invoke(<br>            {&quot;messages&quot;: [SystemMessage(...), HumanMessage(user_message)]},<br>            config=config<br>        )<br>        return self._parse_result(result)</pre><p>The ReAct pattern (Reasoning + Acting) gives the LLM a structured loop: <em>think → pick a tool → observe the result → think again → repeat</em> until it has enough information to produce a final, grounded response.</p><h4>Step 4 in detail — The Evaluation Framework</h4><p>This is what separates a prototype from a production-ready system. The framework implements 4 independent metric layers, each addressing a distinct failure mode.</p><p>Layer 1: Retrieval</p><p>raw_count · selected_count · top_1_score · avg_score · compression_ratio · empty / over / under retrieval</p><p>Layer 2: Generation</p><p>grounded · has_answer · answer_length · potential_hallucination · compression_ratio</p><p>Layer 3: Agentic</p><p>pipeline_complete · execution_steps · agents_used · steps_per_agent · latency_ms · tools_sequence</p><p>Layer 4: LLM Judge</p><p>relevance · faithfulness · completeness · clarity · weighted score (0–5) · verdict + recommendations</p><p>Hallucination detection — if the agent produces a response without calling any search tool, it’s flagged as a potential hallucination:</p><pre>search_tools = {&quot;search_flights&quot;, &quot;search_hotels&quot;, &quot;search_activities&quot;, ...}<br>grounded              = bool(set(tools_used) &amp; search_tools)<br>potential_hallucination = has_answer and not grounded</pre><p>LLM Judge scoring — a second LLM (GPT-4o-mini) scores each response on 4 criteria with a weighted final score:</p><pre># Weighted score — relevance and faithfulness carry more weight<br>score_global = (<br>    relevance    * 0.35 +<br>    faithfulness * 0.30 +<br>    completeness * 0.20 +<br>    clarity      * 0.15<br>)</pre><pre># Verdict<br>if   score &gt;= 4.5: verdict = &quot;EXCELLENT&quot;<br>elif score &gt;= 3.5: verdict = &quot;GOOD&quot;<br>elif score &gt;= 2.5: verdict = &quot;ACCEPTABLE&quot;<br>else:             verdict = &quot;INSUFFICIENT&quot;</pre><h4>Step 5 in detail — Wiring it all together</h4><pre># app.py — called on every user message<br>result = st.session_state.agent.run(user_input, context)</pre><pre># Evaluate immediately, after the agent responds<br>report = evaluator.evaluate(<br>    question     = user_input,<br>    answer       = result[&quot;output&quot;],<br>    tools_used   = result[&quot;tools_used_full&quot;],<br>    tool_results = result[&quot;tool_results&quot;],<br>    latency_ms   = result[&quot;latency_ms&quot;],<br>)</pre><pre>st.session_state.messages.append({<br>    &quot;role&quot;: &quot;assistant&quot;,<br>    &quot;content&quot;: result[&quot;output&quot;],<br>    &quot;tools_used&quot;: result[&quot;tools_used&quot;],<br>    &quot;eval_report&quot;: report.summary(),<br>})</pre><h3>🚀Installation &amp; Setup</h3><pre># 2. Create and activate virtual environment<br>python -m venv venv<br>venv\Scripts\activate      # Windows<br>source venv/bin/activate   # Mac / Linux</pre><pre># 3. Install dependencies<br>pip install -r requirements.txt</pre><pre># 4. Configure API keys<br>cp .env.example .env<br># Edit .env and add your keys:<br># OPENAI_API_KEY=sk-proj-...<br># SERPAPI_API_KEY=...  (optional)</pre><pre># 5. Launch<br>streamlit run app.py</pre><p>Open <a href="http://localhost:8501/">http://localhost:8501</a> — the agent initialises automatically from your .env file. No API key input required in the UI.</p><h3>💡What I Learned</h3><p>Lesson 01: LangChain moves fast — pin your versions</p><p>The migration from AgentExecutor to LangGraph between 0.x and 1.x broke most existing tutorials. Always run pip show langchain before copying code from the internet, and test against a specific pinned version in requirements.txt.</p><p>Lesson 02: Pydantic v2 is strict about annotations</p><p>Inside any class inheriting from BaseTool, class-level constants must be typed as ClassVar. Missing this annotation causes a PydanticUserError at import time — it took me a frustrating debugging session to find this one.</p><p>Lesson 03: Keep evaluation completely decoupled from business logic</p><p>Mixing metrics into the agent code makes everything fragile. With metrics.py as a standalone observer, I could iterate on scoring logic, add new metric layers, and run A/B comparisons without touching the agent or tools at all.</p><p>Lesson 04: The LLM Judge catches what technical metrics miss</p><p>A pipeline_complete = True score doesn&#39;t mean the response is actually good. The LLM Judge detected subtle failures: a structurally correct but factually incomplete itinerary, a response that answered a different question than the one asked, and answers that were technically grounded but confusingly written.</p><h3>🔮What’s Next</h3><ul><li>Real booking APIs — Amadeus or Skyscanner integration instead of simulated confirmations</li><li>Persistent memory — store conversation history across sessions in a database</li><li>True multi-agent architecture — separate research, itinerary-building, and support agents</li><li>Production monitoring — export metrics to LangSmith or a Grafana dashboard</li><li>Automated regression tests — a reference question dataset to catch quality regressions on each code change</li></ul><h3>📎Resources</h3><ul><li>🐙</li><li>Source Code<br><a href="https://github.com/cheikhb/voyager-ai">github.com/cheikhb/voyager-ai</a></li><li>📚</li><li>LangGraph Documentation<br><a href="https://langchain-ai.github.io/langgraph">langchain-ai.github.io/langgraph</a></li><li>📚</li><li>Streamlit Documentation<br><a href="https://docs.streamlit.io/">docs.streamlit.io</a></li><li>🔑</li><li>OpenAI API<br><a href="https://platform.openai.com/api-keys">platform.openai.com/api-keys</a></li><li>🔍</li><li>SerpAPI — Live flight &amp; hotel data <a href="https://serpapi.com/">serpapi.com</a></li></ul><p>Cheikh Badiane</p><p>AI Engineer passionate about LangChain, LangGraph, and building production-ready intelligent systems.<br>If this article helped you, consider starring the repo ⭐</p><p><a href="/service/mailto:cheikhbadiane99@gmail.com">✉ Email</a><a href="https://github.com/cheikhb">⌥ GitHub</a><a href="https://github.com/cheikhb/voyager-ai">★ Star the repo</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a72e086ea371" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Road Accident Knowledge Graph with BigQuery Graph and French Open Data]]></title>
            <link>https://cheikhbadiane99.medium.com/building-a-road-accident-knowledge-graph-with-bigquery-graph-and-french-open-data-af4189af9dbf?source=rss-1447748d95d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/af4189af9dbf</guid>
            <category><![CDATA[graph-data-analysis]]></category>
            <category><![CDATA[google-bigquery]]></category>
            <category><![CDATA[bigquery-graph]]></category>
            <category><![CDATA[graphql]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <dc:creator><![CDATA[Cheikh Badiane]]></dc:creator>
            <pubDate>Sun, 12 Apr 2026 18:17:29 GMT</pubDate>
            <atom:updated>2026-04-12T19:00:15.068Z</atom:updated>
            <content:encoded><![CDATA[<h3>From raw CSV files to interactive graph analytics — a complete end-to-end walkthrough</h3><p><em>Google Cloud · BigQuery Graph · Data Engineering · Open Data</em></p><p>Every year, tens of thousands of road accidents occur across France. The French government publishes all of this data openly through <a href="https://www.data.gouv.fr/">data.gouv.fr</a> — but in the form of flat CSV files that are hard to explore for complex relationship queries like <em>“which vehicle types cause the most fatalities on wet motorways at night?”</em></p><p>In this article, I’ll walk through how I built a <strong>property graph</strong> on top of this data using <strong>BigQuery Graph</strong> — Google Cloud’s new ISO GQL-compatible graph engine — transforming 5 flat CSV files into a richly connected graph of accidents, locations, vehicles, and victims, then running graph pattern queries and building interactive visualizations.</p><h3>What is BigQuery Graph?</h3><p>BigQuery Graph extends BigQuery’s analytical engine with a <strong>property graph model</strong> — nodes (entities) and edges (relationships) — queryable using <strong>ISO GQL (Graph Query Language)</strong> and <strong>SQL/PGQ</strong>. Rather than copying data into a separate graph database, it creates a virtual graph layer directly on top of your existing BigQuery tables. No ETL, no data duplication.</p><p>It’s currently in <strong>Private Preview</strong> — sign up at <a href="https://tinyurl.com/bq-graph">tinyurl.com/bq-graph</a> to get your project allowlisted.</p><h3>The Dataset: BAAC French Road Accidents</h3><p>The <strong>BAAC (Bulletin d’Analyse des Accidents Corporels)</strong> dataset is published annually by the French Ministry of the Interior via data.gouv.fr. It covers every bodily injury accident recorded by police and gendarmerie across metropolitan France and overseas territories.</p><p>For 2024, it comes as <strong>5 CSV files</strong>:</p><ul><li><strong>caract-2024.csv</strong> — Accident characteristics (date, location, weather, lighting) → Node: <strong>Accident</strong></li><li><strong>lieux-2024.csv</strong> — Road geometry (surface, profile, infrastructure) → Node: <strong>Location</strong></li><li><strong>vehicules-2024.csv</strong> — Vehicles involved (category, manoeuvre, collision point) → Node: <strong>Vehicle</strong></li><li><strong>usagers-2024.csv</strong> — People involved (severity, role, safety equipment) → Node: <strong>User</strong></li><li><strong>matricules-2024.csv</strong> — Vehicle registration data → Standalone (not joinable to the graph)</li></ul><p>The key join columns are Num_Acc (accident ID shared across all tables) and id_vehicule (vehicle ID linking vehicles to their occupants).</p><h3>Architecture Overview</h3><pre>data.gouv.fr CSVs<br>      ↓<br>GCS Bucket (EU multi-region)<br>      ↓<br>BigQuery Dataset (EU location)<br>      ↓  [CREATE PROPERTY GRAPH]<br>AccidentGraph<br>  Nodes: Accident, Location, Vehicle, User<br>  Edges: OccurredAt, InvolvedVehicle, CarriedUser<br>      ↓<br>GQL queries + Python visualizations</pre><h3>Step 1 — Download the Data</h3><p>Download the 2024 BAAC files from the official dataset page:</p><p><a href="https://www.data.gouv.fr/datasets/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2024">data.gouv.fr — BAAC 2005 to 2024</a></p><p>Upload all 5 CSV files to a GCS bucket. In this project the bucket is bq-graph-data with a data/ folder, located in the EU multi-region.</p><h3>Step 2 — Create the BigQuery Dataset</h3><p>Open Cloud Shell and create the dataset in the <strong>EU</strong> region to match the GCS bucket location. This is critical — location mismatches will cause all load operations to fail silently.</p><pre>bq mk --location=EU accidents_graph</pre><pre># Verify location<br>bq show --format=json accidents_graph | python3 -c \<br>  &quot;import sys,json; d=json.load(sys.stdin); print(d.get(&#39;location&#39;))&quot;<br># → EU</pre><h3>Step 3 — Load the CSV Files</h3><p>The BAAC files use semicolons as delimiters and double-quote all values. Getting the load flags right took some debugging:</p><pre>for table in &quot;caract-2024.csv:caracteristiques&quot; &quot;lieux-2024.csv:lieux&quot; \<br>             &quot;vehicules-2024.csv:vehicules&quot; &quot;usagers-2024.csv:usagers&quot; \<br>             &quot;matricules-2024.csv:matricules&quot;; do<br>  file=&quot;${table%%:*}&quot;<br>  tbl=&quot;${table##*:}&quot;<br>  echo &quot;Loading $file into $tbl...&quot;<br>  bq load --location=EU \<br>    --source_format=CSV \<br>    --field_delimiter=&quot;;&quot; \<br>    --skip_leading_rows=1 \<br>    --encoding=UTF-8 \<br>    --quote=&#39;&quot;&#39; \<br>    --max_bad_records=10 \<br>    --autodetect \<br>    accidents_graph.$tbl \<br>    gs://bq-graph-data/data/$file<br>done</pre><p><strong>Common pitfalls I hit:</strong></p><ul><li><strong>Location mismatch</strong> — EU bucket vs US dataset causes “Not found: Dataset” errors. Always verify location with bq show --format=json</li><li><strong>Wrong quote flag</strong> — --quote=&quot;&quot; disables quote handling entirely, causing literal &quot; characters to appear in every value. Use --quote=&#39;&quot;&#39;</li><li><strong>Wrong encoding</strong> — --encoding=ISO-8859-1 causes garbled characters on 2024 files. Use UTF-8</li><li><strong>Reserved keyword</strong> — rows is reserved in BigQuery. Use cnt or total as column aliases</li></ul><p>Verify row counts after loading:</p><pre>SELECT &#39;caracteristiques&#39; as tbl, COUNT(*) as cnt FROM accidents_graph.caracteristiques<br>UNION ALL SELECT &#39;lieux&#39;, COUNT(*) FROM accidents_graph.lieux<br>UNION ALL SELECT &#39;vehicules&#39;, COUNT(*) FROM accidents_graph.vehicules<br>UNION ALL SELECT &#39;usagers&#39;, COUNT(*) FROM accidents_graph.usagers</pre><p>You should see ~54k accidents, ~70k location records, ~100k vehicles, and ~125k users.</p><h3>Step 4 — Verify the Schema</h3><p>Before creating the graph, confirm exact column names and types. Don’t assume — the 2024 schema has some surprises:</p><pre>SELECT table_name, column_name, data_type<br>FROM accidents_graph.INFORMATION_SCHEMA.COLUMNS<br>WHERE table_name IN (&#39;caracteristiques&#39;, &#39;lieux&#39;, &#39;vehicules&#39;, &#39;usagers&#39;)<br>ORDER BY table_name, ordinal_position;</pre><p><strong>Key findings for 2024 data:</strong></p><ul><li>id_vehicule is <strong>STRING</strong> (alphanumeric like &quot;155 781 758&quot;), not INT64</li><li>id_usager is also <strong>STRING</strong></li><li>lat and long are <strong>INT64</strong> — stored as integer × 10000 in BAAC format</li></ul><h3>Step 5 — Create Edge Views</h3><p>BigQuery Graph does not allow the same table to appear in both NODE TABLES and EDGE TABLES. Since lieux, vehicules, and usagers serve as both nodes and edge sources, we create lightweight views:</p><pre>CREATE OR REPLACE VIEW accidents_graph.accident_at_location AS<br>SELECT Num_Acc FROM accidents_graph.lieux;</pre><pre>CREATE OR REPLACE VIEW accidents_graph.accident_has_vehicle AS<br>SELECT Num_Acc, id_vehicule FROM accidents_graph.vehicules;</pre><pre>CREATE OR REPLACE VIEW accidents_graph.vehicle_carried_user AS<br>SELECT id_vehicule, id_usager, grav FROM accidents_graph.usagers;</pre><p>This is a documented limitation of the current Private Preview — using a view as the edge table resolves it cleanly.</p><h3>Step 6 — Create the Property Graph</h3><p>With views in place, create the full property graph with 4 node types and 3 edge types:</p><pre>CREATE OR REPLACE PROPERTY GRAPH accidents_graph.AccidentGraph<br>NODE TABLES (</pre><pre>  accidents_graph.caracteristiques<br>    KEY (Num_Acc)<br>    LABEL Accident<br>    PROPERTIES (Num_Acc, jour, mois, an, hrmn, lum, dep, com, agg, atm, col, lat, long),</pre><pre>  accidents_graph.lieux<br>    KEY (Num_Acc)<br>    LABEL Location<br>    PROPERTIES (Num_Acc, catr, circ, nbv, vosp, prof, plan, surf, infra, situ, vma),</pre><pre>  accidents_graph.vehicules<br>    KEY (id_vehicule)<br>    LABEL Vehicle<br>    PROPERTIES (id_vehicule, Num_Acc, catv, obs, obsm, choc, manv, motor),</pre><pre>  accidents_graph.usagers<br>    KEY (id_usager)<br>    LABEL User<br>    PROPERTIES (id_usager, Num_Acc, id_vehicule, catu, grav, sexe, an_nais, trajet, secu1)</pre><pre>)<br>EDGE TABLES (</pre><pre>  accidents_graph.accident_at_location<br>    KEY (Num_Acc)<br>    SOURCE KEY      (Num_Acc) REFERENCES caracteristiques (Num_Acc)<br>    DESTINATION KEY (Num_Acc) REFERENCES lieux (Num_Acc)<br>    LABEL OccurredAt<br>    PROPERTIES (Num_Acc),</pre><pre>  accidents_graph.accident_has_vehicle<br>    KEY (id_vehicule)<br>    SOURCE KEY      (Num_Acc)     REFERENCES caracteristiques (Num_Acc)<br>    DESTINATION KEY (id_vehicule) REFERENCES vehicules (id_vehicule)<br>    LABEL InvolvedVehicle<br>    PROPERTIES (Num_Acc, id_vehicule),</pre><pre>  accidents_graph.vehicle_carried_user<br>    KEY (id_usager)<br>    SOURCE KEY      (id_vehicule) REFERENCES vehicules (id_vehicule)<br>    DESTINATION KEY (id_usager)   REFERENCES usagers (id_usager)<br>    LABEL CarriedUser<br>    PROPERTIES (id_vehicule, id_usager, grav)</pre><pre>);</pre><p>Verify it was created:</p><pre>SELECT property_graph_name, property_graph_schema<br>FROM accidents_graph.INFORMATION_SCHEMA.PROPERTY_GRAPHS;</pre><h3>Step 7 — Query with GQL</h3><p>This is where graph queries shine. Instead of complex multi-join SQL, you write natural pattern matching using ISO GQL syntax.</p><h3>Fatalities by road type (3-hop traversal)</h3><pre>GRAPH accidents_graph.AccidentGraph<br>MATCH (a:Accident)-[:OccurredAt]-&gt;(l:Location),<br>      (a)-[:InvolvedVehicle]-&gt;(v:Vehicle)-[:CarriedUser]-&gt;(u:User)<br>WHERE u.grav = 2<br>RETURN<br>  l.catr AS road_category,<br>  COUNT(DISTINCT a.Num_Acc) AS fatal_accidents,<br>  COUNT(u.id_usager) AS fatalities<br>ORDER BY fatalities DESC;</pre><h3>Severity breakdown by vehicle type</h3><pre>GRAPH accidents_graph.AccidentGraph<br>MATCH (a:Accident)-[:InvolvedVehicle]-&gt;(v:Vehicle)-[:CarriedUser]-&gt;(u:User)<br>RETURN<br>  v.catv AS vehicle_type,<br>  COUNTIF(u.grav = 2) AS killed,<br>  COUNTIF(u.grav = 3) AS hospitalized,<br>  COUNTIF(u.grav = 4) AS slight_injury,<br>  COUNT(*) AS total<br>ORDER BY killed DESC;</pre><h3>Top 10 departments by accidents and fatalities</h3><pre>GRAPH accidents_graph.AccidentGraph<br>MATCH (a:Accident)-[:InvolvedVehicle]-&gt;(v:Vehicle)-[:CarriedUser]-&gt;(u:User)<br>RETURN<br>  a.dep AS department,<br>  COUNT(DISTINCT a.Num_Acc) AS accidents,<br>  COUNTIF(u.grav = 2) AS fatalities<br>ORDER BY accidents DESC<br>LIMIT 10;</pre><h3>Dangerous conditions — weather × lighting</h3><pre>GRAPH accidents_graph.AccidentGraph<br>MATCH (a:Accident)-[:InvolvedVehicle]-&gt;(v:Vehicle)-[:CarriedUser]-&gt;(u:User)<br>WHERE u.grav IN (2, 3)<br>RETURN<br>  a.atm AS weather,<br>  a.lum AS lighting,<br>  COUNT(*) AS serious_casualties<br>ORDER BY serious_casualties DESC;</pre><h3>Step 8 — Native Graph Visualization</h3><p>In <strong>BigQuery Studio → New Notebook</strong>, the %%bigquery --graph magic command renders an interactive node-edge explorer directly in the notebook cell. Click any node to inspect its properties, drag to rearrange the layout, zoom in and out.</p><pre>%%bigquery --graph<br>GRAPH accidents_graph.AccidentGraph<br>MATCH<br>  (a:Accident)-[o:OccurredAt]-&gt;(l:Location),<br>  (a)-[iv:InvolvedVehicle]-&gt;(v:Vehicle)-[cu:CarriedUser]-&gt;(u:User)<br>WHERE u.grav = 2<br>RETURN<br>  TO_JSON(a) AS c1, TO_JSON(o) AS c2, TO_JSON(l) AS c3,<br>  TO_JSON(iv) AS c4, TO_JSON(v) AS c5, TO_JSON(cu) AS c6,<br>  TO_JSON(u) AS c7<br>LIMIT 100;</pre><h3>Step 9 — Advanced Visualizations with Python</h3><p>For richer visuals, install pyvis and Plotly in the notebook:</p><pre>!pip install pyvis plotly pandas db-dtypes -q</pre><p><strong>Interactive network graph with pyvis:</strong></p><pre>from pyvis.network import Network<br>from google.cloud import bigquery<br>import IPython.display as display</pre><pre>client = bigquery.Client(project=&quot;YOUR_PROJECT_ID&quot;)</pre><pre>query = &quot;&quot;&quot;<br>GRAPH accidents_graph.AccidentGraph<br>MATCH (a:Accident)-[:InvolvedVehicle]-&gt;(v:Vehicle)-[:CarriedUser]-&gt;(u:User)<br>WHERE u.grav IN (2, 3)<br>RETURN TO_JSON(a) AS accident, TO_JSON(v) AS vehicle, TO_JSON(u) AS user<br>LIMIT 60<br>&quot;&quot;&quot;</pre><pre>rows = list(client.query(query).result())</pre><pre># Important: TO_JSON returns dicts directly — no json.loads() needed<br>net = Network(height=&quot;650px&quot;, width=&quot;100%&quot;, bgcolor=&quot;#0f1117&quot;,<br>              font_color=&quot;white&quot;, notebook=True, cdn_resources=&quot;in_line&quot;)<br>net.barnes_hut(gravity=-8000, central_gravity=0.3, spring_length=120)</pre><pre>added_nodes = set()<br>for row in rows:<br>    a_props = row.accident.get(&quot;properties&quot;, {})<br>    v_props = row.vehicle.get(&quot;properties&quot;, {})<br>    u_props = row.user.get(&quot;properties&quot;, {})<br>    # add nodes and edges with colors by severity...</pre><pre>net.save_graph(&quot;accident_network.html&quot;)<br>display.HTML(&quot;accident_network.html&quot;)</pre><p><strong>Fatality heatmap — vehicle type × lighting conditions:</strong></p><pre>import plotly.express as px</pre><pre>query = &quot;&quot;&quot;<br>GRAPH accidents_graph.AccidentGraph<br>MATCH (a:Accident)-[:InvolvedVehicle]-&gt;(v:Vehicle)-[:CarriedUser]-&gt;(u:User)<br>WHERE u.grav = 2<br>RETURN v.catv AS vehicle_type, a.lum AS lighting, COUNT(*) AS fatalities<br>&quot;&quot;&quot;<br>df = client.query(query).to_dataframe()</pre><pre>VEHICLE = {1:&quot;Bicycle&quot;,2:&quot;Moped&quot;,7:&quot;Car&quot;,10:&quot;Van&quot;,13:&quot;Truck&quot;,30:&quot;Scooter&quot;,80:&quot;E-bike&quot;}<br>LIGHTING = {1:&quot;Daylight&quot;,2:&quot;Dawn/Dusk&quot;,3:&quot;Night-lit&quot;,4:&quot;Night-unlit&quot;,5:&quot;Night-no light&quot;}<br>df[&quot;vehicle_label&quot;]  = df[&quot;vehicle_type&quot;].map(VEHICLE).fillna(&quot;Other&quot;)<br>df[&quot;lighting_label&quot;] = df[&quot;lighting&quot;].map(LIGHTING).fillna(&quot;Unknown&quot;)</pre><pre>pivot = df.pivot_table(index=&quot;vehicle_label&quot;, columns=&quot;lighting_label&quot;,<br>                       values=&quot;fatalities&quot;, fill_value=0)</pre><pre>fig = px.imshow(pivot, color_continuous_scale=&quot;YlOrRd&quot;,<br>                title=&quot;Fatalities Heatmap: Vehicle Type x Lighting Conditions&quot;)<br>fig.show()</pre><h3>Key Learnings</h3><h3>What worked really well</h3><ul><li>GQL pattern matching makes multi-hop relationship queries dramatically simpler than equivalent SQL joins</li><li>No data duplication — the graph is a virtual layer on existing tables; existing pipelines don’t need to change</li><li>The %%bigquery --graph notebook visualization is excellent for exploratory analysis</li><li>BigQuery’s scale means the full 20-year national dataset (1.2M+ accidents) could be loaded and queried the same way</li></ul><h3>Gotchas to watch out for</h3><ul><li>A table cannot appear in both NODE TABLES and EDGE TABLES — use views as a workaround</li><li>GCS bucket and BigQuery dataset must be in the same region/multi-region</li><li>BAAC CSVs use double-quote as quote character — use --quote=&#39;&quot;&#39; not --quote=&quot;&quot;</li><li>id_vehicule is alphanumeric STRING in 2024, not INT64 — always verify with INFORMATION_SCHEMA first</li><li>The matricules file has no join key to the other tables — keep it as a standalone table</li><li>rows is a reserved keyword in BigQuery — use cnt or total as aliases</li></ul><h3>Graph vs SQL for this use case</h3><p>The real power shows when traversing relationships. Finding all users in all vehicles involved in all accidents on wet roads at night is a natural 3-hop GQL pattern. In SQL, that’s three joins with careful key management. As queries get deeper (5+ hops, variable path lengths), GQL becomes increasingly advantageous.</p><h3>What’s Next</h3><ul><li>Add multi-year data (2019–2024) for trend analysis</li><li>Use quantified path patterns to find accident hotspot chains along road networks</li><li>Integrate with Spanner Graph for real-time transactional queries alongside BigQuery’s batch analytics</li><li>Build a Looker Studio dashboard connected to GQL query results</li></ul><h3>Resources</h3><ul><li><a href="https://cloud.google.com/bigquery/docs/graph-intro">BigQuery Graph documentation</a></li><li><a href="https://www.data.gouv.fr/datasets/bases-de-donnees-annuelles-des-accidents-corporels-de-la-circulation-routiere-annees-de-2005-a-2024">BAAC dataset on data.gouv.fr</a></li><li><a href="https://tinyurl.com/bq-graph">Sign up for BigQuery Graph Private Preview</a></li><li><a href="https://cloud.google.com/spanner/docs/reference/standard-sql/graph-intro">ISO GQL reference</a></li></ul><p><em>If you found this useful, give it a clap and follow for more Google Cloud data engineering content. Questions? Drop them in the comments.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=af4189af9dbf" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>