You can query the 100,000-row ADSBiq Caribbean sample with DuckDB, but first account for its state-diff format: a row may contain only fields that changed, not a complete aircraft record. The sample covers positioned observations within 5–30° N and 95–55° W on 2026-06-29. It is a bounded sample, not a complete record of Caribbean air traffic.
What this sample contains—and what it does not
The ADSBiq dataset card describes a reproducible educational sample of 100,000 positioned observations from its daily open ADS-B corpus. The sample is provided as a 1.58 MB Parquet file and is tied to a single date, 2026-06-29, within a rectangular area from 5° to 30° north and 95° to 55° west. Those figures describe the dataset card’s sample, not continuous coverage of the whole Caribbean or every aircraft in the area.
As an Amazon Associate I earn from qualifying purchases.
The publisher says the observations come from a distributed community receiver network. Coverage varies with receiver location, antenna placement, terrain, aircraft altitude, traffic and time. As the ADSBiq dataset card cautions, “Missing observations do not prove that no aircraft were present.” Do not use gaps in this sample as proof that an aircraft was absent, or present the sample as the complete ADSBiq corpus.
Free tools Windows power users keep installed
One-click scans. No signup required.
The card links a DuckDB notebook described as memory-safe, but the workflow below is an explanatory pattern rather than a tested benchmark or a claim that the notebook was run. Check the dataset card for the file and notebook links and any current schema details before adapting a query.
#1 Best Overall
Why state-diff rows must be reconstructed
The sample uses state diffs. A row marked is_snapshot=true contains a full state; later rows can include only fields that changed. A blank or null field in a diff therefore need not mean that the aircraft’s value became unknown—it may mean the value was unchanged and omitted.
Common fields include ts (timestamp), hex (aircraft identifier), flight, lat, lon, alt_baro, gs, track, squawk and type. For a meaningful time series or movement summary, carry the latest known value forward within each aircraft’s ordered sequence. Counts based on raw rows can count updates rather than distinct aircraft or complete states.
Rank #2
Load and inspect the Parquet file in DuckDB
Use the Parquet file linked from the ADSBiq dataset card as the input. DuckDB can query Parquet directly; substitute the path to the downloaded file for sample.parquet in the examples. These SQL snippets illustrate the documented reconstruction convention and have not been independently executed against the notebook or file.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →-
Inspect the available columns and types:
DESCRIBE SELECT * FROM read_parquet('sample.parquet'); -
Inspect representative rows, including snapshot markers and timestamps:
SELECT ts, hex, is_snapshot, flight, lat, lon, alt_baro, gs, track, squawk, type FROM read_parquet('sample.parquet') ORDER BY hex, ts LIMIT 20;
Confirm the actual timestamp type, null representation and column names before relying on these expressions. In particular, check that rows can be ordered consistently by ts for each hex.
Reconstruct each aircraft’s latest known state
Order records by aircraft and timestamp, then forward-fill each nullable state field. In DuckDB SQL, a window expression using LAST_VALUE with IGNORE NULLS can express that operation when supported by the installed DuckDB version and the column types:
Rank #4
WITH ordered AS (
SELECT *,
row_number() OVER (PARTITION BY hex ORDER BY ts) AS row_in_aircraft
FROM read_parquet('sample.parquet')
), reconstructed AS (
SELECT
ts,
hex,
is_snapshot,
row_in_aircraft,
last_value(flight IGNORE NULLS) OVER w AS flight,
last_value(lat IGNORE NULLS) OVER w AS lat,
last_value(lon IGNORE NULLS) OVER w AS lon,
last_value(alt_baro IGNORE NULLS) OVER w AS alt_baro,
last_value(gs IGNORE NULLS) OVER w AS gs,
last_value(track IGNORE NULLS) OVER w AS track,
last_value(squawk IGNORE NULLS) OVER w AS squawk,
last_value(type IGNORE NULLS) OVER w AS type
FROM ordered
WINDOW w AS (
PARTITION BY hex
ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
)
SELECT *
FROM reconstructed
ORDER BY hex, ts;This pattern assumes null means “not supplied in this diff.” If the source distinguishes an omitted field from an explicit clearing of a value, a plain null-based forward fill would not preserve that distinction; confirm the card’s schema semantics before making that interpretation. Also verify how ties in ts should be ordered if multiple rows for one aircraft share a timestamp.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Derive summaries only from reconstructed states
Once fields have been carried forward, aggregate the reconstructed output rather than the raw diffs. For example, to count records with a known position by aircraft, wrap the reconstruction query and group the results:
Best Value
WITH reconstructed AS (
-- Use the reconstructed CTE from the preceding query.
SELECT * FROM reconstructed_states
)
SELECT hex, count(*) AS positioned_rows
FROM reconstructed
WHERE lat IS NOT NULL AND lon IS NOT NULL
GROUP BY hex
ORDER BY positioned_rows DESC;In a runnable query, replace the illustrative reconstructed_states reference with the preceding reconstruction CTE. This count measures reconstructed sample rows with positions; it is not a count of unique flights, all aircraft in the region, or total Caribbean traffic. Likewise, a speed or altitude summary describes recorded sample states, subject to the same coverage and reconstruction limits.
When a different historical source may fit better
OpenSky is a separate source, not the origin of this ADSBiq sample. Its historical state vectors are documented through Trino and MinIO, and access is application-reviewed. OpenSky’s guide describes access for university-affiliated researchers, government organizations and aviation authorities conducting aviation research or incident investigations; private or commercial entities are directed to contact OpenSky for a license.
OpenSky’s state_vectors_data4 schema includes Unix time, hexadecimal icao24, WGS84 latitude and longitude, velocity, heading, vertical rate, callsign, ground status, altitude, last-contact time, receiver serials and an hour partition. Its documented units include metres, metres per second and seconds. The guide requires filtering state-vector queries by hour; unpartitioned scans are disallowed or rejected under current enforcement. It also gives time - lastcontact <= 15 as an example stale-data condition and says vectors may remain for up to 300 seconds after an aircraft leaves coverage. These are OpenSky-specific details, not rules for the ADSBiq Parquet sample. See the OpenSky historical-data guide for current access and query requirements.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →OpenSky also catalogs other scientific datasets, including weekly state-vector snapshots from 2017–2022, an ADS-C dataset, aircraft metadata and a March 2026 snapshot of Trino tables. Those options have their own coverage and access conditions; the OpenSky datasets catalog is the starting point for evaluating them.
Attribute and reuse the data correctly
The ADSBiq dataset card states that the data is licensed under ODbL-1.0 and requests attribution to the ADSB IQ feeder network. It separately identifies example code in the API-demo repository as MIT-licensed; the code license does not replace or change the data license. Review the card’s terms before redistributing derived data or the source file.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
