Connector Improvement: Quickbooks - Add Reporting Tables to the Source
Answered
The Quickbooks API contains a number of pre-baked reports. Pulling these in would be ideal as this is what our customers actually see in the QBO reporting interface, and re-assembling this data can be difficult to get perfect.
I know there is this dbt transform to make the General Ledger by Period and other views out of the raw data, but the numbers are way off, so we can't use it.
https://github.com/fivetran/dbt_quickbooks_source
Not being an accountant I don't really know the intricate details of whats wrong in the dbt transform, again having the reporting data that is actually published by Quickbooks would be the most efficient here given they provide that API.
I'm happy to stand up a function connector to grab these reports and feed them back into Fivetran (https://fivetran.com/docs/functions), but I would need to get access to the Bearer Token to call the QBO API since I"m using the connect-card to have my customers authenticate. Is there a Fivetran API or way to me access the token, and/or pass it to my custom function connector?
The #1 priority for me is the General Ledger. The others are nice-to-have.
General Ledger:
Balance Sheet:
https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/customerbalance
Profit and Loss, Profit and Loss Detail
https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/profitandloss
https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/profitandlossdetail
-
Official comment
Hi Derek Yimoynes thanks for raising this feature request.
The dbt package models are our recommended method for generating the reports you outlined above. That being said, I notice you mentioned you had already attempted to use the dbt_quickbooks General Ledger by Period model and saw the numbers were way off. This model (in addition to the others) should be complete and accurate. However, if they are not then we would love to get to the bottom of the discrepancy.
Would you be open to meeting with our team to discuss the package models and the discrepancies you are seeing? You can schedule some time with our team using our Office Hours link. Look forward to chatting!
-
I wanted to further offer some input on this solution. I went to support, looking to have FT add one of Quickbook's missing tables (PROFIT_LOSS_DETAIL) to their replication schema list, so my company could get an exact copy in our data warehouse, of what finance is seeing. If there's one thing that's critical for a data team to accomplish, that is to match 100% with a company's source of truth, finance especially.
The dbt package developed by your analytics engineer team is pretty superb and comprehensive--but not flawless, as the OP on this request has observed in the output, as the results differ from what his finance team sees in a canned report such as the General Ledger or the P&L Detail. These are the critical pieces of data we're trying to get for our teams, and without it, we lose our business leadership's confidence, and by extension, we lose confidence in Fivetran's infrastructure. Not good for Fivetran.While I didn't have the same problem--in fact, once I installed the package and ran the repo in dbt, I got a 100% match with what I was hoping to see--I can see OP's concern. As cool as this package is (for those it works for), it's a cookie-cutter solution that won't fit all needs for all customers. While GAAP is a thing, there is lots of wiggle room for staying within the bounds, and there's even a disclaimer with this package to this effect: "This dbt package takes an opinionated stance on how to define the ordering and cash flow types in our model based on best financial practices." Not just ordering cash flows, but there are other nuances in accounting which will make this package not a workable solution for all customers.
Simply providing all base tables available as listed on the Intuit Developer site as options in your replication schema would resolve whatever reporting issues OP and others are trying to overcome. The much-easier of just giving all available tables is a solution which ensures that whatever our business people are seeing in their source system, we're getting identically from Fivetran.
And although as I mentioned above, the dbt solution worked for me, I'd love to just see the GENERAL_LEDGER and PROFIT_LOSS_DETAIL flat tables appear for me in Snowflake, as opposed to having to derive it. It's just way easier for the end user, period.
-
Have to agree with this sentiment; "there are .. nuances in accounting which will make this package not a workable solution for all customers."
I'm in the same boat here - implementing the quickstart I'm seeing significant variation from what the dbt model outputs; not only that but went through the process of configuring my own repo, enabling the credit card module, etc. to no avail.
I'm using the Stitchdata profit_and_loss_report - which still requires pretty heavy transformation after it's in the warehouse.
It's unfortunate that in both cases I don't have a solution that 'just works' - however at this point, it's unclear whether the Fivetran transformation is even workable for us.But it goes back to the original point - why introduce all this obscurity and complexity, when you have the reports accessible and they match what QuickBooks thinks their definition should be?
Please sign in to leave a comment.
Comments
3 comments