ShipMonk Merchant Data Catalog (MDC)

Version: April 2026
Maintained by: ShipMonk Tech Solutions

Table of Contents

1. Overview
2. Delivery Methods
3. Refresh Cadence
4. Schema & Tables Reference
5. Common Joins & FAQ
6. Data Nuances by Table
7. Timestamp Conventions
8. KPI Metrics Reference
9. Known Limitations
10. 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)
• 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

⚠️

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.

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.

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

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).

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:

⚠️

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:

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?

⚠️

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.

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):

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

B2B

Inbound

Inventory

Returns

Billing

Transportation

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:

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

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