Create an EDGAR, PUDL, and NOAA Unified Semantic Layer

Current prerequisite warning: PUDL registration is known to fail. The successful run below is historical and conflicts with that result. Verify PUDL registration on your instance before using this example.

This guide records a model created and queried over JDBC on 2026-10-01. Treat its outputs as historical examples.

Start with Connecting Codex to Zetaris. Connect and get a row containing 1 from SELECT 1; before registering sources or compiling the USL. The connection check does not depend on a sample database already existing.

The model is lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl. It links Dominion Energy SEC facts to PUDL generators through a SEC CIK/EIA utility crosswalk, then adds NOAA observations to 2025 state-level generation rows.

Follow these steps in order:

1. Connect.
2. Inspect existing registrations.
3. Register missing sources.
4. Check schemas and join keys.
5. Cache NOAA.
6. Verify weather coverage.
7. Compile the USL.
8. Activate its tables.
9. Query the joined result.
10. Run DQ separately.

What you will create

flowchart LR
    SEC[SEC EDGAR REST] --> Company[company]
    SEC --> Revenue[revenue_year]
    PUDL[PUDL crosswalk and generators] --> Utility[utility_state_year]
    NOAA[Cached NOAA station CSVs] --> Weather[weather_period]
    Utility -->|cik| Company
    Revenue -->|cik| Company
    Utility -->|revenue_year_key| Revenue
    Utility -->|weather_period_key for 2025| Weather

The PUDL crosswalk uses probabilistic matching and may contain errors or missing matches. Treat CIK-to-utility links as analytical associations, not proof of ownership. See the crosswalk documentation.

The recorded run had 37 observed days per station, 2025-01-01 through 2025-02-06. This is a partial station series, not a full-year or statewide weather measure; the USL keeps the date range and day count.

1. Connect first and establish execution scope

Follow the connection guide. The driver included in zetaris-platform uses these local defaults:

Use your instance's host and port if they differ. localhost is the JDBC client's machine, which may not be the agent's host.

Use Zetaris Lightning SQL through the supplied driver; do not start Spark or create a local SparkSession. No application code changes are needed.

Send one SQL statement per JDBC request and wait for completion before dependent statements. Keep the connection open through caching and its queries where possible. Step 7's four-table COMPILE USL ... DDL is one payload; do not split on semicolons.

Before SEC registration, replace YOUR_APP_NAME YOUR_CONTACT_EMAIL with a real application name and contact email. Do not reuse the database login or put credentials in SQL, source control, or reports.

2. Inspect existing registrations and choose a model name

Create only missing objects. Inspect matching existing registrations before proceeding.

SHOW LIGHTNING DATABASES;

For each existing database, inspect its tables:

SHOW LIGHTNING TABLES SEC_DATA;

SHOW LIGHTNING TABLES PUDL_S3;

SHOW LIGHTNING TABLES NOAA_GHCN_S3;

Run table listings only for databases that exist. SHOW DATASOURCES does not list all Lightning REST/file databases.

For a fresh model, use the namespace and name shown here. If it exists, inspect it first; IF NOT EXISTS does not update it. For a separate version, choose a new name and change it in every compile, activation, and verification statement. Do not remove existing models to run this guide.

3. Register the required sources

These are the minimum registrations from the successful run. See the PUDL, EDGAR, and NOAA scripts. Register only the tables used here.

3.1 SEC EDGAR revenue facts

Create the logical database only if absent:

CREATE LIGHTNING DATABASE SEC_DATA DESCRIBE BY "SEC EDGAR company facts REST source";

Register Dominion Energy, CIK 0000715957, after replacing the SEC User-Agent placeholder:

CREATE LIGHTNING REST TABLE dominion_revenue_facts FROM SEC_DATA REQUEST(
    endpoint "https://data.sec.gov/api/xbrl/companyconcept/CIK0000715957/us-gaap/Revenues.json",
    method "get",
    response_type "json",
    http_encoding "URLENCODED"
) HEADER (
    user-agent "YOUR_APP_NAME YOUR_CONTACT_EMAIL"
) BODY ();

