Analytics to Data Warehouse: Building the Pipeline

Moving analytics to a data warehouse means copying raw product event data into Snowflake, BigQuery, or Redshift so it can be joined against the billing, CRM, and support data that lives in other systems. A product analytics data warehouse pipeline has four parts: an export from the analytics tool, a load into the warehouse, a schema the events land in, and a transformation layer that turns raw events into models people query.

Most of the difficulty sits in three decisions. How the schema is shaped, how often you load, and which identity key ties a product user to a Stripe customer and a CRM account. Get those wrong and you end up with a warehouse full of events nobody can join to revenue. This guide walks through each of them, plus what is worth modeling once the data lands.

What Moving Analytics to a Warehouse Means

Your analytics tool knows what people do in your product. It does not know how much they pay, how often they contact support, which rep closed them, or when their contract renews. A warehouse is the place where those separate facts sit in the same schema so a single query can span them.

Take a question like: which behaviors in the first 30 days predict accounts that expand within the first year? Answering it requires behavioral events on one side and contract and revenue records on the other. Neither system holds both halves. In the warehouse the join is three lines of SQL.

The second reason is historical depth. Analytics platforms are tuned for interactive queries over recent windows. A warehouse stores years of event history cheaply and will happily scan all of it for a long horizon cohort study, a seasonality check, or a year over year comparison where the comparison period is genuinely three years back.

When You Need the Warehouse, and When You Do Not

The warehouse is not free. It is a pipeline to maintain, a dbt project to review, a bill that grows with your event volume, and a queue of analyst requests. Before building one, it is worth separating the questions that genuinely need a cross-system join from the ones that only felt like they did because the old analytics tool could not answer them.

That second category has shrunk. In Kissmetrics you ask the AI chat a question in plain language and it builds the metric or report for you. For the complex ones it works the query out itself against your own event data, picks an efficient path to run it, and saves the definition so it re-runs later. “Show me activation rate by acquisition channel for accounts that signed up in Q2, split by whether they invited a teammate in week one” is a question you can answer without an export, a load, and a dbt model. If you have been assuming every non-standard question needs a warehouse round trip, that assumption is worth retesting. Our guide to AI SQL generation covers why a model that has to guess at your schema gets it wrong so often, and what changes when it does not have to guess.

Snowflake, BigQuery, or Redshift

Snowflake, Google BigQuery, and Amazon Redshift can all handle event data at the volumes a mid-market product generates. The right choice depends more on your existing cloud and your team’s familiarity than on any feature comparison.

Snowflake

Snowflake runs on AWS, GCP, and Azure. Its defining property is the separation of storage and compute: you scale query processing independently of how much data you keep. That suits event data well, since volumes are large but query patterns are bursty. You pay for storage continuously and for compute only while queries run. Snowflake also handles data sharing cleanly, letting you expose a dataset to another team or a partner without copying it. The SQL dialect is conventional and well documented, which matters when your analysts are not database engineers.

Google BigQuery

BigQuery is serverless. There are no clusters to size and no infrastructure to maintain: you load data and run queries. Pricing is per byte scanned, with a flat rate option if that variability is uncomfortable. It connects natively to the rest of the Google stack, including Google Ads, Looker, and Sheets, so a Google-centric marketing stack has the shortest path here. It is also strong on semi-structured data, which matters because event properties usually arrive as JSON.

Amazon Redshift

Redshift is tightly bound to AWS. Redshift Serverless gives you consumption-based pricing similar to BigQuery, while provisioned clusters give more control over cost on steady workloads. It is the obvious pick if your infrastructure already sits on AWS and you want direct integration with S3, Lambda, and Glue. Redshift Spectrum lets you query files in S3 without loading them, which keeps cold historical event data cheap.

ETL vs ELT

Getting data from the analytics tool and everything else into the warehouse requires a pipeline. The two paradigms are ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform). The difference is where the transformation happens.

ETL: Transform Before Loading

Raw data is extracted, reshaped into its final schema in a staging environment, and then loaded. This was the right design when warehouse compute was expensive and you wanted to minimize work done inside it. The upside is that everything in the warehouse is already clean. The downside is that transformations are fixed upfront, so a new analysis usually means changing the pipeline, and anything the transform threw away is gone.

