Checking access…

Dataset Specification
Storage sizing & schema guide
Built on Google Cloud
Data Platform · Technical Specification

Dataset Specification
& Storage Sizing Guide

Field-level definitions, data relationships, and storage size calculations across three granularities — raw timestamped, 5-minute aggregation, and 15-minute aggregation — for four core operator data domains.

Web Traffic (Daily) Footfall Location (Daily) Demographics (Monthly) Reference Master Tables (Monthly) GCP · Single Region US

Classification: Internal — Restricted

Prepared for: Operator Infrastructure & Data Engineering

Pricing basis: GCP Cloud Storage · $0.020/GB/mo (Standard) · $0.0012/GB/mo (Archive)

Source data: Minimal_Kb_File_Size_v2.xlsx (actual field structure & byte measurements)

Date: July 2026

Contents

  1. Dataset Overview & Relationships
  2. Web Traffic — Daily Dataset
  3. Footfall Location — Daily Dataset
  4. Demographics — Monthly Dataset
  5. Reference Master Tables
  6. Entity Relationship Schematic
  7. Storage Size Calculations
  8. Aggregation Size Comparison
Section 01

Dataset Overview & Relationships

The operator's data platform ingests four distinct dataset families. Two are generated daily at high velocity (web traffic and footfall location), two are updated monthly (demographics and reference master tables). Together they form a unified analytical layer linking subscriber behaviour, physical movement, identity attributes, and network topology.

ℹ️
The file Minimal_Kb_File_Size_v2.xlsx provides the actual field schema from three sheets: Online (web traffic), Location (footfall), and Demographic. Byte-per-row measurements from this file form the empirical basis for all size calculations in Section 7. The Sheet1 metadata confirms a raw payload of 117,602 bytes across 2,419 sample rows — approximately 49 bytes/row in compressed columnar format.
Web Traffic
4 TB
per day · ~50M sessions
Footfall Location
10 TB
per day · ~200M pings
Demographics
~50 GB
per month · ~60M subscribers
Reference Tables
~5 GB
per month · cell sites + ZIP codes
Section 02

Web Traffic — Daily Dataset

Captures every subscriber browsing session routed through the operator's network. Each row represents one TCP session: a unique pairing of a subscriber token, a cell site, a destination host, and the data volumes transferred. Source: Online sheet from Minimal_Kb_File_Size_v2.xlsx.

🌐

web_traffic_daily

table: vz_web.web_traffic_daily · partition: event_date · format: Parquet

Daily 4 TB/day raw ~50M rows/day

Each record captures one web session event for a unique subscriber-cell-host combination. Sessions are identified by a hashed TOKEN_ID that maps back to the Demographic table via MSISDN hash. The CELL_ID field is the primary join key to the Reference Cell Site table. Download and upload volumes are in bytes; session duration is in seconds.

Primary Key & Identifiers

Field NameData TypeSize (bytes)KeyDefinitionExample Value
TOKEN_ID VARCHAR(64) 64 PK Privacy-preserving hashed subscriber identifier. SHA-256 of MSISDN + salt. Consistent across sessions for the same device. Maps to Demographic.Hashed_MSISDN. 454030lsRNu1wVl…cSgmso=
START_VISIT_DATE_TIME TIMESTAMP WITH TZ 8 PK Session start timestamp in UTC with timezone offset. Precision to the minute. Used as partition basis (event_date extracted from this field). Buckets to 5-min and 15-min windows in aggregated tables. 2026-02-06 11:30:00+00:00
CELL_ID VARCHAR(20) 20 FK Unique identifier for the serving radio cell. Encodes site code and sector. Format: {site_code}-{year}. Foreign key to ref_cell_site.cell_id. Derives geographic location without storing raw lat/lon at this table level. 928A601-2022

Network & Timing Fields

Field NameData TypeSize (bytes)KeyDefinitionExample Value
VALID_FROM_CELL TIMESTAMP 8 IX Timestamp from which the CELL_ID assignment became valid. Used to resolve cell site changes in historical joins. When joining to ref_cell_site, filter VALID_FROM_CELL BETWEEN valid_from AND valid_to for point-in-time accuracy. 1900-01-01 00:00:00
SESSION_DURATION INT (seconds) 4 — Total duration of the TCP session in seconds. Includes both active data transfer and idle keep-alive time. Sessions under 5 seconds are typically DNS lookups or pre-fetch traffic. Aggregated as SUM and AVG in rolled-up tables. 90

Traffic Volume Fields

Field NameData TypeSize (bytes)KeyDefinitionExample Value
HOST_NAME VARCHAR(128) varies ~30 IX Destination hostname of the session (SNI or reverse DNS). Used for domain categorisation, content classification, and streaming vs. browsing split. High cardinality — typically 500K+ unique domains per day. Stored as dictionary-encoded in Parquet for compression. 119.29.29.88
DOWNLOAD_VOLUME BIGINT (bytes) 8 — Total bytes transferred from server to device (downlink) for this session. Aggregated as SUM at 5-min and 15-min windows. Divide by 1,048,576 for MB. Primary metric for network capacity planning. Range: 0 to ~50MB per session typical. 2,407
UPLOAD_VOLUME BIGINT (bytes) 8 — Total bytes transferred from device to server (uplink) for this session. Typically 10–30% of download volume for standard browsing; higher for video upload or cloud sync workloads. Aggregated as SUM in rolled-up tables. 1,383
Hit INT 4 — Number of times the subscriber clicked on or requested the subdomain / SNI (Server Name Indication) hostname within this session. A value of 1 means a single page load or DNS hit; values above 10 indicate active browsing or app polling (e.g. social media feeds, streaming buffering). 0 means the session was established but no SNI interaction was recorded (e.g. background sync). NULL indicates the session predates Hit tracking or the cell node did not support DPI at time of capture. 0 / 3 / 12 / 47
Sample data — web_traffic_daily (3 rows from source file)
TOKEN_IDSTART_VISIT_DATE_TIMECELL_IDHOST_NAMEDOWNLOAD_VOLUMEUPLOAD_VOLUMESESSION_DURATIONHit?
454030lsRNu1…Sgmso=2026-02-06 11:30:00+00:00928A601-2022netflix.com2,4071,3839047
454030lsRNu1…Sgmso=2026-02-06 15:05:00+00:00927C502-2022instagram.com1,9071,3191712
454030lsRNu1…Sgmso=2026-02-06 15:20:00+00:00927C502-2022cdn.google.com2,1191,383163
7B9Qm2kPxW…Tc4R=2026-02-06 09:14:00+00:00924A301-2023apple.com5,8418922101
7B9Qm2kPxW…Tc4R=2026-02-06 09:22:00+00:00924A301-2023icloud-content.com48,230312440
R3nPvXtZwK…Hq9L=2026-02-06 18:45:00+00:00931B104-2021youtube.com91,0472,1043808
Section 03

Footfall Location — Daily Dataset

Captures subscriber movement as a sequence of geo-timestamped pings. Each row represents one location event: a transition between start and end coordinates within a time window. Source: Location sheet from Minimal_Kb_File_Size_v2.xlsx (530 sample rows, single device).

📍

footfall_location_daily

table: vz_location.footfall_daily · partition: event_date · format: Parquet + delta-encoded coordinates

Daily 10 TB/day raw ~200M pings/day

Each record is a location ping for a single masked subscriber. The subscriber identifier uses the same TOKEN_ID (hashed MSISDN) format as the Web Traffic table — enabling a direct join between browsing behaviour and physical movement without any raw PII exposure. Pings are generated when the device transitions between cell coverage areas or at a minimum heartbeat interval (~45–90 seconds for active devices). Each row also carries a cell_site_id which maps directly to the Reference Cell Site table, giving network-based location as a complement to GPS coordinates. The operator's ~60M subscriber base generates approximately 200–300M location pings per day.

