On The 8 Spot

Replicating a city health center database into BigQuery: from a change-data stream to a privacy-split dashboard

A case study in moving production health data into an analytics warehouse in staged, reversible steps, with the personal data separated before anyone could query it

Download the long edition (EPUB, 35 KB)

At a glance

Situation A city government's health center information system kept its records in a managed MySQL service. Analysts wanted the data in BigQuery for research, planning and dashboards, under a written onboarding workflow that required tagging, dataset permissions and a vendor label
Approach Continuous change-data capture with Google Datastream from the production primary, one stream per database, started in waves. Then a column-by-column personal-data classification, authorized views that hide identifying columns, and a one-page Looker Studio dashboard built only on those views
Window 24 October to 10 November 2026, with the streams started over the weekend of 7 and 8 November
Output 16 running streams covering 650 of 784 tables, a classification of 5,119 columns, 636 views in 30 view datasets, a documented access decision, nine BigQuery queries rebuilt from twelve legacy ones, a one-page dashboard, and a report in Markdown, Word and slides
Method The consultant owned every approval and typed every password. Claude Code read the documents, did the checks and configuration in the cloud console, ran the queries, and wrote the reports
Status Data is flowing and the private dashboard works. Four large streams are not yet reconciled against the source, access to the raw data is deliberately left as it was, and nothing is public

The situation

The health center system is a set of services that write to many MySQL databases. For analysis, the city's data team had two documents: one describing how to ingest a database into BigQuery and set dataset permissions, and one describing onboarding and ways of working. Both assumed a dataset per source database, a tag on each dataset naming the vendor, and separate datasets for views.

Four constraints shaped the whole effort.

How the work was run

  1. Read the documents and the product docs first. Claude read the two workflow documents and Google's transfer and Datastream pages, and asked one question that mattered: what is the source, and how is it reachable? The consultant chose Datastream and the managed MySQL service as the source.
  2. Look before changing anything. Every early step was read-only. Claude checked whether the database could produce a change stream at all. The primary already had point-in-time recovery, so binary logging was on. The read replica did not, and Google's guide does not mention replicas, so the primary became the source. The trade-off was written down: the first copy would read from production, so it should run off-peak.
  3. Write the access request as a document, not a message. The request covered every database in one pass, listed the roles and the exact grant commands, and ended with a section meant to be pasted into an AI assistant for whoever would act on it. It was produced as Markdown and Word. When the roles turned out to have been granted by group, the document was kept as a record of what had been asked.
  4. Make small, named, reversible changes. The source needed four things: an allowlist of the stream service's regional addresses, a few connection-timeout and packet-size settings, a replication user, and a destination. The addresses were added as five separate entries with a shared name so they could be removed together. The settings were noted as instance-wide, so they also affected the production applications. The replication user got read access on one database first and more only as each wave started. The consultant chose and typed the password, in a form where Claude never saw it.
  5. Cut the scope with evidence. Claude queried table sizes through the database console and ranked what to leave out. Tokens, secrets and sign-in data were excluded on principle. Large derived or bulky tables were excluded on size and risk: a documents table of about 26 GiB, rendered PDF and HTML copies, an audit log with old and new values that can capture secrets, and a search index. Streamed volume fell from about 38 GiB to about 10 GiB, and the final scope was 650 of 784 tables across 16 streams.
  6. Build the cost model before starting. The model priced the first copy and weekly runs with growth. The outputs were estimates, and the report labels the prices as third-party and unverified.
  7. Write the permission request. A short note for the consultant's superiors asked for permission to run the streams on a specific weekend, worded around the city's health centers, at the consultant's request.
  8. Start in waves, watching the source. Before starting, Claude raised the source's binary-log retention from one day to seven, with the consultant's approval, because a stream that paused for more than a day could lose its place and need a full re-copy. The setting took effect without a restart. The smallest streams went first and were checked after fifteen minutes. The next waves followed once the earlier ones finished. The largest streams, the patient index, billing and documents, went last. The primary's CPU peaked at under five percent in the first twenty-four hours.
  9. Validate in layers. Claude compared stream event counts from the monitoring service with row counts in BigQuery, per dataset, and searched every dataset for column names suggesting credentials.
  10. Classify the columns. Claude read every column in the 15 filled datasets and proposed a personal-data label for each, in a spreadsheet with a table of recommended values. The consultant reviewed every flagged column.
  11. Build the safe layer. Two kinds of view were created for each dataset: one with the personal data and one without. All were authorized views, so a person could read the without-personal-data view without having read access to the raw tables.
  12. Rebuild the legacy reports. Twelve existing queries from the old reporting tool were converted to Markdown and nine of them rebuilt in BigQuery. A derived age-at-registration view was added so the dashboard never needed a date of birth.
  13. Build one page. A Looker Studio report in the city's own account used only the without-personal-data views, with monthly trend, sex and age breakdowns, filters and a last-seven-days tile. The consultant took the screenshot for the documentation after Claude's own capture froze.

