Module 12: Data engineering and the API

View study sheet (PDF) View SQL cheat sheet (PDF)

Teaching this module? A facilitator guide adds a preparation checklist, timing cues, discussion guidance, slides, and a printable PDF.

This session moves students from querying data in a browser to understanding how a production data API is structured and how a small pipeline is built on top of it. Students start in the no-code sandbox, using its discovery views to see what a table catalog and a schema endpoint actually expose, then move to the documented shape of the production API itself: a single query endpoint that every table shares, a small set of filter operators, and pagination on an offset rather than a page number. The session closes with an optional, hands-on extension in which students use a class API key and a reusable Python client to pull one brand's transaction rows, store them, and compute an aggregate themselves, since the API is deliberately built to return rows rather than server-side aggregates. The throughline is a data engineering lesson as much as a finance one: understanding the contract a data provider offers is the first step in building anything reliable on top of it.

Target course(s) and level

Data engineering, applied data science, or a technical elective within a FinTech or business analytics program. Suitable for undergraduates or graduate students who have taken an introductory programming or data science course. The core session assumes no prior API experience; the optional extension assumes basic Python familiarity.

Learning objectives

By the end of this session, students will be able to:

  1. Use the sandbox's discovery views to identify which tables exist and which fields a given table exposes, and explain why discovery should precede any query design.
  2. Describe the shape of the production API's query endpoint, including the request method, the required header, and the four parts of the request body.
  3. Explain why the API paginates on an offset rather than a page number, and describe how a client would retrieve a full result set larger than one page.
  4. Distinguish a query that filters and sorts rows from a query that aggregates, and explain why this API returns rows and leaves aggregation to the client.
  5. (Extension) Use the reusable Python client to authenticate a request, retrieve paginated results for a single brand, and compute a simple aggregate, such as a count or an average, from the retrieved rows.
  6. Identify the operational limits of the API relevant to a data engineering context, including the page size ceiling and the fact that API keys are issued manually by the team rather than self-served.

Prerequisites

An introductory programming or data science course. No prior exposure to REST APIs is required for the core session. The optional code extension assumes basic Python familiarity, including reading a JSON-like data structure and writing a simple loop.

Materials and access needed

  • Sandbox access at sandbox.altfndata.com, self-registered with a work or school email, auto-approved.
  • Projector or screen share for the instructor demo.
  • Documentation at docs.altfndata.com, including the quickstart, the endpoint reference, and the tutorials notebook.
  • For the optional extension only: an instructor class API key requested from info@altfndata.com, and the downloadable Python client (altfndata_client.py) or tutorials notebook (altfndata_tutorials.ipynb) at docs.altfndata.com.

Session outline (90 minutes)

  • 0 to 10 min: Introduce the session's frame, understanding a data provider's contract before building on it, and preview the two discovery endpoints and the single query endpoint every table shares.
  • 10 to 25 min: Sandbox orientation and discovery. Students use the sandbox's table list and schema tab to answer the question, what tables exist and what fields does each one expose.
  • 25 to 40 min: Guided walkthrough of the production API's query endpoint shape, the request body's four parts, and the response envelope, using the documentation and worked examples rather than live code.
  • 40 to 55 min: Guided walkthrough of pagination on offset, using a worked example that shows what a client must do to retrieve more rows than fit on one page.
  • 55 to 70 min: Optional code extension, instructor demo using the class API key and the Python client to pull one brand's rows and compute a client-side aggregate.
  • 70 to 85 min: Small-group exercise, students design (on paper or in the sandbox) the query body they would send to answer a specific question about a chosen brand, and if doing the code extension, run it.
  • 85 to 90 min: Wrap-up and homework assignment.

