BUG - rows in the xero__general_ledger model that have a NULL value in the journal_line_id column

[jazzy-r]

Are you a current Fivetran customer?

Yes - Jared - Samarkand Global - Data Team Lead

Describe the bug

There are instances where there are rows in the xero__general_ledger model that have a NULL value in the journal_line_id column.

This causes the test:

dbt_utils_unique_combination_of_columns_xerogeneral_ledger_journal_line_idsource_relation

to fail because the NULL values are seen to be the same journal_line_id

Expected behavior

I'm not 100%

• Either something is wrong with our data and / or the Fivetran connector because I would expect every journal line to have an ID.
• Or update the test to include the journal_id or journal_number. But this might not work if a single journal has more than one row that have null values in journal_line_id
• Add a "where journal_line_id is not null clause"

Package Version

packages:
  - package: fivetran/xero_source
    version: [">=0.3.0","<0.4.0"]

Warehouse

• [ X] BigQuery
• [ ] Redshift
• [ ] Snowflake
• [ ] Postgres
• [ ] Databricks
• [ ] Other (provide details below)

Please indicate the level of urgency

Low

Are you interested in contributing to this package?

• [ ] Yes, I can do this and open a PR for your review.
• [X] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• [ ] No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[fivetran-joemarkiewicz]

Hi @jazzy-r, thanks for opening this issue!

I would first like to confirm that this is not an error from the connector. Would you be able to confirm within your Xero application that for one of these journal_ids that have null for the journal_line_id, do in fact have no journal_line_id.

Our assumption when developing the package was that the journal_line_id would always be populated and be the true unique identifier for these journal records. t would be great to confirm that assumption with your live data.

[jazzy-r]

Hi @fivetran-joemarkiewicz no problem.

How would I go about verifying that there is a journal_line_id in the xero application?

[fivetran-joemarkiewicz]

I believe if you go to the general_ledger_report within Xero and filter for one of the entries, you should be able to double click on the journal entry and see if there are journal lines that make up the entry.

[jazzy-r]

I've had a quick look and I can't actually find a way to link these rows with test failures to an entry in the general ledger.

It seems this is because many of the column values are in fact NULL.

[fivetran-joemarkiewicz]

Interesting, my understanding is that the journal table would only contain the metadata of the journal record, and then journal_id would contain the monetary value of the entry. I would assume journal would not exist without a journal_line entry?

Would you be able to run the below query and see if you obtain any results? When I run, I see no results. If you do see results, I think we may want to open a support ticket to dig into this behavior in the connector.

with journal as (
    SELECT *
    FROM xero.journal

), journal_line as (
    select *
    from xero.journal_line
)
select 
    journal.journal_id,
    journal_line.journal_line_id,
    journal_line.gross_amount
from journal 
left join journal_line 
    on journal.journal_id = journal_line.journal_id
where journal_line.journal_line_id is null

[jazzy-r]

I ran the above query but changed the FROM clause to match the schema we use:

The tables were stg_xero__journal and stg_xero_journal_line.

It returned rows. Can you confirm that what I did was correct?

[fivetran-joemarkiewicz]

@jazzy-r yeah those changes are fine and wouldn't alter the intention of the query.

I just confirmed with our PM of this connector that it is the intended behavior of Xero that a journal_line entry should be present for each journal entry. Would it be possible for you to confirm internally what these journal entries in fact are and if they should be tied to a journal_line entry? If so, this may be a bigger question for Xero as to why these journals are not matching to their respective journal_lines.

[jazzy-r]

@fivetran-joemarkiewicz I've been discussing this with the finance team. It seems it might be instances when a journal entry was created using 0 amounts and / or when this journal is updated again.

[fivetran-joemarkiewicz]

Thinking through these cases 🤔

• What would be the business reason for 0 journal entry amounts?
• If the journal is updated again (I assume to have > 0 amounts) then I would expect a journal_line. Is that not the case?

[jazzy-r]

My understanding is that the 0 journal entry amounts are for audit trail purposes.

I would always expect a journal_line even for the 0 entries.

It might be that there are two entries 1 at 0 and 1 when it's updated. I'll have to look into this

[fivetran-joemarkiewicz]

Hey @jazzy-r! I just wanted to check in if you had a chance to look into this further?

[jazzy-r]

Hi @fivetran-joemarkiewicz yes but I didn't get any further with it. Not sure what else can be done.

[fivetran-joemarkiewicz]

Thanks for the update @jazzy-r.

I think what will probably be the next best step is to open a ticket with our Support Team as they will have a better idea about what next steps should be taken to look further into this on the Xero side. Unfortunately, I do not have much visibility in this area, but they have helped a lot of other folks with similar questions and I believe they can help us get to the bottom of this question!

[fivetran-joemarkiewicz]

Hi @jazzy-r 👋

I will be closing this issue as I assume the thread has moved to the support channel. Please feel free to re-open this issue if you would like to discuss this further.

Thanks!