Week 1 · Ingest anything › Day 5

Day 5. SharePoint, OneDrive, Google Drive, SFTP and object storage

1. Why this matters

"Shared folder" in a modern company usually means SharePoint/OneDrive (Microsoft shops), Google Drive (Google shops), an SFTP server (vendors, hotel PMS, payroll, banks) or a bucket (S3, Azure Blob/ADLS Gen2, GCS). Each has its own way of listing files, detecting changes and failing.

2. SharePoint and OneDrive via Graph

Same app-registration pattern as Day 4, permission Sites.Selected (scoped to one site, preferred) or Files.Read.All. Every library is a drive; you list children and download content. The delta endpoint returns only what changed since a token you saved: a server-side watermark.

site = requests.get("https://graph.microsoft.com/v1.0/sites/contoso.sharepoint.com:/sites/Finance", headers=H).json()
drive = requests.get(f"https://graph.microsoft.com/v1.0/sites/{site['id']}/drive", headers=H).json()
url = saved_delta_link or f"https://graph.microsoft.com/v1.0/drives/{drive['id']}/root:/Exports/QSR:/delta"
while url:
    page = requests.get(url, headers=H, timeout=30).json()
    for item in page["value"]:
        if "file" in item and "deleted" not in item:
            content = requests.get(f"https://graph.microsoft.com/v1.0/drives/{drive['id']}/items/{item['id']}/content",
                                   headers=H, timeout=60).content
            # land(content, name=item["name"], etag=item["eTag"], modified=item["lastModifiedDateTime"])
    url = page.get("@odata.nextLink")
    if "@odata.deltaLink" in page:
        saved_delta_link = page["@odata.deltaLink"]   # persist this; next run starts from it

3. Google Drive

Use a service account, share the folder with the service account's email, and query with files.list(q="'<folderId>' in parents and trashed=false and modifiedTime > '...'"). Google Sheets need files.export to CSV/XLSX; regular files use files.get_media. The changes API is Drive's equivalent of delta.

4. SFTP

Vendors push to (or let you pull from) SFTP. Two classic failure modes: the partial upload (you read while they write) and host-key surprises (the vendor rebuilt their server).

import paramiko, stat
def pull_sftp(host, user, key_path, remote_dir, local_dir, known_hosts="~/.ssh/known_hosts"):
    client = paramiko.SSHClient()
    client.load_host_keys(os.path.expanduser(known_hosts))        # reject unknown hosts: no AutoAddPolicy in prod
    client.connect(host, username=user, key_filename=key_path, timeout=30)
    sftp = client.open_sftp()
    for a in sftp.listdir_attr(remote_dir):
        if stat.S_ISREG(a.st_mode) and a.filename.endswith(".txt"):   # vendor renames .partial -> .txt when done
            sftp.get(f"{remote_dir}/{a.filename}", str(local_dir / a.filename))
    client.close()

Agree a completion signal with the vendor: rename on finish (.partial → .txt, as in the course data), a _SUCCESS or .done marker file, or a row count in a trailer line. Without one, use the size-stable check from Day 3.

5. Object storage as your raw zone

From here on, think of the raw zone as a bucket, even if it's a local folder today. Buckets are cheap (cents per GB-month), durable and every tool reads them. Locally you can run MinIO (S3-compatible) in Docker; in Azure you'd use ADLS Gen2; fsspec/s3fs/adlfs let one Python path string work against all of them.

import fsspec
fs = fsspec.filesystem("s3", key="minio", secret="minio123", client_kwargs={"endpoint_url": "http://localhost:9000"})
fs.put("data/raw/hotel/business_date=2026-09-14/pms_bookings_20260914__ab12cd34.txt",
       "raw/hotel/business_date=2026-09-14/pms_bookings_20260914__ab12cd34.txt")