What is data prep for Snowflake (and why eCommerce teams get stuck on it)

What "data prep" actually means on Snowflake

On Snowflake, prep is the work of turning raw, loaded data into clean, analysis-ready tables: deduplicating, standardising, joining and reshaping until every table has a clear grain and a definition someone is willing to stand behind. It sits between two things most teams already have. On one side, connectors – Fivetran, Airbyte, or native loaders – land raw Shopify, Meta and Google Ads data into RAW schemas. On the other side, BI tools visualise finished tables. Prep is everything in the middle.

Two properties separate real prep from ad-hoc querying. First, it should run in Snowflake – as pushdown SQL executing in your own warehouse – not as extracts copied into a desktop tool and back. Second, it should be repeatable: a dataflow that runs on a schedule, with dependencies, not a query someone re-runs by hand every Monday. Most of the pain teams describe as "our data is a mess" is actually the absence of one of these two properties.

What raw eCommerce data really looks like

The connector brochure says "your Shopify data in Snowflake in minutes", and that part is true. What lands, though, is shaped for syncing, not for analysis. A typical raw layer for an eCommerce brand looks like this:

• Shopify: orders with nested JSON line items; every order edit synced as a new row version; refunds and cancellations as separate objects that have to be netted against the original order; timestamps in UTC.
• Meta Ads: insights at ad-set-by-day grain, in the ad account's timezone, with spend restated for up to a few days as attribution settles.
• Google Ads: campaign stats with cost in cost_micros (millionths of the account currency), segmented by a different date column name than every other source.
• Everything else: an email platform keyed on email address, a subscription app keyed on its own customer ID, and no shared key between any of them.

None of this is wrong – it is faithful to each source system. But revenue is keyed by order and customer, spend is keyed by campaign and day, and the business question ("what did we make after ad spend yesterday?") spans all of them. A warehouse full of raw tables is potential energy. Prep is what converts it.

The five prep jobs every eCommerce team repeats

Across eCommerce and subscription brands, the same five prep jobs come up on almost every source, in roughly this order:

• 1. Deduplicate and version. Connectors sync updates as new rows. An order that was placed, edited and partially refunded can exist three times. The rule is almost always "latest version wins", enforced per business key.
• 2. Standardise units. One reporting timezone (pick it once, write it down), one currency, spend normalised out of micros, column names that mean the same thing in every table.
• 3. Resolve customer identity. Guest checkouts, repeat buyers with two email addresses, the subscription app's separate ID – collapsed into one durable customer key. Every retention and LTV number downstream inherits this decision.
• 4. Define the grain. Every prepped table should answer "one row per what?" in one sentence – one row per order, per order line, per customer per day. Grain drift is where fan-out joins and double-counted revenue come from.
• 5. Conform the calendar and channels. A shared date spine, and one channel vocabulary – meta is not facebook is not paid_social – so spend and revenue can meet in the same row.

The first job, deduplication, is a two-line pattern in Snowflake once you know it:

-- One clean row per order: latest synced version wins
create or replace table PREP_ORDERS as
select *
from RAW_SHOPIFY.ORDERS
qualify row_number() over (
  partition by order_id
  order by _synced_at desc
) = 1;

Individually, none of these jobs is hard. What makes prep expensive is that each one encodes a business decision – whose timezone, which customer key, what counts as a new customer – and those decisions have to hold consistently across every dataflow that touches the data afterwards.

Why teams get stuck

If the jobs are known and the SQL is learnable, why does prep stall? Three structural reasons, and they compound.

The skills split. The people who know the business definitions – analysts – usually don't own the transformation tooling. The people who own the tooling – data engineers, where they exist – don't own the definitions. So a one-line change to "what counts as a new customer" becomes a ticket, a sprint, and a review. At brands with no data engineer at all, the queue is just longer: it runs through an agency or a borrowed backend developer.

The two-tool problem. The classic stacks split prep from schedule: dbt for transformations plus Airflow for orchestration, or a desktop prep tool plus a server product to run it on a timer. Two tools means glue code, two failure surfaces, and an engineer to keep the pair alive – which recreates the skills split even in teams that adopted the tools to escape it. (We compare the trade-offs directly in Refyner vs Alteryx and Refyner vs dbt / Airflow.)

The rebuild problem. LTV, blended CAC, retention, RFM – every eCommerce brand needs substantially the same models, and almost every team builds them from a blank canvas as if they were novel. That is weeks of prep work spent re-deriving decisions hundreds of teams have already made.

The visible symptoms are familiar: a prep backlog measured in weeks, dashboards quietly pointed at raw tables because the prepped ones aren't ready, and the real reporting living in a spreadsheet a marketer exports on Fridays.

What good looks like

Teams that get past the stall tend to converge on the same shape, whatever tools they use:

• Analysts build and change prep dataflows themselves – the person who owns the definition owns the transformation.
• Every prepped table has a declared grain, checked before the dataflow ships, not discovered in a broken dashboard.
• Transformations run as pushdown on Snowflake – data doesn't leave the warehouse to be cleaned.
• Prep and schedule live in one place: every dataflow has a refresh time and a dependency chain, and a failed run alerts a person before it becomes a wrong number.
• Models are organised by business subject area – a Customer LTV model, a Blended CAC model – each a small set of dataflows with an owner.

Notice that none of this requires a particular vendor. It requires collapsing the distance between the person who knows what the number should be and the system that produces it.

Where Refyner fits

Refyner is built for exactly this shape of work. Analysts prep and schedule dataflows in one place, everything runs as pushdown on your own Snowflake, and the eCommerce and subscription models above – LTV, blended CAC, retention and repurchase, RFM, MRR movement, churn – ship built in rather than rebuilt from scratch. Prep and dataflow building are free; scheduled dataflows are pay-as-you-go – the detail is on the pricing page. If you are weighing this against a general-purpose desktop prep tool, start with Refyner vs Alteryx.

Keep reading: