How to Build a Modern Data Infrastructure: A Practical Guide

Most growing companies don't have a data problem. They have a plumbing problem: numbers spread across a CRM, five ad platforms, Stripe and a dozen spreadsheets, and nobody is sure which one is right.

This guide is for founders and heads of data, ops or marketing who want a data infrastructure people actually use. I'll walk through each layer, the tools I pick at each one, and where teams usually overbuild.

What is a data infrastructure?#

A data infrastructure is the set of tools and layers that move your data end to end: from the source systems where it's created, into central storage, through cleaning and modeling, and out to the people and systems that use it.

It's not one tool. It's a chain, and it's only as useful as its weakest link.

The goal is boring on purpose: the same question gets the same answer, no matter who asks it or which tool they open.

Why spreadsheets and all-in-one tools stop working#

Your data is the only thing that tells you where the business stands: what works, what doesn't, and what to fix next. The problem is that it lives in more places every year. CRMs, ad platforms, your website, your app, billing, support tools, spreadsheets.

Excel and off-the-shelf "all-in-one" reporting tools work for a while. Then you hit the same walls:

• Two dashboards show two different revenue numbers.
• Someone spends every Monday exporting CSVs and pasting them together.
• Historical data disappears because some platforms cap how far back you can query.
• You can't join ad spend to CRM deals to actual payments, so you can't calculate real CAC or ROAS. At that point you don't need another tool. You need infrastructure.

What does a modern data infrastructure look like?#

Here is the architecture I use as a starting point for most clients. Every layer can be swapped, but the shape stays the same.

Let's go layer by layer.

Layer 1: How do you extract data from your sources?#

The first step is getting data out of every source and into one place. There are two schools of thought here.

Team code-first builds every connector from scratch, even for dozens of sources. Full control, and a lot of maintenance when APIs change.

Team efficiency-first is where I sit. Why rebuild a Google Ads or HubSpot connector when mature tools already handle pagination, rate limits, schema changes and incremental loads?

My default split:

• Standard SaaS sources (Google Ads, Meta Ads, HubSpot, Salesforce, Stripe, Shopify): a managed connector tool like Airbyte (open source or cloud) or Fivetran. Stitch still exists but I rarely reach for it now.
• Custom APIs, internal tools, niche platforms: Python, using dlt (data load tool) so pipelines stay modular, incremental and testable instead of becoming one-off scripts. I compared the trade-offs of managed connectors in more detail in No-Code ELT Tools: Pros and Cons.

Here's what a small dlt pipeline looks like for a custom REST API. It loads only records updated since the last run and merges them on the primary key:

import dlt
from dlt.sources.rest_api import rest_api_source

crm_source = rest_api_source({
    "client": {
        "base_url": "https://api.example-crm.com/v1/",
        "auth": {"type": "bearer", "token": dlt.secrets["crm_api_token"]},
    },
    "resources": [
        {
            "name": "deals",
            "primary_key": "id",
            "write_disposition": "merge",
            "endpoint": {
                "path": "deals",
                "params": {"updated_since": "{incremental.start_value}"},
                "incremental": {
                    "cursor_path": "updated_at",
                    "initial_value": "2025-01-01T00:00:00Z",
                },
            },
        },
    ],
})

pipeline = dlt.pipeline(
    pipeline_name="crm_deals",
    destination="bigquery",
    dataset_name="raw_crm",
)

print(pipeline.run(crm_source))

What to check before you pick an ingestion tool:

• Sync frequency: do you really need hourly data, or is daily enough? Daily is cheaper and simpler.
• Incremental loads: full reloads get expensive fast on large tables.
• Schema changes: what happens when a source adds or renames a field?
• Ownership: can you export your configs and move them if you change vendors? Ingestion is also where messy reality shows up. For a PPC agency, we reconciled 125+ client ad accounts into one model, and most of the work was naming and mapping, not moving bytes. If you want this layer handled end to end, that's what our data integration service covers.

Layer 2: Where should you store your data? Warehouse vs lake vs lakehouse#

Once data is extracted, it needs one central home. You have three main options, plus a lightweight fourth for small datasets.

I still prefer a data warehouse for most businesses. It's simple to run, every BI and AI tool connects to it, and business users can query it without a platform team.

The line between warehouse and lakehouse is also blurring. Open table formats like Apache Iceberg are now readable by Snowflake, BigQuery, Databricks and AWS Athena, so you can start in a warehouse and keep the option to move heavy workloads to a lakehouse later.

For large enterprises and AI-heavy use cases with lots of unstructured data, a lakehouse can be the right call. For a 20 to 200 person company with a CRM, ads and billing, it's usually overkill. I explain the lighter setup in A Simple Data Platform for Small Businesses.

Layer 3: How do you make the data usable?#

Stored data isn't usable data. Raw tables are full of duplicates, cryptic column names, test records and amounts stored in cents. Transformation is where you clean that up, apply naming conventions, join sources together and build logical models.

The tools depend on your storage:

• Warehouses: dbt is the standard. SQLMesh is a solid alternative with built-in change planning.
• Lakes and lakehouses: Apache Spark (often on Databricks or AWS Glue), writing to Delta or Iceberg tables. I use dbt on almost every project, with a simple three-layer structure:

1. Staging: one model per source table. Rename, cast types, filter junk. No business logic.
2. Intermediate: joins and reusable logic (for example, matching Stripe customers to CRM companies).
3. Marts: final tables built for a purpose, like fct_revenue or dim_customers. This is what dashboards and AI read. A staging model is short and boring, which is exactly what you want:

-- models/staging/stripe/stg_stripe__charges.sql
with source as (

    select * from {{ source('stripe', 'charges') }}

)

select
    id                  as charge_id,
    customer            as customer_id,
    amount / 100.0      as amount,
    upper(currency)     as currency,
    status,
    created             as created_at
from source
where livemode = true

And tests catch problems before your CEO does:

# models/staging/stripe/_stripe__models.yml
models:
  - name: stg_stripe__charges
    columns:
      - name: charge_id
        data_tests:
          - unique
          - not_null
      - name: created_at
        data_tests:
          - not_null

This modeling layer is the heart of what we build in our data foundations work. Get it right and every layer after it gets easier.

Layer 4: How do your pipelines run on schedule?#

Ingestion and transformation need to run in the right order, retry on failure and alert someone when they break. That's orchestration.

Pick the lightest option that does the job:

• Simple stacks (a few sources, daily refresh): your ingestion tool's scheduler plus dbt Cloud jobs, or a scheduled GitHub Actions workflow running dbt build.
• Growing stacks with dependencies across many pipelines: Dagster or Apache Airflow 3. If you're on AWS, Amazon MWAA runs Airflow for you; on GCP, Cloud Composer does the same. I like Dagster for its asset-based view of pipelines and Airflow for its ecosystem. What I don't like is deploying Airflow for five daily syncs. That's infrastructure for the sake of infrastructure.

Whatever you choose, add alerting (Slack or email on failure) and freshness checks on key tables. A dashboard that silently shows last week's numbers is worse than no dashboard.

Layer 5: How do you make your data accessible?#

Modeled data only creates value when the people making decisions can use it, usually without writing SQL.

• Dashboards: Looker Studio, Metabase, Power BI, Tableau or Lightdash. Build fewer, better dashboards tied to real decisions.
• Automated reports: weekly summaries sent to Slack or email via Make, Zapier, n8n or a small script. Great for leadership teams who won't open a BI tool.
• Reverse ETL: push modeled data back into the tools teams already use, like lead scores into HubSpot or offline conversions into Google Ads. Hightouch and Census are the common tools.
• Embedded analytics: put charts directly inside internal tools or customer-facing apps.

What makes a data infrastructure AI-ready?#

Everyone wants AI agents that answer questions about the business. Most of them fail for the same reason: they're pointed at raw, undocumented tables and asked to guess how revenue is calculated.

An AI-ready data infrastructure has:

• Clean, modeled marts with clear names, not 400 raw tables.
• One definition per metric, ideally in a semantic layer (dbt Semantic Layer or Cube) so humans and AI get the same answer.
• Documentation on models and columns. LLMs read descriptions too.
• Access controls so an agent only sees what the person asking is allowed to see.
• Freshness and quality tests so the AI doesn't confidently report stale numbers. The good news: these are the same things that make dashboards trustworthy. Get AI-ready by starting with your data. When the foundation is solid, our AI systems work builds agents on top of it.

Checklist: is your data infrastructure working?#

• Every key metric (revenue, CAC, ROAS, churn) has exactly one definition.
• Data from your main sources lands in one warehouse automatically.
• Raw data is kept, so you can rebuild history when logic changes.
• Core models have tests, and failures alert a real person.
• Nobody exports CSVs to build a recurring report.
• Business users can answer common questions without asking an engineer.
• You could switch BI or AI tools without rebuilding your models.

FAQ#

How long does it take to build a data infrastructure?#

A focused first version covering 3 to 5 core sources, a warehouse, dbt models and one or two dashboards is usually a matter of weeks, not months. The long tail is adding sources and refining definitions. Start with the questions you need answered this quarter and expand from there.

Do small businesses need a data warehouse?#

If you have more than two or three data sources and want to combine them, yes. BigQuery and Snowflake bill mostly on usage, so a small company's warehouse costs little to run. What matters more than size is whether you make decisions that need data from several tools at once.

What is the difference between a data warehouse and a data lake?#

A data warehouse stores structured, modeled data optimized for SQL queries and reporting. A data lake stores raw files of any format, which is useful for ML and large-scale processing but harder for business users. A lakehouse adds table formats like Iceberg or Delta on top of a lake to get warehouse-like reliability.

Should I build or buy data connectors?#

Buy connectors for standard SaaS sources: they're maintained by vendors who track every API change. Build only for custom or niche sources, and use a framework like dlt so your code stays maintainable. Most teams get the best result from a mix of both.

Do I need Airflow to run my data pipelines?#

Not at first. For a few daily pipelines, the schedulers in your ingestion tool and dbt (or GitHub Actions) are enough. Bring in Airflow or Dagster when you have many dependent pipelines, multiple teams or complex retry and backfill needs.

Key takeaways#

• Think in layers: ingestion, storage, transformation, orchestration, activation.
• Buy what's standard, build what's specific.
• Start with a warehouse and dbt unless you have a clear reason not to.
• Invest most in modeling and metric definitions. That's what makes dashboards trusted and AI useful.
• Keep it modular so you can swap any tool without starting over. Your data isn't just a record of the past. It's what you'll use to decide what to do next, and soon what your AI agents use too. Building the infrastructure underneath it is one of the best calls you can make.

Want a data infrastructure your team trusts, built and maintained for you? Book a call with Dublo and we'll map out the right stack for your sources and goals.