This uses company-wide us-gaap:Revenues facts from SEC EDGAR. The response repeats periods across filings; step 8 selects full-year facts and the latest filing per period end.

3.2 PUDL utility mapping and generator history

Create the logical database only if absent:

CREATE LIGHTNING DATABASE PUDL_S3 DESCRIBE BY "Catalyst Cooperative PUDL S3 filestore source";

Register the SEC-to-EIA crosswalk:

CREATE LIGHTNING FILESTORE TABLE pudl_sec10k_utility_matches FROM PUDL_S3 FORMAT PARQUET OPTIONS (
  PATH "s3a://pudl.catalyst.coop/stable/core_sec10k__assn_sec10k_filers_and_eia_utilities.parquet",
  inferSchema "true",
  isS3BucketPublic "true",
  useS3PathStyleAccess "true",
  s3Endpoint "s3.us-west-2.amazonaws.com"
);

Register the generator history:

CREATE LIGHTNING FILESTORE TABLE pudl_eia_yearly_generators FROM PUDL_S3 FORMAT PARQUET OPTIONS (
  PATH "s3a://pudl.catalyst.coop/stable/out_eia__yearly_generators.parquet",
  inferSchema "true",
  isS3BucketPublic "true",
  useS3PathStyleAccess "true",
  s3Endpoint "s3.us-west-2.amazonaws.com"
);

The public-bucket path needs no AWS credentials. PUDL's stable alias can change; for reproducible results, use a verified release path and inspect its schemas. See PUDL data access.

3.3 NOAA station histories

Create the logical database only if absent:

CREATE LIGHTNING DATABASE NOAA_GHCN_S3 DESCRIBE BY "NOAA GHCN-Daily S3 filestore source";

Use the three station files below instead of scanning the full csv/by_year/2025.csv file. Each station file contains historical data and takes time to load into cache.

CREATE LIGHTNING FILESTORE TABLE noaa_va_airport FROM NOAA_GHCN_S3 FORMAT CSV OPTIONS (
  PATH "s3a://noaa-ghcn-pds/csv/by_station/USW00013740.csv",
  inferSchema "false",
  header "false",
  isS3BucketPublic "true",
  useS3PathStyleAccess "true",
  s3Endpoint "s3.us-east-1.amazonaws.com"
);

CREATE LIGHTNING FILESTORE TABLE noaa_nc_airport FROM NOAA_GHCN_S3 FORMAT CSV OPTIONS (
  PATH "s3a://noaa-ghcn-pds/csv/by_station/USW00013722.csv",
  inferSchema "false",
  header "false",
  isS3BucketPublic "true",
  useS3PathStyleAccess "true",
  s3Endpoint "s3.us-east-1.amazonaws.com"
);

CREATE LIGHTNING FILESTORE TABLE noaa_sc_airport FROM NOAA_GHCN_S3 FORMAT CSV OPTIONS (
  PATH "s3a://noaa-ghcn-pds/csv/by_station/USW00013880.csv",
  inferSchema "false",
  header "false",
  isS3BucketPublic "true",
  useS3PathStyleAccess "true",
  s3Endpoint "s3.us-east-1.amazonaws.com"
);

These are one-station series per state, not weather at every PUDL plant. The original run checked station IDs and names against the NOAA inventory.

4. Confirm schemas and the CIK-to-utility match

Inspect each source schema before compiling the USL:

DESCRIBE DATASOURCE TABLE SEC_DATA.dominion_revenue_facts;

DESCRIBE DATASOURCE TABLE PUDL_S3.pudl_sec10k_utility_matches;

DESCRIBE DATASOURCE TABLE PUDL_S3.pudl_eia_yearly_generators;

DESCRIBE DATASOURCE TABLE NOAA_GHCN_S3.noaa_va_airport;

