The idea in brief
Bring source data into a warehouse, manage the reporting logic in dbt, and give Tableau clear, tested datasets. This article follows that approach from ingestion through to daily operations.
The reporting problem
Many teams inherit expensive ELT stacks dominated by per-connector fees and heavy ops. We rebuilt the stack for a multi-entity Oracle NetSuite customer on Google Cloud with dbt, BigQuery, and a thin Python / make.com ingestion layer. Result: ~60% lower run rate, simpler change management, and faster Tableau dashboards.
Why this approach
- Serverless warehouse: BigQuery delivers separation of storage/compute, slot autoscaling, and flat-rate options for predictable spend.
- Modelling you control: dbt makes transformations versioned, tested, and reviewable—no black-box connectors.
- Low-cost ingestion: Python on Cloud Run plus make.com scenarios handle Oracle NetSuite APIs without per-connector premiums.
- Tableau-ready outputs: Curated marts and certified extracts keep dashboards fast and trusted.
How the data flows
- Ingestion: Cloud Run jobs (Python) pull NetSuite via RESTlet/SuiteTalk; make.com orchestrates delta pulls, retries, and alerts. Files land in Cloud Storage, then load to BigQuery raw tables.
- Transform: dbt models layered as staging → core → marts, with freshness + schema tests. Metrics are defined once for Tableau/AI reuse.
- Serve: Tableau connects live/Extract to marts; row-level security driven from dbt dimension tables.
- Ops & observability: Cloud Scheduler triggers runs; Cloud Logging + dbt artifacts feed a lightweight health dashboard.
Connecting NetSuite
- Authentication: Token-based auth per environment; secrets in Secret Manager.
- Delta strategy: System notes + last modified timestamps drive incremental pulls; Cloud Storage keeps 30-day raw archives.
- Schema drift: Python normalizes field changes; dbt tests catch missing/extra columns before they hit marts.
- Error handling: make.com retries transient API errors; fatal errors open PagerDuty/Slack alerts with request IDs.
Where costs were reduced
- Ingestion cost: Replaced per-connector billing with Cloud Run + make.com scenario minutes; NetSuite API calls stayed within plan.
- Compute: dbt runs on scheduled slots; most models are incremental to avoid full scans.
- Storage: Tiered Cloud Storage for raw + Time Travel pruning in BigQuery to trim retention.
- Tableau extracts: Only business-critical dashboards use extracts; others run live with aggregate tables to minimize compute.
Keeping it running
- Git + CI: Pull requests run
dbt testand a slim data diff on staging. - Runbooks: Standard playbooks for failed loads, schema additions, and backfills.
- Data contracts: dbt tests and source freshness enforce SLAs with upstream app teams.
Delivering Tableau reports
- Certified data sources: Only marts with passing tests are published to Tableau Server/Cloud.
- Performance: Extracts are filtered and aggregated; live connections hit summary tables with clustering/partitioning.
- RLS: User entitlements stored in a dbt-managed dimension; applied in Tableau via data source filters.
Other cloud platforms
Prefer AWS? Swap BigQuery for Redshift or Snowflake; run Python ingestion in Lambda/Fargate; orchestrate with EventBridge + Step Functions. The principles remain: light ingestion, versioned transformations, cost-aware compute, and certified BI outputs.
What to take away
- Own your transformations with dbt; don’t outsource core logic to connectors.
- Keep ingestion thin and observable; API-first beats heavy agents.
- Design marts for the tools your users live in—here, Tableau—and enforce trust with tests + RLS.
- Cost wins come from architectural choices, not just discounts.
Need a similar rebuild? We’ve used this pattern to drop platform costs by more than 60% while speeding dashboard delivery. Let’s map it to your stack.
Need help building the data foundation behind this?