In-class demo (sandbox-first, no code)

  1. Open sandbox.altfndata.com, sign in, and open the table list view. Ask students to identify how many production tables exist and to name three.
  2. Open the schema tab for one table, for example the handbags data, and have students list every documented field shown, noting that a query should only ever reference fields that appear here.
  3. Explain that the production API mirrors this same discovery pattern outside the sandbox, through GET /v1/tables, which lists every table, and GET /v1/tables/{name}/schema, which lists a given table's fields, and that a well-built client always calls these before constructing a query.
  4. Walk through the shape of the query endpoint itself, POST /v1/tables/{name}/query, sent with an X-API-Key header, and a JSON body containing fields, filters, sort, limit, and offset. Show the worked example request body below on the projector.
  5. Walk through the response envelope, table, result_count, and data, and point out that the API returns individual rows in data, not a pre-computed total or average.
  6. Introduce pagination on offset with a worked example, explain that a single request returns at most around 1,000 rows, and that a client retrieving a brand with 3,400 matching rows must send four requests, each advancing offset by the number of rows already retrieved, stopping once a response returns fewer rows than the limit requested.
  7. If running the optional extension, open the tutorials notebook, authenticate with the class API key through the Python client, and live-run the small ETL below, pulling one brand's rows, storing them in a local list or file, and computing an aggregate, such as the sold-lot count or the average realized price, from the retrieved rows.
  8. Close by asking students to compare what discovery, querying, and pagination looked like in the sandbox against the same three ideas expressed as raw endpoints and a JSON body.

Datasets and queries used

Dataset: any production category table (documented fields: designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker). Examples below use all_watches_data.

Discovery, list all tables:

GET /v1/tables
Header: X-API-Key: <key>

Discovery, list a single table's fields:

GET /v1/tables/all_watches_data/schema
Header: X-API-Key: <key>

Query, sold lots for a single brand, first page:

POST /v1/tables/all_watches_data/query
Header: X-API-Key: <key>

{
  "fields": ["designer", "item_title", "sale_date", "usd_price_decimal"],
  "filters": [
    {"field": "designer", "op": "like", "value": "%Omega%"},
    {"field": "status", "op": "eq", "value": "sold"}
  ],
  "sort": [{"field": "sale_date", "direction": "desc"}],
  "limit": 1000,
  "offset": 0
}

Pagination, same query, second page (offset advances by the limit used on the prior page):

{
  "fields": ["designer", "item_title", "sale_date", "usd_price_decimal"],
  "filters": [
    {"field": "designer", "op": "like", "value": "%Omega%"},
    {"field": "status", "op": "eq", "value": "sold"}
  ],
  "sort": [{"field": "sale_date", "direction": "desc"}],
  "limit": 1000,
  "offset": 1000
}

A client repeats this pattern, incrementing offset by the number of rows returned each time, until a response's data array contains fewer rows than the limit requested, which signals the last page.

Optional small ETL, using the reusable Python client (altfndata_client.py) to pull one brand's rows across all pages and compute a client-side aggregate, since the API returns rows rather than a server-side average or count:

from altfndata_client import AltFnDataClient

client = AltFnDataClient(api_key="<class key>")

rows = []
offset = 0
limit = 1000
while True:
    page = client.query(
        table="all_watches_data",
        fields=["designer", "item_title", "sale_date", "usd_price_decimal"],
        filters=[
            {"field": "designer", "op": "like", "value": "%Omega%"},
            {"field": "status", "op": "eq", "value": "sold"},
        ],
        sort=[{"field": "sale_date", "direction": "desc"}],
        limit=limit,
        offset=offset,
    )
    rows.extend(page["data"])
    if len(page["data"]) < limit:
        break
    offset += limit

# Client-side aggregate, since the API does not compute this server-side.
prices = [r["usd_price_decimal"] for r in rows if r.get("usd_price_decimal")]
sold_lots = len(prices)
average_price = sum(prices) / sold_lots if sold_lots else None

