Data Architecture Generator

Recommended tools

• Ingestion

  Managed ELT (Fivetran / Airbyte Cloud)

  Pulls data from each source into the warehouse on a schedule

  NetSuite, Shopify expose APIs, so data can be retrieved automatically and loaded into the warehouse. A balanced priority with limited engineering time favours managed connectors to reach useful output sooner.

• Ingestion

  Automated file loads

  Brings spreadsheets and CSVs in alongside system data

  Google Sheets are manual today, but can be loaded on a schedule from a shared folder or sheet so nobody copy-pastes.

• Warehouse

  BigQuery

  Central store and single source of truth

  You already use Google Cloud, so BigQuery keeps everything in one ecosystem with little to manage.

• Modelling

  dbt

  Turns raw data into tested, documented business metrics

  Keeps business logic in plain, version-controlled SQL with tests, so metrics are defined once and trusted everywhere.

• BI / Analytics

  Looker Studio

  Dashboards and reports for the team

  Free, connects directly to BigQuery, and is easy for non-technical users.

Build vs buy for ingestion

A balanced priority with limited engineering time favours managed connectors to reach useful output sooner.

• Faster to first useful output, but software costs grow with data volume and connectors.
• Less engineering to maintain, with less control over exactly how data is pulled.

Architecture principles

• Keep core business logic in version-controlled SQL or code, not locked inside drag-and-drop tools.
• Avoid using the dashboard layer as the main place for complex data modelling.
• Centralise important raw data before building lots of disconnected reports.
• Prefer an architecture where the business keeps access to its underlying data.
• Avoid adding tools when an existing platform already solves the problem well.
• Separate ingestion, modelling and presentation so each can change independently.

A high-level starting point, not a costed plan. Exact costs and timelines depend on data volumes and detail we haven't asked about.