<!-- Generated by tools/data-dictionary. Edit docs-public/spec or docs-public/fragments instead. -->

# SORACOM Query JP Public Data Dictionary

This dictionary lists the query-facing views available for this coverage.

## Views

| View | Description |
|------|-------------|
| [SIM_STATS](jp/SIM_STATS.md) | SIM usage statistics including data transfer bytes and packet counts. |
| [SIM_STATS_DAILY_SUMMARY](jp/SIM_STATS_DAILY_SUMMARY.md) | Daily SIM usage totals by SIM and speed class. |
| [SIM_STATS_MONTHLY_SUMMARY](jp/SIM_STATS_MONTHLY_SUMMARY.md) | Monthly SIM usage totals by SIM and speed class. |
| [SIM_PEERS](jp/SIM_PEERS.md) | SIM traffic peer summary records with FQDN and IP peer lists. |
| [SIM_SNAPSHOTS](jp/SIM_SNAPSHOTS.md) | Latest SIM attributes and status for the request context. |
| [SIM_SESSION_EVENTS](jp/SIM_SESSION_EVENTS.md) | SIM session lifecycle events such as creation and deletion. |
| [SIM_STATUS_COUNTS](jp/SIM_STATUS_COUNTS.md) | Daily SIM counts grouped by lifecycle status. |
| [SIM_STATUS_HISTORY](jp/SIM_STATUS_HISTORY.md) | SIM status change history and associated SIM attributes. |
| [HARVEST_DATA](jp/HARVEST_DATA.md) | Device-submitted data records available through SORACOM Harvest Data. |
| [HARVEST_FILES](jp/HARVEST_FILES.md) | File metadata available through SORACOM Harvest Files. |
| [TAGS](jp/TAGS.md) | Resource tags for SIMs, devices, groups, and VPGs. |
| [GROUPS](jp/GROUPS.md) | Group configuration records. |
| [BILLING_HISTORY](jp/BILLING_HISTORY.md) | Monthly billing summary records. |
| [BILL_ITEMS](jp/BILL_ITEMS.md) | Per-entity billing line item records. |
| [COUNTRIES](jp/COUNTRIES.md) | Country reference data. |
| [NETWORKS](jp/NETWORKS.md) | Mobile network reference data. |
| [CELL_TOWERS](jp/CELL_TOWERS.md) | Cell tower reference data. |
| [VPG_SIM_DETAILED_STATS](jp/VPG_SIM_DETAILED_STATS.md) | VPG SIM detailed per-flow traffic records. |


# SIM_STATS

SIM usage statistics including data transfer bytes and packet counts.

Use `SIM_STATS` to analyze SIM data usage over the request time range. Query examples should name the columns they need instead of relying on column position.

## Public Contract

- Rows are limited to the current SORACOM Query request context.
- Rows are limited to the time range selected for the SORACOM Query request.
- The request time range is applied to `TIMESTAMP`.

## Data Freshness

Updated multiple times per day.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| TIMESTAMP | TIMESTAMP_NTZ | When the stats were recorded. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| SIM_ID | TEXT | SIM identifier. |
| PRIMARY_IMSI | TEXT | Primary IMSI for multi-IMSI SIMs. |
| IMSI | TEXT | IMSI used for the session. |
| UE_IP_ADDRESS | TEXT | User equipment IP address. |
| VPLMN | TEXT | Visited PLMN. |
| COUNTRY_CODE | TEXT | Country code associated with the visited network. |
| DOWNLINK_BYTES | NUMBER | Total bytes downloaded. |
| DOWNLINK_DROPPED | NUMBER | Dropped downlink bytes. |
| DOWNLINK_PKTS | NUMBER | Total packets downloaded. |
| UPLINK_BYTES | NUMBER | Total bytes uploaded. |
| UPLINK_DROPPED | NUMBER | Dropped uplink bytes. |
| UPLINK_PKTS | NUMBER | Total packets uploaded. |
| VPG | BOOLEAN | Whether the SIM used VPG connectivity. |
| MISC | VARIANT | Additional usage attributes. |

## JSON / VARIANT Fields

`MISC` contains additional attributes that can vary by SIM and record. The object commonly includes timestamps, monthly counters, correction offsets, and optional service usage counters.

For non-VPG rows, common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `created_time` | string | When the related usage record was created, commonly as epoch milliseconds. |
| `last_active_time` | string | Last observed activity time, commonly as epoch milliseconds. |
| `timestamp` | number | Timestamp value in milliseconds. |
| `downlink_bytesYYYYMM` | number | Monthly downlink byte counter for the indicated month, such as `downlink_bytes202405`. |
| `uplink_bytesYYYYMM` | number | Monthly uplink byte counter for the indicated month, such as `uplink_bytes202405`. |
| `downlink_pktsYYYYMM` | number | Monthly downlink packet counter for the indicated month, such as `downlink_pkts202405`. |
| `uplink_pktsYYYYMM` | number | Monthly uplink packet counter for the indicated month, such as `uplink_pkts202405`. |
| `downlink_bytes_correction_diff` | number | Correction offset for downlink bytes. |
| `downlink_pkts_correction_diff` | number | Correction offset for downlink packets. |
| `uplink_bytes_correction_diff` | number | Correction offset for uplink bytes. |
| `uplink_pkts_correction_diff` | number | Correction offset for uplink packets. |
| `imei` | string | Device IMEI when available. |
| `idle_timeout_threshold` | string | Session timeout setting when available. |
| `session_lifetime_limit` | string | Session lifetime limit when available. |
| `funnel_in_http` | number | Optional Funnel inbound HTTP counter. |
| `funnel_out_*` | number | Optional Funnel output counters. |
| `harvest_in_http` | number | Optional Harvest inbound HTTP counter. |
| `harvest_out_plain` | number | Optional Harvest outbound plain counter. |

For VPG rows, common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `created_time` | string | When the related usage record was created, commonly as epoch milliseconds. |
| `last_active_time` | string | Last observed activity time, commonly as epoch milliseconds. |
| `timestamp` | number | Timestamp value in milliseconds. |
| `imei` | string | Device IMEI when available. |
| `version` | string | Optional VPG version identifier, such as `202402`. |

Example:

```sql
SELECT
  SIM_ID,
  MISC:downlink_bytes202501::NUMBER AS jan_2025_downlink
FROM SIM_STATS;
```

Service usage counters are optional and appear only when the corresponding service usage exists in the record. Query optional fields with explicit casts and null checks.

## Common Queries

Total usage per SIM:

```sql
SELECT
  SIM_ID,
  IMSI,
  SUM(DOWNLINK_BYTES) AS total_downlink,
  SUM(UPLINK_BYTES) AS total_uplink
FROM SIM_STATS
GROUP BY SIM_ID, IMSI
ORDER BY total_downlink DESC;
```

Usage by visited network:

```sql
SELECT
  VPLMN,
  SUM(DOWNLINK_BYTES + UPLINK_BYTES) AS total_bytes
FROM SIM_STATS
GROUP BY VPLMN
ORDER BY total_bytes DESC;
```

When a customer refers to a plan in SIM usage questions, use `SUBSCRIPTION` from `SIM_SNAPSHOTS`. Use `SPEED_CLASS` only when the customer specifically says speed plan or speed class.

SIM counts by country and plan for SIMs with recent traffic. Require `SUBSCRIPTION IS NOT NULL` to exclude subtotal or grouping-set rows:

```sql
WITH recent AS (
  SELECT
    st.SIM_ID,
    st.VPLMN,
    ANY_VALUE(ss.SUBSCRIPTION) AS subscription,
    SUM(st.DOWNLINK_BYTES + st.UPLINK_BYTES) AS total_bytes
  FROM SIM_STATS st
  LEFT JOIN SIM_SNAPSHOTS ss
    ON st.SIM_ID = ss.SIM_ID
  WHERE st.TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ)
  GROUP BY st.SIM_ID, st.VPLMN
),
active AS (
  SELECT
    SIM_ID,
    VPLMN,
    subscription,
    total_bytes
  FROM recent
  WHERE total_bytes > 0
),
with_country AS (
  SELECT
    a.SIM_ID,
    a.subscription,
    n.COUNTRYNAME AS country
  FROM active a
  LEFT JOIN NETWORKS n
    ON a.VPLMN = n.MCC || n.MNC
  WHERE a.subscription IS NOT NULL
)
SELECT
  country,
  subscription,
  COUNT(DISTINCT SIM_ID) AS sim_count
FROM with_country
WHERE country IS NOT NULL
GROUP BY country, subscription
ORDER BY country, subscription;
```


# SIM_STATS_DAILY_SUMMARY

Daily SIM usage totals by SIM and speed class.

`SIM_STATS_DAILY_SUMMARY` contains pre-aggregated daily traffic totals per SIM and speed class. Prefer it over raw `SIM_STATS` records whenever you are aggregating daily usage counts or traffic totals and the included dimensions are enough. Those dimensions include SIM, IMSI, operator, date, speed class, and the `MISC` usage attributes; in Global/SNG tables, `SPEED_CLASS` can also carry a three-letter country code. Aggregate across `SPEED_CLASS` when users ask for total daily usage per SIM.

Do not assume `SPEED_CLASS` is only a plan code. In Global/SNG tables, `SPEED_CLASS` can be prefixed with a three-letter uppercase country code and hyphen, such as `USA-plan01s` or `MUS-plan01s`. It can be empty for non-SIM entries, and the country prefix can be omitted when it does not make sense, such as a `planArc01` WireGuard connection. JP entries usually omit the country prefix because JP coverage supports Japan only.

The `MISC` column contains optional service usage counters and VPG usage attributes.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated daily.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| SIM_ID | TEXT | SIM identifier. |
| IMSI | TEXT | IMSI used by the SIM or device identifier for non-SIM devices. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| DATE | TEXT | Usage date in YYYYMMDD format. |
| SPEED_CLASS | TEXT | Speed class for the aggregated usage. Global/SNG values can include a three-letter uppercase country code prefix and hyphen, such as USA-plan01s. |
| DOWNLINK_BYTES | NUMBER | Total bytes downloaded for the day and speed class. |
| UPLINK_BYTES | NUMBER | Total bytes uploaded for the day and speed class. |
| DOWNLINK_PKTS | NUMBER | Total packets downloaded for the day and speed class. |
| UPLINK_PKTS | NUMBER | Total packets uploaded for the day and speed class. |
| MISC | OBJECT | Additional service usage counters and VPG usage attributes. |

