Optimizing Enterprise Data Warehouses and Distributed Computing

Data Warehouse / SQL Optimization / Enterprise Data Architecture

Why Integration Performance Depends on Architecture

The growth of corporate information volumes creates new requirements for analytical processing systems.

Enterprises that use multiple ERP, CRM, electronic document management, and specialized application suites must regularly consolidate their data. For this purpose, ETL, ELT, SQL procedures, and distributed computing platforms are used.

However, even the use of productive hardware does not guarantee the efficiency of information processes.

For example, an ETL server may repeatedly read the same ERP table, perform large sorts, and transfer intermediate results over the network. Another process may load information into the DWH, after which it launches SQL queries with inefficient joins that overload the disk subsystem.

In both cases, the main problem may not be related to the number of servers, but to the incorrect placement of transformations.

Therefore, during modernization, it is important to evaluate the entire information architecture: sources, transfer channels, intermediate storage, computations, and final analytical models.

The effectiveness of corporate integration is determined not only by the performance of an individual ETL tool, but also by how correctly computations are distributed among sources, the integration platform, and the target repository.

Three Locations for Executing Information Transformations

In corporate architecture, transformations can be performed at different levels.

In the Source System

Some operations can be performed directly within the ERP or another DBMS.

For example, a source can generate a selection of only changed records. This reduces the amount of information that needs to be transmitted over the network.

However, resource-intensive analytical queries can degrade the performance of a production ERP.

Therefore, it is necessary to limit the load and, if possible, use special replicas or defined processing windows.

At the Integration Level

The ETL platform can ingest data from multiple sources, cleanse it, and perform necessary transformations. This approach allows for centralizing logic and avoiding excessive load on operational systems.

However, the computational capabilities of the integration server can become a bottleneck.

In the target data warehouse

ELT involves using SQL and the computational resources of the DWH. This can be effective for large joins, aggregations, and transformations that are well-supported by the analytical DBMS.

However, competition with user BI queries and the limitations of the specific platform must be considered.

Pushdown Optimization: Moving Operations to the DBMS

One practical approach is Pushdown Optimization. Its essence lies in performing defined transformations using the capabilities of the respective DBMS instead of transferring all records to a separate ETL process.

For example, if it is necessary to select sales only for the current month, it is advisable to perform the appropriate filtering at the source, if this does not create an unacceptable load.

Instead of retrieving the entire historical table, ETL loads only the required records.

Filtering

SELECT
    order_id,
    customer_id,
    order_date,
    amount
FROM Orders
WHERE order_date >= '2017-04-01'
  AND order_date < '2017-05-01';

Such a query can reduce the volume of network traffic.

However, its efficiency depends on indexes, data distribution, and the execution plan.

Aggregation

Sometimes, partial grouping at the source is advisable. For example, a regional ERP might transmit the aggregated number of operations per day.

However, this approach is unsuitable if corporate analytics requires details of individual documents.

Therefore, information requirements must be defined before aggregation.

Table Joins

If several large tables are located in the same DBMS, joining them at the source can be more efficient than transferring all records to the ETL server.

But the operation must consider the performance of the main system.

It is especially important not to run heavy analytical joins without control on a production financial ERP.

Columnar Storage and Analytical Computations

One of the reasons for the development of ELT is the proliferation of columnar storage mechanisms. In a traditional row-oriented structure, the values of a single record are placed together.

This is convenient for many transactional operations. In columnar storage, the values of a single attribute are organized together.

For analytical queries, this can reduce the amount of data read. For example, a report calculates the average order amount by product categories.

It only needs a few columns from a large table.

Columnar architecture allows for more efficient operation with similar workloads.

Microsoft SQL Server 2016

SQL Server 2016 supports columnstore indexes, including enhanced clustered columnstore capabilities. For analytical tables, this can provide a significant performance increase.

However, the effectiveness depends on the table structure, the nature of updates, and the corresponding queries.

Amazon Redshift

Redshift uses columnar storage and distributed processing.

When designing, it is necessary to consider the distribution style and sort keys. For example, if large tables are frequently joined on a single key, proper distribution can reduce inter-node data transfer.

But choosing an unsuccessful key can cause uneven load distribution.

Google BigQuery

BigQuery allows executing SQL queries on large analytical datasets in a managed cloud environment.

