Mastering the ADS Layer: Design Principles, Modeling, and Real‑Time Data Services

Core Positioning of the ADS Layer

The ADS (Application Data Service) layer is the final output layer of a data‑warehouse, the closest to business applications, and directly serves reports, API interfaces, recommendation systems, and other concrete scenarios with customized data services.

Design Goals

Efficient query : Optimize storage for high‑frequency query patterns using pre‑aggregation and columnar storage.

Business adaptability : Flexible response to metric additions or dimension adjustments.

Data freshness : Support T+1 offline updates, hourly micro‑batch processing, and real‑time streaming updates.

Flexibility : Allow direct BI queries on detailed or aggregated data.

Key Design Points

1. Data Modeling Strategy

Scenario‑driven modeling creates purpose‑built tables:

Reporting wide table (e.g., ads_report_daily) – stores all dimensions for business reports.

User profile tag table (e.g., ads_user_profile) – enables fast tag retrieval via HBase or Elasticsearch.

Real‑time aggregation table (e.g., ads_realtime_trade) – built on Kafka + Flink + ClickHouse for second‑level latency.

High‑concurrency API table (e.g., ads_api_orders) – stored in MySQL with Redis caching.

2. Aggregation Dimension Optimization

Separate tables by time granularity (hourly vs. daily), hierarchical geographic dimensions (province → city → district), and pre‑computed business dimension combinations such as channel + user level.

3. Data Service Enablement

Expose data through APIs and accelerate OLAP queries:

# Example: FastAPI endpoint for order data
@app.get("/orders")
def get_orders(start_date: str, end_date: str, channel: str = None):
    query = """
    SELECT dt, channel, SUM(gmv) AS gmv
    FROM ads_report_daily
    WHERE dt BETWEEN '{start_date}' AND '{end_date}'
    {channel_filter}
    GROUP BY dt, channel
    """.format(
        start_date=start_date,
        end_date=end_date,
        channel_filter=f"AND channel='{channel}'" if channel else ""
    )
    return execute_query(query)

Use ClickHouse/Doris materialized views for complex queries and Kylin pre‑computed cubes (e.g., CUBE(dt, channel, product)).

Typical Challenges and Solutions

Conflicting multi‑business‑line requirements : Split tables per line (e.g., ads_finance_report, ads_marketing_report) to avoid tight coupling.

High‑concurrency query pressure : Cache hot query results in Redis and apply read‑write separation with replica databases.

Realtime‑offline consistency : Adopt a Lambda architecture with parallel real‑time and batch pipelines, periodic merge, and binlog‑based incremental compensation.

Sensitive data protection : Implement field‑level masking and dynamic data masking based on role permissions.

Best Practices

Demand‑driven development : Clarify metric definitions with product and operations teams (e.g., whether GMV includes refunds).

Data version management : Keep snapshot tables (e.g., ads_report_daily_v2_20231001) for historical comparison.

Cold‑hot data separation : Store hot data on SSD or in‑memory stores like Redis; archive cold data to low‑cost storage such as HDFS/OSS.

Monitoring & alerting : Track data update latency, query P90/P99 response times, and API error rates.

Performance first : Use pre‑aggregation, caching, and columnar storage to optimize query efficiency.

Scalable architecture : Design for rapid addition of new business requirements.

Security and control : Enforce data permissions and masking mechanisms.