Two Columns, One SAP Material Number, and the Join Uses the Padded One

Query your SAP S/4HANA data with AI against a replicated warehouse, filter on the material number everyone in the business says out loud, and you get nothing back. The rows exist. SAP stores material 4711 as 000000000000004711, and those MATNR leading zeros are the whole problem. Your connector noticed, stripped them into a second column, and then wrote every one of its joins against the first one.

Before you start

• An S/4HANA estate you can reach with SQL. The managed route is Fivetran's SAP ERP on HANA connector, which covers "SAP ECC and S/4HANA systems running on a HANA platform". It needs an Enterprise or Business Critical plan.
• No second vendor. Airbyte publishes no SAP HANA source; the URL 404s. If your SAP data landed, it almost certainly came through Fivetran or a generic database replication.
• No free instance to practise on. SAP's route to a working system is the Cloud Appliance Library, which needs your own paid cloud infrastructure and an SAP entitlement. This post runs against the extract you already have.
• Know which client is production. You'll need it by step three, and it isn't in the data.
• Expect casing to vary. The SQL below is lower case, matching fivetran/dbt_sap. A Snowflake destination commonly lands these uppercase, so check yours.

The question

"What did we sell of material 4711 last quarter?"

Somebody in supply chain asks it that way, with that number, because 4711 is what the material is called in every conversation, every spreadsheet, and every screen inside SAP. Your job is to make the warehouse answer the question in the words it was asked in.

What breaks

An agent reads the schema, finds matnr on the sales order item table, and writes the obvious thing:

-- WRONG. Two defects, and neither one raises an error.
select sum(i.netwr) as net_value
from   vbap i
join   vbak h on h.vbeln = i.vbeln
where  i.matnr = '4711';

It returns a single row reading 0, or no row at all. Nothing was violated. The column exists, the join compiles, and the predicate is valid SQL that happens to match no stored value. An agent looking at that result has no way to tell "we sold none" from "I asked the wrong question".

The second attempt is worse, because it looks like it's using the modelled layer properly. Fivetran's own package ships a material dimension, so an agent joins to it:

-- WRONG in a more expensive way. material_number is the readable
-- copy, and it joins to nothing in the fact table.
select sum(f.net_value) as net_value
from   sap__fact_sales_order f
join   sap__dim_material m
  on   m.material_number = f.material_id;

That one returns an empty result for every padded material in the estate. Both columns are real, both are in the same table, and their names differ by one word.

Why it breaks

SAP declares the readable form on the type, not on the value

SAP publishes the file formats for its own repository objects at SAP/abap-file-formats, MIT licensed. The schema for a domain, the object that defines a data type, is doma-v1.json. Read its top-level properties and the answer is sitting in the layout:

formatVersion, header, format, outputCharacteristics,
fixedValues, fixedValueIntervals, valueTable, fixedValueAppends

Two of those sit side by side, and they split this post in half:

By SAP's own published schema, the conversion that turns 000000000000004711 into 4711 is an output characteristic. It's filed beside output length and output style, and separately from format, which is how the value is actually stored. The stored value is what format says: a character field of a fixed width, holding padded digits.

Because the rule lives on the domain rather than on any one screen, every field built on that domain inherits it. One declaration, made once, covers every table column, every report, and every API payload that uses that type.

The connector's own transform proves the padded form lands

You don't have to take an inference for it. The party that moved your data says what it moved. fivetran/dbt_sap strips those zeros in four separate places, and nothing would need stripping if the landed value weren't padded:

-- sap__dim_material.sql
ltrim(material.material_id , '0') as material_number,

-- sap__dim_customer.sql
ltrim(customer_id, '0' ) as customer_number,

-- sap__fact_sales_order.sql
ltrim(sales_document_header.sales_document_id, '0') as sales_document_number,
ltrim(sales_document_item.sales_document_item_id, '0') as sales_document_item,

Four identifiers, one pattern. This isn't a quirk of the material number. It's what happens to every numeric identifier SAP stores, which is why kunnr, vbeln, and posnr are all on that list.

Two columns for one identifier, and the join uses the other one

Here's the detail that turns a footnote into a post. Fivetran doesn't replace the padded column with the trimmed one. It ships both, in the same table. The whole select list of sap__dim_customer.sql is four lines long:

select ltrim(customer_id, '0' ) as customer_number,
       country_key_id,
       name as customer_name,
       city,
       customer_id
from {{ ref('int_sap__customer') }}

customer_number and customer_id are the same customer, in two forms, one column apart. The material dimension does exactly the same thing with material_number and material_id.

Now read the joins in the fact model. Every single one of them uses the untrimmed column:

inner join sales_document_item
  on sales_document_header.sales_document_id
   = sales_document_item.sales_document_id

inner join sales_document_item_status
  on sales_document_item.sales_document_id
   = sales_document_item_status.sales_document_id
 and sales_document_item.sales_document_item_id
   = sales_document_item_status.sd_item_id

So the trimmed _number columns are display output and they join to nothing. They're correct, they're useful, and they're the ones a human will reach for, because they hold the value a human recognises. Pick the pair the wrong way round and you get an empty result rather than an error.

The declaration on the left is the one thing that cannot replicate. Every quotation is verbatim from SAP's or Fivetran's own published source.

The client column sits underneath all of it

One more column deserves a sentence before you write any filter, because it changes totals rather than emptying them. Every table here is keyed on mandt, the SAP client, before its business key. Fivetran is direct about what that means:

"When logging into an SAP system, the login is client-specific, and data is automatically filtered by client (MANDT). Our connector has broader data access and can replicate data for all clients, regardless of the logon client (client-independent data selection)."

So by default your warehouse holds production beside every test and SAP-delivered client. Fivetran's own package is split on it: the extractor-report models each require a client variable and filter on it, while the star-schema models carry the client through the intermediate layer and then drop it from the facts and dimensions without ever filtering. Its own CI fixture shows the shape, with two sales-document rows sitting in clients 100 and 200.

Find out what yours holds before you trust a total:

select mandt,
       count(*) as sales_documents
from   vbak
group  by mandt
order  by sales_documents desc;

More than one row means the client filter is now your job. That's the same root cause as the padding, pointed at a different symptom, and Oracle EBS does the identical thing with ORG_ID. The two applications land on opposite failures. EBS gives you a total that's too big. SAP gives you no rows at all.

What each hop keeps and what it drops. The warehouse band is what an agent sees, and both questions it needs answered were settled one band earlier.

What the application did for you

Inside SAP, nobody has ever had to write this rule down, because somebody wrote it down once.

The conversion routine is a single field on a domain. Every data element built on that domain inherits it. Every table column, screen field, report column, and API payload built on that data element inherits it in turn. A developer never calls it. A user never sees the padded form. Two decades of reports agree on what a material number looks like, and not one of them contains the rule that makes it so.

Then replication copies the column. The ABAP Dictionary isn't a column, and it isn't a table a connector can sync, so it stays behind. The estate's single shared definition of what an identifier looks like to a person is exactly the thing that doesn't make the trip, and the warehouse holds the internal form with no note attached saying it's the internal form.

This is the recurring shape of the whole series, and SAP's is the purest version yet. Salesforce put the line-item grain in a report type. Oracle EBS put the organisation boundary in a session variable. SAP put the readable form of every identifier in the type system.

The fix

Nothing here lands as a foreign key, and Fivetran says so: "In rare cases, a view may appear to have a primary key … However, Fivetran automatically discards these primary key constraints." So every relationship has to be declared, and every declaration carries mandt, because a client boundary belongs inside the join rather than in a filter somebody remembers.

In the semantic model

relationships:
  - from_table: vbap
    from_column: vbeln
    to_table: vbak
    to_column: vbeln
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Many items per sales document. Fivetran's own star schema joins these two
      on the document number and nothing else. The match must also include
      mandt, because a header and its items always share a client and declaring
      that keeps the client boundary inside the join. Both columns land as
      character fields holding the padded internal form.
    source: https://github.com/fivetran/dbt_sap/blob/main/models/sales_and_procurement/star_schema/sap__fact_sales_order.sql

  - from_table: vbap
    from_column: matnr
    to_table: mara
    to_column: matnr
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      The material on the order item. Join on the stored form on both sides.
      Fivetran's dimension also exposes material_number, an ltrim of the same
      value, and joining that column to this one returns nothing for every
      padded material.
    source: https://github.com/fivetran/dbt_sap/blob/main/models/sales_and_procurement/star_schema/sap__dim_material.sql

  - from_table: makt
    from_column: matnr
    to_table: mara
    to_column: matnr
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Material descriptions, one row per material per language. makt carries
      mandt, matnr, and spras, so a join that omits spras fans the material
      master out by the number of maintained languages.
    source: https://github.com/fivetran/dbt_sap/blob/main/models/staging/src_sap.yml