## JSON / VARIANT Fields

`MISC` contains optional service usage counters. Fields appear only when the corresponding usage exists.

Common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `beamStatsMap.inHttp.count` | number | Beam inbound HTTP request count. |
| `beamStatsMap.outHttp.count` | number | Beam outbound HTTP request count. |
| `beamStatsMap.inHttps.count` | number | Beam inbound HTTPS request count. |
| `beamStatsMap.outHttps.count` | number | Beam outbound HTTPS request count. |
| `beamStatsMap.inTcp.count` | number | Beam inbound TCP request count. |
| `beamStatsMap.outTcp.count` | number | Beam outbound TCP request count. |
| `beamStatsMap.inUdp.count` | number | Beam inbound UDP request count. |
| `beamStatsMap.outUdp.count` | number | Beam outbound UDP request count. |
| `beamStatsMap.inMqtt.count` | number | Beam inbound MQTT request count. |
| `beamStatsMap.outMqtt.count` | number | Beam outbound MQTT request count. |
| `harvestStatsMap.harvest_inHttp.count` | number | Harvest inbound HTTP request count. |
| `harvestStatsMap.harvest_inTcp.count` | number | Harvest inbound TCP request count. |
| `harvestStatsMap.harvest_inUdp.count` | number | Harvest inbound UDP request count. |
| `harvestStatsMap.harvest_inMqtt.count` | number | Harvest inbound MQTT request count. |
| `harvestStatsMap.harvest_outPlain.count` | number | Harvest outbound plain request count. |
| `funkStatsMap.funk_outAwsLambda.count` | number | Funk AWS Lambda invocation count. |
| `funnelStatsMap.<funnelType>.outputCount` | number | Funnel output count for the funnel type. |
| `inVPGStats` | object | Usage counters for traffic through VPG. |

Example:

```sql
SELECT
  SIM_ID,
  DATE,
  MISC:beamStatsMap.inHttp.count::NUMBER AS beam_in_http
FROM SIM_STATS_DAILY_SUMMARY
WHERE MISC:beamStatsMap IS NOT NULL;
```

## Common Queries

`SIM_STATS_DAILY_SUMMARY` provides daily traffic totals per SIM and `SPEED_CLASS`. Column names match the `SIM_STATS` convention: `DOWNLINK_BYTES`, `UPLINK_BYTES`, `DOWNLINK_PKTS`, and `UPLINK_PKTS`.

Daily traffic by SIM, aggregated across speed classes:

```sql
SELECT
  SIM_ID,
  DATE,
  SUM(DOWNLINK_BYTES) AS total_downlink_bytes,
  SUM(UPLINK_BYTES) AS total_uplink_bytes
FROM SIM_STATS_DAILY_SUMMARY
GROUP BY SIM_ID, DATE
ORDER BY DATE, total_downlink_bytes DESC;
```

Top SIMs for a specific day, aggregated across speed classes:

```sql
SELECT
  SIM_ID,
  IMSI,
  SUM(DOWNLINK_BYTES + UPLINK_BYTES) AS total_bytes
FROM SIM_STATS_DAILY_SUMMARY
WHERE DATE = '20250101'
GROUP BY SIM_ID, IMSI
ORDER BY total_bytes DESC
LIMIT 100;
```


# SIM_STATS_MONTHLY_SUMMARY

Monthly SIM usage totals by SIM and speed class.

`SIM_STATS_MONTHLY_SUMMARY` contains pre-aggregated monthly traffic totals per SIM and speed class. Prefer it over raw `SIM_STATS` records whenever you are aggregating monthly usage counts or traffic totals and the included dimensions are enough. Those dimensions include SIM, IMSI, operator, month, speed class, and the `MISC` usage attributes; in Global/SNG tables, `SPEED_CLASS` can also carry a three-letter country code. Use it for trend analysis and monthly usage ranking. Aggregate across `SPEED_CLASS` when users ask for total monthly usage per SIM.

Do not assume `SPEED_CLASS` is only a plan code. In Global/SNG tables, `SPEED_CLASS` can be prefixed with a three-letter uppercase country code and hyphen, such as `USA-plan01s` or `MUS-plan01s`. It can be empty for non-SIM entries, and the country prefix can be omitted when it does not make sense, such as a `planArc01` WireGuard connection. JP entries usually omit the country prefix because JP coverage supports Japan only.

The `MISC` column contains optional service usage counters and VPG usage attributes.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated monthly.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| SIM_ID | TEXT | SIM identifier. |
| IMSI | TEXT | IMSI used by the SIM or device identifier for non-SIM devices. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| YEAR_MONTH | TEXT | Usage month in YYYYMM format. |
| SPEED_CLASS | TEXT | Speed class for the aggregated usage. Global/SNG values can include a three-letter uppercase country code prefix and hyphen, such as USA-plan01s. |
| DOWNLINK_BYTES | NUMBER | Total bytes downloaded for the month and speed class. |
| UPLINK_BYTES | NUMBER | Total bytes uploaded for the month and speed class. |
| DOWNLINK_PKTS | NUMBER | Total packets downloaded for the month and speed class. |
| UPLINK_PKTS | NUMBER | Total packets uploaded for the month and speed class. |
| MISC | OBJECT | Additional service usage counters and VPG usage attributes. |

## JSON / VARIANT Fields

`MISC` contains optional service usage counters. Fields appear only when the corresponding usage exists.

Common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `beamStatsMap.inHttp.count` | number | Beam inbound HTTP request count. |
| `beamStatsMap.outHttp.count` | number | Beam outbound HTTP request count. |
| `beamStatsMap.inHttps.count` | number | Beam inbound HTTPS request count. |
| `beamStatsMap.outHttps.count` | number | Beam outbound HTTPS request count. |
| `beamStatsMap.inTcp.count` | number | Beam inbound TCP request count. |
| `beamStatsMap.outTcp.count` | number | Beam outbound TCP request count. |
| `beamStatsMap.inUdp.count` | number | Beam inbound UDP request count. |
| `beamStatsMap.outUdp.count` | number | Beam outbound UDP request count. |
| `beamStatsMap.inMqtt.count` | number | Beam inbound MQTT request count. |
| `beamStatsMap.outMqtt.count` | number | Beam outbound MQTT request count. |
| `harvestStatsMap.harvest_inHttp.count` | number | Harvest inbound HTTP request count. |
| `harvestStatsMap.harvest_inTcp.count` | number | Harvest inbound TCP request count. |
| `harvestStatsMap.harvest_inUdp.count` | number | Harvest inbound UDP request count. |
| `harvestStatsMap.harvest_inMqtt.count` | number | Harvest inbound MQTT request count. |
| `harvestStatsMap.harvest_outPlain.count` | number | Harvest outbound plain request count. |
| `funkStatsMap.funk_outAwsLambda.count` | number | Funk AWS Lambda invocation count. |
| `funnelStatsMap.<funnelType>.outputCount` | number | Funnel output count for the funnel type. |
| `inVPGStats` | object | Usage counters for traffic through VPG. |

Example:

```sql
SELECT
  SIM_ID,
  YEAR_MONTH,
  MISC:beamStatsMap.inHttp.count::NUMBER AS beam_in_http
FROM SIM_STATS_MONTHLY_SUMMARY
WHERE MISC:beamStatsMap IS NOT NULL;
```

## Common Queries

`SIM_STATS_MONTHLY_SUMMARY` provides monthly traffic totals per SIM and `SPEED_CLASS`. Column names match the `SIM_STATS` convention: `DOWNLINK_BYTES`, `UPLINK_BYTES`, `DOWNLINK_PKTS`, and `UPLINK_PKTS`.

Monthly traffic trend per SIM, aggregated across speed classes:

```sql
SELECT
  SIM_ID,
  YEAR_MONTH,
  SUM(DOWNLINK_BYTES + UPLINK_BYTES) AS total_bytes
FROM SIM_STATS_MONTHLY_SUMMARY
GROUP BY SIM_ID, YEAR_MONTH
ORDER BY SIM_ID, YEAR_MONTH;
```

Top SIMs for a specific month, aggregated across speed classes:

```sql
SELECT
  SIM_ID,
  IMSI,
  SUM(DOWNLINK_BYTES + UPLINK_BYTES) AS total_bytes
FROM SIM_STATS_MONTHLY_SUMMARY
WHERE YEAR_MONTH = '202501'
GROUP BY SIM_ID, IMSI
ORDER BY total_bytes DESC
LIMIT 100;
```

Beam usage counters:

```sql
SELECT
  SIM_ID,
  YEAR_MONTH,
  MISC:beamStatsMap.inHttp.count::NUMBER AS beam_in_http,
  MISC:beamStatsMap.outHttp.count::NUMBER AS beam_out_http
FROM SIM_STATS_MONTHLY_SUMMARY
WHERE MISC:beamStatsMap IS NOT NULL;
```


# SIM_PEERS

SIM traffic peer summary records with FQDN and IP peer lists.

`SIM_PEERS` summarizes traffic peers observed for SIM activity. Use it to find frequently contacted domains or IP addresses and to compare peer activity by SIM.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated multiple times per day.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| VERSION | TEXT | Record version. |
| IMSI | TEXT | IMSI used by the SIM. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| SIM_ID | TEXT | SIM identifier. |
| CREATED_TIME | TIMESTAMP_NTZ | Record creation time. |
| UE_IP_ADDRESS | TEXT | User equipment IP address. |
| UPLINK_PKTS | NUMBER | Total packets uploaded. |
| UPLINK_BYTES | NUMBER | Total bytes uploaded. |
| DOWNLINK_PKTS | NUMBER | Total packets downloaded. |
| DOWNLINK_BYTES | NUMBER | Total bytes downloaded. |
| LAST_ACTIVE_TIME | TIMESTAMP_NTZ | Last activity time. |
| VPG_ID | TEXT | VPG identifier associated with the traffic when available. |
| TIMESTAMP | TIMESTAMP_NTZ | When the peer summary was recorded. |
| FQDN_PEERS | ARRAY | Domain-name peers observed for the SIM. |
| IP_PEERS | ARRAY | IP address peers observed for the SIM. |

## JSON / VARIANT Fields

`FQDN_PEERS` and `IP_PEERS` are arrays. Flatten them when you need one row per peer value.

Example:

```sql
SELECT
  SIM_ID,
  fqdn.value::STRING AS fqdn_peer
FROM SIM_PEERS,
  LATERAL FLATTEN(INPUT => FQDN_PEERS) fqdn;
```

## Common Queries

Top SIMs by peer traffic:

```sql
SELECT
  SIM_ID,
  IMSI,
  SUM(DOWNLINK_BYTES + UPLINK_BYTES) AS total_bytes
FROM SIM_PEERS
GROUP BY SIM_ID, IMSI
ORDER BY total_bytes DESC
LIMIT 100;
```

Most common domain peers:

```sql
SELECT
  fqdn.value::STRING AS fqdn_peer,
  COUNT(*) AS records
FROM SIM_PEERS,
  LATERAL FLATTEN(INPUT => FQDN_PEERS) fqdn
GROUP BY fqdn_peer
ORDER BY records DESC
LIMIT 100;
```


# SIM_SNAPSHOTS

Latest SIM attributes and status for the request context.

`SIM_SNAPSHOTS` returns the latest available daily snapshot for each SIM in the request context. `SNAPSHOT_DATE` is the snapshot date represented by the rows returned.

When counting or listing SIMs, use `SIM_ID` as the SIM identifier and use `COUNT(DISTINCT SIM_ID)` for counts. A SIM can be associated with multiple IMSI values over time.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

`SIM_SNAPSHOTS` returns the latest daily snapshot of each SIM status as of `SNAPSHOT_DATE`. The view returns only the latest snapshot, so `SNAPSHOT_DATE` is expected to be the same for all rows in a single query result.

For historical trends or state changes over time, use `SIM_SESSION_EVENTS` instead.

A `SIM_ID` identifies the customer-facing SIM container. A SIM can be associated with multiple IMSI values over time, so customer questions such as "how many SIMs?" or "which SIMs?" should use `SIM_ID` as the authoritative SIM identifier. Use `COUNT(DISTINCT SIM_ID)` for SIM counts and key SIM lists by `SIM_ID`.

When a customer refers to a "plan" in SIM-related questions, they usually mean `SUBSCRIPTION`. Interpret "speed plan" as `SPEED_CLASS` only when the customer specifically asks for speed plan or speed class.

## Data Freshness

Updated daily.

Because `SIM_SNAPSHOTS` refreshes daily, results may be up to 24 hours old.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| SNAPSHOT_DATE | DATE | Date of the SIM snapshot. |
| APN | TEXT | Access Point Name configured for the SIM. |
| BUNDLES | ARRAY | Bundle information for the SIM. |
| EXPIRY_ACTION | TEXT | Action configured for SIM expiry. |
| GROUP_ID | TEXT | Group identifier associated with the SIM. |
| ICCID | TEXT | Physical SIM card identifier. |
| IMEI_LOCK | VARIANT | IMEI lock configuration. |
| IMSI | TEXT | IMSI assigned to the SIM. |
| IP_ADDRESS | TEXT | IP address assigned to the current session when available. |
| MODULE_TYPE | TEXT | SIM module type. |
| MSISDN | TEXT | Phone number assigned to the SIM when available. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| PACKET_CAPTURE_SESSIONS | TEXT | Packet capture session information. |
| PLAN | NUMBER | Plan identifier. |
| PREVIOUS_SESSION | VARIANT | Previous session attributes. |
| SERIAL_NUMBER | TEXT | SIM serial number. |
| SESSION_STATUS | VARIANT | Current session attributes. |
| SIM_ID | TEXT | SIM identifier. |
| SPEED_CLASS | TEXT | Connection speed class. |
| STATUS | TEXT | Current SIM status. |
| SUBSCRIPTION | TEXT | Subscription plan code. |
| TAGS | VARIANT | Key-value tags associated with the SIM. |
| TERMINATION_ENABLED | NUMBER | Whether termination is enabled. |
| TYPE | TEXT | SIM type. |
| VERSION | NUMBER | Record version. |
| CREATED_AT | TIMESTAMP_NTZ | When the SIM was created. |
| EXPIRED_AT | TIMESTAMP_NTZ | When the SIM expires or expired. |
| LAST_MODIFIED_AT | TIMESTAMP_NTZ | When SIM attributes were last modified. |
| LAST_PORT_MAPPING_CREATED_TIME | TIMESTAMP_NTZ | When the latest port mapping was created. |
| REGISTERED_TIME | TIMESTAMP_NTZ | When the SIM was registered. |
| RENEWAL_FEE_STATUS_SET_TIME | TIMESTAMP_NTZ | When renewal fee status was set. |

## JSON / VARIANT Fields

`SESSION_STATUS` contains the current session object when available. Common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `cell` | object | Cell information for the current session when available. |
| `dnsServers` | array | DNS servers assigned to the session. |
| `gtpcIpAddress` | string | GTP-C IP address. |
| `gtpcTeid` | number | GTP-C Tunnel Endpoint Identifier. |
| `imei` | string | Device IMEI. |
| `lastUpdatedAt` | string | Last update time for the current session object. |
| `online` | number | Online flag, commonly `1` for online and `0` for offline. |
| `operatorId` | string | Operator identifier associated with the session object. |
| `placement` | string | Placement value for the session. |
| `sessionId` | string | Session identifier. |
| `ueIpAddress` | string | User equipment IP address. |

`PREVIOUS_SESSION` contains the previous session object when available. Common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `cell` | object | Cell information for the previous session when available. |
| `createdTime` | string | Previous session creation time. |
| `deletedTime` | string | Previous session deletion time. |
| `dnsServers` | array | DNS servers assigned to the previous session. |
| `gtpcIpAddress` | string | GTP-C IP address. |
| `gtpcTeid` | number | GTP-C Tunnel Endpoint Identifier. |
| `imei` | string | Device IMEI. |
| `sessionId` | string | Session identifier. |
| `subscription` | string | Subscription plan code for the previous session. |
| `ueIpAddress` | string | User equipment IP address. |

The nested `cell` object commonly includes:

| Field | Type | Description |
|-------|------|-------------|
| `ci` | number | Cell identifier when available. |
| `eci` | number | E-UTRAN Cell Identifier when available. |
| `lac` | number | Location Area Code when available. |
| `mcc` | number | Mobile Country Code; join with `NETWORKS` and `CELL_TOWERS`. |
| `mnc` | number | Mobile Network Code; join with `NETWORKS` and `CELL_TOWERS`. |
| `rac` | number | Routing Area Code when available. |
| `radioType` | string | Radio technology. |
| `sac` | number | Service Area Code when available. |
| `tac` | number | Tracking Area Code when available. |

`IMEI_LOCK` contains the device IMEI lock configuration when set:

```json
{
  "imei": "866667030129919"
}
```

The `imei` value may be a standard IMEI such as `866667030129919`, or an IMEI with an anonymous flag such as `359418742599708|ANONYMOUS`. The `|ANONYMOUS` suffix allows a session to be established even when the IMEI is temporarily not reported.

`TAGS` contains SIM tag key-value pairs for SIM or subscriber resources. The structure is a user-defined JSON object; common keys include `name`, `owner`, `environment`, and `location`.

```json
{
  "name": "Production-Device-001"
}
```

For human-readable names or tag metadata for any supported resource type, use the `TAGS` table.

## Common Queries

Count SIMs by current status. Use `SIM_ID` as the SIM identifier and use `COUNT(DISTINCT SIM_ID)` for SIM counts:

```sql
SELECT
  STATUS,
  COUNT(DISTINCT SIM_ID) AS sim_count
FROM SIM_SNAPSHOTS
GROUP BY STATUS
ORDER BY sim_count DESC;
```

When listing SIMs, key the result by `SIM_ID`. A SIM can have more than one IMSI over time:

```sql
SELECT
  SIM_ID,
  ANY_VALUE(ICCID) AS iccid,
  ANY_VALUE(STATUS) AS status,
  ANY_VALUE(SUBSCRIPTION) AS subscription,
  MAX(LAST_MODIFIED_AT) AS last_modified_at
FROM SIM_SNAPSHOTS
GROUP BY SIM_ID
ORDER BY SIM_ID;
```

When users ask about purchased SIMs, use `CREATED_AT` or `REGISTERED_TIME`; purchasing a SIM does not mean it is activated. Use `STATUS` for the current operational state, such as active, ready, inactive, or suspended:

```sql
SELECT
  SIM_ID,
  ICCID,
  STATUS,
  CREATED_AT,
  REGISTERED_TIME,
  SUBSCRIPTION
FROM SIM_SNAPSHOTS
WHERE CREATED_AT >= '2024-01-01'
  AND CREATED_AT < '2024-02-01'
ORDER BY CREATED_AT;
```

When users ask about renewal fees or renewal charges, use `RENEWAL_FEE_STATUS_SET_TIME`, not `EXPIRED_AT`. `RENEWAL_FEE_STATUS_SET_TIME` is when a SIM was marked as subject to renewal fees. `EXPIRED_AT` is when a SIM expired or will expire based on its expiry action setting. The renewal period varies by customer and is typically 1 or 2 years; if the customer does not know the period, default to 1 year.

```sql
SELECT
  SIM_ID,
  SUBSCRIPTION,
  STATUS,
  RENEWAL_FEE_STATUS_SET_TIME,
  DATEADD(year, 1, RENEWAL_FEE_STATUS_SET_TIME) AS renewal_fee_date
FROM SIM_SNAPSHOTS
WHERE DATEADD(year, 1, RENEWAL_FEE_STATUS_SET_TIME) >= '2026-01-01'
  AND DATEADD(year, 1, RENEWAL_FEE_STATUS_SET_TIME) < '2027-01-01'
ORDER BY renewal_fee_date;
```

Join current session cell information to tower coordinates:

```sql
SELECT
  ss.SIM_ID,
  ss.ICCID,
  ss.SESSION_STATUS:cell:mcc::NUMBER AS mcc,
  ss.SESSION_STATUS:cell:mnc::NUMBER AS net,
  ANY_VALUE(ct.LAT) AS lat,
  ANY_VALUE(ct.LON) AS lon
FROM SIM_SNAPSHOTS ss
JOIN CELL_TOWERS ct
  ON ss.SESSION_STATUS:cell:mcc::NUMBER = ct.MCC
  AND ss.SESSION_STATUS:cell:mnc::NUMBER = ct.NET
WHERE ss.SESSION_STATUS IS NOT NULL
  AND ct.LAT IS NOT NULL
  AND ct.LON IS NOT NULL
GROUP BY
  ss.SIM_ID,
  ss.ICCID,
  ss.SESSION_STATUS:cell:mcc::NUMBER,
  ss.SESSION_STATUS:cell:mnc::NUMBER;
```

When a customer refers to a plan, use `SUBSCRIPTION`. Use `SPEED_CLASS` only when the customer specifically says speed plan or speed class. Marketing plan codes that map to `SUBSCRIPTION` include:

```text
planV1
planX3
plan07
plan01-low_data_volume
planJPK1
planArc01
planAP1
planP1
plan06
planGLK1
planX1
plan05
plan01s-low_data_volume
planP2
planNT1
planX2
plan03
planM1
planX3-EU
plan04
plan01
plan02
planJPKM2
planFX1
plan-NA1-package
plan-US-max
plan01s
plan-US
plan-US-NA
```


# SIM_SESSION_EVENTS

SIM session lifecycle events such as creation and deletion.

## Public Contract

- Rows are limited to the current SORACOM Query request context.
- Rows are limited to the time range selected for the SORACOM Query request.
- The request time range is applied to `EVENT_TIME`.

## Data Freshness

Updated multiple times per day.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| APN | TEXT | Access Point Name used by the session. |
| CREATED_TIME | TIMESTAMP_NTZ | Record creation time. |
| EVENT_TIME | TIMESTAMP_NTZ | When the event occurred. |
| DNS0 | TEXT | Primary DNS server. |
| DNS1 | TEXT | Secondary DNS server. |
| EVENT | TEXT | Event type. |
| HPLMN | TEXT | Home PLMN. |
| ICCID | TEXT | SIM card identifier. |
| IMEI | TEXT | Device IMEI. |
| IMSI | TEXT | IMSI used by the session. |
| LAST_MODIFIED_TIME | TIMESTAMP_NTZ | Last modification time. |
| CELL | OBJECT | Cell tower attributes reported with the event. |
| MSISDN | TEXT | Phone number associated with the SIM when available. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| PLACEMENT | TEXT | Placement information. |
| PRIMARY_IMSI | TEXT | Primary IMSI for multi-IMSI SIMs. |
| SESSION_ID | TEXT | Session identifier. |
| SIM_ID | TEXT | SIM identifier. |
| SPEED_CLASS | TEXT | Connection speed class. |
| SUBSCRIPTION | TEXT | Subscription plan code. |
| TIME | NUMBER | Event time as a Unix timestamp in milliseconds. |
| UE_IP_ADDRESS | TEXT | User equipment IP address. |
| VPLMN | TEXT | Visited PLMN. |

## JSON / VARIANT Fields

`CELL` contains cell information reported with a session event. Common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `mcc` | number | Mobile Country Code; join with `NETWORKS` and `CELL_TOWERS`. |
| `mnc` | number | Mobile Network Code; join with `NETWORKS` and `CELL_TOWERS`. |
| `eci` | number | E-UTRAN Cell Identifier when available. |
| `lac` | number | Location Area Code when available. |
| `rac` | number | Routing Area Code when available. |
| `radioType` | string | Radio technology. |
| `tac` | number | Tracking Area Code when available. |

Use `CELL:mcc` with `NETWORKS.MCC` to retrieve country information. Use `CELL:mcc` and `CELL:mnc` together when network operator or cell tower context is required.

## Common Queries

Recent session event counts by SIM:

```sql
SELECT
  SIM_ID,
  COUNT(*) AS events,
  COUNT_IF(EVENT = 'Created') AS created_events,
  COUNT_IF(EVENT = 'Modified') AS modified_events,
  COUNT_IF(EVENT = 'Deleted') AS deleted_events
FROM SIM_SESSION_EVENTS
GROUP BY SIM_ID
ORDER BY events DESC
LIMIT 100;
```

When users ask for SIM information by country, join `NETWORKS` to retrieve MCC and MNC data by country name. Use `NETWORKS.ORGNAME` when users ask for network operator names. A SIM can have multiple sessions in a requested period, so use `DISTINCT SIM_ID` for SIM activity counts.

SIMs with activity in a country during the request time range:

```sql
SELECT DISTINCT
  se.SIM_ID,
  se.CELL:mcc::STRING AS mcc,
  se.CELL:mnc::STRING AS mnc
FROM SIM_SESSION_EVENTS se
WHERE se.CELL:mcc::STRING IN (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
);
```

Latest country observed for each SIM from recent events. The `mcc_country` CTE deduplicates country rows per MCC to avoid join fan-out:

```sql
WITH recent_events AS (
  SELECT
    SIM_ID,
    IMSI,
    EVENT,
    EVENT_TIME,
    CELL:mcc::STRING AS mcc,
    ROW_NUMBER() OVER (
      PARTITION BY SIM_ID
      ORDER BY EVENT_TIME DESC
    ) AS rn
  FROM SIM_SESSION_EVENTS
  WHERE EVENT_TIME >= DATEADD(hour, -168, CURRENT_TIMESTAMP())
),
latest AS (
  SELECT
    SIM_ID,
    IMSI,
    EVENT AS most_recent_event,
    EVENT_TIME AS most_recent_event_time,
    mcc
  FROM recent_events
  WHERE rn = 1
),
mcc_country AS (
  SELECT
    MCC,
    ANY_VALUE(COUNTRYNAME) AS country
  FROM NETWORKS
  GROUP BY MCC
)
SELECT
  l.SIM_ID,
  l.IMSI,
  t.VALUE AS display_name,
  l.most_recent_event,
  l.most_recent_event_time,
  mc.country
FROM latest l
LEFT JOIN mcc_country mc
  ON mc.MCC = l.mcc
LEFT JOIN TAGS t
  ON l.IMSI = t.RESOURCE_ID
  AND t.RESOURCE_TYPE = 'subscriber'
  AND t.NAME = 'name'
ORDER BY l.most_recent_event_time DESC;
```

A SIM may be online without a recent session event. For "currently online or recently active in a country" questions, combine recent `SIM_SESSION_EVENTS` rows with currently online `SIM_SNAPSHOTS` rows:

```sql
WITH mccs AS (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
),
combined_online_sims AS (
  SELECT SIM_ID
  FROM SIM_SESSION_EVENTS se
  WHERE se.EVENT_TIME >= DATEADD(day, -7, CURRENT_DATE())
    AND se.CELL:mcc::STRING IN (SELECT MCC FROM mccs)

  UNION

  SELECT SIM_ID
  FROM SIM_SNAPSHOTS ss
  WHERE ss.SESSION_STATUS:online = 1
    AND ss.SESSION_STATUS:cell:mcc::STRING IN (SELECT MCC FROM mccs)
)
SELECT COUNT(DISTINCT SIM_ID) AS sim_count
FROM combined_online_sims;
```

Break down recent or currently online SIMs by PLMN:

```sql
WITH mccs AS (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
),
combined_online_sims AS (
  SELECT
    SIM_ID,
    CELL:mcc::STRING AS mcc,
    CELL:mnc::STRING AS mnc
  FROM SIM_SESSION_EVENTS se
  WHERE se.EVENT_TIME >= DATEADD(day, -7, CURRENT_DATE())
    AND CELL:mcc::STRING IN (SELECT MCC FROM mccs)

  UNION

  SELECT
    SIM_ID,
    SESSION_STATUS:cell:mcc::STRING AS mcc,
    SESSION_STATUS:cell:mnc::STRING AS mnc
  FROM SIM_SNAPSHOTS ss
  WHERE ss.SESSION_STATUS:online = 1
    AND ss.SESSION_STATUS:cell:mcc::STRING IN (SELECT MCC FROM mccs)
)
SELECT
  COUNT(DISTINCT SIM_ID) AS sim_count,
  CONCAT(mcc, mnc) AS plmn
FROM combined_online_sims
GROUP BY plmn
ORDER BY sim_count DESC;
```

Filter by network operator name using `NETWORKS.ORGNAME`:

```sql
WITH mccs AS (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
    AND LOWER(ORGNAME) LIKE '%kddi%'
),
combined_online_sims AS (
  SELECT
    SIM_ID,
    CELL:mcc::STRING AS mcc,
    CELL:mnc::STRING AS mnc
  FROM SIM_SESSION_EVENTS se
  WHERE se.EVENT_TIME >= DATEADD(day, -7, CURRENT_DATE())
    AND CELL:mcc::STRING IN (SELECT MCC FROM mccs)

  UNION

  SELECT
    SIM_ID,
    SESSION_STATUS:cell:mcc::STRING AS mcc,
    SESSION_STATUS:cell:mnc::STRING AS mnc
  FROM SIM_SNAPSHOTS ss
  WHERE ss.SESSION_STATUS:online = 1
    AND ss.SESSION_STATUS:cell:mcc::STRING IN (SELECT MCC FROM mccs)
)
SELECT
  COUNT(DISTINCT SIM_ID) AS sim_count,
  CONCAT(mcc, mnc) AS plmn
FROM combined_online_sims
GROUP BY plmn
ORDER BY sim_count DESC;
```


# SIM_STATUS_COUNTS

Daily SIM counts grouped by lifecycle status.

`SIM_STATUS_COUNTS` contains daily SIM inventory counts by lifecycle status. Use it for status distribution dashboards and inventory trend reports.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated daily.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| SNAPSHOT_DATE | DATE | Date the SIM counts were captured. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| STATUS | TEXT | SIM lifecycle status for the aggregated rows. |
| SIM_COUNT | NUMBER | Number of SIMs for the status. |

## Common Queries

Latest SIM counts by status:

```sql
SELECT
  STATUS,
  SUM(SIM_COUNT) AS sim_count
FROM SIM_STATUS_COUNTS
WHERE SNAPSHOT_DATE = (
  SELECT MAX(SNAPSHOT_DATE) FROM SIM_STATUS_COUNTS
)
GROUP BY STATUS
ORDER BY sim_count DESC;
```

Status count trend:

```sql
SELECT
  SNAPSHOT_DATE,
  STATUS,
  SUM(SIM_COUNT) AS sim_count
FROM SIM_STATUS_COUNTS
GROUP BY SNAPSHOT_DATE, STATUS
ORDER BY SNAPSHOT_DATE, STATUS;
```


