Extract, Transform, and Load (ETL) pipelines are widely used to ingest data into Enterprise Data Warehouse (EDW) systems. These pipelines can be very complex and often tightly coupled to a given EDW, making it challenging to upgrade from a legacy EDW to a Cloud Data Warehouse (CDW). This paper presents a novel solution for a transparent and fully-automated porting of legacy ETL pipelines to CDW environments.

Performing a detailed workload analysis is a crucial step in determining the feasibility, timeline and cost of a major data warehouse replatforming project, i.e., migration from one platform to another. A large company's data warehouse applications may include millions of queries, some of which will use features that are unsupported or have different semantics in the new warehouse, or may have poor performance there. In this paper we present q Insight, a workload analyzer that Datometry has used in data warehouse replatforming efforts for dozens of major clients. qInsight leverages Datometry's Hyper-Q to obtain insights from a workload, including SQL features and workload structural information that could not be obtained without deep query analysis. qInsight uses the identified features and a weighting scheme based on human expert judgments to assess the difficulty of rewriting each application in the workload via traditional migration methods. Datometry's clients find this information useful in planning their projects, including the order in which to migrate applications. We present a q Insight-based data warehouse usage analysis of over 1.7 billion queries from real-world workloads.

In recent years, increased interest in cloud-based data warehousing technologies has emerged with many enterprises moving away from on-premise data warehousing solutions. The incentives for adopting cloud data warehousing technologies are many: cost-cutting, on-demand pricing, offloading data centers, unlimited hardware resources, built-in disaster recovery, to name a few. There is inherent difference in the language surface and feature sets of on-premise and cloud data warehousing solutions. This could range from subtle syntactic and semantic differences, with potentially big impact on result correctness, to complete features that exist in one system but are missing in other systems. While there have been some efforts to help automate the migration of on-premise applications to new cloud environments, a major challenge that slows down the migration pace is the handling of features not yet supported, or partially supported, by the cloud technologies. In this paper we build on our earlier work in adaptive data virtualization and present novel techniques that allow running applications utilizing sophisticated database features within foreign query engines lacking the native support of such features. In particular, we introduce a framework to manage discrepancy of metadata across heterogeneous query engines, and various mechanisms to emulate database applications code in cloud environments without any need to rewrite or change the application code.

The database industry is about to undergo a fundamental transformation of unprecedented magnitude as enterprises start trading their well-established database stacks on premises for cloud database technology in order to take advantage of the economics cloud service providers have long promised. Industry experts and analysts expect the next years to prove a watershed moment in this transformation, as cloud databases finally reached critical mass and maturity. Enterprises eager to move to the cloud face a significant dilemma: while moving the content of their databases to the cloud is a well-studied problem, making existing applications work with new database platforms is an enormously costly undertaking that calls for rewriting and adjusting of 100's if not 1,000's of applications. In this paper, we present a next-generation virtualization technology that lets existing applications run natively on cloud-based database systems. Using this platform, enterprises can move rapidly to the cloud and innovate and create competitive advantage as a matter of months instead of years. We describe technology and application scenarios and demonstrate effectiveness and performance of the approach through actual customer use cases.

Partitioning of tables based on value ranges provides a powerful mechanism to organize tables in database systems. In the context of data warehousing and large-scale data analysis partitioned tables are of particular interest as the nature of queries favors scanning large swaths of data. In this scenario, eliminating partitions from a query plan that contain data not relevant to answering a given query can represent substantial performance improvements. Dealing with partitioned tables in query optimization has attracted significant attention recently, yet, a number of challenges unique to Massively Parallel Processing (MPP) databases and their distributed nature remain unresolved. In this paper, we present optimization techniques for queries over partitioned tables as implemented in Pivotal Greenplum Database. We present a concise and unified representation for partitioned tables and devise optimization techniques to generate query plans that can defer decisions on accessing certain partitions to query run-time. We demonstrate, the resulting query plans distinctly outperform conventional query plans in a variety of scenarios.

