Design how business systems fit together: map the landscape, draw data flows, choose build or buy, specify integrations and data migrations, and deliver with UAT and change management.

Solution Design & Integration

Open jsonplaceholder.typicode.com and read the list of resources and routes it supports.

Run `curl -i https://jsonplaceholder.typicode.com/users/1`. Note the status code, the `Content-Type` header and three fields from the body.

Run `curl -i "https://jsonplaceholder.typicode.com/posts?userId=1"`. Count how many records come back and note how the query parameter filtered them.

Run a call for a user that does not exist (`/users/9999`) and note the status code and body.

Send a POST to `/posts` with a JSON body (see the lesson) and note the status code and the `id` returned.

Open the Swagger Petstore demo specification (petstore3.swagger.io) and find one endpoint. Write down its method, path, required parameters, success response fields and documented error codes.

Write a three-sentence integration requirement for that endpoint, covering success, not found and time-out behaviour, as in the lesson.

## Why it matters

Most integrations today are built on APIs (application programming interfaces). An analyst who can read an API specification can answer questions that would otherwise wait days for a developer: "Does the vendor's API give us the loyalty balance?", "Can we filter orders by date?", "What happens if the customer does not exist?" You do not need to write the integration code, but you do need to specify it precisely and test that it behaves as the requirements say.

## The core idea: requests and responses

A web API (usually REST over HTTPS) works like a structured conversation. The client sends a **request**:

- **Method**: what to do. `GET` reads, `POST` creates, `PUT` or `PATCH` updates, `DELETE` removes.
- **URL**: which resource, for example `https://api.example.com/v1/customers/1042`. Parts after `?` are **query parameters**, such as `?status=active&limit=50`.
- **Headers**: metadata, such as `Authorization: Bearer <token>` and `Content-Type: application/json`.
- **Body**: the data sent with `POST`, `PUT` or `PATCH`, usually JSON.

The server returns a **response** with a **status code** and usually a JSON body:

| Code | Meaning |
|---|---|
| 200 OK, 201 Created | Success (201 after creating something) |
| 400 Bad Request | The request was malformed or failed validation |
| 401 Unauthorized / 403 Forbidden | Not authenticated / not allowed |
| 404 Not Found | The resource does not exist |
| 409 Conflict | It clashes with the current state, such as a duplicate |
| 429 Too Many Requests | Rate limit hit; slow down and retry later |
| 500-599 | Server-side error; often worth retrying |

## Reading a specification

Most modern APIs publish an **OpenAPI** (formerly Swagger) specification, often rendered as interactive documentation. A fragment:

```yaml
paths:
  /customers/{id}/points:
    get:
      summary: Get a customer's loyalty balance
      parameters:
        - name: id
          in: path
          required: true
          schema: { type: string }
      responses:
        "200":
          description: Balance found
          content:
            application/json:
              schema:
                type: object
                properties:
                  customerId: { type: string }
                  balance:    { type: integer }
                  asOf:       { type: string, format: date-time }
        "404":
          description: Customer not found
```

From this an analyst can write: "POS calls `GET /customers/{id}/points` at checkout. On 200, show `balance`. On 404, show 'No loyalty account' and offer sign-up. On any 5xx or no reply in 2 seconds, continue the sale without points and log the failure." That sentence is an integration requirement a developer can build and a tester can check.

Things to look for in any spec: authentication method, rate limits, pagination (how to get page 2 of results), date and time formats and time zones, required versus optional fields, error formats, and versioning (what happens when v2 arrives).

## Worked example: testing calls yourself

You can test a public practice API with `curl` in a terminal, or with a graphical client such as Postman, Insomnia or Bruno. JSONPlaceholder (`jsonplaceholder.typicode.com`) is a free fake API made for practice:

```bash
curl -i https://jsonplaceholder.typicode.com/users/1
curl -i "https://jsonplaceholder.typicode.com/posts?userId=1"
curl -i https://jsonplaceholder.typicode.com/users/9999
curl -i -X POST https://jsonplaceholder.typicode.com/posts \
  -H "Content-Type: application/json" \
  -d '{"title": "Test", "body": "Hello", "userId": 1}'
```

The `-i` flag shows the status line and headers. The first call returns 200 with one user; the second filters with a query parameter; the third returns 404; the fourth returns 201 with the new record (the fake API does not really save it). Writing down what you sent and what came back is the start of an interface test plan.

## Security notes