For ELT, this creates an opportunity to perform transformations without self-administering compute nodes.

However, it is necessary to control the volume of processed data and optimize queries.

Bulk Loading: Why Transfer Method Matters

Before performing ELT, information must be delivered to the target environment.

For large volumes, performing a separate INSERT for each record can be inefficient.

Therefore, specialized bulk loading mechanisms are used.

SQL Server

For Microsoft SQL Server, Bulk Insert, BCP, and SSIS tools can be used.

They allow for more efficient loading of large information sets with appropriate configuration.

PostgreSQL

For PostgreSQL, the COPY command is used.

It allows data transfer between a table and a corresponding file or stream source.

However, encoding, field types, and error handling rules must be considered.

Amazon Redshift

Redshift supports bulk loading via COPY, including from Amazon S3.

For large datasets, it is advisable to use a file structure that effectively utilizes the cluster’s parallel resources.

Azure SQL Data Warehouse

This platform can use PolyBase for bulk loading.

It allows integration of the SQL environment with corresponding external datasets.

However, the supported sources and formats of the specific version must be considered.

An important principle is that load optimization should be performed concurrently with data integrity checks.

Quickly transferring incorrect data is not a sign of successful integration.

Hadoop and Hive: SQL over Distributed Files

For particularly large data arrays, enterprises can use Hadoop.

Apache Hive allows describing file sets through tables and executing HiveQL queries.

As of April 2017, Hive branch 2.1 is available, including version 2.1.1 from December 2016. Version Hive 2.2.0 will only appear in July 2017.

For ELT, this creates an opportunity to load information into HDFS and perform transformations in a distributed environment.

Partitioning

For large datasets, it is important to partition data according to characteristic filters.

For example, operation logs can be stored by year, month, and day.

This allows reducing the number of files that need to be read for a defined period.

Parquet and ORC

Columnar formats Apache Parquet and ORC are used for analytical datasets.

They can reduce storage volume and increase read efficiency.

However, the choice of format must be coordinated with the computational tools and the nature of the queries.

Hive Limitations

Hive should not be automatically considered a full replacement for a productive transactional DBMS.

Its main purpose is analytical work with large datasets.

For ERP systems that require frequent short transactions and complex record integrity, a traditional RDBMS usually remains a more suitable choice.

Apache Spark SQL as a Distributed Transformation Engine

Apache Spark can be used to process information in a Data Lake.

As of April 2017, Spark 2.1.0 is the current version.

Spark SQL and the DataFrame API allow for the creation of distributed transformations using high-level operations.

For example, one can read a dataset in Parquet, perform joins with a dimension table, and generate an aggregated result.

An advantage is the ability to use a single compute engine for multiple types of analytical tasks.

However, shuffle operations—the movement of data between executors during data redistribution—must be controlled.

Large joins and groupings can create significant network load.

Therefore, performance depends not only on the number of nodes but also on data distribution, memory capacity, and computation structure.

Example with a large lookup table

If an enterprise connects a billion operations with a small product category lookup table, a broadcast join can be used under the right conditions.

A small set is transferred to executors, which allows reducing some inter-node exchange operations.

However, before applying, it is necessary to assess the actual size of the lookup table and available memory.

Hybrid ETL and ELT Architecture

For large enterprises, there is no need to use the same mechanism for all sources.

For example, an enterprise architecture may involve multiple streams.

ERP financial data undergoes strict validation and normalization before being published to the DWH.

Web service logs are loaded into Hadoop in their raw format, after which they are processed via Spark.

CRM data arrives in the staging area, where SQL procedures perform mapping with MDM.

SED reference data is synchronized via controlled APIs or periodic ETL tasks.

This architecture combines different mechanisms depending on the nature of the information.

Particular attention should be paid to corporate identifiers.

If one counterparty has different codes in ERP, CRM, and SED, placing data in a single DWH by itself will not ensure its consistency.

This requires MDM rules and corresponding mapping tables.

Reproducibility and Control of Incremental Loads

One of the most important tasks is the ability to safely repeat information processing.

For example, the ELT process for April completed with an error.

After correction, the corresponding set needs to be reprocessed.

However, simply re-executing the same INSERT may create duplicates.

Therefore, it is necessary to define idempotency rules.

Package Identifier

A unique batch_id can be created for each download.

