etl¶

Run OWID's ETL client.

Create ETL step templates, compare different datasets, generate dependency visualisations, synchronise charts across different servers, import datasets from non-ETL OWID sources, improve your metadata, etc.

Note: For a UI experience, refer to CLI etlwiz.

Running etl with no subcommand opens the unified browser for searching steps and snapshots.

Usage:

etl [OPTIONS] COMMAND [ARGS]...

Options:

Subcommands

• anomalist: Detect anomalies.
• approve: Automatically approve chart diffs with identical data. This is done by taking their configs and replacing variable IDs with hashes of their data.
• archive: Archive one or more steps.
• autoupdate: Automatically update data snapshots and optionally create a pull request with the changes.
• chart-sync: Sync Grapher charts and revisions from an environment to the main environment.
• compare: Compare two dataframes/tables/datasets in terms of their structure, values and metadata.
• d: Run development tools.
• diff: Compare all datasets from two catalogs and print out a summary of their differences.
• graphviz: Generate a Graphviz DOT file to see all dependencies.
• harmonize: Generate a dictionary with the mapping of country names to OWID's canonical names.
• indicator-upgrade: Indicator upgrader CLI.
• inspector: Check explorer, multidim views, chart configs, and posts (including articles, topic pages, and data insights) for typos and semantic issues.
• metadata-export: Export dataset, tables & indicator metadata in YAML format.
• owidbot: Post result of etl diff to Github PR.
• pr: This script creates a new draft pull request in GitHub, which starts a new staging server.
• run: Generate datasets by running their corresponding ETL steps.
• snapshot: Create snapshot from a snapshot script or .dvc file.
• update: Update one or more steps to their new version, if possible.

anomalist¶

Detect anomalies.

Usage:

etl anomalist [OPTIONS]

Options:

approve¶

Automatically approve chart diffs with identical data. This is done by taking their configs and replacing variable IDs with hashes of their data.

If the configs are then identical, the chart is approved.

The comparison process: 1. Fetches all pending chart diffs (not yet approved/rejected) 2. For each chart, compares the normalized config between environments 3. Approves charts where configs are identical 4. Reports results

Usage:

etl approve [OPTIONS]

Options:

archive¶

Archive one or more steps.

This tool lets you move one or more data steps from their active to their archive dag.

Examples:

Note: Remove the --dry-run if you want to actually write to the dag.

• To archive a single step:

  $ etl archive data://meadow/aviation_safety_network/2022-10-12/aviation_statistics --dry-run
  

  Note that, since no steps are using this snapshot, the new snapshot will be added to the temporary dag.

• To archive not only that step, but also the steps that use it:

  $ etl archive data://meadow/aviation_safety_network/2022-10-12/aviation_statistics --include-usages --dry-run
  

Usage:

etl archive [OPTIONS] [STEPS]...

Options:

autoupdate¶

Automatically update data snapshots and optionally create a pull request with the changes.

Main use case: Run all autoupdate-enabled snapshots, update their data if needed, and create a PR if there are changes.

Examples:

# Run all autoupdate snapshots, update data, and create PRs if needed
etl autoupdate --create-pr

# Run in dry-run mode (no changes will be made)
etl autoupdate --dry-run

# Only process snapshots matching a filter
etl autoupdate --filter "population"

Usage:

etl autoupdate [OPTIONS]

Options:

chart-sync¶

Sync Grapher charts and revisions from an environment to the main environment.

It syncs the charts and revisions from SOURCE to TARGET. This is especially useful for syncing work from staging servers to production.

SOURCE and TARGET can be either name of staging servers (e.g. "staging-site-mybranch") or paths to .env files or repo/commit hash if you want to get the branch name from merged pull request. Use ".env.prod.write" as TARGET to sync to live.

