Nightly PostgreSQL to BigQuery sales ETL
This data pipeline flowchart documents a nightly sales ETL run from PostgreSQL to BigQuery. Changed order records land first in Cloud Storage, where the batch is checked against its expected schema before any warehouse write occurs. Invalid files move to quarantine and notify the data engineer.
Open it in the AI editor with a prompt pre-filled — keep what works, change what doesn't.
Scenario
A retail team refreshes its sales reporting every night from an operational PostgreSQL database. The flow makes data-quality stops visible before warehouse tables are updated.
Key decisions
- Incremental extraction: Only orders changed since the previous run are exported.
- Schema contract: Unexpected columns or types are isolated before they contaminate analytics.
- Business reconciliation: Row counts and revenue totals must agree before publishing.
- Idempotent load: MERGE updates the order fact table without creating duplicates.
When to reuse this
Use this for a daily transactional-to-warehouse ETL job with a cloud object-store landing zone. It suits reporting pipelines where a failed batch is safer than partial dashboard data.
Frequently asked questions
Why land files before loading BigQuery?
Why use MERGE for orders?
What should the revenue check compare?
Tweak it with chat, export PNG/SVG, or fork it for your own use case.