# SIM_STATUS_HISTORY

SIM status change history and associated SIM attributes.

`SIM_STATUS_HISTORY` contains SIM lifecycle status changes and related SIM attributes. Use it to review status transitions over time.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated daily.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| IMSI | TEXT | IMSI associated with the SIM. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| APPLY_DATETIME | TIMESTAMP_NTZ | When the status change was applied. |
| STATUS | TEXT | SIM lifecycle status after the change. |
| SIM_ID | TEXT | SIM identifier. |
| SIM_ID_SRN | TEXT | SORACOM Resource Name for the SIM identifier. |
| SPEED_CLASS | TEXT | Speed class of the subscription. |
| SUBSCRIPTION | TEXT | Subscription plan code. |
| PRIMARY_IMSI | TEXT | Primary IMSI for multi-IMSI SIMs. |
| IS_PRIMARY | NUMBER | Whether the row represents the primary IMSI. |
| BUNDLES | ARRAY | Bundle codes associated with the SIM. |
| GROUP_ID | TEXT | Group identifier associated with the SIM. |
| SRN | TEXT | SORACOM Resource Name for the SIM. |

## JSON / VARIANT Fields

`BUNDLES` is an array of bundle codes associated with the SIM.

Example:

```sql
SELECT
  SIM_ID,
  bundle.value::STRING AS bundle_code
FROM SIM_STATUS_HISTORY,
  LATERAL FLATTEN(INPUT => BUNDLES) bundle;
```

## Common Queries

Recent status changes:

```sql
SELECT
  SIM_ID,
  IMSI,
  APPLY_DATETIME,
  STATUS
FROM SIM_STATUS_HISTORY
ORDER BY APPLY_DATETIME DESC
LIMIT 100;
```

Status history for a SIM:

```sql
SELECT
  APPLY_DATETIME,
  STATUS,
  SPEED_CLASS,
  SUBSCRIPTION
FROM SIM_STATUS_HISTORY
WHERE SIM_ID = '8981100000000000000'
ORDER BY APPLY_DATETIME;
```


# HARVEST_DATA

Device-submitted data records available through SORACOM Harvest Data.

Use `HARVEST_DATA` to query device-submitted data stored as structured JSON in `CONTENT`.

## Public Contract

- Rows are limited to the current SORACOM Query request context.
- Rows are limited to the time range selected for the SORACOM Query request.
- The request time range is applied to `TIMESTAMP`.

## Data Freshness

Updated near real time.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| RESOURCE_ID | TEXT | Identifier of the resource that submitted the data. |
| FULL_RESOURCE_ID | TEXT | Resource identifier path associated with the record. |
| TIME_WITH_SUFFIX | TEXT | Timestamp key used to distinguish records with the same timestamp. |
| TIME_MS | NUMBER | Timestamp in milliseconds. |
| TIMESTAMP | TIMESTAMP_NTZ | When the data was recorded. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| GROUP_ID | TEXT | Group identifier associated with the resource. |
| CATEGORY | TEXT | Data category. |
| CONTENT_TYPE | TEXT | MIME type of the content. |
| CONTENT | VARIANT | Device-submitted JSON payload. |
| RESOURCE_TYPE | TEXT | Type of resource that submitted the data. |
| SIM_ID | TEXT | SIM identifier associated with the resource when available. |

## JSON / VARIANT Fields

`CONTENT` contains the JSON payload submitted by the device. Field names and value types depend on the data sent by each device or application.

VARIANT field names are case-sensitive. For example, `CONTENT:TMP` and `CONTENT:tmp` read different fields. Cast values to the type you need in query output.

Example:

```sql
SELECT
  RESOURCE_ID,
  TIMESTAMP,
  CONTENT:temperature::NUMBER AS temperature
FROM HARVEST_DATA
WHERE CONTENT:temperature IS NOT NULL;
```

## Common Queries

Recent records for each resource:

```sql
SELECT
  RESOURCE_ID,
  TIMESTAMP,
  CONTENT
FROM HARVEST_DATA
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY RESOURCE_ID
  ORDER BY TIMESTAMP DESC
) = 1;
```

Subscriber-originated records with a numeric payload field:

```sql
SELECT
  SIM_ID,
  RESOURCE_ID,
  TIMESTAMP,
  CONTENT:temperature::NUMBER AS temperature
FROM HARVEST_DATA
WHERE RESOURCE_TYPE = 'subscriber'
  AND CONTENT:temperature IS NOT NULL;
```

Records grouped by category:

```sql
SELECT
  CATEGORY,
  COUNT(*) AS record_count,
  MAX(TIMESTAMP) AS latest_recorded_at
FROM HARVEST_DATA
GROUP BY CATEGORY
ORDER BY latest_recorded_at DESC;
```


# HARVEST_FILES

File metadata available through SORACOM Harvest Files.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated near real time.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| FILENAME | TEXT | Name of the file. |
| DIRECTORY | TEXT | Directory path. |
| FILE_PATH | TEXT | Full path to the file. |
| CONTENT_TYPE | TEXT | MIME type of the file. |
| CONTENT_LENGTH | NUMBER | Size of the file in bytes. |
| DIR | NUMBER | Directory identifier. |
| FILE_ID | TEXT | Unique file identifier. |
| ETAG | TEXT | Entity tag for cache validation. |
| CREATED_TIME | TIMESTAMP_NTZ | When the file was created. |
| LAST_MODIFIED_TIME | TIMESTAMP_NTZ | When the file was last modified. |

## Common Queries

Largest files:

```sql
SELECT
  FILE_PATH,
  CONTENT_TYPE,
  CONTENT_LENGTH,
  LAST_MODIFIED_TIME
FROM HARVEST_FILES
WHERE DIR = 0
ORDER BY CONTENT_LENGTH DESC
LIMIT 100;
```

Recently modified files:

```sql
SELECT
  FILE_PATH,
  CONTENT_TYPE,
  CONTENT_LENGTH,
  LAST_MODIFIED_TIME
FROM HARVEST_FILES
ORDER BY LAST_MODIFIED_TIME DESC
LIMIT 100;
```


# TAGS

Resource tags for SIMs, devices, groups, and VPGs.

Use `TAGS` to look up human-readable names and other tag values for resources. A common tag has `NAME = 'name'`.

`RESOURCE_TYPE` values are lowercase in public examples, such as `subscriber`, `device`, and `group`. When joining subscriber tags to SIM data, subscriber tags use IMSI as `RESOURCE_ID`, not `SIM_ID`.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated as tag data changes.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| RESOURCE_TYPE | TEXT | Type of tagged resource. |
| RESOURCE_ID | TEXT | Unique identifier of the tagged resource. |
| VALUE | TEXT | Tag value. |
| NAME | TEXT | Tag name. |
| RESOURCE_SUMMARY | VARIANT | Additional resource attributes. |
| CREATED_AT | TIMESTAMP_NTZ | When the tag was created. |

## Common Queries

Human-readable names for resources:

```sql
SELECT
  RESOURCE_TYPE,
  RESOURCE_ID,
  VALUE AS display_name
FROM TAGS
WHERE NAME = 'name';
```

When joining `TAGS` with SIM data, subscriber tags use IMSI as `RESOURCE_ID`, not `SIM_ID`. Join `TAGS` to `SIM_SNAPSHOTS` or `SIM_STATS` on `IMSI`, and use lowercase `RESOURCE_TYPE` values such as `subscriber`, `device`, and `group`.

Join SIM snapshots to subscriber tags:

```sql
SELECT
  ss.SIM_ID,
  ss.IMSI,
  t.VALUE AS display_name,
  ss.STATUS
FROM SIM_SNAPSHOTS ss
LEFT JOIN TAGS t
  ON ss.IMSI = t.RESOURCE_ID
  AND t.RESOURCE_TYPE = 'subscriber'
  AND t.NAME = 'name';
```

Join SIM usage to group names:

```sql
SELECT
  ss.GROUP_ID,
  tg.VALUE AS group_name,
  SUM(st.DOWNLINK_BYTES) AS total_downlink_bytes,
  SUM(st.UPLINK_BYTES) AS total_uplink_bytes,
  SUM(st.DOWNLINK_BYTES + st.UPLINK_BYTES) AS total_bytes
FROM SIM_STATS st
JOIN SIM_SNAPSHOTS ss
  ON st.SIM_ID = ss.SIM_ID
LEFT JOIN TAGS tg
  ON ss.GROUP_ID = tg.RESOURCE_ID
  AND tg.RESOURCE_TYPE = 'group'
  AND tg.NAME = 'name'
GROUP BY ss.GROUP_ID, tg.VALUE
ORDER BY total_bytes DESC;
```


# GROUPS

Group configuration records.

`GROUPS` contains the latest available group configuration records for the current SORACOM Query request context. Use it to inspect settings shared by SIMs and devices that belong to the same group.

The `CONFIGURATION` column contains service-specific settings such as SORACOM Beam, Harvest, Funk, and Funnel configuration.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated daily.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| SNAPSHOT_DATE | DATE | Date of the group configuration snapshot. |
| GROUP_ID | TEXT | Group identifier. |
| CONFIGURATION | VARIANT | Group service configuration as JSON. |
| CREATED_AT | TIMESTAMP_NTZ | When the group was created. |
| LAST_MODIFIED_AT | TIMESTAMP_NTZ | When the group was last modified. |

## JSON / VARIANT Fields

`CONFIGURATION` is a JSON object whose fields vary by the services configured on the group. Top-level keys commonly correspond to SORACOM service names.

Common fields include:

| Field | Type | Description |
|-------|------|-------------|
| `SoracomBeam` | object | SORACOM Beam group settings. |
| `SoracomHarvest` | object | SORACOM Harvest group settings. |
| `SoracomFunk` | object | SORACOM Funk group settings. |
| `SoracomFunnel` | object | SORACOM Funnel group settings. |

Example:

```sql
SELECT
  GROUP_ID,
  CONFIGURATION:SoracomBeam AS beam_configuration
FROM GROUPS
WHERE CONFIGURATION:SoracomBeam IS NOT NULL;
```

## Common Queries

Count groups:

```sql
SELECT COUNT(*) AS group_count
FROM GROUPS;
```

Find groups with SORACOM Beam settings:

```sql
SELECT
  GROUP_ID,
  CONFIGURATION:SoracomBeam AS beam_configuration
FROM GROUPS
WHERE CONFIGURATION:SoracomBeam IS NOT NULL;
```

Recently modified groups:

```sql
SELECT
  GROUP_ID,
  LAST_MODIFIED_AT
FROM GROUPS
ORDER BY LAST_MODIFIED_AT DESC
LIMIT 100;
```


# BILLING_HISTORY

Monthly billing summary records.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated as billing records are finalized.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| YEAR_MONTH | TEXT | Year and month in YYYY-MM format. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| AMOUNT | NUMBER | Total charge amount. |
| CURRENCY | TEXT | Currency code for the charge. |
| PAYMENT_ITEM_SUMMARY | VARIANT | Summary of payment items. |
| PAYMENT_TRANSACTION_ID | TEXT | Payment transaction identifiers associated with the billing record. |
| STATE | TEXT | Current billing record state. |

## JSON / VARIANT Fields

`PAYMENT_ITEM_SUMMARY` contains billing item details as JSON. The shape commonly includes:

```json
{
  "billName": "monthlyPayment-202006",
  "categorizedItems": {
    "<itemKey>": {
      "amount": -456.4485,
      "category": "<itemKey>",
      "quantity": 1,
      "taxFreeAmount": 0,
      "unitPrice": 0
    }
  }
}
```

`categorizedItems` is an object keyed by billing item key. Each item key maps to an object with `amount`, `category`, `quantity`, `taxFreeAmount`, and `unitPrice`.

Billing item keys may include plan-specific suffixes such as `::plan01s` or `::plan-NA1-package`. Use `LIKE` with a wildcard when searching for renewal charges or a charge family.

Some billing item key families may include private qualifiers. In the list below, `<PRIVATE_QUALIFIER>` means the qualifier is redacted and is not a literal key value. Query those families by stable prefix, for example `STARTSWITH(item.key, 'ONE_YEAR_CONTRACT_EXTENSION_CHARGE_FOR_')`.

Known billing item keys include:

```text
SORACOM_AIR_BASIC_CHARGE
SORACOM_AIR_DATA_CHARGE
SORACOM_AIR_CUSTOM_DNS_CHARGE
SORACOM_AIR_IN_VPG_CUSTOM_DNS_CHARGE
SORACOM_AIR_CHAP_AUTHENTICATION_CHARGE
SORACOM_AIR_IN_VPG_CHAP_AUTHENTICATION_CHARGE
SORACOM_AIR_SMS_CHARGE
SORACOM_AIR_INTERNATIONAL_SMS_CHARGE
SORACOM_AIR_VOICE_CHARGE
SORACOM_AIR_SUBSCRIBER_SUSPENSION_CHARGE
SORACOM_AIR_SUBSCRIBER_ACTIVATION_CHARGE
SORACOM_AIR_SUBSCRIBER_CONTRACT_RENEWAL_CHARGE
SORACOM_AIR_SUBSCRIBER_TERMINATION_CHARGE
SORACOM_AIR_SUBSCRIPTION_CONTRACT_RENEWAL_CHARGE
SORACOM_AIR_SUBSCRIPTION_DELIVERY_CHARGE
SORACOM_AIR_SUBSCRIPTION_USAGE_CHARGE
ACTIVATED_SIM
ONE_YEAR_CONTRACT_EXTENSION_CHARGE_FOR_<PRIVATE_QUALIFIER>
EXTENSION_CONTRACT_FOR_2YEARS_CONTRACT_CHARGE_FOR_<PRIVATE_QUALIFIER>
VPG_SETUP_CHARGE
VPG_CANAL_SETUP_CHARGE
VPG_DIRECT_SETUP_CHARGE
VPG_DOOR_SETUP_CHARGE
VPG_FIXED_GLOBAL_IPADDRESS_OPTION_CHARGE
SORACOM_AIR_VPG_CHARGE
SORACOM_AIR_INSPECTION_CHARGE
SORACOM_AIR_BASIC_CHARGE_FOR_KM1
SORACOM_AIR_TRAFFIC_CHARGE_FOR_KM1
SORACOM_AIR_SUBSCRIBER_ACTIVATION_CHARGE_FOR_KM1
SORACOM_AIR_CONTRACT_RENEWAL_CHARGE_FOR_KM1
SORACOM_AIR_READY_STATUS_CHARGE_FOR_PLAN_D_WITH_BUNDLE
SORACOM_AIR_SUSPENDED_STATUS_CHARGE_FOR_PLAN_D_WITH_BUNDLE
SORACOM_AIR_BUNDLE_CHARGE_FOR_PLAN_D_WITH_BUNDLE
SORACOM_AIR_ADDITIONAL_DATA_CAPACITY_CHARGE_FOR_PLAN_D_WITH_BUNDLE
SORACOM_AIR_READY_STATUS_CHARGE_FOR_PLAN_K2_WITH_BUNDLE
SORACOM_AIR_SUSPENDED_STATUS_CHARGE_FOR_PLAN_K2_WITH_BUNDLE
SORACOM_AIR_BUNDLE_CHARGE_FOR_PLAN_K2_WITH_BUNDLE
SORACOM_AIR_ADDITIONAL_DATA_CAPACITY_CHARGE_FOR_PLAN_K2_WITH_BUNDLE
SORACOM_AIR_READY_STATUS_CHARGE_FOR_PLAN_DU
SORACOM_AIR_BUNDLE_CHARGE_FOR_PLAN_DU
SORACOM_AIR_ADDITIONAL_DATA_CAPACITY_CHARGE_FOR_PLAN_DU
SORACOM_AIR_BUNDLE_CHARGE_FOR_PLAN_NA1_PACKAGE
SORACOM_AIR_EXCESS_DATA_TAFFIC_CHARGE_FOR_PLAN_NA1_PACKAGE
SORACOM_AIR_BUNDLE_CHARGE_FOR_PLAN_X3
SORACOM_AIR_BUNDLE_CHARGE_FOR_PLAN_X3_EU
SORACOM_AIR_EXCESS_DATA_TRAFFIC_CHARGE_FOR_PLAN_X3_EU
SORACOM_AIR_FIXED_RATE_PLAN_INITIAL_CHARGE
SORACOM_AIR_FIXED_RATE_PLAN_TOP_UP_CHARGE
SORACOM_AIR_BASIC_MONTHLY_CHARGE_FOR_DATA_BUNDLE
SORACOM_AIR_OUT_OF_POOL_DATA_CHARGE_FOR_DATA_BUNDLE
SORACOM_AIR_ESIM_PROFILE_INITIAL_CHARGE
SORACOM_BEAM_REQUEST_CHAGE
SORACOM_BEAM_IN_VPG_REQUEST_CHAGE
SORACOM_CANAL_VPG_CHARGE
SORACOM_CANAL_PEERING_CHARE
SORACOM_DIRECT_VPG_CHARGE
SORACOM_DIRECT_VIF_CHARGE
SORACOM_DOOR_VPG_CHARGE
SORACOM_DOOR_VPNCONNECTION_CHARGE
VPG_TYPE_E_CHARGE
VPG_TYPE_E_SETUP_CHARGE
VPG_TYPE_F_CHARGE
VPG_TYPE_F_SETUP_CHARGE
VPG_TYPE_F2_CHARGE
VPG_TYPE_F2_SETUP_CHARGE
VPG_TYPE_X_CHARGE
VPG_TYPE_X_SUPPORT_CHARGE
VPG_TRANSIT_GATEWAY_ATTACHMENT_CHARGE
VPG_TRANSIT_GATEWAY_VPC_ATTACHMENT_CHARGE
VPG_TRANSIT_GATEWAY_PEERING_CONNECTION_CHARGE
SORACOM_ENDORSE_CHARGE
SORACOM_FUNNEL_REQUEST_CHARGE
SORACOM_FUNNEL_IN_VPG_REQUEST_CHARGE
SORACOM_FUNK_REQUEST_CHARGE
SORACOM_FUNK_IN_VPG_REQUEST_CHARGE
SORACOM_HARVEST_CHARGE
SORACOM_HARVEST_FILES_STORE_CHARGE
SORACOM_HARVEST_FILES_EXPORT_CHARGE
SORACOM_INVENTORY_MONTHLY_CHARGE
SORACOM_INVENTORY_EVENT_CHARGE
SORACOM_INVENTORY_DEVICE_REGISTRATION_CHARGE
SORACOM_JUNCTION_CHARGE
SORACOM_JUNCTION_INSPECTION_CHARGE
SORACOM_KRYPTON_PROVISIONING_CHARGE
SORACOM_LAGOON_MONTHLY_CHARGE
SORACOM_LAGOON_LICENSE_PACK_CHARGE
SORACOM_SMS_DELIVERY_CHARGE
SORACOM_SMS_SUBMIT_CHARGE
USSD_REQUEST_CHARGE
PLAN_KM1_LOCATION_OPTION_CHARGE
PLAN_KM1_LOCATION_OPTION_REQUEST_CHARGE
DATA_MONTHLY_CHARGE
DATA_REQUEST_CHARGE
DEVICE_MANAGEMENT_MONTHLY_CHARGE
LORA_GATEWAY_SHARED_SERVICE_MODEL_MONTHLY_CHARGE
LORA_GATEWAY_OWNED_MODEL_FIRST_UNIT_MONTHLY_CHARGE
LORA_GATEWAY_OWNED_MODEL_ADDITIONAL_UNIT_MONTHLY_CHARGE
SIGFOX_MESSAGE_CHARGE
SIGFOX_DEVICE_REGISTRATION_CHARGE
SIGFOX_ANNUAL_CONNECTIVITY_CHARGE
SORACOM_LTE_M_BUTTON_POWERED_BY_AWS_RENEWAL_CHARGE
SORACOM_HARVEST_TERM_EXTENSION_CHARGE
SORACOM_HARVEST_DATA_EXPORT_CHARGE
SORACOM_NAPTER_CHARGE
SORACOM_NAPTER_AUDIT_LOG_CHARGE
SORACOM_NAPTER_AUDIT_LOG_EXPORT_CHARGE
SORACOM_MOSAIC_MONTHLY_CHARGE
AI_ALGORITHM_USAGE_CHARGE
SORACOM_ORBIT_CHARGE
SORACOM_ORBIT_REQUEST_CHARGE
SORACOM_PEEK_CHARGE
SORACOM_PEEK_DATA_STORE_CHARGE
SORACOM_PEEK_DATA_EXPORT_CHARGE
SORACOM_ARC_VIRTUAL_SIM_INITIAL_CHARGE
SORACOM_ARC_VIRTUAL_SIM_WITH_SIM_MONTHLY_CHARGE
SORACOM_ARC_VIRTUAL_SIM_WITHOUT_SIM_MONTHLY_CHARGE
SORACOM_ARC_VIRTUAL_SIM_DATA_TRAFFIC_CHARGE
API_AUDIT_LOG_CHARGE
API_AUDIT_LOG_ENTERPRISE_OPTION_CHARGE
API_AUDIT_LOG_EXPORT_CHARGE
SIGFOX_CONNECTIVITY_CHARGE
SORACOM_PLATFORM_USAGE_CHARGE
SORACOM_PLATFORM_CONNECTION_POINT_USAGE_CHARGE
MINIMUM_REVENUE_OBLIGATION
VCONNEC_BANDWIDTH_WHOLESALE_CHARGE
MANAGED_VCONNEC_CHARGE
ALADIN_USAGE_CHARGE
CONTRACT_FEE
SUBSCRIBER_CONTRACT_FEE
INITIAL_FEE
MINIMUM_MONTHLY_SERVICE_CHARGE
KDDI_GCP_BASIC_CHARGE
KDDI_GCP_DATA_CHARGE_FOR_10MB_INCLUDED_PLAN
KDDI_GCP_EXCEEDED_DATA_CHARGE_FOR_10MB_INCLUDED_PLAN
SORACOM_NAPTER_FOR_KDDI_GCP_CHARGE
S4_USAGE_CHARGE
SURPLUS_SMART_NOTIFICATION_CHARGE
SORACAM_LICENSE_CHARGE
SORACAM_CELLULAR_PACK_MONTHLY_CHARGE
SORACAM_DATA_EXPORT_CHARGE
SORACAM_DEVICE_ATOM_CAM_UNLIMITED_LIVE_STREAM_CHARGE
SORACAM_DEVICE_STATUS_NOTIFICATION_CHARGE
SORACAM_DEVICE_EVENT_NOTIFICATION_CHARGE
SMS_A2P_MESSAGING_CHARGE
SORACOM_CLOUD_MFA_CHARGE
SORACOM_FLUX_BASIC_MONTHLY_CHARGE
SORACOM_FLUX_EVENT_CHARGE
SORACOM_FLUX_CREDIT_CHARGE
FREETIER_SORACOM_AIR_DATA_TRAFFIC
FREETIER_SORACOM_BEAM_REQUEST
FREETIER_SORACOM_ENDORSE
FREETIER_SORACOM_FUNNEL_REQUEST
FREETIER_SORACOM_FUNK_REQUEST
FREETIER_SORACOM_NAPTER
FREETIER_SORACOM_HARVEST
FREETIER_SORACOM_INVENTORY
FREETIER_SORACOM_ORBIT
FREETIER_SORACOM_ORBIT_REQUEST
FREETIER_SORACOM_PEEK_FOR_SIM
FREETIER_SORACOM_ARC_VIRTUAL_SIM_INITIAL_CHARGE
FREETIER_SORACOM_ARC_VIRTUAL_SIM_WITH_SIM_MONTHLY_CHARGE
FREETIER_SORACOM_ARC_VIRTUAL_SIM_WITHOUT_SIM_MONTHLY_CHARGE
SORACOM_COUPON
PREPAID_AMOUNT_USAGE
UNUSED_BUNDLE_AMOUNT_ADJUSTMENT_FEE
CARRIED_OVER_ADJUSTMENT_AMOUNT
INSUFFICIENT_PREPAID_BALANCE_AMOUNT
DISCOUNT_SORACOM_SERVICES_BY_LORA_GATEWAYS
DISCOUNT_SORACOM_SERVICES_BY_SIGFOX_DEVICES
DISCOUNT_SORACOM_SERVICES_BY_PLAN_DU
DISCOUNT_SORACOM_SORACOM_AIR_PLAN_DU_FREE_READY_STATUS
DISCOUNT_SORACOM_AIR_PLAN_D_FREE_READY_STATUS
DISCOUNT_SORACOM_AIR_PLAN_D_WITH_BUNDLE_FREE_READY_STATUS
DISCOUNT_SORACOM_AIR_PLAN_K2_WITH_BUNDLE_FREE_READY_STATUS
DISCOUNT_SORACOM_AIR_PLAN_X3_EU_FREE_BUNDLE_CHARGE
DISCOUNT_SORACOM_AIR_FREE_READY_STATUS
DISCOUNT_IN_VPG_SORACOM_SERVICE
DISCOUNT_IN_VPG_SORACOM_SERVICE_FOR_NON_TAXABLE_CHARGE
DISCOUNT_SORACOM_MOSAIC_FREE_MONTHLY_CHARGE
VOLUME_DISCOUNT_SORACOM_AIR_BASIC_CHARGE
VOLUME_DISCOUNT_SORACOM_AIR_MONTHLY_FIXED_BASIC_CHARGE
TIERED_VOLUME_DISCOUNT_SORACOM_AIR_BASIC_CHARGE
TIERED_VOLUME_DISCOUNT_SORACOM_AIR_BASIC_MONTHLY_CHARGE
TIERED_VOLUME_DISCOUNT_SORACOM_AIR_BASIC_CHARGE_FOR_KM1
TIERED_VOLUME_DISCOUNT_SORACOM_AIR_DATA_CHARGE
DISCOUNT_SORACOM_AIR_DATA_CHARGE
TIERED_VOLUME_DISCOUNT_SORACOM_INVENTORY_MONTHLY_CHARGE
DISCOUNT_FOR_SORACOM_AIR_DATA_CHARGE_BUNDLED_WITH_BASIC_CHARGE
DISCOUNT_FOR_SORACOM_AIR_DATA_CHARGE_INCLUDED_IN_ACTIVATED_SIM
DISCOUNT_FOR_SORACOM_AIR_BASIC_MONTHLY_CHARGE_FOR_PLAN01S_DATA_BUNDLE_PREPAYMENT
DISCOUNT_SORACOM_SMS_SUBMIT_CHARGE
DISCOUNT_SORACOM_SMS_DELIVERY_CHARGE
```

The known legacy spellings `SORACOM_AIR_EXCESS_DATA_TAFFIC_CHARGE_FOR_PLAN_NA1_PACKAGE`, `SORACOM_BEAM_REQUEST_CHAGE`, `SORACOM_BEAM_IN_VPG_REQUEST_CHAGE`, and `SORACOM_CANAL_PEERING_CHARE` are preserved here because they may appear in billing item data.

## Common Queries

Monthly billing totals:

```sql
SELECT
  YEAR_MONTH,
  CURRENCY,
  SUM(AMOUNT) AS total_amount
FROM BILLING_HISTORY
WHERE STATE = 'closed'
GROUP BY YEAR_MONTH, CURRENCY
ORDER BY YEAR_MONTH;
```

Find renewal-related billing items across plan-specific keys:

```sql
SELECT
  YEAR_MONTH,
  CURRENCY,
  item.key AS billing_item_key,
  item.value:amount::FLOAT AS charge_amount
FROM BILLING_HISTORY,
  LATERAL FLATTEN(input => PAYMENT_ITEM_SUMMARY:categorizedItems) AS item
WHERE STATE = 'closed'
  AND item.key LIKE '%RENEWAL_CHARGE%'
ORDER BY YEAR_MONTH;
```

Use the wildcard match because renewal-related keys can be suffixed by plan, for example `SORACOM_AIR_SUBSCRIBER_CONTRACT_RENEWAL_CHARGE::plan01s` or `SORACOM_AIR_SUBSCRIBER_CONTRACT_RENEWAL_CHARGE::plan-NA1-package`.


# BILL_ITEMS

Per-entity billing line item records.

`BILL_ITEMS` contains billing line items for charged entities such as SIMs, devices, or operator accounts. Each row represents a charge or aggregate billing item for an entity, date, and billing item name.

Use `BILLING_MONTH` for month-level filtering, and use `BILL_ITEM_NAME` to group charges by item type.

## Public Contract

- Rows are limited to the current SORACOM Query request context.

## Data Freshness

Updated as billing item records are finalized.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| SIM_ID | TEXT | SIM identifier resolved when the charged entity is a SIM IMSI, when available. |
| CHARGED_ENTITY | TEXT | Identifier of the entity the charge applies to, such as a SIM IMSI, device ID, operator ID, or other billable entity. |
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| BILLING_MONTH | TEXT | Billing month in YYYY-MM format. |
| DATE | DATE | Date associated with the billing item. |
| BILL_ITEM_NAME | TEXT | Billing item name or charge code. |
| STATE | TEXT | Billing item state. |
| AMOUNT | NUMBER | Billing item charge amount. |
| UNIT_PRICE | NUMBER | Unit price for the billing item when available. |
| QUANTITY | NUMBER | Quantity of units billed when available. |
| CURRENCY | TEXT | Currency code for the billing item. |
| CURRENCY_EXCHANGE | VARIANT | Currency exchange details when available. |
| TAX_FREE_AMOUNT | NUMBER | Tax-free portion of the billing item amount. |
| TAX_CLASS | TEXT | Tax classification for the billing item. |
| DISCOUNT_AMOUNT | NUMBER | Discount amount applied to the billing item when available. |
| DISCOUNT_DESCRIPTION | TEXT | Description of the discount applied when available. |
| UPDATE_DATE_TIME | TIMESTAMP_NTZ | When the billing item was last updated. |

## JSON / VARIANT Fields

`CURRENCY_EXCHANGE` contains currency exchange details when an exchange value is associated with the billing item. The exact fields can vary by record.

Query optional fields with explicit casts and null checks.

Example:

```sql
SELECT
  SIM_ID,
  BILLING_MONTH,
  BILL_ITEM_NAME,
  CURRENCY_EXCHANGE
FROM BILL_ITEMS
WHERE CURRENCY_EXCHANGE IS NOT NULL;
```

## Common Queries

Monthly total by billing item:

```sql
SELECT
  BILLING_MONTH,
  BILL_ITEM_NAME,
  CURRENCY,
  SUM(AMOUNT) AS total_amount
FROM BILL_ITEMS
GROUP BY BILLING_MONTH, BILL_ITEM_NAME, CURRENCY
ORDER BY BILLING_MONTH, total_amount DESC;
```

Charges for a charged entity across billing months:

```sql
SELECT
  BILLING_MONTH,
  DATE,
  BILL_ITEM_NAME,
  AMOUNT,
  CURRENCY
FROM BILL_ITEMS
WHERE CHARGED_ENTITY = '440000000000001'
  AND BILLING_MONTH BETWEEN '2025-01' AND '2025-03'
ORDER BY DATE, BILL_ITEM_NAME;
```

Discounted billing items:

```sql
SELECT
  BILLING_MONTH,
  SIM_ID,
  BILL_ITEM_NAME,
  DISCOUNT_AMOUNT,
  DISCOUNT_DESCRIPTION
FROM BILL_ITEMS
WHERE DISCOUNT_AMOUNT IS NOT NULL
ORDER BY BILLING_MONTH, SIM_ID;
```


# COUNTRIES

Country reference data.

## Data Freshness

Updated periodically.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| CCA2 | TEXT | ISO 3166-1 alpha-2 country code. |
| CCA3 | TEXT | ISO 3166-1 alpha-3 country code. |
| COUNTRY | TEXT | Country name. |
| LAT | FLOAT | Country centroid latitude. |
| LON | FLOAT | Country centroid longitude. |

## Common Queries

Find countries by code or name:

```sql
SELECT
  CCA2,
  CCA3,
  COUNTRY,
  LAT,
  LON
FROM COUNTRIES
WHERE CCA2 = 'JP'
   OR CCA3 = 'JPN'
   OR COUNTRY ILIKE '%Japan%';
```


# NETWORKS

Mobile network reference data.

## Data Freshness

Updated periodically.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| MCC | TEXT | Mobile Country Code. |
| MNC | TEXT | Mobile Network Code. |
| TADIG | TEXT | TADIG code for roaming. |
| ALPHA3 | TEXT | ISO 3166-1 alpha-3 country code. |
| ALPHA2 | TEXT | ISO 3166-1 alpha-2 country code. |
| COUNTRYNAME | TEXT | Country name. |
| ORGNAME | TEXT | Network operator or carrier name. |
| SRCFILE | TEXT | Reference code for the network data source. |

## Common Queries

Use `NETWORKS` to translate mobile country and network codes into country and operator information. Country lookups should generally filter `COUNTRYNAME`; network operator lookups should generally filter `ORGNAME`.

Find mobile network codes for a country:

```sql
SELECT
  MCC,
  MNC,
  ORGNAME
FROM NETWORKS
WHERE COUNTRYNAME = 'Japan'
ORDER BY MCC, MNC;
```

Join paths:

- `SIM_STATS.VPLMN` can join to `NETWORKS` by concatenating `MCC || MNC`.
- `SIM_SESSION_EVENTS.CELL:mcc` and `SIM_SESSION_EVENTS.CELL:mnc` can join to `NETWORKS`.
- `SIM_SNAPSHOTS.SESSION_STATUS:cell:mcc` and `SIM_SNAPSHOTS.SESSION_STATUS:cell:mnc` can join to `NETWORKS`.

SIM counts by country and plan for SIMs with recent traffic. Require `SUBSCRIPTION IS NOT NULL` to exclude subtotal or grouping-set rows:

```sql
WITH recent AS (
  SELECT
    st.SIM_ID,
    st.VPLMN,
    ANY_VALUE(ss.SUBSCRIPTION) AS subscription,
    SUM(st.DOWNLINK_BYTES + st.UPLINK_BYTES) AS total_bytes
  FROM SIM_STATS st
  LEFT JOIN SIM_SNAPSHOTS ss
    ON st.SIM_ID = ss.SIM_ID
  WHERE st.TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ)
  GROUP BY st.SIM_ID, st.VPLMN
),
active AS (
  SELECT
    SIM_ID,
    VPLMN,
    subscription
  FROM recent
  WHERE total_bytes > 0
),
with_country AS (
  SELECT
    a.SIM_ID,
    a.subscription,
    n.COUNTRYNAME AS country
  FROM active a
  LEFT JOIN NETWORKS n
    ON a.VPLMN = n.MCC || n.MNC
  WHERE a.subscription IS NOT NULL
)
SELECT
  country,
  subscription,
  COUNT(DISTINCT SIM_ID) AS sim_count
FROM with_country
WHERE country IS NOT NULL
GROUP BY country, subscription
ORDER BY country, subscription;
```


# CELL_TOWERS

Cell tower reference data.

## Data Freshness

Updated periodically.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| RADIO | TEXT | Radio technology. |
| MCC | NUMBER | Mobile Country Code. |
| NET | NUMBER | Mobile Network Code. |
| AREA | NUMBER | Location Area Code or Tracking Area Code. |
| CELL | NUMBER | Cell identifier. |
| UNIT | NUMBER | Unit identifier. |
| LON | NUMBER | Cell tower longitude. |
| LAT | NUMBER | Cell tower latitude. |
| RANGE | NUMBER | Estimated coverage range in meters. |
| SAMPLES | NUMBER | Number of measurements used for the position. |
| EXACT | NUMBER | Exact location indicator. |
| CREATED | NUMBER | Creation time as a Unix timestamp. |
| UPDATED | NUMBER | Last update time as a Unix timestamp. |
| AVG_STRENGTH | NUMBER | Average signal strength. |

## Common Queries

Look up approximate tower coordinates for current SIM session cells:

```sql
SELECT
  ss.SIM_ID,
  ss.SESSION_STATUS:cell:mcc::NUMBER AS mcc,
  ss.SESSION_STATUS:cell:mnc::NUMBER AS net,
  ss.SESSION_STATUS:cell:tac::NUMBER AS area,
  ANY_VALUE(ct.LAT) AS lat,
  ANY_VALUE(ct.LON) AS lon
FROM SIM_SNAPSHOTS ss
JOIN CELL_TOWERS ct
  ON ss.SESSION_STATUS:cell:mcc::NUMBER = ct.MCC
  AND ss.SESSION_STATUS:cell:mnc::NUMBER = ct.NET
WHERE ss.SESSION_STATUS IS NOT NULL
GROUP BY
  ss.SIM_ID,
  ss.SESSION_STATUS:cell:mcc::NUMBER,
  ss.SESSION_STATUS:cell:mnc::NUMBER,
  ss.SESSION_STATUS:cell:tac::NUMBER;
```


# VPG_SIM_DETAILED_STATS

VPG SIM detailed per-flow traffic records.

`VPG_SIM_DETAILED_STATS` contains SIM traffic records that are output when the Detailed Stats feature of a Virtual Private Gateway (VPG) is enabled. Each row is a single traffic record; use aggregate functions such as `SUM()` with `GROUP BY` for traffic totals.

## Public Contract

- Rows are limited to the current SORACOM Query request context.
- Rows are limited to the time range selected for the SORACOM Query request.
- The request time range is applied to `TIMESTAMP`.

## Data Freshness

Updated multiple times per day.

## Columns

| Column | Type | Description |
|--------|------|-------------|
| OPERATOR_ID | TEXT | Operator identifier associated with the row. |
| IMSI | TEXT | IMSI used by the SIM. |
| IMEI | TEXT | Device IMEI. |
| MSISDN | TEXT | Phone number associated with the SIM when available. |
| PRIMARY_IMSI | TEXT | Primary IMSI for multi-IMSI SIMs. |
| SIM_ID | TEXT | SIM identifier. |
| GROUP_ID | TEXT | Group identifier associated with the SIM. |
| UE_IP_ADDRESS | TEXT | User equipment IP address. |
| GTPC_IP_ADDRESS | TEXT | GTP-C IP address. |
| GTPC_TEID | NUMBER | GTP-C Tunnel Endpoint Identifier. |
| SESSION_ID | TEXT | Session identifier. |
| MCC | NUMBER | Mobile Country Code. |
| MNC | NUMBER | Mobile Network Code. |
| TAC | NUMBER | Tracking Area Code. |
| ECI | NUMBER | E-UTRAN Cell Identifier. |
| CREATED_TIME | TIMESTAMP_NTZ | Record creation time. |
| TIMESTAMP | TIMESTAMP_NTZ | When the traffic record was recorded. |
| LAST_ACTIVE_TIME | TIMESTAMP_NTZ | Last activity time. |
| APN | TEXT | Access Point Name. |
| RATING_GROUP | TEXT | Rating group for billing. |
| VPLMN | TEXT | Visited PLMN. |
| COUNTRY_CODE | TEXT | Country code. |
| FQDN | TEXT | Domain name associated with the traffic record when available. |
| DEST_IP_ADDRESS | TEXT | Destination IP address. |
| DEST_PORT | NUMBER | Destination port. |
| PROTOCOL | TEXT | Protocol used. |
| DOWNLINK_BYTES | NUMBER | Bytes downloaded in this period. |
| DOWNLINK_PKTS | NUMBER | Packets downloaded in this period. |
| UPLINK_BYTES | NUMBER | Bytes uploaded in this period. |
| UPLINK_PKTS | NUMBER | Packets uploaded in this period. |
| DUPLICATED_FQDN | TEXT | Duplicate domain name indicator. |

## Common Queries

Traffic totals for selected IMSIs:

```sql
SELECT
  IMSI,
  SUM(DOWNLINK_BYTES) AS total_downlink_bytes,
  SUM(DOWNLINK_PKTS) AS total_downlink_packets,
  SUM(UPLINK_BYTES) AS total_uplink_bytes,
  SUM(UPLINK_PKTS) AS total_uplink_packets
FROM VPG_SIM_DETAILED_STATS
WHERE IMSI IN ('440000000000001', '440000000000002')
GROUP BY IMSI
ORDER BY IMSI;
```

Top SIMs by VPG traffic:

```sql
SELECT
  SIM_ID,
  IMSI,
  COUNT(*) AS record_count,
  SUM(DOWNLINK_BYTES) AS total_downlink_bytes,
  SUM(UPLINK_BYTES) AS total_uplink_bytes
FROM VPG_SIM_DETAILED_STATS
GROUP BY SIM_ID, IMSI
ORDER BY total_downlink_bytes + total_uplink_bytes DESC
LIMIT 100;
```