/beta/query_datalinks

This guide details available datalinks, table schemas, sample queries, and field definitions for querying SynMax’s datasets via the unified /v4/beta/query_datalinks endpoint.

Key Benefits:

• Query multiple datasets with a single endpoint (Hyperion, Vulcan, Leviaton)

• Use standard SQL syntax across all datalinks

• Access real-time and historical data in one place

Warning

Beta API Notice: This endpoint is currently in beta and may change as we refine the feature.

Endpoint

Method	POST
URL	https://vulcan.api.synmax.com/v2/beta/query_datalinks

Request Examples

cURLSynmax Python Client

curl -X POST "https://vulcan.api.synmax.com/v2/beta/query_datalinks" \
     -H "Access-Key: $API_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
           "query": "SELECT * FROM vdl.under_construction LIMIT 10"
         }'

Leviaton (Datalink ID: 1002)

Datalink ID: 1002

Global LNG trade flow intelligence tracking cargo transactions, vessel movements, and fleet status for 921 LNG carriers worldwide. Provides vessel-level visibility into LNG supply chains from production terminals to consumption markets.

ldl.transaction_history_raw

Global LNG loading and offloading events tracking complete cargo journeys from production facilities to consumption terminals. Contains BCF volumes with loading_origin_country for true production source tracking.

Table ID: ldl.transaction_history_raw

Sample Queries

-- US LNG exports by terminal (last 30 days)
SELECT
    terminal,
    COUNT(*) as transaction_count,
    SUM(volume) as total_bcf
FROM ldl.transaction_history_raw
WHERE transaction_type = 'loading'
  AND country = 'USA'
  AND timestamp >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY terminal
ORDER BY 3 DESC
LIMIT 10;

-- Global trade flows by origin country
SELECT
    loading_origin_country,
    country as destination_country,
    COUNT(*) as shipments,
    SUM(volume) as total_volume_bcf
FROM ldl.transaction_history_raw
WHERE transaction_type = 'offloading'
  AND loading_origin_country IS NOT NULL
  AND timestamp >= '2024-01-01'
GROUP BY loading_origin_country, country
ORDER BY 4 DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
vessel_id	varchar	Unique vessel identifier
imo	bigint	IMO number for vessel identification
timestamp	timestamp	Date/time of the transaction event
transaction_type	varchar	Type of event: ‘loading’ or ‘offloading’
terminal	varchar	Name of the LNG terminal
country	varchar	Country where the transaction occurred
volume	float	Cargo volume in BCF (billion cubic feet)
loading_origin_country	varchar	Country where cargo was originally loaded (for offloading events)
loading_terminal	varchar	Terminal where cargo was originally loaded
loading_timestamp	timestamp	Timestamp of original loading event
name	varchar	Vessel name
mmsi	bigint	Maritime Mobile Service Identity number
flag	varchar	Vessel flag state
maximum_capacity	float	Maximum cargo capacity in cubic meters
created_at	timestamp	Record creation timestamp
modified_at	timestamp	Record last modification timestamp

ldl.ais_history_raw

Historical vessel tracking database with 26.7 million AIS records for 921 LNG vessels from January 2019 to present. Captures precise positions, movement data, draught (loading indicator), and captain’s destination with standardized port codes.

Table ID: ldl.ais_history_raw

Sample Queries

-- Track specific vessel journey (last 30 days)
SELECT
    timestamp_dynamic,
    latitude,
    longitude,
    speed,
    captains_destination,
    draught
FROM ldl.ais_history_raw
WHERE imo = 9331660
  AND timestamp_dynamic >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY timestamp_dynamic
LIMIT 100;

-- Daily active vessel count
SELECT
    DATE(timestamp_dynamic) as date,
    COUNT(DISTINCT vessel_id) as active_vessels
FROM ldl.ais_history_raw
WHERE timestamp_dynamic >= '2024-01-01'
GROUP BY DATE(timestamp_dynamic)
ORDER BY date
LIMIT 30;

Field Reference

Field Name	Data Type	Description
vessel_id	varchar	Unique SynMax vessel identifier
timestamp_static	timestamp	Timestamp of static AIS message
imo	bigint	IMO number for vessel identification
mmsi	bigint	Maritime Mobile Service Identity
name	varchar	Vessel name from AIS
callsign	varchar	Radio callsign
captains_destination	varchar	Destination reported by captain
captains_eta	timestamp	Estimated time of arrival reported by captain
flag	varchar	Flag state code
draught	float	Current vessel draught in meters (indicates loading state)
timestamp_dynamic	timestamp	Timestamp of dynamic AIS message (position)
ais_nav_status	varchar	Navigation status code
longitude	float	Longitude coordinate
latitude	float	Latitude coordinate
location	varchar	Geographic location description
speed	float	Speed over ground in knots
heading	float	True heading in degrees
course	float	Course over ground in degrees
rate_of_turn	float	Rate of turn
msg_collection_method	varchar	Method used to collect AIS message
modified_at	timestamp	Record modification timestamp
created_at	timestamp	Record creation timestamp

ldl.vessel_info_latest

Real-time snapshot of 903 active LNG vessels combining latest AIS position with complete vessel specifications (capacity, dimensions, type) and forecasted destinations. Use for current fleet monitoring, real-time locations, and near-term supply forecasting.

Table ID: ldl.vessel_info_latest

Sample Queries

-- Current fleet snapshot by vessel type
SELECT
    vessel_type,
    COUNT(*) as vessel_count,
    AVG(maximum_capacity) as avg_capacity_cbm,
    AVG(speed) as avg_speed_knots
FROM ldl.vessel_info_latest
WHERE synmax_status = 'ACTIVE'
GROUP BY vessel_type
ORDER BY 2 DESC;

-- Vessels heading to specific terminal
SELECT
    name,
    imo,
    maximum_capacity,
    forecast_destination_terminal,
    forecast_destination_timestamp,
    speed
FROM ldl.vessel_info_latest
WHERE forecast_destination_terminal LIKE '%Sabine%'
  AND forecast_destination_timestamp BETWEEN NOW() AND NOW() + INTERVAL '30 days'
ORDER BY forecast_destination_timestamp;

Field Reference

Field Name	Data Type	Description
vessel_id	varchar	Unique SynMax vessel identifier
imo	bigint	IMO number
mmsi	bigint	Maritime Mobile Service Identity
name	varchar	Vessel name
callsign	varchar	Radio callsign
flag	varchar	Flag state
vessel_type	varchar	Type of LNG vessel
synmax_status	varchar	Vessel operational status (ACTIVE, INACTIVE, etc.)
maximum_capacity	float	Maximum cargo capacity in cubic meters
length_overall	float	Length overall in meters
beam	float	Beam (width) in meters
draught	float	Current draught in meters
speed	float	Current speed in knots
heading	float	Current heading
course	float	Current course over ground
latitude	float	Current latitude
longitude	float	Current longitude
timestamp_dynamic	timestamp	Timestamp of latest position
captains_destination	varchar	Captain-reported destination
captains_eta	timestamp	Captain-reported ETA
forecast_destination_terminal	varchar	SynMax forecasted destination terminal
forecast_destination_country	varchar	SynMax forecasted destination country
forecast_destination_timestamp	timestamp	Forecasted arrival time
last_loading_terminal	varchar	Last terminal where vessel loaded cargo
last_loading_country	varchar	Country of last loading
last_loading_timestamp	timestamp	Timestamp of last loading
ais_nav_status	varchar	AIS navigation status
build_year	int	Year vessel was built
modified_at	timestamp	Record modification timestamp
created_at	timestamp	Record creation timestamp

Hyperion Supply (Datalink ID: 1003)

Datalink ID: 1003

Comprehensive well-level production data, forecasting, and metadata spanning the entire well lifecycle from drilling to production across U.S. and Canadian basins. Provides historical state reported monthly production (1960s-present), well attributes, and short/long-term forecasts.

hdl.production_by_well

Historical state reported monthly well-level production with oil/gas/water volumes from 1960s to December 2024. Contains 411M+ records at well-month grain.

Important

Production fields are stored as strings and require type conversion (CAST to NUMERIC) before aggregation.

Table ID: hdl.production_by_well

Sample Queries

-- Recent production for an operator (with type conversion)
SELECT
    api,
    date,
    production_month,
    CAST(gas_monthly AS NUMERIC) as gas_mcf,
    CAST(oil_monthly AS NUMERIC) as oil_bbl,
    operator_name,
    state_ab
FROM hdl.production_by_well
WHERE operator_name ILIKE '%EQT%'
  AND date >= '2023-01-01'
ORDER BY date DESC
LIMIT 100;

-- Aggregate monthly production by sub-region
SELECT
    date,
    sub_region,
    SUM(CAST(gas_monthly AS NUMERIC)) as total_gas_mcf,
    COUNT(DISTINCT api) as well_count
FROM hdl.production_by_well
WHERE date >= '2023-01-01'
  AND state_ab = 'TX'
GROUP BY date, sub_region
ORDER BY date DESC
LIMIT 50;

Field Reference

Field Name	Data Type	Description
api	varchar	API well number (unique well identifier)
date	date	Production reporting date
production_month	varchar	Month of production
gas_monthly	varchar	Monthly gas production in MCF (requires CAST to NUMERIC)
oil_monthly	varchar	Monthly oil production in BBL (requires CAST to NUMERIC)
water_monthly	varchar	Monthly water production in BBL (requires CAST to NUMERIC)
gas_daily	varchar	Daily gas rate in MCF/day
oil_daily	varchar	Daily oil rate in BBL/day
operator_name	varchar	Well operator name
state_ab	varchar	State abbreviation
county	varchar	County name
sub_region	varchar	Sub-region classification
well_name	varchar	Well name
latitude	float	Well latitude
longitude	float	Well longitude

hdl.wells

Modern well master file (1900-2025) with standardized drilling/completion dates, lateral lengths, wellpad assignments, and coordinates. Production-focused subset with 99% populated depth/location fields.

Table ID: hdl.wells

Sample Queries

-- Recent horizontal wells in Permian Basin
SELECT *
FROM hdl.wells
WHERE sub_region_natgas = 'Permian-NM'
  AND wellbore_type = 'Horizontal'
  AND date_spud >= '2023-01-01'
ORDER BY horizontal_length DESC
LIMIT 20;

-- Count wells by operator and basin for 2024 completions
SELECT
    operator,
    sub_region_natgas,
    COUNT(*) as well_count
FROM hdl.wells
WHERE date_completion >= '2024-01-01'
  AND date_completion < '2025-01-01'
GROUP BY operator, sub_region_natgas
ORDER BY 3 DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
api	varchar	API well number
wellpad_id	int	Wellpad identifier for grouping co-located wells
operator	varchar	Current well operator
well_name	varchar	Well name
state_ab	varchar	State abbreviation
county	varchar	County name
sub_region_natgas	varchar	Natural gas sub-region (e.g., ‘Permian-NM’, ‘West - TX’)
region_natgas	varchar	Natural gas region
basin_play	varchar	Basin/play classification
formation	varchar	Target formation
wellbore_type	varchar	Wellbore type (Horizontal, Vertical, Directional)
date_spud	date	Spud date (drilling start)
date_completion	date	Completion date
date_first_production	date	First production date
horizontal_length	float	Horizontal lateral length in feet
total_depth	float	Total measured depth
true_vertical_depth	float	True vertical depth
latitude	float	Surface latitude
longitude	float	Surface longitude
latitude_bh	float	Bottomhole latitude
longitude_bh	float	Bottomhole longitude

hdl.short_term_forecast

Monthly well-level production forecasts (2-3 months ahead) with oil/gas volumes in daily and monthly units. Includes date_forecast_run for tracking forecast vintages.

Table ID: hdl.short_term_forecast

Sample Queries

-- Forecast production by sub-region (U.S. only)
SELECT
    date_prod,
    sub_region_natgas,
    SUM(prod_dry_gas_mcf_day) / 1000 as total_mmcf_day
FROM hdl.short_term_forecast
WHERE region_natgas != 'Canada'
  AND date_prod >= CURRENT_DATE
GROUP BY date_prod, sub_region_natgas
ORDER BY date_prod, 3 DESC
LIMIT 50;

-- Operator forecast comparison
SELECT
    operator,
    date_prod,
    SUM(prod_oil_bbl_day) as total_oil_bbl_day,
    SUM(prod_dry_gas_mcf_day) as total_gas_mcf_day
FROM hdl.short_term_forecast
WHERE operator IN ('EOG RESOURCES', 'PIONEER NATURAL')
GROUP BY operator, date_prod
ORDER BY date_prod, operator
LIMIT 50;

Field Reference

Field Name	Data Type	Description
api	varchar	API well number
date_prod	date	Production forecast date
date_forecast_run	date	Date the forecast was generated
operator	varchar	Well operator
sub_region_natgas	varchar	Natural gas sub-region
region_natgas	varchar	Natural gas region
prod_oil_bbl_day	float	Forecasted oil production (BBL/day)
prod_dry_gas_mcf_day	float	Forecasted dry gas production (MCF/day)
prod_oil_bbl_month	float	Forecasted oil production (BBL/month)
prod_dry_gas_mcf_month	float	Forecasted dry gas production (MCF/month)

hdl.long_term_forecast

Three-year regional production forecasts at sub-region-quarter grain (BCF/d) based on public producer earnings adjusted by public/private frac activity ratios. Updated quarterly.

Table ID: hdl.long_term_forecast

Sample Queries

-- Long-term forecast by sub-region
SELECT
    date_prod,
    sub_region_natgas,
    prod_dry_gas_bcf_day
FROM hdl.long_term_forecast
WHERE date_prod >= CURRENT_DATE
ORDER BY date_prod, prod_dry_gas_bcf_day DESC
LIMIT 50;

-- Regional production outlook by quarter
SELECT
    region_natgas,
    DATEFROMPARTS(YEAR(date_prod), ((MONTH(date_prod)-1)/3)*3+1, 1) as quarter,
    SUM(prod_dry_gas_bcf_day) as total_bcf_day
FROM hdl.long_term_forecast
GROUP BY region_natgas, DATEFROMPARTS(YEAR(date_prod), ((MONTH(date_prod)-1)/3)*3+1, 1)
ORDER BY 2, 3 DESC;

Field Reference

Field Name	Data Type	Description
date_prod	date	Production forecast date
sub_region_natgas	varchar	Natural gas sub-region
region_natgas	varchar	Natural gas region
prod_oil_bbl_day	float	Forecasted oil production (BBL/day)
prod_dry_gas_bcf_day	float	Forecasted dry gas production (BCF/day)

hdl.tils

TIL (Turned In Line) monitoring for Expand, Apex, EQT only in Haynesville-LA and Northeast since March 2024. Tracks completion-to-production timing at wellpad-day grain.

Table ID: hdl.tils

Sample Queries

-- DTIL inventory by operator
SELECT
    operator,
    sub_region_natgas,
    SUM(wells) as total_wells,
    SUM(prod_dry_gas_bcf_day) as total_production
FROM hdl.tils
WHERE date_til IS NULL
GROUP BY operator, sub_region_natgas;

-- Wells with TIL dates (compute days_to_til in application layer)
SELECT
    operator,
    wellpad_id,
    date_frac_end,
    date_til,
    wells
FROM hdl.tils
WHERE date_til IS NOT NULL
ORDER BY date_til DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
wellpad_id	int	Wellpad identifier
wells	int	Number of wells on the wellpad
prod_dry_gas_bcf_day	float	Production rate (BCF/day)
operator	varchar	Operator name
sub_region_natgas	varchar	Natural gas sub-region
date_frac_end	date	Date frac operations ended
date_til	date	Turned-in-line date (NULL if not yet TIL)

Hyperion API (Datalink ID: 1004)

Datalink ID: 1004

Daily tracking of drilling, completion, and frac crew activity across the well development lifecycle in North American basins. Coverage beginning Sept 2020 for U.S.; May 2024 for Canada. Regional start dates vary: Northeast begins March 2021.

hdl.rigs

Daily drilling rig activity at wellpad-day grain tracking active rigs by location, operator, and commodity (oil/gas). Use COUNT(*) grouped by date for daily rig counts.

Table ID: hdl.rigs

Sample Queries

-- Daily active rig count
SELECT
    date,
    COUNT(*) as active_rigs
FROM hdl.rigs
WHERE date >= '2024-01-01'
GROUP BY date
ORDER BY date
LIMIT 30;

-- Permian basin rig activity by operator
SELECT
    operator_name,
    COUNT(*) as rig_days,
    COUNT(DISTINCT wellpad_id) as wellpads
FROM hdl.rigs
WHERE sub_region IN ('West - TX', 'Permian-NM')
  AND date >= '2024-01-01'
GROUP BY operator_name
ORDER BY 2 DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
date	date	Observation date
wellpad_id	int	Wellpad identifier
operator_name	varchar	Drilling operator
sub_region	varchar	Sub-region (e.g., ‘West - TX’, ‘Permian-NM’)
region	varchar	Region classification
state_ab	varchar	State abbreviation
county	varchar	County name
commodity	varchar	Primary commodity (Oil/Gas)
latitude	float	Wellpad latitude
longitude	float	Wellpad longitude

hdl.fraccrews

Daily frac crew activity at wellpad-day grain tracking hydraulic fracturing operations by location, operator, and service company. Use COUNT(*) grouped by date for daily crew counts.

Table ID: hdl.fraccrews

Sample Queries

-- Daily active frac crew count
SELECT
    date,
    COUNT(*) as active_crews
FROM hdl.fraccrews
WHERE date >= '2024-01-01'
GROUP BY date
ORDER BY date
LIMIT 30;

-- Frac activity by service company
SELECT
    service_company,
    COUNT(*) as crew_days,
    COUNT(DISTINCT wellpad_id) as wellpads_fracked
FROM hdl.fraccrews
WHERE date >= '2024-01-01'
  AND service_company != ''
GROUP BY service_company
ORDER BY 2 DESC
LIMIT 15;

Field Reference

Field Name	Data Type	Description
date	date	Observation date
wellpad_id	int	Wellpad identifier
operator_name	varchar	Well operator
service_company	varchar	Frac service company
sub_region	varchar	Sub-region
region	varchar	Region
state_ab	varchar	State abbreviation
county	varchar	County name
commodity	varchar	Primary commodity
latitude	float	Wellpad latitude
longitude	float	Wellpad longitude

hdl.completions

Individual well completion records at well-level grain with date_completion_start and date_completion_end. Filter by end_date to measure when wells were actually completed.

Table ID: hdl.completions

Sample Queries

-- Monthly completions by sub-region
SELECT
    DATEFROMPARTS(YEAR(end_date), MONTH(end_date), 1) as month,
    sub_region,
    COUNT(*) as wells_completed
FROM hdl.completions
WHERE end_date >= '2024-01-01'
GROUP BY DATEFROMPARTS(YEAR(end_date), MONTH(end_date), 1), sub_region
ORDER BY 1, 3 DESC
LIMIT 50;

-- Completions by operator (oil vs gas)
SELECT
    operator_name,
    completion_class,
    COUNT(*) as wells
FROM hdl.completions
WHERE end_date >= '2024-01-01'
GROUP BY operator_name, completion_class
ORDER BY 3 DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
api	varchar	API well number
wellpad_id	int	Wellpad identifier
operator_name	varchar	Well operator
start_date	date	Completion start date
end_date	date	Completion end date (use for timing analysis)
sub_region	varchar	Sub-region
region	varchar	Region
state_ab	varchar	State abbreviation
county	varchar	County name
completion_class	varchar	Oil or Gas classification
lateral_length	float	Horizontal lateral length
latitude	float	Well latitude
longitude	float	Well longitude

hdl.ducs_by_operator

Drilled but Uncompleted well inventory at county-operator-day grain with count field showing inventory snapshot at date. Rising DUCs when rig/frac ratio >2.5, falling when <2.5.

Table ID: hdl.ducs_by_operator

Sample Queries

-- Current total DUC inventory
SELECT
    SUM(count) as total_ducs
FROM hdl.ducs_by_operator
WHERE date = (SELECT MAX(date) FROM hdl.ducs_by_operator);

-- DUC inventory trend by sub-region
SELECT
    date,
    sub_region,
    SUM(count) as duc_count
FROM hdl.ducs_by_operator
WHERE date >= '2024-01-01'
GROUP BY date, sub_region
ORDER BY date, 3 DESC
LIMIT 50;

Field Reference

Field Name	Data Type	Description
date	date	Observation date
operator_name	varchar	Operator name
sub_region	varchar	Sub-region
region	varchar	Region
state_ab	varchar	State abbreviation
county	varchar	County name
count	int	Number of DUCs (drilled but uncompleted wells)
commodity	varchar	Primary commodity classification

Pipelines (Datalink ID: 1005)

Datalink ID: 1005

Daily interstate pipeline nomination and capacity data tracking natural gas movements across the U.S. transmission network at point-by-point level. Provides scheduled flows, operating capacities, and location metadata for receipts and deliveries.

hdl.pipeline_flow

Complete daily pipeline dataset with flow metrics and location metadata. Contains gas_day, total_scheduled_quantity, operating_capacity, location_category, and rec_del. Use smx_location_id to track specific points over time.

Table ID: hdl.pipeline_flow

Sample Queries

-- Daily LNG export feedgas by terminal
SELECT
    gas_day,
    pipeline_name,
    location_name,
    SUM(total_scheduled_quantity) as total_feedgas
FROM hdl.pipeline_flow
WHERE location_category = 'LNG'
  AND rec_del = -1
  AND gas_day >= '2024-01-01'
GROUP BY gas_day, pipeline_name, location_name
ORDER BY gas_day DESC
LIMIT 30;

-- Production receipts by state
SELECT
    location_state_ab,
    gas_day,
    SUM(total_scheduled_quantity) / 1000000.0 as bcf_day
FROM hdl.pipeline_flow
WHERE location_category = 'Production'
  AND rec_del = 1
  AND gas_day >= '2024-01-01'
