Logistics operations run on time-critical data. A shipment exception that surfaces 10 minutes after it occurs is an operational problem. An inventory level that updates every hour instead of every few seconds is a stockout waiting to happen. A fleet dashboard showing vehicle positions from 5 minutes ago is a routing tool that can't respond to live conditions.
ClickHouse® handles the event volume that modern logistics platforms generate: shipment status updates, warehouse scan events, inventory transactions, and vehicle telemetry all flowing continuously at a rate that traditional operational databases can't query analytically in real time.
This post covers the event schemas, shipment tracking queries, inventory analytics, fleet operations patterns, and supply chain visibility queries that logistics engineering teams build with ClickHouse.
Shipment event schema
Logistics data is append-only event history. A shipment produces a sequence of status events (created, picked up, in transit, out for delivery, delivered) that form the audit trail for customer visibility and carrier performance. The MergeTree engine with a sort key aligned to operational query patterns is the right foundation:
CREATE TABLE shipment_events
(
event_time DateTime64(3),
event_type LowCardinality(String), -- 'created', 'pickup', 'in_transit', 'out_for_delivery', 'delivered', 'exception'
shipment_id String,
carrier_id LowCardinality(String),
origin_facility LowCardinality(String),
dest_facility LowCardinality(String),
service_level LowCardinality(String), -- 'ground', 'express', 'overnight'
region LowCardinality(String),
exception_code LowCardinality(Nullable(String)),
exception_reason LowCardinality(Nullable(String)),
promised_delivery Date,
latitude Nullable(Float32),
longitude Nullable(Float32),
scan_location LowCardinality(Nullable(String)),
weight_kg Nullable(Float32),
properties String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (carrier_id, region, event_time, shipment_id);
carrier_id and region first in the sort key reflects the primary operational query pattern: carrier performance dashboards and regional ops centers both filter by these dimensions before time. LowCardinality on high-frequency string columns like event_type, carrier_id, service_level, and exception_code significantly reduces GROUP BY cost on the operational queries that run continuously.
Shipment tracking and on-time performance
Carrier on-time delivery rate is the primary SLA metric for logistics operations. Computing it across all active carriers in real time:
SELECT
carrier_id,
service_level,
count() AS total_delivered,
countIf(event_time <= toDateTime(promised_delivery) + INTERVAL 1 DAY) AS on_time,
round(
countIf(event_time <= toDateTime(promised_delivery) + INTERVAL 1 DAY)
/ count() * 100, 2
) AS on_time_pct,
avg(
dateDiff('hour', toDateTime(promised_delivery), event_time)
) AS avg_delay_hours
FROM shipment_events
WHERE event_type = 'delivered'
AND event_time >= today() - 30
GROUP BY carrier_id, service_level
ORDER BY on_time_pct ASC;
Exception rate by carrier and exception type, for identifying systemic carrier issues:
SELECT
carrier_id,
exception_code,
exception_reason,
count() AS exception_count,
uniq(shipment_id) AS affected_shipments,
toStartOfWeek(event_time) AS week
FROM shipment_events
WHERE event_type = 'exception'
AND event_time >= today() - 90
GROUP BY carrier_id, exception_code, exception_reason, week
ORDER BY week DESC, exception_count DESC;
For customer-facing tracking, using windowFunnel to detect shipments stuck in a specific status longer than expected:
SELECT
shipment_id,
carrier_id,
region,
max(event_time) AS last_event_time,
argMax(event_type, event_time) AS current_status,
dateDiff('hour', max(event_time), now()) AS hours_since_last_scan
FROM shipment_events
WHERE event_time >= today() - 7
GROUP BY shipment_id, carrier_id, region
HAVING current_status NOT IN ('delivered', 'returned')
AND hours_since_last_scan > 24
ORDER BY hours_since_last_scan DESC;
Shipments with no scan event in over 24 hours while not in a terminal status are stalled. This query surfaces them across millions of active shipments in under a second.
Inventory analytics
For real-time inventory management, the ClickHouse pattern mirrors the logistics event model: every inventory transaction is an event (receipt, sale, adjustment, transfer), and current stock levels are computed via ReplacingMergeTree or materialized aggregation:
CREATE TABLE inventory_events
(
event_time DateTime64(3),
event_type LowCardinality(String), -- 'receipt', 'sale', 'adjustment', 'transfer_out', 'transfer_in'
sku_id String,
facility_id LowCardinality(String),
quantity_delta Int32, -- positive for inflows, negative for outflows
unit_cost Nullable(Float32),
order_id Nullable(String),
batch_id Nullable(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (facility_id, sku_id, event_time);
-- Current stock level per SKU per facility
SELECT
sku_id,
facility_id,
sum(quantity_delta) AS current_stock,
countIf(event_type = 'sale') AS total_sales,
sumIf(quantity_delta, event_type = 'receipt') AS total_receipts
FROM inventory_events
WHERE event_time >= today() - 365
GROUP BY sku_id, facility_id
HAVING current_stock < 50
ORDER BY current_stock ASC;
For reorder point detection, combining current stock with recent sales velocity:
WITH sales_velocity AS (
SELECT
sku_id,
facility_id,
abs(sum(quantity_delta)) / 30 AS avg_daily_sales
FROM inventory_events
WHERE event_type = 'sale'
AND event_time >= today() - 30
GROUP BY sku_id, facility_id
),
current_stock AS (
SELECT
sku_id,
facility_id,
sum(quantity_delta) AS stock
FROM inventory_events
GROUP BY sku_id, facility_id
)
SELECT
cs.sku_id,
cs.facility_id,
cs.stock,
sv.avg_daily_sales,
round(cs.stock / nullIf(sv.avg_daily_sales, 0), 1) AS days_of_stock
FROM current_stock AS cs
INNER JOIN sales_velocity AS sv USING (sku_id, facility_id)
WHERE days_of_stock < 7
ORDER BY days_of_stock ASC;
SKUs with fewer than 7 days of stock at current sales velocity are reorder candidates. This query runs in under a second across millions of SKU-facility combinations.
Fleet and last-mile operations
For delivery fleet operations, real-time vehicle telemetry analysis:
SELECT
carrier_id,
region,
count() AS active_vehicles,
countIf(
dateDiff('minute', max(event_time), now()) > 10
) AS offline_vehicles,
avg(JSONExtractFloat(properties, 'speed_kmh')) AS avg_speed,
countIf(
JSONExtractFloat(properties, 'speed_kmh') = 0
AND event_type = 'in_transit'
) AS stationary_in_transit
FROM shipment_events
WHERE event_type IN ('in_transit', 'out_for_delivery')
AND event_time >= now() - INTERVAL 1 HOUR
AND latitude IS NOT NULL
GROUP BY carrier_id, region
ORDER BY offline_vehicles DESC;
A high stationary_in_transit count for a region may indicate a traffic incident or vehicle breakdown, surfaceable in a live ops dashboard without polling an external traffic API.
Supply chain visibility and lead time analytics
For procurement teams measuring supplier performance and lead times, using change data capture tools to sync purchase orders from the ERP into ClickHouse:
SELECT
supplier_id,
product_category,
count() AS total_orders,
avg(
dateDiff('day', order_date, delivery_date)
) AS avg_lead_days,
quantile(0.95)(
dateDiff('day', order_date, delivery_date)
) AS p95_lead_days,
countIf(
dateDiff('day', order_date, delivery_date) >
JSONExtractInt(properties, 'promised_lead_days')
) AS late_deliveries,
round(
countIf(
dateDiff('day', order_date, delivery_date) >
JSONExtractInt(properties, 'promised_lead_days')
) / count() * 100, 2
) AS late_rate_pct
FROM purchase_orders
WHERE order_date >= today() - 365
GROUP BY supplier_id, product_category
ORDER BY late_rate_pct DESC;
Carrier scorecard for procurement
Quarterly carrier reviews compare SLA performance, exception rates, and transit times to inform carrier selection and contract negotiations. ClickHouse computes the full scorecard in a single query against the event history rather than requiring a separate reporting extract:
SELECT
carrier_id,
service_level,
count() AS total_shipments,
round(
countIf(event_time <= toDateTime(promised_delivery) + INTERVAL 1 DAY) /
count() * 100, 2
) AS on_time_pct,
round(
countIf(event_type = 'exception') /
uniq(shipment_id) * 100, 2
) AS exception_rate_pct,
avg(
dateDiff('hour',
minIf(event_time, event_type = 'pickup'),
maxIf(event_time, event_type = 'delivered')
)
) AS avg_transit_hours,
quantile(0.95)(
dateDiff('hour',
minIf(event_time, event_type = 'pickup'),
maxIf(event_time, event_type = 'delivered')
)
) AS p95_transit_hours
FROM shipment_events
WHERE event_time >= today() - 90
GROUP BY carrier_id, service_level
ORDER BY on_time_pct ASC;
Procurement teams use this query to compare carriers on the same lanes and service levels, with p95 transit time revealing which carriers are consistent versus which have occasional extreme outliers hidden by a decent average.
Cross-border and customs analytics
International shipments add customs clearance as an additional failure mode alongside carrier exceptions. ClickHouse tracks clearance events alongside standard shipment events, enabling customs-specific SLA analysis:
SELECT
dest_facility,
carrier_id,
count() AS cross_border_shipments,
countIf(
JSONExtractString(properties, 'clearance_status') = 'held'
) AS held_in_customs,
avg(
JSONExtractFloat(properties, 'clearance_days')
) AS avg_clearance_days,
quantile(0.95)(
JSONExtractFloat(properties, 'clearance_days')
) AS p95_clearance_days,
countIf(
dateDiff('day',
toDateTime(promised_delivery),
event_time
) > 0
AND JSONExtractString(properties, 'clearance_status') = 'held'
) AS late_due_to_customs
FROM shipment_events
WHERE event_type = 'customs_clearance'
AND event_time >= today() - 60
GROUP BY dest_facility, carrier_id
ORDER BY late_due_to_customs DESC;
p95_clearance_days by destination and carrier tells procurement teams which lanes and carriers produce customs delays most often, informing routing decisions before the shipment is booked rather than after it misses SLA.
Tinybird for logistics and supply chain
The operational reality of logistics analytics is that the data comes from many sources: carrier APIs emit shipment scans, warehouse management systems emit inventory transactions, TMS systems emit routing events, and ERP systems emit order and purchase records. Each arrives on its own schedule and in its own format. The analytics layer needs to unify all of it and serve it at operational speed.
Tinybird is managed ClickHouse with a streaming Events API and Kafka connector for high-throughput topics. Each event source writes to its own Tinybird datasource. SQL Pipes join across datasources to build unified views. HTTP endpoints expose the operational queries in this post as APIs your ops dashboards, alerting systems, and customer-facing tracking tools call directly.
Build dashboards with real-time data interaction is the pattern for logistics control towers: a dashboard that shows current shipment exceptions, carrier SLA performance, and inventory risk positions, all from parameterized Tinybird endpoints that refresh every 30 seconds with fresh data. No cache invalidation, no pre-computation jobs, no stale data in the ops center.
For CDC from ERP and WMS systems, real-time streaming architectures describes the patterns that handle schema evolution, replication lag, and high-volume change streams reliably. Your source-of-truth systems stay exactly where they are. Tinybird receives changes within seconds and makes them queryable across the full event history.
The shipment exception query in this post, the one that surfaces stalled shipments across millions of active shipments in under a second, becomes a Tinybird Pipe with a carrier_id and region parameter. Your operations center calls it every minute. Your alerting system calls it. Your customer-facing tracking API calls it with a shipment_id parameter. One SQL definition, multiple callers, all getting fresh results from the same underlying ClickHouse table.
Tinybird documents that their internal growth analytics runs on Tinybird itself, treating the platform as the right tool for real-time operational dashboards that need fresh data. That same pattern applies directly to logistics control towers: the data engineering team maintains the Tinybird datasources and Pipes, and the operations team gets a live dashboard without maintaining a separate data pipeline.
Tinybird is SOC 2 Type II certified. Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. The pipeline from event source to queryable HTTP endpoint takes hours to configure rather than weeks to build. Your ERP, WMS, TMS, and carrier APIs all feed into Tinybird. Your ops team sees unified logistics data in real time.
Latency is an operational decision
In logistics, the gap between when something happens and when it becomes visible in a dashboard is a business decision, even if it was never made explicitly. Systems built on batch ETL make that decision at 24 hours. Systems built on hourly reporting make it at 60 minutes. What ClickHouse changes is that the technical cost of making that gap seconds instead of hours becomes negligible. The queries in this post run against live event streams. The cost of the operation is a ClickHouse table and a sort key.
The operations teams using these dashboards don't see ClickHouse. They see a control tower where exceptions surface in real time, carrier SLA scores update as deliveries complete, and inventory risk positions refresh as transactions post. The analytical infrastructure underneath is invisible to them. That's the point.