Identifying New Returns to Process • ef2

Overview

Every tax year, the IRS releases a new batch of 990 e-file XML filings. The complete list of filings available for a year lives in an index file — a data frame with (at minimum) a TaxYear and a URL column, where each URL points to one raw XML document on the Giving Tuesday (“GT”) data lake:

https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/...

What has actually been processed lives in the published DuckDB databases on the NCCS S3 bucket, one database per year:

https://nccs-efile.s3.us-east-1.amazonaws.com/duckdb/efile_v2_1/EFILE<YEAR>.duckdb

Inside each database, the KEYS table holds one row per processed return, and its URL column records where that return came from.

This tutorial shows how to difference the two — the filings the index says are available, versus the URLs already baked into the database — so you know exactly what still needs to be processed before running an update.

Step 1 — Load the current

The index file contains a catalog of all IRS 990 Efile XMLs stored on the Giving Tuesday Data Commons:

|     |    990|  990EZ|  990PF|  990T|
|:----|------:|------:|------:|-----:|
|2020 | 320343| 169865| 114957| 22673|
|2021 | 336488| 202729| 119354| 23865|
|2022 | 349062| 206135| 122819| 22626|
|2023 | 354774| 206382| 124294| 22982|
|2024 | 277302| 178868| 112560| 16881|
|2025 |  22388|  35522|  15215|   668|

library(ef2)

index <- get_current_index_full()
#> |Var1 |   Freq|
#> |:----|------:|
#> |2007 |     34|
#> |2008 |    221|
#> |2009 |  51126|
#> |2010 | 211600|
#> |2011 | 276149|
#> |2012 | 313371|
#> |2013 | 349117|
#> |2014 | 388478|
#> |2015 | 417228|
#> |2016 | 437375|
#> |2017 | 469707|
#> |2018 | 500964|
#> |2019 | 524994|
#> |2020 | 653322|
#> |2021 | 682538|
#> |2022 | 700822|
#> |2023 | 708446|
#> |2024 | 585611|
#> |2025 |  73793|

# show files by year + formtype

table( index$TaxYear, index$FormType ) |>
  knitr::kable()

	990	990EZ	990PF	990T
2007	17	17	0	0
2008	87	114	20	0
2009	33311	15470	2345	0
2010	123025	63326	25249	0
2011	159504	82048	34597	0
2012	179688	93750	39933	0
2013	198855	104375	45887	0
2014	218619	116417	53442	0
2015	233519	124894	58815	0
2016	243903	130484	62988	0
2017	261612	139145	68950	0
2018	271442	149384	80138	0
2019	284515	152689	87790	0
2020	343790	171891	114967	22674
2021	336521	202730	119360	23927
2022	349091	206160	122858	22713
2023	354774	206382	124308	22982
2024	277302	178868	112560	16881
2025	22388	35522	15215	668

Step 2 — Difference available vs. processed

find_missing_urls() does the comparison for you. It:

attaches the remote EFILE<YEAR>.duckdb read-only over httpfs,
pulls the distinct URL values out of the KEYS table (the processed set),
filters your index to the requested TaxYear (the available set), and
returns setdiff(available, processed) — the URLs not yet in the database.

YEAR <- 2023
missing_urls <- find_missing_urls(year = YEAR, index = index)
#> 🔎 Checking for missing URLs in TaxYear 2023
#> 180659 missing URLs detected.

length(missing_urls)   # how many filings still need processing
#> [1] 180659
head(missing_urls)
#> [1] "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202600899349200340_public.xml"
#> [2] "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202610579349300641_public.xml"
#> [3] "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202620699349300302_public.xml"
#> [4] "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202630859349300513_public.xml"
#> [5] "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202631079349100808_public.xml"
#> [6] "https://gt990datalake-rawdata.s3.amazonaws.com/EfileData/XmlFiles/202600989349201870_public.xml"

You’ll see console messages like:

🔎 Checking for missing URLs in TaxYear 2023
1289 missing URLs detected.

No network writes happen here — this is a read-only diagnostic. Run it as often as you like to monitor how far behind a database is.

Step 3 — Interpret the gap

A few quick summaries make the result actionable:

n_available <- nrow(dplyr::filter(index, TaxYear == as.character(YEAR)))
n_missing   <- length(missing_urls)
n_processed <- n_available - n_missing

cat(
  sprintf("Year %s\n", YEAR),
  sprintf("  Available (index):   %s\n", format(n_available, big.mark = ",")),
  sprintf("  Processed (DuckDB):  %s\n", format(n_processed, big.mark = ",")),
  sprintf("  Missing:             %s (%0.1f%%)\n",
          format(n_missing, big.mark = ","),
          100 * n_missing / n_available)
)
#> Year 2023
#>    Available (index):   708,446
#>    Processed (DuckDB):  527,787
#>    Missing:             180,659 (25.5%)

Interpreting the outcome:

n_missing == 0 — the published database already contains every filing in the index. Nothing to do.
n_missing > 0 — those filings exist on the GT data lake but have not been flattened into the database yet. Feed them to an update (next tutorial).
A negative difference (rare) means the database contains URLs your index doesn’t — usually a sign your local index is stale. Refresh the index file before trusting the diff.

Step 4 — Check several years at once

To audit an entire archive, loop over the years and collect the counts:

years <- 2019:2023

audit <- purrr::map_dfr(years, function(y) {
  idx <- dplyr::filter( index, TaxYear == y )
  miss <- find_missing_urls(year = y, index = idx)
  data.frame(
    TaxYear   = y,
    available = nrow(idx),
    missing   = length(miss)
  )
})
#> 🔎 Checking for missing URLs in TaxYear 2019
#> 87789 missing URLs detected.
#> 🔎 Checking for missing URLs in TaxYear 2020
#> 137630 missing URLs detected.
#> 🔎 Checking for missing URLs in TaxYear 2021
#> 143219 missing URLs detected.
#> 🔎 Checking for missing URLs in TaxYear 2022
#> 147130 missing URLs detected.
#> 🔎 Checking for missing URLs in TaxYear 2023
#> 180659 missing URLs detected.

audit$processed <- audit$available - audit$missing
audit
#>   TaxYear available missing processed
#> 1    2019    524994   87789    437205
#> 2    2020    653322  137630    515692
#> 3    2021    682538  143219    539319
#> 4    2022    700822  147130    553692
#> 5    2023    708446  180659    527787

This gives you a one-glance table of where every year stands.

What you learned

Index files enumerate the filings available on the GT data lake.
The KEYS table inside each EFILE<YEAR>.duckdb records what’s processed.
find_missing_urls(year, index) returns the difference — a character vector of URLs still to be processed — without modifying anything.

Once you have that vector of missing URLs, you’re ready to build them into an update. See “Updating a DuckDB Database with Missing Files.”