What the checks found

Streams and the source

All 16 streams reached the running state with no warnings or errors in the logs for the six hours checked. Nine streams matched row for row: their event counts in the monitoring service equalled the rows in BigQuery. Two had small gaps consistent with changes captured after the copy. Four, covering finance, outpatient, insurance and the patient index, showed event counts about one and a quarter to a little over two times their BigQuery row counts.

Merge mode explains part of that. A row updated many times is one row in BigQuery but many events. Claude did not accept the explanation without a check: it could also be a retried copy. The honest answer was that the evidence could not tell them apart. The counters had also lagged on first read, and rereading them from the monitoring API closed some gaps. The decisive test is a per-table count on the source against BigQuery. Claude did not have a credential for that and refused to take one from an application's environment file, and it did not create a storage bucket to export counts without the consultant's approval. The consultant deferred the check to an evening session at home, a script covering the 233 tables was offered, and the item was flagged in a memory note so it would not be lost.

No credential-like columns

A search for password, token, secret and key column names across all datasets found none, including the authentication and insurance datasets.

Classification

The classification covered 5,119 columns. Rules and name patterns produced the first pass. The result was 339 columns recommended for exclusion from the without-personal-data view, 79 that needed a human decision, and two credential-like columns to exclude from both views. The consultant reviewed all 79 and decided to keep them in both views, because the data is used for research and planning and fields such as address, sex and insurance type are needed for that. The decision, the rationale and the date were written into the spreadsheet, the Markdown copy and the CSV for a later audit. Claude did not write column descriptions, because there was no source documentation for them and an audit would see invented text.

Views and access

All 636 views were created and checked. The raw datasets, however, had a project-wide reader entry, so anyone with project-level viewer access could still read the raw tables. Claude presented two options: leave the raw access alone, which needs a written justification, or replace it with named groups, at the cost of removing access from some existing users. The consultant chose to leave it as it was and asked for that to be recorded as their own decision. The group work waits until restricted members exist, and the security clean-up was paused.

Dashboard numbers

Rebuilding the legacy queries exposed a flaw that the original queries shared. A visits table repeats some visit identifiers, up to 64 times, which inflates consultation counts by 0.33 percent. It was documented instead of silently fixed. A last-seven-days tile showed a fall of 14 percent. Claude ran the same query directly in BigQuery and got the same numbers as Looker, so the drop was not a loading lag. It came from the calendar: almost no consultations are recorded on weekends, one date had no rows at all, and the week held one fewer working day. Run again the next day, the same tile showed a rise of 43 percent. The fix, comparing 28 days to 28 days, was written into a future-updates file and the tile left as is for the time being.

Where it went wrong, and how it was handled

What was left open

Takeaways

  1. Check the source before the destination. Whether the database could emit changes at all decided the whole design, and it took one read-only visit to find out.
  2. Name what you add. Five named allowlist entries and a dedicated replication user make every change easy to undo.
  3. Cut scope with numbers. A ranked list of table sizes and risks turned "exclude things that look big" into a defensible scope, and shrank the copy by roughly three quarters.
  4. Start small and watch the live system. Waves, a fifteen-minute check and a CPU look kept the first copy from becoming an incident.
  5. Do not explain away a mismatch. Merge mode is a plausible reason for event counts above row counts. It is also a reason that cannot be told from a retry without a source count, so the item stayed open and visible.
  6. Separate identity before you analyse. Classifying every column and publishing views that hide identifying fields made the dashboard safe to build before the access question was settled.
  7. Record decisions as the owner's. The choices about the 79 review columns and the raw-data access were written down as the consultant's, with the date and the reason, which is what an audit will ask for.
  8. Check a surprising tile against the source. A 14 percent fall turned out to be the calendar, and finding that took one query.

About these numbers

Stream, table, view and column counts come from the cloud console, the monitoring service, BigQuery's information schema and the files Claude generated. Event-to-row comparisons were taken at a fixed time and changed as merges landed. The size reduction is from the source's own size query. Cost figures in the report are third-party price assumptions and were not verified, so none are repeated here. The 14 percent and 43 percent figures were calculated by running the dashboard's queries directly in BigQuery. Names of the city, the health centers, the cloud projects, instances, groups, accounts, vendors and people are deliberately left out.