TL;DR
Join historical prices, fundamentals and disclosures to security_segments using both ticker and the inclusive date span. A ticker alone can connect unrelated companies that reused a symbol. Shared security_id values follow one security across renames, while canonical_symbol lets you select its history. A merger into a new registrant can create a different security rather than a rename. The page's query is a proposed example, so inspect returned spans and open ended dates before using them.
- Query name
- lake.security_segments
- Storage partition
- snapshot
- Columns
- 9
Columns and types
VARCHAR
- ticker
- security_id
- canonical_symbol
- share_class_figi
- cik
- name
- asset_type
DATE
- from_date
- to_date
Current logical schema for lake.security_segments
Inspect the complete lake.security_segments schema
Scroll sideways to compare all columns. Select a heading to sort.
| ticker | VARCHAR |
| from_date | DATE |
| to_date | DATE |
| security_id | VARCHAR |
| canonical_symbol | VARCHAR |
| share_class_figi | VARCHAR |
| cik | VARCHAR |
| name | VARCHAR |
| asset_type | VARCHAR |
No matching rows. Clear the filter to see all records.
Current logical schema for lake.security_segments
Query lake.security_segments using the column names and types below. The query validator and engine use this logical catalog; a field absent from an older shard returns typed NULL.
What this table contains
Which company traded under a ticker on a date; follows a company across renames.
Follow one security across ticker names
Query example, not executed here. The dates and filters define the proposed research scope; returned rows and current coverage must be checked in your workspace.
Rows with one security_id track one security across renames. Join a price row on ticker and its inclusive date span, using an open-ended upper bound for NULL to_date. A ticker-only join can mix different companies.
SELECT ticker, from_date, to_date, security_id, canonical_symbol, name
FROM lake.security_segments
WHERE canonical_symbol = 'META'
ORDER BY from_date
LIMIT 20;Submit the security_segments example and read back its parts
Run the exact SQL through the Python SDK
Save this page's SQL as query.sql and the runner below as query.py. Use a registered key with lake scope from Developers. The runner submits the displayed security_segments query once, saves its operation, waits on that same ID, saves the returned manifest and downloads its Parquet parts.
This submission has not been executed for the reference page. Inspect the SQL, dates and resource limits before running it. Reuse the request key only for this identical SQL and limits; use a new key when changing them.
python3 -m pip install nexustrade==1.42.0
export NEXUSTRADE_API_KEY="YOUR_LAKE_SCOPED_KEY"
python3 query.py
import json
from pathlib import Path
from nexustrade import NexusTradeClient
# Save the SQL displayed on this page as query.sql.
sql = Path("query.sql").read_text()
client = NexusTradeClient.from_environment()
operation = client.create_lake_query(
{"query": sql, "limits": {"timeoutSeconds": 30, "maxRows": 50,
"maxResultBytes": 1048576}},
idempotency_key="reference-security_segments-20261012-v1",
)
Path("query-operation.json").write_text(json.dumps(operation, indent=2))
finished = client.wait_for_lake_query(operation["id"])
if finished["status"] != "completed":
raise RuntimeError(finished)
manifest = client.get_lake_query_manifest(operation["id"])["result"]
Path("query-manifest.json").write_text(json.dumps(manifest, indent=2))
print(json.dumps(manifest, indent=2))
for part in manifest["parts"]:
Path(f"query-{part['part']}.parquet").write_bytes(
client.download_lake_query_part(operation["id"], part["part"], max_bytes=1048576)
)
Coverage and timing
Inspect source coverage and timing before using the rows
Which company traded under a ticker on a date; follows a company across renames.
One row per span a security traded under one ticker: `from_date` and `to_date` are its first and last sessions, inclusive, and `to_date` is NULL while it still trades. Built from Polygon's point-in-time listing. Rows sharing a `security_id` are one security across renames.
`canonical_symbol` is the ticker a security's whole history is known by: its current ticker if it still trades, otherwise its last ticker, or `__SECURITY__<security_id>` when a later company took that ticker. Filter on it to follow one company across renames.
Price, fundamentals and disclosure tables are keyed by the ticker as traded on each date. Join them here on `ticker` AND `date BETWEEN from_date AND COALESCE(to_date, DATE '9999-12-31')`; joining on `ticker` alone matches every company that ever used the symbol.
A merger into a new registrant is a new security, not a rename: Paramount Global (`PARA` to 2025-08-06) and Paramount Skydance (`PSKY` from 2025-08-07) have different `security_id`s, because holders did not all receive shares.
No comments yet.