Repeat the NOAA description for noaa_nc_airport and noaa_sc_airport. The recorded schema exposed _c0–_c7 as strings; activation uses _c1 for YYYYMMDD, _c2 for element, and _c3 for value. Adjust if your schema differs. Valid date filtering excludes a header row.

The PUDL crosswalk needs central_index_key and utility_id_eia. The generator table needs utility_id_eia, report_date, state, capacity_mw, and net_generation_mwh.

Check the SEC identity as data, not just as an inferred column:

SELECT cik, entityName FROM SEC_DATA.dominion_revenue_facts;

The recorded SEC CIK was numeric 715957; the USL pads it to 0000715957 to match PUDL's string key. Confirm both company name and CIK before using them as non-null keys.

Confirm the example's match and reporting coverage:

SELECT m.central_index_key, m.utility_id_eia,
       MAX(g.utility_name_eia) AS utility_name,
       g.report_date, g.state,
       COUNT(*) AS generator_count,
       SUM(g.net_generation_mwh) AS generation_mwh
FROM PUDL_S3.pudl_sec10k_utility_matches m
JOIN PUDL_S3.pudl_eia_yearly_generators g
  ON m.utility_id_eia = g.utility_id_eia
WHERE m.central_index_key = '0000715957'
  AND g.report_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
GROUP BY m.central_index_key, m.utility_id_eia, g.report_date, g.state
ORDER BY g.report_date, g.state;

The run matched CIK 0000715957 to EIA utility 5248 and returned nine state/year rows for NC, SC, and VA in 2023–2025. Resolve any different match before treating the foreign keys as valid. PUDL had 2026 rows with missing generation, so this example stops at 2025.

5. Cache the NOAA tables before aggregation

Execute each statement separately and wait for completion:

CACHE TABLE NOAA_GHCN_S3.noaa_va_airport;

CACHE TABLE NOAA_GHCN_S3.noaa_nc_airport;

CACHE TABLE NOAA_GHCN_S3.noaa_sc_airport;

All three cache commands completed in the recorded run; the first load took about 107 seconds.

Cache completion does not prove query correctness; run step 6. Persistence across reconnects or restarts is unverified, so treat this as temporary cache, not materialization. Keep the JDBC connection open where possible and reload as needed. SHOW CACHE TABLES does not prove explicit-cache membership; see the SQL companion.

6. Read the cached weather data and assess its period

SELECT state,
       COUNT(DISTINCT obs_date) AS observed_days,
       MIN(obs_date) AS from_date,
       MAX(obs_date) AS through_date,
       AVG(CASE WHEN element = 'TMAX' THEN CAST(data_value AS DOUBLE)/10.0 END) AS mean_tmax_c,
       AVG(CASE WHEN element = 'TMIN' THEN CAST(data_value AS DOUBLE)/10.0 END) AS mean_tmin_c,
       AVG(CASE WHEN element = 'PRCP' THEN CAST(data_value AS DOUBLE)/10.0 END) AS mean_daily_precip_mm
FROM (
  SELECT 'VA' AS state, _c1 AS obs_date, _c2 AS element, _c3 AS data_value FROM NOAA_GHCN_S3.noaa_va_airport
  UNION ALL
  SELECT 'NC' AS state, _c1 AS obs_date, _c2 AS element, _c3 AS data_value FROM NOAA_GHCN_S3.noaa_nc_airport
  UNION ALL
  SELECT 'SC' AS state, _c1 AS obs_date, _c2 AS element, _c3 AS data_value FROM NOAA_GHCN_S3.noaa_sc_airport
) obs
WHERE obs_date BETWEEN '20250101' AND '20251231'
  AND element IN ('TMAX','TMIN','PRCP')
  AND CAST(data_value AS INT) <> -9999
GROUP BY state
ORDER BY state;

The query divides temperatures and precipitation by 10 because NOAA stores tenths of °C and mm. mean_daily_precip_mm averages recorded precipitation days; it is not annual total rainfall. The query excludes -9999 but does not filter other NOAA quality flags.

Observed results from the cached aggregation:

Check current dates before using _2025_JANFEB. If the files cover another period, update the identifier in both weather and utility activations. Do not label a partial period as annual weather.

