Order analytics: finance, customer service, merchant portal, loyalty history

Table format. All new Iceberg tables use format v2. v3 (deletion vectors, row lineage) is not readable by every engine yet: Athena cannot read it and Trino's support is experimental, and Trino is the query engine here. Move to v3 only when every engine that reads the tables supports it.

Small files. Raw is partitioned by ingestion hour, so every hour holds many tiny files and nothing downstream should query it directly. The fix is compaction plus not querying raw: dashboards read core and marts. Raw keeps its ingestion-hour partitioning because it makes "what arrived since last run" a cheap read. Core is partitioned by order date, at day grain (hourly partitions are what created the problem), and written in large batches.

Late events (up to 3 days). A late event arrives in a new ingestion hour, so it is found by reading new hours; the trailing window of 4 days (3 plus one day of margin) re-reads recent hours so a failed or delayed run recovers itself. Merging is by order id and keeps the version with the latest event time, so a late older event cannot overwrite a newer state, and rerunning gives the same result. Every mart is rebuilt for each order date or month an arriving event touches, by overwriting that partition, never by adding to it. An event older than the window is not applied silently: it goes to the quarantine table, where its count is monitored.

Finance: monthly revenue by region and product category. It is an aggregate, so it stays in the lake as marts.finance_monthly_revenue (month, region, product category, revenue) and is read through Trino by Superset. Because events can arrive 3 days late, a month's figure can still change for 3 days after month end; the table carries a "provisional" flag until the 4th, and the dashboard shows it. Revenue is defined once, in the dbt model, so there is a single definition (assumed: completed orders, net of refunds, line amount times quantity; see Open questions).

Customer service: searchable order list, ~200 agents, CSV export. This is a known, constant load of many concurrent row-level lookups, so it goes to a serving store, not Trino (which is built for fewer, larger analytic queries). serving.cs_orders in a new managed PostgreSQL holds one row per order (customer, status, order date, amounts, items summary) with indexes on customer, status and order date; it is synced from core.orders every 15 minutes by upserting changed orders only. A separate database means agents can never slow the merchant app. Agents use a Superset table dashboard on this store, with filters for customer, status and date range and Superset's CSV export. The customer's name and contact details are in scope because agents need them: the store has a restricted cs_agent role with read-only access to this one table; export has a row cap (assumed 50,000) and is limited to agent and supervisor roles; every export is logged. Policy questions (retention of this copy, which fields agents may export) are in Open questions. The sync follows the whole-load rule below.

Merchant portal: daily aggregates. Reverse ETL: marts.merchant_daily_sales (merchant, date, orders, units, gross and net sales) is loaded into a read-only analytics schema inside the app's PostgreSQL. The pipeline owns that schema; the app only reads it and never writes there, so an app write and a load can't collide, and the pipeline never writes into the app's own tables. Each load is built in a staging table and then swapped in within a single transaction, so each load becomes visible whole and the portal never shows a half-loaded day. The load replaces the trailing days (late events change them), and the portal always filters by the signed-in merchant's id. This removes the live aggregation from the app's database; the app now only does indexed reads of a small table. Data is at most about an hour old.

Product: loyalty tier over time. core.customer_tier_history is a slowly-changing-dimension (type 2) table: one row per customer per tier with valid_from, valid_to and is_current. It is built from tier changes in event-time order, so a late event rebuilds only that customer's rows (the intervals before and after it are corrected). The table is small and not partitioned. History is only as old as the first data the events carry; changes before that cannot be reconstructed.

Freshness assumed (per consumer, in minutes). Customer service order list: 15. Merchant daily sales: 60. Finance and product: daily is enough; they refresh hourly at no extra cost.