GROUP BY location_state_ab, gas_day
ORDER BY gas_day DESC, 3 DESC
LIMIT 30;

Field Reference

Field Name	Data Type	Description
gas_day	date	Gas day (flow date)
smx_location_id	int	Unique SynMax location identifier
pipeline_name	varchar	Name of the pipeline
location_name	varchar	Name of the flow point
location_category	varchar	Category: Production, LNG, Power, Storage, Interconnect, etc.
rec_del	int	Receipt (1) or Delivery (-1) indicator
total_scheduled_quantity	float	Scheduled flow quantity
operating_capacity	float	Operating capacity
design_capacity	float	Design capacity
location_state_ab	varchar	State abbreviation
location_county	varchar	County name
latitude	float	Location latitude
longitude	float	Location longitude
cycle_lookup	varchar	Nomination cycle

hdl.pipeline_locations

Master location metadata reference with one row per location. Contains pipeline_name, location_name, location_category, rec_del, and coordinates for joining to flow tables.

Table ID: hdl.pipeline_locations

Sample Queries

-- Find LNG terminal locations
SELECT
    smx_location_id,
    pipeline_name,
    location_name,
    location_category,
    location_state_ab
FROM hdl.pipeline_locations
WHERE location_category = 'LNG'
ORDER BY pipeline_name, location_name
LIMIT 30;

-- Count locations by category
SELECT
    location_category,
    COUNT(*) as location_count
FROM hdl.pipeline_locations
GROUP BY location_category
ORDER BY 2 DESC;

Field Reference

Field Name	Data Type	Description
smx_location_id	int	Unique SynMax location identifier
pipeline_name	varchar	Pipeline name
location_name	varchar	Location/point name
location_category	varchar	Category classification
rec_del	int	Receipt (1) or Delivery (-1)
location_state_ab	varchar	State abbreviation
location_county	varchar	County name
latitude	float	Latitude
longitude	float	Longitude

hdl.pipeline_flow_only

Daily flow metrics without location metadata. Contains gas_day, total_scheduled_quantity, operating_capacity, cycle_lookup. Requires join to hdl.pipeline_locations for location details.

Table ID: hdl.pipeline_flow_only

Sample Queries

-- Track specific location over time
SELECT
    f.gas_day,
    f.total_scheduled_quantity,
    f.operating_capacity,
    f.cycle_lookup
FROM hdl.pipeline_flow_only f
WHERE f.smx_location_id = 50669
  AND f.gas_day >= '2024-01-01'
ORDER BY f.gas_day
LIMIT 30;

-- Join with locations for category filtering
SELECT
    f.gas_day,
    l.pipeline_name,
    l.location_name,
    SUM(f.total_scheduled_quantity) as total_flow
FROM hdl.pipeline_flow_only f
JOIN hdl.pipeline_locations l ON f.smx_location_id = l.smx_location_id
WHERE l.location_category = 'Power'
  AND f.gas_day >= '2024-01-01'
GROUP BY f.gas_day, l.pipeline_name, l.location_name
ORDER BY f.gas_day DESC
LIMIT 30;

Field Reference

Field Name	Data Type	Description
gas_day	date	Gas day (flow date)
smx_location_id	int	Location identifier (join key)
total_scheduled_quantity	float	Scheduled flow quantity
operating_capacity	float	Operating capacity
design_capacity	float	Design capacity
cycle_lookup	varchar	Nomination cycle

Vulcan (Datalink ID: 1006)

Datalink ID: 1006

Satellite-verified construction monitoring for energy infrastructure including power generation, data centers, and LNG facilities. Tracks construction milestones from land clearing through commissioning and provides independent predictions of online dates.

vdl.under_construction

U.S. power generation projects (solar, wind, battery, gas) with satellite-verified construction milestones and capacity details. One row per generator with no deduplication required.

Table ID: vdl.under_construction

Sample Queries

-- Confirmed projects by technology
SELECT
    technology,
    COUNT(DISTINCT plant_id) as plant_count,
    SUM(nameplate_capacity) as total_capacity_mw
FROM vdl.under_construction
WHERE vulcan_status = 'Confirmed'
  AND nameplate_capacity IS NOT NULL
GROUP BY technology
ORDER BY 3 DESC;

-- High-likelihood projects coming online in 12 months
SELECT
    plant_name,
    technology,
    nameplate_capacity,
    state_code,
    date_vulcan_earliest_online,
    project_rank
FROM vdl.under_construction
WHERE project_rank >= 5
  AND vulcan_status = 'Confirmed'
  AND date_vulcan_earliest_online BETWEEN CURRENT_DATE AND CURRENT_DATE + 365
ORDER BY date_vulcan_earliest_online
LIMIT 20;

Field Reference

Field Name	Data Type	Description
synmax_id	varchar	Unique SynMax identifier
plant_id	varchar	EIA plant identifier
plant_name	varchar	Plant name
generator_id	varchar	Generator identifier
technology	varchar	Technology type (Solar, Wind, Battery, Gas)
nameplate_capacity	float	Nameplate capacity in MW
state_code	varchar	State code
county	varchar	County
latitude	float	Latitude
longitude	float	Longitude
vulcan_status	varchar	Construction status (Confirmed, Possible, etc.)
project_rank	int	Construction likelihood score (0-7 scale)
date_vulcan_earliest_online	date	SynMax earliest predicted online date
date_vulcan_latest_online	date	SynMax latest predicted online date
date_eia_planned_operation	date	EIA official planned operation date
entity_name	varchar	Owner/developer entity

vdl.datacenters

Data center facilities (hyperscale, co-location, enterprise, crypto) with power demand forecasts and construction status.

Important

This table requires deduplication by the latest modified_at per synmax_id. Use window functions (e.g., ROW_NUMBER) to isolate the most recent record.

Table ID: vdl.datacenters

Sample Queries

-- Deduplicated data centers under construction
WITH latest_datacenters AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY modified_at DESC) as rn
    FROM vdl.datacenters
)
SELECT
    data_center_type,
    COUNT(DISTINCT plant_id) as facility_count,
    SUM(unit_capacity) as total_capacity_mw
FROM latest_datacenters
WHERE rn = 1
GROUP BY data_center_type
ORDER BY 3 DESC;

-- Top data center owners
WITH latest_datacenters AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY modified_at DESC) as rn
    FROM vdl.datacenters
)
SELECT
    owner_name,
    COUNT(DISTINCT plant_id) as facility_count,
    SUM(unit_capacity) as total_capacity_mw
FROM latest_datacenters
WHERE rn = 1
GROUP BY owner_name
ORDER BY 3 DESC
LIMIT 15;

Field Reference

Field Name	Data Type	Description
synmax_id	varchar	Unique SynMax identifier
plant_id	varchar	Plant/facility identifier
plant_name	varchar	Facility name
data_center_type	varchar	Type: Hyperscale, Co-location, Enterprise, Crypto
owner_name	varchar	Facility owner
unit_capacity	float	Power capacity in MW
state_code	varchar	State code
county	varchar	County
latitude	float	Latitude
longitude	float	Longitude
vulcan_status	varchar	Construction status
modified_at	timestamp	Last modification timestamp (use for deduplication)

vdl.lng_projects

Global LNG liquefaction and regasification terminals with construction progress and capacity. Use plant_name + phase_number as composite key. No deduplication needed.

Table ID: vdl.lng_projects

Sample Queries

-- LNG projects by technology type
SELECT
    technology,
    COUNT(*) as project_count,
    SUM(nameplate_capacity) as total_capacity_mtpa
FROM vdl.lng_projects
WHERE vulcan_status = 'Confirmed'
GROUP BY technology;

-- Projects expected online in next 24 months
SELECT
    plant_name,
    phase_number,
    technology,
    nameplate_capacity,
    date_vulcan_earliest_online,
    entity_name
FROM vdl.lng_projects
WHERE date_vulcan_earliest_online BETWEEN CURRENT_DATE AND CURRENT_DATE + 730
ORDER BY date_vulcan_earliest_online
LIMIT 20;

Field Reference

Field Name	Data Type	Description
synmax_id	varchar	Unique SynMax identifier
plant_name	varchar	LNG terminal name
phase_number	int	Project phase number
technology	varchar	Liquefaction or Regasification
nameplate_capacity	float	Capacity in MTPA
country	varchar	Country
state_code	varchar	State/province code
latitude	float	Latitude
longitude	float	Longitude
vulcan_status	varchar	Construction status
date_vulcan_earliest_online	date	Earliest predicted online date
date_vulcan_latest_online	date	Latest predicted online date
entity_name	varchar	Developer/owner entity

vdl.project_rankings

Construction likelihood rankings (0-7 scale) with conservative and seasonal forecast dates for all monitored projects.

Important

This table requires deduplication by the latest date_updated per synmax_id.

Table ID: vdl.project_rankings

Sample Queries

