December 11, 2015 at 12:00 am
Comments posted to this topic are about the item How Much Eventual Consistency Do You Have?
December 11, 2015 at 1:33 am
Given that an asynchronous member of an availability group could be classed as eventually consistent, quite a few of us...
Me, for one.
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 11, 2015 at 1:58 am
Given that data is committed in an operational system and that commitment process plays no part in the confirmation of delivery to the reporting process I'd say that eventual consistency with all its problems has been around for a long-long time.
The difference is that the NOSQL is upfront and frank about it. I feel it is kind of a dirty little secret that we don't talk about much.
December 11, 2015 at 7:43 am
Today I'm curious how many of you have systems that your businesses depend on where the data is eventually consistent because of some technology that moves information from one database to another. Perhaps you might even share some of the tricks you use to ensure that delays or problems in your transfer process are detected and fixed before your clients realize just how inconsistent their data might be.
On the first point, we simply stopped letting users run reports out of multiple systems 🙂
On the second, proper error reporting when a process fails to move data from one system to the other.
December 11, 2015 at 9:56 am
SQL Server's default Read Committed isolation works in an OLTP database, so long as you keep both your transactions and your query access methods short and optimized. As for bulk loading and aggregate reporting, that should be pushed down to an OLAP database. It's kind of like a husband and wife coming to an agreement about the division of household tasks. Plan ahead in a meaningful way, and then everything gets done, and no one steps on the other's toes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 11, 2015 at 10:32 am
David.Poole (12/11/2015)
Given that data is committed in an operational system and that commitment process plays no part in the confirmation of delivery to the reporting process I'd say that eventual consistency with all its problems has been around for a long-long time.The difference is that the NOSQL is upfront and frank about it. I feel it is kind of a dirty little secret that we don't talk about much.
This is one of the things I've heard from MS architects and engineers. We have this in SQL, but don't think about it. Mostly because of convenience.
December 11, 2015 at 12:20 pm
Replication is a key part of Cassandra.
And I have to agree that SQL Server replication has so much potential, seems to have been neglected, and is brittle. And I always use the phrase "near real time".
The more you are prepared, the less you need it.
December 11, 2015 at 3:24 pm
I feel that we tend to use a one size fits all approach with little regard to actual business need.
Billing systems have to be accurate. Marketing systems can work on data with lower granularity. If we force the billing paradigm on a marketing use case we add delay but no improvement for the business decision makers. In the perfect world decisions could be entirely driven by data however not all sources of information driving decisions is captured electronically or, when it is, in a form that aids decision making.
Then there is user behaviour. When the data confirms the desired conclusion no one questions it. When it does not then all sorts of reasons emerge to justify ignoring it.
I also know of flawed systems where the flaws are acknowledged but there is no appetite to change the systems because consistency of approach is valued above other consideration.
What this tells me that businesses require rapid access to credible but not necessarily precise data. If I am correct then eventual consistency is perfectly good enough in the majority of cases
December 11, 2015 at 5:56 pm
Personally, I look at this problem as the same way I do when doing any ETL development. You must verify consistency before moving on.
In the classic data warehouse -> data mart example, the data mart cannot be refreshed until the data warehouse is refreshed with the latest data. This takes time to do. Systems are in place to verify consistency before populating other systems that rely on one another for that same consistency.
In my example, I am moving towards a data lake that sits on top of the data warehouse that sits on top of the data marts that sits on top of the in-memory OLAP cached applications that visualize the data.
Audit and log tables are used to ensure every parent to the child is consistent all the way up to HDFS replication across the nodes. In the batch loading world, it's a bit easier to handle. You have time to wait. But, the same methodologies still apply I would imagine elsewhere. You cannot move on from parent to child until you are absolutely sure the parent is ready. Otherwise you have inconsistent data that is wrong or worse, flat out missing.
December 14, 2015 at 6:38 am
xsevensinzx (12/11/2015)
You cannot move on from parent to child until you are absolutely sure the parent is ready. Otherwise you have inconsistent data that is wrong or worse, flat out missing.
Totally agree. This is a problem we've had to deal with as well.
December 14, 2015 at 9:22 am
xsevensinzx (12/11/2015)
Personally, I look at this problem as the same way I do when doing any ETL development. You must verify consistency before moving on.In the classic data warehouse -> data mart example, the data mart cannot be refreshed until the data warehouse is refreshed with the latest data. This takes time to do. Systems are in place to verify consistency before populating other systems that rely on one another for that same consistency.
In my example, I am moving towards a data lake that sits on top of the data warehouse that sits on top of the data marts that sits on top of the in-memory OLAP cached applications that visualize the data.
Audit and log tables are used to ensure every parent to the child is consistent all the way up to HDFS replication across the nodes. In the batch loading world, it's a bit easier to handle. You have time to wait. But, the same methodologies still apply I would imagine elsewhere. You cannot move on from parent to child until you are absolutely sure the parent is ready. Otherwise you have inconsistent data that is wrong or worse, flat out missing.
In large load areas, we've had this issue as well. One thing we've done is use a join table, very small, that has an active date/flag in it. All queries join to this to get recent data. I think we may have had multiple tables for different areas (fact v dimensions)
We load data, verify it, and if it's complete, we add a row to this table(s) to make the load active. If we find issues, we remove the row, so queries go back to look at other, older data automatically. In this way we minimized potential partial load issues
December 14, 2015 at 8:18 pm
Steve Jones - SSC Editor (12/14/2015)
xsevensinzx (12/11/2015)
Personally, I look at this problem as the same way I do when doing any ETL development. You must verify consistency before moving on.In the classic data warehouse -> data mart example, the data mart cannot be refreshed until the data warehouse is refreshed with the latest data. This takes time to do. Systems are in place to verify consistency before populating other systems that rely on one another for that same consistency.
In my example, I am moving towards a data lake that sits on top of the data warehouse that sits on top of the data marts that sits on top of the in-memory OLAP cached applications that visualize the data.
Audit and log tables are used to ensure every parent to the child is consistent all the way up to HDFS replication across the nodes. In the batch loading world, it's a bit easier to handle. You have time to wait. But, the same methodologies still apply I would imagine elsewhere. You cannot move on from parent to child until you are absolutely sure the parent is ready. Otherwise you have inconsistent data that is wrong or worse, flat out missing.
In large load areas, we've had this issue as well. One thing we've done is use a join table, very small, that has an active date/flag in it. All queries join to this to get recent data. I think we may have had multiple tables for different areas (fact v dimensions)
We load data, verify it, and if it's complete, we add a row to this table(s) to make the load active. If we find issues, we remove the row, so queries go back to look at other, older data automatically. In this way we minimized potential partial load issues
Yeah, I really like that type of system too. I am moving towards similar for reloading backdated data based on time. The only difference is that I have a third field that is a metric that measures the amount of records loaded. The date signifies when the refresh happened, the flag represents if the data loaded for that date is active in production and the metric is used as a benchmark.
The benchmark is important because in my use case, we don't control the source data. We have to use benchmarks on what is normal and not normal. While data may come in, it does not mean the amount of data is correct. So, the dates with the flags and the metric help the system identify problem days of bad data refreshes that can manually be set to re-import data on that date or set to be automatic.
This is a fail safe for when the system assumes the parent is ready, but really, it wasn't. We need to roll back and try again to ensure consistency and of course accuracy. You can't always ensure both unfortunately.
I look at this as almost like A.I, which makes SQL Server super important even when using NoSQL. It acts as the centralized brain for the entire infrastructure, which is pretty cool in my limited experienced mind. :hehe:
December 15, 2015 at 4:46 am
We have many systems that provide "eventual consistency", however, it is often the lax attitude to this consistency issue that causes problems not the "eventual consistency" itself.
Often fanbois and fangrrrls believe the "eventual consistency" hype instead of the technical "eventual consistency" scenario which they would have to design around.
I am sure that this is not limited to NoSQL, especially on other issues.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply