18  Modern Data Mart Consumption: APIs and Semantic Layers

From Direct Queries to Service Contracts

The previous chapter concluded with the construction of a physical Sales data mart comprising four core tables: dim_tiempo, dim_ubicacion, dim_departamento, and fact_ventas. While querying this dimensional schema directly via SQL or BI tools connected through ODBC/JDBC remains common, it is not the only—nor always the most effective—consumption pattern. Mobile applications, embedded customer dashboards, microservices, and autonomous software agents should not require direct database credentials or internal schema knowledge to retrieve sales metrics. This chapter formalizes exposing data marts through a service contract—an API layer—rather than direct database access.

18.1 From Direct Access to Service Contracts

In traditional consumption models, every client application connects directly to the data warehouse and executes custom SQL queries. While simple initially, this tightly couples client applications to internal physical schemas: renaming a column in dim_ubicacion breaks every downstream client. Furthermore, enforcing centralized access control, rate limiting, and schema versioning becomes difficult.

Definition. A data service contract decouples the physical storage representation \(DM_j\) from client-facing interfaces via an access function \(A\) that exposes only authorized operations and attributes: \[ A: \text{Request} \longrightarrow \text{Response}, \qquad A \text{ encapsulates the internal schema of } DM_j \]

Sales Data Mart

fact_ventas + conformed dimensions.

→

Semantic Layer + API

Defines metrics once; exposes them via REST / GraphQL.

→

Clients

Mobile App · Embedded Dashboard · Microservice · Analytics Notebook

Architectural flow. Clients consume a unified service contract without needing direct database access or internal schema awareness.

This separation between storage and consumption extends classic data independence principles to the API layer (Kimball and Ross 2013).

18.2 REST APIs over Data Marts

REST (Representational State Transfer) maps operational resources and analytical queries to stateless HTTP endpoints (Fielding 2000). For a data mart, a natural REST resource is an aggregated metric query (e.g., “sales by region and quarter”).

Definition. A REST endpoint over a data mart maps filter parameters and dimension groupings to a structured payload (typically JSON): \[ \text{GET } /\text{sales}?\text{region}=r\,\&\,\text{quarter}=t \;\longmapsto\; \big\{\text{department}: v \;\big|\; v = C(r,t,\cdot)\big\} \]

The Richardson Maturity Model categorizes REST API maturity from Level 0 (single RPC endpoint) to Level 3 (HATEOAS hypermedia controls) (Fowler 2010). Most enterprise analytical APIs operate at Level 2, leveraging HTTP verbs and status codes alongside query parameter filtering.

18.2.1 Endpoint Interface Design

A Level 2 REST endpoint interface for the Sales data mart:

# app.py — illustrative interface using FastAPI
from fastapi import FastAPI, Query

app = FastAPI(title="Sales Analytics API")

@app.get("/v1/sales")
def get_sales(
    region: str | None = Query(None),
    quarter: str | None = Query(None),
    department: str | None = Query(None),
):
    """Returns aggregated sales filtered by region, quarter, and/or department."""
    result = query_metric(
        "total_sales",
        group_by=["region", "quarter", "department"],
        filters={"region": region, "quarter": quarter, "department": department},
    )
    return {"data": result}

The key architectural pattern here is separation of concerns: the HTTP endpoint delegates metric calculation to query_metric, an underlying semantic layer function that abstracts SQL join execution (Ramírez, Sebastián 2024).

18.2.2 Reproducible Request/Response Simulation

We simulate this request/response cycle using Python and pandas:

import pandas as pd

dim_tiempo = pd.DataFrame({
    "time_key": [1, 2], "quarter": ["Q1", "Q2"], "year": [2025, 2025],
})
dim_ubicacion = pd.DataFrame({
    "location_key": [1, 2, 3, 4],
    "city": ["CDMX", "Guadalajara", "Monterrey", "Merida"],
    "region": ["Central", "West", "North", "Southeast"],
})
dim_departamento = pd.DataFrame({
    "dept_key": [1, 2, 3, 4],
    "department": ["Wood", "Plumbing", "Decoration", "Electrical"],
})
fact_ventas = pd.DataFrame([
    (1, 1, 1, 70), (1, 1, 2, 40), (1, 1, 3, 20), (1, 1, 4, 30),
    (2, 1, 1, 50), (2, 1, 2, 60), (2, 1, 3, 22), (2, 1, 4, 28),
    (1, 2, 1, 60), (1, 2, 2, 35), (1, 2, 3, 25), (1, 2, 4, 40),
    (2, 2, 1, 45), (2, 2, 2, 50), (2, 2, 3, 30), (2, 2, 4, 35),
    (1, 3, 1, 48), (1, 3, 2, 38), (1, 3, 3, 18), (1, 3, 4, 26),
    (2, 3, 1, 36), (2, 3, 2, 55), (2, 3, 3, 25), (2, 3, 4, 30),
    (1, 4, 1, 52), (1, 4, 2, 42), (1, 4, 3, 15), (1, 4, 4, 37),
    (2, 4, 1, 44), (2, 4, 2, 33), (2, 4, 3, 20), (2, 4, 4, 28),
], columns=["time_key", "location_key", "dept_key", "sales_amount"])

