Real project
40+ countries of shipping reports, turned into one daily dataset.
An automated operating system that turns inconsistent maritime reports into clean, daily, decision-ready datasets across more than 40 countries.
40+countries, one reliable flow
SQL EXAMPLE
How the sources are merged: the SQL
A simplified version of the Snowflake query I use to merge three lineup sources into one deduplicated dataset. Names are placeholders; the structure follows my production query.
-- Merge three vessel lineup sources into one deduplicated dataset (Snowflake).
-- All database, table and source names are placeholders.
WITH all_sources AS (
-- each source exposes the same columns
SELECT *, 'source_a' AS src FROM demo_db.lineups.source_a_daily
UNION ALL
SELECT *, 'source_b' AS src FROM demo_db.lineups.source_b_weekly
UNION ALL
SELECT *, 'source_c' AS src FROM demo_db.lineups.source_c_weekly
WHERE "ETS Reporter" < DATE '2025-04-01'
),
-- Step 1: normalize the matching keys
normalized AS (
SELECT *,
TRIM(LOWER(SPLIT_PART("Vessel Name", '/', 1))) AS vessel_key,
"Product":"Code"::STRING AS product_code,
TO_DATE("ETS Reporter") AS ets_date,
-- quantity bands about 10% wide, on a log scale
IFF("Quantity" > 0, FLOOR(LN("Quantity") / LN(1.1)), 0) AS quantity_band
FROM all_sources
),
-- Step 2: start a new group when reports are more than 9 days apart
flagged AS (
SELECT *,
IFF(DATEDIFF(day,
LAG(ets_date) OVER (PARTITION BY vessel_key, product_code, quantity_band
ORDER BY ets_date, src),
ets_date) > 9, 1, 0) AS new_group
FROM normalized
),
grouped AS (
SELECT *,
SUM(new_group) OVER (PARTITION BY vessel_key, product_code, quantity_band
ORDER BY ets_date, src
ROWS UNBOUNDED PRECEDING) AS time_group
FROM flagged
),
-- Step 3: count how many sources report the same shipment
counted AS (
SELECT *,
COUNT(DISTINCT src) OVER (PARTITION BY vessel_key, product_code,
quantity_band, time_group) AS sources_in_group
FROM grouped
)
-- Step 4: keep single-source shipments; when several sources report one, drop source A's copy
SELECT
"Cut-Off Date",
src AS data_source,
"Country Reporter":"Code"::STRING AS country_reporter,
"Port Reporter":"Code"::STRING AS port_reporter,
"Trade Direction":"Code"::STRING AS trade_direction,
product_code,
"Quantity",
"Vessel Name",
"Vessel IMO",
ets_date
FROM counted
WHERE sources_in_group = 1 OR src <> 'source_a'
ORDER BY vessel_key, product_code, quantity_band, time_group, ets_date;