Discussion questions

  1. Why should a client always call the discovery endpoints before constructing a query, rather than guessing at table and field names?
  2. What would go wrong for a client that assumed a single request could return an unlimited number of rows, and how does the offset-based pagination pattern prevent that failure mode?
  3. Why might a data provider choose to return raw rows rather than server-side aggregates, and what tradeoff does that design place on the client?
  4. The API exposes 11 filter operators, including eq, like, in, and is_null. What kinds of questions can be answered with these operators alone, and what kinds would require something more expressive?
  5. Why does the API require a manually issued key rather than self-service key generation, and what does that imply about how a course would provision access for a class?
  6. If a client's pagination loop has a bug that never advances the offset, what would happen to the requests it sends, and how would you detect that bug from the response alone?
  7. What discovery step would you add to a client if a table's schema could change over time, and why does checking the schema before every query in production make a pipeline more resilient?

Homework assignment

Each student writes a one-page data engineering brief describing a small ETL they would build on top of the API to answer a specific business question of their choosing, using only documented fields and any of the all_*_data tables. The brief must specify the exact discovery calls the client would make first, the exact query body or bodies it would send, how it would handle pagination if the result exceeds one page, and what aggregate or transformation it would compute client-side from the retrieved rows. Students who complete the optional code extension may submit their working script in place of the written query bodies, along with a short paragraph explaining the pagination logic. The brief must include an explicit limitations section addressing the manual key-issuance process and the fact that this dataset's newest quarters are still being ingested. Grading criteria: correct discovery-then-query design (25 percent), correct and complete description or implementation of pagination (30 percent), soundness of the client-side aggregate design (25 percent), and clarity of the brief (20 percent).

Going deeper

Key terms

  • Discovery endpoint: an API endpoint whose purpose is to describe what is available, here GET /v1/tables and GET /v1/tables/{name}/schema, rather than to return transaction data.
  • Request body: the JSON object sent with a POST request, here containing fields, filters, sort, limit, and offset.
  • Response envelope: the wrapping structure of an API response, here table, result_count, and data, that surrounds the actual rows returned.
  • Pagination: the practice of splitting a large result set across multiple requests, here controlled by limit and offset rather than a page number.
  • Offset: the number of rows to skip before starting to return results, used here to advance through a paginated result set.
  • Filter operator: a documented comparison a query can apply to a field, such as eq, like, in, gt, gte, lt, lte, or is_null.
  • Client-side aggregation: computing a summary statistic, such as a count or an average, from raw rows already retrieved, rather than asking the server to compute it.
  • API key: a credential passed in the X-API-Key header that authenticates a request; in this course, issued manually to the instructor as a shared class key.

Common pitfalls

  • Skipping the schema discovery step and guessing at a field name, which produces either an error or, worse, a query that silently returns nothing.
  • Writing a pagination loop that never terminates because it checks the wrong condition instead of comparing the returned row count to the limit requested.
  • Assuming the API can return an aggregate directly, and then being surprised that an average or count must be computed after retrieving the underlying rows.
  • Sharing an individual API key with an entire class instead of using the single, instructor-held class key.

Additional queries to explore

  • A query using the in operator to retrieve rows for several brands in a single request, useful for building a small comparison pipeline without multiple round trips.
  • A query using is_null on sale_estimates_high_usd_price to identify rows that would need to be excluded before computing any ratio involving the estimate.

Extension activities

  • Have students extend the small ETL to write retrieved rows to a local file, then re-run the aggregate computation from the saved file instead of a fresh API call, illustrating the difference between a data pull and a data pipeline.
  • Ask students to sketch, in plain language, how they would schedule the small ETL to run on a recurring basis, and what they would need to check on each run to avoid silently missing new data.

Connections to other modules

  • Module 2, Data science and SQL, for the query concepts this session translates from the sandbox's SQL editor into the API's request body.
  • Module 6, FinTech and data products, for a broader look at how a data product like this one is packaged and sold.
  • Module 13, Data ethics, quality, and coverage bias, for the responsibilities that come with pulling and storing this data in a student-built pipeline.