# Bulk exports

> Download each day's new episodes, transcripts and shows as Parquet and JSONL.gz, and load them with DuckDB, BigQuery, pandas or your own tools.

The daily export files are every new episode, transcript and show, a day at a time, as files your warehouse loads in one step. Each closed UTC day is built the next morning as three datasets, each written twice with the same rows: as Parquet, which carries its column types, and as JSONL.gz, one JSON object a line.

- **`episodes`**: every episode transcribed that day, or whose sponsors, people and topics were completed that day. A row is the episode as [`GET /api/v1/episodes/{episode_id}`](https://api.podzone.fm/api/v1/docs#tag/episodes/operation/get_episode) gives it, with its sponsors, people and topics.
- **`transcripts`**: every transcript stored that day, as [the transcript download](https://api.podzone.fm/api/v1/docs#tag/episodes/operation/download_episode_transcript) gives it in `json`: timed segments, keyed by `episode_id`.
- **`podcasts`**: every show Podzone transcribes, as [`GET /api/v1/podcasts/{podcast_id}`](https://api.podzone.fm/api/v1/docs#tag/podcasts/operation/get_podcast) gives it, as of that day.

They are for when you want all of it: the [firehose](https://podzone.fm/docs/firehose) pushes new episodes as they arrive, and the API answers one question at a time.

## Listing the days

The files are on the plans that include them ([pricing](https://podzone.fm/pricing)). An account admin can download them from the app's Exports page; code lists them with [`GET /api/v1/exports`](https://api.podzone.fm/api/v1/docs#tag/exports/operation/list_exports):

```bash
curl https://api.podzone.fm/api/v1/exports \
  -H "Authorization: Bearer $PODZONE_API_KEY"
```

```json
{
  "results": [
    {
      "date": "2026-11-15",
      "built_at": "2026-11-16T04:12:09Z",
      "missing_transcripts": 0,
      "manifest_url": "https://...",
      "files": [
        {
          "dataset": "episodes",
          "format": "parquet",
          "rows": 12931,
          "bytes": 8409113,
          "sha256": "9f2c...",
          "url": "https://..."
        }
      ]
    }
  ],
  "expires_at": "2026-11-16T10:00:00Z"
}
```

The days your plan keeps are listed newest first, each with all six of its files. Listing costs one API request, and downloading costs nothing. On a plan without the files, the request is refused with `403 upgrade_required`.

## Downloading and checking the files

Each `url` is signed, so send no API key to it. It works until `expires_at`, an hour after you listed; after that, list again for fresh links. This script downloads every day you do not have yet in one format, a folder per dataset and day, and checks each file against its `sha256`:

```bash
format=parquet  # or jsonl.gz

curl -sf https://api.podzone.fm/api/v1/exports \
  -H "Authorization: Bearer $PODZONE_API_KEY" > exports.json

jq -r --arg format "$format" '.results[] | .date as $date | .files[]
       | select(.format == $format) | [$date, .dataset, .sha256, .url] | @tsv' exports.json |
while IFS=$'\t' read -r date dataset sha256 url; do
  file="exports/$dataset/date=$date/$dataset.$format"
  [ -f "$file" ] && continue
  mkdir -p "$(dirname "$file")"
  curl -sf -o "$file.part" "$url"
  echo "$sha256  $file.part" | shasum -a 256 -c --quiet - && mv "$file.part" "$file"
done
```

A file whose checksum does not match is left as `.part`, so the next run fetches it again. On Linux, `sha256sum -c` does the same as `shasum -a 256 -c`. To keep both formats, give each its own folder: readers that take a whole folder expect one format in it.

Each day also has a manifest (`manifest_url`): its files, their rows, sizes and checksums, and each dataset's Parquet schema.

## Loading the files

Parquet is the one to load: its columns arrive with their types (timestamps in UTC, lists of sponsors, people, topics and segments as nested records), so nothing has to be declared first. JSONL.gz holds the same rows for tools that read JSON.

**DuckDB** reads a whole directory of days at once, and takes each day from its folder name:

```sql
SELECT date, count(*) AS episodes
FROM read_parquet('exports/episodes/*/*.parquet', hive_partitioning = true)
GROUP BY date
ORDER BY date;
```

JSONL.gz files read the same way, with `read_json('exports/episodes/*/*.jsonl.gz', hive_partitioning = true)`.

**BigQuery** loads a Parquet file with its schema. List inference loads the nested lists as repeated records:

```bash
bq load --source_format=PARQUET --parquet_enable_list_inference=true \
  podzone.episodes_2026_11_15 exports/episodes/date=2026-11-15/episodes.parquet
```

**pandas** reads the same files through pyarrow:

```python
import pandas as pd

episodes = pd.read_parquet("exports/episodes")  # every day, with a `date` column
one_day = pd.read_json("exports/episodes/date=2026-11-15/episodes.jsonl.gz", lines=True)
```

## Keeping the latest row for each episode

A day's files hold what changed that day, not what was published that day. An episode is in the day it was transcribed, again in the day its sponsors, people and topics were completed, and again in any later day they are relinked. So the same `id` can be in several days: keep the latest row for each, or upsert on `id` as each day arrives.

```sql
CREATE OR REPLACE TABLE episodes AS
SELECT *
FROM read_parquet('exports/episodes/*/*.parquet', hive_partitioning = true)
QUALIFY row_number() OVER (PARTITION BY id ORDER BY date DESC) = 1;
```

```python
episodes["date"] = episodes["date"].astype(str)  # read from the folder names
latest = episodes.sort_values("date").drop_duplicates("id", keep="last")
```

Transcripts are kept the same way on `episode_id`, and shows on `id`, where each day's `podcasts` file is a complete snapshot.

## When the files are built

Each UTC day is built the next morning, usually before 05:00 UTC, after the night's work on that day's episodes has finished. A day is listed only once all of its files and its manifest are in place.

Files start from the first day built. Older episodes are not in them: fetch those [through the API](https://podzone.fm/docs/quickstart). Each day is kept for your plan's window and then deleted, so download the days you want to keep. A day can be rebuilt to leave out material that has been removed from Podzone; it then has a later `built_at`, and is listed with its new files.

`missing_transcripts` counts transcripts stored that day that could not be read when the files were built, so are not in them. It is usually 0; each one can still be downloaded on its own.

## What you may do with the files

Use them in your business and in what you build for your customers, as the [terms](https://podzone.fm/terms#building-on-podzone) allow. Bulk data has terms of its own ([section 4A](https://podzone.fm/terms#bulk-data)): keep the files secure, do not pass them on, and remove what we tell you has been removed.

## Troubleshooting

- **`403 upgrade_required` when listing:** your plan does not include the files ([pricing](https://podzone.fm/pricing)).
- **`403` from a file's link:** the link has expired. List again for new ones.
- **Yesterday is not listed yet:** it is built the next morning, usually before 05:00 UTC.
- **A checksum does not match:** the download was cut short. Download the file again from a fresh link.
