Extract: Performance Tips

Below listed some common performance tips for extract queries.

1. Extract required columns only and specify in the query, avoid select *
2. Extract required rows only. delta only
3. Avoid unnecessary sorting operations
• union vs union all
• distinct, group by, order by: use only when necessary
4. Avoid transformation logic in the extract queries. These logic should be offloaded to ETL engine.
5. Indexes
• proper where clause; 
• use AND; 
• avoid OR/NOT/<>/NOT IN
• be careful using wildcard and range
• create proper indexes
• maintain indexes regularly
6. Consider "Read uncommitted" (AKA dirty read, NOLOCK). Learn and use it carefully.
7. Use native utility and driver to execute your query. They are usually tuned for better performance.

Different file encoding and formats

Our ETL program need to handle different format and encoding coming from different sources. Below listed some common technical challenges.

• Encoding: ASCII vs EBCDIC
• Character Set: UTF8, Unicode, etc.
• Format: Fixed length, Delimited, JSON, XML, Excel Spreadsheet and more
• Numeric Format: Binary, Packed-decimal, Zoned-decimal and more

Some of the above format or encoding may be challenging in hand coding ETL. However, most modern ETL engine could help to deal with the above different format and encoding.

Transfer extracted data and checking

As mentioned in previous post, pushing or the publish/subscribe model are preferred. Todays topic is how to transfer the extracted files.

ETL: Interface files/tables or pipeline

We have different approaches to implement the ETL process. We may create an end-to-end ETL pipeline to propagate the data from extract, transform to load. Another common approach is to save the extracted data in some intermediate files or tables before transforming and loading.

Extract: Push, Pull or Publish/Subscribe

There are different extraction models. They are pushing, pulling and publish/subscribe models.

Change Data Capture (CDC)

The concept of incremental extraction is strict forward. We just extract the changed data. Today's post is going to tell you how. How can we identify changed data?

Extract: Full or Incremental (Delta)

Extract is the first step in the ETL process which obtains data from the source systems. This is our first question. How much data we need to extract?