Every query, and what it produced

One committed extract, plus the census and verification queries that established the method. Four of the latter changed how the extract is written.

The committed pipeline

bq query --use_legacy_sql=false --format=csv --max_rows=100000 \
  < extract.sql > output/nirf_by_discipline.csv
python3 build_notes.py     # regenerates all eight discipline notes from the CSV
python3 check_notes.py     # exits 1 on any mismatch

--max_rows is not optional: the output is 1,601 rows and bq truncates at 100 without warning.

Extract	Output	Rows	What it feeds
extract.sql	nirf_by_discipline.csv	1,601	Everything — the board, all eight discipline notes, the trend tables

---

Verification queries that changed the extract

V1. Are the duplicate rows identical, or conflicting?

Why. The plan was to de-duplicate with MAX(value). That is only a de-duplication if the duplicates agree; if they disagree it is a silent choice of the larger number.

WITH k AS (
  SELECT institute_id, ranking_year, ranking_category, academic_year, type, category,
         COUNT(*) AS n_rows, COUNT(DISTINCT value) AS n_distinct_values,
         MIN(value) AS lo, MAX(value) AS hi
  FROM `avantifellows.external_data_sources.nirf_fact_master`
  GROUP BY 1,2,3,4,5,6 HAVING n_rows > 1
)
SELECT COUNT(*) AS dup_keys, COUNTIF(n_distinct_values = 1) AS identical,
       COUNTIF(n_distinct_values > 1) AS conflicting, MAX(hi - lo) AS worst_spread
FROM k

Result. 6,271 duplicate keys, 6,271 identical, 0 conflicting, worst spread 0.

Action. MAX() is safe and is used. Recorded here because the next person will reasonably wonder whether it is hiding a conflict.

V2. How many academic years does one ranking year carry?

Why. To find out whether ranking_year alone is a safe filter.

SELECT ranking_year, COUNT(DISTINCT academic_year) AS n_acad_years,
       STRING_AGG(DISTINCT academic_year ORDER BY academic_year) AS which
FROM `avantifellows.external_data_sources.nirf_fact_master`
WHERE ranking_category = 'Engineering' GROUP BY 1 ORDER BY 1

Result. Seven academic years per ranking year, on a rolling window. Ranking 2025 carries 2017-18 to 2023-24; ranking 2024 carries 2016-17 to 2022-23. Academic year 2021-22 appears in five different editions.

Action. Every query pins exactly one ranking_year. Summing across them would count a cohort up to seven times. Written up as trap 1.

V3. Which academic year carries which metric?

Why. To build an intake-to-placement funnel.

SELECT academic_year,
       COUNTIF(category = 'Number of students placed' AND value IS NOT NULL) AS has_placed,
       COUNTIF(category = 'Median salary of placed graduates' AND value IS NOT NULL) AS has_salary,
       COUNTIF(category = 'Number of first year students intake' AND value IS NOT NULL) AS has_intake
FROM `avantifellows.external_data_sources.nirf_fact_master`
WHERE ranking_category = 'Engineering' AND ranking_year = 2025
GROUP BY 1 ORDER BY 1

Result. Intake is populated for 2017-18 to 2022-23 and zero for 2023-24. Placement and salary are populated for 2021-22 to 2023-24 and zero before.

Action. The funnel was abandoned rather than faked. There is no academic year carrying both sides, so entry and exit must be linked through the programme’s duration — which is why duration_years is parsed out of the type string and why no funnel is published yet. Trap 2.

V4. Does every discipline report outcomes for the same academic year?

Why. The cross-discipline board was about to pin academic_year = '2023-24'.

SELECT discipline, level, duration_years, academic_year, institutes
FROM output_of_extract WHERE ranking_year = 2025 AND discipline IN ('Medical','Engineering')

Result. No. Medical UG — the 5-year MBBS — has no 2023-24 row at all; its latest is 2022-23. Every other subject track reaches 2023-24.

Action. Pinning one year would have dropped MBBS from the board silently and with no error. The extract now computes is_latest_outcome_year per (discipline, ranking year, level, grain) and the board selects on that flag instead of a literal year. Trap 5.

---

The census queries

Kept because they define the shape of the source and would otherwise be re-derived.

-- 15 ranking categories, of which 8 are subjects and 6 are institution types
SELECT ranking_category, COUNT(DISTINCT institute_id) AS institutes,
       MIN(ranking_year) AS y0, MAX(ranking_year) AS y1, COUNT(*) AS rows_
FROM `avantifellows.external_data_sources.nirf_fact_master` GROUP BY 1 ORDER BY institutes DESC

-- 7 metrics, all present in all 15 categories, 2019-2025
SELECT category AS metric, COUNT(*) AS n, COUNT(DISTINCT ranking_category) AS in_n_cats
FROM `avantifellows.external_data_sources.nirf_fact_master` GROUP BY 1 ORDER BY n DESC

Results. Engineering is the largest track at 267 institutes; Management and State Public Universities appear only in 2025; Agriculture only from 2023. The seven metrics are intake, admitted, lateral entry, graduated in minimum time, placed, selected for higher studies, and median salary — every one present in every category.