It allows to determine which records belong to a specific execution.

MERGE or Controlled Update

For relevant DBMS, mechanisms for updating or inserting records by business keys can be used.

However, the availability and semantics of MERGE depend on the specific platform.

For example, one should not assume that all SQL engines in Hadoop or cloud services support the same transactional model.

Reconciliation Checks

After reloading, it is necessary to check the record count and checksums.

This is especially important for financial data.

Journaling

It is necessary to record the start time, source, SQL procedure version, processing volume, and verification results.

This ensures auditability and error analysis.

Architecture Selection Based on Load Characteristics

A comparative model can be used for practical selection.

These options are a starting point, not a universal recommendation.

For example, complex financial integration might effectively use ELT if the target platform implements proper quality and transactional controls.

Similarly, isolated bulk transformations can remain on the ETL server if it is economically justified.

Practical Scenario: Modernizing a Holding Company’s Information Architecture

Let’s consider a hypothetical industrial holding company with three manufacturing plants.

Each plant uses its own ERP system.

A central DWH generates consolidated financial statements and analyzes production metrics.

Over the past few years, the volume of information has significantly increased.

The nightly ETL load has started taking over six hours, and on some days, it delays morning reports.

Stage 1. Process Profiling

The team measures the execution time of various stages.

It turns out that the largest expenses are associated with sorting, joining, and re-reading large datasets.

Stage 2. Transformation Inventory

All operations are divided into critical checks, informational mappings, and analytical aggregations.

Stage 3. Staging Development

Initial records are loaded into separate tables.

Technical and operational metadata are recorded for each batch.

Stage 4. Operation Offloading

Large SQL aggregations are performed directly in the DWH.

Complex specialized checks remain at the integration level.

Stage 5. Optimization

Indexes, table structure, and load execution schedules are reviewed.

Columnar storage usage is evaluated for individual analytical datasets.

Stage 6. Result Control

The finance department verifies the consistency of indicators with the previous system.

The IT department measures the total load time, resource utilization, and error frequency.

Stage 7. Operational Regulations

The enterprise defines the procedure for re-executing batches, responding to deviations, and managing changes in SQL transformations.

The result of such a project should be not only a reduction in processing time but also an increase in the manageability of information flows.

Organizational and Economic Consequences

• Changes in transformation architecture affect the IT department’s competencies.
• ETL developers need to better understand the capabilities of analytical DBMS.
• DBAs participate in resource planning and SQL workload control.
• Data Architects define information models and the placement of transformations.
• Data Stewards ensure quality control.

For cloud environments, total cost of ownership must also be evaluated.

For example, migrating transformations from an on-premises ETL server to BigQuery may reduce the need for administering proprietary nodes, but will require continuous monitoring of external compute resource utilization.

It is also important to consider the cost of storing staging datasets.

If all historical copies are stored without defined retention periods, the volume of data can grow rapidly.

Therefore, archiving, deletion, and re-formation rules for datasets are required.

Intecracy Group Competencies in DWH Architecture Development

Optimization of information flows requires several interconnected specializations.

IQusion IT operates in the fields of system integration, electronic document management, and BPM. Such competencies are important for connecting corporate sources and considering business procedures within which data is generated.

Softline IT has competencies in server, network infrastructure, and storage systems. These determine the performance and fault tolerance of local analytical platforms.

However, the final choice of architecture should be based on the enterprise’s requirements for data quality, cost, and the time to obtain analytical results.

Practical Implementation Guidelines

Optimization of a corporate data warehouse requires a comprehensive analysis of the entire integration architecture.

Traditional ETL remains effective for complex data cleansing and control procedures.

ELT allows transferring a significant portion of SQL transformations to the production analytical platform.

Hadoop, Hive, and Apache Spark create additional opportunities for distributed processing of large datasets.

Cloud services Amazon Redshift, Google BigQuery, and Azure SQL Data Warehouse offer different models for using computing resources.

However, none of these technologies is universally the best.

For each data stream, it is necessary to determine the volume, update frequency, transformation complexity, data criticality, and control requirements.

It is especially important to ensure the reproducibility of results, metadata management, and correct use of corporate directories.

An effective DWH architecture is not a system that simply quickly transfers a large volume of data, but a controlled environment capable of consistently transforming information from various sources into reliable and timely business indicators.