# How to Build a Data Warehouse Without a Data Engineer

> Build a full HubSpot, GA, and Google Ads warehouse with BigQuery, Airbyte, dbt, and Claude Code in about a day, no data engineer required.

**Type:** Play · **Read time:** 10 min · **For:** Managing Partner, COO · **Published:** 8 May 2026

[All resources](https://aigenticlab.com/resources) · [View as HTML](https://aigenticlab.com/resources/how-to-build-a-data-warehouse-without-a-data-engineer)

---

### Not a tooling gap
## The real problem is not the technology

Most firms without business intelligence are not held back by complexity. They are held back by an assumption: that proper data infrastructure needs a specialist hire, a six-figure budget, and a multi-month project. A data engineer costs north of £80,000 a year, so most professional services firms operate blind — pulling numbers from spreadsheets, chasing reports from HubSpot, and guessing at what Google Ads spend actually produces.

That assumption is the villain. AI vendors and data platform companies have spent years positioning business intelligence as enterprise territory. The demos are impressive. The pricing pages are not. The stack below dismantles the argument: every component has a generous free tier or is open-source, and Claude Code handles the technical complexity. The result looks like what a small specialist data team would build, because functionally that is what it is.

### The toolkit
## The stack, and why each piece earns its place

This build connects HubSpot, Google Analytics, and Google Ads into a single reporting layer with no hand-written code and no data engineer on the payroll. Each component does one job:

- **Google Cloud Platform and BigQuery** — the warehouse. Stores all raw and transformed data; the free tier covers query volumes a firm of 15–250 people typically generates.
- **Airbyte Cloud** — extraction and loading. Connects to HubSpot, Google Analytics, Google Ads, and dozens of sources, moving raw data into BigQuery on a schedule. The free trial starts only after the first successful sync, so configuration time does not burn it.
- **dbt Core** — transformation. Turns raw data into clean, structured models ready for analysis, triggered automatically by GitHub Actions.
- **GitHub** — version control and automation. dbt models live in a private repo; GitHub Actions runs transformations on a schedule, giving audit history and free compute at this scale.
- **Claude Code** — the AI coding assistant. Writes the dbt models, fixes errors, troubleshoots schema mismatches, and iterates on the structure directly inside the repo.
- **Lightdash** — the visualisation layer. Reads metric definitions written in dbt and turns them into charts and dashboards, with a built-in AI agent that answers questions from the data.

### First foundations
## Set up the cloud, the extractor, and the repo

Start by creating a Google Cloud project at console.cloud.google.com — the project name becomes the permanent project ID, so choose carefully. Under IAM, create a service account: a technical identity not tied to any individual's login, so integrations keep working when someone leaves. This build named it "dbt-runner" to reflect its role across dbt and BigQuery. Assign owner access for now (tighten later), generate a JSON key, and treat it like a password — it controls access to both the data and any billing.

Next, configure Airbyte. Set up the first connection to HubSpot via OAuth, then point the destination at BigQuery with the dataset set to "bronze". The naming convention matters: data lands in **bronze** (raw), is transformed through **silver**, and surfaces in **gold** (report-ready). This three-layer approach keeps raw data intact, makes transformations auditable, and stops reports breaking when source schemas change. The initial HubSpot sync pulls companies, contacts, and deals; Google Analytics and Google Ads go into their own schemas (bronze_g4, bronze_google_ads) because different sources often produce identically named tables. One practical note: the advertised Xero connector was not actually in the marketplace at build time — verify connector availability before designing a pipeline around it.

Finally, create a private GitHub repository inside your organisation, add a README to initialise it, and upload a project specification document — a markdown file describing what questions the warehouse must answer, which systems are involved, and the modelling approach. The spec does double duty: it gives Claude Code the context to generate accurate models and documents the project for anyone who joins later.

### The heavy lifting
## Let Claude Code build the dbt project

This is where Claude Code takes on most of the work. Connect it to the repo, create a project-specific environment for credentials, then prompt it to scaffold the full dbt structure. The prompt is specific: name the project, describe the directory structure, list the files. Claude Code builds a to-do list, works through each item, and shows every change as a diff — additions with a plus, deletions with a minus. When scaffolding is done, it opens a pull request and merges to main.

> Each of these is the kind of error that would take a developer time to diagnose and fix — Claude Code worked through them methodically, tried alternatives when the first attempt failed, and committed the corrected code back.

Subsequent prompts follow the same pattern for each layer:

- **Staging models** — one per source table, cleaning raw data and applying consistent naming. Claude Code writes the SQL, runs it, and validates rows are present.
- **Mart models** — one per business question: pipeline health (lead counts, stuck deals, average time to close), conversion funnel, attribution (Google Analytics click to HubSpot contact), paid media performance, lead generation by source, and sales activity.
- **Lightdash metrics** — metric definitions written inside dbt that Lightdash reads directly, with no need to recreate calculations in the visualisation layer.

Real errors surfaced and were resolved along the way: a JSON auth key that broke as plain text was re-encoded as base64 and decoded on use; a BigQuery region mismatch (EU vs Europe West) was diagnosed and the references matched; missing dbt packages and auth libraries were caught in test runs and added to the setup script; and HubSpot engagements missing from the first sync were enabled in Airbyte, then the new schema was screenshotted to Claude Code, which updated the models.

### Reporting layer
## Connect Lightdash and ship dashboards

Lightdash connects to BigQuery using the same service account JSON key from step one, and to the GitHub repo to read the dbt project directly. Set the target dataset to "gold" — the final, report-ready layer. On connect, Lightdash compiles the project and surfaces every model and metric.

A built-in AI agent can be configured with a system prompt (here, "marketing expert") and access to the data, letting team members ask questions in plain English and get charts back. Dashboards can be built manually by selecting metrics and chart types, or pushed programmatically through Lightdash's API using YAML files generated by Claude Code.

### Why it matters
## What this changes for a professional services firm

The KWA framework — Knowledge, Workflow, Agents — describes three layers of operational AI maturity, and business intelligence sits at the intersection of the first two. Before a firm can automate anything meaningful it needs knowledge: data that is centralised, accurate, and accessible. A pipeline like this creates that foundation — HubSpot, website, and paid media data in one place, cleaned to a consistent standard, and refreshed every 24 hours. From there, workflow automation becomes straightforward: scheduled reports replace manual spreadsheet-pulling, and anomalies trigger alerts instead of waiting for a monthly review.

The cost reflects the point. BigQuery, Airbyte Cloud, dbt Core, and GitHub are free or free at the volumes a 15–250-person firm generates; Lightdash offers a free trial then a self-hosted open-source option; Claude Code is included in existing Anthropic plans. The total is a fraction of a data engineer's day rate, and the build — from first prompt to working dashboards — took roughly one day of active work, troubleshooting included.

A Managing Partner or COO who has been putting off proper reporting because they cannot justify a data engineer now has a working alternative. The principle is the same one that applies across AI adoption: start with one workflow, get it working, then compound. Build the HubSpot pipeline first, verify the data is accurate, then add Google Analytics and Google Ads. When the foundation is solid, the analysis becomes straightforward.