4

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.

Improve this question
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, 2020 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?
    – Emerson Cardoso
    Feb 21, 2020 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.
    – Yong Jun Kim
    Feb 21, 2020 at 22:59
  • @MartinK, I think I will take a look ELK and what it can do.
    – Yong Jun Kim
    Feb 21, 2020 at 22:59

2 Answers 2

Reset to default
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.

Improve this answer
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.
    – Yong Jun Kim
    Feb 21, 2020 at 23:05
  • This method seems like the most reasonable approach to the problem I have. Thanks!
    – Yong Jun Kim
    Feb 22, 2020 at 20:19
2

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.

Improve this answer
2
  • This seems interesting, but doesn't this still require a user to update the ETL process to add the satellites?
    – Yong Jun Kim
    Feb 21, 2020 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, 2020 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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