[Bug] ERRORS ON TRANSFORMATIONS

[zdrag]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

This pertains to Xero Balance Sheet transformations.

Notice how you use the snapshot financial_year_end_month and financial_year_end_day as the financial year end, so it goes and creates the 29th of February for 2023, 2022, 2021 etc.

Those dates don't exist and create an error:

select 
    case
        when cast(extract(year from current_date) || '-' || financial_year_end_month || '-' || financial_year_end_day as date) >= current_date
        then cast(extract(year from current_date) || '-' || financial_year_end_month || '-' || financial_year_end_day as date)
        else cast(extract(year from {{ dbt.dateadd('year', -1, 'current_date') }}) || '-' || financial_year_end_month || '-' || financial_year_end_day as date)
    end as current_year_end_date,

I've attached the file showing financial_year_end_day and financial_year_end_month as fixed on 02/29.

Relevant error log or model output

23:04:36
Database Error in model xero__balance_sheet_report (models/xero__balance_sheet_report.sql)
100040 (22007): Date '2023-2-29' is not recognized
compiled Code at target/run/xero/models/xero__balance_sheet_report.sql
23:04:36
23:04:36
Done. PASS=17 WARN=0 ERROR=1 SKIP=0 TOTAL=18

Expected behavior

Not to give an error

dbt Project configurations

I don't know how to do this, I'm not a programmer

Package versions

I don't know how to do this, I'm not a programmer

What database are you using dbt with?

snowflake

dbt Version

I don't know how to do this, I'm not a programmer

Additional Context

I'm being sent here by fivetran support, as a business user who does ACCOUNTING, instead of FIvetran handling it. My whole system is offline because of the errors and Fivetran can't give two shits about that.

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[zdrag]

please understand that I don't know coding, and I don't understand why a service that I pay for expects me as the end user to make a github account and speak programming lingo to programmers to get a problem solved that's affecting my business.

I'm stressing my balls off here, for the love of God please help.

[fivetran-catfritz]

Hi @zdrag, as a member of the team responsible for developing our data models, I want to say thanks for bringing this issue to our attention. I understand the frustration and inconvenience it has caused you and your business. It's important for us to address issues like this, so your feedback helps us ensure that everything functions as expected.

Regarding the leap year issue, we've identified a potential fix to calculate the last day of the month instead of relying on the column financial_year_end_day. However, before implementing this fix, we need to confirm internally that this day should always be the last day of the month and not, for example, a mid-month date.

We're actively working on resolving this issue, and I'll keep you updated on our progress.

[fivetran-catfritz]

Hi @zdrag, to update you on our approach, we confirmed that financial_year_end_day can be any day of the month, so we've decided to take a more direct approach. We plan to add logic so if the financial_year_end_day corresponds to the 29th of February, we will use the 28th for the previous year. This should prevent the issue you were seeing.

I wanted to check in with you to see if you have any objections or concerns regarding this solution. If not, we'll proceed with releasing the fix.

[zdrag]

Hi @fivetran-catfritz that's perfect - thank you, should resolve the problem as leap-years are only every 4 years.

Regards

[fivetran-catfritz]

Great, and thank you for taking a look @zdrag. My team's code updates have been approved and released; therefore, I will be closing this Issue. Now it's a matter of the Fivetran Quickstart product being updated with those changes. For this next step, you can follow up with your Support ticket to receive status updates on when this will be live in Quickstart.