How can you handle changing business requirements in a dimensional model for analytics?

From my experience, business requirements often change due to the volatile nature of today's rapidly evolving markets. A startup today might pivot tomorrow based on customer feedback, or a longstanding enterprise may undergo a major shift due to new entrants in the market. These changes may seem abrupt, but they underscore the importance of an agile mindset in the data engineering space. Moreover, with the growth in digitisation, businesses now have more data sources available than ever, from IoT devices to customer interactions on social media. This new influx of data brings in its own set of requirements, emphasising the need for a robust, scalable, and flexible dimensional model.

In today's dynamic environment we need to keep evolving and adapt to changes at much faster rate. Optimize existing business and operational challenges, New business acquisition's and new product / services offerings are the key contributors for requirement change. These involves lot of decision making skills. In these scenarios, data driven insights proven to be more accurate and help us to make better decisions. As BI reports are built for this, it also need to be flexible and robust to generate new insights in this dynamic market. This makes business requirements change more often and rapid.

There are myriad reasons for business requirements to shift, especially in our rapidly changing world. Factors like evolving market trends, technological breakthroughs, regulatory changes, or organizational restructuring often drive these changes. For those involved in dimensional modeling, this fluidity underscores the importance of creating flexible and adaptable models. Instead of rigid designs, the focus should be on models that can evolve in tandem with business needs. In essence, as the business landscape continually transforms, so must our approach to dimensional modeling.

Meet with stakeholders early in the process of building data models. Create a list of the dimensions and measures requested and the granularity needed. Once this information is gathered, I recommend meeting with stakeholders from adjacent teams and verify that the framework for the model will work for them too. It’s rare that a model will be used only by one team, so getting input from various stakeholders before sinking time into building will save you time in the long term.

New entrants, disruptive business models and technological innovations could also result in evolving business requirements. OpenAI & ChatGPT are great examples. They have forced most tech players to reassess their strategic goals and, by extension, their business requirements ! Regulatory and compliance changes are also very frequent these days, forcing companies to adapt their operations, leading to change in business requirements. Having said that, changing business requirements not only imposes challenges and risks but also provides immense opportunities for innovation and growth.

In my experience, it is impossible to know every use case for the data up front. Following a ELT process and a medallion architecture help you fulfill future use cases without having to start all the way over. ELT stands for Extract, Load, Transform. This means that you basically just copy the data down into the data lake exactly like you found it, without any transformations. This becomes the bronze or raw layer in your medallion architecture. Then, you clean up the data and enrich it while staying at the lowest grain and the result becomes your silver tables. Finally, you create meaningful aggregates and KPIs in your gold tables. When a new gold requirement comes, you can start from the silver tables. Silver from Bronze. Layers help

Technology wise, we are currently in a transition era, wherein we already have a data model for Agile development, but the technology is not quite there for all use cases. Using Data Vault 2.0 or Anchor will free you from the constraints of expensive re-builds of your data marts and reports, by allowing you to virtualize all of these from highly normalized sources. Specifically regarding DWH, there are very few on-prem RDBMS solutions that will handle dozens of joins effortlessly. Some exist as a cloud-only solution, but not that many still. So, unless you have this game on easy mode, invest a lot of time and effort in properly fleshed-out data catalog and lineage solutions to make changes as painless as possible for all.

This is great advice above. When naming your fields, remain as unbiased and agnostic as possible. For example, don’t name the field ‘ARR’ because only sales might understand what that means. Instead name the field ‘annual_recurring_revenue’ which allows users from many teams to understand your data.

From data model perspective, data vault model helps to some extent. Along with it destructive changes should not be allowed in the model using schema registries also help.

You cannot accommodate changes in business requirements by just viewing them as data modelling challenges. Changes may need to be replicated across systems. While it may not be possible to anticipate every single change, it is very much possible to ensure adequate readiness for decision making. For most organizations and enterprises, having an Enterprise Knowledge Management System(EKMS) in place can greatly help to accommodate changes. The semantic layer could consist of business knowledge, tribal knowledge, data design knowledge, implementation knowledge etc. and related taxonomies. The EKMS can support many type of changes including data modelling requirements, which can be generated leveraging relevant knowledge areas.

Generally, the trend is moving towards taking snapshots of all the dimensional data, rather than implementing complex SCD types. This is popularized by Maxime Beauchemin who has seen this strategy being used in Facebook, Airbnb, and Lyft. His reasoning is based on the following arguments, 1. Storage is cheap, engineering time is expensive 2. Dimensional data is generally small compared to fact data. 3. Snapshots are easy to reason with and have a simpler mental model, compared to the queries that you might have to write for a Type 2 or Type 3.

