fivetran / fivetran/dbt_stripe

[Investigation] Possible fanout in `int_stripe__account_daily` when using connected accounts

Open
#80 3 comments 0 reactions 0 assignees View on GitHub
type:investigation
Dominant language
No language data
Stars
61
Forks
40
PR merge metrics
No merged PRs in 30d

Description

### What to investigate

This was discovered from an investigation from error `query exceeded resource limits` in `int_stripe__account_daily`.

Upon review, [this join](https://github.com/fivetran/dbt_stripe/blob/main/models/intermediate/int_stripe__account_daily.sql#L64-L66) does not join on some sort of `account_id` or `connected_account_id`. For typical cases where only one account is in use, this is no issue, however if a user is using connected accounts, this would cause a fanout since the same balance_transaction would be repeated for every account. Also, this fanout could be what is causing resource issues.

Because of internal data limitations, there is uncertainty on how to correctly address this. Ideally we want to review data from a user with connected accounts. (If that's you and would like to help us, please let us know in this thread!)

### Possible Solution
For model `int_stripe__account_daily`, in my initial investigation I thought to update the CTE `daily_account_balance_transactions` with a filter like:
```sql
...
from date_spine
left join balance_transaction
on cast(balance_transaction.date as date) = date_spine.date_day
and balance_transaction.source_relation = date_spine.source_relation
and balance_transaction.connected_account_id =
case when balance_transaction.connected_account_id is not null
then date_spine.account_id
else null end -- necessary for cases where an account is not a connected account. We don't want to erroneously filter transactions out.
group by 1,2,3
```
However the issue is still that we don't have appropriate data to test if this is accurate. Just posting here, so it isn't lost.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by reading models/intermediate/int_stripe__account_daily.sql, focusing on the daily_account_balance_transactions CTE and the join around lines 64–66. Reproduce or inspect data for connected accounts and compare transaction counts across accounts. Done means the fanout is confirmed or ruled out and any change is validated for both connected-account and standalone-account data.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
data-engineering
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.