COE-LA Cloud Database Migration Guide

What this page is

A preview of COE-LA's 9 reports now running on a new, faster cloud database, plus tools to view them and compare their speed against the original versions.

How to open it

https://internal.la-coe.com

You'll need to be signed in to your organization Google account to view the reports. If a report area appears blank, sign in to that account and refresh the page.

Using the report tabs

Tab	What it's for
Browse v2	The simplest way to view the 9 updated reports — click any report name to open it directly. This is all most people need.
Compare	See the old and new versions side by side. Each row shows the Original (left) next to the updated `_v2` (right), with an optional "Reload & time" button to watch them reload and see which finishes first.

What to expect

These are the same reports with the same data — nothing about the content or the numbers has changed. The only difference is that the updated versions load faster.

How much faster? — a quick summary

Across all 9 reports, the updated versions loaded faster every time. On average, a report that used to take about 4.1 seconds now loads in about 2.8 seconds — roughly a third quicker — and the biggest improvements nearly cut the wait in half.

Report	Before (old)	Now (updated)
Success — Compare Programs	3.9 s	1.7 s
Success — Compare a Program	3.6 s	2.2 s
Success — Time Trend	4.2 s	3.3 s
Progress — Compare Programs	3.4 s	2.9 s
Progress — Compare a Program	5.3 s	3.8 s
Progress — Time Trend	6.6 s	3.6 s
Earning — Compare a Program	3.3 s	3.0 s
Earning — Compare Programs	3.0 s	2.9 s
Earning — Time Trend	4.0 s	1.9 s
Average	~4.1 s	~2.8 s

Times are typical load times measured during testing; exact numbers vary a little run to run. Every updated report showed the exact same figures as the original.

Questions?

If anything doesn't look right or you have questions, please contact: [Your name/contact]

Purpose of the migration

COE-LA's 9 Looker Studio reports were moved from Google Sheets data sources to a dedicated cloud PostgreSQL database on Render. This eliminates the manual CSV export/import workflow, removes stale data, and gives the reports a real query engine — the same reports and the same data, just faster and easier to maintain.

Architecture

The pipeline has three layers:

MacBook Pro (on-premises source). The source of truth is a local SQL Server 2025 database on a Mac. A Python script (etl_to_postgres.py) pulls from its 9 reporting views and reloads the cloud database — run manually a few times a year, whenever the data updates (a full truncate + reload each run).
Render Postgres (cloud database). A single always-on PostgreSQL database (coe_reporting) holds 9 flat tables (~2.09M rows total), indexed on the reports' filter columns. This is the one shared source both reporting tools read from.
Looker Studio & Power BI Desktop (reporting consumers). The 9 Looker Studio reports and the staff Power BI reports connect directly to the Render database via the native PostgreSQL connector and read live — no more CSV exports or embedded stale data.

Performance results

All 9 reports were benchmarked (original vs. updated) on a cold filter change. Postgres was faster or tied on every report, and every report's data matched the original.

Report	Original	Updated (_v2)	Faster by
Progress — Time Trend	6.6 s	3.6 s	46%
Progress — Compare a Program	5.3 s	3.8 s	28%
Progress — Compare Programs	3.4 s	2.9 s	15%
Success — Time Trend	4.2 s	3.3 s	21%
Success — Compare a Program	3.6 s	2.2 s	40%
Success — Compare Programs	3.9 s	1.7 s	55%
Earning — Time Trend	4.0 s	1.9 s	53%
Earning — Compare a Program	3.3 s	3.0 s	10%
Earning — Compare Programs	3.0 s	2.9 s	~tie
Average	~4.1 s	~2.8 s	~32%

Note: the database query itself runs in ~18 ms — most of the load time is Looker Studio's own overhead, so the improvement comes from the faster data source, and further database tuning wouldn't help. Warm/cached reloads are about equal (Looker caches results); the gains show on cold loads and filter changes, which is what users wait on.

What changed

Destination	Render PostgreSQL (Basic-256mb), Oregon region
Monthly cost	~$7.50 ($6 instance + $1.50 for 5 GB storage)
Database	`coe_reporting` — 9 flat tables, one per report
Source of truth	On-prem SQL Server 2025 (`COELA`), read via 9 `_LOOKER_VIEW` views
Data volume	2,089,617 rows · ~620 MB · 51 indexes
ETL	`etl_to_postgres.py` — manual, truncate + reload per refresh

Data accuracy

Every updated report showed the exact same figures as the original — the change is purely about speed. (One cosmetic exception: Earning — Compare Programs displays earnings with decimals where the original rounds; the underlying values are identical.)

Recommendation

The migration met its goal on both speed and data accuracy — no report regressed. Recommendation: cut over to the updated reports, and continue migrating the Power BI reports to the same Render database.

Each _v2 production report (left) beside its staging counterpart (right) — the current live data vs the corrected data awaiting review. The changes are specific and easy to miss on two near-identical charts, so look for:

Median Change in Earnings now shows values where the live report is blank (Employment & Earnings reports).
Latest-year percentages sit within 0–100 (the live report can show values in the thousands), and the Top 15 is reranked by rate rather than raw count.
Progress Top 15 lists 15 programs where the live report shows about 8.
Sample sizes (n=…) are whole numbers.

Full list of intended changes: REVIEW_CHECKLIST.md.

Workflow Comparison

Current manual workflow vs new cloud-automated workflow

Dimension	Current workflow	New cloud workflow
Data source technology
Report data source	Google Sheets (flat CSV imported manually)	Render Postgres — live cloud database
Data format	Flat denormalized spreadsheet	Indexed database tables, query-optimized
Connection type	Static file import — snapshot in time	Live native Postgres connector
CSV download hosting	Google Drive — new file ID on every upload	Cloudflare R2 — permanent URLs, zero egress fees
ETL & data refresh
Update process	36 manual steps across 9 reports	One Python script — single command
Tools involved	SSMS export → CSV → Google Drive → Looker import	etl_to_postgres.py + generate_csvs.py
Error risk	High — manual copy/paste, wrong file, missed report	Low — automated, consistent every run
Update frequency	A few times per year	A few times per year — no change needed
Report performance
Initial load time	5–15 seconds	Under 1 second
Filter / dropdown	3–8 seconds per interaction	Near instant
Multiple users	Gets slower as concurrent users increase	No degradation — database handles concurrency
Query engine	Google Sheets — loads entire file per query	PostgreSQL — returns only requested rows
User operations
Report experience	Slow, frustrating wait on every filter	Fast, responsive — no visible wait
CSV download button	Broken after every data update — requires manual fix	Permanent URL — always works, always fresh data
Download experience	Redirects to Google Drive preview page	Direct download — page stays open
Login required	Sometimes — if Google Drive permissions lapse	Never — public direct download
Admin operations
Steps per data refresh	36 manual steps (9 reports × 4 steps each)	1 command — python etl_to_postgres.py
Download link maintenance	Update 9 report buttons after every upload	Zero — links never change
Risk of stale data	High — easy to forget a report or wrong file	None — all 9 reports update from same script run
Power BI
Data delivery	Stale data embedded in .pbix file	Live connection to Render Postgres
Refresh process	Redistribute new .pbix file to all staff	Staff click Refresh in Power BI Desktop
Google Workspace exit
Google Sheets	Required — Looker data source	Eliminated — replaced by Render Postgres
Google Drive (CSVs)	Required — download button hosting	Eliminated — replaced by Cloudflare R2
Google Drive (docs)	Required — shared internal documents	Move to COE-LA Dropbox (already paid)
Looker Studio	Free Google product — stays	Stays — reconnects to Postgres, no cost change
Google Workspace subscription	~$30–60/mo (5 users)	$0 — cancel after migration complete
Domain (coe-la.com)
Current registrar	Squarespace (formerly Google Domains)	Transfer to Cloudflare Registrar
Annual renewal cost	$20/yr (Squarespace standard rate since Sep 2024)	~$10/yr (Cloudflare at-cost pricing)
DNS management	Squarespace dashboard (separate from everything)	Cloudflare — same place as R2, simpler to manage
Total cost comparison
Google Workspace	~$30–60/mo	$0 saves $30–60/mo
Domain renewal	$20/yr	$10/yr saves $10/yr
Render Postgres	$0	$7.50/mo
Cloudflare R2	$0	$0 (free tier)
Net monthly savings	—	~$22–52/mo + $10/yr on domain after $7.50/mo Postgres cost

Prepared by Howard Chen · howardhchen@howardhchen.com