There is no silver bullet. For large, complex data integration, Data Vaults may be better suited as they can handle both complex schema evolution and conformity gracefully. However, analysis & reporting, we still need dimensional modelling for creating the data marts. Open table formats are also gaining in popularity over traditional SCD. Mandar has already mentioned about the functional data engineering approach proposed by Maxime. Personally, I believe & insist in having a Enterprise Knowledge Management System in place as it can immensely help in managing schema evolutions amongst several other things.

In a dimensional model, flexibility is key. We employ agile design principles, allowing swift adaptation to changing business needs. Modularity and a well-defined ETL process enable seamless adjustments without compromising data integrity, ensuring analytics stay relevant

Usually develop the dimensions with either a SCD Type 2 or Type 3 capability. Type 1 just overwrites history do it’s difficult to track change. Data in Types 2 & 3 are easily identified with queries

Adjustments to the dimensional model often stem from evolving business needs, and there's no universal solution that caters to every scenario. Nevertheless, a practice I've found invaluable is incorporating a robust metadata management system. Surprisingly, many dimensional models either overlook or undervalue this aspect. A well-implemented metadata management system streamlines the identification and execution of necessary changes. Take, for instance, a healthcare system: if regulatory standards shift, necessitating added details for a particular field, the system can swiftly pinpoint the impacted elements and facilitate a prompt implementation.

This is where having a development and a production environment can save you. Writing to a dev schema can allow you to implement your changes across many models and test thoroughly without making potentially detrimental changes to the production environment.

The following are a few key approaches to address data validation and testing: 1) Implement CI/CD: Start by integrating Continuous Integration/Continuous Deployment (CI/CD) pipelines to streamline the deployment and management of dimension table updates. 2) UAT: Integrate User Acceptance Testing (UAT) into the dimension table update process. This allows stakeholders to validate and assess the impact of the changes on data quality and reporting before deploying them into production. 3) Embed Data Quality Checks: Include automated data quality checks specific to dimension tables in your testing process. These checks should confirm data accuracy, completeness, and consistency following the modifications.

When testing and validating Slowly Changing Dimension Type 2 (SCD2) changes, focus on accuracy and historical data integrity. Create test cases that encompass typical and edge scenarios, ensuring the new records and effective date tracking work correctly. Automate testing when possible and conduct manual testing for user interface aspects. Perform regression tests to confirm existing functionality remains intact. Involve end-users in user acceptance testing (UAT) to validate real-world usability. Document results and iterate as needed, especially if issues arise. Post-implementation validation is crucial to ensure the historical and incremental data is preserved accurately in the dimensional model.

Data engineers use various testing methods, including unit and integration testing, to validate dimensional model changes for analytics. Regression and user acceptance testing compare different model versions. Data profiling, quality assessment, reconciliation, and audits analyze data. Visualization and storytelling help communicate changes to stakeholders.

After changes are deployed, the need to meet business expectations and not affect other operations. A branching strategy is imperative to ensure data and pipeline changes are version-controlled and follow a strict release process. The Gitflow Branching Model is a good option for data warehouse releases as it allows changes to be monitored in a separate environment before being released into production. When changes are deployed, there should be a period where they need to be monitored in an environment where data is similar to the live environment. Design various tests like regression and anomaly tests to ensure that the changes do not affect other metrics besides the ones intended. Set up a dashboard to monitor these changes over time.

Monitoring and maintaining SCD2 changes involves ongoing vigilance. Continuously monitor data updates to ensure historical and incremental records are preserved accurately. Employ alerts and notifications for any anomalies. Regularly validate the integrity of historical data to catch discrepancies. Maintain clear documentation and a change management process to track and manage SCD2 changes effectively. Stay responsive to user feedback, and promptly address any issues or optimization opportunities. By keeping a watchful eye and maintaining a structured approach, you ensure the long-term success of your SCD2 changes.

After testing and validating dimensional model changes, data engineers need to monitor and maintain them effectively. They use tools like ELK stack, Grafana, or Splunk for monitoring data pipeline and model performance. Data quality, security, and compliance are tracked via dashboards, lineage diagrams, and governance reports. Data usage and satisfaction can be monitored with a data catalog, dictionary, or user survey. Documentation and code are maintained with version control (e.g., Git), and a data modeling tool (e.g., ERwin) ensures model design and best practices are upheld. This ongoing maintenance ensures the model continues to deliver value and meets business expectations.

Expect a lot of changes in the early stages of the analytics process. Marrying yourself to one solution can slow progress. You might have to try a few solutions before you find the right one. Let all stakeholders know that changes are expected and you should be off to the races.