## Summary
This is A8 plan unit U03 in full: SURTR-1536, plus SURTR-1537, which Keval squash-merged into this branch from #2085. It adds the runner every A8 admissions parity mart plugs into, the EduCRM provenance view, and both G1 marts.
- New runner pipelines/runners/mart-aerie-admissions-refresh (Lambda, bundling: true, src/requirements.txt).
- Triggers: on_pipeline_success of sales-educrm-mart-sync (every 30 min) and mart-aerie-hubspot-refresh (6-hourly), with forward_upstream_execution_context.
- Upstream check: the handler confirms with describe_execution that the upstream run SUCCEEDED on that pipeline's state machine. On-demand runs are also accepted, and may name a subset of procedures.
- What it runs: it CALLs each procedure in REFRESH_PROCEDURES (one line in pipeline.json; later units append to it), then checks the published mart read-only: non-empty, unique mart_row_id, one source_run_id.
- Failure handling: a failed procedure makes the run partial_failure and the others still run. If every procedure fails, the run fails. It also fails if a write's acceptance cannot be ruled out: every Data API write carries a ClientToken, and an ambiguous CALL submission raises UnknownStatementOutcomeError rather than being recorded as a procedure failure.
- DDL is in pipelines/cdk/sql/mart_education/ as 006-011, the schema's numbered out-of-band deploy directory. scripts/apply_ddl.py applies exactly those six files, in order.
- 006 v_aerie_educrm_observed_publication gives, per EduCRM table, the latest SUCCESS or PARTIAL sales-educrm-mart-sync run whose per-table result is success, plus the latest started run of any status. It is built with UNPIVOT over results_by_table. rows_loaded must be a plain integer.
- 007 is the shared owner-only writer mutex.
- 008/009 aerie_admissions_program (slug aerie-admissions-program) is Aerie's queryPrograms SQL, copied verbatim from reference.ts:132-148, including the CURRENT_DATE prior-year predicate. It adds school_year, canonical_source_run_id and lineage. Its procedure requires the observed EduCRM run to be the latest started run (so no later writer can have replaced the table), rows_loaded to equal the full snapshot, and the run to be under 24 hours old.
- 010/011 aerie_admissions_program_directory (slug aerie-admissions-program-directory) is Aerie's queryHubspotPrograms SQL, copied verbatim from hubspot.ts:75-98. Its lineage is the directory's single hubspot_publication_run_id / hubspot_source_published_at.
- Both procedures build the candidate in temp tables and publish with DELETE + named-column INSERT in the CALL transaction, with no TRUNCATE. They fail closed on an empty candidate, bad lineage, and a duplicate mart_row_id.
- mart_row_id is the MD5 of the key and its occurrence number, ordered by every published column.
- Unresolved and duplicate rows are published as-is, so Aerie's own mapper still throws exactly as on the legacy read.
- PIPELINE §13 exception (WAREHOUSE §2.2 and PIPELINE §7) is documented in the README with owner, risk, controls and follow-on. Reader grants are left to the DBA; the DDL only protects the writer.
## Business Value
- It completes the Surtr side of G1, the first A8 group. programs is the hard prerequisite of every Aerie runRefreshCycle, and it plus programDirectory (which also feeds A1) can now be read through the Surtr Gateway with Aerie's unchanged row mappers. This moves Aerie off direct EduCRM reads from the EC2 analytics worker, which is the SURTR-735 quarterly commitment and what unblocks tearing the worker down.
- Parity is provable. Tests pin Aerie's SQL, the reconciliation EXCEPT is 0 in both directions, and every row carries lineage.
- Later A8 units reuse this foundation. The runner, the provenance view and the mutex serve about 10 more EduCRM marts, which then add only a procedure and one REFRESH_PROCEDURES entry.
## Manual Effort Estimate
About 18 focused hours (roughly 2.5 days) to build by hand without AI: reading both Aerie queries and the EduCRM run-log shape, the SUPER-aware provenance view, two procedures, the runner and client hardening, the tests and the reconciliation. Keval: please confirm or adjust.
## Testing / evidence
- uv run pytest: 85 passed. This covers the handler, the pipeline contract (every env var src/ reads is declared), the SQL contracts, apply_ddl and the Redshift client, including ambiguous-submission cases.
- The SQL-contract tests pin both Aerie queries and assert that each procedure's candidate is exactly that SQL plus the appended lineage columns, and that the reconciliation uses the same candidate.
- The pinned text matches Aerie origin/main byte for byte, at both 92fd47992 and 3d4fe1a97.
- Ruff 0.15.22: ruff check and ruff format --check are clean. CI is green.
- Read-only reconciliation was run with psql as CQL_download_OM (SELECT only), query 1 of each file (Aerie SQL vs the procedure's candidate):
| mart | aerie rows | candidate rows | aerie − candidate | candidate − aerie |
|---|---|---|---|---|
| aerie_admissions_program | 90 | 90 | 0 | 0 |
| aerie_admissions_program_directory | 113 | 113 | 0 | 0 |
Query 2, against the published marts, errors with "relation does not exist" as expected, because no DDL has been applied.
- Negative controls on the same multiset shape:
- dropping a row gives 1 / 0;
- duplicating a row gives 1 / 1.
- Procedure expressions run read-only:
- The view resolves 49 tables, with 0 unparsable counts. For mart_all_program, the observed run equals the latest run, and rows_loaded 180 = snapshot 180.
- The integer parser returns 180 → 180, and 89.9 / 1.8e2 / "180" → NULL.
- Stamping gives 90 and 113 unique mart_row_ids.
- The directory has 1 publication run id and 0 rows missing lineage.
- scripts/apply_ddl.py --dry-run (6 files, 43 statements, in apply order) is below:
<details><summary>apply_ddl.py --dry-run output</summary>
-- 006_v_aerie_educrm_observed_publication.sql: 3 statement(s)-- Shared observed-provenance helper for the mart-aerie-admissions-refresh
-- procedures (A8 plan §3.1 rule 2). sales-educrm-mart-sync republishes each
-- EduCRM table in its own transaction and reports per-table outcomes only in
-- its run summary, so there is no atomic row-level lineage link. This view
-- exposes, per Redshift table, the latest SUCCESS/PARTIAL run whose per-table
-- result is 'success', together with the latest sales-educrm-mart-sync run to
-- have started (any status, RUNNING included: CreateRunRecord inserts it
-- before the run touches a table).
--
-- A procedure pins the observed run only when it is also that latest run: no
-- later writer can then have replaced the table, so, read in the same
-- transaction snapshot, the table is that run's publication. The procedure
-- still requires rows_loaded to equal the snapshot count.
-- Generalises the inline pattern in sp_refresh_aerie_program_directory
-- (mart-aerie-hubspot-refresh/ddl/20260819_incident_stopgap_duplicate_school_year.sql).
--
-- Placement (WAREHOUSE §10): writer-internal helper for mart procedures only.
-- It captures no source extraction (not staging) and holds no business
-- meaning (not core), so it lives beside its only readers in mart_education.
CREATE OR REPLACE VIEW mart_education.v_aerie_educrm_observed_publication AS
WITH educrm_runs AS (
SELECT
run_id,
status,
started_at,
ended_at,
output_summary
FROM staging_other.pipeline_runs_prod
WHERE pipeline_id = 'sales-educrm-mart-sync'
),
latest_run AS (
SELECT
run_id::VARCHAR(36) AS latest_run_id,
status::VARCHAR(20) AS latest_run_status
FROM (
SELECT
run_id,
status,
ROW_NUMBER() OVER (ORDER BY started_at DESC, run_id DESC) AS recency
FROM educrm_runs
) ranked
WHERE recency = 1
),
completed_runs AS (
SELECT
run_id,
ended_at,
CASE WHEN CAN_JSON_PARSE(output_summary) THEN JSON_PARSE(output_summary) END AS output_summary_super
FROM educrm_runs
WHERE status IN ('SUCCESS', 'PARTIAL')
AND ended_at IS NOT NULL
),
table_results AS (
SELECT
run.run_id,
run.ended_at,
table_key,
table_result
FROM completed_runs run, UNPIVOT run.output_summary_super.results_by_table AS table_result AT table_key
),
successful_table_results AS (
SELECT
table_key::VARCHAR(256) AS educrm_table,
table_result.redshift_table::VARCHAR(256) AS redshift_table,
run_id::VARCHAR(36) AS observed_run_id,
ended_at AT TIME ZONE 'UTC' AS observed_completed_at,
-- Only a plain non-negative integer is a row count; anything else
-- (89.9, 1.8e2, a string) becomes NULL so the procedure fails closed
-- instead of a cast truncating it into a matching count.
CASE
WHEN JSON_TYPEOF(table_result.rows_loaded) = 'number'
AND JSON_SERIALIZE(table_result.rows_loaded) ~ '^[0-9]{1,18}$'
THEN JSON_SERIALIZE(table_result.rows_loaded)::BIGINT
END AS observed_row_count,
ROW_NUMBER() OVER (
PARTITION BY table_result.redshift_table::VARCHAR(256)
ORDER BY ended_at DESC, run_id DESC
) AS recency
FROM table_results
WHERE table_result.status::VARCHAR(32) = 'success'
AND table_result.redshift_table::VARCHAR(256) IS NOT NULL
)
SELECT
observed.educrm_table,
observed.redshift_table,
observed.observed_run_id,
observed.observed_completed_at,
observed.observed_row_count,
latest.latest_run_id,
latest.latest_run_status
FROM successful_table_results observed
CROSS JOIN latest_run latest
WHERE observed.recency = 1;
COMMENT ON VIEW mart_education.v_aerie_educrm_observed_publication IS
'Purpose: writer-internal observed provenance for Aerie admissions parity mart procedures (mart-aerie-admissions-refresh); not a consumer contract. Grain: one EduCRM Redshift table written by sales-educrm-mart-sync. Key: redshift_table. observed_run_id is the latest SUCCESS or PARTIAL run whose per-table result is success; latest_run_id/latest_run_status describe the most recently started run of any status. This is observed provenance, not an atomic publication link: a procedure must require observed_run_id = latest_run_id and observed_row_count = the snapshot it reads, in one transaction.';
ALTER TABLE mart_education.v_aerie_educrm_observed_publication
OWNER TO "CQL_download_OM";
-- 007_aerie_admissions_refresh_writer_mutex.sql: 5 statement(s)
-- Owner-only mutex shared by every mart-aerie-admissions-refresh procedure.
-- Each procedure locks it first, so overlapping runs (both upstream triggers
-- can fire together) publish one at a time. It is locked instead of the
-- target mart, which is locked only for the final DELETE + INSERT.
CREATE TABLE IF NOT EXISTS mart_education.aerie_admissions_refresh_writer_mutex (
lock_scope VARCHAR(64) NOT NULL
)
DISTSTYLE ALL;
COMMENT ON TABLE mart_education.aerie_admissions_refresh_writer_mutex IS
'Owner-only writer mutex for the mart-aerie-admissions-refresh stored procedures. It contains no data and is not a consumer contract.';
REVOKE ALL ON mart_education.aerie_admissions_refresh_writer_mutex FROM PUBLIC;
REVOKE ALL ON mart_education.aerie_admissions_refresh_writer_mutex FROM GROUP team_engineers;
ALTER TABLE mart_education.aerie_admissions_refresh_writer_mutex
OWNER TO "CQL_download_OM";
-- 008_aerie_admissions_program.sql: 14 statement(s)
-- Canonical DDL for mart_education.aerie_admissions_program (A8 unit U03,
-- Gateway slug aerie-admissions-program). Sole writer:
-- mart_education.sp_refresh_aerie_admissions_program().
--
-- Query-shaped parity mart: columns are exactly the output aliases of Aerie's
-- queryPrograms SQL (sync/src/analytics/queries/reference.ts), with source
-- types kept (SUPER included) so pg and Gateway readers serialise them the
-- same way. school_year and canonical_source_run_id are lineage additions.
CREATE TABLE IF NOT EXISTS mart_education.aerie_admissions_program (
program_public_id VARCHAR(64),
source_program_id VARCHAR(256),
source_program_code SUPER,
program_code VARCHAR(512),
program_name VARCHAR(512),
is_expansion BOOLEAN,
owner_name SUPER,
grade_levels SUPER,
school_address SUPER,
school_status SUPER,
show_in_dashboard BOOLEAN,
school_year BIGINT,
canonical_source_run_id VARCHAR(128),
mart_row_id VARCHAR(32) NOT NULL,
source_run_id VARCHAR(128) NOT NULL,
source_published_at TIMESTAMPTZ NOT NULL,
refreshed_at TIMESTAMP NOT NULL,
created_by VARCHAR(128) NOT NULL,
PRIMARY KEY (mart_row_id)
)
DISTSTYLE ALL
SORTKEY (mart_row_id);
COMMENT ON TABLE mart_education.aerie_admissions_program IS
'Purpose: Surtr publication of the rows Aerie''s queryPrograms reads (EduCRM mart_all_program LEFT JOIN core_education.dim_program on the HubSpot Program id), so Aerie can read them through the Surtr Gateway with its unchanged row mapper. Grain: one output row of that SQL for the previous calendar school year (school_year = EXTRACT(YEAR FROM CURRENT_DATE) - 1, evaluated at refresh); normally one EduCRM program_id. Key: mart_row_id (MD5 of source_program_id plus its occurrence number). source_program_id is expected unique but not enforced: duplicate or unresolved rows (NULL program_public_id) are published as-is so Aerie''s own identity checks still fail closed. Lineage: source_run_id/source_published_at are the observed sales-educrm-mart-sync run (v_aerie_educrm_observed_publication), not an atomic publication link. Sensitive data: owner_name holds a staff member''s name. Full snapshot replaced atomically by mart_education.sp_refresh_aerie_admissions_program.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.program_public_id IS
'core_education.dim_program.program_id for the active HubSpot Program whose hubspot_program_id equals source_program_id; NULL when unresolved.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.source_program_id IS
'EduCRM program_id as text (TRIM(BOTH ''"'' FROM program_id::varchar)); this is the HubSpot Program id.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.program_code IS
'dim_program.program_name (the canonical program code). NULL when unresolved.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.program_name IS
'dim_program.display_name. NULL when unresolved.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.school_year IS
'EduCRM school_year (starting calendar year) of the published row. Lineage addition; not read by Aerie.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.canonical_source_run_id IS
'dim_program.hubspot_publication_run_id of the joined canonical Program row; NULL when unresolved. Lineage addition; not read by Aerie.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.mart_row_id IS
'Deterministic row key: MD5 of source_program_id and its occurrence number. Gateway orderBy for total-order paging. Not stable across a change to the row''s key.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.source_run_id IS
'Observed sales-educrm-mart-sync run_id whose mart_all_program rows_loaded equalled the snapshot this publication read.';
COMMENT ON COLUMN mart_education.aerie_admissions_program.source_published_at IS
'End time (UTC) of the observed sales-educrm-mart-sync run.';
ALTER TABLE mart_education.aerie_admissions_program
OWNER TO "CQL_download_OM";
-- Writer protection only. Reader access is provisioned by the Redshift DBA
-- (PIPELINE §13); the Surtr Gateway reads as the owner.
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
ON mart_education.aerie_admissions_program FROM PUBLIC;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
ON mart_education.aerie_admissions_program FROM GROUP team_engineers;
-- 009_sp_refresh_aerie_admissions_program.sql: 4 statement(s)
-- Sole writer for mart_education.aerie_admissions_program (WAREHOUSE §7.1).
--
-- The candidate is Aerie's queryPrograms SQL, copied verbatim from
-- sync/src/analytics/queries/reference.ts:132-148 at Aerie 92fd47992. Two
-- changes only: MART_ALL_PROGRAM_YEAR_PREDICATE is expanded in place, and two
-- lineage columns are appended to the select list. Aerie's identity and
-- duplicate checks stay in Aerie's row mapper, so this procedure publishes
-- unresolved or duplicate rows as-is instead of rejecting them.
--
-- Fails closed on: no or incomplete observed EduCRM run; a later
-- sales-educrm-mart-sync run (any status, including one still running) that
-- could have republished the table since; an observation older than 24 hours;
-- a snapshot count that differs from the observed rows_loaded; an empty
-- candidate; and a duplicate mart_row_id. The DELETE + INSERT publish stays
-- inside the CALL transaction; never TRUNCATE (it commits implicitly).
CREATE OR REPLACE PROCEDURE mart_education.sp_refresh_aerie_admissions_program()
AS $$
DECLARE
v_observation_count BIGINT;
v_observed_run_id VARCHAR(36);
v_observed_completed_at TIMESTAMPTZ;
v_observed_row_count BIGINT;
v_latest_run_id VARCHAR(36);
v_latest_run_status VARCHAR(20);
v_snapshot_row_count BIGINT;
v_candidate_count BIGINT;
v_duplicate_count BIGINT;
v_school_year BIGINT;
v_refreshed_at TIMESTAMP;
BEGIN
LOCK TABLE mart_education.aerie_admissions_refresh_writer_mutex;
v_refreshed_at := GETDATE();
v_school_year := EXTRACT(YEAR FROM CURRENT_DATE) - 1;
SELECT COUNT(*) INTO v_observation_count
FROM mart_education.v_aerie_educrm_observed_publication
WHERE redshift_table = 'staging_education.sales_educrm_wh_mart_all_program'
AND educrm_table = 'educrm_wh.mart_all_program';
IF v_observation_count <> 1 THEN
RAISE EXCEPTION
'aerie_admissions_program: expected one successful EduCRM mart_all_program observation; found %',
v_observation_count;
END IF;
SELECT observed_run_id, observed_completed_at, observed_row_count, latest_run_id, latest_run_status
INTO v_observed_run_id, v_observed_completed_at, v_observed_row_count, v_latest_run_id, v_latest_run_status
FROM mart_education.v_aerie_educrm_observed_publication
WHERE redshift_table = 'staging_education.sales_educrm_wh_mart_all_program'
AND educrm_table = 'educrm_wh.mart_all_program';
IF NULLIF(BTRIM(v_observed_run_id), '') IS NULL
OR v_observed_completed_at IS NULL
OR v_observed_row_count IS NULL
OR v_observed_row_count <= 0 THEN
RAISE EXCEPTION
'aerie_admissions_program: EduCRM observation is incomplete (run %, completed %, rows %)',
v_observed_run_id, v_observed_completed_at, v_observed_row_count;
END IF;
-- The observed run must also be the most recently started EduCRM run.
-- Otherwise a later run (still running, failed, or one that failed this
-- table) may have republished it, and a matching row count would not prove
-- which run's rows are there. Read in this same transaction snapshot, no
-- later writer means the table is the observed run's publication.
IF v_latest_run_id IS NULL OR v_observed_run_id <> v_latest_run_id THEN
RAISE EXCEPTION
'aerie_admissions_program: EduCRM run % (status %) started after observed run %; the table may hold newer rows',
v_latest_run_id, v_latest_run_status, v_observed_run_id;
END IF;
-- sales-educrm-mart-sync runs every 30 minutes. An observation this old
-- can no longer vouch for the table it describes.
IF v_observed_completed_at < SYSDATE - INTERVAL '24 hours' THEN
RAISE EXCEPTION
'aerie_admissions_program: latest EduCRM observation % completed at % is older than 24 hours',
v_observed_run_id, v_observed_completed_at;
END IF;
-- Reconcile the full snapshot (every school year) to the observed run
-- before the year predicate narrows it.
SELECT COUNT(*) INTO v_snapshot_row_count
FROM staging_education.sales_educrm_wh_mart_all_program;
IF v_snapshot_row_count <> v_observed_row_count THEN
RAISE EXCEPTION
'aerie_admissions_program: EduCRM snapshot has % rows but observed run % reported %',
v_snapshot_row_count, v_observed_run_id, v_observed_row_count;
END IF;
DROP TABLE IF EXISTS tmp_aerie_admissions_program_query;
CREATE TEMP TABLE tmp_aerie_admissions_program_query AS
-- aerie-sql:begin
SELECT
canonical_program.program_id AS program_public_id,
TRIM(BOTH '"' FROM p.program_id::varchar) AS source_program_id,
p.program_code AS source_program_code,
canonical_program.program_name AS program_code,
canonical_program.display_name AS program_name,
p.is_expansion,
p.owner_name,
p.grade_levels,
p.school_address,
p.school_status,
p.show_in_dashboard,
-- A8 lineage additions (not in Aerie's select list):
p.school_year,
canonical_program.hubspot_publication_run_id AS canonical_source_run_id
FROM staging_education.sales_educrm_wh_mart_all_program p
LEFT JOIN core_education.dim_program canonical_program
ON canonical_program.hubspot_program_id = TRIM(BOTH '"' FROM p.program_id::varchar)
AND canonical_program.hubspot_source_presence_status = 'active'
WHERE p.school_year = EXTRACT(YEAR FROM CURRENT_DATE) - 1
-- aerie-sql:end
;
DROP TABLE IF EXISTS tmp_aerie_admissions_program;
CREATE TEMP TABLE tmp_aerie_admissions_program (LIKE mart_education.aerie_admissions_program);
INSERT INTO tmp_aerie_admissions_program (
program_public_id,
source_program_id,
source_program_code,
program_code,
program_name,
is_expansion,
owner_name,
grade_levels,
school_address,
school_status,
show_in_dashboard,
school_year,
canonical_source_run_id,
mart_row_id,
source_run_id,
source_published_at,
refreshed_at,
created_by
)
SELECT
q.program_public_id,
q.source_program_id,
q.source_program_code,
q.program_code,
q.program_name,
q.is_expansion,
q.owner_name,
q.grade_levels,
q.school_address,
q.school_status,
q.show_in_dashboard,
q.school_year,
q.canonical_source_run_id,
MD5(
'aerie_admissions_program|'
|| COALESCE('v' || q.source_program_id, 'n')
|| '|'
|| (ROW_NUMBER() OVER (
PARTITION BY q.source_program_id
-- Every output column, so rows that share a key are numbered
-- the same way on every refresh; only identical rows tie.
ORDER BY q.program_public_id, q.program_code, q.program_name,
JSON_SERIALIZE(q.source_program_code), q.is_expansion,
JSON_SERIALIZE(q.owner_name), JSON_SERIALIZE(q.grade_levels),
JSON_SERIALIZE(q.school_address), JSON_SERIALIZE(q.school_status),
q.show_in_dashboard, q.school_year, q.canonical_source_run_id
))::VARCHAR
),
v_observed_run_id,
v_observed_completed_at,
v_refreshed_at,
'mart-aerie-admissions-refresh/v1'
FROM tmp_aerie_admissions_program_query q;
SELECT COUNT(*) INTO v_candidate_count FROM tmp_aerie_admissions_program;
IF v_candidate_count = 0 THEN
RAISE EXCEPTION
'aerie_admissions_program: candidate is empty for school_year % (observed run %)',
v_school_year, v_observed_run_id;
END IF;
SELECT COUNT(*) INTO v_duplicate_count
FROM (
SELECT mart_row_id
FROM tmp_aerie_admissions_program
GROUP BY mart_row_id
HAVING COUNT(*) > 1
) duplicates;
IF v_duplicate_count <> 0 THEN
RAISE EXCEPTION 'aerie_admissions_program: candidate has % duplicate mart_row_id value(s)', v_duplicate_count;
END IF;
LOCK TABLE mart_education.aerie_admissions_program;
DELETE FROM mart_education.aerie_admissions_program;
INSERT INTO mart_education.aerie_admissions_program (
program_public_id,
source_program_id,
source_program_code,
program_code,
program_name,
is_expansion,
owner_name,
grade_levels,
school_address,
school_status,
show_in_dashboard,
school_year,
canonical_source_run_id,
mart_row_id,
source_run_id,
source_published_at,
refreshed_at,
created_by
)
SELECT
program_public_id,
source_program_id,
source_program_code,
program_code,
program_name,
is_expansion,
owner_name,
grade_levels,
school_address,
school_status,
show_in_dashboard,
school_year,
canonical_source_run_id,
mart_row_id,
source_run_id,
source_published_at,
refreshed_at,
created_by
FROM tmp_aerie_admissions_program;
IF (SELECT COUNT(*) FROM mart_education.aerie_admissions_program) <> v_candidate_count THEN
RAISE EXCEPTION 'aerie_admissions_program: post-publication row count mismatch';
END IF;
RAISE INFO 'aerie_admissions_program: published % row(s) from observed EduCRM run %',
v_candidate_count, v_observed_run_id;
DROP TABLE tmp_aerie_admissions_program;
DROP TABLE tmp_aerie_admissions_program_query;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;
ALTER PROCEDURE mart_education.sp_refresh_aerie_admissions_program()
OWNER TO "CQL_download_OM";
REVOKE ALL ON PROCEDURE mart_education.sp_refresh_aerie_admissions_program()
FROM PUBLIC;
GRANT EXECUTE ON PROCEDURE mart_education.sp_refresh_aerie_admissions_program()
TO "CQL_download_OM";
-- 010_aerie_admissions_program_directory.sql: 13 statement(s)
-- Canonical DDL for mart_education.aerie_admissions_program_directory (A8
-- unit U03, Gateway slug aerie-admissions-program-directory). Sole writer:
-- mart_education.sp_refresh_aerie_admissions_program_directory().
--
-- Thin parity mart: columns are exactly the output aliases of Aerie's
-- queryHubspotPrograms SQL (sync/src/analytics/queries/hubspot.ts) over
-- mart_education.aerie_program_directory_current, with source types kept.
CREATE TABLE IF NOT EXISTS mart_education.aerie_admissions_program_directory (
program_id VARCHAR(100),
hubspot_name VARCHAR(512),
display_name VARCHAR(512),
tuition NUMERIC(18, 4),
city VARCHAR(255),
state VARCHAR(100),
school_address VARCHAR(1000),
school_latitude NUMERIC(18, 8),
school_longitude NUMERIC(18, 8),
grade_levels VARCHAR(1000),
email VARCHAR(500),
contact_number VARCHAR(100),
enrollment_deposit VARCHAR(255),
application_fee VARCHAR(255),
school_year_start VARCHAR(256),
school_year_end VARCHAR(256),
website VARCHAR(2000),
school_summary VARCHAR(65535),
maxio_site_id VARCHAR(255),
canonical_source_run_id VARCHAR(128),
mart_row_id VARCHAR(32) NOT NULL,
source_run_id VARCHAR(128) NOT NULL,
source_published_at TIMESTAMPTZ NOT NULL,
refreshed_at TIMESTAMP NOT NULL,
created_by VARCHAR(128) NOT NULL,
PRIMARY KEY (mart_row_id)
)
DISTSTYLE ALL
SORTKEY (mart_row_id);
COMMENT ON TABLE mart_education.aerie_admissions_program_directory IS
'Purpose: Surtr publication of the rows Aerie''s queryHubspotPrograms reads (mart_education.aerie_program_directory_current LEFT JOIN core_education.dim_program on the HubSpot Program id), so Aerie can read them through the Surtr Gateway with its unchanged row mapper. Grain: one output row of that SQL; normally one active HubSpot Program. Key: mart_row_id (MD5 of program_id plus its occurrence number); program_id is expected unique but not enforced, so Aerie''s own checks still see any duplicate. Lineage: source_run_id/source_published_at are the directory''s hubspot_publication_run_id/hubspot_source_published_at. Sensitive data: email and contact_number are school contact points and can identify staff. Full snapshot replaced atomically by mart_education.sp_refresh_aerie_admissions_program_directory.';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.hubspot_name IS
'COALESCE(dim_program.program_name, directory program_code).';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.display_name IS
'COALESCE(dim_program.display_name, directory program_name).';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.school_year_start IS
'Directory school_year_start DATE cast to text (YYYY-MM-DD), as Aerie selects it.';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.school_year_end IS
'Directory school_year_end DATE cast to text (YYYY-MM-DD), as Aerie selects it.';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.canonical_source_run_id IS
'dim_program.hubspot_publication_run_id of the joined canonical Program row; NULL when unmatched. Lineage addition; not read by Aerie.';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.mart_row_id IS
'Deterministic row key: MD5 of program_id and its occurrence number. Gateway orderBy for total-order paging.';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.source_run_id IS
'aerie_program_directory_current.hubspot_publication_run_id; the procedure requires exactly one value per snapshot.';
COMMENT ON COLUMN mart_education.aerie_admissions_program_directory.source_published_at IS
'aerie_program_directory_current.hubspot_source_published_at of that publication.';
ALTER TABLE mart_education.aerie_admissions_program_directory
OWNER TO "CQL_download_OM";
-- Writer protection only. Reader access is provisioned by the Redshift DBA
-- (PIPELINE §13); the Surtr Gateway reads as the owner.
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
ON mart_education.aerie_admissions_program_directory FROM PUBLIC;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
ON mart_education.aerie_admissions_program_directory FROM GROUP team_engineers;
-- 011_sp_refresh_aerie_admissions_program_directory.sql: 4 statement(s)
-- Sole writer for mart_education.aerie_admissions_program_directory
-- (WAREHOUSE §7.1).
--
-- The candidate is Aerie's queryHubspotPrograms SQL, copied verbatim from
-- sync/src/analytics/queries/hubspot.ts:75-98 at Aerie 92fd47992, with three
-- lineage columns appended to the select list. Lineage is carried from the
-- upstream Surtr mart (mart-aerie-hubspot-refresh), which stamps every
-- directory row with one accepted HubSpot publication.
--
-- Fails closed on: an empty candidate, missing or mixed upstream lineage, and
-- a duplicate mart_row_id. The DELETE + INSERT publish stays inside the CALL
-- transaction; never TRUNCATE (it commits implicitly).
CREATE OR REPLACE PROCEDURE mart_education.sp_refresh_aerie_admissions_program_directory()
AS $$
DECLARE
v_candidate_count BIGINT;
v_lineage_run_count BIGINT;
v_lineage_published_count BIGINT;
v_lineage_invalid_count BIGINT;
v_duplicate_count BIGINT;
v_source_run_id VARCHAR(128);
v_source_published_at TIMESTAMPTZ;
v_refreshed_at TIMESTAMP;
BEGIN
LOCK TABLE mart_education.aerie_admissions_refresh_writer_mutex;
v_refreshed_at := GETDATE();
DROP TABLE IF EXISTS tmp_aerie_admissions_program_directory_query;
CREATE TEMP TABLE tmp_aerie_admissions_program_directory_query AS
-- aerie-sql:begin
SELECT
directory.program_id,
COALESCE(canonical_program.program_name, directory.program_code) AS hubspot_name,
COALESCE(canonical_program.display_name, directory.program_name) AS display_name,
directory.tuition,
directory.city,
directory.state,
directory.school_address,
directory.latitude AS school_latitude,
directory.longitude AS school_longitude,
directory.grade_range AS grade_levels,
directory.school_email AS email,
directory.school_phone AS contact_number,
directory.enrollment_deposit,
directory.application_fee,
directory.school_year_start::varchar,
directory.school_year_end::varchar,
directory.website,
directory.school_summary,
directory.maxio_site_id,
-- A8 lineage additions (not in Aerie's select list):
canonical_program.hubspot_publication_run_id AS canonical_source_run_id,
directory.hubspot_publication_run_id AS directory_publication_run_id,
directory.hubspot_source_published_at AS directory_source_published_at
FROM mart_education.aerie_program_directory_current directory
LEFT JOIN core_education.dim_program canonical_program
ON canonical_program.hubspot_program_id = directory.program_id
AND canonical_program.hubspot_source_presence_status = 'active'
-- aerie-sql:end
;
SELECT COUNT(*) INTO v_candidate_count FROM tmp_aerie_admissions_program_directory_query;
IF v_candidate_count = 0 THEN
RAISE EXCEPTION 'aerie_admissions_program_directory: candidate is empty';
END IF;
SELECT COUNT(DISTINCT directory_publication_run_id),
COUNT(DISTINCT directory_source_published_at),
SUM(CASE
WHEN NULLIF(BTRIM(directory_publication_run_id), '') IS NULL
OR directory_source_published_at IS NULL THEN 1
ELSE 0
END),
MIN(directory_publication_run_id),
MIN(directory_source_published_at)
INTO v_lineage_run_count, v_lineage_published_count, v_lineage_invalid_count,
v_source_run_id, v_source_published_at
FROM tmp_aerie_admissions_program_directory_query;
IF v_lineage_run_count <> 1 OR v_lineage_published_count <> 1 OR v_lineage_invalid_count <> 0 THEN
RAISE EXCEPTION
'aerie_admissions_program_directory: directory lineage is mixed or incomplete (% run id(s), % published_at value(s), % row(s) missing lineage)',
v_lineage_run_count, v_lineage_published_count, v_lineage_invalid_count;
END IF;
DROP TABLE IF EXISTS tmp_aerie_admissions_program_directory;
CREATE TEMP TABLE tmp_aerie_admissions_program_directory (LIKE mart_education.aerie_admissions_program_directory);
INSERT INTO tmp_aerie_admissions_program_directory (
program_id,
hubspot_name,
display_name,
tuition,
city,
state,
school_address,
school_latitude,
school_longitude,
grade_levels,
email,
contact_number,
enrollment_deposit,
application_fee,
school_year_start,
school_year_end,
website,
school_summary,
maxio_site_id,
canonical_source_run_id,
mart_row_id,
source_run_id,
source_published_at,
refreshed_at,
created_by
)
SELECT
q.program_id,
q.hubspot_name,
q.display_name,
q.tuition,
q.city,
q.state,
q.school_address,
q.school_latitude,
q.school_longitude,
q.grade_levels,
q.email,
q.contact_number,
q.enrollment_deposit,
q.application_fee,
q.school_year_start,
q.school_year_end,
q.website,
q.school_summary,
q.maxio_site_id,
q.canonical_source_run_id,
MD5(
'aerie_admissions_program_directory|'
|| COALESCE('v' || q.program_id, 'n')
|| '|'
|| (ROW_NUMBER() OVER (
PARTITION BY q.program_id
-- Every output column, so rows that share a key are numbered
-- the same way on every refresh; only identical rows tie.
ORDER BY q.hubspot_name, q.display_name, q.tuition, q.city, q.state,
q.school_address, q.school_latitude, q.school_longitude,
q.grade_levels, q.email, q.contact_number, q.enrollment_deposit,
q.application_fee, q.school_year_start, q.school_year_end,
q.website, q.school_summary, q.maxio_site_id, q.canonical_source_run_id
))::VARCHAR
),
v_source_run_id,
v_source_published_at,
v_refreshed_at,
'mart-aerie-admissions-refresh/v1'
FROM tmp_aerie_admissions_program_directory_query q;
SELECT COUNT(*) INTO v_duplicate_count
FROM (
SELECT mart_row_id
FROM tmp_aerie_admissions_program_directory
GROUP BY mart_row_id
HAVING COUNT(*) > 1
) duplicates;
IF v_duplicate_count <> 0 THEN
RAISE EXCEPTION
'aerie_admissions_program_directory: candidate has % duplicate mart_row_id value(s)',
v_duplicate_count;
END IF;
LOCK TABLE mart_education.aerie_admissions_program_directory;
DELETE FROM mart_education.aerie_admissions_program_directory;
INSERT INTO mart_education.aerie_admissions_program_directory (
program_id,
hubspot_name,
display_name,
tuition,
city,
state,
school_address,
school_latitude,
school_longitude,
grade_levels,
email,
contact_number,
enrollment_deposit,
application_fee,
school_year_start,
school_year_end,
website,
school_summary,
maxio_site_id,
canonical_source_run_id,
mart_row_id,
source_run_id,
source_published_at,
refreshed_at,
created_by
)
SELECT
program_id,
hubspot_name,
display_name,
tuition,
city,
state,
school_address,
school_latitude,
school_longitude,
grade_levels,
email,
contact_number,
enrollment_deposit,
application_fee,
school_year_start,
school_year_end,
website,
school_summary,
maxio_site_id,
canonical_source_run_id,
mart_row_id,
source_run_id,
source_published_at,
refreshed_at,
created_by
FROM tmp_aerie_admissions_program_directory;
IF (SELECT COUNT(*) FROM mart_education.aerie_admissions_program_directory) <> v_candidate_count THEN
RAISE EXCEPTION 'aerie_admissions_program_directory: post-publication row count mismatch';
END IF;
RAISE INFO 'aerie_admissions_program_directory: published % row(s) from HubSpot publication %',
v_candidate_count, v_source_run_id;
DROP TABLE tmp_aerie_admissions_program_directory;
DROP TABLE tmp_aerie_admissions_program_directory_query;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;
ALTER PROCEDURE mart_education.sp_refresh_aerie_admissions_program_directory()
OWNER TO "CQL_download_OM";
REVOKE ALL ON PROCEDURE mart_education.sp_refresh_aerie_admissions_program_directory()
FROM PUBLIC;
GRANT EXECUTE ON PROCEDURE mart_education.sp_refresh_aerie_admissions_program_directory()
TO "CQL_download_OM";
</details>
## Keval steps
1. Apply the DDL to prod before merging. A merge reaches production within the hour, and the EduCRM trigger then fires every 30 minutes. Run cd pipelines/runners/mart-aerie-admissions-refresh && uv run python scripts/apply_ddl.py (runs as CQL_download_OM; applies 006-011 in order). Using the usual numbered out-of-band SQL deploy for those files is equivalent.
2. Merge. Mercy withholds auto-approve on pipelines/cdk/ paths, so this needs a human approval. Once released, run mart-aerie-admissions-refresh on demand. Expect status: success, with results showing 90 program rows (source_run_id = the latest EduCRM run) and 113 directory rows (source_run_id = the HubSpot publication).
3. Run both reconciliation files. Query 2 must return 0 in both directions.
4. Reader access (optional). Reader access for non-owners is a DBA grant.
## Not covered
- Other A8 units: Gateway registration (U04, SURTR-1533, merged) and the Aerie read gate with its purge guard (U05).
- The procedure bodies have not been executed in Redshift, because no DDL was applied. Their candidate SELECTs, the view, the guards and the stamping expressions were run read-only instead. A behavioural rollback test needs a Redshift sandbox; Mercy deferred this as coverage.
- Behaviour inherited from Aerie: the prior-year predicate is evaluated at refresh, so rows can lag by one refresh (≤30 min) after 1 January. Aerie's 2028 predicate limitation also applies.
- When the program procedure fails, then succeeds 30 minutes later. This happens if an EduCRM run is in flight when mart-aerie-hubspot-refresh triggers, or if the latest EduCRM run failed mart_all_program. The mart keeps its last publication meanwhile. Procedure failures are PARTIAL (amber, throttled), not paging.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
