A marketing data warehouse is a single database, usually Snowflake or BigQuery, that holds a copy of your marketing, sales and customer data from every platform you use (Meta, Google, TikTok, Shopify, Amazon, Klaviyo, Salesforce) so that one query can answer a question no single platform can. Latticework stands a retail brand up on one in under 30 days with Fivetran, Snowflake and a first dashboard. This guide covers what goes in it, the schema, the timeline, the cost, and where projects stall.
What is a marketing data warehouse?
It is the place where the numbers get reconciled. Meta reports a purchase, Google reports the same purchase, Shopify records the order, Klaviyo claims the email drove it, and the ERP holds the cost of goods. Each tool is right about its own slice. The warehouse is where the slices become one record per order, one record per customer, and one record per day of spend, so that LTV, CAC and contribution margin can be computed from the same tables. The data is loaded by an ELT tool (Fivetran or Airbyte), modeled in SQL (often with dbt), and read by a BI tool (Tableau, Sigma, Looker or Power BI).
Where is marketing data processed before it reaches the warehouse?
In a modern stack, almost nowhere. Fivetran or Airbyte call each platform’s API on a schedule and land the raw tables in the warehouse unchanged. Transformation happens after loading, in SQL inside the warehouse. The older pattern, where data was cleaned in a separate ETL server before loading, is what made these projects take six months. Loading first and modeling second is why the 30-day timeline works.
Marketing data warehouse schema
The Fivetran connectors deliver standard schemas, so the model looks the same from brand to brand. The tables that matter:
| Source | Key tables | Grain | Joins to |
|---|---|---|---|
| Shopify | order, order_line, customer, refund, product_variant |
one row per order and per line item | customer by customer_id; spend by order date and UTM or discount code |
| Meta (Facebook Ads) | ad_insights, ads, ad_sets, campaigns |
one row per ad per day | campaign naming or UTM to orders; date to daily revenue |
| Google Ads | ad_group_criterion_stats, campaign_stats, ad_stats |
one row per campaign or ad group per day | same as Meta |
| Klaviyo | event, person, campaign, flow |
one row per email event | person by email to Shopify customer |
| Amazon Seller Central | order, order_item, settlement reports |
one row per order and per item | no customer identity; joins to Shopify only at the product and date level |
| ERP (NetSuite or QuickBooks) | items, COGS, landed cost | one row per SKU | product by SKU for contribution margin |
The modeled layer on top is small: a daily spend table (all ad platforms unioned), an orders table with contribution margin per order, a customers table with first-order date and cohort, and the cohort and payback tables built from those three. That is the schema behind every LTV:CAC analysis Latticework runs.
How long does it take to get marketing data flowing into a warehouse?
Connectors load in one to two days. The full 30-day plan below splits roughly 20 percent ingestion, 60 percent validation and modeling, 20 percent dashboards and handoff. The step people skip is validation, and it is the one that takes the longest.
What does a marketing data warehouse cost?
For a $50 to 100M retail brand the recurring cost is the ELT tool, the warehouse compute, and the BI seats. Fivetran prices by monthly active rows, Snowflake and BigQuery by compute and storage, and both start in the low hundreds of dollars a month at this data volume. The build is the larger cost: the 30-day sprint is a Latticework Lite engagement; a three-month build with modeling and training is Latticework Basic. Compare that with a full-time data engineer’s salary and the decision usually makes itself.
The 30-day plan
After going through this process with dozens of companies, we have it down to a routine. Below is each step, how long it takes, and where the gotchas live.
It turns out, getting all your marketing data into a central warehouse is the easy part. Modern data tools like Fivetran and Snowflake make getting this whole thing done in 30 days very straightforward: especially if you’re not focused on the enormous “long tail” of marketing tech platforms, but instead focused on just the high-value, well-mapped-out sources that almost every company uses to market and sell. In fact, the “Ingestion” and the “Delivery” portion each only take about 20% of the total time.
We’ve done enough of these projects that we see the 20-60-20 pattern emerge in almost every project, and we’re even able to break it out into further detail, down to a 10-10-30-30-10-10 pattern. Then we just run our standard playbook.
1. Inventory
First things first, we need to understand what we’re collecting. There’s almost 10,000 marketing tech platforms nowadays and we need to start getting specific fast. We’ve only got 30 days after all.
It’s hard to be an expert in every marketing tech platform, but the good news is most companies tend to use the same 5-10. They’re buying on Facebook and Google. They’re selling on Shopify and Recharge. They’re engaging users on Salesforce and Mailchimp. And they’re measuring on Google Analytics or Mixpanel.
2. Access
Then we need some logins. We need to get into the source platforms, get authenticated, and start grabbing the right data. Platforms like Fivetran make extracting that data completely painless. Sometimes they’ve already pre-built reports for you so you don’t have to scan through 100+ columns to decide what you want. They deliver clean, standard schemas into warehouses like Snowflake, so we know exactly what to expect.
This is a quick process. You should be able to get Fivetran loading all your sources into a Snowflake instance in 1-2 days. Then comes the fun part.
3. Data validation
Is all this loaded data correct? You may be surprised, but many APIs expose slightly different versions of their source data than what comes out of their source UIs. And sometimes the API has hiccups, and forgets to deliver a couple days worth of stuff.
You’d be surprised how often folks fail to plan for this section of the process. But it’s well mapped out territory for our team. Just bear in mind that this phase often requires 50% more time and attention than the first two phases combined. Lots of clients can get spooked by this process and abandon the project at this phase, but it’s all very standard.
4. Joining & visualizing
Now comes the actual fun part. We need to decide how to “reconnect the dots”. This isn’t some ethereal concept. For example, when someone orders a Big Mac at McDonald’s, over 25 systems store some important sliver of that transaction. Sales, marketing, inventory, operations, payroll, accounting. Some of those systems store things at different levels of granularity (some systems speak users and email addresses, others speak days or campaigns, others speak DMAs), and some of the systems even contradict other systems.
We quite literally need to reassemble the McDonald’s Big Mac, if you will. It goes beyond just figuring out join keys (although that’s part of it). This is both art and science, and it’s the core of our expertise.
And once we’re done joining, we really start sprinting. There’s lots of questions you can ask of your marketing data, but we’ve heard most of these questions at some point in the past. All the major ad systems tend to speak the same stuff (impressions, clicks, conversions, ROAS, CPA, LTV, churn rates) and covering 80% of those questions is usually just plotting them into a template that we’ve already got built.
Remember, we’re at the 80% mark and we’ve only got 6 days left in the project. Let’s get this in the client’s hands for review.
5. Handoff
Handoff, or, as it’s sometimes adoringly referred to, “quality assurance” is an important phase to plan for. We ask for sample reports up front so we know what we’re comparing to. If we’ve done our work correctly, there’s little risk of a major slip. This is generally just pixel pushing and chasing a small amount of edge cases.
6. Training
At this point we’re getting the client prepared to fly the airplane themselves. There’s very little for them to configure since this is all so well mapped out. If they want to bring in some new data sources, or create some very specific custom metrics, or if they want to blow their stakeholders out of the water with some custom art direction, we’re jotting all this down and prepping for months 2 and 3.
But let’s not lose the forest for the trees here.
30 days ago there was no infrastructure and zero data scientists. Today there’s a fully automated, fully functional “Marketing Data Warehouse” with a dashboard to run the company with.
We can do this for you and your clients too!
As you can tell from the blueprint above, this process is pretty standard for our team. The 30 day sprint may not include everything under the sun, but it’ll give your company all the major building blocks to run your company off a modern data stack, instead of messing around with spreadsheets and wrangling 3rd party platforms together.
FAQ
Do I need a data warehouse if I am on Shopify?
Shopify reports on Shopify. The moment you need Meta and Google spend next to Shopify orders, or Amazon orders next to DTC orders, you need a place to join them, and that place is a warehouse.
Snowflake or BigQuery for a retail brand?
Either works at this size. Snowflake has the larger connector and partner ecosystem; BigQuery is cheaper if you already live in Google Cloud and Google Analytics. Latticework builds on both.
Fivetran, Airbyte or Funnel?
Fivetran for reliability and standard schemas; Airbyte when cost or a missing connector matters; Funnel when the goal is marketing reporting only and no warehouse is wanted. Most Latticework builds use Fivetran.
What comes after Triple Whale, Polar or Daasity?
Those tools are a dashboard with a fixed data model. Brands move to their own warehouse when they need contribution margin by SKU by channel, LTV cohorts on their own definitions, or Amazon and wholesale data in the same model. The migration reuses the same connectors, so the 30-day plan applies.
We’d love to hear how we can help you get started. A modern “Marketing Data Warehouse” is a key asset for being competitive, and we want to help you get there. Get in touch and let’s get you started.