Synthetic Data in Practice: A Shopify Case Study | by Piotr Gruszecki | Dec, 2024

Testing new Snowflake performance with a 30k data dataset

Working with information, I hold working into the identical downside an increasing number of usually. On one hand, we’ve got rising necessities for information privateness and confidentiality; on the opposite — the necessity to make fast, data-driven selections. Add to this the trendy enterprise actuality: freelancers, consultants, short-term tasks.

As a call maker, I face a dilemma: I want evaluation proper now, the inner crew is overloaded, and I can’t simply hand over confidential information to each exterior analyst.

And that is the place artificial information is available in.

However wait — I don’t wish to write one other theoretical article about what artificial information is. There are sufficient of these on-line already. As a substitute, I’ll present you a particular comparability: 30 thousand actual Shopify transactions versus their artificial counterpart.

What precisely did I test?

• How faithfully does artificial information replicate actual developments?
• The place are the most important discrepancies?
• When can we belief artificial information, and when ought to we be cautious?

This received’t be one other “learn how to generate artificial information” information (although I’ll present the code too). I’m specializing in what actually issues — whether or not this information is definitely helpful and what its limitations are.

I’m a practitioner — much less concept, extra specifics. Let’s start.

When testing artificial information, you want a stable reference level. In our case, we’re working with actual transaction information from a rising e-commerce enterprise:

• 30,000 transactions spanning 6 years
• Clear development development yr over yr
• Mixture of excessive and low-volume gross sales months
• Various geographical unfold, with one dominant market

For sensible testing, I centered on transaction-level information akin to order values, dates, and fundamental geographic info. Most assessments require solely important enterprise info, with out private or product specifics.

The process was easy: export uncooked Shopify information, analyze it to keep up solely a very powerful info, produce artificial information in Snowflake, then examine the 2 datasets aspect by aspect. One can consider it as producing a “digital twin” of what you are promoting information, with comparable developments however solely anonymized.

[Technical note: If you’re interested in the detailed data preparation process, including R code and Snowflake setup, check the appendix at the end of this article.]

The primary take a look at for any artificial dataset is how nicely it captures core enterprise metrics. Let’s begin with month-to-month income — arguably a very powerful metric for any enterprise (for positive in prime 3).

Wanting on the uncooked developments (Determine 1), each datasets observe the same sample: regular development through the years with seasonal fluctuations. The artificial information captures the final development nicely, together with the enterprise’s development trajectory. Nevertheless, after we dig deeper into the variations, some fascinating patterns emerge.

To quantify these variations, I calculated a month-to-month delta:

Δ % = (Artificial - Shopify) / Shopify

We see from the plot, that month-to-month income delta varies — generally unique is greater, and generally artificial. However the bars appear to be symmetrical and in addition the variations are getting smaller with time. I added variety of data (transactions) per 30 days, perhaps it has some affect? Let’s dig a bit deeper.

The deltas are certainly fairly nicely balanced, and if we take a look at the cumulative income traces, they’re very nicely aligned, with out massive variations. I’m skipping this chart.

The deltas are getting smaller, and we intuitively really feel it’s due to bigger variety of data. Allow us to test it — subsequent plot exhibits absolute values of income deltas as a operate of data per 30 days. Whereas the variety of data does develop with time, the X axis isn’t precisely time — it’s the data.

The deltas (absolute values) do lower, because the variety of data per 30 days is larger — as we anticipated. However there may be yet one more factor, fairly intriguing, and never that apparent, no less than at first look. Above round 500 data per 30 days, the deltas don’t fall additional, they keep at (in common) roughly identical stage.

Whereas this particular quantity is derived from our dataset and would possibly differ for various enterprise sorts or information constructions, the sample itself is necessary: there exists a threshold the place artificial information stability improves considerably. Beneath this threshold, we see excessive variance; above it, the variations stabilize however don’t disappear solely — artificial information maintains some variation by design, which truly helps with privateness safety.

There’s a noise, which makes month-to-month values randomized, additionally with bigger samples. All, whereas preserves consistency on larger aggregates (yearly, or cumulative). And whereas reproducing general development very nicely.

It could be fairly fascinating to see comparable chart for different metrics and datasets.

We already know income delta depends upon variety of data, however is it simply that extra data in a given month, the upper the income of artificial information? Allow us to discover out …

So we wish to test how income delta depends upon variety of data delta. And we imply by delta Artificial-Shopify, whether or not it’s month-to-month income or month-to-month variety of data.

The chart under exhibits precisely this relationship. There’s some (mild) correlation – if variety of data per 30 days differ considerably between Artificial and Shopify, or vice-versa (excessive delta values), the income delta follows. However it’s removed from easy linear relationship – there may be additional noise there as nicely.

