{"id":1184,"date":"2026-10-13T15:46:20","date_gmt":"2026-10-13T15:46:20","guid":{"rendered":"https:\/\/javierladino.com\/fr\/?post_type=rara-portfolio&#038;p=1184"},"modified":"2026-10-14T06:20:48","modified_gmt":"2026-10-14T06:20:48","slug":"oiseaux-de-nantes-pipeline-de-donnees-de-biodiversite","status":"publish","type":"rara-portfolio","link":"https:\/\/javierladino.com\/fr\/portfolio\/oiseaux-de-nantes-pipeline-de-donnees-de-biodiversite\/","title":{"rendered":"Oiseaux de Nantes \u2014 Pipeline de donn\u00e9es de biodiversit\u00e9"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"571\" src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1024x571.png\" alt=\"Tableau de bord Power BI des observations d'oiseaux \u00e0 Nantes : indicateurs, carte de densit\u00e9, saisonnalit\u00e9 et classements\" class=\"wp-image-1186\" srcset=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1024x571.png 1024w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-300x167.png 300w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-767x428.png 767w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-108x60.png 108w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard.png 1310w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><br><strong>Un pipeline ELT de bout en bout sur AWS qui transforme chaque matin les observations naturalistes d&rsquo;iNaturalist en tableau de bord Power BI.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>AWS Lambda<\/code> <code>S3<\/code> <code>Athena<\/code> <code>dbt<\/code> <code>Apache Airflow<\/code> <code>Terraform<\/code> <code>Power BI<\/code> <code>GitHub Actions<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Projet personnel \u00b7 Conception, d\u00e9veloppement et d\u00e9ploiement \u00b7 <a href=\"https:\/\/github.com\/javiladino\/oiseaux_nantes\" target=\"_blank\" rel=\"noopener\">Code sur GitHub<\/a><\/p>\n\n\n\n<figure class=\"wp-block-video\"><video height=\"1080\" style=\"aspect-ratio: 1080 \/ 1080;\" width=\"1080\" controls src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/oiseaux_nantes_motion.mp4\"><\/video><\/figure>\n\n\n\n<h2 class=\"wp-block-heading has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-1\">Contexte et probl\u00e8me<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Des milliers de naturalistes partagent leurs observations d&rsquo;oiseaux sur iNaturalist, mais ces donn\u00e9es brutes (des fichiers JSON volumineux et imbriqu\u00e9s) ne permettent pas de r\u00e9pondre simplement \u00e0 des questions comme : <em>quelles esp\u00e8ces observe-t-on autour de Nantes ? \u00c0 quelle saison ? O\u00f9 ?<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">L&rsquo;objectif \u00e9tait de construire un pipeline fiable, automatis\u00e9 et peu co\u00fbteux qui collecte ces donn\u00e9es chaque jour, garantit leur qualit\u00e9 et les met \u00e0 disposition dans un outil d&rsquo;analyse, en appliquant les pratiques du modern data stack.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-2\">La solution<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"291\" src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/image-1024x291.png\" alt=\"Sch\u00e9ma d'architecture du pipeline ELT sur AWS : Lambda, S3, Athena et dbt, orchestr\u00e9s par Airflow, jusqu'\u00e0 Power BI\" class=\"wp-image-1188\" srcset=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/image-1024x291.png 1024w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/image-300x85.png 300w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/image-766x218.png 766w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/image-1536x437.png 1536w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/image-211x60.png 211w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/image-2048x583.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><br>Le pipeline suit une architecture <strong>ELT<\/strong> : les donn\u00e9es sont d&rsquo;abord charg\u00e9es brutes, puis transform\u00e9es dans le data lake.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Ingestion<\/strong>\u00a0: une fonction AWS Lambda interroge l&rsquo;API REST d&rsquo;iNaturalist de fa\u00e7on incr\u00e9mentale (seules les nouvelles observations depuis la derni\u00e8re ex\u00e9cution) et d\u00e9pose le JSON brut dans S3.<\/li>\n\n\n\n<li><strong>Transformation<\/strong>\u00a0: dbt, ex\u00e9cut\u00e9 sur Amazon Athena, nettoie et structure les donn\u00e9es selon une architecture m\u00e9daillon (raw \u2192 staging \u2192 marts) jusqu&rsquo;\u00e0 un\u00a0<strong>sch\u00e9ma en \u00e9toile<\/strong>\u00a0en Parquet.<\/li>\n\n\n\n<li><strong>Orchestration<\/strong>\u00a0: Apache Airflow 3 encha\u00eene chaque matin l&rsquo;ingestion puis chaque mod\u00e8le dbt et ses tests, avec reprises automatiques et alertes e-mail.<\/li>\n\n\n\n<li><strong>Restitution<\/strong>\u00a0: Power BI se connecte aux tables finales : carte de densit\u00e9, saisonnalit\u00e9, classements et photos des esp\u00e8ces.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-3\">Stack et mon r\u00f4le<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Couche<\/th><th>Outils<\/th><\/tr><\/thead><tbody><tr><td>Ingestion<\/td><td>AWS Lambda (Python), SSM Parameter Store<\/td><\/tr><tr><td>Stockage<\/td><td>Amazon S3 (data lake), AWS Glue Data Catalog<\/td><\/tr><tr><td>Transformation<\/td><td>dbt Core, Amazon Athena, Parquet<\/td><\/tr><tr><td>Orchestration<\/td><td>Apache Airflow 3, Astronomer Cosmos, EC2<\/td><\/tr><tr><td>Infrastructure<\/td><td>Terraform, IAM (moindre privil\u00e8ge), Session Manager<\/td><\/tr><tr><td>Qualit\u00e9 et CI<\/td><td>77 tests dbt, pytest, GitHub Actions<\/td><\/tr><tr><td>Visualisation<\/td><td>Power BI<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Mon r\u00f4le :<\/strong> projet r\u00e9alis\u00e9 de A \u00e0 Z : sp\u00e9cification, mod\u00e9lisation des donn\u00e9es, d\u00e9veloppement, infrastructure as code, d\u00e9ploiement en production et documentation.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-4\">D\u00e9tails techniques<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"584\" src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph-1024x584.png\" alt=\"DAG Airflow nantes_birds_pipeline : t\u00e2che d'ingestion suivie du groupe de t\u00e2ches dbt, ex\u00e9cut\u00e9 chaque jour \u00e0 05:00 UTC\" class=\"wp-image-1189\" srcset=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph-1024x584.png 1024w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph-300x171.png 300w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph-768x438.png 768w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph-105x60.png 105w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph.png 1346w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><br><strong>Ingestion incr\u00e9mentale et idempotente.<\/strong> Un <em>watermark<\/em> stock\u00e9 dans SSM m\u00e9morise la derni\u00e8re ex\u00e9cution r\u00e9ussie ; il n&rsquo;avance qu&rsquo;apr\u00e8s une \u00e9criture r\u00e9ussie dans S3. En cas d&rsquo;\u00e9chec, l&rsquo;ex\u00e9cution suivante reprend au m\u00eame point, sans perte ni doublon.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Qualit\u00e9 des donn\u00e9es comme garde-fou.<\/strong> Chaque mod\u00e8le dbt est suivi de ses tests (unicit\u00e9, valeurs nulles, zone g\u00e9ographique, int\u00e9grit\u00e9 r\u00e9f\u00e9rentielle). Un test en \u00e9chec bloque automatiquement tous les mod\u00e8les qui en d\u00e9pendent : une donn\u00e9e invalide n&rsquo;atteint jamais le tableau de bord.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Orchestration fine.<\/strong> Gr\u00e2ce \u00e0 Cosmos, chaque mod\u00e8le dbt devient une paire de t\u00e2ches Airflow (ex\u00e9cution + test) : 13 t\u00e2ches visibles, relan\u00e7ables individuellement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>S\u00e9curit\u00e9 et co\u00fbts.<\/strong> Instance EC2 sans aucun port ouvert (acc\u00e8s via Session Manager), r\u00f4les IAM au moindre privil\u00e8ge, Parquet compress\u00e9 et partitionn\u00e9. Co\u00fbt total : environ 20 $ par mois.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"515\" src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-model-1024x515.png\" alt=\"Sch\u00e9ma en \u00e9toile dans Power BI : table de faits fct_observations reli\u00e9e aux dimensions esp\u00e8ces, observateurs et dates\" class=\"wp-image-1190\" srcset=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-model-1024x515.png 1024w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-model-767x386.png 767w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-model-300x151.png 300w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-model-119x60.png 119w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-model.png 1435w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-5\">D\u00e9fis et apprentissages<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Le d\u00e9ploiement en conditions r\u00e9elles a r\u00e9v\u00e9l\u00e9 des probl\u00e8mes qu&rsquo;aucun environnement de test n&rsquo;aurait montr\u00e9s. Douze ont \u00e9t\u00e9 corrig\u00e9s et document\u00e9s, dont :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Une table incr\u00e9mentale dangereuse<\/strong>\u00a0: la strat\u00e9gie\u00a0<code>insert_overwrite<\/code>\u00a0aurait effac\u00e9 chaque jour les donn\u00e9es du mois en cours. Avec ~18 000 lignes, une reconstruction compl\u00e8te quotidienne s&rsquo;est r\u00e9v\u00e9l\u00e9e plus simple, toujours correcte et aussi peu co\u00fbteuse.<\/li>\n\n\n\n<li><strong>Des formats de date ISO 8601<\/strong>\u00a0avec fuseau horaire refus\u00e9s par Athena, corrig\u00e9s avec\u00a0<code>from_iso8601_timestamp<\/code>.<\/li>\n\n\n\n<li><strong>Un chargement initial trop long pour Lambda<\/strong>\u00a0(limite de 3 minutes) : l&rsquo;historique a \u00e9t\u00e9 charg\u00e9 une seule fois hors de Lambda, puis le pipeline ne traite que les nouveaut\u00e9s.<\/li>\n\n\n\n<li><strong>Des d\u00e9cisions de qualit\u00e9 de donn\u00e9es<\/strong>\u00a0: 77 observations sans date sont conserv\u00e9es en staging (avertissement) mais exclues du mod\u00e8le analytique.<br><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"594\" src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph2-1024x594.png\" alt=\"Historique Airflow : trois ex\u00e9cutions en \u00e9chec pendant le d\u00e9ploiement, puis toutes les ex\u00e9cutions suivantes r\u00e9ussies\" class=\"wp-image-1191\" srcset=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph2-1024x594.png 1024w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph2-767x445.png 767w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph2-300x174.png 300w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph2-330x190.png 330w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph2-103x60.png 103w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/airflow-dag-graph2.png 1349w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><br>Ce que j&rsquo;en retiens : un pipeline n&rsquo;est termin\u00e9 que lorsqu&rsquo;il tourne seul en production, et savoir quand <em>ne pas<\/em> utiliser une technique avanc\u00e9e fait partie du m\u00e9tier.<\/p>\n\n\n\n<h2 class=\"wp-block-heading has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-6\">R\u00e9sultats<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"571\" src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1-1024x571.png\" alt=\"Tableau de bord final : 4 726 observations en 2026, 306 esp\u00e8ces, 484 observateurs et 83,7 % de qualit\u00e9 recherche\" class=\"wp-image-1192\" srcset=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1-1024x571.png 1024w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1-300x167.png 300w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1-767x428.png 767w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1-108x60.png 108w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/powerbi-dashboard-1.png 1310w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><br><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u2705\u00a0<strong>En production<\/strong>\u00a0depuis octobre 2026, ex\u00e9cution automatique chaque matin<\/li>\n\n\n\n<li><strong>18 454 observations<\/strong>,\u00a0<strong>583 taxons<\/strong>,\u00a0<strong>1 874 observateurs<\/strong>\u00a0(de 1970 \u00e0 aujourd&rsquo;hui)<\/li>\n\n\n\n<li><strong>77 tests<\/strong>\u00a0de qualit\u00e9 des donn\u00e9es \u00e0 chaque ex\u00e9cution<\/li>\n\n\n\n<li><strong>~20 $ \/ mois<\/strong>\u00a0d&rsquo;infrastructure<\/li>\n\n\n\n<li>Documentation compl\u00e8te : guide de d\u00e9ploiement pas \u00e0 pas, exploitation, r\u00e9sultats de validation<br><br><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1012\" height=\"277\" src=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/athena-validation.png\" alt=\"Requ\u00eate Athena de validation : 18 454 lignes et 18 454 observations distinctes, sans doublon, 583 taxons, 1 874 observateurs\" class=\"wp-image-1193\" srcset=\"https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/athena-validation.png 1012w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/athena-validation-300x82.png 300w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/athena-validation-767x210.png 767w, https:\/\/javierladino.com\/fr\/wp-content\/uploads\/2026\/10\/athena-validation-219x60.png 219w\" sizes=\"auto, (max-width: 1012px) 100vw, 1012px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading has-vivid-cyan-blue-color has-text-color has-link-color wp-elements-7\">Prochaines \u00e9tapes et liens<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Prochaines \u00e9tapes :<\/strong> historisation SCD Type 2 avec les snapshots dbt, tests dbt sur Athena dans la CI (OIDC), d\u00e9ploiement continu, analyse horaire \u00e0 partir de l&rsquo;heure r\u00e9elle d&rsquo;observation.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd17 <a href=\"https:\/\/github.com\/javiladino\/oiseaux_nantes\" target=\"_blank\" rel=\"noopener\">Code et documentation sur GitHub<\/a> \u00b7 <a href=\"https:\/\/www.linkedin.com\/posts\/jladino_dataengineering-analyticsengineering-dbt-ugcPost-7515770152739659776-o55f\/\" target=\"_blank\" rel=\"noopener\">Publication LinkedIn<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udcbc <strong>Je suis ouvert \u00e0 des opportunit\u00e9s en Data Engineering \/ Analytics Engineering.<\/strong> <br><br><a href=\"mailto:%E2%80%A6\" target=\"_blank\" rel=\"noopener\">Contactez-moi<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Un pipeline ELT de bout en bout sur AWS qui transforme chaque matin les observations naturalistes d&rsquo;iNaturalist en tableau de bord Power BI. AWS Lambda S3 Athena dbt Apache Airflow Terraform Power BI GitHub Actions Projet personnel \u00b7 Conception, d\u00e9veloppement et d\u00e9ploiement \u00b7 Code sur GitHub Contexte et probl\u00e8me Des milliers de naturalistes partagent leurs &hellip; <\/p>\n","protected":false},"author":1,"featured_media":1185,"comment_status":"open","ping_status":"closed","template":"","meta":{"ngg_post_thumbnail":0},"rara_portfolio_categories":[35],"class_list":["post-1184","rara-portfolio","type-rara-portfolio","status-publish","has-post-thumbnail","hentry","rara_portfolio_categories-data-engineer"],"_links":{"self":[{"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/rara-portfolio\/1184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/rara-portfolio"}],"about":[{"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/types\/rara-portfolio"}],"author":[{"embeddable":true,"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/comments?post=1184"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/media\/1185"}],"wp:attachment":[{"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/media?parent=1184"}],"wp:term":[{"taxonomy":"rara_portfolio_categories","embeddable":true,"href":"https:\/\/javierladino.com\/fr\/wp-json\/wp\/v2\/rara_portfolio_categories?post=1184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}