SAP Global Trade Services (GTS) data model
Normalized replica of SAP GTS: master data (business partner, country, legal regulation, self-referencing HS commodity code, denied-party list); customs declaration header → item with commodity classification and duty, product classification and FTA preference determination; and compliance objects — SPL screening audit results, embargo checks, and export licenses with value/quantity depreciation. The commodity-code hierarchy is a self-reference (plain key, no FK) to avoid a cycle.
SAP Global Trade Services (GTS, /SAPSLL/) modeled as a normalized (3NF) source-table replica: Compliance Management (Sanctioned Party List screening of business partners against OFAC/EU/UN denied-party lists, embargo checks, and export/legal-control licenses) and Customs Management (import/export customs declarations with commodity HS/tariff classification, customs value and duty, plus free-trade-agreement preference determination). Surrogate keys are warehouse-generated while GTS business keys (customs document, commodity code, license number, partner) are preserved; partner names and screening matches are PII; built for OFAC / C-TPAT trade-compliance auditability. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 12
- tables
- 107
- columns
- 15
- relationships
- 4
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
SPL screening modeled as an append-only audit result table
OFAC/EU/UN sanctioned-party screening must keep every screening event (match status, score, matched term, release decision) for audit; a dedicated spl_screening_result table per partner-per-list preserves that trail rather than overwriting a partner status flag.
Self-referencing HS commodity-code hierarchy
Customs tariff (HS) codes form a multi-level hierarchy; modeling the parent as a plain key column (not an FK) gives drill-up classification while avoiding the self-loop the validator rejects as a circular FK.
Customs declaration header → item with duty at the item grain
Duty and customs value are determined per commodity line, so a header (customs_document) → item (customs_document_item) split keyed by the GTS declaration number and item number mirrors the source and supports duty and statistical-value reporting.
License value/quantity depreciation tracked on the license record
Export licenses authorize a limited value/quantity that depletes as goods ship; carrying value_limit/value_used and quantity_limit/quantity_used on the license enables utilization and expiry monitoring.
Preference determination separated from classification
Free-trade-agreement preference qualification depends on rules of origin and vendor declarations independent of the base HS classification, so it is its own table linked to commodity code and the preference regulation.
Tables in the SAP Global Trade Services (GTS) model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (7)
customs_document
Grain: one row per import/export customs declaration (header)
GTS customs declaration header (import or export).
| Column | Type | Keys |
|---|---|---|
| customs_document_key | bigint | PK |
| customs_doc_id | varchar | BK |
| direction | varchar | |
| business_partner_key | bigint | |
| legal_regulation_key | bigint | |
| country_destination_key | bigint | |
| country_dispatch_code | varchar | |
| customs_office | varchar | |
| declaration_date | date | |
| declaration_status | varchar | |
| total_customs_value | decimal | |
| currency | varchar | |
| mode_of_transport | varchar |
customs_document_item
Grain: one row per customs declaration item (customs_doc_id, item_number)
GTS customs declaration item carrying commodity classification and duty.
| Column | Type | Keys |
|---|---|---|
| customs_document_item_key | bigint | PK |
| customs_doc_id | varchar | BK |
| item_number | varchar | BK |
| customs_document_key | bigint | |
| commodity_code_key | bigint | |
| product_number | varchar | |
| item_quantity | decimal | |
| customs_value | decimal | |
| statistical_value | decimal | |
| duty_amount | decimal | |
| country_of_origin | varchar | |
| currency | varchar |
product_classification
Grain: one row per product-to-commodity-code classification
GTS product classification — assigns products to customs commodity codes.
| Column | Type | Keys |
|---|---|---|
| product_classification_key | bigint | PK |
| classification_id | varchar | BK |
| product_number | varchar | BK |
| commodity_code_key | bigint | |
| classification_status | varchar | |
| valid_from | date | |
| classified_by | varchar | |
| numbering_scheme | varchar |
preference_determination
Grain: one row per free-trade-agreement preference determination
GTS preference determination for free-trade-agreement duty savings.
| Column | Type | Keys |
|---|---|---|
| preference_determination_key | bigint | PK |
| preference_id | varchar | BK |
| commodity_code_key | bigint | |
| legal_regulation_key | bigint | |
| agreement_name | varchar | |
| preference_status | varchar | |
| valid_from | date | |
| rule_of_origin | varchar | |
| vendor_declaration_status | varchar |
spl_screening_result
Grain: one row per sanctioned-party-list screening result
GTS SPL screening audit trail (OFAC / EU / UN denied-party screening).
| Column | Type | Keys |
|---|---|---|
| spl_screening_result_key | bigint | PK |
| screening_id | varchar | BK |
| business_partner_key | bigint | |
| denied_party_list_key | bigint | |
| screened_object_type | varchar | |
| screened_date | timestamp | |
| match_status | varchar | |
| match_score | decimal | |
| matched_name | varchar | PII |
| decision | varchar | |
| decided_by | varchar | PII |
| is_blocked | boolean |
embargo_check
Grain: one row per embargo / country compliance check
GTS embargo/country check against legal regulations.
| Column | Type | Keys |
|---|---|---|
| embargo_check_key | bigint | PK |
| check_id | varchar | BK |
| customs_document_key | bigint | |
| country_key | bigint | |
| legal_regulation_key | bigint | |
| check_result | varchar | |
| check_date | timestamp | |
| is_embargoed | boolean |
license
Grain: one row per export license / legal-control license
GTS export/legal-control license with value & quantity depreciation.
| Column | Type | Keys |
|---|---|---|
| license_key | bigint | PK |
| license_id | varchar | BK |
| license_number | varchar | BK |
| legal_regulation_key | bigint | |
| business_partner_key | bigint | |
| license_type | varchar | |
| valid_from | date | |
| valid_to | date | |
| value_limit | decimal | |
| value_used | decimal | |
| quantity_limit | decimal | |
| quantity_used | decimal | |
| currency | varchar | |
| license_status | varchar |
Lookup tables (5)
business_partner
Grain: one row per GTS business partner (customer / vendor / carrier)
SAP GTS business partner (replicated from feeder system BP / KNA1 / LFA1).
| Column | Type | Keys |
|---|---|---|
| business_partner_key | bigint | PK |
| partner | varchar | BK |
| name1 | varchar | PII |
| partner_type | varchar | |
| country | varchar | |
| city | varchar | PII |
country
Grain: one row per country / customs territory
GTS country / customs-territory check table.
| Column | Type | Keys |
|---|---|---|
| country_key | bigint | PK |
| country_code | varchar | BK |
| country_name | varchar | |
| region | varchar | |
| is_embargoed | boolean | |
| trade_block | varchar |
legal_regulation
Grain: one row per legal regulation
GTS legal regulation — the basis for compliance, customs and preference checks.
| Column | Type | Keys |
|---|---|---|
| legal_regulation_key | bigint | PK |
| regulation | varchar | BK |
| description | varchar | |
| regulation_type | varchar | |
| regulating_country | varchar |
commodity_code
Grain: one row per customs commodity (HS / tariff) code
GTS commodity code (HS/tariff) master with a self-referencing hierarchy.
| Column | Type | Keys |
|---|---|---|
| commodity_code_key | bigint | PK |
| commodity_code | varchar | BK |
| description | varchar | |
| code_hierarchy_level | smallint | |
| parent_commodity_key | bigint | |
| duty_rate | decimal | |
| statistical_unit | varchar | |
| numbering_scheme | varchar |
denied_party_list
Grain: one row per sanctioned/denied party list version
GTS sanctioned-party list master used for SPL screening.
| Column | Type | Keys |
|---|---|---|
| denied_party_list_key | bigint | PK |
| list_id | varchar | BK |
| list_name | varchar | |
| list_source | varchar | |
| list_version | varchar | |
| effective_date | date |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- customs_document.business_partner_key → business_partner (many-to-one)
- customs_document.legal_regulation_key → legal_regulation (many-to-one)
- customs_document.country_destination_key → country (many-to-one)
- customs_document_item.customs_document_key → customs_document (many-to-one)
- customs_document_item.commodity_code_key → commodity_code (many-to-one)
- product_classification.commodity_code_key → commodity_code (many-to-one)
- preference_determination.commodity_code_key → commodity_code (many-to-one)
- preference_determination.legal_regulation_key → legal_regulation (many-to-one)
- spl_screening_result.business_partner_key → business_partner (many-to-one)
- spl_screening_result.denied_party_list_key → denied_party_list (many-to-one)
- embargo_check.customs_document_key → customs_document (many-to-one)
- embargo_check.country_key → country (many-to-one)
- embargo_check.legal_regulation_key → legal_regulation (many-to-one)
- license.legal_regulation_key → legal_regulation (many-to-one)
- license.business_partner_key → business_partner (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.