<![CDATA[Stitch Blog]]>https://www.stitchdata.com/blog/https://www.stitchdata.com/blog/favicon.pngStitch Bloghttps://www.stitchdata.com/blog/Ghost 1.26Wed, 21 Aug 2019 20:48:18 GMT60<![CDATA[New Stitch features empower more flexible data pipelines]]>https://www.stitchdata.com/blog/enterprise-scheduling-webhooks-notifications/5d3b0f850ee7db003845a791Tue, 30 Jul 2019 13:00:00 GMTNew Stitch features empower more flexible data pipelines

At Stitch, we're always looking for ways to make the ETL process easier and more powerful for businesses of all sizes, from small startups to sophisticated enterprises.

We're delighted to announce the launch of four new features to help you customize and control your ETL processes and improve your data workflow:

  • advanced scheduling
  • post-load webhooks
  • notification extensibility, and
  • API key management.

These new features are available to all customers on Stitch enterprise plans.

Control data extractions with advanced scheduling

Our customers have told us that they want fine-grained control over their data extraction so they can better match their data analytics cadence, so we've added a way for Stitch users to specify granular start times for extraction jobs via cron scheduling. You can use this advanced scheduling to manage data warehouse usage and Stitch row volumes.

As an example, say you wanted to kick off an extraction every morning at 6 a.m. in order to have data for a report refreshed by the time you got to the office at 9. Setting the schedule for your Stitch job is easy -— just specify a Quartz cron expression while configuring the data source:

New Stitch features empower more flexible data pipelines

Advanced scheduling is rolling out gradually across the more than 100 integrations Stitch supports. All of our database integrations and about half of our other integrations support cron scheduling today, and we'll be adding support for more data sources over the coming weeks.

Connect your workflow with post-load webhooks

Organizations looking to automate and extend their data loading process can now trigger events via post-load webhooks, which let you add downstream tasks to the loading processes via AWS Lambda, Zapier, or other tools upon the completion of Stitch loading jobs.

Post-load webhooks takes the "did my data finish loading?" guessing game out of the equation and lets you define your ETL cadence on your terms.

Stay informed with notification extensibility

Data engineers and their teams can now save time by integrating Stitch monitoring and notification with the system they use to monitor the rest of their infrastructure. Stitch can send status and error emails to notification services like PagerDuty or Datadog, allowing you to centralize monitoring and sleep more soundly. Check out our PagerDuty and Datadog integration guides for details.

Manage ETL programmatically with our API key management

Want programmatic control of Stitch, beyond what you get from our dashboard? You can configure Stitch directly with the Stitch API, and we've added new API key management features to make it easier to access the API. You can take programmatic control over your Stitch account for pipeline operations such as replication scheduling and table and column selection. For example, with the API you could create a preselected roster of fields and tables to replicate without selecting them one by one on the dashboard. If you have multiple integrations of the same data source, large numbers of tables, or multiple instances of sharded databases that get created regularly, you can save valuable time by using the Stitch API to handle operations that would take longer in the UI.

Get started today

All of these features, along with unlimited data replication, are available to new users during their 14-day free trial period. After the trial, these features are available with a Stitch enterprise plan. If you're interested in one or more of these features, or want to learn more about the other advantages of Stitch for the enterprise, get in touch.

<![CDATA[Business intelligence vs. data analytics]]>https://www.stitchdata.com/blog/business-intelligence-vs-data-analytics/5d0a5a7fa5b5eb0037963d9cTue, 25 Jun 2019 14:58:40 GMTBusiness intelligence vs. data analytics

Data-driven organizations often use the terms "business intelligence" (BI) and "data analytics" interchangeably. They're not the same thing, but if someone asked you to explain the difference, what would you say?

Some people distinguish between the two by saying that business intelligence looks backward at historical data to describe things that have happened, while data analytics uses data science techniques to predict what will or should happen in the future. We think that's close, but there's more to it.

Business intelligence involves the use of data to help make business decisions, or as OLAP.com puts it, BI "refers to technologies, applications, and practices for the collection, integration, analysis, and presentation of business information. The purpose of business intelligence is to support better business decision-making." However, one could say the same about data analytics.

To draw the line between business intelligence and data analytics, we think it's more useful to talk about what we want to accomplish. We can divide analytics into three categories: descriptive, predictive, and prescriptive.

Descriptive analytics takes data and turns it into something business managers can visualize, understand, and interpret. It provides intelligence into historical performance, and answers questions about what happened. Descriptive analytics reports are designed to be run and viewed on a regular basis. Examples include customer, operations, and sales reports.

Predictive analytics provides insights about likely future outcomes — forecasts, based on descriptive data but with added predictions using data science and often algorithms that make use of multiple data sets. The more data available, the better the predictions. Examples include sales forecasting, consumer credit scores, and retailers' suggestions for what you may want to read, view, or purchase next.

Prescriptive analytics offers advice about what actions to take. It examines possible outcomes that result from different possible actions and suggests which actions will have optimal outcomes. Creating prescriptive analytics requires advanced modeling techniques and knowledge of many analytic algorithms — all part of the job of data scientists.

Big data strategist Mark van Rijmenam writes, "If we see descriptive analytics as the foundation of business intelligence and we see predictive analytics as the basis of big data, than we can state that prescriptive analytics will be the future of big data."

So what's the difference between BI and data analytics?

Using these three categories, we can make a better distinction between BI and data analytics.

All descriptive analytics falls into the category of business intelligence. Some predictive analytics also constitute BI. After all, why look at analytics if you don't intend to use them to take action to enhance future outcomes? Prescriptive analytics, however, rises above BI into the realm of data analytics.

Where do we draw the line? Business intelligence relies on data that business managers work with. If they're trained in using visualization tools, such as Tableau, Microsoft Power BI, Looker, or any of a host of other options, they could create their own BI reports.

Data analytics requires a higher level of mathematical expertise. Data scientists take big data sets and apply algorithms to organize and model them to the point where the data can be used for forward-looking, predictive reports. It relies on algorithms, simulations, and quantitative analysis to determine relationships between data that aren't obvious on the surface. That doesn't happen with BI.

Rather than answering questions about what happened, data analytics tries to learn why things happened. Stitch co-founder and Talend SVP Jake Stein says, "Data analytics is about iteratively asking questions. The answer to any given question is often viewed only once and used to inform the next question on our way answering a fundamental business question or solving a problem."

Common ground for business intelligence and analytics

Business intelligence addresses ongoing operations, helping businesses and departments meet organizational goals. Data analytics can help companies that want to transform the way they do business. Both disciplines can benefit from a little data preparation.

Data analytics generally requires data modeling, in which raw data is collected, cleansed, categorized, converted, aggregated, validated, and otherwise transformed. Clean data is also helpful for BI.

Once the data is clean, it's stored in a structure and format that lends itself to reporting. Often that means the data is stored in a data warehouse — a columnar data store that, nowadays, often runs on scalable cloud infrastructure. The data in the data warehouse represents a single version of truth for all organizational reporting, for both BI and data analytics.

Both BI and data analytics call for an analytics stack founded on a data warehouse, with data piped in via an ETL tool. Stitch makes populating your data warehouse easy.

Try Stitch for free

Case closed?

Does this discussion settle the question? Not likely. No matter how we define it, people are still going to use terms however they like. So what if someone says, "Data analytics is how you get to business intelligence" or "Business intelligence encompasses data analytics"? What if they want to talk about "business analytics"? So be it. The point of both processes is to analyze data and create reports to improve decision-making — on that point, everyone agrees.

Image credit: Jeff Dahl
<![CDATA[The Stitch guide to choosing a business intelligence tool]]>https://www.stitchdata.com/blog/choosing-a-business-intelligence-tool/5d07eeb3a5b5eb0037963d97Tue, 18 Jun 2019 13:01:00 GMTThe Stitch guide to choosing a business intelligence tool

You want to choose the best business intelligence (BI) program for your organization. The problem is that there are so many products and platforms available — how do you choose the best one?

Here's our guide to choosing a BI or data analytics platform, developed through what we've learned from working with thousands of companies to power their data infrastructure, as well as discussion with data analytics professionals and experts at companies that offer BI products and services. There are three high-level steps in the decision process: gathering information by asking questions, creating a scorecard, and performing evaluations.

1. Ask questions about data usage

The first phase of the process involves learning about your organization and the way it uses data. Ask questions about who, what, when, where, how, and why.

Who handles the data?

In your organization today, who works with data? Do you have a team of data analysts who build all your visualizations? Do business users create their own reports? Are data scientists building models with the data? Who's responsible for data transformations?

Why you need this information: Data scientists and other sophisticated users have different needs and expectations than business users. Talk to everyone who creates reports (or, if you're in a large organization, a representative sample) and learn about how they use data and what their requirements are. Are they viewing dashboards daily, or do they need hourly updates? Do they print reports? Do they collaborate with other users? Do they need to create ad hoc visualizations?

The question of data transformations is as significant as the choice of data warehouse. Chances are you're going to want to replicate your raw data into your data warehouse, and transform some of the data once it gets there to make it more useful for reporting. Some BI tools can perform transformations on raw data, but many organizations prefer to run transformations as a separate step. Tools designed to do transformations include dbt, Talend Open Studio for Data Integration, and Talend Pipeline Designer.

How you'll use this information: The information about who uses analytical data will help you compile evaluation criteria for screening potential products and, eventually, trying them out in-house.

If non-technical employees need self-service analytics, then you'll want a tool with an intuitive point-and-click interface. If you plan to have only technical workers use the tool, then you can explore tools that use code as input. In some cases, only expert users can use a tool to its full potential. Then you have to ask what's the opportunity cost of having expert data analysts build and update all reports, compared against a tool that enables anyone to get analytics on their own.

One tool may be able to meet the needs of both constituencies. Many organizations feel it's better to avoid multiple tools across different departments. Other organizations choose to select different tools for different types of users.

What kind of questions do you need to answer?

Write down the use cases for your organization's BI tool, with as many specifics as you can, including what users expect to accomplish with your reports, visualizations, and analytics. Some common needs include:

  • operational reporting snapshots
  • data exploration
  • spreadsheet integration (both directions)
  • data analysis
  • self-service custom reports, charts, and graphs

Why you need this information: BI tools are complex products with many capabilities. It's hard to know how to start evaluating them. You're better off starting from your needs than a product's capabilities, because some of those super shiny features won't be useful for your organization.

How you'll use this information: Having use cases and users' goals will help you build the evaluation criteria you'll use.

Where is the data?

Where does the data you're reporting on live? Is it in in-house databases? SaaS platforms? A corporate data warehouse?

Why you need this information: You have to consider your BI tool in the context of an entire data analytics infrastructure. Some tools read directly from a variety of data sources — but often not all the ones you use. Some connect with cloud data warehouses, and these data warehouses need to be filled with data replicated from all of the sources you want to analyze. That's where Stitch comes in. Stitch connects to more than 90 data sources and replicates data to any of the the popular cloud data warehouses, from which any BI tool can access data from all of your sources.

Try Stitch for free

How you'll use this information: Knowing where your data is helps you consider how to round out your analytics infrastructure. Many important analytic tasks are impossible without a data warehouse. If you don't already have one, you can evaluate a data warehouse simultaneously with BI tools, and thereby ensure you have a data stack that really suits the needs of your business.

What does the data look like?

You also have to take into account the state of the data being queried. How much data prep is necessary before someone can use the visualization tool? If self-service users need to have access to the data, but the data is messy or poorly modeled, then you need to factor in a transformation tool, or your BI tool needs to be able to make transformations. You have more options if the data is modeled cleanly and there are no logical calculations being done, other than generic aggregations of available fields.

Why you need this information: If your data is poorly prepared, you'll need to clean it up before you can use it. The alternative is to limit report creation to experts who can work around the flaws.

How you'll use this information: You'll have to consider the opportunity cost of having expert data analysts build and update all reports vs. enabling all kinds of users to get their analytics themselves.

What's your budget?

Before you bring in any BI tool, you need to know how much you can afford to pay for it. And, as we've seen, your budget may have to cover more than just the BI tool — it may also have to encompass implementing a data warehouse and an ETL tool. For each of those components you must consider licensing costs, operational costs, and, if you use on-premises resources, hardware costs. (Hint: Don't use on-premises hardware. A cloud-based data warehouse can scale elastically to handle virtually any level of data processing. That makes it attractive to do any necessary data cleaning or transformation once the data is already in the data warehouse, which in turn makes the ETL pipeline faster and easier to use.)

Then there are expenses on top of what you pay for the software. You may need technical experts to transform data and maintain data models, or data engineers to write and manage home-brewed ETL pipelines. You may need to provide training for your staff, conducted by a peer leader, if you're lucky, or by a vendor or a third party.

Do your costs have to be predictable? If not, you can take advantage of a cloud platform's usage-based pricing to save money during slack periods, and spend more during periods of heavy usage, such as year end.

The number and kind of users also affects pricing. Some tools don't charge anything for dashboard users. Others require licenses for anyone who creates or consumes BI.

2. Make a scorecard to evaluate BI tools

At this point you should have a good feeling for how your organization uses analytics and how your colleagues want to use new BI tools going forward. The next step is to create a list of criteria on which to evaluate each BI platform.

Criteria can be as general or specific as you find useful. Here are some points to consider (not a comprehensive list). Does the tool have the ability to:

  • Run on your client platforms (Windows or Mac) or in a web browser
  • Access the specific data sources you use
  • Integrate data from multiple sources, databases, and tables
  • Create custom measures for reporting
  • Create specific visualization types (line graphs, scatter charts, maps, etc.)
  • Perform interactive filtering
  • Aggregate visualizations in dashboards
  • Drill down into visualizations for in-depth reporting
  • Create mobile-friendly visualizations, or let users work on data from a mobile device
  • Meet your organization's governance requirements — for example, will the vendor of a BI platform sign a BAA for an organization bound by HIPAA?

You should also think about how well the tool will perform in the future. The volume of queries against your data will almost certainly grow once you have a tool in place. Look for functionality that may not be useful for your organization today but could come in handy eventually.

Create a rubric for each criterion so that everyone involved in the evaluation process understands how they should evaluate all the tools. The rubric also serves as documentation for managers who aren't involved in the evaluation process.

Next, take the list and divide your criteria into two groups: must have and nice to have. If a factor is truly must-have, and a tool you're considering doesn't support it, you can cross that tool off your list.

Once you've considered all of the yes/no points, you can turn to more nuanced questions, such as:

  • How easy is it to administer the tool? and
  • How much time does it take to build a report?

You can score these questions on a numeric (1 – 10) or qualitative scale (poor, mediocre, good, great). Some people like to give an even number of choices (0 – 3) to force people to score something as either above or below a middle value. You can also attach adjectives to factors, such as intuitive or simple or clunky or deficient.

You may better organize your thinking if you group your criteria into categories, such as:

  • Analytics capabilities
  • Reporting and data visualization capabilities
  • Consumption/sharing of reports

Add up the scores in each of your categories, or weight them according to their importance, to get an overall ranking. Look for one to three products or platforms to rise to the top.

3. Conduct hands-on evaluations

Now you can bring in the top contenders and try them with your data. All product demos look slick, but how well does the tool perform in practice?

Use a small project to see how quickly stakeholders can use the tool. Watch how your users work, and find out how close they can get to their ideal solution.

During this part of the evaluation you can judge how well the developer supports its tool. Does the vendor provide adequate online documentation? Is tech support available, and if so, how quickly can you get attention? If you have a difficult problem, will you have to pay extra for consulting?

How well does the new technology fit in with your existing infrastructure? How much hand-holding do users need? If they need a lot during the evaluation, it may be an indicator of what to expect later when you have more complex requirements — and it may foreshadow how much support you'll have to provide to internal customers.

Reach out for peer evaluations

Hands-on evaluations are critical, but you can also ask other data professionals about their opinions of the tools you're considering. If you don't already know anyone who can give you an opinion, go to a local user group meeting and ask your questions there.

Give special emphasis to any positive assessments; people remember negative experiences they had and are happy to talk about them, but people tend to have fewer good things to say about products and services.

Pull the trigger

When you reach the end of this long process you're ready to buy. We'll leave negotiating the best deal to you. Rolling out the solution is also a topic for another day.

Finally, once you've made the purchase decision, don't stress about whether the product you picked was the absolute best one you could have chosen. Chances are that if a product made it to the top of your list, it will do fine for you.

Thanks to the experts who contributed their advice for this post: Jennifer Hudiono and Alex Poulos for Chartio; Erin Franz at Looker; Adam Bonefeste and Lauren Gimmillaro at Periscope Data; Redditors opendataalex, triiimit, olavhellothere, and secretWolfMan; Amy McQuaid at Talend; and Roshan Bhalla, Rob Moore, and Qun Wei.

