Keeping analytics off the primary database

Questions about a product usually start as queries against its main database. How many tenants turned on SSO this month? Which teams grew the fastest? Each query is small. Run enough of them and they start competing with real customer traffic for the same database.

At Auth0 I work on change data capture (CDC) pipelines that stream identity data into Snowflake, so this kind of analysis can happen without touching the primary databases.

How CDC works

Every database keeps a log of the writes it commits. CDC reads that log and publishes each change as an event: this row was inserted, this one updated, this one deleted.

A consumer reads those events and builds its own copy of the data in the warehouse. Analysis runs against that copy. The primary database only does what it was built for, serving requests.

What has to be true for it to work

Moving data is the easy part. Keeping the copy correct is the actual work.

• Events need enough to rebuild state. The key, the operation, and the full row after the change. If the consumer has to call back into the primary to fill gaps, you have moved the load, not removed it.
• The consumer has to be idempotent. After any failure the log gets replayed, so the same event will arrive more than once. Applying it twice must give the same result as applying it once.
• Lag has to be measured. A warehouse that is an hour behind looks exactly like one that is up to date. Track how far behind the consumer is and alert on it, or nobody will notice.
• Schema changes need a plan. A new column in the source table should not break the pipeline, and a renamed one should not silently turn into nulls.

What you get

The primary database goes back to doing one job. Analysis gets its own system that can be as slow and heavy as it needs to be. And when someone asks a new question about the data, the answer does not depend on how busy production is that day.