When producing artificial information, we regularly must protect not simply general metrics, but in addition their distribution throughout completely different dimensions like geography. I stored nation and state columns in our take a look at dataset to see how artificial information handles dimensional evaluation.

The outcomes reveal two necessary features:

1. The reliability of artificial information strongly depends upon the pattern measurement inside every dimension
2. Dependencies between dimensions should not preserved

Taking a look at income by nation:

For the dominant market with hundreds of transactions, the artificial information supplies a dependable illustration — income totals are comparable between actual and artificial datasets. Nevertheless, for international locations with fewer transactions, the variations change into vital.

A vital commentary about dimensional relationships: within the unique dataset, state info seems just for US transactions, with empty values for different international locations. Nevertheless, within the artificial information, this relationship is misplaced — we see randomly generated values in each nation and state columns, together with states assigned to different international locations, not US. This highlights an necessary limitation: artificial information technology doesn’t keep logical relationships between dimensions.

There’s, nonetheless, a sensible strategy to overcome this country-state dependency situation. Earlier than producing artificial information, we might preprocess our enter by concatenating nation and state right into a single dimension (e.g., ‘US-California’, ‘US-New York’, whereas maintaining simply ‘Germany’ or ‘France’ for non-US transactions). This easy preprocessing step would protect the enterprise logic of states being US-specific and forestall the technology of invalid country-state mixtures within the artificial information.

This has necessary sensible implications:

• Artificial information works nicely for high-volume segments
• Be cautious when analyzing smaller segments
• At all times test pattern sizes earlier than drawing conclusions
• Bear in mind that logical relationships between dimensions could also be misplaced, contemplate pre-aggregation of some columns
• Think about further information validation if dimensional integrity is essential

Probably the most fascinating findings on this evaluation comes from inspecting transaction worth distributions. Taking a look at these distributions yr by yr reveals each the strengths and limitations of artificial information.

The unique Shopify information exhibits what you’d usually anticipate in e-commerce: extremely uneven distribution with an extended tail in direction of larger values, and distinct peaks equivalent to common single-product transactions, displaying clear bestseller patterns.

The artificial information tells an fascinating story: it maintains very nicely the general form of the distribution, however the distinct peaks from bestseller merchandise are smoothed out. The distribution turns into extra “theoretical”, dropping some real-world specifics.

This smoothing impact isn’t essentially a nasty factor. In actual fact, it is likely to be preferable in some instances:

• For normal enterprise modeling and forecasting
• If you wish to keep away from overfitting to particular product patterns
• In case you’re in search of underlying developments slightly than particular product results

Nevertheless, in case you’re particularly all in favour of bestseller evaluation or single-product transaction patterns, you’ll must issue on this limitation of artificial information.

Figuring out, the aim is product evaluation, we’d put together unique dataset in a different way.

To quantify how nicely the artificial information matches the true distribution, we’ll take a look at statistical validation within the subsequent part.

Let’s validate our observations with the Kolmogorov-Smirnov take a look at — a normal statistical technique for evaluating two distributions.

The findings are constructive, however what do these figures imply in apply? The Kolmogorov-Smirnov take a look at compares two distributions and returns two important metrics: D = 0.012201 (smaller is healthier, with 0 indicating equivalent distributions), and p-value = 0.0283 (under the conventional 0.05 stage, indicating statistically vital variations).

Whereas the p-value signifies some variations between distributions, the very low D statistic (almost to 0) verifies the plot’s findings: a near-perfect match within the center, with simply slight variations on the extremities. The artificial information captures essential patterns whereas maintaining sufficient variance to make sure anonymity, making it appropriate for business analytics.

In sensible phrases, this implies:

• The artificial information supplies a wonderful match in a very powerful mid-range of transaction values
• The match is especially robust the place we’ve got probably the most information factors
• Variations seem primarily in edge instances, which is anticipated and even fascinating from a privateness perspective
• The statistical validation confirms our visible observations from the distribution plots

This type of statistical validation is essential earlier than deciding to make use of artificial information for any particular evaluation. In our case, the outcomes counsel that the artificial dataset is dependable for many enterprise analytics functions, particularly when specializing in typical transaction patterns slightly than excessive values.

Let’s summarize our journey from actual Shopify transactions to their artificial counterpart.

General enterprise developments and patterns are maintained, together with transactions worth distributions. Spikes are ironed out, leading to extra theoretical distributions, whereas sustaining key traits.

Pattern measurement issues, by design. Going too granular we are going to get noise, preserving confidentiality (along with eradicating all PII in fact).

Dependencies between columns should not preserved (country-state), however there may be a straightforward stroll round, so I believe it isn’t an actual situation.

It is very important perceive how the generated dataset will probably be used — what sort of evaluation we anticipate, in order that we are able to take it under consideration whereas reshaping the unique dataset.

The artificial dataset will work completely for purposes testing, however we should always manually test edge instances, as these is likely to be missed throughout technology.

In our Shopify case, the artificial information proved dependable sufficient for many enterprise analytics situations, particularly when working with bigger samples and specializing in normal patterns slightly than particular product-level evaluation.

This evaluation centered on transactions, as one in all key metrics and a simple case to begin with.

We are able to proceed with merchandise evaluation and in addition discover multi-table situations.

Additionally it is value to develop inner pointers learn how to use artificial information, together with test and limitations.

You may scroll by this part, as it’s fairly technical on learn how to put together information.

Uncooked Information Export

As a substitute of counting on pre-aggregated Shopify experiences, I went straight for the uncooked transaction information. At Alta Media, that is our commonplace method — we favor working with uncooked information to keep up full management over the evaluation course of.

The export course of from Shopify is easy however not speedy:

• Request uncooked transaction information export from the admin panel
• Anticipate e mail with obtain hyperlinks
• Obtain a number of ZIP information containing CSV information

Information Reshaping

I used R for exploratory information evaluation, processing, and visualization. The code snippets are in R, copied from my working scripts, however in fact one can use different languages to attain the identical last information body.

The preliminary dataset had dozens of columns, so step one was to pick solely the related ones for our artificial information experiment.

Code formatting is adjusted, in order that we don’t have horizontal scroll.

#-- 0. libs
pacman::p_load(information.desk, stringr, digest)
#-- 1.1 load information; the csv information are what we get as a 
# full export from Shopify
xs1_dt <- fread(file = "shopify_raw/orders_export_1.csv")
xs2_dt <- fread(file = "shopify_raw/orders_export_2.csv")
xs3_dt <- fread(file = "shopify_raw/orders_export_3.csv")
#-- 1.2 test all columns, restrict them to important (for this evaluation) 
# and bind into one information.desk
xs1_dt |> colnames()
# there are 79 columns in full export, so we choose a subset, 
# related for this evaluation
sel_cols <- c(
"Title", "E-mail", "Paid at", "Achievement Standing", "Accepts Advertising", 
"Foreign money", "Subtotal", 
"Lineitem amount", "Lineitem identify", "Lineitem value", "Lineitem sku", 
"Low cost Quantity", "Billing Province", "Billing Nation")

We’d like one information body, so we have to mix three information. Since we use information.desk package deal, the syntax could be very easy. And we pipe mixed dataset to trim columns, maintaining solely chosen ones.

xs_dt <- information.desk::rbindlist(
l = checklist(xs1_dt, xs2_dt, xs3_dt), 
use.names = T, fill = T, idcol = T) %>% .[, ..sel_cols]

Let’s additionally change column names to single string, changing areas with underscore “_” — we don’t must cope with additional quotations in SQL.

#-- 2. information prep
#-- 2.1 change areas in column names, for simpler dealing with
sel_cols_new <- sel_cols |> 
stringr::str_replace(sample = " ", alternative = "_")
setnames(xs_dt, previous = sel_cols, new = sel_cols_new)

I additionally change transaction id from character “#1234”, to numeric “1234”. I create a brand new column, so we are able to simply examine if transformation went as anticipated.

xs_dt[, `:=` (Transaction_id = stringr::str_remove(Name, pattern = "#") |> 
as.integer())]

After all you can even overwrite.

Further experimentation

Since this was an experiment with Snowflake’s artificial information technology, I made some further preparations. The Shopify export incorporates precise buyer emails, which might be masked in Snowflake whereas producing artificial information, however I hashed them anyway.

So I hashed these emails utilizing MD5 and created a further column with numerical hashes. This was purely experimental — I wished to see how Snowflake handles several types of distinctive identifiers.

By default, Snowflake masks text-based distinctive identifiers because it considers them personally identifiable info. For an actual software, we’d wish to take away any information that might probably determine prospects.

new_cols <- c("Email_hash", "e_number")
xs_dt[, (new_cols) := .(digest::digest(Email, algo = "md5"),
digest::digest2int(Email, seed = 0L)), .I]

I used to be additionally curious how logical column will probably be dealt with, so I modified kind of a binary column, which has “sure/no” values.

#-- 2.3 change Accepts_Marketing to logical column
xs_dt[, `:=` (Accepts_Marketing_lgcl = fcase(
Accepts_Marketing == "yes", TRUE, 
Accepts_Marketing == "no", FALSE, 
default = NA))]

Filter transactions

The dataset incorporates data per every merchandise, whereas for this specific evaluation we’d like solely transactions.

xs_dt[Transaction_id == 31023, .SD, .SDcols = c(
"Transaction_id", "Paid_at", "Currency", "Subtotal", "Discount_Amount", 
"Lineitem_quantity", "Lineitem_price", "Billing_Country")]