data_mart = (
    fact_ventas
    .merge(dim_tiempo, on="time_key")
    .merge(dim_ubicacion, on="location_key")
    .merge(dim_departamento, on="dept_key")
)
data_mart.head()

	time_key	location_key	dept_key	sales_amount	quarter	year	city	region	department
0	1	1	1	70	Q1	2025	CDMX	Central	Wood
1	1	1	2	40	Q1	2025	CDMX	Central	Plumbing
2	1	1	3	20	Q1	2025	CDMX	Central	Decoration
3	1	1	4	30	Q1	2025	CDMX	Central	Electrical
4	2	1	1	50	Q2	2025	CDMX	Central	Wood

Implementing the endpoint request handler:

def get_sales_endpoint(region=None, quarter=None, department=None):
    df = data_mart
    if region:
        df = df[df["region"] == region]
    if quarter:
        df = df[df["quarter"] == quarter]
    if department:
        df = df[df["department"] == department]

    aggregated = (
        df.groupby(["region", "quarter", "department"], as_index=False)["sales_amount"]
        .sum()
        .rename(columns={"sales_amount": "total_sales"})
    )
    return {"data": aggregated.to_dict(orient="records")}

request_params = {"region": "Central", "quarter": "Q2"}
response_payload = get_sales_endpoint(**request_params)
response_payload

{'data': [{'region': 'Central',
   'quarter': 'Q2',
   'department': 'Decoration',
   'total_sales': 22},
  {'region': 'Central',
   'quarter': 'Q2',
   'department': 'Electrical',
   'total_sales': 28},
  {'region': 'Central',
   'quarter': 'Q2',
   'department': 'Plumbing',
   'total_sales': 60},
  {'region': 'Central',
   'quarter': 'Q2',
   'department': 'Wood',
   'total_sales': 50}]}

18.3 GraphQL APIs over Data Marts

GraphQL empowers client applications to request specific fields and structure responses dynamically in a single request, resolving REST over-fetching (retrieving unneeded attributes) and under-fetching (requiring multiple round-trips) (GraphQL Foundation 2021).

Definition. A GraphQL query specifies a subset of fields \(F \subseteq \text{fields}(R)\) on resource \(R\); the server resolver returns strictly \(F\).

Illustrative GraphQL schema definition:

# schema.py — illustrative Strawberry GraphQL definition
import strawberry

@strawberry.type
class AggregatedSale:
    region: str
    quarter: str
    department: str
    total_sales: float

@strawberry.type
class Query:
    @strawberry.field
    def sales(self, region: str | None = None, quarter: str | None = None) -> list[AggregatedSale]:
        result = query_metric(
            "total_sales",
            group_by=["region", "quarter", "department"],
            filters={"region": region, "quarter": quarter},
        )
        return [AggregatedSale(**row) for row in result]

schema = strawberry.Schema(query=Query)

Client GraphQL query requesting only department and total_sales:

query {
  sales(region: "Central", quarter: "Q2") {
    department
    totalSales
  }
}

18.3.1 Reproducible Simulation of GraphQL Field Selection

def resolve_sales_query(requested_fields, region=None, quarter=None, department=None):
    df = data_mart
    if region:
        df = df[df["region"] == region]
    if quarter:
        df = df[df["quarter"] == quarter]
    if department:
        df = df[df["department"] == department]

    aggregated = (
        df.groupby(["region", "quarter", "department"], as_index=False)["sales_amount"]
        .sum()
        .rename(columns={"sales_amount": "total_sales"})
    )
    return aggregated[requested_fields].to_dict(orient="records")

resolve_sales_query(["department", "total_sales"], region="Central", quarter="Q2")

[{'department': 'Decoration', 'total_sales': 22},
 {'department': 'Electrical', 'total_sales': 28},
 {'department': 'Plumbing', 'total_sales': 60},
 {'department': 'Wood', 'total_sales': 50}]

18.4 Semantic Layers: Metrics as Code

Both REST and GraphQL patterns rely on an underlying semantic layer. A semantic layer defines business metrics (calculation rules, aggregations, filters) once as code, serving as a single source of truth across dashboards, REST endpoints, GraphQL queries, and notebooks (dbt Labs 2024; Cube Dev, Inc. 2024).

Definition. A semantic metric is a registered function \(M: (\text{groupings}, \text{filters}) \to \mathbb{R}\) executed over the underlying data mart: \[ M_{\text{total\_sales}}(G, F) = \sum_{i \,:\, i \models F} \text{sales\_amount}_i \quad \text{grouped by } G \]

Decoupling metric definitions from visualization tools is commonly referred to as headless BI.

18.4.1 Reproducible Semantic Metric Registry

METRICS_REGISTRY = {
    "total_sales": {
        "column": "sales_amount",
        "aggregation": "sum",
    },
    "average_ticket": {
        "column": "sales_amount",
        "aggregation": "mean",
    },
}

def query_metric(metric_name, group_by, filters=None):
    if metric_name not in METRICS_REGISTRY:
        raise ValueError(f"Unregistered metric: {metric_name}")

    definition = METRICS_REGISTRY[metric_name]
    df = data_mart

    for field, value in (filters or {}).items():
        if value is not None:
            df = df[df[field] == value]

    aggregated = (
        df.groupby(group_by, as_index=False)[definition["column"]]
        .agg(definition["aggregation"])
        .rename(columns={definition["column"]: metric_name})
    )
    return aggregated.to_dict(orient="records")

query_metric(
    "total_sales",
    group_by=["region", "quarter"],
    filters={"quarter": "Q2"},
)

[{'region': 'Central', 'quarter': 'Q2', 'total_sales': 160},
 {'region': 'North', 'quarter': 'Q2', 'total_sales': 146},
 {'region': 'Southeast', 'quarter': 'Q2', 'total_sales': 125},
 {'region': 'West', 'quarter': 'Q2', 'total_sales': 160}]

query_metric(
    "average_ticket",
    group_by=["department"],
    filters={"region": "Central"},
)

[{'department': 'Decoration', 'average_ticket': 21.0},
 {'department': 'Electrical', 'average_ticket': 29.0},
 {'department': 'Plumbing', 'average_ticket': 50.0},
 {'department': 'Wood', 'average_ticket': 60.0}]

18.5 Comparing Consumption Contracts

Central Region Sales in Q2 by Department — Three Contracts

Server dictates response shape: includes all resource fields by default.

Request

GET /v1/sales?region=Central&quarter=Q2

Response

{
  "data": [
    {"region": "Central", "quarter": "Q2", "department": "Televisions", "total_sales": 45},
    {"region": "Central", "quarter": "Q2", "department": "Computers",   "total_sales": 50},
    {"region": "Central", "quarter": "Q2", "department": "Decoration",  "total_sales": 30},
    {"region": "Central", "quarter": "Q2", "department": "Electrical",  "total_sales": 35}
  ]
}

Client dictates response shape: requests only department and total_sales.

Request

query {
  sales(region: "Central", quarter: "Q2") {
    department
    totalSales
  }
}

Response

{
  "data": {
    "sales": [
      {"department": "Televisions", "totalSales": 45},
      {"department": "Computers",   "totalSales": 50},
      {"department": "Decoration",  "totalSales": 30},
      {"department": "Electrical",  "totalSales": 35}
    ]
  }
}

Both API protocols delegate metric evaluation to the centralized semantic layer.

Invocation

query_metric(
    "total_sales",
    group_by=["department"],
    filters={"region": "Central", "quarter": "Q2"},
)

Result

[
  {"department": "Televisions", "total_sales": 45},
  {"department": "Computers",   "total_sales": 50},
  {"department": "Decoration",  "total_sales": 30},
  {"department": "Electrical",  "total_sales": 35}
]

All three contracts expose identical metric calculations (45, 50, 30, 35); only protocol interaction format varies.

18.6 Security and Governance at the API Layer

Centralizing data mart access through an API layer enables security controls that are difficult to enforce with direct database queries:

• Authentication & Authorization: Verifies caller identities and scope permissions (JWT, API keys).
• Row-Level Security (RLS): Automatically restricts data visibility based on user roles (e.g., regional managers see only their assigned regions).
• Rate Limiting & Throttling: Protects analytical databases from unoptimized, expensive queries.
• Contract Versioning: Explicit URL paths (e.g., /v1/) support schema evolution without breaking legacy client integrations.

18.7 Conclusions

Exposing data marts via APIs and semantic layers transforms database tables into governed data products (Dehghani 2022). Whether providing interactive OLAP cube exploration or programmatic REST/GraphQL service endpoints, defining conformed dimensions and metrics once guarantees enterprise-wide consistency across all analytics consumers.

18.8 References

Cube Dev, Inc. 2024. Cube Documentation. Cube Dev. https://cube.dev/docs/.

dbt Labs. 2024. Dbt Semantic Layer Documentation. Dbt Labs. https://docs.getdbt.com/docs/use-dbt-semantic-layer/dbt-sl.

Dehghani, Zhamak. 2022. Data Mesh: Delivering Data-Driven Value at Scale. O’Reilly Media.

Fielding, Roy Thomas. 2000. “Architectural Styles and the Design of Network-Based Software Architectures.” PhD thesis, University of California, Irvine.

Fowler, Martin. 2010. Richardson Maturity Model. Martinfowler.com. https://martinfowler.com/articles/richardsonMaturityModel.html.

GraphQL Foundation. 2021. GraphQL Specification. GraphQL Foundation. https://spec.graphql.org/.

Kimball, Ralph, and Margy Ross. 2013. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. 3rd ed. Wiley.

Ramírez, Sebastián. 2024. FastAPI Documentation. FastAPI. https://fastapi.tiangolo.com/.