Using external tables

 In your data warehouse you are likely to have various difference data sources, these could be an API call, another database accessed through a link or perhaps a file In the data warehouse series I am writing at the moment I am using an external file, very unrealistically it is starting as a single file with data from Amazon Prime. 

Posts so Far: 

Data Warehouse Design Considerations

Getting Started - Picking a Dataset

Loading External Table into Oracle:

Now I am not going to reinvent the wheel, why should I? There are lots of great posts out there on how to use external tables in oracle and other systems, basically they allow you to query a file sitting on the database server. From this you can load the data and process through your ETL. The code I am using to load this sample dataset is below. 

History Tracking - Tracking Changes:

In Data Warehousing we have processes to cater for slowly changing data, or slowly changing dimensions. I will do a post on these in my next post and then I need to build a solution. For this example I will likely build a very bespoke solution but highlight how it could be used to generate a generic solution using dynamic SQL.