• Note 1: The dataset (with the new chart's underlying indicators) from SOURCE must exist in TARGET. This means that you have to merge your work to master and wait for the ETL to finish running all steps.

Considerations on charts:

• You get a notification if the chart has been modified on live after staging server was created.
• If the chart is pending in chart-diff, you'll get a warning and Slack notification
• Deleted charts are not synced.
• Use --ignore-conflicts to sync approved charts ignoring conflicts. Useful when syncing between staging servers.

Considerations on tags:

• Tags are synced for both new and existing charts.

Example 1: Run chart-sync in dry-run mode to see what charts will be updated

$ etl chart-sync staging-site-my-branch .env.prod.write --dry-run

Example 2: Run it for real

etl chart-sync staging-site-my-branch .env.prod.write

Example 3: Sync only one chart

etl chart-sync staging-site-my-branch .env.prod.write --chart-id 123 --dry-run

Example 4: Ignore conflicts when syncing between staging servers (useful if conflicts with master have already been dealt with in a subbranch server)

etl chart-sync staging-site-my-subbranch staging-site-baseline-branch --ignore-conflicts --dry-run

Example 5: Archive datasets that have no charts (dry-run)

etl chart-sync staging-site-my-branch .env.prod.write --archive --dry-run

Considerations on archiving:

• Use --archive to automatically archive datasets in TARGET that have no charts using their indicators.
• Only datasets that were part of the synced charts are considered for archiving.
• Datasets are only archived if the corresponding ETL dataset is in the archive DAG (not in the active DAG).
• Archived datasets are not deleted, just marked as archived.

Usage:

etl chart-sync [OPTIONS] SOURCE TARGET

Options:

compare¶

Compare two dataframes/tables/datasets in terms of their structure, values and metadata.

Usage:

etl compare [OPTIONS] COMMAND [ARGS]...

Options:

Subcommands

• dataframes: Compare two DATAFRAME1 with DATAFRAME2.
• grapher: Compare a dataset in the local database with the remote database.
• table:

dataframes¶

Compare two DATAFRAME1 with DATAFRAME2.

It compares the columns, index columns and index values (row indices) as sets between the two dataframes and outputs the differences. Finally it compares the values of the overlapping columns and rows with the given threshold values for absolute and relative tolerance.

The exit code is: - 0 if the dataframes are equal - 1 if there is an error loading the dataframes - 2 if the dataframes are structurally equal but are otherwise different - 3 if the dataframes have different structure and/or different values.

Usage:

etl compare dataframes [OPTIONS] DATAFRAME1 DATAFRAME2

Options:

grapher¶

Compare a dataset in the local database with the remote database.

It loads the dataset from grapher/NAMESPACE/VERSION/DATASET. It compares dataset and variables metadata, and optionally the values from S3 with (use the --values flag for this). It does the comparison in the same way as the etl-catalog command.

The exit code is always 0 even if dataframes are different.

Examples:

compare  --show-values grapher ggdc 2020-10-01 ggdc_maddison__2020_10_01 --values

Usage:

etl compare grapher [OPTIONS] NAMESPACE VERSION DATASET

Options:

table¶

Compare a table in the local catalog with the analogous one in the remote catalog.

The table in the local catalog is loaded from CHANNEL/NAMESPACE/DATASET/{version}/TABLE. The value for {version} is given by the option --version. If not given, the latest local version of the dataset is compared with the latest remote version of the same dataset.

It compares the columns, index columns and index values (row indices) as sets between the two dataframes and outputs the differences. Finally it compares the values of the overlapping columns and rows with the given threshold values for absolute and relative tolerance.

The exit code is: - 0 if the tables are equal - 1 if there is an error loading the tables - 2 if the tables are structurally equal but are otherwise different - 3 if the tables have different structure and/or different values.

Usage:

etl compare table [OPTIONS] CHANNEL NAMESPACE DATASET TABLE

Options:

d¶

Run development tools.

Usage:

etl d [OPTIONS] COMMAND [ARGS]...

Options:

Subcommands

• housekeeper: Keep things in OWID catalog clean by regularly checking and reviewing content.
• map-datasets: Temporary script that prints the grapher dataset pairs that need to be (manually) given to the chart upgrader, based
• profile-cpu: This script runs certain step of the ETL pipeline and profiles memory or CPU usage of its run function line by line. You can additionally specify other functions to profile.
• publish: Publish the generated data catalog to S3.
• reindex: Create a catalog-[channel].feather file inside etl/data with all tables in each channel.
• run-python-step: Import and run a specific step of the ETL.
• scan-chart-diff: Scan all open PRs in the etl repository and run
• version-tracker: Check that all DAG dependencies (e.g. make sure no step is missing).

housekeeper¶

Keep things in OWID catalog clean by regularly checking and reviewing content.

Usage:

etl d housekeeper [OPTIONS]

Options:

map-datasets¶

Temporary script that prints the grapher dataset pairs that need to be (manually) given to the chart upgrader, based on the committed changes in your current git branch.

NOTE: * This script should eventually be part of the new indicator upgrader, but for now it can be helpful as a CLI. * The logic may be more complicated than needed. It may suffice to find the newly created grapher datasets that do not yet have charts, and then attempt to find their corresponding previous version. But some of the code can be useful for other reasons (for example in the new chart diff tool).

Usage:

etl d map-datasets [OPTIONS]

Options:

profile-cpu¶

This script runs certain step of the ETL pipeline and profiles memory or CPU usage of its run function line by line. You can additionally specify other functions to profile.

Example: Profile CPU usage of run function of the step:

etl d profile --cpu garden/biodiversity/2024-01-25/cherry_blossom

Example: Profile specific functions (excludes run for cleaner output):

etl d profile --cpu garden/biodiversity/2024-01-25/cherry_blossom -f calculate_multiple_year_average
etl d profile --cpu garden/biodiversity/2024-01-25/cherry_blossom -f etl.helpers.PathFinder.load_dataset
etl d profile --cpu garden/biodiversity/2024-01-25/cherry_blossom -f etl.data_helpers.geo.RegionAggregator.__init__

To profile grapher upserts, it is better to use cProfile and run something like this:

ssh owid@staging-site-my-branch "cd etl && uv run python -m cProfile -s cumtime etl/command.py grapher://grapher/biodiversity/2024-01-25/cherry_blossom --grapher --only --force --workers 1" | head -n 100

Usage:

etl d profile-cpu [OPTIONS] STEP

Options:

publish¶

Publish the generated data catalog to S3.

Usage:

etl d publish [OPTIONS]

Options:

reindex¶

Create a catalog-[channel].feather file inside etl/data with all tables in each channel.

This enables catalog.search to be aware of what datasets currently exists. So, if for example you create a new dataset locally, you won't be able to find it in your local catalog unless you re-run reindex.

Usage:

etl d reindex [OPTIONS]

Options:

run-python-step¶

Import and run a specific step of the ETL.

Meant to be ran as a subprocess by the main etl command. There's a quite big overhead (~3s) from importing all packages again in the new subprocess.

Usage:

etl d run-python-step [OPTIONS] URI DEST_DIR

Options:

scan-chart-diff¶

Scan all open PRs in the etl repository and run etl owidbot etl/branch --services chart-diff against them.

Usage:

etl d scan-chart-diff [OPTIONS]

Options:

version-tracker¶

Check that all DAG dependencies (e.g. make sure no step is missing).

Run all version tracker sanity checks.

Usage:

etl d version-tracker [OPTIONS]

Options:

diff¶

Compare all datasets from two catalogs and print out a summary of their differences.

Compare all the datasets from catalog in PATH_A with all the datasets in catalog PATH_B. The catalog paths link to the data/ folder with all the datasets (it contains a catalog.meta.json file)

You can also use a path to a dataset.

Note that you can use the keyword "REMOTE" as the path, if you want to run a comparison with the remote catalog.

This tool is useful as a quick way to see what has changed in the catalog and whether our updates don't have any unexpected side effects.

Note: This command differs from etl compare in that it compares all the datasets and not two specific ones.

How does it work?

It uses source checksums to find candidates for comparison. Source checksum includes all files used to generate the dataset and should be sufficient to find changed datasets, just note that we're not using checksum of the files themselves. So if you change core ETL code or some of the dependencies, e.g. change in owid-datautils-py, core ETL code or updating library version, the change won't be detected. In cases like these you should increment ETL version which is added to all source checksums (not implemented yet).

Example 1: Compare the remote catalog with a local one for changed files

$ etl diff REMOTE data/ --changed

Example 2: Compare the remote catalog with a local one

$ etl diff REMOTE data/ --include maddison

Example 3: Compare two local catalogs

$ etl diff other-data/ data/ --include maddison

Usage:

etl diff [OPTIONS] PATH_A PATH_B

Options:

graphviz¶

Generate a Graphviz DOT file to see all dependencies.

Saves the output as a file in OUTPUT_PATH.

Usage:

etl graphviz [OPTIONS] OUTPUT_FILE

Options:

harmonize¶

Generate a dictionary with the mapping of country names to OWID's canonical names.

Harmonize the country names in COLUMN of a DATA_FILE (CSV or feather) and save the mapping to OUTPUT_FILE as a JSON file. The harmonization process is done according to OWID's canonical country names.

The harmonization process is done interactively, where the user is prompted with a list of ambiguous country names and asked to select the correct country name from a list of suggestions (ranked by similarity).

When the mapping is ambiguous, you can use:

• Choose Option [custom] to enter a custom name.
• Type Ctrl-C to exit and save the partially complete mapping

If a mapping file already exists, it will resume where the mapping file left off.

Usage:

etl harmonize [OPTIONS] DATA_FILE COLUMN OUTPUT_FILE

Options:

indicator-upgrade¶

Indicator upgrader CLI.

This CLI provides tools for managing indicator upgrades, including: - Matching variables between old and new datasets - Upgrading indicators in the database - Undoing indicator upgrades - Automatically detecting and upgrading dataset migrations

Usage:

etl indicator-upgrade [OPTIONS] COMMAND [ARGS]...

Options:

Subcommands

• auto: Automatically detect and upgrade dataset migrations.
• match: Match variable IDs from an old dataset to a new dataset.
• undo: Undo the last indicator upgrade.
• upgrade: Upgrade indicators to use new variable mappings.

auto¶

Automatically detect and upgrade dataset migrations.

This command combines dataset detection, variable matching, and upgrade into a single workflow. It detects which datasets have been updated based on version tracker changes, matches variables between old and new versions, and applies the upgrades.

By default, only perfect matches (100% similarity) are processed automatically. Use --threshold to adjust the similarity threshold for automatic matching.

Usage:

etl indicator-upgrade auto [OPTIONS]

Options:

match¶

Match variable IDs from an old dataset to a new dataset.

After a dataset has been uploaded to OWID's MySQL database, we need to pair new variable IDs with the old ones, so that all graphs update properly.

If the variable names are identical, the task is trivial: find indexes of old variables and map them to the indexes of the identical variables in the new dataset. However, if variable names have changed (or the number of variables have changed) the pairing may need to be done manually. This CLI helps in either scenario.

Usage:

etl indicator-upgrade match [OPTIONS]

Options:

undo¶

Undo the last indicator upgrade.

This command will reverse the most recent variable mapping operation, restoring charts and other references to use the previous variable IDs.

Usage:

etl indicator-upgrade undo [OPTIONS]

Options:

upgrade¶

Upgrade indicators to use new variable mappings.

This command will apply the variable mappings stored in the database to update all charts and other references to use the new variable IDs.

The variable mappings must have been previously created using the 'match' command or through the Streamlit UI.

Usage:

etl indicator-upgrade upgrade [OPTIONS]

Options:

inspector¶

Check explorer, multidim views, chart configs, and posts (including articles, topic pages, and data insights) for typos and semantic issues.

Examples:

# Check specific slug (with --slug or -s)
$ etl inspector -s natural-disasters

# For simplicity, the slug applies to all content types (explorers, mdims and posts); you can specify one (or more) content type (with --type or -t)
$ etl inspector -s natural-disasters -t post

# etl inspector -s natural-disasters -t post -t explorer

# You can check multiple slugs
$ etl inspector -s global-food -s natural-disasters

# Use different models (with --model or -m); currently available models are haiku=fast/cheap, sonnet=balanced, opus=best quality/more expensive
$ etl inspector -s global-food -m sonnet
$ etl inspector -s natural-disasters -m opus

# Save results to an output file (with --output-file or -o); it auto-resumes if interrupted, skipping already inspected content
$ etl inspector -o issues.csv
$ etl inspector -s global-food -o food_issues.csv

# Limit number of views (useful for testing/cost control)
$ etl inspector -l 10
$ etl inspector -s natural-disasters -l 5 -m haiku

# Skip specific checks
$ etl inspector --skip-issues  # Only run typo checks with codespell (skipping AI inspection); this option has no cost
$ etl inspector --skip-typos  # Only run semantic checks (skipping codespell inspection); this option has costs

# Estimate costs without running (dry run)
$ etl inspector --dry-run
$ etl inspector -s global-food -m opus --dry-run

# Enable experimental grouping/pruning (for large result sets)
$ etl inspector --enable-grouping

# Debug: see exact prompts sent to Claude
$ etl inspector -s energy -l 1 --display-prompt

Usage:

etl inspector [OPTIONS]

Options:

metadata-export¶

Export dataset, tables & indicator metadata in YAML format.

Given a DATASET_PATH, load the corresponding dataset and export its metadata in YAML format (including table and indicator metadata). The metadata file and can be later edited manually. If the output YAML already exists, it will be updated with new values.

When can this be useful? - This is useful when some metadata fields have been created dynamically in the code and you want to see the final result. - To prefill the YAML metadata file with the list of indicators and tables in the dataset. Note that, when first created, an ETL step is not yet aware of the columns of the tables of the dataset. It only knows that once you've executed th step.

Example 1: Save to YAML file etl/steps/data/garden/ggdc/2020-10-01/ggdc_maddison.meta.yml

etl metadata-export data/garden/ggdc/2020-10-01/ggdc_maddison

Example 2: Show output instead of saving the file

etl metadata-export data/garden/ggdc/2020-10-01/ggdc_maddison --show

Usage:

etl metadata-export [OPTIONS] DATASET_PATH

Options:

owidbot¶

Post result of etl diff to Github PR.

Example:

$ etl owidbot etl/my-branch --services data-diff chart-diff --dry-run
$ etl owidbot owid-grapher/my-branch --services grapher --dry-run

Usage:

etl owidbot [OPTIONS] REPO_BRANCH

Options:

pr¶

This script creates a new draft pull request in GitHub, which starts a new staging server.

Arguments:

TITLE: The title of the PR. This must be given.

CATEGORY: The category of the PR. This is optional. If not given, the user will be prompted to choose one.

Main use case: Branch out from master to a temporary work_branch, and create a PR to merge work_branch -> master. You will be asked to choose a category. The value of work_branch will be auto-generated based on the title and the category.

# Without specifying a category (you will be prompted for a category)
etl pr "some title for the PR"

# With a category
etl pr "some title for the PR" data

# With private stating server
etl pr "some title for the PR" --private

Custom use case (1): Same as main use case, but with a specific branch name for the work_branch.

etl pr "some title for the PR" --work-branch "this-temporary-branch"
# Shorter
etl pr "some title for the PR" -w "this-temporary-branch"

Custom use case (2): Create a pull request from current_branch to master.

etl pr "some title for the PR" --direct

Custom use case (3): Create a pull request from branch this-temporary-branch -> develop.

etl pr "some title for the PR" --direct --base-branch "develop" --work-branch "this-temporary-branch"
# Shorter
etl pr "some title for the PR" --direct -b "develop" -w "this-temporary-branch"

Custom use case (4): Create the new branch in a sibling git worktree so you can keep editing your current branch in parallel.

etl pr "some title for the PR" --worktree
# Shorter
etl pr "some title for the PR" -t
# With a custom path
etl pr "some title for the PR" -t --worktree-path /tmp/etl-mybranch

The new working directory is printed at the end (default: ../etl-BRANCH); cd into it to start working there.

Custom use case (5): Share the original repo's data/ directory with the new worktree, so ETL steps don't have to recompute population, regions, etc.

etl pr "some title for the PR" -t --share-data

This makes the new worktree's data/ a shortcut (symlink) to the original repo's data/, so both worktrees share the same ETL outputs and you don't have to recompute them. Note that data/ is a symlink to the original repo's data/, so: - If you run the same steps in both worktrees, they may overwrite each other's output. - DO NOT use rm -rf data/; this would wipe both the symlink and the original data folder. Instead, use git worktree remove ../etl-[whatever-branch] to remove a worktree.

After the command finishes, uv sync has already run inside the worktree, so its .venv/ is ready to use. With a chpwd hook in your ~/.zshrc that sources .venv/bin/activate whenever present, cd ../etl-BRANCH is all that's needed — activation is automatic. Without the hook, also run source .venv/bin/activate after the cd. Skipping activation silently routes etl/etlr to the original repo's source code.

See the docs (Working on multiple branches in parallel) for full details and tips.

Usage:

                                                                               
 etl pr                                                                         
 [OPTIONS] TITLE                                                                
 [[data|bug|refactor|enhance|feature|docs|chore|style|wip|tests]]

Options:

run¶

Generate datasets by running their corresponding ETL steps.

Run all ETL steps in the DAG matching the value of STEPS. A match is a dataset with an uri that contains the value of any of the words in STEPS.

Example 1: Run steps matching "mars" in the DAG file:

$ etl run mars

Example 2: Preview those steps that match "mars" or "prio" (i.e. don't run them):

$ etl run mars prio

Example 3: If you only want to preview what would be executed, use the --dry-run flag:

$ etl run mars prio --dry-run

Usage:

etl run [OPTIONS] [STEPS]...

Options:

snapshot¶

Create snapshot from a snapshot script or .dvc file.

DATASET_PATH can be provided in several formats: - Full path: namespace/version/short_name (e.g., tourism/2024-08-17/unwto_gdp) - Partial path: version/short_name (e.g., 2024-08-17/unwto_gdp) - Short name only: short_name (e.g., unwto_gdp) - Full file path: snapshots/namespace/version/short_name.py

The command will automatically find the corresponding .py script or .dvc file in the snapshots directory. If multiple matches are found, you'll need to provide a more specific path.

Run snapshot scripts in a standardized way. Supports three scenarios: 1. Scripts with main() function - runs module directly 2. Scripts with run() function - wraps in CLI with upload/path-to-file args 3. Scripts with only .dvc file - runs snap.create_snapshot()

Examples:

etl snapshot tourism/2024-08-17/unwto_gdp
etls tourism/2024-08-17/unwto_gdp
etls 2024-08-17/unwto_gdp
etls snapshots/tourism/2024-08-17/unwto_gdp.py

etl snapshot abs/2024-08-06/employee_earnings_and_hours_australia_2008 --path-to-file data.csv
etls abs/2024-08-06/employee_earnings_and_hours_australia_2008 --path-to-file data.csv
etls 2024-08-06/employee_earnings_and_hours_australia_2008 --path-to-file data.csv

etl snapshot dataset_name --skip-upload
etls dataset_name --skip-upload

etl snapshot dataset_name --dry-run
etls dataset_name --dry-run

Usage:

etl snapshot [OPTIONS] DATASET_PATH

Options:

update¶

Update one or more steps to their new version, if possible.

This tool lets you update one or more snapshots or data steps to a new version. It will:

• Create new folders and files for each of the steps.
• Add the new steps to the dag, with the same header comments as their current version.

Notes:

Keep in mind that:

• If there is ambiguity, the user will be asked for confirmation before updating each step, and on situations where there is some ambiguity.
• If new snapshots are created that are not used by any steps, they are added to a temporary dag (temp.yml). These steps are then removed from the temporary dag as soon as they are used by an active step.
• All dependencies of new steps will be assumed to use their latest version possible.
• Steps whose version is already equal to the new version will be skipped.
• Wildcard patterns (*, ?, [abc]) are supported in step names for batch operations.

Examples:

Note: Remove the --dry-run if you want to actually execute the updates in the examples below (but then remember to revert changes).

• To update a single snapshot to the new version:

  $ etl update snapshot://animal_welfare/2023-10-24/fur_laws.xlsx --dry-run
  

  Note that, since no steps are using this snapshot, the new snapshot will be added to the temporary dag.

• To update not only that snapshot, but also the steps that use it:

  $ etl update snapshot://animal_welfare/2023-10-24/fur_laws.xlsx --include-usages --dry-run
  

• To update all dependencies of the climate change impacts explorer:

  $ etl update data://explorers/climate/latest/climate_change_impacts --include-dependencies --dry-run
  

  Note that the code of the explorers step itself will not be updated (since it has version "latest"), but its dependencies will be updated in the dag.

• To update all snapshots in a namespace/version directory using wildcards:

  $ etl update "snapshot://climate/2025-07-18/*" --dry-run
  

  This will update all snapshot files matching the pattern in the climate/2025-07-18 directory.

Usage:

etl update [OPTIONS] [STEPS]...

Options: