fivetran / fivetran/dbt_netsuite
[FEATURE] stg_netsuite2_transactions.status does not provide "display" status
- Dominant language
- No language data
- Stars
- 57
- Forks
- 42
- Avg merge
- 6d 9h
- Merged PRs (30d)
- 2
Description
### Is there an existing issue for this?
- [X] I have searched the existing issues
### Describe the issue
The current package setup returns 'A', 'B', 'C', etc as transactions status (differently from NS1).
NS2 provides a dedicated function to calculate the display status: https://timdietrich.me/blog/netsuite-suiteql-transaction-statuses/
### Relevant error log or model output
_No response_
### Expected behavior
The "display" status should be 'Fully Billed', 'Open', etc
### dbt Project configurations
basic config
### Package versions
0.7.1
### What database are you using dbt with?
snowflake
### dbt Version
1.3.2
### Additional Context
Solution 1: package should foresee the join with stg_netsuite2__transactionstatus on status and type
```
FROM {{ ref('stg_netsuite2__transactions') }} AS transactions
INNER JOIN {{ ref('stg_transaction_status') }} AS status_map
ON transactions.status = status_map.id
AND transactions.transaction_type = status_map.trantype
```
Solution 2: adding the conversion in the _tmp model
Solution 3: a macro to get the value from the transaction status and type:
```
{% macro get_status_display_value(tr_status, tr_type) %}
SELECT name
FROM {{ source('netsuite2', 'transactionstatus') }}
WHERE id = {{ tr_status }}
AND trantype = {{ tr_type }}
{% endmacro %}
```
### Are you willing to open a PR to help address this issue?
- [X] Yes.
- [ ] Yes, but I will need assistance and will schedule time during our [office hours](https://calendly.com/fivetran-solutions-team/fivetran-solutions-team-office-hours) for guidance
- [ ] No.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by inspecting the stg_netsuite2__transactions model and the stg_transaction_status or transactionstatus source mentioned in the issue. Compare the proposed join, temporary-model conversion, and macro approaches, then ensure transaction status output contains display values such as “Fully Billed” and “Open” for the relevant status and transaction type combinations.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- data-engineering, databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100