A data warehouse is a single copy of the data in every production database, kept for the business teams to query.
The team scales from 15 engineers to 70, across 12 teams. The business teams report that engineers take a lot of time to run their queries, and that one business query needs data from the profile, orders and delivery teams.
The core problem is that the business teams are coupled with the tech team for data insights.
The fix:
- The data in the 12 Postgres databases is collected into a single data source.
- Business teams fire read queries on the copy, so a heavy analytics query does not overload a production database.
- A server exposes APIs for the dashboards and a SQL query interface for custom queries.
Amazon Kinesis streams the data and Amazon Athena queries it. The product management team asks for open source: Apache Kafka for streaming, the Hadoop file system or CSV files on Amazon S3 for the store, and Apache Spark for queries. The microservices publish events to Kafka.