设计面向客户的数据分析架构
根据租户规模与查询需求比较技术方案,设计数据流、统一指标口径及隔离与性能保障。
完整提示词
<context>
Analytics shown to customers inside a product has different needs from internal BI: every query must be scoped to one tenant, latency must feel interactive, and numbers must match what customers see elsewhere in the app and on their invoices. Running aggregate queries on the primary database is the usual starting point and the usual source of outages once data grows. The progression is predictable: a read replica buys time, then pre-aggregated rollup tables (per tenant, per day, per dimension) refreshed by jobs handle most dashboards, and only large event volumes with free-form slicing justify a columnar OLAP store. Embedded BI tools ship fast but bring per-viewer pricing, styling limits and their own tenant isolation model that must be configured correctly. Freshness requirements are usually looser than first stated; "updated every 15 minutes" is far cheaper than real time. Metric definitions belong in one place (a semantic layer, views or a shared module), otherwise the chart, the CSV export and the API disagree and support tickets follow.
</context>
<task>
<needs>
{{needs}}
</needs>
{{#data}}
<data>
{{data}}
</data>
{{/data}}
{{#constraints}}
Constraints: {{constraints}}
{{/constraints}}
1. Restate the requirements as numbers: tenants, rows or events per tenant, largest tenant, queries per minute at peak, freshness target and latency target. Ask for or label missing ones.
2. Compare serving options against those numbers, including the cheapest one that could work.
3. Recommend one, with the trigger that would move to the next option.
4. Design the data flow: source, change capture or batch extraction, transformation into rollups or the analytical store, refresh schedule and late-arriving data.
5. Define where metric definitions live and how charts, exports and any API share them.
6. Define tenant isolation: tenant key in every query enforced in one layer (row-level security, a mandatory filter in the query builder, or separate datasets), plus a test that fails if a query leaks across tenants.
7. Set performance guardrails: maximum date range per query, pagination and async generation for large exports, caching keyed by tenant and filters.
</task>
<constraints>
- Tenant isolation is not optional; never rely on the frontend to filter by tenant.
- Do not recommend an OLAP store or streaming pipeline when rollup tables meet the stated freshness and volume.
- Do not quote vendor prices or product limits as facts; say what to verify.
- Numbers shown to customers must have one definition; flag any metric that is ambiguous.
{{> output/uncertainty}}
</constraints>
<output_format>
## Recommendation
The serving approach and why, in at most five lines.
## Options compared
Table: option (query the primary or replica, pre-aggregated tables, OLAP store, embedded BI tool), fit, cost, effort, risk.
## Data flow
A Mermaid diagram from source tables to the dashboard, with freshness at each step.
## Metric definitions
Where definitions live so every chart and export agrees.
## Tenant isolation
How each query is scoped and tested.
## Performance and limits
Query budgets, caching, guardrails on date ranges and exports.
## Rollout
Numbered steps, starting with the slowest existing report.
</output_format>可替换内容
- {{needs}}
- {{#data}}
- {{data}}
- {{/data}}
- {{#constraints}}
- {{constraints}}
- {{/constraints}}
- {{> output/uncertainty}}
分类与标签
编程开发、多租户分析、数据架构、指标口径、查询优化