How Animals Move
by Jan Busse & Charlotte Jordan
Geo-Tracking with Microsoft Fabric: Boars, Storks, and Whales
End-to-end tracking analysis with Fabric Lakehouse, PySpark, and Power BI — 5M+ points across species, cleaned, modeled, and visualized.
TL;DR
- 5M+ GPS points from multiple Movebank studies ingested into Fabric Lakehouse and standardized with PySpark.
- Step-level fact table (distance, deltas, speed, cumulative stats) + small dims; compact deterministic integer keys for lean joins.
- Power BI report with playback, cross-filtering, and species pages for boars, storks, and whales.
What we built
We ingested GPS tracks from multiple Movebank studies into Microsoft Fabric, standardized and cleaned them with PySpark (Bronze → Silver), computed step metrics (Haversine distance, time deltas, cumulative stats), then published a Power BI report (Gold) with an interactive playback to watch individuals move over time. We used compact, deterministic integer surrogate keys for lightweight joins, and a simple snowflake schema to keep the model readable and fast.
Get Jan Busse’s stories in your inbox
Join Medium for free to get updates from this writer.
Data sources (Movebank):
Storks (Bavaria) · Whales (Azores) · Wild pigs (Clemson, USA) · Boar study (Austria) · Wild boar (France)
Architecture at a glance
- Bronze — Raw CSV ingest into Delta tables (no transformations).
- Silver — Light standardization: column cleanup, robust timestamp parsing, coordinate casting, basic validity filters (lat/lon ranges, nulls).
- Gold — Step-level fact table with distances, time deltas, speeds, cumulative metrics + small dimension tables (animal, study, individual).
Medallion flow (Fabric Lakehouse + Notebooks + Pipelines):
The report
The Power BI report is split into three pages (species-focused). Each page offers:
- Playback: select an individual and hit Play to animate movement across timestamps.
- Distance: hour-of-day profiles, cumulative distance timelines.
- Filtering: cross-filter by species, study, individual.
What worked — and what didn’t
Worked well
- Fabric’s Lakehouse + Delta made I/O simple and repeatable.
- Small, deterministic keys helped with join speed and model size.
- Separating concerns (Bronze/Silver/Gold) kept notebooks focused and debuggable.
Limitations / trade-offs
- Data quality & imbalance vary heavily by study (e.g., storks dominate volume vs. boars). We applied basic validity rules (nulls, ranges) but avoided domain-specific filtering without expert input.
- Timestamp optimization: for DAX measures we kept full timestamps; splitting date/time into separate columns might help some models but wasn’t necessary here.
- Keys at massive scale: Our hash-based small integers are fine for tiny dims (3 animals, ~5 studies, ~70 individuals). For very large/slowly changing dims, consider persisted dimension tables with “assign-on-first-seen” keys (MERGE) to guarantee stability forever.
- “Road crossings”: meaningful for boars on the ground, not for flying storks/whales; we left this as a scoped, species-specific extension.
If you want to extend this
- Species-specific features: diurnal vs. nocturnal movement, habitat overlays (land cover, elevation), or — where it makes sense — road crossing detection via OSM + spatial joins (Apache Sedona).
- Anomaly detection: flag GPS errors or biologically implausible speeds (species-aware thresholds).
- Forecasting / classification: e.g., predict next location, classify activity states from speed/turning angles.
Key takeaways
- You can build a clean, end-to-end geo-tracking pipeline entirely in Fabric: ingest → standardize → model → visualize.
- Small engineering choices (parsing, validation, keys, schema) drive report performance and analytical clarity.
- Understanding the data-generation process matters; better hypotheses require both good pipelines and domain knowledge.
