5

From the Agile Manifesto:

We follow these principles:

Welcome changing requirements, even late in development. Agile processes harness change for the customer's competitive advantage.

I have a requirement where we are doing cross database joins with multiple partner teams in many different data sources. Some are traditional RDMBS, some are columnar, and one is more of an API than a database for security reasons (so I can't run arbitrary SELECT ... JOIN against it).

Our solution is to set up ETL jobs against the various sources and try to get snapshots of relevant data in a single place, and use these local tables to do the cross db joins and eventually generate reports.

The problem is that if I need to use some new column as a join key (a new data source is introduced), or if a new business question is being asked, and I haven't pulled that data, then I need to modify the schemas and then rerun all the extracts again, and in some cases change the business calculations in the transforms which can take hours and requires a lot of operational overhead.

I keep hoping that requirements won't keep changing, but honestly that hope is not Agile - I should be able to respond to additional business questions on the fly. Another part of Agile is that the PMs should try to plan for these sorts of changes as early as possible - is that where my process is breaking down? Alternatively, how can I structure dataflows and related processes to be just as agile as other types of code?

  • 1
    What prevents you from pulling extracts from the data sources in a more general way, and then running specific queries on that? It sounds like your data pulls are very specific to the business reporting requirements, and I don't think they should be. Your database containing the snapshots should be the one that you can ask any question you want without having to create a brand new ETL. – Robert Harvey Sep 1 '16 at 16:57
  • @RobertHarvey does that mean I should pull every single column from partner teams? One partner team has their data distributed across 7 different tables, are you suggesting I mirror them all? I'm also doing some math as part of the transform. – durron597 Sep 1 '16 at 16:58
  • Not necessarily every single column, but I think you can choose the columns that will get you 80 to 90 percent coverage for any arbitrary business question. If the data has to be filtered first before you retrieve it (due to sheer data volume concerns), that's a different problem. – Robert Harvey Sep 1 '16 at 17:00
  • @RobertHarvey what's been happening is that we've been bad at anticipating which columns we need. Which could be just a function of lack of practice on my end? Sheer volume of data is also a problem, we've had filters that have turned out to be too narrow. – durron597 Sep 1 '16 at 17:01
5

I do not believe this is about being Agile with database design. You could remove the database from this question and replace it with a SOAP or REST interface, or anything else involving multiple system communicating.

The core issue here is "what does Agile have to say about multiple teams developing multiple products in tandem that have to work together?" or "how do we manage change outside of our control?"

As usual, the Agile Manifesto says what the team does, not how. Agile is a philosophy, not a specific technique. It just so happens that we have honed techniques to manage the complexity you are dealing with.

In a word: automate.

When interfaces change, there will be manual changes and adjustments. Try to automate as much as possible. Maybe link some fields in a configuration file, and use an automated process to build out the code that actually moves data. Another automated process then moves the data through the system.

One project I worked on had a similar setup, where data was moving through a web service and into a database. Since the interfaces were changing even late in development, I got used to plugging in a new WSDL, updating a DAO's configuration, adding a line of code to move the data, then running the automated build process. It would generate code, create an installer, install the application, and move data from one end to the other and verify the records pushed into the web service made it to the database as expected.

Regarding the time for these processes to work: sounds like a good candidate for an overnight process.

There is really not much else you can do, since trying to get multiple teams (probably with stakeholders who earn more in one paycheck than you do in a year) to reach consensus is worse than herding cats. In my experience, project managers are fairly useless in these situations. They tend to sit around and argue with each other, while my time is better spent automating the process and saving time.

  • This is basically the same conclusion that Thomas and I reached in chat. I phrased it slightly differently in my self answer. +1 – durron597 Sep 1 '16 at 17:35
3

Discussed this in chat with @Thomas Owens.

The conclusion we reached is that if backfills are common and columns changes are common, then the user story is:

We need to have proper tools to respond quickly to changing requirements and backfills in a way that doesn't consume significant developer time.

The actual length of time for backfills to run isn't the problem, the problem is my time doing them all by hand. The solution is delivering working software that meets the requirements of the environment that I'm in, in other words, automating as much of this data maintenance as possible.

  • So the answer to manual, time-consuming, tedious work done with computers is automation... Well you cannot automate a mess. If requirements don't get past the "customers dunno what they want but they want it quick" stage, there will be little chance for automation. In the best scenario you could make or get configurable tools for the extractions but the changes to the database won't be automated since they need human intervention or a computer like HAL from 2001 A Space Odissey that can take decisions. I'm not sure agile methodologies can be applied to database design. – Tulains Córdova Sep 1 '16 at 17:47
  • @TulainsCórdova I can't remove manual work completely, but I can reduce the amount of manual time involved. – durron597 Sep 1 '16 at 17:48

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.