SAP FI/CO Accounting data model
Third-normal-form mirror of the SAP FI/CO tables as they land from the source ERP. Each SAP table becomes its own entity with a warehouse surrogate key plus the genuine SAP business key(s); line items reference their document header and the G/L / cost-center masters via foreign keys, and AR/AP subledger items hang off the BSEG line. Built for SOX and IFRS reconciliation.
Normalized replica of SAP ECC/S4HANA Financial Accounting and Controlling source tables for a warehouse staging/replication layer: accounting document header (BKPF) and line items (BSEG), the G/L account master (SKA1/SKB1), open/cleared AR (BSID/BSAD) and AP (BSIK/BSAK) subledger items, cost centers (CSKS) and CO line items (COEP) — keyed by the real SAP business keys (bukrs, belnr, gjahr, buzei, saknr, kostl) and anchored to T001/T003/posting-key/currency check tables. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 11
- tables
- 115
- columns
- 11
- relationships
- 2
- 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
Header/line normalization mirrors SAP FI
SAP FI stores every posting as a BKPF document header with one or more BSEG line items keyed by (bukrs, belnr, gjahr, buzei); replicating that 1:1 keeps the staging layer lossless and reconcilable to the source.
AR/AP subledger items kept as separate entities
SAP indexes customer (BSID/BSAD) and vendor (BSIK/BSAK) open and cleared items separately from BSEG for subledger reporting; modeling them as their own tables with an is_cleared flag preserves the open-item lifecycle.
Controlling line items modeled alongside FI
CO postings (COEP) reference cost centers (CSKS) and cost elements, and do not always have a 1:1 FI document; keeping COEP as its own period-keyed entity supports cost-center reporting without distorting the FI ledger.
Check tables modeled as lookups
Company code (T001), document type (T003), posting key (TBSL) and currency (TCURC) are slow SAP check tables; modeling them as lookups without an audit trail keeps reference data clean and avoids re-keying every line.
Tables in the SAP FI/CO Accounting 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)
gl_master
Grain: one row per G/L account in a company code (ktopl/saknr/bukrs)
SAP SKA1 (chart of accounts) + SKB1 (company-code) — G/L account master.
| Column | Type | Keys |
|---|---|---|
| gl_master_key | bigint | PK |
| company_code_key | bigint | |
| saknr | varchar | BK |
| bukrs | varchar | BK |
| ktopl | varchar | |
| txt50 | varchar | |
| ktoks | varchar | |
| xbilk | boolean | |
| waers | varchar | |
| mitkz | varchar |
cost_center
Grain: one row per cost center (kokrs/kostl/datbi)
SAP CSKS — cost center master.
| Column | Type | Keys |
|---|---|---|
| cost_center_key | bigint | PK |
| company_code_key | bigint | |
| kokrs | varchar | BK |
| kostl | varchar | BK |
| datbi | date | BK |
| datab | date | |
| ktext | varchar | |
| verak | varchar | PII |
| bukrs | varchar | |
| kosar | varchar |
accounting_header
Grain: one row per accounting document header (bukrs/belnr/gjahr)
SAP BKPF — accounting document header.
| Column | Type | Keys |
|---|---|---|
| accounting_header_key | bigint | PK |
| company_code_key | bigint | |
| document_type_key | bigint | |
| bukrs | varchar | BK |
| belnr | varchar | BK |
| gjahr | varchar | BK |
| blart | varchar | |
| bldat | date | |
| budat | date | |
| monat | varchar | |
| waers | varchar | |
| usnam | varchar | PII |
| xreversal | varchar | |
| tcode | varchar |
accounting_line
Grain: one row per accounting document line item (bukrs/belnr/gjahr/buzei)
SAP BSEG — accounting document segment (line item).
| Column | Type | Keys |
|---|---|---|
| accounting_line_key | bigint | PK |
| accounting_header_key | bigint | |
| posting_key_key | bigint | |
| gl_master_key | bigint | |
| cost_center_key | bigint | |
| bukrs | varchar | BK |
| belnr | varchar | BK |
| gjahr | varchar | BK |
| buzei | varchar | BK |
| bschl | varchar | |
| shkzg | varchar | |
| saknr | varchar | |
| hkont | varchar | |
| dmbtr | decimal | |
| wrbtr | decimal | |
| mwskz | varchar | |
| kostl | varchar | |
| zuonr | varchar | |
| sgtxt | varchar |
customer_line
Grain: one row per AR open/cleared item (bukrs/kunnr/belnr/gjahr/buzei)
SAP BSID (open) / BSAD (cleared) — accounts receivable line items.
| Column | Type | Keys |
|---|---|---|
| customer_line_key | bigint | PK |
| accounting_line_key | bigint | |
| bukrs | varchar | BK |
| kunnr | varchar | BK |
| belnr | varchar | BK |
| gjahr | varchar | BK |
| buzei | varchar | BK |
| umsks | varchar | |
| budat | date | |
| dmbtr | decimal | |
| shkzg | varchar | |
| zfbdt | date | |
| augbl | varchar | |
| augdt | date | |
| is_cleared | boolean |
vendor_line
Grain: one row per AP open/cleared item (bukrs/lifnr/belnr/gjahr/buzei)
SAP BSIK (open) / BSAK (cleared) — accounts payable line items.
| Column | Type | Keys |
|---|---|---|
| vendor_line_key | bigint | PK |
| accounting_line_key | bigint | |
| bukrs | varchar | BK |
| lifnr | varchar | BK |
| belnr | varchar | BK |
| gjahr | varchar | BK |
| buzei | varchar | BK |
| umsks | varchar | |
| budat | date | |
| dmbtr | decimal | |
| shkzg | varchar | |
| zfbdt | date | |
| augbl | varchar | |
| augdt | date | |
| is_cleared | boolean |
cost_center_line
Grain: one row per CO line item (kokrs/belnr/buzei)
SAP COEP — CO object: line items by period.
| Column | Type | Keys |
|---|---|---|
| cost_center_line_key | bigint | PK |
| cost_center_key | bigint | |
| kokrs | varchar | BK |
| belnr | varchar | BK |
| buzei | varchar | BK |
| gjahr | varchar | |
| perio | varchar | |
| kostl | varchar | |
| kstar | varchar | |
| wkgbtr | decimal | |
| megbtr | decimal | |
| meinb | varchar | |
| beknz | varchar |
Lookup tables (4)
company_code
Grain: one row per company code
SAP T001 — company code check table.
| Column | Type | Keys |
|---|---|---|
| company_code_key | bigint | PK |
| bukrs | varchar | BK |
| butxt | varchar | |
| land1 | varchar | |
| waers | varchar | |
| ktopl | varchar |
document_type
Grain: one row per FI document type
SAP T003 / T003T — document type check table.
| Column | Type | Keys |
|---|---|---|
| document_type_key | bigint | PK |
| blart | varchar | BK |
| ltext | varchar | |
| numkr | varchar |
posting_key
Grain: one row per posting key
SAP TBSL — posting key check table.
| Column | Type | Keys |
|---|---|---|
| posting_key_key | bigint | PK |
| bschl | varchar | BK |
| ltext | varchar | |
| shkzg | varchar | |
| koart | varchar |
currency
Grain: one row per currency
SAP TCURC — currency code check table.
| Column | Type | Keys |
|---|---|---|
| currency_key | bigint | PK |
| waers | varchar | BK |
| ltext | varchar | |
| currdec | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- gl_master.company_code_key → company_code (many-to-one)
- cost_center.company_code_key → company_code (many-to-one)
- accounting_header.company_code_key → company_code (many-to-one)
- accounting_header.document_type_key → document_type (many-to-one)
- accounting_line.accounting_header_key → accounting_header (many-to-one)
- accounting_line.posting_key_key → posting_key (many-to-one)
- accounting_line.gl_master_key → gl_master (many-to-one)
- accounting_line.cost_center_key → cost_center (many-to-one)
- customer_line.accounting_line_key → accounting_line (many-to-one)
- vendor_line.accounting_line_key → accounting_line (many-to-one)
- cost_center_line.cost_center_key → cost_center (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.