3

I am currently in a process of maintaining a data warehouse for a quickly growing start up company. There is a lot of reporting demands from the clients, and this is usually handled by a data warehouse we set up. However, unlike bigger more established companies I worked for in the past, this company has a rapidly growing schema that would see almost 1 new table and 2 to 3 table changes every two weeks. These rapid schema changes have been picked up by the ETL team, but it has been challenging to keep up so I was wondering if there is a better way to handle it.

To explain the process in more detail, we have been using a traditional star schema data warehouse model. The tables would be transferred to ODS DB first without any changes, and will be transferred to Data warehouse layer as dimension and fact tables. If there is one change in the production DB schema, we will need to change DB structure for ODS and Data warehouse, and change the ETL steps as well. In essence, we have a meeting every sprint to check if there is any change in the production DB, and apply these changes to the ODS DB, Data Warehouse, and ETL. At this point I am wondering whether there is a better systematic way of doing this maintenance.

4
  • Try a schemaless approach like the ELK (Elasticsearch/Kibana) stack. The visualizations are rather limited but data and dimensions are completely free-form.
    – Martin K
    Feb 20 '20 at 21:45
  • If you know that you have a quickly changing schema and that will continue to occur in a long-term, have you considered using a Data Lake? Feb 21 '20 at 10:36
  • @EmersonCardoso Yes, we considered data lake. However, for the tools and analytics we were trying to do, a Data Lake seemed like a bit of over kill. We do have a changing schema, but data itself was very limited. Also, we did want to modify data a bit for reporting purpose. Feb 21 '20 at 22:59
  • @MartinK, I think I will take a look ELK and what it can do. Feb 21 '20 at 22:59
2

Your overall strategy is fine. The point where you could try to improve is the way how the transformation rules for filling the ODS and the DW are defined. You may be able to find a declarative approach, where the mapping from the source columns and tables to the destination columns/tables is defined in a very compact, concise DSL, and these rules are used to drive the transformation processes (at least, partly). That would not take the burden from you to adopt the ETL process to schema changes, but it could actually make it simpler and quicker.

In general, this subject is still under research, you will find some papers and even some commercial products by googling for "declarative etl". But maybe you will find a way to design something simple which fits to your specific environment, and the expected kind of schema changes for your use cases.

2
  • This seems very promising. We have used SSIS for ETL. However, SSIS was not easily made into a re-usable module with parameters, and we ended up writing same repetitive codes. However, it seems like we can focus on making SSIS pipeline step to be more declarative. Feb 21 '20 at 23:05
  • This method seems like the most reasonable approach to the problem I have. Thanks! Feb 22 '20 at 20:19
1

Consider changing the warehouse schema to "data vault" (https://en.wikipedia.org/wiki/Data_vault_modeling) which is very well suited to quickly adapt to changes in data structures.

As a two-liner summary: basically, you keep the core identifier for an object in a so-called "hub" with all attributes in joined "satellite" tables. If you have new attributes for a given object, you simply add another satellite instead of modifying the existing structure. Relations between hubs are expressed as "links", which also have their attributes in satellites.

2
  • This seems interesting, but doesn't this still require a user to update the ETL process to add the satellites? Feb 21 '20 at 23:01
  • 1
    Sure. But last time we implemented that, we actually generated the warehouse loading processes automatically from the data modelers model files. As for the business layer: you'll always have to update that.
    – mtj
    Feb 22 '20 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.