Ultimate subset of columns and filtering data with complete quantity paid.

trans_sel_cols <- c(
"Transaction_id", "Email_hash", "e_number", "Paid_at", "Subtotal", 
"Foreign money", "Billing_Province", "Billing_Country",
"Fulfillment_Status", "Accepts_Marketing_lgcl")
xst_dt <- xs_dt[!is.na(Paid_at), ..trans_sel_cols]

Export dataset

As soon as we’ve got a dataset, we nee to export it as a csv file. I export full dataset, and I additionally produce a 5% pattern, which I exploit for preliminary take a look at run in Snowflake.

#-- full dataset
xst_dt |> fwrite(file = "information/transactions_a.csv")
#-- a 5% pattern
xst_5pct_dt <- xst_dt[sample(.N, .N * .05)]
xst_5pct_dt |> fwrite(file = "information/transactions_a_5pct.csv")

And in addition saving in Rds format, so I don’t must repeat all of the preparatory steps (that are scripted, so they’re executed in seconds anyway).

#-- 3.3 save Rds file
checklist(xs_dt = xs_dt, xst_dt = xst_dt, xst_5pct_dt = xst_5pct_dt) |> 
saveRDS(file = "information/xs_lst.Rds")

As soon as we’ve got our dataset, ready in line with our wants, technology of it’s artificial “sibling” is easy. One must add the info, run technology, and export outcomes. For particulars observe Snowflake pointers. Anyway, I’ll add right here brief abstract, for complteness of this text.

First, we have to make some preparations — function, database and warehouse.

USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE ROLE data_engineer;
CREATE OR REPLACE DATABASE syndata_db;
CREATE OR REPLACE WAREHOUSE syndata_wh WITH
WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED';
GRANT OWNERSHIP ON DATABASE syndata_db TO ROLE data_engineer;
GRANT USAGE ON WAREHOUSE syndata_wh TO ROLE data_engineer;
GRANT ROLE data_engineer TO USER "PIOTR";
USE ROLE data_engineer;

Create schema and stage, if not outlined but.

CREATE SCHEMA syndata_db.experimental;
CREATE STAGE syn_upload 
DIRECTORY = ( ENABLE = true ) 
COMMENT = 'import information';

Add csv information(s) to stage, after which import them to desk(s).

Then, run technology of artificial information. I like having a small “pilot”, somethiong like 5% data to make preliminary test if it goes by. It’s a time saver (and prices too), in case of extra sophisticated instances, the place we’d want some SQL adjustment. On this case it’s slightly pro-forma.

-- generate artificial
-- small file, 5% data
name snowflake.data_privacy.generate_synthetic_data({
'datasets':[
{
'input_table':  'syndata_db.experimental.transactions_a_5pct',
'output_table': 'syndata_db.experimental.transactions_a_5pct_synth'    
}
],
'replace_output_tables':TRUE
});

It’s good to examine what we’ve got in consequence — checking tables immediately in Snowflake.

After which run a full dataset.

-- massive file, all data
name snowflake.data_privacy.generate_synthetic_data({
'datasets':[
{
'input_table':  'syndata_db.experimental.transactions_a',
'output_table': 'syndata_db.experimental.transactions_a_synth'    
}
],
'replace_output_tables':TRUE
});

The execution time is non-linear, for the total dataset it’s means, means quicker than what information quantity would counsel.

Now we export information.

Some preparations:

-- export information to unload stage
CREATE STAGE syn_unload 
DIRECTORY = ( ENABLE = true ) 
COMMENT = 'export information';
CREATE OR REPLACE FILE FORMAT my_csv_unload_format
TYPE = 'CSV'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY = '"';

And export (small and full dataset):

COPY INTO @syn_unload/transactions_a_5pct_synth 
FROM syndata_db.experimental.transactions_a_5pct_synth
FILE_FORMAT = my_csv_unload_format
HEADER = TRUE;
COPY INTO @syn_unload/transactions_a_synth 
FROM syndata_db.experimental.transactions_a_synth
FILE_FORMAT = my_csv_unload_format
HEADER = TRUE;

So now we’ve got each unique Shopify dataset and Artificial. Time to research, examine, and make some plots.

For this evaluation, I used R for each information processing and visualization. The selection of instruments, nonetheless, is secondary — the secret is having a scientific method to information preparation and validation. Whether or not you utilize R, Python, or different instruments, the necessary steps stay the identical:

• Clear and standardize the enter information
• Validate the transformations
• Create reproducible evaluation
• Doc key selections

The detailed code and visualization methods might certainly be a subject for one more article.

In case you’re all in favour of particular features of the implementation, be at liberty to succeed in out.