Never paste real production credentials or customer data into an online tool, a shared Postman workspace or a chat. Use test environments ("sandbox" accounts) and test data. API keys belong in the tool's secret storage or environment variables, not in saved requests you share.

## Common mistakes

- Specifying only the happy path; every interface needs rules for 4xx, 5xx and time-outs.
- Ignoring pagination and assuming the first page is all the data.
- Time zones: `2026-03-16T23:30:00Z` is already the 17th in many places.
- Assuming field meanings from names; confirm with the spec and sample data.

## Quick recap

- A request has a method, URL, headers and optional body; the response has a status code and usually JSON.
- OpenAPI specs tell you endpoints, parameters, fields and errors; turn them into precise integration requirements.
- Test with curl or Postman against sandboxes, and never with real credentials or customer data in shared tools.

## Try it

In the Do tab you read a real spec and test calls against a free practice API.


Read an API specification, understand requests and responses, and test a call yourself.

APIs for analysts

Introduction to APIs for Business Analysts

Postman Beginner Tutorial 2026 | Install Postman, Workspace, Collection & First API Request + QUIZ

## Why it matters

Every integration and every migration moves data from a **source** structure to a **target** structure, and the two never match exactly. The old system stores "Rivera, Ana" in one field; the new one wants first and last name. The old system uses status code `A`; the new one wants `active`. A **source-to-target mapping** (STTM) is the document that says, field by field, how one becomes the other. Developers build from it, testers test against it, and auditors read it later. Getting it wrong means wrong data in the new system on day one.

## The core idea: one row per target field

A mapping is usually a spreadsheet with one row per **target** field:

| Target field | Source field(s) | Transformation rule | Default / null handling | Notes |
|---|---|---|---|---|
| `external_id` | `cust_no` | prefix with `HO-` | required | used to trace back to legacy |
| `first_name` | `full_name` | text after the comma, trimmed | blank if no comma | |
| `last_name` | `full_name` | text before the comma, trimmed | whole value if no comma | |
| `state_code` | `state_name` | look up in state table, case-insensitive | `UNKNOWN`, flagged for review | |
| `status` | `status_code` | A->active, I->inactive | anything else -> review | `X` seen in data profiling |
| `joined_on` | `joined` | MM/DD/YYYY -> YYYY-MM-DD | required | |

Mapping types you will meet:

- **Direct (1:1):** copy as is, perhaps renaming.
- **Transformation:** reformat, convert units, split or concatenate.
- **Lookup / code mapping:** translate codes via a reference table.
- **Derived:** calculate from several fields.
- **Default / constant:** fill a target field the source does not have.
- **Not migrated:** record source fields you deliberately drop, and why.

## Profile the data first

Never write rules from field names alone. **Data profiling** means querying the source to see what is really there:

```sql
SELECT status_code, COUNT(*) FROM legacy_customers GROUP BY status_code;
SELECT DISTINCT state_name FROM legacy_customers ORDER BY state_name;
SELECT COUNT(*) FROM legacy_customers WHERE full_name NOT LIKE '%,%';
```

At Harbor Outfitters this revealed a status code `X` that nobody remembered (it turned out to mean "closed by fraud team") and state names typed in lower case. Both became explicit rules in the mapping rather than surprises in testing.

## Worked example: the mapping as a query

Writing the mapping as a SQL `SELECT` is a quick way to test it, because you can run it against a copy of the source and look at the output. A few rules from the table:

```sql
SELECT
  'HO-' || cust_no AS external_id,
  CASE status_code
    WHEN 'A' THEN 'active'
    WHEN 'I' THEN 'inactive'
    ELSE 'review'
  END AS status
FROM legacy_customers;
```

Text functions do the splitting and reformatting. In SQLite, `INSTR(full_name, ',')` gives the position of the comma, `SUBSTR(text, start, length)` cuts out part of a string, `TRIM` removes spaces, and `||` concatenates. Lookups use a `LEFT JOIN` so that unmatched source rows are kept, with `COALESCE(code, 'UNKNOWN')` supplying the default. An inner join would silently drop customers whose state is missing from the lookup table, which is one of the most common mapping bugs.

## Rules for good mappings

- Write the rule so two people would implement it identically. "Clean up the name" is not a rule.
- Say what happens for nulls, blanks, unknown codes and values too long for the target field.
- Keep a **traceability key** (here `external_id`) so every target record can be traced to its source.
- Version the mapping and get the data owner to sign it off; it is a requirement, not a developer note.

## Common mistakes

