Point-In-Time Recovery (PITR) is a critical capability for enterprise applications.  It allows database administrators to recover from accidental data deletion by restoring their production databases to a time before the incident.  

Cloud SQL for PostgreSQL launched support for PITR in July 2020, allowing you to recover from disasters like data corruption or accidental deletion by restoring your Cloud SQL instance to a previous time. We’re excited to announce an additional enhancement to PITR for Cloud SQL for PostgreSQL that makes enabling PITR an even easier decision: for instances with Point-in-Time Recovery newly-enabled, the write-ahead logs being stored for PITR operations (which are the transaction logs that are used to go back in time) will no longer consume disk storage space.  Instead, when you enable PITR for new instances, Cloud SQL will store transaction logs collected during the retention window in Google Cloud Storage, and retrieve them when you perform a restore.  Because transaction logs can grow rapidly when your database experiences a burst of activity, this move will help reduce the impact these bursts have on your provisioned disk storage.  These logs will be stored for up to seven days in the same Google Cloud region as your instance at no additional cost to you.  

PITR is enabled by default when you create a new Cloud SQL for PostgreSQL instance from the Google Cloud console, and transaction logs will no longer be stored on the instance for instances that have PITR newly enabled.  If you have already enabled PITR on your PostgreSQL instances, this enhancement will be rolled out to your instances at a later point.  If you want to take advantage of this change sooner, you can first disable and then re-enable PITR on your instance (which will reset your ability to perform a point-in-time restore to the time at which PITR was re-enabled).  On instances with this feature enabled, you’ll notice that consumed storage on your instance will reduce relative to the volume of write-ahead logs (WAL) generated by your instance.  The actual amount of storage your logs consume will vary by instance and by database activity – during busy times for your database, log size may shrink or grow.  However, these logs will now only be stored on your instance long enough to successfully replicate to any replicas of the instance and to ensure that they are safely written to Cloud Storage; afterwards, they will be removed from your instance.

We’re excited to continue to enhance Cloud SQL for PostgreSQL to ensure that disaster recovery is easy to enable, cost effective, and seamless to use.  Learn more about this change in our documentation.

Digitec Galaxus AG is the biggest online retailer in Switzerland, operating two online stores: Digitec, Switzerland’s online market leader for consumer electronics and media products, and Galaxus, the largest Swiss online shop with a steadily growing range of consistently low-priced products for almost all daily needs. 

Known for its efficient, personalized shopping experiences, it’s clear that Digitec Galaxus understands what it takes to deliver a platform that is interesting and relevant to customers every time they shop. 

The problem: Personalizing decisions for every situation

Digitec Galaxus already had established an engine to help them personalize experiences for shoppers when they reached out to Google Cloud. They had multiple recommendation systems in place and were also extensive early adopters of Recommendations AI, which already enabled them to offer personalized content in places like their homepages, product detail pages, and their newsletter. 

But those same systems sometimes made it difficult to understand how best to combine and optimize to create the most personalized experiences for their shoppers. Their requirements were threefold:

1. Personalization: They have over 12 recommenders they can display on the app, however they would like to contextualize this and choose different recommenders (which in turn select the items) for different users. Furthermore they would like to exploit existing trends as well as experiment with new ones.
2. Latency: They would like to ensure that the solution is architected so that the ranked list of recommenders can be retrieved with sub 50 ms latency.
3. End-to-end easy to maintain & generalizable/modular architecture: Digitec wanted the solution to be architected using an easy to maintain, open source stack, complete with all MLops capabilities required to train and use contextual bandits models. It was also important to them that it is built in a modular fashion such that it can be adapted easily to other use cases which have in mind such as recommendations on the homepage, Smartags and more . 

To improve, they asked us to help them implement a machine learning (ML) contextual bandit based recommender system on Google Cloud taking all the above factors into consideration to take their personalization to the next level. 

Contextual bandits algorithms are a simplified form of reinforcement learning and help aid real-world decision making by factoring in additional information about the visitor (context) to help learn what is most engaging for each individual. They also excel at exploiting trends which work well, as well as exploring new untested trends which can yield potentially even better results. For instance, imagine that you are personalizing a homepage image where you could show a comfy living room couch or pet supplies. 

Without a contextual bandit algorithm, one of these images would be shown to someone at random without considering information you may have observed about them during previous visits. Contextual bandits enable businesses to consider outside context, such as previously visited pages or other purchases, and then observe the final outcome (a click on the image) to help determine what works best. 

Creating a personalization system with contextual bandits

While Digitec Galaxus heavily personalizes their website homepages, they are very very sensitive and also require more cross-team collaboration to update and make changes. 

Together with the Digitec Galaxus team, we decided to narrow the scope and focus on building a contextual bandit personalization system for the newsletter first. The digitec Galaxus team has complete control over newsletter decisions and testing various ML experiments on a newsletter would have less chance of adverse revenue impact than a website homepage. 

The main goal was to architect a system that could be easily ported over to the homepage and other services offered by Digitec with minimal adaptations. It would also need to satisfy the functional and non-functional requirements of the homepage as well as other internal use cases.

Below is a diagram of how the newsletter’s personalization recommendation system works:

• The system is given some context features about the newsletter subscriber such as their purchase history and demographics. Features are sometimes referred to as variables or attributes, and can vary widely depending on what data is being analyzed. 
• The contextual bandit model trains recommendations using those context features and 12 available recommenders (potential actions). 
• The model then calculates which action is most likely to enhance the chance of reward (a user clicking in the newsletter) and also minimize the problem (an unsubscribe). 

Calculating whether a click was a newsletter or an unsubscribe enabled the system to optimize for increasing clicks and avoid showing non-relevant content to the user (click-bait). This enabled Digitec Galaxus to exploit popular trends while also exploring potentially better-performing trends. 

How Google Cloud helps

The newsletter context-driven personalization system was built on Google Cloud architecture using the ML recommendation training and prediction solutions available within our ecosystem. 

Below is a diagram of the high-level architecture used:

The architecture covers three phases of generating context-driven ML predictions, including: 

ML Development: Designing and building the ML models and pipeline 
Vertex Notebooks are used as data science environments for experimentation and prototyping. Notebooks are also used to implement model training, scoring components, and pipelines. The source code is version controlled in Github. A continuous integration (CI) pipeline is set up to automatically run unit tests, build pipeline components, and store the container images to Cloud Container Registry. 

ML Training: Large-scale training and storing of ML models 
The training pipeline is executed on Vertex Pipelines. In essence, the pipeline trains the model using new training data extracted from BigQuery and produces a trained, validated contextual bandit model stored in the model registry. In our system, the model registry is a curated Cloud Storage. 

The training pipeline uses Dataflow for large scale data extraction, validation, processing, and model evaluation, and Vertex Training for large-scale distributed training of the model. AI Platform Pipelines also stores artifacts, the output of training models, produced by the various pipeline steps to Cloud Storage. Information about these artifacts are then stored in an ML metadata database in Cloud SQL. To learn more about how to build a Continuous Training Pipeline, read the documentation guide.

ML Serving: Deploying new algorithms and experiments in production 
The training pipeline uses batch prediction to generate many predictions at once using AI Platform Pipelines, allowing Digitec Galaxus to score large data sets. Once the predictions are produced, they are stored in Cloud Datastore for consumption. The pipeline uses the most recent contextual bandit model in the model registry to evaluate the inference dataset in BigQuery and give a ranked list of the best newsletters for each user, and persist it in Datastore. A Cloud Function is provided as a REST/HTTP endpoint to retrieve the precomputed predictions from Datastore.

All components of the code and architecture are modular and easy to use, which means they can be adapted and tweaked to several other use cases within the company as well.

Better newsletter predictions for millions

The newsletter prediction system was first deployed in production in February, and Digitec Galaxus has been using it to personalize over 2 million newsletters a week for subscribers. The results have been impressive, 50% higher than our baseline. However, the collaboration is still ongoing to improve the results even more. 

“Working at this level in direct exchange with Google’s machine learning experts is a unique opportunity for us. The use of contextual bandits in the targeting of our recommendations enables us to pursue completely new approaches in personalization by also personalizing the delivery of the respective recommender to the user. We have already achieved good results in our newsletter in initial experiments and are now working on extending the approach to the entire newsletter by including more contextual data about the bandits arms. Furthermore, as a next step, we intend to apply the system to our online store as well, in order to provide our users with an even more personalized experience. To build this scalable solution, we are using Google’s open source tools such as TFX and TF Agents, as well as Google Cloud Services such as Compute Engine, Cloud Machine Learning Engine, Kubernetes Engine and Cloud Dataflow.”—Christian Sager, Product Owner, Personalization ( Digitec Galaxus)

Since the existing architecture and system is also dynamic, it will automatically adapt to new behaviours, trends, and users. As a result, Digitec Galaxus plans to re-use the same components and extend the existing system to help them improve the personalization of their homepage and other current use cases they have within the company. Beyond clicks and user engagement, the system’s flexibility also allows for future optimization of other criteria. It’s a very exciting time and we can’t wait to see what they build next!

Businesses around the globe are realizing the benefits of replacing legacy data silos with cloud-based enterprise data warehouses, including easier collaboration across business units and access to insights within their data that were previously unseen. However, bringing data from numerous disparate data sources into a single data warehouse requires you to develop pipelines that ingest data from these various sources into your enterprise data warehouse. Historically, this has meant that data engineering teams across the organization procure and implement various tools to do so. But this adds significant complexity to managing and maintaining all these pipelines and makes it much harder to effectively scale these efforts across the organization. Developing enterprise-grade, cloud-native pipelines to bring data into your data warehouse can alleviate many of these challenges. But, if done incorrectly, these pipelines can present new challenges that your teams will have to spend their time and energy addressing. 

Developing cloud-based data ingestion pipelines that replicate data from various sources into your cloud data warehouse can be a massive undertaking that requires significant investment of staffing resources. Such a large project can seem overwhelming and it can be difficult to identify where to begin planning such a project. We have defined the following principles for data pipeline planning to begin the process. These principles are intended to help you answer key business questions about your effort and begin to build data pipelines that address your business and technical needs. Each section below details a principle of data pipelines and certain factors your teams should consider as they begin developing their pipelines.

Principle 1: Clarify your objectives

The first principle to consider for pipeline development is clarify your objectives. This can be broadly defined as taking a holistic approach to pipeline development that encompasses requirements from several perspectives: technical teams, regulatory or policy requirements, desired outcomes, business goals, key timelines, available teams and their skill sets, and downstream data users. Clarifying your objectives clearly identifies and defines requirements from each key stakeholder at the beginning of the process and continually checks development against these requirements to ensure the pipelines built will meet these requirements.This is done by first clearly defining the desired end state for each project in a way that addresses a demonstrated business need of downstream data users. Remember that data pipelines are almost always the means to accomplish your end state, rather than the end state itself. An example of an effectively defined end-state is “enabling teams to gain a better understanding of our customers by providing access to our CRM data within our cloud data warehouse” rather than “move data from our CRM to our cloud data warehouse”. This may seem like a merely semantic difference, but framing the problem in terms of business needs helps your teams make technical decisions that will best meet these needs. 

After clearly defining the business problem you are trying to solve, you should facilitate requirement gathering from each stakeholder and use these requirements to guide the technical development and implementation of your ingestion pipelines. We recommend gathering stakeholders from each team, including downstream data users, prior to development to gather requirements for the technical implementation of the data pipeline. These will include critical timelines, uptime requirements, data update frequency, data transformation, DevOps needs, and security, policy, or regulatory requirements by which a data pipeline must meet.

Principle 2: Build your team

The second principle to consider for pipeline development is build your team. This means ensuring you have the right people with the right skills available in the right places to develop, deploy, and maintain your data pipelines. After you have gathered your pipeline requirements, you can begin to develop a summary architecture that will be used to build and deploy your data pipelines. This will help you identify the human talent you will need to successfully build, deploy, and manage these data pipelines and identify any potential shortfalls that would require additional support from either third-party partners or new team members.

Not only do you need to ensure you have the right people and skill sets available in aggregate, but these individuals need to be effectively structured to empower them to maximize their abilities. This means developing team structures that are optimized for each team’s responsibilities and their ability to support adjacent teams as needed.

This also means developing processes that prevent blockers to technical development whenever possible, such as ensuring that teams have all of the appropriate permissions they need to move data from the original source to your cloud data warehouse without violating the concept of least privilege. Developers need access to the original data source (depending on your requirements and architecture) in addition to the destination data warehouse. Examples of this are ensuring that developers have access to develop and/or connect to a Salesforce Connected App or read access to specific Search Ads 360 data fields.

Principle 3: Minimize time to value

The third principle to consider for pipeline development is minimize time to value. This means considering the long-term maintenance burden of a data pipeline prior to developing and deploying it in addition to being able to deploy a minimum viable pipeline as quickly as possible. Generally speaking, we recommend the following approach to building data pipelines to minimize their maintenance burden: Write as little code as possible. Functionally, this can be implemented by:

1. Leveraging interface-based data ingestion products whenever possible. These products minimize the amount of code that requires ongoing maintenance and empower users who aren’t software developers to build data pipelines. They can also reduce development time for data pipelines, allowing them to be deployed and updated more quickly. 

• Products like Google Data Transfer Service and Fivetran allow for managed data ingestion pipelines by any user to centralize data from SaaS applications, databases, file systems, and other tooling. With little to no code required, these managed services enable you to connect your data warehouse to your sources quickly and easily.
• For workloads managed by ETL developers and data engineers, tools like Google Cloud’s Data Fusion provide an easy-to-use visual interface for designing, managing and monitoring advanced pipelines with complex transformations.

2. Whenever interface-based products or data connectors are insufficient, use pre-existing code templates. Examples of this include templates available for Dataflow that allow users to define variables and run pipelines for common data ingestion use cases, and the Public Datasets pipeline architecture that our Datasets team uses for onboarding.

3. If neither of these options are sufficient, utilize managed services to deploy code for your pipelines. Managed services, such as Dataflow or Dataproc, eliminate the operational overhead of managing pipeline configuration by automatically scaling pipeline instances within predefined parameters.

Principle 4: Increase data trust and transparency

The fourth principle to consider for pipeline development is increase data trust and transparency. For the purposes of this document, we define this as the process of overseeing and managing data pipelines across all tools. Numerous data ingestion pipelines that each leverage different tools or are not developed under a coordinated management plan can result in “tech sprawl”, which significantly increases the management overhead of data ingestion pipelines as the quantity of data pipelines increases. This becomes especially cumbersome if you are subject to service-level agreements, or legal, regulatory, or policy requirements for overseeing data pipelines. Preventing tech sprawl is, by far, the best strategy for dealing with it by developing streamlined pipeline management processes that automate reporting. Although this can theoretically be achieved by building all of your data pipelines using a single cloud-based product, we do not recommend doing so because it prevents you from taking advantage of features and cost optimizations that come with choosing the best product for your use case. 

A monitoring service such as Google Cloud Monitoring Service or Splunk that automates metrics, events, and metadata collection from various products, including those hosted in on-premise and hybrid computing environments, can help you centralize reporting and monitoring of your data pipelines. A metadata management tool such as Google Cloud’s Data Catalog or Informatica’s Enterprise Data Catalog can help you better communicate the nuances of your data so users better understand which data resources are best fit for a given use case. This significantly reduces your pipeline’s governance burden by eliminating manual reporting processes that often result in inaccuracies or lagging updates.

Principle 5: Manage costs

The fifth principle to consider for pipeline development is manage costs. This encompasses both the cost of cloud resources and the staffing costs necessary to design, develop, deploy, and maintain your cloud resources. We believe that your goal should not necessarily be to minimize cost, but rather maximizing the value of your investment. This means maximizing the impact of every dollar spent by minimizing waste in cloud resource utilization and human time. There are several factors to consider when it comes to managing costs:

• Use the right tool for the job – Different data ingestion pipelines will have different requirements for latency, uptime, transformations, etc. Similarly, different data pipeline tools have different strengths and weaknesses. Choosing the right tool for each data pipeline can help your pipelines operate significantly more efficiently. This can reduce your overall cost, free up staffing time to focus on the most impactful projects, and make your pipelines much more efficient.
• Standardize resource labeling –  Implement and utilize a consistent labeling schema across all tools and platforms to have the most comprehensive view of your organization’s spending. One example is requiring all resources to be labeled by the cost center or team at time of creation. Consistent labeling allows you to monitor your spend across different teams and calculate the overall value of your cloud spending.
• Implement cost controls – If available, leverage cost controls to prevent errors that result in unexpectedly large bills. 
• Capture cloud spend – Capture your spend on all cloud resource utilization for internal analysis using a cloud data warehouse and a data visualization tool. Without it, you won’t understand the context of changes in cloud spend and how they correlate with changes in business.
• Make cost management everyone’s job – Managing costs should be part of the responsibilities of everyone who can create or utilize cloud resources. To do this well, we recommend making cloud spend reporting more transparent internally and/or implementing chargebacks to internal cost centers based on utilization.

Long-term, the increased granularity in cost reporting available within Google Cloud can help you better measure your key performance indicators. You can shift from cost-based reporting (i.e. – “We spent $X on BigQuery storage last month”) to value-based reporting (i.e. – “It costs $X to serve customers who bring in $Y revenue”). 

To learn more about managing costs, check out Google Cloud’s “Understanding the principles of cost optimization” white paper.

Principle 6: Leverage continually improving services

The sixth principle is leverage continually improving services. Cloud services are consistently improving their performance and stability, even if some of these improvements are not obvious to users. These improvements can help your pipelines run faster, cheaper, and more consistently over time. You can take advantage of the benefits of these improvements by:

• Automating both your pipelines and pipeline management: Not only should data pipelines be automated, but almost all aspects of managing your pipelines can also be automated. This includes pipeline/data lineage tracking, monitoring, cost management, scheduling, access management and more. This helps reduce long-term operational costs of each data pipeline that can significantly alter your value proposition and prevent any manual configurations from negating the benefits of later product improvements.
• Minimizing pipeline complexity whenever possible: While ingestion pipelines are relatively easy to develop using UI-based or managed services, they also require continued maintenance as long as they are in use. The most easily maintained data ingestion pipelines are typically the ones that minimize complexity and leverage automatic optimization capabilities. Any transformation in a data ingestion pipeline is a manual optimization of the pipeline that may struggle to adapt or scale as the underlying services improve. You can minimize the need for such transformations by building ELT (extract, load, transform) pipelines rather than ETL (extract, transform, load) pipelines. This pushes transformations down to the data warehouse that is use a specifically optimized query engine to transform your data rather than manually configured pipelines.

Next steps

If you’re looking for more information about developing your cloud-based data platform, check out our Build a modern, unified analytics data platform whitepaper. You can also visit our data integration site to learn more and find ways to get started with your data integration journey.

Once you’re ready to begin building your data ingestion pipelines, learn more about how Cloud Data Fusion and Fivetran can help you make sure your pipelines address these principles.

Thirteen years’ experience in e-commerce has given Teddy Chan, Chief Executive Officer and Chief Technology Officer, AfterShip, a deep understanding of the challenges of shipping and tracking packages to customers worldwide.

“The key problem many merchants face is customers asking ‘where is my order?’ and ‘when I will get the package?’” Chan says. “When I considered this issue, I came up with the idea of AfterShip.” Chan helped found AfterShip in 2011 to enable merchants to keep track of packages sent to customers via a web portal or an API. AfterShip also allows merchants to notify customers of anticipated delivery times.

“Using AfterShip, merchants can provide the same experience to buyers regardless of which couriers they use,” Chan says. “Merchants can also improve their customer engagement by including up-selling or marketing content with their delivery notifications.”

Hong Kong-headquartered AfterShip continues to grow quickly and now has a 40-person team. Thirty members of this team are based on the China special administrative region and 10 are based in India.

“Google Cloud Platform has a network of global datacentres with deep connectivity that enables us to put our infrastructure close to our customers. In addition, the ability to horizontally scale our global database using tools such as Google Cloud Spanner eliminates any limits on our geographic expansion.”

—Teddy Chan, Chief Executive Officer and Chief Technology Officer, AfterShip

By October 2017, the business was tracking about 30 million packages per month and had expanded its services to include label and rate calculation and self-service return. Revenue, package transaction numbers and team size have doubled every year for the past three years, while more than 300,000 merchants and 426 couriers are signed up to the service. Key customers include Wish, Etsy and Groupon.

Close to half AfterShip’s customers are based in the United States, about one third in Europe and the remainder are located in Asia. AfterShip had initially delivered its applications and services from an incumbent public cloud service. However, the company wanted to continue its growth trajectory while automating key infrastructure processes, implementing a continuous deployment model and controlling costs.

The business needed to achieve these objectives while maintaining a global presence and high-quality service. AfterShip started reviewing its options and decided to migrate to Google Cloud Platform (GCP). “Google Cloud Platform has a network of global datacentres with deep connectivity that enables us to place our infrastructure close to our customers,” Chan says.

“In addition, the ability to horizontally scale our global database using tools such as Google Cloud Spanner eliminates any limits to our geographic expansion. Furthermore, the managed services provided through GCP would allow us to focus on building better features for online merchants.” The reliability provided by GCP would also enable AfterShip to meet the stringent service level requirements of large digital marketplaces in the United States, Asia and elsewhere.

“Google Cloud Platform could manage the high volumes and enable us to deliver the service levels that would realise our ambition of becoming the number one tracking API platform in the world,” Chan says. “For example, with Google Cloud Platform, we can provide a 99.95% monthly uptime service level to our customers.” Finally, GCP provided managed solutions, including Google Kubernetes Engine powered by open source container orchestrator Kubernetes, that would enable AfterShip to automate processes such as scaling and enable its team to focus on developing applications.

AfterShip has moved its websites into GCP infrastructure in three datacentres around the world and anticipates completing the migration in Q4 2017. “Google provided a lot of assistance, particularly early in the project when we needed it,” Chan says. “They briefed us on several services we hadn’t known about that could replace the equivalents in the public cloud we were using previously.” The business then completed the migration using its own skilled team members. As well as Google Kubernetes Engine and Google Cloud Spanner, AfterShip is using Google BigQuery to store and analyse transaction information.

“Google Cloud Platform could manage the high volumes and enable us to deliver the service levels that would realise our ambition of becoming the number one tracking API platform in the world.”

—Teddy Chan, Chief Executive Officer and Chief Technology Officer, AfterShip

Deployment times down from one hour to two minutes

With deployment times falling from up to one hour in its previous cloud environment to about two minutes in GCP, AfterShip has been able to adopt a continuous deployment model. “This has improved our service levels,” Chan says. “If there are any issues we can fix them quickly, while we can iterate faster to create new features in response to customer requests or changes in the market. “This enables us to continue to lead our competitors.”

Targeting a 30 percent reduction in costs

AfterShip is now targeting a 30% reduction in costs by optimising its use of Docker containerisation technology on GCP.

“By using Docker with Kubernetes, we have been able to fine-tune our use of compute resources and better control our costs,” Chan says. “We’re extremely pleased with Google Cloud Platform as it really is built for engineers,” he adds. “In addition, its documentation is extremely clear, allowing us to troubleshoot or carry out activities on the platform ourselves. “We look forward to continuing to grow and extend our package tracking and associated services with Google Cloud Platform.”