Reporting Systems

Introduction

Reporting workloads are read-heavy, scan-large, and run by humans on demand. They belong on replicas or warehouses, not on your OLTP primary.

Understanding the topic

Core concepts to understand:

• Run reports on a replica or warehouse.
• Pre-aggregate hot reports as materialized views.
• Schedule heavy queries off-peak.
• Use BI tools (Metabase, Looker, Superset) on top.
• Govern access with views + roles.

Syntax reference

Visual workflow / architecture:

OLTP primary ──▶ replica ──▶ warehouse
                              ▲
                            BI tools / dashboards

Real-world use

Most companies separate operational and analytical workloads. Stripe runs analytics on Snowflake/BigQuery; OLTP stays on Postgres/MySQL.

Best practices

• Never run heavy analytics on the primary.
• Pre-aggregate hot reports.
• Govern via views + roles.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why split OLTP and OLAP?
• Q2. Materialized view refresh strategies.
• Q3. Replica lag — when problematic.
• Q4. BI tooling you've used.
• Q5. Access governance via views.