A denormalised schema that is rebuilt rather than synchronised

A reporting schema shaped for the questions, populated by a full rebuild, which removes every synchronisation bug.

CREATE TABLE rpt_order_facts (
  order_id        BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  customer_id     BIGINT UNSIGNED NOT NULL,
  customer_name   VARCHAR(255) NOT NULL,   -- denormalised
  customer_tier   VARCHAR(32) NOT NULL,    -- denormalised
  placed_date     DATE NOT NULL,
  line_count      SMALLINT UNSIGNED NOT NULL,
  net_minor       BIGINT NOT NULL,
  tax_minor       BIGINT NOT NULL,
  KEY idx_date_tier (placed_date, customer_tier)
);

Denormalisation is safe here because the table is truncated and rebuilt rather than updated, so there is no path by which the copy can disagree with the source — the copy is either current or absent. That is the property that makes this different from a projection maintained by events, and it costs a nightly window.