---
updatedAt: 2026-04-16T15:01:28.000Z
agentTools:
  projectIndex: https://apidocs.shipmonk.com/llms.txt
---

# Data Catalog

<br />

# ShipMonk Merchant Data Catalog (MDC)

> **Version:** April 2026\
> **Maintained by:** ShipMonk Tech Solutions

***

## Table of Contents

1. [Overview](#overview)
2. [Delivery Methods](#delivery-methods)
3. [Refresh Cadence](#refresh-cadence)
4. [Schema & Tables Reference](#schema--tables-reference)
5. [Common Joins & FAQ](#common-joins--faq)
6. [Data Nuances by Table](#data-nuances-by-table)
7. [Timestamp Conventions](#timestamp-conventions)
8. [KPI Metrics Reference](#kpi-metrics-reference)
9. [Known Limitations](#known-limitations)
10. [Naming Conventions](#naming-conventions)

***

## Overview

The **Merchant Data Catalog (MDC)** is ShipMonk's standardized, merchant-facing data layer. It exposes curated mart tables which are, purpose-built for analytics and business intelligence.

MDC tables are designed to be:

* **Flat and self-contained** — minimize required joins for common use cases
* **UTC-normalized** — all timestamps follow the `_at_utc` suffix convention
* **Row-level secured** — each merchant sees only their own account's data
* **Reporting-tool-compatible** — field names, types, and structures are optimized for use in ShipMonk Insights and any external BI tool

***

## Delivery Methods

MDC data can be delivered to merchants via several channels. All methods expose the same underlying mart tables.

### 1. Snowflake Data Sharing

The primary and most capable delivery method. Merchants connect directly to the `MARTS.MERCHANT_DATA_CATALOG` schema via a Snowflake data share.

* **Best for:** Teams with a Snowflake account, SQL-first workflows, and external BI tools (Sigma, Tableau, dbt, etc.)
* **Access:** Row-level security (RLS) is enforced via Snowflake Row Access Policies — each merchant's provisioned role filters data automatically to their `ACCOUNT_ID`
* **Latency:** Data available after the nightly MDC refresh (see [Refresh Cadence](#refresh-cadence))
* **Setup:** Contact your ShipMonk Tech Solutions team to initiate provisioning

### 2. SFTP Drop

A file-based export of MDC mart data, delivered as CSVs to a merchant-provided SFTP server.

* **Best for:** Merchants with ERP/WMS integrations, EDI workflows, or internal data warehouses that ingest flat files
* **Delivery schedule:** Aligned to the nightly MDC refresh cadence
* **Format:** One CSV per mart table, gzip-compressed
* **Setup:** Provide your SFTP host, username, password/key, and target directory to ShipMonk. ShipMonk will provide an egress IP address for you to whitelist on your end.

### 3. Amazon S3

Data is exported and placed into a ShipMonk-managed or merchant-managed S3 bucket.

* **Best for:** Merchants already on AWS, or those feeding data into Glue, Athena, Redshift, or similar services
* **Format:** CSV
* **Setup:** Provide your S3 bucket name and IAM details, or ShipMonk can push to a pre-configured bucket with cross-account access

### 4. ShipMonk Insights

In-app access to MDC via the ShipMonk reporting interface, available directly in the ShipMonk portal.

* **Best for:** Merchants who want a no-code, self-serve reporting experience without managing data infrastructure
* **Native SQL:** Available to merchants who need custom analysis — write SQL directly against MDC tables, subject to your tenant row access policy
* **Visual Builder:** Drag-and-drop report building

***

## Refresh Cadence

| Refresh Type                | Frequency                       | Notes                                                                                              |
| --------------------------- | ------------------------------- | -------------------------------------------------------------------------------------------------- |
| **MDC Marts (all tables)**  | Daily delta refresh             | Runs nightly; new and updated records since the last run are applied                               |
| **Row Access Policies**     | Re-applied daily, atomically    | Policies are detached and re-applied during each MDC rebuild to ensure no gap in security coverage |
| **ShipMonk Insights cache** | Configured per report/dashboard | Caching is enabled on high-traffic reports to improve load times                                   |

> ⚠️ **Important:** Because MDC uses a **daily delta refresh**, intraday data is not yet available. Always filter your reports using `<= yesterday` or `< today` for accurate completeness.

***

## Schema & Tables Reference

All MDC tables live in:

```
MARTS.MERCHANT_DATA_CATALOG
```

The double-underscore (`__`) naming pattern on select tables (e.g., `MARTS_MDC__ORDER`) denotes a primary/core domain table vs. a supporting mart.

| Table Name                                 | Domain         | Grain                           | Description                                                                          |
| ------------------------------------------ | -------------- | ------------------------------- | ------------------------------------------------------------------------------------ |
| `MARTS_MDC__ORDER`                         | Orders         | 1 row per order                 | Core order header data including status, timestamps, and shipping address            |
| `MARTS_MDC__ORDER_ITEM`                    | Orders         | 1 row per order item            | SKU-level line detail on each order                                                  |
| `MARTS_MDC_ORDER_LINE`                     | Orders         | 1 row per order line            | Order line with fulfillment-level detail and timestamps                              |
| `MARTS_MDC_ORDER_REFERENCE`                | Orders         | 1 row per order reference value | Flattened order reference/attribute key-value pairs                                  |
| `MARTS_MDC_ORDER_ACTION_REQUIRED`          | Orders         | 1 row per action-required event | History of action-required statuses on orders                                        |
| `MARTS_MDC_ORDER_PART`                     | Orders         | 1 row per order part/split      | Split order part detail                                                              |
| `MARTS_MDC_ORDER_WITH_REMOVED_ITEMS`       | Orders         | 1 row per removed item          | Items that were removed from orders                                                  |
| `MARTS_MDC_ORDER_SHIPPING_SURCHARGES`      | Orders         | 1 row per surcharge             | Shipping surcharge adjustments at the order level                                    |
| `MARTS_MDC_PACKAGE_LEVEL_DETAIL`           | Transportation | 1 row per package               | Package-level shipping data including carrier, zone, weight, and delivery timestamps |
| `MARTS_MDC_PACKAGE_LINE`                   | Transportation | 1 row per package line item     | SKU-level breakdown within each package                                              |
| `MARTS_MDC_SHIPPING_ADJUSTMENTS`           | Transportation | 1 row per adjustment            | Carrier billing adjustments (DIM weight, residential, etc.)                          |
| `MARTS_MDC_INVOICE_ITEM`                   | Billing        | 1 row per invoice line          | Itemized billing charges by category (pick/pack, freight, storage, etc.)             |
| `MARTS_MDC_RECEIVING_LINE`                 | Inbound        | 1 row per ASN line              | ASN line-level receiving detail including expected vs. actual quantities             |
| `MARTS_MDC_RETURN_LINE`                    | Returns        | 1 row per return line           | Return line detail including grade, disposition, and timestamps                      |
| `MARTS_MDC_INVENTORY_LEDGER`               | Inventory      | 1 row per ledger event          | Full inventory transaction history                                                   |
| `MARTS_MDC_INVENTORY_LEDGER_7D`            | Inventory      | 1 row per event (7-day)         | Rolling 7-day view of inventory ledger events                                        |
| `MARTS_MDC_PRODUCT_WITH_ACTUAL_STOCK`      | Inventory      | 1 row per product               | Current on-hand inventory quantities by SKU with product metadata                    |
| `MARTS_MDC_STOCK_UNIT_BY_STORAGE_CATEGORY` | Inventory      | 1 row per SKU/storage type      | Inventory quantity broken out by storage category (pallet, bin, etc.)                |
| `MARTS_MDC_CLAIM`                          | Claims         | 1 row per claim                 | Damage, loss, and shortage claim records                                             |
| `MARTS_MDC_BUNDLE_MAPPING`                 | Products       | 1 row per bundle → component    | Maps bundle SKUs to their component SKUs and quantities                              |

***

## Common Joins & FAQ

### Q: How do I join `ORDER_LINE` to `ORDER` to get order type?

**Join:** `MARTS_MDC_ORDER_LINE.order_id = MARTS_MDC__ORDER.order_id`

> ⚠️ **Critical nuance:** `MARTS_MDC__ORDER` currently only includes **completed orders**. If your order line report includes non-complete orders (backordered, processing, etc.) and you inner-join to `ORDER`, those rows will be silently dropped. Use a LEFT JOIN and account for null records.

Order type (`D2C`, `Retail`/`B2B`) only exists on the `ORDER` table — not on `ORDER_LINE`. This is one of the most common join pain points in MDC today.

```sql
SELECT
    ol.order_id,
    ol.sku,
    ol.quantity,
    o.order_type,
    o.submitted_at_utc
FROM MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC_ORDER_LINE ol
LEFT JOIN MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC__ORDER o
    ON ol.order_id = o.order_id
```

***

### Q: How do I get shipped/delivery timestamps for order lines?

Order line timestamps (shipped, delivered) require a join to `PACKAGE_LEVEL_DETAIL`.

**Join:** `MARTS_MDC_ORDER_LINE.order_id = MARTS_MDC_PACKAGE_LEVEL_DETAIL.order_id`

> ⚠️ **Fan-out risk:** One order line can span multiple packages (e.g., split shipments). This join can produce duplicate rows per order line if an order was multi-packed. Use `COUNT(DISTINCT order_id)` for order-level aggregations, or join at the package level rather than the order line level.

Key timestamps available on `PACKAGE_LEVEL_DETAIL` but **not** on `ORDER_LINE`:

* `delivered_at_utc`
* `truck_checked_out_at_utc` (loaded to truck / first carrier scan)
* `shipped_at_utc`

```sql
SELECT
    ol.order_id,
    ol.sku,
    pld.delivered_at_utc,
    pld.truck_checked_out_at_utc
FROM MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC_ORDER_LINE ol
LEFT JOIN MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC_PACKAGE_LEVEL_DETAIL pld
    ON ol.order_id = pld.order_id
```

***

### Q: How do I calculate Pick & Pack fees per order?

**Join:** `MARTS_MDC_INVOICE_ITEM.reference = MARTS_MDC__ORDER.order_number`

> ⚠️ **Type mismatch:** `INVOICE_ITEM.reference` is a string field representing the order number. `ORDER.order_id` is a numeric ID. **Do not join on `order_id`**. The correct join key is `order_number` (on `ORDER`) matched to `reference` (on `INVOICE_ITEM`).

```sql
SELECT
    o.order_number,
    o.order_type,
    SUM(ii.total_price) AS pick_pack_cost
FROM MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC__ORDER o
LEFT JOIN MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC_INVOICE_ITEM ii
    ON CAST(o.order_number AS VARCHAR) = ii.reference
    AND ii.category = 'Pick & Pack'
GROUP BY 1, 2
```

***

### Q: How do I calculate Click-to-Ship or Click-to-Delivery?

Use `MARTS_MDC__ORDER` for timestamps. After the timestamp expansion in April 2026, the order table includes the full order journey:

| Metric            | Calculation                                                  |
| ----------------- | ------------------------------------------------------------ |
| Click-to-Ship     | `DATEDIFF('hour', created_at_utc, loaded_to_truck_at_utc)`   |
| Click-to-Delivery | `DATEDIFF('hour', created_at_utc, delivered_at_utc)`         |
| Submit-to-Ship    | `DATEDIFF('hour', submitted_at_utc, loaded_to_truck_at_utc)` |

> ⚠️ **Skew warning:** Orders that were backordered, placed on hold, or required action will have inflated time values. Use `order_was_backordered`, `order_was_on_hold`, and `order_was_action_required` flags (where available) to exclude or segment these orders.

***

### Q: How do I join `PACKAGE_LINE` to `ORDER_LINE`?

**Join:** `MARTS_MDC_PACKAGE_LINE.order_id = MARTS_MDC_ORDER_LINE.order_id` AND `MARTS_MDC_PACKAGE_LINE.sku = MARTS_MDC_ORDER_LINE.sku`

There is no direct `order_line_id` join between these two tables. Joining on both `order_id` and `sku` is the most reliable approach, but be aware it can still fan out if the same SKU appears on multiple lines of the same order.

***

### Q: How do I calculate inbound discrepancy (over/under/exact)?

The `RECEIVING_LINE` table contains `expected_quantity` and `received_quantity`. A discrepancy classification can be derived with:

```sql
SELECT
    asn_id,
    sku,
    expected_quantity,
    received_quantity,
    CASE
        WHEN received_quantity > expected_quantity THEN 'over'
        WHEN received_quantity < expected_quantity THEN 'under'
        ELSE 'exact'
    END AS discrepancy_type
FROM MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC_RECEIVING_LINE
```

> A pre-calculated `discrepancy_type` column is on the MDC roadmap to avoid this repetitive case statement.

***

### Q: What is `order_key` and when do I use it vs `order_id`?

| Field          | Type                     | Use For                                                                               |
| -------------- | ------------------------ | ------------------------------------------------------------------------------------- |
| `order_id`     | Integer                  | Primary key for joins between MDC tables                                              |
| `order_number` | String (treat as string) | Human-readable order identifier; also used to join with `INVOICE_ITEM.reference`      |
| `order_key`    | String                   | Surrogate key added in the April 2026 MDC deployment; used for cross-mart consistency |

> ⚠️ Always treat `order_number` as a **string**. If it is detected as numeric by your reporting tool, it may be auto-binned in charts, producing incorrect results. This is a known schema issue being addressed.

***

### Q: How do I look up bundle components for a SKU?

Use `MARTS_MDC_BUNDLE_MAPPING`, which maps bundle SKUs to their component SKUs and expected quantities per bundle unit.

```sql
SELECT
    bundle_sku,
    component_sku,
    component_quantity
FROM MARTS.MERCHANT_DATA_CATALOG.MARTS_MDC_BUNDLE_MAPPING
WHERE bundle_sku = 'YOUR-BUNDLE-SKU'
```

***

## Data Nuances by Table

### `MARTS_MDC__ORDER`

* Only includes **completed orders** as of April 2026. Non-complete orders (backordered, cancelled, and in-progress) are currently excluded. This is a known gap scheduled to be resolved.
* `order_number` should be treated as a string, even if the value appears numeric.
* Timestamps were added in April 2026. Orders predating this update may have null timestamp values.
* `order_type` distinguishes D2C from B2B/Retail and is only available here — not on `ORDER_LINE`.

### `MARTS_MDC_ORDER_LINE`

* Contains duplicate date columns in some environments (`ordered_at_utc` / `date_ordered`, `order_submitted_at_utc` / `date_submitted`, etc.). Prefer the `_at_utc` suffixed columns — the legacy `date_*` columns are deprecated.
* `order_shipped_at_utc` is present, but delivery timestamps require joining to `PACKAGE_LEVEL_DETAIL`.
* Does **not** have `order_type` — join to `ORDER` to filter by D2C vs. B2B.

### `MARTS_MDC_PACKAGE_LEVEL_DETAIL`

* The richest source for carrier, zone, weight, and delivery timestamps.
* `zone` on this table vs. `shipping_zone` on `ORDER_LINE` — these represent the same concept but are named differently. Prefer `zone` from `PACKAGE_LEVEL_DETAIL` for transportation analysis.
* `trading_partner` here maps to `account_trading_partner_name` on `ORDER_LINE`.
* `store` here maps to `store_name` on `ORDER_LINE` and `CLAIM`.

### `MARTS_MDC_INVOICE_ITEM`

* Join to orders using `reference` = `order_number`, **not** `order_id`.
* Use `category` to filter charge types: `Pick & Pack`, `Freight`, `Storage`, `Returns`, `Receiving`, etc.
* `store` on this table = `store_name` on `ORDER_LINE`.

### `MARTS_MDC_RECEIVING_LINE`

* Does not yet include `tracking_number` (planned for a future release).
* Does not have a pre-calculated discrepancy classification — use a CASE statement on `expected_quantity` vs. `received_quantity`.

### `MARTS_MDC_PRODUCT_WITH_ACTUAL_STOCK`

* Column is named `product_sku` here, while most other tables use `sku`. Use this field for joins, but be explicit about the alias.
* Replacement cost / value fields are present and useful for claims valuation and inventory shrink calculations.

### `MARTS_MDC_INVENTORY_LEDGER` / `MARTS_MDC_INVENTORY_LEDGER_7D`

* Use `location_timezone` for the warehouse timezone field (note: other tables use `warehouse_timezone` for the same concept).
* Adjustment categories include: `cycle_count`, `damaged`, `receiving_error` — use these to calculate net/gross inventory adjustments.

### `MARTS_MDC_RETURN_LINE`

* Uses `warehouse_timezone` (not `location_timezone` as in the Ledger tables). Take care when combining these tables.
* `expiration_date` here vs. `lot_expiration_date` on `INVENTORY_LEDGER` — same concept, different column names.

### `MARTS_MDC_CLAIM`

* `claim_order_shipped_at_utc` is this table's version of the shipped timestamp (note the `claim_` prefix distinguishing it from other tables).
* `store_name` and `shipping_method_name` naming is consistent with `ORDER_LINE`.

***

## Timestamp Conventions

All MDC timestamps follow the convention: `{event}_at_utc`

Examples: `created_at_utc`, `submitted_at_utc`, `picked_at_utc`, `packed_at_utc`, `shipped_at_utc`, `delivered_at_utc`

The full order lifecycle timestamps available on `MARTS_MDC__ORDER` (post-April 2026):

| Timestamp                     | Description                                                  |
| ----------------------------- | ------------------------------------------------------------ |
| `ordered_at_utc`              | When the order was placed by the end customer                |
| `created_at_utc`              | When the order was created in ShipMonk                       |
| `submitted_at_utc`            | When the order was submitted for fulfillment                 |
| `picked_at_utc`               | When all items on the order were picked                      |
| `packed_at_utc`               | When the order was packed                                    |
| `loaded_to_truck_at_utc`      | When the package was loaded onto a carrier truck ("shipped") |
| `picked_up_at_utc`            | First carrier scan after pickup                              |
| `marketplace_notified_at_utc` | When the marketplace was notified of shipment                |
| `delivered_at_utc`            | When the package was delivered to the end customer           |

> **Note on local timezones:** Local timezone variants (`_at_local`) exist in the source data but are not yet standardized across all MDC tables. When a local timestamp is needed, derive it using the `location_timezone` or `warehouse_timezone` field available on most tables.

***

## KPI Metrics Reference

These are the approved KPIs available in ShipMonk Insights, organized by domain. All metrics are built on MDC tables.

### D2C

| Metric                              | Primary Table                          | Time Dimension     | Notes                                                      |
| ----------------------------------- | -------------------------------------- | ------------------ | ---------------------------------------------------------- |
| Orders Shipped                      | `ORDER_LINE`                           | `submitted_at_utc` | Filter: order type = D2C                                   |
| Units Shipped                       | `ORDER_LINE`                           | `submitted_at_utc` | SUM quantity, filter: D2C                                  |
| Click-to-Ship                       | `ORDER`                                | `submitted_at_utc` | `created_at_utc` → `loaded_to_truck_at_utc`                |
| Click-to-Delivery                   | `ORDER`                                | `submitted_at_utc` | `created_at_utc` → `delivered_at_utc`                      |
| Submit-to-Ship                      | `ORDER`                                | `submitted_at_utc` | `submitted_at_utc` → `loaded_to_truck_at_utc`              |
| Pick & Pack Cost/Order              | `INVOICE_ITEM`                         | `submitted_at_utc` | Category = Pick & Pack; join via `order_number`            |
| Packaging Cost/Order                | `INVOICE_ITEM`                         | `submitted_at_utc` | Category = Packaging                                       |
| Shipping Cost/Order                 | `INVOICE_ITEM`                         | `submitted_at_utc` | Category = Freight                                         |
| Shipping Service Mix                | `ORDER_LINE` or `PACKAGE_LEVEL_DETAIL` | `submitted_at_utc` | % of orders by shipping service                            |
| Unable to Submit / Backorder Orders | `ORDER`                                | `submitted_at_utc` | COUNT DISTINCT orders where processing\_status = backorder |
| Order Split by Node                 | `ORDER_LINE`                           | `submitted_at_utc` | COUNT DISTINCT orders by warehouse                         |

### B2B

| Metric                       | Primary Table  | Time Dimension     | Notes                                                                            |
| ---------------------------- | -------------- | ------------------ | -------------------------------------------------------------------------------- |
| Orders Shipped               | `ORDER_LINE`   | `submitted_at_utc` | Filter: order type = Retail                                                      |
| Units Shipped                | `ORDER_LINE`   | `submitted_at_utc` | SUM quantity, filter: Retail                                                     |
| OTRS (On Time Ready to Ship) | `ORDER`        | `submitted_at_utc` | `packed_at_utc <= sla_deadline_utc`; SLA fields planned for a future MDC release |
| Pick & Pack Cost/Order       | `INVOICE_ITEM` | `submitted_at_utc` | Category = Pick & Pack, order type = Retail                                      |

### Inbound

| Metric                                  | Primary Table    | Time Dimension    | Notes                                              |
| --------------------------------------- | ---------------- | ----------------- | -------------------------------------------------- |
| Units Received by SKU                   | `RECEIVING_LINE` | `received_at_utc` | SUM received\_quantity                             |
| Arrival to Availability (Dock to Stock) | `RECEIVING_LINE` | `received_at_utc` | `arrived_at_utc` → `received_at_utc`               |
| Inbound by Discrepancy                  | `RECEIVING_LINE` | `received_at_utc` | over/under/exact classification via CASE statement |
| % ASNs Non-Compliant                    | `RECEIVING_LINE` | `received_at_utc` | Requires a subquery; use native SQL mode           |

### Inventory

| Metric                   | Primary Table               | Time Dimension | Notes                                       |
| ------------------------ | --------------------------- | -------------- | ------------------------------------------- |
| Inventory by SKU         | `PRODUCT_WITH_ACTUAL_STOCK` | Daily snapshot | Current on-hand by SKU                      |
| Inventory by Disposition | `PRODUCT_WITH_ACTUAL_STOCK` | Daily snapshot | Group by disposition status                 |
| Net Adjustments          | `INVENTORY_LEDGER`          | Weekly update  | SUM quantity for adjustment categories      |
| Gross Adjustments        | `INVENTORY_LEDGER`          | Weekly update  | SUM ABS(quantity) for adjustment categories |
| Storage Cost             | `INVOICE_ITEM`              | Weekly update  | Category = Storage                          |

### Returns

| Metric                  | Primary Table | Time Dimension    | Notes                                    |
| ----------------------- | ------------- | ----------------- | ---------------------------------------- |
| Top Returned SKUs       | `RETURN_LINE` | `received_at_utc` | SUM units returned by SKU                |
| Returns Processed       | `RETURN_LINE` | `received_at_utc` | COUNT returns                            |
| Recovery Rate (New)     | `RETURN_LINE` | `received_at_utc` | COUNT where grade = New → back to stock  |
| Blind RMA Creation      | `RETURN_LINE` | `received_at_utc` | COUNT 6-digit format RMAs                |
| Return Discrepancy Rate | `RETURN_LINE` | `received_at_utc` | Requires a subquery; use native SQL mode |

### Billing

| Metric                    | Primary Table  | Time Dimension   | Notes                     |
| ------------------------- | -------------- | ---------------- | ------------------------- |
| Costs by Month            | `INVOICE_ITEM` | `created_at_utc` | GROUP BY category         |
| Duplicate Billing Charges | `INVOICE_ITEM` | `created_at_utc` | Filter by charge category |

### Transportation

| Metric                      | Primary Table          | Time Dimension | Notes                     |
| --------------------------- | ---------------------- | -------------- | ------------------------- |
| Average Shipping Cost/Order | `PACKAGE_LEVEL_DETAIL` | —              | SUM charge / COUNT orders |
| Total Shipping Costs        | `INVOICE_ITEM`         | —              | Category = Freight        |

***

## Known Limitations

### ShipMonk Insights Visual Builder: No Subqueries

The visual report builder in ShipMonk Insights does not support subqueries. Metrics that require a subquery (e.g., % of ASNs non-compliant, discrepancy rates, return rates) **cannot be built using the drag-and-drop builder** — they require native SQL mode.

### Order Table: Completed Orders Only

`MARTS_MDC__ORDER` currently excludes non-complete orders. Backordered, cancelled, and in-progress orders are not present. This means:

* Filtering `ORDER_LINE` by order type requires a join that may silently drop incomplete orders
* Metrics like "orders unable to submit" cannot be fully calculated from the `ORDER` table alone

This is the highest-priority schema gap being addressed by ShipMonk's engineering team.

### SLA Fields Not Yet in MDC

OTRS and SLA deadline calculations are not yet available in MDC. The following fields are planned for a future release:

* `sla_deadline_utc` — the latest timestamp an order must leave the warehouse to meet its SLA commitment
* `is_ready_to_ship_within_sla` — a boolean pass/fail flag for on-time readiness

In the meantime, contact your ShipMonk Tech Solutions team for assistance calculating SLA metrics.

### Inconsistent Column Naming Across Tables

Several conceptually identical fields have different names across tables. Notable examples:

| Concept           | Tables & Column Names                                                                                                      |
| ----------------- | -------------------------------------------------------------------------------------------------------------------------- |
| Timezone          | `location_timezone` (Ledger, PLD) vs. `warehouse_timezone` (Order Line, Return Line) vs. `timezone` (Shipping Adjustments) |
| SKU               | `sku` (most tables) vs. `product_sku` (Product with Actual Stock)                                                          |
| Store Name        | `store_name` (Claim, Order Line) vs. `store` (Invoice Item, PLD)                                                           |
| Shipping Method   | `shipping_method_name` (Claim, Shipping Adjustments) vs. `shipping_method` (Order Line)                                    |
| Shipped Timestamp | `order_shipped_at_utc` (Order Line) vs. `shipped_at_utc` (PLD) vs. `claim_order_shipped_at_utc` (Claim)                    |
| Trading Partner   | `trading_partner` (Invoice Item, PLD) vs. `account_trading_partner_name` (Order Line)                                      |

A standardization effort is underway. New canonical column names will be added; legacy names will be marked deprecated in field descriptions.

### `order_number` May Be Misdetected as Numeric

Some reporting tools will detect `order_number` as a numeric field and apply automatic bucketing or binning in chart axes, producing meaningless groupings. Always configure `order_number` as a string/category field in your visualizations.

***

## Naming Conventions

| Convention                 | Example                                          | Notes                                                           |
| -------------------------- | ------------------------------------------------ | --------------------------------------------------------------- |
| Table prefix               | `MARTS_MDC_`                                     | All MDC tables start with this prefix                           |
| Primary domain tables      | `MARTS_MDC__ORDER` (double underscore)           | Double underscore denotes a primary/core mart                   |
| Timestamp suffix           | `submitted_at_utc`                               | All UTC timestamps use the `_at_utc` suffix                     |
| ID fields                  | `order_id`, `account_id`, `product_id`           | Integer primary/foreign keys for joining tables                 |
| Human-readable identifiers | `order_number`, `sku`, `warehouse_name`          | String fields; do not use for arithmetic                        |
| Deprecated fields          | Marked with `[DEPRECATED]` in field descriptions | Still present for backwards compatibility; avoid in new queries |

***

*For questions, schema change requests, or to report a data issue, contact your ShipMonk Tech Solutions team.*