-- Deduplicated high-likelihood projects
WITH latest_ranks AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_updated DESC) as rn
    FROM vdl.project_rankings
)
SELECT
    synmax_id,
    plant_id,
    final_rank,
    date_vulcan_proposed_v2_online
FROM latest_ranks
WHERE rn = 1
  AND final_rank >= 5
ORDER BY final_rank DESC
LIMIT 20;

-- Rank distribution
WITH latest_ranks AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_updated DESC) as rn
    FROM vdl.project_rankings
)
SELECT
    final_rank,
    COUNT(*) as project_count
FROM latest_ranks
WHERE rn = 1
GROUP BY final_rank
ORDER BY final_rank DESC;

Field Reference

Field Name	Data Type	Description
synmax_id	varchar	Unique SynMax identifier
plant_id	varchar	Plant identifier
final_rank	int	Construction likelihood rank (0-7, higher = more likely)
date_vulcan_proposed_v2_online	date	SynMax proposed online date
date_vulcan_conservative_online	date	Conservative online date estimate
date_vulcan_seasonal_online	date	Seasonal-adjusted online date
date_updated	date	Date rank was last updated (use for deduplication)

vdl.metadata_history

Historical snapshots of EIA generator metadata tracking capacity changes and schedule slippage over time.

Important

Requires deduplication by latest date_eia_updated per synmax_id.

Table ID: vdl.metadata_history

Sample Queries

-- Deduplicated latest metadata
WITH latest_metadata AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_eia_updated DESC) as rn
    FROM vdl.metadata_history
)
SELECT
    plant_name,
    technology,
    nameplate_capacity,
    state_code,
    date_planned_operations
FROM latest_metadata
WHERE rn = 1
ORDER BY date_planned_operations
LIMIT 20;

-- Projects with significant schedule slip
WITH latest_metadata AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_eia_updated DESC) as rn
    FROM vdl.metadata_history
)
SELECT
    plant_name,
    technology,
    nameplate_capacity,
    days_planned_operation_minus_first_seen_planned_operation as schedule_slip_days
FROM latest_metadata
WHERE rn = 1
  AND days_planned_operation_minus_first_seen_planned_operation > 180
ORDER BY 4 DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
synmax_id	varchar	Unique SynMax identifier
date_eia_updated	date	Date EIA data was updated (use for deduplication)
entity_id	varchar	Entity identifier
entity_name	varchar	Entity name
plant_id	varchar	Plant identifier
plant_name	varchar	Plant name
generator_id	varchar	Generator identifier
technology	varchar	Technology type
nameplate_capacity	float	Nameplate capacity
state_code	varchar	State code
county	varchar	County
latitude	float	Latitude
longitude	float	Longitude
plant_status	varchar	Current plant status
date_planned_operations	date	Planned operations date
date_operating	date	Actual operating date
days_planned_operation_minus_first_seen_planned_operation	int	Schedule slippage in days
date_first_seen	date	First seen in EIA data
date_last_seen	date	Last seen in EIA data

Daily Nat Gas Production (Datalink ID: 1007)

Datalink ID: 1007

Modeled daily dry gas production by sub-region (basin level) covering all major U.S. natural gas producing areas from January 2018 to present. Provides daily production rate estimates (Mcf/day) with 60-90 day timeliness advantage over state-reported monthly production.

hdl.daily_production

Daily dry gas production rates by 30 U.S. sub-regions at date-subregion grain. Values are in Mcf/day.

Note

Aggregation Rules: Use AVG() for time aggregation (monthly/quarterly) since values are daily rates. Use SUM() for spatial aggregation across sub-regions.

Table ID: hdl.daily_production

Sample Queries

-- Total U.S. daily production (spatial aggregation - use SUM)
SELECT
    date,
    SUM(dry_gas) / 1000000.0 as total_us_bcf_per_day
FROM hdl.daily_production
WHERE date >= '2024-01-01'
GROUP BY date
ORDER BY date
LIMIT 30;

-- Monthly basin production (time aggregation - use AVG)
SELECT
    DATE_TRUNC('month', CAST(date AS DATE)) as month,
    sub_region,
    AVG(dry_gas) / 1000000.0 as avg_daily_bcf
FROM hdl.daily_production
WHERE date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', CAST(date AS DATE)), sub_region
ORDER BY 1, 3 DESC
LIMIT 50;

Field Reference

Field Name	Data Type	Description
date	date	Production date
sub_region	varchar	Sub-region/basin name (30 U.S. sub-regions)
dry_gas	float	Dry gas production rate in Mcf/day (divide by 1,000,000 for Bcf/day)