- Inner joins on lookups, losing records.
- Assuming one date format when the source mixes several.
- Ignoring character sets and accents (Ana vs Aña) and field lengths.
- No rule for duplicates: two legacy rows for the same person need a de-duplication rule agreed with the business.

## Quick recap

- An STTM lists each target field with its source, transformation, default and null handling.
- Profile real source data before writing rules.
- Test the mapping as a query; use LEFT JOIN plus defaults for lookups and keep a traceability key.

## Try it

In the Do tab you implement the Harbor Outfitters mapping as one SQLite query.


Write a source-to-target mapping with transformation rules and test it in SQL.

Data mapping

Creating a Data Map - A Vital Step to Data Migration & System Integration

Data Mapping Explained Practically for Business Analysts

## Why it matters

Data migration is the part of a system replacement most likely to delay go-live. The new system can be perfectly configured, but if customers' loyalty points are wrong on Monday morning, store staff will lose trust in it within an hour. A good migration is planned like a small project of its own, rehearsed several times, and proven correct with numbers rather than with "it looked fine".

## The core idea: extract, transform, load, reconcile

1. **Extract** data from the source systems into a staging area, ideally a read-only snapshot.
2. **Transform** it with the rules from the source-to-target mapping: reformat, look up codes, de-duplicate, apply defaults.
3. **Load** it into the target, in dependency order (customers before orders, orders before order lines), using the target's import tools or APIs.
4. **Reconcile:** prove the target matches the source after transformation, and explain every difference.

## The migration plan

A migration plan answers:

- **Scope:** which entities and how much history ("all customers; orders from the last 3 years; older orders archived as read-only files").
- **Data quality and cleansing:** what is fixed in the source before migration, what is fixed by rules, and who approves.
- **Approach:** **big bang** (everything moves in one cutover window) or **phased / trickle** (by region, by entity, or with both systems running in parallel for a while). Big bang is simpler; phased reduces risk but needs temporary interfaces.
- **Rehearsals:** at least two or three full trial migrations into a test environment, timed, with the same scripts as the real one.
- **Reconciliation checks** and sign-off criteria.
- **Fallback:** what happens if the migration fails during cutover, and the latest time you can decide to roll back.

## Reconciliation: proving it worked

Reconciliation works at three levels, from cheapest to most thorough:

```text
1. Counts         source rows in scope vs target rows, per entity
2. Totals         sum of money and points, per entity and per period
3. Record level   every key matched, and key fields compared
```

Counts and totals are quick and catch big failures. Record-level checks find the specific records to fix. A reconciliation report looks like this:

```text
Entity      Source  Target  Diff  Explained
Customers   180,412 180,377   35  35 duplicates merged (list attached)
Orders      612,950 612,948    2  2 orders failed validation (ticket MIG-41)
Points sum  9.81M   9.81M      0  -
```

Every difference must be **explained** in writing, not just small. "Two orders missing" is a defect until someone proves why.

## Worked example: finding exceptions in SQL

With source and target tables side by side (or extracted to a staging database), set-based queries find every exception:

```sql
-- In source but not in target
SELECT s.order_id
FROM source_orders AS s
LEFT JOIN target_orders AS t ON t.order_id = s.order_id
WHERE t.order_id IS NULL;
```

The same pattern, with the tables swapped, finds records that appeared in the target without a source (for example test data left behind). Comparing key fields where both sides exist finds transformation errors, such as an amount that lost a digit. Storing money as whole cents avoids rounding noise in these comparisons.

## Cutover timing

Measure each rehearsal. If extract, transform, load and reconciliation take 14 hours, a weekend window works; if they take 50 hours, you need a different approach (phased, or migrating history in advance and only the latest changes at cutover, often called a **delta** load).

## Common mistakes

- Treating migration as a one-off script written in the last month.
- No rehearsals, so the first real run is the cutover.
- Reconciling counts only; a wrong amount passes a count check.
- Migrating everything "just in case", including decades of history nobody uses.
- Unclear ownership: the business, not IT, must sign off that the data is right.

## Quick recap

- Extract, transform with the mapping, load in dependency order, then reconcile.
- Plan scope, cleansing, approach (big bang or phased), rehearsals and fallback.
- Reconcile counts, totals and records, and explain every difference.

## Try it

In the Do tab you write a reconciliation query that lists every exception from a trial migration.


Plan a data migration with trial runs, reconciliation checks and a fallback.

Data migration

Data Migration Examples

How to do Data Reconciliation | Match Source vs Target Data

Read API specifications, write source-to-target data mappings and plan a data migration that can be reconciled.