The start of a series that goes through the SQL of Databricks statement by statement, every one run on the same small tables. This part: why, what you need, and the sample data – a fictional bike rental in Lucerne with six tables and a few rows that are wrong on purpose.
The last part of the series: what 22 hours of crowdsourced aircraft positions could answer, what worked, what didn't, what I'd do differently – what's left to analyse, and the code as a public repository.
Fifteen parts of notebooks end in two small apps: a marimo notebook that runs as a dashboard, and a live map that collects, analyses and draws in one process. How they're built, and where DuckDB's single writer shaped the design.
During the 37 minutes without take-offs, the arrivals at Zurich landed as if nothing had happened: no holding, no go-around, normal approach times. My go-around detector found six go-arounds in 22 hours anyway – all six were echoes in the data.
My flight data is a sample: what the OpenSky receivers picked up. EUROCONTROL counts every IFR flight. For Zurich on 20 and 21 October, my estimate lands within a few percent of the official count – but the check is rougher than that number suggests.
I put the 10-minute measurements of the MeteoSwiss station at Zurich airport next to the take-offs and landings. In 22 hours the weather barely moved the traffic – the one big dip was the Skyguide fault. The wind fits the runway change that part 12 couldn't explain – but it doesn't prove the cause.
I put the low aircraft positions on the map of the Swiss municipalities and the BFS population grid. About 31,000 people near Zurich airport had at least ten aircraft pass below 3,000 ft (about 900 m) within 500 metres in one day. Most of them live inside the official noise contours – but not all.
Of the flights to and from Zurich in my data, 44 % go to cities the course has a rail time for. For six of them, the train takes at most an hour longer door to door – and per person it emits a fraction. What the numbers say, and what they assume.
Questions for a database of aircraft positions: when exactly did Zurich stop during the Skyguide fault, what happens at night, and how many aircraft only pass through? The press had 'almost an hour'. The positions have the minutes.
A small cleanup in the shop changes 1,276 orders, and the revenue dashboard quietly goes wrong. What the Open Data Contract Standard (ODCS) is, what goes into a contract – and how a contract stops that change in a pull request, tested hands-on with the datacontract CLI, DuckDB and GitHub Actions.
Weather at flight level for every cruise position, and the physics of contrails: two thirds of the positions form one, a third leave a persistent one. Or fewer than 4 % – if the humidity is one rounding step lower.
A transponder doesn't send fuel flow. An open aircraft performance model turns type, altitude and speed into an estimate: about 8,800 tonnes of CO₂ over Switzerland in 22 hours – with a range of −18 to +24 % from one unknown alone, and a third of the traffic the model can't see.
ADS-B doesn't say where a flight is going. A free route database does – and my own positions contradict almost a quarter of its answers. How I checked 3,664 routes, and how far flights over Switzerland really deviate from the great circle.
Gold answers questions: where is every aircraft right now, who is landing where, how busy is each hour? Three SQL views on silver – and why 'the nearest airport' sent 129 Zurich arrivals to an air base.
Turning raw OpenSky answers into clean, typed positions with airline names – incrementally. And the bug that would have silently dropped 2,150 snapshots: a time watermark meets a backfill.
My laptop slept, the home server didn't. How the OpenSky answers in a Cloudflare R2 bucket are organised, how a backfill fills the gaps in bronze without downloading anything twice – and what it means when two sources write into the same table.
From one API call to one every 35 seconds: what the credits allow, what to do after a 429, why a loop drifts – and what my own collector actually did over one afternoon and evening.
Every OpenSky answer goes into DuckDB exactly as it arrived – once, no matter how often you store it. Two tables, one primary key, and what a full day of raw data looks like.
Where live flight data comes from, how ADS-B and the OpenSky Network work, and how to fetch every aircraft over Switzerland with ten lines of Python.
Deploy your Data Stack with Docker and Cloudflare in an automated, secure, and scalable way.
Self-hosting is awesome – you have full control over your data, no subscription fees, and you learn a lot about system administration along the way. But it comes with real challenges: security concern
If you've ever tried to learn Change Data Capture (CDC), streaming pipelines, or incremental data loading, you've probably hit the same frustrating wall I did: **static sample databases don't teach yo
In modern data workflows, it’s essential to collect data where it originates and deliver it to where it’s needed. In this blog post, I’ll show how to connect **SharePoint directly with Databricks** –
In this article I describe how to load data from recurring full snapshots with Delta Live Tables relatively easily and elegantly into a bronze table without the amount of data exploding.
In this blog post, I explore how to set up Databricks SQL Warehouse as a Linked Server in MS SQL Server to seamlessly query data from your Lakehouse directly within SQL Server.
In this step-by-step tutorial, you’ll learn how to set up an Azure DevOps pipeline for seamless deployment of Databricks asset bundles. The post walks you through the entire process—from configuring your Azure DevOps project to automating the deployment of Notebooks, Workflows, and other Databricks resources.
In this article, you will learn how to set up date and time dimensions in Databricks to enable precise time-based analyses and reports.
In data engineering in the Azure Cloud, a common setup is to use Azure Data Factory to orchestrate data pipelines. If you wanted to orchestrate Databricks pipelines, you had a powerful tool at hand wi
In the upcoming blog series, I will highlight different areas of the Databricks CLI with various practical examples.
I will show you different methods to authenticate with the Databricks CLI to Azure Databricks in this quick guide.
In this HowTo I will show you how to load weather data from the OpenWeather API into the Databricks Lakehouse. The data is loaded via REST API from OpenWeather and then processed in the Medallion arch
In this quick guide, I will show you how to automatically copy all schemas and tables from one catalog to another in Databricks.
In this step-by-step guide, I will show you how to install dbt core locally in Visual Studio Code, set up the corresponding Visual Studio Code Extension and run dbt on Databricks.
In this quick guide, I will show you how to set up an Airbyte environment in a virtual machine in Azure.
In this step-by-step guide, I describe how to create a Databricks Lakehouse environment in the Azure Cloud within 30 minutes. This guide is aimed at people who are new to Databricks and want to create
I have now decided in the old year to write on Medium as well as my personal blog.
Azure is Microsoft’s cloud computing platform. It offers a comprehensive suite of cloud services that enable companies to develop, deploy and manage applications without having to have physical hardware on site. Azure enables users to use resources such as virtual machines, storage, databases, networks and much more in Microsoft’s globally distributed data centers. The Azure Cloud is one of the leading hyper-scalers on the market. In the following article, I would like to give beginners a few useful tips to make it easier to get started with the Azure portal.
In this quick guide I will show you how to connect to a Databricks SQL Warehouse Cluster with DBeaver.