Then the part carrying the weight. No column type can say that a character column holds an internal form whose display form differs, so the semantic model has to say it:

entities:
  - name: sap_material
    description: >
      One material in one client. mara.matnr is a character column holding the
      internal form of the material number. SAP declares the conversion to the
      readable form on the domain, under output characteristics, beside output
      length and output style, and separately from format, which is how the
      value is stored. The Dictionary does not replicate, so the warehouse holds
      the internal form and nothing in the schema says so.
    resolves_to:
      table: mara
      key: [mandt, matnr]
    required_dimensions:
      - >
        mandt. Every aggregate either filters it to one client or groups by it.
    forbidden_selectors:
      - >
        Any equality filter on matnr against a value a person typed, unless that
        value has been padded to the column width first. The query is valid and
        returns no rows.
      - >
        Any join between a trimmed identifier column and an untrimmed one. In
        Fivetran's own star schema that is material_number against material_id,
        two columns in the same table, and it returns nothing.
      - >
        LTRIM on the column inside a WHERE clause. It makes the predicate
        unsargable and scans the table. Pad the literal instead.
    caveats:
      - >
        Whether this estate pads at all is a configuration question rather than
        a schema question. Run the discovery query before asserting it.
      - >
        LTRIM(matnr, '0') is lossy for an alphanumeric material number that
        legitimately begins with a zero. Fivetran's package uses it anyway.
    confidence: proposed
    review_state: unreviewed
    source: https://github.com/SAP/abap-file-formats/blob/main/file-formats/doma/doma-v1.json

And the metric, which can't be computed without a client:

name: sap_sales_order_net_value
calculation: >
  Net value of sales order items, within one client. Reads vbap.netwr, joins to
  the header on vbeln and mandt, and cannot be computed without a mandt filter
  or grouping. Any material or customer predicate resolves through the stored
  identifier form, never the display form.
requires_entity: sap_material
source_tables: [vbak, vbap]
primary_table: vbap
required_filters: [mandt]
other_names: [order net value, sales order value, bookings]
bindings:
  HANA: >
    SUM(vbap.netwr)
confidence: proposed
review_state: unreviewed
notes: >
  Not a recognised-revenue figure and not an invoiced figure. Fivetran's star
  schema negates the amount for document categories other than 'c', which is a
  decision about returns and credits that a person makes once.

The pad width is missing from all of that on purpose. The next section finds yours.

Reproduce it yourself

You need an S/4HANA estate replicated into a warehouse and a SQL client. No free instance exists, so this runs on your own data.

1. Ask whether your estate pads at all, and how wide. Run this before you write a single filter or join on an SAP identifier. It's the query this whole post exists to hand over:

-- Does this estate store padded identifiers, and at what width?
select 'mara.matnr' as column_name,
       count(*)                                                    as rows_total,
       min(length(matnr))                                          as min_len,
       max(length(matnr))                                          as max_len,
       sum(case when matnr <> ltrim(matnr, '0') then 1 else 0 end)  as rows_padded
from   mara
union all
select 'kna1.kunnr', count(*), min(length(kunnr)), max(length(kunnr)),
       sum(case when kunnr <> ltrim(kunnr, '0') then 1 else 0 end)
from   kna1
union all
select 'vbak.vbeln', count(*), min(length(vbeln)), max(length(vbeln)),
       sum(case when vbeln <> ltrim(vbeln, '0') then 1 else 0 end)
from   vbak
union all
select 'vbap.posnr', count(*), min(length(posnr)), max(length(posnr)),
       sum(case when posnr <> ltrim(posnr, '0') then 1 else 0 end)
from   vbap;