The performance of analytical query processing in data management systems depends primarily on the capabilities of the system's query optimizer. Increased data volumes and heightened interest in processing complex analytical queries have prompted Pivotal to build a new query optimizer.In this paper we present the architecture of Orca, the new query optimizer for all Pivotal data management products, including Pivotal Greenplum Database and Pivotal HAWQ. Orca is a comprehensive development uniting state-of-the-art query optimization technology with own original research resulting in a modular and portable optimizer architecture.In addition to describing the overall architecture, we highlight several unique features and present performance comparisons against other systems.

The performance of analytical query processing in data management systems depends primarily on the capabilities of the system’s query optimizer. Increased data volumes and heightened interest in processing complex analytical queries have prompted Pivotal to build a new query optimizer. In this paper we present the architecture of Orca, the new query optimizer for all Pivotal data management products, including Pivotal Greenplum Database and Pivotal HAWQ. Orca is a comprehensive development uniting state-of-theart query optimization technology with own original research resulting in a modular and portable optimizer architecture. In addition to describing the overall architecture, we highlight several unique features and present performance comparisons against other systems.

Recurring queries or partial queries occur very frequently in production workloads. Reusing results or intermediates presents a highly intuitive opportunity for performance enhancements and has been explored to various degrees. Strategies suggested so far in the literature depend largely on speculative materialization of results in the hopes that they can be reused later on. However, a materialization is costly and conventional strategies run the risk that the initial investment cannot be amortized reliably unless the exact composition of the workload is known to the strategy a priori.

Online or real-time BI has remained elusive despite significant efforts by academic and industrial research. Some of the most prominent problems in accomplishing faster turnaround are related to the data ingest. The process of extracting data from source systems, transforming, and loading (ETL) it is often bottlenecked by architectural choices and fragmentation of the processing chain.In this paper, we present a vision for a resource-efficient infrastructure that enables just-in-time processing with regards to data ingest. At the heart of our approach are (1) the converting of compute intensive parts of the ETL process into in-database processing and (2) the activating of the process on demand via a system of flexible views.Our approach avoids processing of data that is not being accessed any time soon, scales effectively with the database system and avoids administration and management overhead.

In a typical BI infrastructure, data, extracted from operational data sources, is transformed, cleansed, and loaded into a data warehouse by a periodic ETL process, typically executed on a nightly basis, i.e., a full day's worth of data is processed and loaded during off-hours. However, it is desirable to have fresher data for business insights at near real-time. To this end, the authors propose to leverage a data warehouse's capability to directly import raw, unprocessed records and defer the transformation and data cleaning until needed by pending reports. At that time, the database's own processing mechanisms can be deployed to process the data on-demand. Event-processing capabilities are seamlessly woven into our proposed architecture. Besides outlining an overall architecture, the authors also developed a roadmap for implementing a complete prototype using conventional database technology in the form of hierarchical materialized views.

Query optimizers are among the most complex software components in any database system and are naturally prone to contain software defects. Despite significant efforts in quality assurance, customers occasionally encounter unexpected errors in production systems. A self-contained repro of the problem is often the best approach toward a speedy resolution of the issue. However, repros are notoriously difficult to obtain as they require schema definition, the offending query, and potentially many other pieces of data that are difficult to capture in a consistent and accurate way. As a result, query optimizer issues have a reputation of being hard to tackle and requiring dedicated resources and exceedingly long turnaround time to provide solution to the customer. In this paper we present AMPERe, a mechanism to automatically secure fully self-contained bug repros, as implemented in the optimizer of Greenplum Database. Raising an internal error or run-time assertion automatically triggers the generation of AMPERe-dumps. Similar in nature to error reports of operating systems, AMPERe goes beyond such tools as it delivers a complete minimal repro that allows replaying the problem instantly in isolation on any lab machine. We present the overall architecture of this framework and report on initial experiences with AMPERe as part of Greenplum's regular software development practices.