7. Compile and deploy all four table definitions together

Create the namespace if needed:

CREATE NAMESPACE IF NOT EXISTS lightning.metastore.edgar_pudl_demo;

Send this entire block as one JDBC statement. The semicolons terminate the embedded table definitions; do not split them into independent top-level requests.

COMPILE USL IF NOT EXISTS edgar_pudl_noaa_usl DEPLOY NAMESPACE lightning.metastore.edgar_pudl_demo DDL
CREATE TABLE company (
  cik          varchar(10) NOT NULL PRIMARY KEY,
  company_name varchar(200) NOT NULL
);

CREATE TABLE revenue_year (
  revenue_year_key varchar(20) NOT NULL PRIMARY KEY,
  cik              varchar(10) NOT NULL FOREIGN KEY REFERENCES company(cik),
  report_year      integer NOT NULL,
  period_start     date,
  period_end       date,
  revenue_usd      double,
  filing_date      date,
  accession_number varchar(30)
);

CREATE TABLE weather_period (
  weather_period_key      varchar(40) NOT NULL PRIMARY KEY,
  state                   varchar(2) NOT NULL,
  station_id              varchar(11) NOT NULL,
  station_name            varchar(60),
  period_start            date,
  period_end              date,
  observed_days           integer,
  valid_measurement_count integer,
  mean_daily_tmax_c       double,
  mean_daily_tmin_c       double,
  mean_daily_precip_mm    double
);

CREATE TABLE utility_state_year (
  utility_state_year_key varchar(40) NOT NULL PRIMARY KEY,
  cik                   varchar(10) NOT NULL FOREIGN KEY REFERENCES company(cik),
  revenue_year_key      varchar(20) FOREIGN KEY REFERENCES revenue_year(revenue_year_key),
  weather_period_key    varchar(40) FOREIGN KEY REFERENCES weather_period(weather_period_key),
  utility_id_eia        bigint NOT NULL,
  report_year           integer NOT NULL,
  state                 varchar(2) NOT NULL,
  generator_count       integer,
  net_generation_mwh    double,
  installed_capacity_mw double
);

Compile/deploy confirms the definitions were accepted, not that activated tables return rows.

8. Activate the tables in dependency order

The recorded version activates each source separately and adds cross-source foreign keys. A direct raw SEC/PUDL join hit an internal planning error.

COALESCE satisfies the declared NOT NULL schema when source expressions are nullable. It does not validate keys. Check CIK, state, and report year; blank keys or zero IDs are not valid records.

8.1 Company from SEC

ACTIVATE USL TABLE lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.company AS
SELECT
    COALESCE(LPAD(CAST(cik AS STRING), 10, '0'), '') AS cik,
    COALESCE(MAX(entityName), '') AS company_name
FROM SEC_DATA.dominion_revenue_facts
GROUP BY cik;

8.2 Annual revenue from SEC

ACTIVATE USL TABLE lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.revenue_year AS
SELECT
    COALESCE(CONCAT(cik, '_', CAST(report_year AS STRING)), '') AS revenue_year_key,
    COALESCE(cik, '') AS cik,
    COALESCE(report_year, 0) AS report_year,
    period_start,
    period_end,
    revenue_usd,
    filing_date,
    accession_number
FROM (
    SELECT
        LPAD(CAST(cik AS STRING), 10, '0') AS cik,
        YEAR(CAST(fact.`end` AS DATE)) AS report_year,
        CAST(fact.start AS DATE) AS period_start,
        CAST(fact.`end` AS DATE) AS period_end,
        CAST(fact.val AS DOUBLE) AS revenue_usd,
        CAST(fact.filed AS DATE) AS filing_date,
        fact.accn AS accession_number,
        ROW_NUMBER() OVER (
            PARTITION BY fact.`end`
            ORDER BY fact.filed DESC, fact.accn DESC
        ) AS rn
    FROM SEC_DATA.dominion_revenue_facts
    LATERAL VIEW explode(units.USD) AS fact
    WHERE fact.form = '10-K'
      AND fact.fp = 'FY'
      AND DATEDIFF(CAST(fact.`end` AS DATE), CAST(fact.start AS DATE)) BETWEEN 330 AND 400
      AND YEAR(CAST(fact.`end` AS DATE)) BETWEEN 2023 AND 2025
) annual_facts
WHERE rn = 1;

fp = 'FY' also matches quarterly contexts in annual filings. The duration filter selects full-year facts; the window chooses the latest filing per period end. This example has one company. For multiple companies, partition by CIK and period end. report_year uses period-end year; check non-calendar fiscal years against PUDL's calendar year.

8.3 Partial weather period from cached NOAA data

ACTIVATE USL TABLE lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.weather_period AS
SELECT
    COALESCE(CONCAT(state, '_2025_JANFEB'), '') AS weather_period_key,
    COALESCE(state, '') AS state,
    COALESCE(station_id, '') AS station_id,
    COALESCE(station_name, '') AS station_name,
    CAST(CONCAT(SUBSTR(MIN(obs_date),1,4),'-',SUBSTR(MIN(obs_date),5,2),'-',SUBSTR(MIN(obs_date),7,2)) AS DATE) AS period_start,
    CAST(CONCAT(SUBSTR(MAX(obs_date),1,4),'-',SUBSTR(MAX(obs_date),5,2),'-',SUBSTR(MAX(obs_date),7,2)) AS DATE) AS period_end,
    COUNT(DISTINCT obs_date) AS observed_days,
    COUNT(*) AS valid_measurement_count,
    AVG(CASE WHEN element='TMAX' THEN CAST(data_value AS DOUBLE)/10.0 END) AS mean_daily_tmax_c,
    AVG(CASE WHEN element='TMIN' THEN CAST(data_value AS DOUBLE)/10.0 END) AS mean_daily_tmin_c,
    AVG(CASE WHEN element='PRCP' THEN CAST(data_value AS DOUBLE)/10.0 END) AS mean_daily_precip_mm
FROM (
    SELECT 'VA' AS state, 'USW00013740' AS station_id, 'RICHMOND INTL AP' AS station_name, _c1 AS obs_date, _c2 AS element, _c3 AS data_value FROM NOAA_GHCN_S3.noaa_va_airport
    UNION ALL
    SELECT 'NC' AS state, 'USW00013722' AS station_id, 'RALEIGH AP' AS station_name, _c1 AS obs_date, _c2 AS element, _c3 AS data_value FROM NOAA_GHCN_S3.noaa_nc_airport
    UNION ALL
    SELECT 'SC' AS state, 'USW00013880' AS station_id, 'CHARLESTON INTL AP' AS station_name, _c1 AS obs_date, _c2 AS element, _c3 AS data_value FROM NOAA_GHCN_S3.noaa_sc_airport
) obs
WHERE obs_date BETWEEN '20250101' AND '20251231'
  AND element IN ('TMAX','TMIN','PRCP')
  AND CAST(data_value AS INT) <> -9999
GROUP BY state, station_id, station_name;

8.4 PUDL generation linked to SEC and weather keys

ACTIVATE USL TABLE lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.utility_state_year AS
SELECT
    COALESCE(CONCAT(m.central_index_key, '_', CAST(YEAR(g.report_date) AS STRING), '_', g.state), '') AS utility_state_year_key,
    COALESCE(m.central_index_key, '') AS cik,
    COALESCE(CONCAT(m.central_index_key, '_', CAST(YEAR(g.report_date) AS STRING)), '') AS revenue_year_key,
    CASE WHEN YEAR(g.report_date) = 2025 THEN CONCAT(g.state, '_2025_JANFEB') END AS weather_period_key,
    COALESCE(m.utility_id_eia, 0) AS utility_id_eia,
    COALESCE(YEAR(g.report_date), 0) AS report_year,
    COALESCE(g.state, '') AS state,
    COUNT(*) AS generator_count,
    SUM(g.net_generation_mwh) AS net_generation_mwh,
    SUM(g.capacity_mw) AS installed_capacity_mw
