0

AFAIK, ETL integrates data from a fixed number of sources/tables. Is it possible that ETL can integrate data from an unknown number of tables which might be added in the future, given that these tables might be of different types (customer, shipping, employee, accounting etc.) ?

EDIT -

Let A,B,C be tables. We can have the following ETL scenarios - A + B + C = 1 row or A + B + C = 3 rows

I have the first one. Later, we can add tables D,E,F,G...etc all of which might be different from each other in terms of the data they contain.

Steam
  • 179
  • 1
  • 9

2 Answers2

1

You're asking about an ETL process which accepts data from new tables without having to be adjusted? In principal, sure, all things are possible. In practice, writing this would be a challenge.

There are two main approaches to combining data from two or more tables: joins and unions. These correspond to your two examples, where A, B, and C become one row (they're being joined) or three rows (they're being unioned).

Either way, your ETL process would need to have some kind of pattern with which to identify the sources. For example, it could look for tables named "Orders" and a number. You could write code to create dynamic SQL based on such a pattern, but having multiple nearly-identical tables differing only by suffix is generally a bad design. There are some similar concepts, such as partitioning.

Alternatively, if your tables are substantially different, you're essentially talking about writing an artificial intelligence to recognize and combine related data. This would be a huge project; suitable for a thesis, or DARPA maybe, but probably not a business with deadlines to meet. Any solution it created would need to be vetted and tweaked by hand to be accurate, so why not just update the code yourself?

Is there a business goal you're trying to meet, or is this a matter of curiosity?

Jon of All Trades
  • 5,967
  • 5
  • 45
  • 62
  • unofrtunately, this is a business goal. I am not sure if this is a good thing to do. I thought that ETL was always from a fixed number of tables, unless you merge new tables. – Steam Jan 14 '14 at 22:37
  • Generally it is, but there are exceptions. For example, I have a process which merges multiple CSV files and then imports them, so it accepts and arbitrary set of inputs. But they all have the same fields, it's just a big union. Can you tell us more about what the powers that be have asked for? – Jon of All Trades Jan 14 '14 at 22:40
  • Get data from n tables (where n can be increased with time), then generate txt or csv reports. Some reports use a particular set of columns of one table while some use another set. – Steam Jan 14 '14 at 23:17
  • You could combine the tables by taking a superset of all their fields, and later query data based on a dynamic list of columns. It would be ugly, but that's close to how NoSQL works every day, and it sure seems popular these days. – Jon of All Trades Jan 15 '14 at 17:09
0

When planning ETL it is generally assumed that sources will be stable in structure and that you know the datatypes and range of values. While the first scenario is possible if any table contains a record and the other two don't, it's up to the business rules or programming logic to determine if 0 records is acceptable.

If you must add sources to your ETL process you should understand their structure. I'm not aware of a tool that will automate this for you well.

Gabriel Cuevas
  • 21
  • 4