rows_padded above zero means this estate stores the padded form, and every identifier filter anyone has ever written against it is exposed. max_len is the field width, and it's the number your filters have to pad to. Take it from here rather than from any number you read somewhere, including this page: widths differ between releases and the only one that matters is yours.

2. Count your clients. Run the mandt query from earlier against vbak. More than one row means every total you write needs a client filter or a client grouping, and t000 lists the clients so a person can say which one is production.

3. Rewrite the query that failed. With the width from step one and the client from step two:

select sum(i.netwr) as net_value
from   vbap i
join   vbak h
  on   h.vbeln = i.vbeln
 and   h.mandt = i.mandt
where  i.mandt = '<your production client>'
  and  i.matnr = lpad('4711', <your max_len>, '0');

Pad the literal rather than trimming the column. ltrim(i.matnr, '0') = '4711' returns the same answer and makes the predicate unsargable, so it scans the whole table to get there.

4. Check both halves of every dimension before you join to it. If you use Fivetran's star schema, list its columns and find the pairs:

select table_name,
       column_name,
       data_type
from   information_schema.columns
where  table_name in ('sap__dim_material', 'sap__dim_customer')
order  by table_name, ordinal_position;

Any _number and _id pair on the same table is one identifier in two forms. The _id is the one that joins.

5. Decide what trimming would cost you. ltrim is lossy for an alphanumeric identifier that legitimately starts with a zero, so find out whether you have any:

select count(*) as alphanumeric_materials_starting_with_zero
from   mara
where  matnr like '0%'
  and  ltrim(matnr, '0') <> ''
  and  ltrim(matnr, '0') not similar to '[0-9]+';

Anything above zero means the trimmed column in your warehouse is already wrong for those rows, and trimming is not the fix for your estate.

6. Don't learn this from the test fixtures. fivetran/dbt_sap ships 62 public CSV seeds, one per source table, and they're genuinely useful for checking that a query compiles against the right column names. They will not show you the trap. The material seed holds placeholder values, so the discovery query returns rows_padded = 0 against it and teaches you nothing. One real detail does live in there: the sales item seed stores posnr as 000010, a correctly padded item number, and it's the only genuine instance of the stored form in the whole fixture set.

7. Declare it once. Put the entity, the forbidden selectors, and the client requirement into your semantic model, so the next person who asks what you sold of material 4711 gets an answer without reading this post.

What this looks like in Agami

Everything above holds for whoever builds the semantic model, with or without us. Here's what each piece is in our product, in this post's own terms.

The stored form of an identifier is written down where a query can see it. sap_material records that mara.matnr holds the internal form and that the readable form lives on the domain. That line is the only artifact anywhere in the chain that says so, because SAP kept the answer in the Dictionary and replication couldn't bring it along. A declaration in a file is auditable in a way a prompt never is.

A filter on a material number resolves through the stored form. The entity's forbidden selectors name the three ways to get this wrong: an unpadded equality filter, a join between a trimmed column and an untrimmed one, and LTRIM inside a WHERE clause. A question asked with the number a person says out loud resolves to a predicate written against the value the warehouse holds.

The client requirement travels with the metric. sap_sales_order_net_value carries required_filters: [mandt], so a total can't quietly span production and a test client. That's a property of the metric rather than something each query has to remember.

Fan-out is detected per aggregate before the SQL runs, from declared join cardinality. Join makt to mara without spras and a count inflates by the number of maintained languages. That gets named: which join inflated which number. It isn't blocked, because whether a fan is a bug depends on the question being asked.

Nothing proposed is trusted until a person signs it. Every block above arrives confidence: proposed and review_state: unreviewed. An answer leaning on an unapproved metric still comes back, and it carries a warning until somebody approves it.

Reconciliation is how the number earns trust. Point it at a report somebody already runs inside SAP, as a screenshot, a CSV, or numbers pasted into chat. Inside SAP the conversion routine is applied for you, which makes those reports an unusually good thing to try to agree with.

And what we can't claim. We haven't run this against a real S/4HANA estate, so this page carries no figure for how many estates store padded identifiers, what share of material numbers are numeric, or how much a filter like this costs anyone. We also don't tell you your pad width, and we deliberately haven't printed one: practitioner sources report different widths for different releases, no readable SAP page confirmed either, and step one returns yours. Whether your estate pads at all is a configuration choice we can't see from here.