ELT: Load Then Transform

Raw data is loaded with minimal change and the transformation happens inside the warehouse in SQL, usually orchestrated by dbt. The raw events stay available, so a new analysis is a new model rather than a pipeline change. For event data this is almost always the right call, because you cannot predict which event property you will need in eighteen months and a discarded property cannot be recovered.

The practical version: export raw events, land them as-is in a staging schema, then build staging, intermediate, and mart models on top with dbt. Current reporting gets clean tables, and future questions still have the original data underneath them.

Schema Design for Event Data

The schema decides how hard your queries are to write. For product analytics data the workable shape is a dimensional model: one very large fact table of events, surrounded by dimension tables that give those events context.

The Event Fact Table

One row per event. Timestamp, an identity key, an event name, and the event properties as a structured or JSON column. This is the largest table you will have by several orders of magnitude, so partition it by event date and cluster or sort on the identity key. Almost every query filters by time and groups by person or account, and a table designed around those two access patterns stays fast as it grows into the billions of rows.

User and Account Dimensions

The user dimension holds the attributes that describe who did the thing: signup date, acquisition channel, current plan, company, company size, industry, region. Joined to the fact table it lets you segment any behavioral question by any attribute, comparing feature adoption between enterprise and SMB, or between organic and paid signups. Keep an account dimension separate from the user dimension in B2B, because most of the interesting questions are asked at account level while events arrive at user level.

Decide how you handle attribute changes before you build it. If a user upgrades from Growth to Silver, does the dimension overwrite the plan or keep both rows with validity dates? Overwriting is simpler and wrong for any historical analysis. Slowly changing dimensions are more work and are what you will want the first time someone asks what usage looked like before the upgrade.

Identity Keys and Joining Across Systems

The identity key is the part teams get wrong, and it is the most expensive thing to fix afterward. Your product analytics tool identifies people one way. Stripe identifies customers another way. The CRM has its own IDs and its own idea of which contact belongs to which account. If those do not reconcile, the warehouse holds three datasets that cannot be joined and the whole exercise fails.

Pick one canonical key per grain before the first load. At the person grain that is usually the internal user ID your application assigns, not the email address. Emails change, get shared across a team, and appear with different casing in different systems. At the account grain, pick a single account or organization ID and make sure every system carries it. If your billing system only stores an email, the fix is to write the account ID into a Stripe metadata field, not to build a fuzzy match in dbt.

You also have to handle the pre-identification window. Someone browses the marketing site anonymously, signs up three days later, and both halves of that journey belong to the same person. Kissmetrics resolves anonymous activity to the identified person once a user identifies, which means the events you export already carry a stable identity across that boundary. Preserve that key through the load rather than regenerating your own in the warehouse, or you will split every user in two at the signup event.

Load Cadence and Freshness

Freshness is the lag between an event happening and being queryable in the warehouse. The instinct is to make it as small as possible. The better approach is to set it per use case, because streaming everything costs real money and most of what the warehouse is for does not need it.

Daily is enough for the majority of business intelligence. A marketing team reviewing yesterday’s campaign performance is not harmed by a nightly load. Hourly is worth paying for when the warehouse feeds something operational: lead routing, anomaly alerting, a score reps act on the same day. Sub-hour streaming is justified for a small number of cases and should be an explicit decision, not the default.

For the genuinely real-time need, the warehouse is usually the wrong layer anyway. Watching what is happening in the product right now is what the Live view in Kissmetrics is for, and it answers in seconds with no pipeline in between. Reserve the warehouse for analysis and let the analytics tool handle immediacy.

Whatever cadence you choose, load incrementally. Export only events since the last successful run, watermarked on event timestamp with a small overlap window to catch late arrivals, and deduplicate on event ID in the staging model. Re-exporting full history nightly stops working the month you cross a few hundred million rows.

The Kissmetrics Export Pipeline

Building the pipeline from Kissmetrics to your warehouse means extracting events and person properties, staging them in cloud storage, bulk loading, and then transforming. The shape is the same on all three warehouse platforms.

Extraction

Kissmetrics exports raw event data with the full property set: every tracked event, its timestamp, the person who performed it (anonymous or identified), and every associated property. Because Kissmetrics autoconfigures by scanning your site and then autocaptures clicks, pageviews, and form interactions, the export includes behavior you never explicitly instrumented. That is usually the difference between a warehouse that can answer new questions and one that can only re-answer the questions you already thought of. Run one full historical export for the initial load, then incremental exports on your chosen cadence.

Loading

Stage the extracted files in S3, GCS, or Azure Blob depending on your platform, then use the native bulk loader: COPY INTO for Snowflake, load jobs for BigQuery, COPY for Redshift. All three ingest millions of rows in minutes from cloud storage, and all three are far slower if you insert row by row through a client library. Compress the files and keep them in the tens to hundreds of megabytes range for the best throughput.

Transformation With dbt

Once raw events are landed, dbt turns them into the models analysts use. A typical project has staging models that cast types and standardize names, intermediate models that build sessions and per-user aggregates, and mart models that produce the tables BI tools read. dbt also gives you tests, documentation, and version control, so the transformation layer is reviewable code rather than a folder of saved queries. See automating Kissmetrics data exports for the scheduling side of this.

What to Model Once the Data Lands

The pipeline is not the point. The point is the analysis that was impossible before, which is essentially always a join between behavior and something the product does not know about itself.

Behavior Joined to Billing

Joining events to Stripe, Chargebee, or Recurly data produces the numbers that decide roadmap and pricing. You can compute customer lifetime value segmented by behavior and find out whether users who adopted a specific feature in week one actually retain longer than those who did not. You can see which usage patterns precede downgrades and which onboarding milestones correlate with longer contracts. The join runs through your identity map, on the account key.

Behavior Joined to Support

Joining events to Zendesk or Intercom shows how support experience interacts with the product journey. Do users who contact support during onboarding activate more often or less? Which product areas generate tickets out of proportion to their usage? Does resolution time show up in retention curves months later? These are the questions where a warehouse pays for itself, because both halves are needed and neither system will ever hold the other.

Feeding Models Back Into the Product

The best warehouse projects do not end at a dashboard. A propensity score computed nightly in dbt is more useful pushed back into the CRM as a field reps can see, or into the product as an in-app prompt, than sitting in a table. Treat the return trip as part of the pipeline rather than a later phase. The CRM analytics integration guide covers how that leg usually gets built.

The BI Layer

Looker, Tableau, Metabase, and Mode all read a warehouse well. Looker’s modeling layer lets you define business logic once so every dashboard agrees on what an active user is. Metabase is open source and cheap to start with. Whichever you pick, build each dashboard for a named audience and a named decision, lead with the three to five numbers that audience acts on, and give them a path to drill into detail when something looks wrong. A dashboard whose audience you cannot name will not be opened twice.

Governance and Trust

A warehouse is only useful if people believe the numbers. That belief is built by boring operational discipline and destroyed by a single quarter where the pipeline was silently broken.

Definitions and Tests

Establish one source of truth for each metric definition. What exactly is an active user? Which events count? Encode it once, in the dbt model, and make every dashboard read from that model instead of reimplementing the logic. Add data quality tests that fire when something is off: event volume dropping 90% overnight is a broken pipeline, not a bad day, and you want to hear about it before the Monday meeting does.

Access and Documentation

Manage access so PII and financial detail are visible only to people who need them, and document every table, column, and transformation so the warehouse survives the person who built it leaving. This matters more as AI agents start querying company data directly. An agent handed warehouse credentials will answer with whatever the schema lets it reach, and the access controls are the only thing standing between a convenient integration and a leak. Our piece on AI agent analytics covers that shift in more detail.

Governance is not overhead. It is what makes the data trustworthy, and a warehouse full of data nobody trusts is worse than no warehouse at all.

Build the pipeline for one specific join you cannot currently make, ship it, and let the second use case pull the next piece of schema into existence. Warehouse projects that start from a list of tables to build tend to stall. The ones that start from a question someone is waiting on tend to finish.

Keep reading: See how Kissmetrics data exports feed the pipeline, explore modern GTM stack architecture built on warehouse-ready data, and see how automated revenue reporting pulls from these same models. For pushing results back to sales, read the CRM analytics integration guide.