FROM PUDL_S3.pudl_sec10k_utility_matches m
JOIN PUDL_S3.pudl_eia_yearly_generators g
    ON m.utility_id_eia = g.utility_id_eia
WHERE m.central_index_key = '0000715957'
  AND g.report_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
GROUP BY m.central_index_key, m.utility_id_eia, YEAR(g.report_date), g.state;

generator_count counts source rows at plant/generator/year grain. SUM(net_generation_mwh) ignores missing measurements, so the count does not mean every generator reported generation. The weather key is null for 2023–2024 because no weather period is provided for those years.

All four activations returned registered. This confirms registration, not successful query execution.

9. Query the activated USL and verify the join

First read the weather table:

SELECT *
FROM lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.weather_period;

The recorded query returned three station/state rows, each with 37 days and 111 valid measurements.

Then run the integrated query:

SELECT u.report_year, u.state,
       u.generator_count, u.net_generation_mwh, u.installed_capacity_mw,
       r.revenue_usd,
       w.station_id, w.period_start, w.period_end, w.observed_days
FROM lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.utility_state_year u
JOIN lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.revenue_year r
  ON u.revenue_year_key = r.revenue_year_key
LEFT JOIN lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.weather_period w
  ON u.weather_period_key = w.weather_period_key
ORDER BY u.report_year, u.state;

The recorded join returned nine rows: NC, SC, and VA for 2023–2025. Weather columns were null for 2023–2024 and populated for 2025.

SEC company revenue repeats on every state row; do not sum it across states. The run returned USD 14,393,000,000 for 2023 and USD 14,459,000,000 for 2024. Keep company/year and utility/state/year at separate grains.

These are historical comparison values, not guaranteed outputs from a later PUDL release or SEC filing. Check source tables when results differ.

10. Run DQ separately and record its result

Primary and foreign keys generate DQ rules. To run the utility table's checks:

RUN DQ TABLE lightning.metastore.edgar_pudl_demo.edgar_pudl_noaa_usl.utility_state_year;

Wait for rule results before marking DQ passed. The command returned no result within about three minutes and the JDBC client was interrupted. DQ remains unverified. The nine-row SELECT is separate evidence.

Use a bounded wait. If you cancel, check whether the server operation stopped; disconnecting a client does not prove cancellation. Do not relaunch DQ while an earlier command may still run.

Troubleshooting and reruns

This exercise created no USL materialization or durable cache. The earlier edgar_pudl_energy_usl remained because its removal command failed in that attempt. Reuse or version existing models; do not remove another user's objects.

Suggested instruction for an AI agent

For an AI task, attach this and the connection guide and provide credentials through your approved mechanism. After verifying the connection, use this prompt:

Follow the EDGAR/PUDL/NOAA USL guide against the connected Zetaris instance.
Inspect and reuse existing source registrations. Register only missing objects.
Use the Zetaris JDBC driver and Lightning SQL, one statement per request.
Send the complete COMPILE USL DDL block as one payload.
Cache the three NOAA airport station tables before aggregation, then check
their actual date coverage. Keep partial weather periods explicit.
Activate each source separately and connect the USL tables with the documented
CIK/year and weather-period keys. Verify the weather SELECT and joined SELECT.
Treat registered activations, readable rows, and completed DQ as separate outcomes.
Do not edit application code, remove existing models, or claim DQ passed without
rule results. At the end, report the object names, rows returned, weather dates,
commands that failed or timed out, and the steps you took.

Evidence boundary for this guide

The recorded JDBC run returned a row for SELECT 1, completed three NOAA cache commands, aggregated three stations, compiled the USL, registered four activations, returned three weather rows, and joined nine rows. DQ did not complete within the bounded wait. No local Spark session or application-code changes were used.

The guide reconstructs that run; it has not been rerun from an empty instance. Its successful PUDL result conflicts with the current known failure noted above.