Frequently asked questions

Why does my SAP material number filter return no rows?

Because the warehouse holds the internal form of the identifier and you filtered on the display form. SAP stores 4711 as a right-aligned, zero-padded character string, and the rule that hides the padding is declared on the data type rather than stored with the value. Pad the literal to the column width instead, and take that width from your own data.

Where does SAP say the leading zeros are a display thing?

In its own published schema for ABAP repository objects. The domain format at doma-v1.json puts conversionRoutine, titled "Conversion Routine", under outputCharacteristics, titled "Output characteristics", beside output length and output style, and separately from format, which carries dataType, length, and decimals.

My warehouse has material_number and material_id. Which one do I join on?

material_id. Fivetran's material dimension projects both, one column apart: ltrim(material.material_id , '0') as material_number and material.material_id. Every join in its own fact model uses material_id, so the trimmed _number column is display output and joins to nothing. The same pattern appears on the customer dimension as customer_number and customer_id.

Should I just LTRIM the column in my queries?

No, for two reasons. It makes the predicate unsargable, so the database scans the table rather than seeking. And it's lossy for an alphanumeric material number that legitimately begins with a zero. Pad the literal with LPAD instead, and check first whether any such identifiers exist in your estate.

Does every SAP estate store padded identifiers?

Not necessarily. Whether padding applies is a configuration question rather than a schema question, so it has to be measured rather than assumed. Compare length(matnr) against length(ltrim(matnr, '0')) across your material master. If no row differs, your estate doesn't pad and this post costs you one query.

How wide is the field?

Take it from max(length(matnr)) on your own data. Widths are reported differently for different SAP releases, this post verified none of those numbers from a readable SAP source, and the only width that will make your filter work is the one in your warehouse.

Does this affect anything besides the material number?

Yes. The same mechanism applies to every numeric identifier SAP stores that way, which is why Fivetran's package strips zeros from the customer number, the sales document number, and the sales document item number as well. Fix it once as a rule about identifiers rather than four times as a rule about columns.

What about MANDT, the client column?

Separate problem, same root cause, and it inflates rather than empties. SAP filters by client at login; the connector replicates every client unless you configure otherwise. Group vbak by mandt to see what yours holds, then put the client in the join rather than in a filter somebody remembers.

References

• SAP/abap-file-formats. SAP's own repository. file-formats/doma/doma-v1.json puts conversionRoutine under outputCharacteristics, separately from format, which is the structural claim this post rests on.
• fivetran/dbt_sap. The schema authority. src_sap.yml declares the landed tables and their descriptions, stg_docs/ carries the column descriptions including netwr, and star_schema/ holds the four ltrim lines, the two-columns-for-one-identifier pattern, and every join written against the padded key.
• Fivetran, SAP ERP on HANA. The connector of record, its two connection modes, the NUMC to STRING type mapping, the MANDT behaviour, and the discarded primary key constraints.
• Fivetran, SAP ERP on HANA setup guide. Prerequisites and privileges for both connection modes.
• Fivetran, SAP data model. What the dbt package builds and which models are extractor reports.
• Fivetran connector index. Lists every SAP connector Fivetran publishes, which is worth reading before concluding one doesn't exist.
• Oracle EBS Secured Every Operating Unit in a Session Variable. Your Warehouse Copied the Table.. The same root cause with the opposite symptom: a boundary the application applied at session level, dropped by replication, every surviving row correct and every total too big.
• In Infor M3, Your Custom Fields Are Rows in a Table Called CUGEX1. Another European ERP whose warehouse shape only makes sense once somebody explains the convention behind it.
• One NetSuite Dollar, Counted Twice, Until the Semantic Model Says Otherwise. The ledger-in-the-key problem, which SAP's ACDOCA has too and this post deliberately left alone.
• agami-core on GitHub

Make your SAP data answerable

Agami is the trust layer between your AI assistant and your warehouse. It records that a material number is stored in its internal form, so a question asked with the number everyone says out loud resolves to the value your warehouse actually holds.

Start a free trial or talk to us →