Big data management on Snowflake

Transcript

Hello, this is Thomas, product manager on Adobe Campaign. Today we will explain how to take advantage of Adobe Campaign Classic with Snowflake integration. Firstly, we will explain how to configure Snowflake FDA connector, create a simple schema, do some queries from campaign, and look at what happens at Snowflake level. Then we will learn how to make data available into campaign by using forms and navigation hierarchies to make data accessible for marketers in a simple way. We will also learn how to manipulate those data for targeting by using reports and cubes, and finally, how to create lists and targeting filters. By the same occasion, beginners will learn the campaign basics, like the difference between lists and filters. For this demo, we will create a new campaign branch to store all Snowflake items.

I’m creating a generic folder called Snowflake data model.

For better clarity, we can replace the default image by a Snowflake logo.

Once done, we can create an external account directory.

We can create now the Snowflake connector.

Select external database. Snowflake is the first one on the list. Just a quick save, and we will enter our credentials provided by Snowflake. Server URL, username, database name.

Select the Snowflake schema.

Enter your password.

We can test the connection now, and we’re done.

We can now move onto next chapter. Now connection’s done, next step are to discover the Snowflake tables and generate the description of a table into Adobe Campaign. In Campaign language, it’s called a data schema. So let’s create a dedicated directory in the same tree to host our new data schema.

When I create my new schema, I have the possibility to access to external data. This is where we can find our Snowflake account. Name space is a prefix of your choice. Then I need a name and a label. They are slightly different. Label can be see as a description, and the internal name is a unique identifier. Let’s name it profile XL, because I know my table contains a billion profiles. I select my external account, and let’s discover Snowflake tables.

Here is my XL profile table. The table description, called a schema, is now automatically under construction.

Here is our schema description. I could extend it right now, but let’s keep the standard one for now.

We can search our schema by using the prefix name.

Let’s look at the preview.

The structure looks more meaningful now. A documentation is automatically generated, and in a perfect world, we should see the data. Well, let’s face it, we are not in a perfect world, but we will create our own world. This is even better. By modifying or extending the schema, you will be able to control which collum to see, and to give more meaningful labels or change the data type, without any modification at Snowflake level. This is incredibly powerful, because no matter what are the technical names, you will adapt your model to marketers without any need for DBA or Snowflake administrator.

Here is a good example of what kind of modification you can do. Let’s extend the schema we’ve just created.

I could reuse the same snow extension, but I will add an X to notify it’s not a full schema, but just an extension. Name and label remain exactly the same. If you’re interested about how to extend a schema in details, you can look at the dedicated section in our public documentation. There’s way more option than the simple ones I will use in this example. First, I’m creating an enumeration bound to the gender column. It will help our end users to filter on male or female at a glance, without needing to figure out zero is non-specified, one is male, and two is female. Then I can set a primary key. As you may know, this is mandatory for accessing data from campaign UI, and to create some relationship between tables afterwards.

And I’m renaming the column labels to get something more user-friendly. It sounds easier to work at database level with technical names, but better for marketers to have meaningful labels. You will learn from documentation to never forget closing your element, and then we can check the preview and see if the syntax is correct.

It seems everything is okay now. Otherwise you would face an error message related to missing elements. Just save now. Oops, I still have an error message due to missing primary key. Don’t worry, in Adobe Campaign you just need to restart the client console after schema structure modification. It’s a known small constraint, and maybe one day we’ll fix it.

Perfect, now I can see my data, which means my primary key is well-defined. Let’s move on and check the labels.

Okay, for the labels.

Let’s see if my numeration is correct. Perfect, my table contains zero, one, and two, and our marketers can see male, female, or non-specified. Wasn’t that easy? Never underestimate the benefits of schema extensions. It’s a key concept in Adobe Campaign to make marketer’s job easier.

We will see now how to query on this data, and what happens at Snowflake’s engine level. Okay, let’s move on and create a new workflow folder.

The simplest thing we can do now is counting the number of profiles available in the database.

To do so, I’m creating a simple workflow with a start, count, and stop activities. Behind the scenes, Campaign will run a query, create a temporary table, and then insert the result of the query. In this demo, it will also probably drop this temporary table as I put a stop and don’t really use it.

It’s now running. If we were on a classic database, I will propose you to have a break, grab a coffee, wait a few minutes, and then call the DBA to stop the query before the database server burns. But we are on Snowflake world, so stay with me a couple more seconds and see the magic happen.

Finished, I told you it was an Excel table, and we have one billion profiles in it, and it took us 40 seconds only to get the result. 40 seconds to create a table, insert one billion records, and drop it once it’s done. Let’s check at Snowflake level. We’re now in Snowflake history panel, where you can monitor and analyze the SQL queries. So here our my queries. I’ll create table, and insert in 35 seconds with one billion records and 5.8 gigabytes inserted.

And a drop table.

Let’s look into the detail of the insert statement. You can see here way more than on classic databases and catch all details about CPU, memory, I/O, network, and few other key metrics. Basically, it’s important not for performance because we know already it went amazingly fast, but more regarding the cost, because the Snowflake cost model is computational-metrics based. It may worth a further video showing you how to optimize your campaign queries. Thanks to multi-segmentation workflow engine and scheduler capabilities, it becomes really easy to reducing costs bound to Snowflake significantly. For example, we can see that cost for dropping a table is near to zero. Let’s go back to Adobe Campaign and build a more complex query. Usually when you want to crash test your database, de-duplication activity is a good candidate, then I will use it.

Here is a brand new workflow.

I’m searching all first names starting with dav across my billion profiles.

Then I split profiles having an email, and the other’s less relevant ones, from marketer’s standpoint I mean. They are probably as interesting as the other ones for a chat around the coffee. Or not, who knows? Anyway, it’s a fake database.

In 10 seconds, I know the six million dav among my billion profiles. This is scary to see how fast it can be. Dave is not scary or fast, the query performance is. In fact, I also know a scary Dave, but it’s another story.

Done 10 seconds to filter in the Daves, and 24 seconds to scan over the six millions Dave and split the ones with an email or the one without. This is scary, definitely.

Now we know performance is simply awesome. We will use it leveraging the concrete value of Campaign plus Snowflake integration, a brand new way of handling marketing segmentation using forms and filters instead of queries within technical workflows. For a technical user, workflows can do almost everything, but now they can also share part of their power with marketers, without any concern about performances issues or mistakes building queries. Let’s then create an input form. That way, marketers will be able to manipulate the fields available from that form only. It’s easier than manipulating hundred of unknown columns. Like for previous steps, I’m creating a new folder to store my Snowflake-based forms. We keep only snow forms. If you wanted this filter permanent and secure, you can handle this from folder properties as well. I’m still using same name and prefix, as it’s a different type of object, and I’m using icon box model. Feel free to play with forms. It’s 100% customizable, with tons of useful options.

Here I just have a blank form for now.

For the sake of people here, I won’t do it from scratch, but just take the one I’ve prepared for this exercise.

A pretty simple one, with just a few attributes taken from my Snowflake main profile table.

Let’s now have a small coffee break before moving forward including it on the navigation tree. The navigation tree is another type of customizable object in Adobe Campaign Classic. I’m far from being an expert, so I will show you the simplest way to use it. Okay, we’re on it.

First thing, core means it will be available for everybody and doesn’t rely on a specific package. I want it available easily, so I’m creating a snow core new object.

And I just copy-paste another one before changing and customizing values, such where it will be stored on the navigation tree, and the default field to show. I let you check the campaign documentation and see how to do this all better than me.

Just a quick client console restart to refresh the UI, and then it should appear on my right-click menu.

Here we go, my form is available, waiting for marketers. This is really powerful. I have my billion profile right here now available in a second.

Let’s count to check one billion.

Now let’s go for a search. If you remember well, in previous chapter, we have built a workflow with count and de-duplication. We will do exactly the same directly from the UI, without needing workflow anymore.

From the forms, you just have to click on the bottom right to count number of lines. It’s way easier than having to create a workflow, and by the same occasion, it will save you money as there’s no billion data to insert in a temporary table as workflow does.

From your form, just select a profile, right-click on it, and click on filter on this column. You can now search for the scary Dave.

We have our six millions Daves, so let’s use another method to see which ones I can target. We will use filters, and now you can run a query on email with barely no skill on SQL language. Let’s do an email is not empty to simplify. In real life, I would add a check on email validity by using the same method as well.

And all my Daves with an email are now listed. That’s a good way to have happier marketers, save money, easy to use, and to get faster results in a row.

That’s good to be able to do natural queries in realtime like this, and sometimes you may need to store the query for later usage in marketing campaigns, making sure you will always have latest fresh data. To do so, I will show you now how to create a data cube and report attached to it.

We will see how to keep data easily or save your predefined filters as well. Let’s get started creating a cube.

This data queue will be plugged on my Snowflake table.

Attached to my main profile’s table, I’m adding two dimensions into the queue to create a simple array of data. Address and gender, address, and gender.

Address will contain several different levels we can break down, so I’m adding the country, the state, and the city, to stick with something quite simple.

Now I need to measure something.

Let’s go for the basic one, a simple count.

And then we are done.

Let’s check the preview and see if we are good to go.

That sounds great.

My billion profiles are still there. I could use the cube directly from here, but I would prefer to show you how to create a basic report by the same occasion.

I’ve created a folder to store my Snowflake reports, and I will go through the dedicated HTML UI to change a little. Here you’re on the report area, everything is done to make your life easier. I’m creating a new report based on the cube I just created. There’s nothing else to do, so as it’s boring a bit.

I will change the access to get the gender in line and the address in column.

I double-check to confirm we want to count. Let’s go back to the explorer view now.

My report is available, as expected, and then I can start to play with it. See how everything is fast? That is the Snowflake power combined with the Adobe campaign agility. This time it does not require temporary table creation. It’s direct query to Snowflake, so your CPU, memory, I/O, and network will say thank you, if only they could speak. Your wallet as well, by the way.

Well, I saw people from Arcadia on my report. Let’s send an email message to dear Arcadian’s customers. For this, I have two options, create a static list where the data will be stored into, or go for a predefined filter, where I will retrieve all potential new Arcadians each time I use it. Totally up to you to decide depending on your use case, but let’s go for the list for now. Back to my report, I will save Arcadians in a list. Warning, this list will be stored in Campaign side, and not into Snowflake for now, so beware of big lists. It could take time and disk space. Less that is a limitation as of today, but it will be improved in a further next release, and you’re not supposed to spam the entire world. Just the Arcadians is enough.

Come on Arcadians, you’re on my shopping cart now.

You will be transferred in a campaign table called a list.

Your list is done, with a fixed number of Arcadians inside.

Last one, how to do the same thing, but with a dynamic number of people. In fact, it’s easier. You can build a predefined filter and reuse it in your marketing campaign. If you use this filter from the workflow, a temporary table will be created in Snowflake database and then dropped when workflow ends. This is the main difference with the list.

Let’s go back to the profile form. It’s the simplest path for creating filters. Then we can create an advanced filter.

Select your city, but imagine you want now to check what are the biggest cities on your database before choosing the population. Just go there and click on the distribution of values. It provides me with a distribution of profiles per city. Based on Snowflake, it takes one second. Let’s take the New Yorkers then.

And we can just save our filter pressing this button.

My filter is stored in the default filters folder.

I can just move it to my Snowflake data model folder and have everything in the same place.

The query will run each time you use it on a campaign workflow.

I hope you can see now the value of campaign agility combined to Snowflake performance.

It was a pleasure for me to show how to connect to Snowflake, how to create an extended data schema. Remember the value of defining well-structured information in your database, Snowflake or not, how to query on Snowflake from a campaign workflow, how to create a form and integrate it on your campaign UI. We hope the flexibility it provides will help you to do better and safer digital marketing.

Cubes and reports are your friends to control the costs and restrain search on specific areas. It will prevent markets from bad design in consuming queries in temporary table proliferation, and one more thing, I would say campaign plus Snowflake is a good way to have happier marketers, save money, and to get faster and better results at the same time. Next time I will show you how to use different Snowflake warehouses for different usage, like query and reporting. It gives you the opportunity to monitor the cost per usage, and for the roadmap, I’m not supposed to tell you, but we are working on improving all this stuff by moving the campaign delivery analysis directly on Snowflake and achieve a personalization for 40 millions profiles in less than 10 minutes. Thank you, that was Thomas Jordy with a cool English AI accent with lots of help from his friend and colleague, Christophe Protat. -