Image credit: Growthlakes
<![CDATA[How to create a map of your customers using Mode and Google Maps API]]>https://www.stitchdata.com/blog/how-to-map-customers-using-mode-google-maps-api/5cb9f3026b6d7800bfaac1b4Tue, 11 Jun 2019 13:01:00 GMTHow to create a map of your customers using Mode and Google Maps API

Stitch is based in Philadelphia, but we have customers all over the world. I used our data and a few apps and resources to generate maps to show where our customers are. We find this useful whenever a member of our team is traveling and wants to find out which customers are in the area.

I started by building a SQL query to tie our customers to their locations. For location data, we rely on data from Clearbit and its Enrichment API. I joined the our internal customer information, stored in our data warehouse, with Clearbit data for the same company and retrieved region, latitude, and longitude fields for each customer's location.

I then switched to the Mode analytics platform to create a Google Map with markers. I pasted in my SQL code, then switched to Mode's Report Builder, where I pasted in some simple JavaScript from the alamode.js library to call the Google Maps JavaScript API. The API lets you customize the results, but I was aiming for simplicity and speed. Here's the code I used:

<link rel="stylesheet" href="https://mode.github.io/alamode/alamode.min.css">
<script src="https://mode.github.io/alamode/alamode.min.js"></script>
<div class="mode-header embed-hidden">
  <h1>{{ title }}</h1>
  <p>{{ description }}</p>

<div class="mode-grid container">
  <div class="row">
    <div class="col-md-12">
      <mode-table id="table_7c176e57d750" dataset="dataset" options="table_options"></mode-table>

    google_maps_api_key: "ourapikey",
    title: "Stitch Customer Locations",
    lat_column: "lat",
    lng_column: "lng",
    label_column: "client_name",
    query_name: "Query 1",
    center_lat: 39.9524,
    center_lng: -75.1636,
    starting_zoom: 2,
    map_type: "roadmap",
    height: 900

When I ran the report, it produced the nifty map you see at the top of this post.

In this worldwide view the pins for our customers' locations are lumped together pretty closely, so I ran a few additional queries to zero in on each continent, in which I specified where region = 'regionname' and specified the geographic center of each region in the center_lat and center_lng fields. Here are zoomed-in views on Europe, Asia Pacific, South America, and Africa.



Asia Pacific


South America




A few caveats on these maps: They're not 100% accurate, due in part to limitations of the Google Maps API and the data behind it. Also, they don't include a pin for every customer in every location. We rely on Clearbit for location data, and not all companies have a public presence that Clearbit can use to pull data from. Also, some Stitch users sign up with email addresses that we're unable to associate with a company. However, they are directionally accurate most of the time.

Stitch is all over the map

These maps clearly show that no matter where you are, you can use Stitch's ETL platform to save time and money. We have hundreds of customers outside of the US, including many from countries with data localization regulations, including the EU General Data Protection Regulation (GDPR) and the APEC Cross-Border Privacy Rules (CBPR) System. They can meet all of their compliance requirements with Stitch.

The only continent where we lack a presence is Antarctica. If you work for an organization with an Antarctic presence, we've got a deal for you: If you sign up for Stitch now, we'll give you 14 days of unlimited replication for more than 90 data sources, for free — and we'll send you a pair of Stitch socks, because ... Antarctica.

Try Stitch for free for 14 days

  • Unlimited data volume during trial
  • Set up in minutes
<![CDATA[Why and how we migrated from Redshift to Snowflake]]>https://www.stitchdata.com/blog/migration-from-redshift-to-snowflake/5cf53b7e930db00037051e31Tue, 04 Jun 2019 13:01:00 GMTWhy and how we migrated from 
Redshift to Snowflake

Like our clients, Stitch is a data-driven company. We ETL data from more than 35 different sources into our data warehouse, with replication jobs running 24 hours a day. We run dbt to model the data we load and build tables that our analytics reporting tool, Mode, relies on for both scheduled and ad hoc reporting. Our team also uses desktop SQL clients and Python scripts to accomplish various miscellaneous tasks and jobs. All of this means we’re hard on our data warehouse.

We've been using an Amazon Redshift data warehouse since we started Stitch. It was a powerful tool for our growing business. Redshift made it easy for us to unify our data and build a foundation for the internal analytics that powered lots of our critical business processes. It satisfied our company's needs, as it does for many of our customers.

But as we grew, and as our use of our data became more complex, we began having performance issues, including long query wait times, crashes, and lockups. We’d have to restart our Redshift cluster, kick off manual runs of dbt, manually cancel queries to get things moving, and limit our scheduled runs in Mode. We had report outages, leading to urgent Slack and email messages on the weekend, not to mention the cumulative hours of diminished productivity for our team as they waited for queries to complete.

We might have been able to remedy these issues if we’d hired a database administrator to actively manage Redshift. In the absence of that technical help, our crude solutions to making Redshift work better took up time that we'd rather have spent doing the jobs we were hired for. At the end of the day, we weren’t able to get the level of reliability we needed from Redshift.

To be fair, some of the issues we had are actively being addressed by the AWS team. Nevertheless, we began considering other data warehouse solutions.

How we chose Snowflake

In early 2017 we built a Stitch destination for Snowflake. We’ve seen some exciting adoption since then. Today, hundreds of companies are using Stitch and Snowflake together, and Snowflake has become a close partner of ours. You can even sign up for Stitch from within your Snowflake account. Our team has had lots of experience working with Snowflake from both a partnership and a technical standpoint. As a result, we have become hyperaware of both the ideological alignment of our companies' mission statements and the high degree of success our shared customers are having.

In addition to all of this, Talend, which acquired Stitch last year, is a Snowflake customer. Migrating to the same data warehouse that the rest of the company uses was another compelling reason for choosing Snowflake.

Snowflake's powerful features promised to help our warehouse perform well even if we began to experience heavy loads. Right away, we saw several Snowflake features that we were eager to take advantage of:

  • Auto-scaling — Snowflake’s warehouses (compute nodes) can scale for both performance (warehouse size) and concurrency (warehouse clusters), concurrently or independently. We decided to set up a single extra small warehouse with autoscaling up to 5 nodes. When Snowflake sees a high query load, it can automatically start adding nodes to our cluster to deal with the increased demand. That gives us more power when we need it, and less cost when we don’t.

    At the time we made the decision to migrate, scaling Redshift required active management, taking time that we weren't willing to invest. Amazon has since added capabilities in this area.

  • Native support for structured and semistructured data — Snowflake supports the VARIANT data type, which can store values of any other type, including OBJECT and ARRAY. When Stitch loads JSON data to Snowflake, it uses the VARIANT type to store the JSON contents in a single column of a table. When you have all of your data in one table, you can use the powerful JSON querying tools to work with JSON in Snowflake.

    Redshift has limited support for semistructured data types; instead, we relied on subtables, which forced us to do multiple joins to get all the data into one table again for reporting.

  • Snowflake Data Sharing — With Snowflake Data Sharing, we can securely share datasets with anyone in or outside of our organization. This comes in handy now that Stitch is part of Talend. When you share data in Snowflake, it doesn’t move any data from S3, other folks just get access.

    It's not as easy to share data in Redshift. Giving another person access takes manual work with an S3 bucket.

  • And more —

    • Time Travel lets us instantly roll back the entire warehouse to any point in time during a chosen retention window.
    • Cloning lets us quickly clone databases, schemas, and tables without copying the data.
    • Snowflake uses result caching to retrieve only data that’s different since the last time you executed your query.

The data warehouse migration process

Migrating the data warehouse on which all of our analytics reports rely was a big and scary prospect. To get started, I pointed our internal Stitch account at a new Snowflake warehouse and, within a day, Stitch had completed historical loads of almost all of our production datasets. This was great for 80% of our data migration needs.

Stitch dutifully loaded all of our historical data for SaaS tools like Zuora, Jira, and Intercom into Snowflake. However, we had a few datasets where historical data wasn’t available for Stitch to resync for various reasons — rolling deletions in the source database, for example, or manually assembled datasets. We had fresh data from these sources flowing into Snowflake, but the historical data needed to be migrated manually and inserted into the table in Snowflake where new events were being loaded. The best example of this was our Snowplow data, which we use for web analytics. This process is more complex than it sounds.

In these special cases, our consulting partner Bytecode IO helped get our data where we needed it by walking us through several tasks:

  1. Loading a complete Redshift snapshot to Snowflake
  2. Refactoring our dbt models to the proper syntax for Snowflake. Both Redshift and Snowflake are ANSI-SQL but use slightly different commands, options, and syntax, including things like handling of timezones and timestamps, window functions, datatype casting, and null handling. This was no easy job.
  3. Validating all dbt models and datasets to make sure the data we had in Redshift and Snowflake matched (this task took the longest)
  4. Inserting historical data into the “live” tables being updated by Stitch

In the special case of Snowplow data, we had two different datasets in Redshift that needed to be merged with the fresh dataset in Snowflake. The first dataset was our own self-hosted Snowplow, which had historical data. The second (and our favorite) is hosted by our friends at Fishtown Analytics and is our source of truth for up-to-date Snowplow events. Since the latter is based on the Stitch Import API, it took all of two minutes to repoint the data stream to Snowflake. After that, the historical datasets from Redshift needed to be inserted into the “live” table in Snowflake with proper attention to dates, timezones, and JSON fields from subtables in Redshift. Because Redshift doesn’t support hierarchical datatypes, some of our JSON data was split into subtables when Stitch loaded the data into Redshift. Snowflake supports JSON fields to contain those values. We needed to migrate the historical values from the subtables in Redshift into the “live” tables in Snowflake, recreating the JSON structure.

Migrating data models and reports

Once our data was in good shape, I turned my attention to our data models and our reports. Stitch has more than 160 dbt models that were written against our Redshift database. Though the SQL syntax is similar between Redshift and Snowflake, the differences are significant enough that each of our 13,000 lines of SQL had to be inspected and refactored where needed.

Once we had replicated all of our Redshift data to Snowflake, I began to migrate our Mode reports. Mode supports multiple database connections on the same organization. Once we added the Snowflake connection it was easy to go into each query and flip the data source from Redshift to Snowflake, then rerun the query. Most of the time the queries failed on the first run, so some refactoring was required. Most notably, we needed to add considerations around timezones, timestamps, join keys, and uppercase/lowercase column names.

My process for migrating a production report in Mode, which was designed to minimize report downtime, looked like this:

  1. Clone the report twice (a backup version and a Snowflake version).
  2. Refactor the queries in the Snowflake version of the report.
  3. Copy the succeeding refactored queries to the production report.
  4. To get all the charts to work, rename all fields to lower case in the query. This helps avoid needing to rebuild each chart using the uppercase versions of the column names. (Column names are uppercase in Snowflake, and lowercase in Redshift.)
  5. Compare migrated production report to backup clone to ensure the two match.

Migrating the Mode reports was our final task.


Today our migration is complete. We now have fresher data, lower query wait times, and less report downtime. Our dbt run used to take 45 to 60 minutes to complete in Redshift, and because that was a pretty long time, we ran dbt only twice each day to refresh our reports. In Snowflake, the same run takes 5 minutes or less, so we execute the run hourly.

We’re spending more money on Snowflake. We could have upped our spending on Redshift, and likely would have gotten performance improvements, but we think we're getting more value from our spending on Snowflake. We went with Snowflake because we want the freedom of a fully managed and optimized data warehouse without the need to invest expensive people hours. Snowflake has had automatic optimization baked in from the start, and we’re ready to benefit from their vision as we scale our business and data.

We're happy to have this migration behind us. Snowflake is capable of delivering more power than we're using, and that portends success as Stitch scales. If you're looking for a boost to your data warehouse, give Snowflake a try, and sign up for Stitch to get data flowing to it quickly and easily.

<![CDATA[How building a transformation layer helps us do data modeling that scales]]>https://www.stitchdata.com/blog/transformation-layer-data-modeling/5ce405e56b6d7800bfaac1d7Wed, 22 May 2019 13:01:00 GMTHow building a transformation layer helps us do data modeling that scales

Creating a data pipeline can be a messy job. A typical company has a huge number of data sources that generate important data on a daily basis, and it’s up to the data analysts, scientists, and engineers to turn all this information into something that people can actually use to make smarter decisions.

At a high level, the data architecture here at Envoy follows the extract-load-transform (ELT) paradigm to get our data into a usable state. (If you want to know why we use the ELT paradigm rather than the more traditional ETL paradigm, I would suggest starting with this well-written article.) As the name implies, the three main steps are extracting data from where it is generated, loading it into a data warehouse designed for analytics, and then transforming that data into something that people across the company can actually use.

For the E and L components of our pipeline, we use data ingestion tools like Stitch along with a few custom-built data ingestion apps based on the Singer framework that handle ingesting data from sources that don’t yet have commercially available data loaders. While I could probably write several articles just on these two steps of our data architecture, I’m going to focus on the T, the transformation step. This is the step where you take all the raw data that you’ve worked so hard to get into your data warehouse and make it into clean data tables that can start to generate useful insight about your business and product.

Data transformations 101

The very first “data transformation” that happens at a company is when the first analyst, let’s call him Mike, writes a SQL query and generates a report. It’s as simple as that: Some amount of data is taken and shaped into a digestible format that others in the company can understand.

Mike generates all sorts of reports and dashboards in his first few months on the job, and pretty soon is joined by data team member #2 — let’s call her Sandra. This is the point where previously unknown problems start to surface. In her reports, Sandra converts all the server timezones to PST, while Mike left them as UTC, causing monthly customer counts to be different. In another report, Mike filters deleted users out of his calculations, but Sandra includes them, resulting in different user counts that confuse the product team.

For a while these minor discrepancies can be managed through documentation and communication, but as a company and data team grow, data inconsistency and inaccuracy can become a huge drag on productivity if left unmanaged.

This is where the value of a transformation layer starts to shine. Instead of relying on analysts to apply consistent logic on raw data when creating reports, a more scalable approach is to package this logic into operations that clean and transform raw data in helpful ways.

For example, a simple transformation might be to convert all timestamps to PST, regardless of what timezone the raw data was in. When analysts use these “transformed” tables, they can use timestamps without having to worry about timezone conversions. More examples of useful transformations are deleting test accounts, removing spam email, and converting cents to dollars.

To take things even further, transformations can be used to collect and aggregate important information about an object so analysts don’t have to go hunting around the database for basic information. For instance, adding columns like total_revenue and first_payment_time to your accounts table might save analysts a lot of time, because they can then use those fields in their analysis without having to worry about computing them from the raw financial data.

Ultimately, the outcome of a solid transformation layer is a set of clean tables that the analysts can base their analysis on.

Transformations at scale

Hopefully by this point you see how transformations can make your data team more productive, but you might ask what happens when the number of data sources continue to expand, and the data team continues to grow? How do you ensure that analysts can quickly understand which transformations already exist and are ready for use, and which ones need to be made from scratch? At Envoy we started running into these questions repeatedly; we have data from 40 different sources in our data warehouse and have nine data analysts who constantly create reports, dashboards, and analyses from all this information.

For several months, people were creating transformations on their own with minimal guidance or direction, and the result was absolute chaos. Hundreds of data transformations sprouted up within weeks, and no one could find things they were looking for. Many team members were re-creating transformation pipelines, since they didn’t know the one they needed at the time already existed. The time to run all these transformations was growing unsustainably. Column and table names were not consistent, and as a result we ended up with the very problem we were trying to solve with all these transformations in the first place: data inconsistency and data inaccuracy!

To give you a sense of the scope of this problem, here’s an example of the full DAG of a single data model that's the foundation of all our financial metrics. Each node is a SQL transformation. The green nodes represent raw data.

How building a transformation layer helps us do data modeling that scales

Everyone knew this was a problem. Eventually we stopped kicking the can down the road and called a meeting to discuss things. We quickly realized that what we needed was a framework to organize our data transformation pipeline — something that would be easy to navigate, consistently named, and run quickly.

Bringing order

