currencyrate
controlDated transaction exchange rates: one row per base-currency / transaction-currency pair per effective date, with a rate type.
The UI and the browser page label this record "Currency Exchange Rate"; the analytics id is currencyrate. Grain is basecurrency + transactioncurrency + effectivedate, so a point-in-time restatement needs an as-of pick, not a join on date equality.
What the badges mean
- master
- Data class: what the record holds — master data, transaction documents, control/configuration, or a documented convenience record. NetSuite has no product-family or schema axis, so this is the whole classification.
- Subsidiary-scoped
- The record carries a
subsidiarycolumn that partitions its rows, so the generated SQL anchors it. On a few records the column is a multiselect rather than a scalar foreign key — the table notes say which (see the quirks guide). - Location-scoped
- The record carries a
locationcolumn — an org segment in NetSuite before it is a warehouse — and the generated SQL anchors it the same way. - View
- A documented convenience record rather than a stored one. Land the records it stands in for instead of assuming it extracts as-is — the table notes say where the rows actually live.
Structural facts — how the record is partitioned, not a trap by itself
Join & extract hazards — verify before you rely on this
'T'/'F' string.Fields
6 fields · 4 key
6 fields.
| # | Field | Description | Type | Flags |
|---|---|---|---|---|
| 1 | id | Internal ID | NUMBER | Primary-key field |
| 2 | basecurrency | Base currency (internal id) | NUMBER | Primary-key field |
| 3 | transactioncurrency | Transaction currency (internal id) | NUMBER | Primary-key field |
| 4 | effectivedate | Effective date | DATE | Primary-key fieldThe record's primary analysis date — a real DATE/TIMESTAMP column, no conversion needed |
| 5 | exchangerate | Exchange rate | NUMBER | |
| 6 | currencyratetype | Rate type (internal id) | NUMBER |
Field provenance: hand-curated. 4 key fields.
Boilerplate SQL
Starting point for reading the Connect-landed copy of currencyrate on Databricks — dates are real DATE/TIMESTAMP columns and need no conversion, and the partition anchors are already in place. This record carries no modification stamp, and the snippet says so where the watermark would otherwise go. Set your Unity Catalog location, schema, and filter values below; they’re substituted into the SQL and the copy button.
8 parameters not filled: <catalog>, <schema>, <id>, <basecurrency>, <transactioncurrency>, <effectivedate>, <DATE_FROM>, <DATE_TO>
-- ============================================================
-- Table : currencyrate — Dated transaction exchange rates: one row per base-currency / transaction-currency pair per effective date, with a rate type.
-- Purpose: Column-selected read of currencyrate — auto-generated from field metadata
-- Grain : One row per id + basecurrency + transactioncurrency + effectivedate
-- Notes : Auto-generated skeleton for NetSuite data landed in your lakehouse from a SuiteAnalytics Connect (or SuiteQL) extract — it never addresses live NetSuite. Identifiers are lowercase as NetSuite2.com renders them. Select-type columns hold numeric internal ids: BUILTIN.DF() display resolution exists only at extraction time, so decode ids by joining the landed list records; see quirks #display-values. Check-box columns arrive as 'T'/'F' strings; see quirks #tf-booleans.
-- ============================================================
SELECT
t.id AS "Internal ID",
t.basecurrency AS "Base currency (internal id)",
t.transactioncurrency AS "Transaction currency (internal id)",
t.effectivedate AS "Effective date",
t.exchangerate AS "Exchange rate",
t.currencyratetype AS "Rate type (internal id)"
FROM <catalog>.<schema>.currencyrate t
WHERE
1 = 1 -- no partition column on this record; the filters below are optional
-- AND t.id = <id>
-- AND t.basecurrency = <basecurrency>
-- AND t.transactioncurrency = <transactioncurrency>
-- AND t.effectivedate = '<effectivedate>'
-- AND t.effectivedate >= DATE '<DATE_FROM>'
-- AND t.effectivedate <= DATE '<DATE_TO>'
-- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
ORDER BY t.id;Verified August 2026 · Analytics Browser 2021.1 · corroborated 2025.2
Select columns hold internal ids, not display text — see decoding display values.
Relationships
Diagram of 1-hop neighbors — join details below. Document-link edges are highlighted; they chain one document to the next and are the joins newcomers most often get wrong.
More Foundation tables
- departmentThe department segment: one row per department, hierarchical through parent, used to stripe transaction lines for reporting.
- locationThe location master: stocking sites, stores and virtual locations, hierarchical through parent, carrying the inventory-availability, bin and warehouse-management switches that decide how stock behaves at a site.
- subsidiaryThe OneWorld legal-entity master: one row per subsidiary, hierarchical through parent, each carrying its own base currency, country and fiscal calendar.
- accountingbookThe multi-book accounting register: one row per book, with isprimary marking the book most analytics should anchor on.
- accountingperiodThe period calendar: one row per period, hierarchical through parent, with year/quarter/adjustment flags and the close and lock state that governs what can still post.
- classificationThe class segment: one row per class, hierarchical through parent. The analytics id is classification; the NetSuite UI calls it "Class".