RESTAPI to Bronze Databricks: Public API

Part of: Databricks Ingestion Playbook

When a source exposes a public or authenticated REST API and volume is manageable, you can skip the intermediate storage layer entirely. A Databricks notebook fetches data via Python’s requests library, paginates through the API, converts the result to a Spark DataFrame, and writes directly to Bronze Delta. The NYC TLC SODA API is a real public dataset — 100M+ rows of trip records — making it a useful stand-in for any paginated JSON API you'll encounter in production.

Setup steps

1. Register for a SODA app token — Free at data.cityofnewyork.us. Unauthenticated requests are throttled to 1 req/sec; an app token raises this significantly. Store it in a Databricks Secret Scope.
2. Understand the pagination model — SODA supports $limit and $offset. Fetch 50,000 rows per page, incrementing offset until a page returns fewer rows than the limit.
3. Parameterize by month — Pass year_month as a notebook widget so the job is idempotent. Re-running for the same month overwrites only that partition via replaceWhere.
4. Add retry logic — Wrap each page fetch in a retry loop. SODA occasionally returns 503s on high-traffic datasets — exponential backoff handles this cleanly.
5. Schedule via Databricks Workflows — Create a job with a monthly schedule, passing the target year_month as a parameter. Set a 3-retry policy at the job level.

Create a Databricks Notebook to loop through data and generate fact-table

# Databricks notebook source
import sys
import os
# Go two levels up to reach the project root
project_root = os.path.abspath(os.path.join(os.getcwd(), "../.."))

if project_root not in sys.path:
    sys.path.append(project_root)

import urllib.request
import shutil
from datetime import datetime
from datetime import date, datetime, timezone
from dateutil.relativedelta import relativedelta
from modules.utils.date_utils import get_target_yyyymm
from modules.data_loader.file_downloader import download_file

# Obtains the year-month for 2 months prior to the current month in yyyy-MM format
formatted_date = get_target_yyyymm(2)

# Define the local directory for this date's data
dir_path = f"/Volumes/nyctaxi/00_landing/data_sources/nyctaxi_yellow/{formatted_date}"

# Define the full path for the downloaded file
local_path = f"{dir_path}/yellow_tripdata_{formatted_date}.parquet"

try:
    # Check if the file already exists
    dbutils.fs.ls(local_path)

    # If the file already exists then set continue_downstream to no
    dbutils.jobs.taskValues.set(key="continue_downstream", value="no")
    print("File already downloaded, aborting downstream tasks")
except:
    try:
        # Construct the URL for the Parquet file corresponding to this month
        url = f"https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_{formatted_date}.parquet"

        # Download the file
        # Create the local directory for this date's data
        download_file(url, dir_path, local_path)

        # Set continue_downstream to yes if the file was loaded
        dbutils.jobs.taskValues.set(key="continue_downstream", value="yes")
        print("File succesfully uploaded in current run")
    except Exception as e:
        # Set continue downstream to no if the file was not loaded
        dbutils.jobs.taskValues.set(key="continue_downstream", value="no")
        print(f"File download failed: {str(e)}")

Create a Databricks Notebook to do a single lookup for dimensional-tables

# Databricks notebook source
import sys
import os
# Go two levels up to reach the project root
project_root = os.path.abspath(os.path.join(os.getcwd(), "../.."))

if project_root not in sys.path:
    sys.path.append(project_root)

import urllib.request
import shutil
from modules.data_loader.file_downloader import download_file

try:
    # Construct the URL for the Parquet file corresponding to this month
    url = "https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv"

    # Define and create the local directory for this date's data
    dir_path = f"/Volumes/nyctaxi/00_landing/data_sources/lookup"

    # Define the full path for the downloaded file
    local_path = f"{dir_path}/taxi_zone_lookup.csv"

    # Download the file
    download_file(url, dir_path, local_path)

    dbutils.jobs.taskValues.set(key="continue_downstream", value="yes")
    print("File succesfully uploaded")
except Exception as e:
    dbutils.jobs.taskValues.set(key="continue_downstream", value="no")
    print(f"File download failed: {str(e)}")

Github: Link