The framework we settled on relies on three layers: base models, transform models, and stakeholder models. Here’s a quick description about what happens in each layer:

  • Base — This is the only layer that points to raw data tables in our warehouse. Keeping raw data access confined to a single place in your data pipeline makes it easier to facilitate changes to the raw data itself. For example, things like schema name changes and column name changes only need to be made in one place. Doing this also allows you to standardize your data across many data sources by doing things like column aliasing, currency conversions, and timezone conversions. Some of our data sources send us data in UTC, while others send us data in PST. Some raw data points are stored in cents, while others are stored in USD. The base layer is where we standardize all of this, so downstream models don’t have to worry about timezone conversions or currency conversions at all. We also alias column names, for instance changing columns like external_account_id to envoy_company_id, to make fields more self-descriptive and minimize the time analysts have to spend poring through documentation.
  • Transform — This is the layer where we construct our core business objects, creating tables that represent important entities for our business, and a lot of associated metadata. For example, our most important table is our companies table, with each record representing a single account on our platform. There are columns designating how much revenue we make from each company, who the associated sales reps are, key product metrics for the company, and much more. The real benefit of this transform layer is that commonly used fields can be pre-computed and become easily accessible for analysts. Team members no longer have to go swimming through Salesforce or billing data to get basic information — it’s available right there on each record. Test records are also filtered out here, so the tables that are produced by this layer can be relied upon to contain only clean data. An additional benefit is that query times are typically reduced dramatically in our BI tool; since a lot of the data is denormalized and pre-computed, the need for joins and aggregations is reduced, which in turn makes most queries run much faster.
  • Stakeholder — The last layer we have in our transformation pipeline houses the models that are often too specific to be considered a core object. This layer relies on models from the transform layer, but applies custom logic that is specific to a certain use case. Some examples here are marketing attribution data models for the marketing team, or revenue recognition for the finance team. Having this layer gives us a place to put models that need to be truly customized. We organize this layer by each department in our company (finance, customer success, product, etc.) so it’s clear to an analyst where to put a custom model that he or she built for a stakeholder.

Separating data transformations into these three distinct layers improves the organization for the data team. When an analyst needs to add another column to a data model — maybe they want to add a time_to_conversion column on our salesforce_leads model — it’s clear where to put that logic. If a data source needs to be added for new types of analysis, it’s evident to each team member how to incorporate the necessary data transformations into our pipeline while maintaining consistency with the rest of the project.

As the project grows, this consistency makes finding things easier for all the team members. When there are only one or two analysts, this may not seem like a big deal, but as the data team grows, people can find themselves sifting through data transformations to find what they need, which can be a huge drag on productivity. Sticking to a consistent framework saves the team a lot of frustrating hours.

Final thoughts

Coming up with a consistent framework and sticking to it has been an important factor in allowing our data team to scale with the company. Empowering analysts to build on our data infrastructure has minimized “knowledge silos” that often pop up as companies start to scale, and kept our time to insight down. Since any analyst has the ability to take raw data and create transformations out of it, the whole team can complete requests from end to end. And more importantly, enforcing consistency across our data has enabled us to move much faster, as we can move from one data source to another without having to familiarize ourselves with the nuances of each table.

Our transformation framework, processes, and infrastructure was the result of a couple of rounds of trial and error, and I’m sure it will continue to change as we grow. I hope the best practices we’ve learned can be helpful to any of you who are feeling the struggle of scaling a data team of your own.

Image credit: Lorie Shaull
<![CDATA[How to connect a Singer tap with Stitch]]>https://www.stitchdata.com/blog/how-to-connect-a-singer-tap-with-stitch/5cc858396b6d7800bfaac1c5Tue, 30 Apr 2019 14:19:08 GMTHow to connect a Singer tap with Stitch

We think it’s critical that ETL be extensible to support any data source. That's why we created the open source Singer project. Singer’s goal is to enable anyone — our team, our customers, our partners, and the open source community — to build and use integrations, regardless of whether they're a Stitch customer.

Many organizations use popular SaaS platforms such as Google Analytics and Google Ads, Salesforce, Marketo, Shopify, and Stripe, which are supported by Stitch and by other ETL tools. But businesses may also use applications that are less popular but still essential for their business, and for which there are no integrations available. We made Singer to provide a path to integrate with any application that’s not beholden to anyone’s roadmap.

We also made Singer open source to let organizations share their work, and leverage the work of others. There's no point in reinventing the wheel when you can use or adapt an integration someone else has already created. And once you share you work, other developers may contribute code to enhance your Singer integration. The Singer community has already built dozens of integrations.

If you build a Singer integration, you can make use of it three ways.

Run Singer entirely on your own hardware

Singer taps (which pull data from sources) and targets (which send data to destinations) are self-contained applications. You can run them in your own environment using the scheduling and orchestration solution of your choice. We wrote a tutorial on using Singer with Airflow. Other options include using Luigi, cron, and custom-built schedulers.

An advantage of this approach is that you have total control of your data pipeline. If you have constraints that don't let you run your data through a cloud pipeline, or you don't want to open source any code, this is the way to go — but there are drawbacks.

Singer taps and targets will extract and load your data, but there's more to a data pipeline than that, and if you run the Singer applications on your own hardware, you'll have to write supporting infrastructure code yourself. We explain in another blog post why most people shouldn't build their own data pipeline.

Submit your tap to Stitch

Another approach, and the one most organizations take, is to have a Singer tap built into Stitch. This is the easiest option for the long run, because it requires the least ongoing work for you. Once you've written and tested your code, share it with the community. Stitch developers will review your code, collaborate with you to test the integration, and list your tap or target on the Singer site. We'll also integrate a new tap into Stitch, build documentation for it, and release it for others to use.

This approach gives you all of the advantages of being part of Stitch. Our pipeline is fast, highly reliable, and certified for HIPAA and SOC 2 security and privacy. We provide logging, monitoring, alerting when problems crop up, credential management, and autoscaling infrastructure.

Hybrid — run Singer locally and send to Stitch

You can also run taps on your own hardware and then send the data to the Stitch API, taking advantage of Stitch's features for the load phase. Stitch will make sure your data gets to your destination, whether that's a cloud data warehouse like Amazon Redshift, Google BigQuery, Snowflake, or Microsoft Azure SQL Data Warehouse, or a database like PostgreSQL. This is a good option for organizations that need to ETL data from a new tap right away, before we can certify their code and integrate it with Stitch, or ones that don't want to release the code for a proprietary data source under an open source license, but want to load to a Stitch-supported destination.

To do this, start by following the README instructions to install the Stitch target. If you're not already a Stitch user, sign up (it's free). Make note of your client ID — the six-digit number that appears in the URL after https://app.stitchdata.com/client/ — because you'll need to paste it into a config file.

You'll also need an API access token, which you generate by adding Stitch's Import API as a data source. Within Stitch, click on Add Integration, choose Import API, and follow the instructions in our documentation. Copy the token that Stitch generates.

Use a text editor and create a new file that looks like this:

  "client_id" : clientid,
  "token" : "token"

Paste in both the token and the client ID and save the file as config.json.

Finally, in the directory in which you saved the config file, run the command

$ _yourtap_ | target-stitch --config config.json

Use the name of your tap in place of yourtap, of course.

If all goes as expected, your tap will begin extracting data and sending it to Stitch, which will replicate it to the destination you've specified. If you need support along the way, join the Singer Slack community.

Get started with Singer

Stitch is adding integrations all of the time, but if you have a custom data source that we don't support, follow the getting started documentation and try writing your own tap using Singer.

Image credit: darf_nicht_mehr_hochladen
<![CDATA[Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics]]>https://www.stitchdata.com/blog/tutorial-using-redshift-and-amazon-quicksight-to-deliver-business-analytics/5cab81e56b6d7800bfaac1a7Tue, 16 Apr 2019 13:02:00 GMTTutorial: Using Redshift and Amazon QuickSight to deliver business analytics

How do you begin combining data from SaaS applications with your internal databases to gain insight into your business? Maybe your organization has already standardized on Amazon QuickSight as your analytics tool, but you're still learning about using it with multiple data sources. In this tutorial, we’ll show you how QuickSight can help you deliver business insights.



Once you have data in your data warehouse you can use QuickSight for business intelligence (BI). It can connect with more than two dozen data sources, including data warehouses like Amazon Redshift and Snowflake; databases like MySQL, PostgreSQL, and Microsoft SQL Server; and AWS services like Aurora, Athena, and S3.

Setting up a data warehouse


The first step was to choose or set up a data warehouse to hold the data I'd be replicating from Salesforce and Intercom. If you don't already have a data warehouse, choose one that meets your needs. I picked Panoply, a platform that provides a managed version of Redshift. If you choose Redshift, Snowflake, or one of the other destinations Stitch supports, you can follow the setup steps in the Stitch documentation.

Setting up Stitch for ETL

The next step is setting up an ETL pipeline to replicate data from your sources to the data warehouse. Stitch makes extracting data from a source and loading it into a data warehouse easy. To get started, visit the signup page and enter your email address, and on the next page enter your name, your company's name, and a password.

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

I added Salesforce and Intercom:

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

The documentation linked at the top of the Configure Integration page walks users through the process, which is slightly different for every data source. Depending on the integrations you set up, Stitch may ask you to choose the tables whose data you want to replicate and the fields within those tables.

Add a destination

Once you've selected all the integrations, tables, and fields you want, it's time to select a destination:

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

If you don't already have a data warehouse, you can set up a Panoply destination easily from within Stitch. At the Select a Destination screen, click on Panoply, click on Create a New Account, and follow the prompts. Stitch will use the email address you logged in with, autogenerate a secure password, and set up a database destination. Save your login credentials so you can access your new data warehouse in the future.

Clicking on the Panoply icon brings you to a screen where you can enter your credentials:

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

Fill in your data and save the screen, and you'll have all the pieces in place and the data ready to flow.

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

When you visit your Stitch dashboard, you'll see that your integration is marked Pending. It takes a little time for Stitch to queue up your replication. If you refresh the screen, you'll see the status change to In Progress.

From the dashboard you can also do things like change the replication frequency and add fields, tables, and integrations.

Connecting to your data warehouse from QuickSight

Once the pipeline is pushing data to your destination you can connect QuickSight to your data warehouse. Log in to QuickSight. If your data is in an RDS instance or Redshift cluster, choose it here.

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

Mine wasn't listed, so I clicked Connect to another data source or upload a file, chose Redshift, and entered my Panoply credentials.

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

Next, choose the schema you want to work with, and a table within that schema, then click Select. To finish creating your data set, you can choose to import the data to SPICE, QuickSight's in-memory optimized calculation engine, for better performance, or directly query the data. Click Visualize to complete the process.

To add additional tables to be used in a visualization, go to the main QuickSight page and click on New data set. Scroll down to From Existing Data Sources, pick your database, click Create data set, and select the next schema and table you want to work with, then click Select, or you can click Edit/Preview data, which brings up a screen that lets you deselect fields you don't want to work with, add calculated fields, and change field data types.

To join tables for more complex visualizations, you can click on Add data at the top of the data preparation page and use QuickSight's GUI. However, if you're familiar with SQL, you may find the process of visually joining tables cumbersome. Fortunately, QuickSight provides a better alternative. From the main QuickSight screen, click New analysis, then New data set. Choose your data source, then click Create data set. This time, instead of picking a schema and table, click Use custom SQL. Choose a name for your query, then paste in the SQL code you want to use and click Confirm query.

I spent some time in a SQL editor building a query to give me exactly the data I wanted. My query brought together columns from multiple tables in two schemas. The SQL code (shown below) excludes prospects by retrieving only companies for which we have account IDs, and selects current customers by excluding companies that are marked as deleted. It also consolidates clients on annual and monthly billing programs in each of our plans into a single number for each tier:

-- join users to companies
  intercomid AS (
    users.id AS userid,
    ucc.id AS companyid
  FROM intercom01.users AS users
  JOIN intercom01.users__companies__companies AS ucc
    ON ucc._sdc_source_key_id = users.id),

-- match Intercom ID with Stitch ID
  userco AS (
DISTINCT intercomid.userid,
company_id AS stitch_id
  FROM intercom01.users__companies__companies AS companies
  JOIN intercomid
    ON companies.id = intercomid.companyid),

-- get number of conversations
  convs AS (
    conversations.id AS convo_userid,
    count(conversations.id) AS convo_count
  FROM intercom01.conversations__customers AS conversations
  WHERE conversations.type = 'user'
  GROUP BY conversations.id),
-- join user/companies to conversations
  usercoconv AS (
    userco.userid AS userid,
    userco.companyid AS companyid,
    userco.stitch_id AS stitchid,
  FROM convs
  JOIN userco
    ON convs.convo_userid = userco.userid
  GROUP BY userco.userid, userco.companyid, userco.stitch_id, convs.convo_userid, convs.convo_count),

-- salesforce data
  sf AS (
    CAST(cid__c AS TEXT) AS stitchid,
      when tier__c LIKE '%Starter%' then 'Starter'
      when tier__c LIKE '%Basic%' then 'Basic'
      when tier__c LIKE '%Premier%' then 'Premier'
      when tier__c LIKE '%Custom%' then 'Enterprise'
      else tier__c
      end as tier
  FROM salesforce01.account AS account
    account.isdeleted IS FALSE
    AND tier__c IS NOT NULL
    AND tier__c != 'Free Trial')
-- join user/company/conversations to sf
FROM usercoconv
  ON sf.stitchid = usercoconv.stitchid
GROUP BY tier, usercoconv.convo_userid, usercoconv.userid, usercoconv.companyid, usercoconv.stitchid, usercoconv.convo_count
ORDER BY convo_count;

QuickSight returned a data set with the fields I asked for.

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

Now the trick was to create a visualization. I clicked Save & visualize, then selected vertical bar chart from the available visual types. I dragged the tier field to the X axis, and the count of conversations to the Value field, and QuickSight gave me the visualization I wanted.

Tutorial: Using Redshift and Amazon QuickSight to deliver business analytics

Using an ETL tool like Stitch to move data into a data warehouse lets you leverage the power of Amazon QuickSight to correlate and report on data from multiple sources. Sign up for a free trial of Stitch and start creating your own.

<![CDATA[Reconciling data from Google Ads and Google Analytics]]>https://www.stitchdata.com/blog/reconciling-data-from-google-ads-and-google-analytics/5ca394026b6d7800bfaac19cTue, 09 Apr 2019 13:01:00 GMTReconciling data from Google Ads and Google Analytics

Stitch customers sometimes ask us about discrepancies between the numbers they see in Google Ads (formerly AdWords) and those displayed in Google Analytics. Stitch replicates data from both platforms — shouldn't Google data be Google data?

Unfortunately, it's not that simple. Despite the fact that they're from the same company, these are different products that use their own terminology and business rules. One term, "conversion rate," means something different in each of the two products. Similarly, a click in Google Ads is not equivalent to a session in Google Analytics.

Google gets a lot of questions about this issue. They documented some of the data discrepancies between Google Ads and Google Analytics. If you see differences, Google explains some likely causes:




  • In another post, Google says, "When you compare Clicks from Google Ads and Sessions from Analytics side by side, you might expect that the numbers match, [but] they often don't." The post shows how "the process to record a click is straightforward; however, recording a session involves requirements and checkpoints," and "if latency is introduced ... this can ... reduce the click-to-session ratio (more clicks than sessions are recorded)."

    Finally, Google provides a table (below) that compares Google Analytics and Google Ads conversion metrics, and covers reasons for data differences across the two platforms:

      Analytics: Goal Analytics: Transaction Google Ads: Conversion
    Count Once per session per configured goal Many per session (Transactions with same Transaction ID within one session are not double counted.)

    User configurable for each conversion action:

    One conversion: Once per ad click

    Every conversion: Many per ad click

    Attribution time Time of Goal completion Time of Transaction completion Time of the last impression preceding the ad click that led to a conversion
    Attribution source Last non-direct click model by default, but configurable (in the Model Comparison Tool). Last non-direct click model by default, but configurable (in the Model Comparison Tool). Only counted if the user has clicked on an ad from the Google Ads account (google/cpc traffic only)
    Reporting Reported fully after a 72-hour processing window Reported fully after a 72-hour processing window Reported daily for your chosen conversion window (1–90 days, default: 30 days) 
    Handling of multiple codes on one page One session can have one goal completion for each configured goal. The total can be up to 20 per session. N/A If multiple Conversion Tracking tags for the same account are installed on a single page, Google Ads counts a separate conversion for each tag

    In summary, sets of data from Google Ads and Google Analytics are not consistent. And that's not really surprising — any two analytics tools are likely to deliver slightly different values for the same metrics. Each may define the metric in a slightly different way, and collect data using different methods. The discrepancies seem more surprising, however, when the two analytics applications come from the same company.

    Where does that leave you if you want to check the validity of the data in your data warehouse? You should compare the data you've replicated for each platform with what you see in the Google UI for each, separately.


    Which set of data is "better"? Each organization has to answer that question depending on its use case. Read Google's in-depth explanation of the differences to see whether one or the other makes more sense for your organization. Fortunately, as a data engineer, you don't have to choose. Sign up for Stitch and replicate both data sets to your data warehouse, then let your analytics team use the data that makes the most sense.

    <![CDATA[Delivering the right latency for analytics]]>https://www.stitchdata.com/blog/the-right-latency-for-analytics/5ca22ee96b6d7800bfaac198Tue, 02 Apr 2019 13:02:00 GMTDelivering the right latency for analytics

    Why doesn't Stitch replicate your data in real time? Because it's counterproductive for most Stitch users, for both technical and cost considerations.

    Real-time inserts are a problem

    Stitch replicates data to data warehouses, such as Amazon Redshift, Google BigQuery, Snowflake, and Microsoft Azure SQL Data Warehouse, among other destinations. Organizations use these data warehouses as the foundation for their data analytics stacks because data warehouses use columnar datastores to efficiently group together data that analysts might want to retrieve together. But the architectural design that makes them efficient at retrieving data makes them unsuitable for row-oriented updates and inserts typical of databases used for online transaction processing (OLTP).

    The most efficient way to load data into Redshift is with the COPY command, which "allocates the workload to the cluster nodes and performs the load operations in parallel, including sorting the rows and distributing data across node slices," according to Redshift documentation. "You can also add data to your tables using INSERT commands, though it is much less efficient than using COPY." In fact inserting a single row may take as long as appending tens, hundreds, or thousands of records through bulk copies.

    Real-time replication degrades the performance of data warehouses, bogging down data loading and taking up processing resources that could be spent creating reports.

    Thanks to the elastic nature of the cloud, you could spin up more nodes to handle the increased load, but there's no free lunch. You'll have to pay for the additional nodes you'll need to maintain the level of performance you're comfortable with.

    The right rate of replication

    As we've worked with the thousands of businesses that use Stitch every day, we've come to understand their priorities. We've found that 15 to 20 minutes' latency is the right tradeoff to optimize data warehouse performance and still get maximum value from analytics, so we've optimized Stitch for that. By doing this, we avoid degrading customers' data warehouse performance and incurring unnecessary costs on their behalf.

    Data analytics isn't a race in which the winner is the first to finish replicating data. It's tempting to think that the most up-to-date data will give you the best BI — but that isn't necessarily true. Think about how your company uses data analytics. If it's to give managers the information they need to make better decisions, it doesn't make sense to update your BI dashboards faster than the human brain can process.

    For most organizations and for most use cases, data that's a few minutes old is sufficiently up to date. How rapidly are your people and your systems making decisions? For most use cases it's a minimum of several minutes, and it may be hours or days.

    That said, some use cases do benefit from near-real-time loading of replicated data. If you run a chatbot to automate answers about a customer's inquiries, it needs to know the context of the customer's most recent interaction, so an appropriate level of latency might be a few seconds. If you're analyzing data from a heart monitoring implant, you might want no more than a second's latency. If you're doing algorithmic trading in the financial markets, you'll want up-to-the-microsecond pricing information. Stitch isn't be the optimal tool for these use cases.

    Whether you need real-time latency depends on what do you need to do with your data. Whether it's cost-effective depends on what you pay to support your use case. Stitch provides an efficient, cost-effective balance between latency and load. Take advantage of a free trial of Stitch and see for yourself.

    Image credit: JamesPaulT
    <![CDATA[We’re eliminating overages and introducing new pricing]]>https://www.stitchdata.com/blog/eliminating-overages-and-introducing-new-pricing/5c98e8b26b6d7800bfaac192Tue, 26 Mar 2019 17:31:54 GMTWe’re eliminating overages and introducing new pricing

    We're excited to announce new Stitch pricing that addresses common feedback we've received from our customers.

    If you're currently a Stitch customer, you can remain on your existing plan for up to six months. However, we expect that most of our customers will want to change sooner than that, because our new pricing eliminates overage fees. This removes uncertainty around the monthly bill for Stitch, and we're confident it will be a big win for everyone who uses Stitch.

    We previously introduced overages to allow for a gradual shift from one plan to another, and our new plans solve for that with a larger number of options separated by smaller gaps.

    Our new pricing plans are available today. All of the plans include access to all of our paid integrations (like Salesforce, MongoDB, and Xero), live chat support with our amazing support team, and seven days of free replication for each integration added. And as before, we'll give you a two-month discount for choosing an Annual plan.

    You can still use Stitch for free for up to 5 million rows a month using any of the integrations labeled as free on our Sources page.

    If you're not yet using Stitch, sign up today and give it a try.

    Image credit: Colin Woodcock
    <![CDATA[The Stitch 30: Fastest-growing cloud applications]]>https://www.stitchdata.com/blog/the-stitch-30-fastest-growing-cloud-applications/5c8141c26b6d7800bfaac171Tue, 12 Mar 2019 13:01:00 GMTThe Stitch 30: Fastest-growing cloud applications

    Thousands of companies use Stitch to replicate data from more than 90 different applications, resulting in tens of thousands of connections. This gives us a unique view into the evolution of the software landscape.

    Our customer base is not a completely random sample. These businesses are integrating data from disparate data sources into modern cloud data warehouses and data lakes to power their analytics. These data-driven companies are ahead of their peers, so our ranking is best thought of as a leading indicator of cloud application growth. Additionally, Stitch itself is growing quickly, which impacts the data.

    We used the year over year growth in connections to each data source among our users as a proxy for growth. For applications Stitch has supported for less than a year, we looked at the compound annual growth rate (CAGR) based on the months for which we had data. Finally, we looked at the growth in data volume per connection as a proxy for the growth of the end users of each application.

    Fastest-growing applications

    Among applications whose integrations were available for all of 2018, these grew fastest over the prior year:

    Rank Application Growth rate
    1 Google Ads 453%
    2 Google Cloud SQL 360%
    3 Microsoft SQL Server 304%
    4 Harvest 293%
    5 Branch 261%
    6 Quickbooks 260%
    7 Shopify 245%
    8 Zuora 225%
    9 Freshdesk 211%
    10 Jira 190%

    Google Ads is a powerhouse — in addition to being the fastest-growing application, it's also our third-most popular overall. Google's ad revenue has been on a rocketship trajectory for more than a decade, and its use among Stitch customers is growing even faster.

    Google also occupied the second fastest-growing spot with Cloud SQL. Google Cloud Platform is still a minority of Google's business and smaller than Amazon Web Services and Microsoft Azure, but it's growing fast.

    Several of the fastest growing companies had big financial and fundraising milestones in 2018. Branch and Freshworks, the company behind Freshdesk, both raised a large rounds of funding that valued the companies at over $1 billion. Shopify crossed the $1 billion revenue mark faster than any SaaS company ever, and Zuora went public in April.

    The most impressive performance was by Harvest, the only private, self-funded company whose application is on our fastest-growing list.

    Hottest new integrations

    We released dozens of new integrations in 2018, and these got off to the fastest starts since launch:

    Rank Integration CAGR
    1 S3 91%
    2 Yotpo 48%
    3 Mailjet 40%
    4 Campaign Monitor 38%
    5 GitHub 29%
    6 Bronto 28%
    7 Heap 26%
    8 Quick Base 12%
    9 Particle 9%
    10 Google Campaign Manager 4%

    Amazon S3 tops our list, which is no surprise given the popularity of AWS. It's worth noting that this 91% growth is for S3 as a data source, not as a destination, in keeping with all of the other applications in this post. However, S3 grew even faster as a destination, with 14x year-over-year growth.

    Email marketing applications Mailjet and Campaign Monitor took the third and fourth spots on this list with similar growth numbers. Campaign Monitor sent more than 3 million email campaigns on customers' behalf in 2018, and added live image editing capabilities to its email builder tool.

    Heap last year cracked Search Engine Watch's list of top 10 tools for getting an insight into your website analytics, and announced a partnership with Snowflake as a destination for Heap Connect.

    Fastest-growing customers

    These applications are used by the fastest growing companies in our customer base. We used data volume per connection as a proxy for the growth of individual customers.

    Here's an example: Let's assume the average HubSpot customer has increased the volume of data they are replicating from HubSpot by 80% year over year, and the average Marketo customer has increased data replicated from Marketo by 60%. We would infer that HubSpot customers are growing faster on average that Marketo customers.

    This approach helps to normalize across different kinds of data sources that produce differing amounts of data and zeroes in on the rate of change.

    Rank Application Increase vs. previous year
    1 Vero 2,599%
    2 Drip 604%
    3 GitLab 338%
    4 AppsFlyer 205%
    5 Iterable 126%
    6 Sendwithus 66%
    7 SparkPost 45%
    8 Microsoft Azure Database 44%
    9 AfterShip 44%
    10 Close 37%

    Several of the applications with the fastest-growing customers are in related spaces, including Vero, Drip, Iterable, and Sendwithus. It's no surprise to see that next-generation tools driving more intelligent marketing and customer engagement have fast-growing customers.

    Vero added workflows to its email messaging platform, and its how-to guides help users leverage data to grow faster. Drip introduced a Visual Email Builder in July, which coincided with the beginning of a big jump in growth in joint customers with Stitch. Iterable launched the next generation of their platform that leverages AI for better personalization

    Developer collaboration company GitLab saw huge growth as a result of Microsoft's decision in June to buy rival GitHub. It shows up in the numbers that they shared publicly as well as usage among Stitch customers.

    Mobile attribution vendor AppsFlyer surpassed $100 million in annual recurring revenue, and has been doubling in size each year.

    The upshot

    We saw growth across almost all of the applications with which we integrate. Our most successful customers are continually finding new ways add value to their customers and grow their business, and that means more data to analyze.

    If you're not analyzing data from all of your data sources together in context, try Stitch for free to bring it all together into a data warehouse, and join the ranks of our rapidly growing customers.

    Image credit: DaniRodriguesLS
    <![CDATA[The vision behind Talend's acquisition of Stitch]]>https://www.stitchdata.com/blog/the-vision-behind-talends-acquisition-of-stitch/5c816f336b6d7800bfaac174Thu, 07 Mar 2019 19:35:28 GMTThe vision behind Talend's acquisition of Stitch

    In November, we announced that Stitch had been acquired by Talend. I'm grateful for the positive messages and support we received from our customers, our partners, and our team in the wake of that announcement. In addition to good vibes, we also got a lot of questions. The most common, including a comment on that original blog post, is how we see the Stitch and Talend products working together in the future. The combination is a big win for our customers; understanding how requires explaining the vision that led to the acquisition itself.

    Our strategy at Stitch has always been to land and expand. We want to deliver value to our users as quickly as possible and then grow that value over time. This is the right thing to do for our customers, and it's also in our financial interest. Delivering value upfront improves our conversion rates, and the best way to grow our revenue over time is by growing the value that our customers derive from our product.

    This land and expand strategy is why the call to action at the top of every page on the Stitch website is not to talk to sales, or to get a demo, but to try the product, for free, right now. It's also why we have a free-forever tier, an unlimited two-week free trial, and paid plans that start at $100 per month.

    We launched Stitch because we saw a need for a new kind of data pipeline. As the world was changing from on-premises to cloud data warehouses, the requirements for getting data into data warehouses were changing as well. Traditional ETL (extract, transform, load) was the only realistic option in an on-premises environment. The move to the cloud enabled many changes, foremost among them the ability to rapidly scale your data warehouse up and down, and do the transformation post load — in other words, ELT.

    We focused on extraction and loading (also known as ingestion), which enabled us to deliver dramatically faster times to value than had been possible before. However, focusing on one part of the problem doesn't mean that the rest of the problem went away. Once you extract and load your data, it still needs to be transformed, and the data journey doesn't end with transformation. Businesses need to do many other things to fully realize the value of data.

    We land with ingestion, and our long-term vision has been to expand with additional products and services to help realize that value. For a lot of reasons, the Talend acquisition was a win for the Stitch team, customers, and product, and the most important is that it accelerates the realization of that vision.

    Talend already helps users along that data journey by providing data transformation, data sharing, data governance, and more. That's why we changed up the acronym a bit. When we say ELT, we now mean extract, load, Talend.*

    ELT now means extract, load, Talend

    It has been gratifying to see the combined Stitch and Talend vision resonate with early joint customers and prospects. While Stitch remains an independent business unit, our team is working closely with the rest of Talend to refine the joint user experience.

    Our vision remains the same. We'll continue make it faster and easier for you to ingest your data, and with Talend's help we'll deliver more value by adding transformation, governance, and sharing to our ingestion. I'm excited about what this will mean for our customers, our business, and our team. If you'd like to take the first step on that data journey with us, try Stitch for free today.

    * — first coined by Shaun McAvinney, director of sales at Stitch

    Image credit: Meggan
    <![CDATA[Join us for Snowflake Data for Breakfast]]>https://www.stitchdata.com/blog/join-us-for-snowflake-data-for-breakfast/5c6588dcdc1c2100bf0660e9Tue, 19 Feb 2019 14:01:00 GMTJoin us for Snowflake Data for Breakfast

    Throughout March, Stitch and Talend will be participating in the Snowflake Data for Breakfast tour. We'd love to have you join us.

    Snowflake is a cloud data warehouse — you can use Stitch to replicate data from more than 90 sources into Snowflake. This tour aims to show organizations how they can share and consume live data across teams, partners, and clouds.

    Breakfast is the headline, and yes, we'll feed you, but the main attractions are even tastier. You'll hear from Snowflake about how to make your organization more data-driven. You'll hear from a Snowflake customer and their experiences. And you'll hear from us — Talend is presenting at the event in 11 cities, including Philadelphia, where attendees can hear from and meet Stitch co-founder Jake Stein. Each stop on the tour will have a joint demo presented by Stitch and Looker. After a Q&A panel with all the presenters, you'll have a chance to meet and compare notes with other attendees. You can then stay for a Zero to Snowflake workshop, where Snowflake will walk you through the technical process of getting started.

    Here's the calendar of events. Click on a city to learn more and to register.

    March 6: Salt Lake City
    March 7: Nashville
    March 7: Phoenix
    March 12: Denver
    March 13: Detroit
    March 13: Philadelphia
    March 14: Minneapolis
    March 19: Calgary
    March 20: Portland
    March 26: Montreal
    March 28: Oklahoma City

    <![CDATA[Microsoft SQL Server, Azure SQL Database, and Azure SQL Data Warehouse: What's the difference?]]>https://www.stitchdata.com/blog/microsoft-sql-server-azure-sql-database-and-azure-sql-data-warehouse-whats-the-difference/5c5b3fd97cc26c00bf5714a0Tue, 12 Feb 2019 14:01:00 GMTMicrosoft SQL Server, Azure SQL Database, and Azure SQL Data Warehouse: What's the difference?

    Microsoft has so many database products with SQL in their names that it's not hard to understand why some people get them confused. Do you know the difference between:

    The tl;dr is that SQL Server can be an on-premises enterprise database, Azure SQL Database is a cloud-hosted enterprise database, and Azure SQL Data Warehouse is a cloud-hosted data warehouse. But there's more to the story.

    A deeper dive

    All of these platforms have some history behind them.

    SQL Server was Microsoft's first enterprise database. It debuted in 1989 for OS/2, an ill-fated operating system collaboration between Microsoft and IBM. Microsoft ported SQL Server to Windows NT when that operating system was introduced in 1993. The most current version is SQL Server 2019. It runs atop Windows Server and Linux operating systems, either on local servers or on cloud instances.

    On-premises servers were the standard way organizations ran most of their software until about 2006, when Amazon launched Amazon Web Services (AWS) and gave birth to the cloud computing revolution. Amazon began offering a relational database on AWS in 2009. Amazon Relational Database Service (RDS) was a SaaS alternative to local databases like SQL Server, or to running those databases on Amazon Elastic Compute Cloud (EC2). There's a version of RDS for SQL Server.

    Meanwhile, Microsoft began working on its own cloud platform, Windows Azure, which it introduced it in 2010. (The company changed the name to Microsoft Azure in 2014.) Along with cloud storage, Microsoft offered a cloud-hosted version of SQL Server that it called SQL Azure, which it renamed Azure SQL Database in 2012. The current version of Azure SQL Database shares the SQL Server 2016 codebase.

    Amazon, meanwhile, continued developing its cloud platform. In 2013 it launched Redshift, a column-oriented database designed to support analytics queries. In 2015, Microsoft countered with Azure SQL Data Warehouse, its own enterprise data warehouse.

    The competition

    Today each of these databases competes against several prominent alternatives:

    Microsoft database Comparable products and platforms
    On-premises relational database SQL Server Oracle Database, IBM Db2, SAP Sybase ASE, PostgreSQL, MySQL, MariaDB
    Cloud RDBMS Azure SQL Database Amazon RDS, Amazon Aurora, Google Cloud SQL, Heroku Postgres, EnterpriseDB Postgres, Oracle Database Cloud Service
    Cloud data warehouse Azure SQL Data Warehouse Amazon Redshift, Google BigQuery, Snowflake, IBM Db2 Warehouse on Cloud

    Stitch supports Azure SQL Data Warehouse as a destination. Demand for an Azure data warehouse alternative has grown along with the platform's strong and steady growth. Stitch's acquisition by Talend last year, and the close partnership between Talend and Microsoft, also made support for Azure SQL Data Warehouse a natural move.

    If you're using Azure SQL Data Warehouse, sign up for Stitch and begin replicating your data sources to your data warehouse in minutes. New users get a free 14-day trial, during which you can move an unlimited amount of data from more than 90 data sources, including popular platforms such as Google Analytics and Google Ads, Shopify, Salesforce, and Stripe.

    Image credit: Jason Mrachina, Yiuamwbxn