Identifier & Time Fields

Field NameData TypeSize (bytes)KeyDefinitionExample Value
TOKEN_ID VARCHAR(64) 64 PK Privacy-preserving hashed subscriber identifier — identical token format as Web Traffic. SHA-256 of the subscriber's MSISDN (masked MSISDN / hashed phone number) + rotating daily salt. This shared identifier is the primary cross-domain join key: web_traffic_daily.TOKEN_ID = footfall_location_daily.TOKEN_ID enables direct behavioural + movement enrichment with no PII traversal. Consistent per device within a calendar day; re-salted at midnight UTC. 454030lsRNu1wVl…Sgmso=
start_time TIMESTAMP WITH TZ 8 PK Timestamp when the device entered this geographic position, in UTC. Sub-second precision captured but stored to millisecond. Forms the lower bound of the dwell window. Used for 5-min and 15-min bucket assignment in aggregated tables. 2025-02-05 00:01:24+00:00
end_time TIMESTAMP WITH TZ 8 — Timestamp when the device left this geographic position. The difference end_time - start_time gives dwell duration. For instantaneous pings (same second), start_time equals end_time — these represent network-forced location updates rather than movement events. 2025-02-05 00:01:28+00:00

Geographic Fields

Field NameData TypeSize (bytes)KeyDefinitionExample Value
start_latitude DOUBLE (float64) 8 — WGS-84 latitude of the device position at start_time, in decimal degrees. Precision to 15 significant figures (~1mm accuracy). In aggregated tables this is replaced by geohash or H3 grid cell index. Range: -90 to +90. Observed values are in Saudi Arabia (21.4°N, 39.9°E) region. 21.457580566400001
start_longitude DOUBLE (float64) 8 — WGS-84 longitude of the device position at start_time, in decimal degrees. Precision to 15 significant figures. Range: -180 to +180. Stored separately from latitude for columnar compression efficiency; in Parquet, delta encoding compresses consecutive nearby coordinates by 4–8×. 39.894401550300003
end_latitude DOUBLE (float64) 8 — WGS-84 latitude of the device position at end_time. For stationary pings, equals start_latitude exactly. For movement events, the difference (end_lat - start_lat, end_lon - start_lon) encodes the displacement vector. Dropped in 5-min aggregation in favour of centroid lat/lon. 21.457580566400001
end_longitude DOUBLE (float64) 8 — WGS-84 longitude of the device position at end_time. For stationary pings, equals start_longitude exactly. High repetition ratio for urban dwellers (observed: same coordinates repeated 30+ consecutive times). Parquet dictionary encoding compresses repeated values to 2–3 bytes. 39.894401550300003
cell_site_id VARCHAR(20) 20 FK The radio cell tower serving the device at the time of the ping. Foreign key to ref_cell_site.cell_id. Provides a network-topology-based geographic representation that complements GPS coordinates — essential for cell-level footfall density analytics and coverage capacity planning. Every ping from a device on the operator network carries a cell_site_id; NULL only for Wi-Fi calling or IP-only sessions where no radio cell is assigned. Same format as web_traffic_daily.CELL_ID — enabling cell-level join across both datasets. 928A601-2022
Sample data — footfall_location_daily (6 rows · multiple devices · with cell_site_id)
TOKEN_IDstart_timeend_timestart_latstart_lonend_latend_loncell_site_id
454030lsRNu1…Sgmso=2026-02-05 00:01:24+00:002026-02-05 00:01:28+00:0021.4575839.8944021.4575839.89440928A601-2022
454030lsRNu1…Sgmso=2026-02-05 00:02:15+00:002026-02-05 00:02:22+00:0021.4563239.8966421.4563239.89664928A601-2022
454030lsRNu1…Sgmso=2026-02-05 00:06:11+00:002026-02-05 00:06:20+00:0021.4596639.8876621.4596639.88766927C502-2022
7B9Qm2kPxW…Tc4R=2026-02-05 00:10:41+00:002026-02-05 00:11:09+00:0040.71280-74.0059040.71310-74.00480931B104-2021
R3nPvXtZwK…Hq9L=2026-02-05 00:13:07+00:002026-02-05 00:13:16+00:0034.05220-118.2437034.05250-118.24290924A301-2023
R3nPvXtZwK…Hq9L=2026-02-05 00:17:03+00:002026-02-05 00:17:07+00:0034.05280-118.2431034.05280-118.24310924A301-2023
⚠️
Observation from source data: A single TOKEN_ID generates ~530 pings in roughly 2 hours, with many consecutive pings showing identical coordinates (device stationary). This high-stationarity pattern means deduplication at the source can reduce row count by 60–70% before storage, while delta encoding reduces the coordinate byte footprint by 4–6× in Parquet. The cell_site_id field is populated for all network-derived pings and provides a fallback geographic anchor when GPS precision is not required.
Section 04

Demographics — Monthly Dataset

Subscriber-level attribute table refreshed monthly. Contains stable identity and socioeconomic attributes. The join key between this table and the web traffic and footfall datasets is the hashed subscriber identifier. Source: Demographic sheet from Minimal_Kb_File_Size_v2.xlsx.

👤

subscriber_demographics

table: vz_subscriber.demographics · partition: snapshot_month · format: Parquet

Monthly ~50 GB/month ~60M subscribers

One row per active subscriber per monthly snapshot. Attributes are derived from the operator's customer records, supplemented by third-party data append for income banding. The table is slowly changing — most fields remain constant month-to-month. A Type 2 SCD (Slowly Changing Dimension) approach is used: changed records generate a new row, enabling historical attribute join accuracy.

Field NameData TypeSize (bytes)KeyDefinitionExample Value
Hashed_MSISDN VARCHAR(64) 64 PK Primary join key. SHA-256 hash of the subscriber's MSISDN (Mobile Station ISDN Number — the phone number). This is the cross-table link: web traffic TOKEN_ID and footfall TOKEN_ID both map back here through a secure linkage table. Never stores the raw phone number. Hashed MSISDN
MOBILE_COUNTRY_CODE SMALLINT 2 FK ITU-T E.212 Mobile Country Code. Identifies the country of the subscriber's home network. For US operator subscribers this is typically 311 or 312. Foreign subscribers roaming on the operator network have their home MCC recorded. Joins to ref_network_operator.mcc. 1
MOBILE_NETWORK_CODE SMALLINT 2 FK ITU-T E.212 Mobile Network Code. Identifies the specific operator within the MCC. The operator uses MNC 480 (for 311) and 12 (for 312). Combined MCC+MNC uniquely identifies the PLMN. Joins to ref_network_operator.mnc. 1
Age TINYINT 1 — Subscriber age in years, derived from date of birth in customer records. Stored as a 5-year age band integer in analytical layer: 1=18–24, 2=25–34, 3=35–44, 4=45–54, 5=55–64, 6=65+. Raw DOB not stored in this table for privacy compliance (CCPA). Update frequency: annually on subscriber anniversary. 2
Gender CHAR(1) 1 — Self-reported gender from subscriber profile. Values: M (male), F (female), N (non-binary/not specified), U (unknown). Approximately 3% of records have U. Used for audience segmentation in advertising analytics. Treated as sensitive PII — access controlled by role-based permissions. M
Home_ZIP VARCHAR(10) 10 FK USPS ZIP code of the subscriber's billing address (home location proxy). 5-digit ZIP format for US, ZIP+4 for precision. Foreign key to ref_zip_demographics.zip_code for area-level attribute enrichment (median income, urban/rural classification, population density). Updated when subscriber changes billing address. 10019
Income TINYINT 1 — Household income band code, third-party appended: 1=<$25K, 2=$25–50K, 3=$50–75K, 4=$75–100K, 5=$100–150K, 6=$150–200K, 7=$200K+. Derived from ZIP-level census data combined with device plan tier. Approximately 12% of records have no income assignment (NULL). Refreshed quarterly by data vendor. 3
Section 05

Reference Master Tables — Monthly

Static lookup tables that provide geographic, network topology, and classification context. Updated monthly when cell site commissioning records change or ZIP demographic data is refreshed. These tables are small but critical — they are the geographic spine that connects all four datasets.

🗺️

ref_cell_site

table: vz_reference.cell_site · updated: monthly · format: Parquet / CSV

Monthly ~2 GB/month ~500K cell sites
Field NameData TypeSize (bytes)KeyDefinition
cell_idVARCHAR(20)20PKUnique cell identifier. Matches CELL_ID in web_traffic and Cell Site ID in footfall. Format: {site_code}-{year_commissioned}.
site_latitudeDOUBLE8—WGS-84 latitude of the cell tower antenna. Used to spatially join footfall pings to sites when GPS is unavailable.
site_longitudeDOUBLE8—WGS-84 longitude of the cell tower antenna.
zip_codeVARCHAR(10)10FKZIP code of the cell site. Bridge key linking web traffic (via cell) to ZIP-level demographics without exposing subscriber identity.
valid_fromDATE4—Date from which this cell configuration is valid. Enables point-in-time joins for historical analysis.
valid_toDATE4—Date until which this cell configuration is valid. NULL = currently active.
technologyVARCHAR(10)10—Radio access technology: 4G-LTE, 5G-NR, 5G-mmWave, 3G-UMTS. Classifies coverage quality for capacity analytics.
sectorTINYINT1—Antenna sector (1–6). Each physical site may have multiple sectors covering different azimuth ranges.
🏙️

ref_zip_demographics

table: vz_reference.zip_demographics · updated: monthly · format: Parquet

Monthly ~150 MB ~42K ZIP codes
Field NameData TypeSizeKeyDefinition
zip_codeVARCHAR(10)10PKUSPS ZIP code. Primary key. Links to subscriber_demographics.Home_ZIP and ref_cell_site.zip_code.
state_codeCHAR(2)2—Two-letter USPS state abbreviation.
populationINT4—Resident population from most recent US Census ACS 5-year estimate.
median_incomeINT4—Median household income from ACS 5-year estimate. In USD.
urban_rural_codeTINYINT1—USDA Rural-Urban Continuum Code 1–9. 1–3 = metro, 4–6 = suburban, 7–9 = rural. Used for network capacity segmentation.
centroid_latFLOAT4—Geographic centroid latitude of the ZIP code boundary polygon. Used for radius-based spatial queries.
centroid_lonFLOAT4—Geographic centroid longitude of the ZIP code boundary polygon.
Section 06

Entity Relationship Schematic

The diagram below shows how all four dataset families connect. The two daily high-volume tables (web traffic and footfall) join to the two monthly reference dimensions via shared keys. All subscriber-level joins pass through hashed identifiers — no raw PII traverses the analytical layer.

Eureka AI — Entity Relationship Diagram All subscriber joins use hashed TOKEN_ID · No raw PII in analytical layer · Dashed = privacy/internal · Solid = analytical FK 🌐 web_traffic_daily vz_web · 4 TB/day · ~750M rows PK TOKEN_ID VARCHAR(64) PK START_VISIT_DATE_TIME TIMESTAMP TZ FK CELL_ID VARCHAR(20) VALID_FROM_CELL TIMESTAMP HOST_NAME VARCHAR(128) DOWNLOAD_VOLUME BIGINT UPLOAD_VOLUME BIGINT SESSION_DURATION INT Hit INT (SNI click count) 4 TB / day ~80 bytes/row uncompressed 📍 footfall_location_daily vz_location · 10 TB/day · ~250M rows PK TOKEN_ID VARCHAR(64) PK start_time TIMESTAMP TZ FK cell_site_id VARCHAR(20) end_time TIMESTAMP TZ start_latitude DOUBLE start_longitude DOUBLE end_latitude DOUBLE end_longitude DOUBLE 10 TB / day ~116 bytes/row uncompressed 🔗 Secure Linkage Hub Internal · Not in analytical layer TOKEN_ID (web + footfall) Hashed_MSISDN (demographics) Same physical subscriber across all 6 data domains ~60M operator subscribers 👤 subscriber_demographics vz_subscriber · Monthly · ~14 GB · 60M rows PK Hashed_MSISDN VARCHAR(64) FK Home_ZIP VARCHAR(10) MOBILE_COUNTRY_CODE SMALLINT MOBILE_NETWORK_CODE SMALLINT Age TINYINT Gender CHAR(1) Income TINYINT Monthly refresh · SCD2 🗼 ref_cell_site vz_reference · Monthly · ~2 GB · 500K sites PK cell_id VARCHAR(20) FK zip_code VARCHAR(10) site_latitude DOUBLE site_longitude DOUBLE technology VARCHAR(10) · 4G/5G sector TINYINT valid_from / valid_to DATE Monthly refresh · SCD2 🏙️ ref_home_zip_master vz_reference · Monthly · ~150 MB · 42K ZIPs PK zip_code VARCHAR(10) state_code CHAR(2) city_name VARCHAR(60) population INT · ACS 5yr median_income INT (USD) urban_rural_code TINYINT 1–9 centroid_lat / _lon FLOAT · FLOAT timezone VARCHAR(40) SAMPLE: 10019 · NY · New York · 21,400 $89,420 · urban(1) · 40.7549,-73.9840 Annual census refresh — ADDITIONAL DATA DOMAINS (Planned / Pending Specification) — 📞 call_log_daily vz_voice · Daily · Placeholder PK TOKEN_ID VARCHAR(64) call_start_time TIMESTAMP call_duration_sec INT call_type CHAR(3) · VOI/SMS/MMS originating_cell_id VARCHAR(20) terminating_zip VARCHAR(10) ⏳ Schema TBD · ~3 TB/day est. 💳 finance_billing_monthly vz_finance · Monthly · Placeholder PK Hashed_MSISDN VARCHAR(64) bill_period DATE total_charges_usd DECIMAL(10,2) overage_data_gb FLOAT payment_status VARCHAR(12) account_type CHAR(1) · I/B/E ⏳ Schema TBD · ~7 GB/month est. 📋 subscriber_plan vz_subscriber · Monthly · Placeholder PK Hashed_MSISDN VARCHAR(64) plan_code VARCHAR(20) plan_name VARCHAR(60) data_cap_gb FLOAT · NULL=unlimited valid_from / valid_to DATE plan_tier TINYINT · 1=Basic…5=Unlimited+ ⏳ Schema TBD · ~4 GB/month est. 🏠 home_internet_monthly vz_fios · Monthly · Placeholder PK account_id VARCHAR(20) FK Hashed_MSISDN VARCHAR(64) service_zip VARCHAR(10) speed_tier_mbps INT · 100/300/940 monthly_dl_gb FLOAT service_type VARCHAR(10) · FiOS/LTE-HI ⏳ Schema TBD · ~2 GB/month est. TOKEN_ID → TOKEN_ID → → Hashed_MSISDN CELL_ID → cell_id cell_site_id → cell_id Home_ZIP → zip_code → TOKEN_ID shared Privacy linkage (internal only) FK join (analytical layer) ZIP geographic join Planned join (schema TBD) Placeholder table (pending spec)
📌
Key join path: To enrich web traffic with subscriber demographics, the join goes web_traffic_daily.TOKEN_ID → linkage_hub → demographics.Hashed_MSISDN. The linkage hub is never exposed in the analytical layer — BigQuery/Spark jobs use a pre-joined view. To add geography, join web_traffic_daily.CELL_ID → ref_cell_site.cell_id → ref_cell_site.zip_code → ref_zip_demographics.zip_code. This full enrichment path requires no raw PII at any join step.
Section 07

Storage Size Calculations

Based on the actual byte measurements from Minimal_Kb_File_Size_v2.xlsx (117,602 bytes net data across 2,419 rows = ~49 bytes/row compressed) and the field-level byte widths defined above. Three granularity levels are calculated: raw timestamped (every event), 5-minute aggregation, and 15-minute aggregation.

Parquet Compression Calculator
Adjust codec to see impact on all datasets
Snappy: measured ratio from source file (117,602 raw bytes ÷ 2,419 rows = 48.6 bytes/row vs 151 bytes/row raw = 3.1× on web traffic). Fast decompression, native to BigQuery external tables and Dataflow.
Daily ingestion at codec
Web Traffic
~61 TB
raw: ~188 TB
Location Pings
~18 TB
raw: ~56 TB
Combined daily (web + location)
~79 TB
saves ~165 TB/day vs raw

7.1 — Web Traffic Daily: Size by Granularity

📐
Row estimate basis: The operator's US network serves ~60M subscribers. Average active subscribers per day: ~50M. Average sessions per active subscriber per day: ~25. Daily row count: ~1.25 billion rows/day at timestamped granularity. Field widths from source file: TOKEN_ID(64) + TIMESTAMP(8) + CELL_ID(20) + VALID_FROM(8) + HOST(~30) + DL(8) + UL(8) + DUR(4) + HIT(1) = ~151 bytes/row raw, ~49 bytes/row Parquet compressed.
GranularityRows / dayBytes / row (raw)Bytes / row (Parquet)Daily size (raw)Daily size (compressed)Annual (compressed)Reduction vs raw
⏱ Timestamped (raw)
every session event		 ~1.25 B15149 ~178 GB~58 GB~21 TB 1× (baseline)
⏱ 5-min aggregation
per TOKEN_ID × CELL_ID × 5min window		 ~85 M7224 ~5.8 GB~1.9 GB~720 GB 31× smaller
⏱ 15-min aggregation
per TOKEN_ID × CELL_ID × 15min window		 ~30 M7224 ~2.1 GB~0.7 GB~252 GB 83× smaller
🔢
5-min aggregation fields: window_start (TIMESTAMP), TOKEN_ID (VARCHAR 64), CELL_ID (VARCHAR 20), session_count (INT 4), total_download_bytes (BIGINT 8), total_upload_bytes (BIGINT 8), total_duration_sec (INT 4), distinct_hosts (INT 4) = 8 fields vs 9 raw. The key reduction is collapsing 1.25B individual sessions into 85M (CELL_ID × subscriber × 5-min window) groupings — approximately ~15 sessions per bucket.

7.2 — Footfall Location Daily: Size by Granularity

📐
Row estimate basis: ~50M active devices per day. Average ping frequency: ~1 ping per 45 seconds for an active device active ~8 hours. Pings per device per day: ~640. Daily rows: ~8 billion pings/day at raw (before deduplication). After deduplication of stationary pings (source data shows 60–70% repetition): ~2.5 billion unique location events/day. Field widths: TOKEN_ID(64) + start_ts(8) + end_ts(8) + start_lat(8) + start_lon(8) + end_lat(8) + end_lon(8) + cell_id(20) = ~88 bytes/row raw. Source file confirms ~116 chars/row text = ~88 bytes binary.
GranularityRows / dayBytes / row (raw)Bytes / row (Parquet+delta)Daily size (raw)Daily size (compressed)Annual (compressed)Reduction vs raw
⏱ Timestamped (raw)
every ping incl. stationary		 ~8 B (raw)
~2.5 B (deduped)		8815 ~660 GB~37 GB~13.5 TB 1× (baseline)
⏱ 5-min aggregation
per device × H3 cell (resolution 8) × 5min		 ~180 M4414 ~7.6 GB~2.4 GB~875 GB 15× smaller
⏱ 15-min aggregation
per device × H3 cell (resolution 8) × 15min		 ~65 M4414 ~2.8 GB~0.9 GB~320 GB 41× smaller
🗺️
5-min aggregation fields: window_start (TIMESTAMP), TOKEN_ID (VARCHAR 64), h3_index_r8 (VARCHAR 16 — H3 resolution 8 ≈ 0.74 km² hexagon), ping_count (INT 4), dwell_seconds (INT 4) = 5 fields. Replacing 4 float64 coordinate columns with one H3 index saves ~24 bytes/row and makes spatial joins trivial. The H3 resolution 8 cell side is ~461m — appropriate for neighbourhood-level footfall density analytics.

7.3 — Demographics & Reference Tables

TableRowsBytes / rowMonthly rawMonthly compressedAnnualNotes
subscriber_demographics~120 M81~9.2 GB~2.8 GB~34 GBLow cardinality fields compress extremely well. SCD Type 2 adds ~5% row growth per month.
ref_cell_site~500 K75~36 MB~12 MB~144 MBSlowly changing. Monthly snapshot for point-in-time accuracy. Tiny footprint.
ref_zip_demographics~42 K29~1.2 MB~0.4 MB~5 MBEssentially static. Annual census refresh. Negligible storage impact.
Section 08

Aggregation Size Comparison Summary

Full comparison of all four datasets across all three granularities, showing combined daily and annual storage footprint. These figures use Parquet + Zstandard compression ratios validated against the source file measurements.

Dataset Timestamped/day 5-min agg/day 15-min agg/day Timestamped/year 5-min/year 15-min/year
🌐 Web Traffic 58 GB1.9 GB0.7 GB 21.2 TB694 GB256 GB
📍 Footfall Location 37 GB2.4 GB0.9 GB 13.5 TB876 GB329 GB
👤 Demographics Monthly refresh — 2.8 GB/month 34 GB/year (all snapshots)
🗺️ Reference Tables Monthly refresh — 12.4 MB/month 149 MB/year (all snapshots)
📦 TOTAL 95 GB/day 4.3 GB/day 1.6 GB/day ~35 TB/yr ~1.6 TB/yr ~620 GB/yr
⚡
Recommendation: Store all three granularities in a tiered lake architecture. Keep 15-min aggregation in Standard storage (hot, queryable) for BI dashboards and real-time analytics — only 620 GB/year. Keep 5-min aggregation in Nearline (accessed for operational queries) — 1.6 TB/year. Keep raw timestamped in Coldline/Archive (for forensic replay, model training, regulatory compliance) — 35 TB/year at $0.0012/GB/mo in GCP Archive.

What happens at each aggregation level

⏱ Timestamped (Raw)
  • Every individual session / ping
  • Full fidelity — exact user path reconstructable
  • TOKEN_ID visible (shared key across web & footfall)
  • Required for compliance, fraud, legal holds
  • Suitable for ML model training
  • 95 GB/day · 35 TB/year
⏱ 5-min Aggregation
  • Sessions rolled up per subscriber × cell × window
  • Footfall: device position at H3-r8 (461m) precision
  • Adds: count, sum volumes, distinct hosts
  • Drops: individual HOST_NAME per session
  • Suitable for: network ops, capacity planning
  • 4.3 GB/day · 1.6 TB/year
⏱ 15-min Aggregation
  • Further roll-up — 3× smaller than 5-min
  • Footfall: H3-r7 (5.16 km²) precision
  • TOKEN_ID may be dropped → only CELL_ID
  • Fully anonymised if k≥5 grouping applied
  • Suitable for: BI dashboards, exec reporting
  • 1.6 GB/day · 620 GB/year