Decoupling Ops Troubleshooting: Building a DataOps Warehouse with ETL

Introduction

The industry talks about AI and AIOps, but the real foundation is data. DataOps was defined to emphasize that AI needs high‑quality data, so building a data warehouse and ETL pipeline is the first step.

Data Perspective vs. Ops Troubleshooting

Traditional SRE troubleshooting involves two steps: data collection (running commands) and logical judgment based on command results. By pre‑collecting relevant data into a warehouse, the process becomes a simple SQL query:

Data collection → Data warehouse

Issue investigation via SQL

Operational Data Types

Two basic data types are needed:

Metadata : relatively static information describing objects, such as host name, IP, machine model, cluster name, IDC, software version.

Runtime metrics : dynamic, time‑related data like performance metrics, logs, events.

Metadata requires high accuracy but low volume; runtime data tolerates lower accuracy but demands real‑time processing and large‑scale storage.

Unified Data Layer Specification

In data‑warehouse terminology, metadata corresponds to DIM (dimension) tables and runtime data to ODS (operational data store). Building the warehouse follows a layered approach:

ODS layer : raw operational data.

DIM layer : static descriptive data.

DWD layer : wide tables created by joining ODS and DIM, containing all dimensions needed for downstream analysis.

DWS layer : aggregated or summarized data for reporting.

Example: OOM logs from processes are joined with machine information to produce a wide table that can answer questions about kernel version, CPU, memory, etc., without repeated joins.

Data Timeliness Trade‑offs

Operational scenarios often demand low latency, but real‑time processing increases resource consumption and ETL complexity. Not all data needs to be real‑time; choose the appropriate freshness based on use case:

Decision‑making scenarios: prefer real‑time solutions.

Reporting scenarios: batch (T+1) is sufficient.

Common real‑time and batch products include Alibaba Cloud StreamCompute and MaxCompute, as well as open‑source Spark, Storm, Hadoop & Hive.

Conclusion

Data warehouses provide mature techniques that, when combined with DataOps, enable SRE teams to automate and intelligent‑ly drive operations. By aligning warehouse theory with DataOps practice, we can continuously improve the intelligence level of operational troubleshooting.