Like to find out how you can better your data? Please fill in the form below.

Get in touch

Come and see us

Datasparq people are smart, curious, skilled, experienced and creative. If you have what it takes to create data solutions that aren’t like everyone else’s, we’d love to hear from you.

Inspired? Join our team

Contact

Welcome to Datasparq’s technology blog. At Datasparq, we design, deliver and run bespoke data science products to help organisations capture value from data. - Medium

Datasparq Technology - Medium

With current carbon offsetting processes being dubiously effective, we suggest a data-driven approach that companies can use to become reliably sustainable and protect themselves from punitive investor and government action.
<figure><img alt="Approximately 120 stacked fans of roughly diameter 2m, situated in a desert used in the direct air capture of CO2" src="https://cdn-images-1.medium.com/max/1024/1*yU8u93yHTHBTatArduUj9w.png"><figcaption>An array of fans blow air through a carbon dioxide-absorbing material in this mock-up of a direct air capture plant. © Carbon Engineering</figcaption></figure>The Paris climate agreement was important — vitally important — not because it meant that it solved the problem of climate change — it did not. Nor did it mean that the world’s governments were able to agree on a mechanism for greenhouse gas reduction, that is clearly a path much longer in the making. It was important because it represented a global, multi-nation-state backed, statement of intent to try to limit average global warming to 1.5C.
Now that has happened it is up to us: citizens, employees, colleagues, companies, organisations… to take a lead. Even if successive COPs seem woefully inadequate in ambition and outcome, it actually doesn’t matter — we now have a global call-to-arms and a goal that needs to be met. And increasingly as many cities and states across the world are finding out, it matters less and less whether national governments are able to reach a consensus or not.
As data scientists and engineers, we have our challenge — and in the (nearly) immortal words of Matt Damon as Martian astronaut, Mark Watney:
<blockquote>
“You solve one problem… and you solve the next one… and then the next. And If you solve enough problems, you get to save the planet!” — Mark Watney
</blockquote>
That sounds like something we can get behind.
<h4>So how can this happen and why isn’t it happening yet?</h4>
The challenge for companies that quite genuinely want to reduce their impact through their operations or services is that they have no easy way of understanding the real costs and downside risk of continuing as they are. Most companies have everyday pressures to maintain or improve their financial bottom line and those are easily understood and accountancy practices are put in place to monitor and support that goal. There are very few companies who monitor and report openly on their carbon bottom line and it is not clear how one could even convert between carbon and cash, especially in jurisdictions where there is no hard requirement to do so.
<blockquote>There is no easy or consistent way of computing the financial downside of pumping millions of tonnes of CO₂ into the atmosphere.</blockquote>
Even for companies who are engaging in offset activities such as carbon credits, there are many pitfalls and no guarantees that carbon is actually being sequestrated and removed from the atmosphere [5].
It is very clear that where there is not now specific environmental legislation governing the release of greenhouse gases in a country, it is a safe assumption that there will be such legislation in many countries in the next 10 years. (Nevertheless without legislation, the pressure today from institutional investors to reach net zero is significant, as evidenced by one of the largest global funds, Norway’s Sovereign Wealth fund [1,6]).
What form any legislation takes will of course vary: carbon credits, carbon trading, carbon taxes, carbon limits, possibly consumer carbon rationing if things get really bad. Each of these options carries a financial impact on companies as well as being variously effective in reducing greenhouse gases.
Although legislation is rarely retrospective it is also a significant possibility that some key polluting industries, e.g. the oil and gas sector, energy generation sectors, may well be asked to be, not just carbon neutral but significantly carbon negative in the future; this will allow those industries to unwind some of their historic carbon debt if done correctly.
<h4>And the data science in this?</h4>
Readers of this series will know how much we use and value reinforcement learning (RL) and RL thinking in our approach to data science.
The reinforcement learning paradigm applies to business operation as much as it does to data science execution. A business is every bit an RL machine — it has levers it can pull, decisions it can take, projects it can try. From those decisions a well-run business will try to quantify their success and from that learnt success or failure, future decisions and projects are initiated.
If that success quantification process (in RL terms, the reward function) excludes a major element of the operation of the company — the cost of removing huge amounts of CO₂ from the atmosphere — then a company will never learn the correct behaviours and decisions to operate in a sustainable way. Never!
<blockquote>What is needed is an accurate quantification of the cost of removing CO₂ from the atmosphere (so-called CDR) that can be established as a baseline measure for carbon removal.</blockquote>
<h4>Hard to quantify costs around CDR</h4>
There is a good reason for not currently being able to quantify the carbon cost easily— because carbon mitigation is complex and a fast changing picture: do you plant trees which require land and long periods of time, do you invest in sustainability projects, do you pay someone for a carbon certificate? What do these mean and do they even achieve the goal of removing carbon from the environment?
An organisation that generates greenhouse gases now and wants to reduce that impact, has broadly two options:
<ul>
<li>change the operation so that they reduce or don’t emit carbon in the first place</li>
<li>acknowledge that some carbon is part of their current operational cycle and attempt to mitigate it through various removal approaches</li>
</ul>
Each of these possibilities carries implementation challenges. But for a business to know whether its worth changing its operation it needs to accurately understand the actual cost of carbon mitigation.
<h4>Direct Air Capture of CO₂</h4>
So now we have established the need for a standard cost metric for atmospheric carbon removal or CDR, we need a standard process that can be costed. Enter Direct Air Capture or DAC.
The process behind Direct Air Capture is relatively simple and starts with a similar chemical reaction to the one used on the Apollo moon missions to remove CO₂ from the various manned space capsules.
As shown below, ingested atmospheric air (with usually only 450 parts per million of CO₂ as of 2023!) is passed through a potassium hydroxide solution. This effectively scrubs the air of CO₂ but leaves a solution of potassium carbonate. Fortunately potassium hydroxide can be recovered from the carbonate salt by means of a pellet reactor and a calcium hydroxide solution. While the potassium hydroxide can be reused directly, the resulting calcium carbonate needs to be further treated in order to extract the CO₂ (for compression and storage) and turned back into calcium hydroxide via a final slaking process.
<figure><img alt="A process diagram representing the chemical process behind direct air capture of CO2. It shows air being ingested on the left and CO2 being produced on the right." src="https://cdn-images-1.medium.com/max/1024/1*9nvyU7gE5a2cG8VNNduDtw.png"><figcaption>The chemical extraction process behind Direct Air Capture of CO₂ (from Keith et al. [3])</figcaption></figure>This all looks very neat — the circularity of the two central processes obviates the need for large amounts imported chemicals. Just as well considering we will ideally need to extract in excess of 37 billion tonnes of CO₂ per year [4] by one means or another (including vegetation growth and oceanic absorption) just to keep at the position we are today.
<h4>But where’s the catch?</h4>
<blockquote>However this seemingly saviour process comes with a big catch — it is enormously energy intensive.</blockquote>
According to [3], it takes 67MWh of energy to capture 170 tonnes of atmospheric carbon dioxide using this process. This may seem like a huge negative and it is indeed a practical challenge of implementation.
For our purposes however, it is exactly what we need to know. Electricity is sold on the wholesale market by the MWh. Currently, as I write, the price of electricity on the UK’s National Grid is retailing £207 per MWh. This gives us a means of pricing CDR both in the short term, and over the longer term. At that price it would cost an eye watering £81 per tonne of CO₂ removed. For context, this figure has regularly dropped to £30-£40 per tonne and the average cost per tonne of CO₂ removal has been £61.85 in the last week in the UK.
Actually it’s worse than that because the UK Grid is also currently operating at a carbon intensity of 134gCO₂/kWh, but this can be taken into account (as it clearly ought to be). Removing the carbon from the electricity generation as well, gives an adjusted price of £86.13/tonne.
<h4>In general</h4>
For a generic CDR technology (t) that takes Eₜ MWh to extract Mₜ kg of CO₂ with an energy cost of Cₛ $/MWh where the energy source (s) has a carbon intensity of Iₛ gCO₂/kWh, the $ cost per kg of CO₂ removal is:
<figure><img alt="\frac{E_t C_s}{M_t — I_s E_t} where I_s E_t is much less than M_t" src="https://cdn-images-1.medium.com/max/332/1*ZU9vkRboFEOtZ9mXv3mg0Q.png"><figcaption>Formula for cost per kg of extracting carbon dioxide from the environment</figcaption></figure><h4>Constructing the Carbon metric</h4>
You would of course not want to sample the spot market price for electricity to give you your carbon metric, you would for instance:
<ul>
<li>take a rolling average over a period of time</li>
<li>find a market where electricity was cheap and itself low-carbon</li>
<li>perhaps only pay for DAC at times when the wholesale price of electricity has dropped below a certain point</li>
</ul>
All these are achievable and can be reflected in a suite of metrics to conform to a gold-standard actual cost of carbon removal for an organisation that has no other way of reducing its operational carbon output but wants to do the right thing!
<h4>Conclusion</h4>
There we have it. As my colleague James Tawton more succinctly put it:
<blockquote>It’s complicated, but we can now expose the costs and savings implications of CDR via direct air capture as part of this data-driven model. With this, organisations and companies can ensure the financial, legal and ethical integrity of their sustainability strategy.</blockquote>
For now an idea, but one we are looking to implement. We work with companies who generate carbon dioxide in various parts of the world through heavy fleet movements, flights, supply chains, etc. We will now be able to introduce realistic cost metrics into our optimisation algorithms that can take into account carbon capture and removal (after first minimising the carbon generated).
I am sure it will generate some interesting discussions but as companies realise that they are going to be liable for this carbon removal cost at some point, it’s probably a good idea to set that money aside now. Even better, start investing in carbon removal technology and plants now and ensure that it is in a form they can be 100% sure actually does the job.
<h4>Postscript: Saving the planet?</h4>
To be clear I am not advocating using DAC as the core technology for carbon reduction in the future — it is more by way of a upper bound on what we may have to do [7]. If other approaches involving increasing global vegetation cover, rewilding, restoring peat bogs, and just burning less stuff can be used and importantly verified [5] then they will likely be easier and much cheaper. DAC of some variety is part of the mix of approaches that we will need to rescue our planetary ecosystem [2].
But let’s play the thought experiment. If we were to put aside a sum of money and an energy budget to reverse carbon dioxide concentrations in the atmosphere — what would that look like?
Using the UK’s grid statistics for the last year, 2022, (which was a high cost year for lots of reasons): £194.79 per MWh at an intensity of 178gCO₂/kWh gives a cost metric of £94.61 per tonne of CO₂ extracted.
If we plan to remove all annual CO₂ emissions and start to reverse atmospheric concentration levels we probably want to plan to remove around 40 billion tonnes annually. That would be £3.78tn or $4.58tn per year of OPEX cost (roughly 4.1% of annual global GDP). To be clear though this would need us to divert around 55% of current globally generated electricity (based on 2021 generation [8]) into carbon sequestration, so more efficient and cheaper techniques are definitely needed as well.
And finally…
If you are interested in joining us at <a href="http://www.datasparq.ai/">Datasparq</a> for exciting projects like this or just finding out how your company can benefit from our sustainable and ethical approaches to Data Science and Engineering, <a href="mailto:%20info@datasparq.ai">please get in touch</a>. We’ll be delighted to hear from you.
References
[1] S. Feingold <a href="https://www.weforum.org/agenda/2022/09/norways-massive-sovereign-wealth-fund-sets-net-zero-goal/">Norway’s massive sovereign-wealth fund sets net-zero goals</a>. World Economic Forum, September 2022
[2] A. Owen, J. Burke, E. Serin. <a href="https://www.lse.ac.uk/granthaminstitute/publication/who-pays-for-greenhouse-gas-removal-in-the-uk-designing-equitable-policy-to-fund-beccs-and-daccs/">Who pays for greenhouse gas removal in the UK? Designing equitable policy to fund BECCS and DACCS?</a> Grantham Research Institute, LSE, August 2022
[3] D.W. Keith, G. Holmes, D. St-Angelo, K. Heidel. <a href="https://doi.org/10.1016/j.joule.2018.05.006">A Process for Capturing CO₂ from the Atmosphere</a>. Joule. Volume 2(8), pp. 1573–1594, 15 August, 2018
[4] I. Tiseo, <a href="https://www.statista.com/statistics/276629/global-co2-emissions/">Annual carbon dioxide (CO₂) emissions worldwide from 1940 to 2021</a>. statista.com. 3 January, 2023
[5] P. Greenfield. <a href="https://www.theguardian.com/environment/2023/jan/18/revealed-forest-carbon-offsets-biggest-provider-worthless-verra-aoe">Revealed: more than 90% of rainforest carbon offsets by biggest certifier are worthless, analysis shows</a>. The Guardian, 18 January 2023
[6] R. Neate. <a href="https://www.theguardian.com/business/2023/feb/03/worlds-biggest-investment-fund-warns-directors-to-tackle-climate-crisis-or-face-sack">World’s biggest investment fund warns directors to tackle climate crisis or face sack</a>. The Guardian, 3 February 2023
[7] S.M. Smith, et al. <a href="https://www.stateofcdr.org/">The State of Carbon Dioxide Removal: A global, independent scientific assessment of Carbon Dioxide Removal</a>. 1st Edition. University of Oxford. January 2023.
[8] <a href="https://www.statista.com/statistics/270281/electricity-generation-worldwide/">Electricity generation worldwide from 1990 to 2021</a>. statista.com. 16 Jan 2023.
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=d537349bc25e" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/can-data-science-and-data-transparency-help-save-the-planet-d537349bc25e">How data science and data transparency can help save the planet</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

How data science and data transparency can help save the planet

<h3>BigQuery vs Synapse — who is currently winning the battle?</h3>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*rNyXaAA3Ns8Wyb47"><figcaption>Photo by <a href="https://unsplash.com/@wacalke?utm_source=medium&amp;utm_medium=referral">Mateusz Wacławek</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure>Data Warehouse — a place where the magic happens. A place where you can throw massive amounts of data from various origins, write some SQL and draw meaningful conclusions — and this is all without stressing out about your CPU power.
The definition of a data warehouse evolved tremendously in the last ten years. Companies started to give up the traditional, obsolete service-oriented architectures and joined the rush movement into cloud computing. As part of this change, such products as Google BigQuery, Azure Synapse and Amazon Redshift appeared on the market, offering extensive data analysis for a reasonable price.
To not become a believer of one tool, it is sometimes worthwhile to revisit the market, and compare your main weapon with the other alternatives. While working on projects for a wide range of clients, where each one has different needs and preferences, I have recently had a chance to dive deeper into Synapse. Upon project completion, I have decided to compare it to BigQuery, where my previous experience was based, and share some thoughts based on what I discovered/observed. Taking into respect multiple categories, we will try to answer the question — is one product surpassing the other in 2022?
<h3>Cost</h3>
So let’s start with what the most individuals want to know first — the price. And although predicting it with a fine-toothed comb is virtually impossible, the choice of the used pool type is going to influence the end price the most, therefore I am going to focus on this aspect.
NOTE: Wanting to make the costs clear and transparent to everybody, this section turned out to be the longest. Having in mind not everybody is interested in such thorough descriptions, I have prepared a shortened version of costs in form of a table, after which you are officially allowed to move to the next section.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/954/1*uUkgIeUePlSzJdX21ZvA2A.png"><figcaption>* Prices with respect to London’s region i.e. ‘uk-south’ in Azure and ‘europe-west2’ in GCP</figcaption></figure>For the ones wanting to dive deeper and know all the details — here you are!
<h4>DEDICATED POOL - Synapse:</h4>
In this option, we decide to pay in advance for the specific pool performance we want to use. The power however, is very flexible and can be adjusted to the current need anytime. The hourly cost of the smallest cluster — DW100c is going to cost us in this moment £1.40, while such a monster as DW30000c will be even £421.14! Charges are only applied when the cluster is up and running, therefore, forgetting to pause it when is idle may cost you a pretty penny. Using Azure functions, we can automate that process, pausing compute instances at a fixed time or after a specified amount of time.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fUY0LacPYBSvwhl-XgejsQ.jpeg"></figure>SERVERLESS POOL - Synapse and BigQuery:
Serverless is another possibility our queries can run. It is the native environment for BigQuery, which was designed and built from the beginning as the serverless platform, whereas in Synapse it is an option. The payment models of this mode are very similar in both services and are very cost-efficient. Instead of paying for the resources deployed, you pay per-query — and the more computationally heavy it is, the higher amount you need to pay. In this option you do not care about pausing clusters, as the charge is only made after executing a query. The price of processed TB is slightly cheaper in Synapse at £5, when its £6.25 in BigQuery. Both services offer the first TB of processed data for free and the quota renews every month! This benefit, however, will be dropped out from Synapse in the end of December 2022.
The other cost we need to add on to both services is saving and storing the tables. In case of BigQuery tables are saved internally, which means any data used for analysis is priorly imported directly into BigQuery. Synapse on the other hand does not support internal tables in the serverless option, and tables are saved externally on Data Lake Storage Gen2. The advantage of internal tables is that running queries against them (especially if tables are very large) is measurably faster in comparison to the external tables. The disadvantage is the slightly higher price; 1GB of stored data in BigQuery is going to cost monthly ~£0.023, whereas in Synapse ~£0.016. However, the pricier service allows storing the first 10GB of data per month charge-free. Both cases assume the table is stored in the closest region (in this case, London), is frequently accessed and modified.
ALTERNATIVE PAYMENT OPTIONS - Synapse and BigQuery:
For those whose pockets are not the bottomless pits and want to have a greater control over the bills, both services offer something to prevent ending up a bankrupt. In BigQuery you can opt for flat-rate pricing option and pay for the purchased amount of BigQuery slots (processing power) on the annual, monthly or flex commitment plans. Synapse on the other hand offers ‘safe limits’ … which cause a Budget Exhausted error when crossed.
Trial period:
<ul><li>The trial period in Synapse lasts 30 days, during which we can enjoy the free $200 credit given us by Microsoft to explore all of the tools Azure has to offer. Some of them remain free up to one year. That looks even better in Google, where the new users receive free $300 credit valid for all of the cloud tools for 90 days!</li></ul>
Student pricing plan:
<ul><li>Azure offers students a very attractive option to use the cloud, giving them $100 credit, even without the need to provide a credit card! Although Google offers students their qwiklabs where they can learn the fundamentals of cloud computing, there is not really an option for more experienced young users wanting to create and manage their own projects at a reduced price.</li></ul>
Summing the costs section up, the pricing plan in BigQuery looks slightly more attractive from the one in Synapse, therefore BigQuery is the winner in this category.
Stats so far: Synapse: 0, BigQuery: 1
<h3>Speed</h3>
This factor is the toughest to measure as it depends on plenty of factors to be considered and in the end, probably everybody would need to do their own benchmark. Mine was based at the serverless option, as this is the one we run the most projects on in Datasparq. What I was mainly interested in was, how the tool deals with the complex operations on big datasets and conversely — how long does a simple SELECT * take?
To check this out, I started big and ran a query to first create an artificial 120 million row dataset based on CROSS JOINs, then ran a rolling window on it and finally save the result table. This is the operation I was worried about the most in Synapse because first — it is very computationally heavy, and second — unlike BigQuery, Synapse does not allow users to save tables internally and forces saving them externally in .parquet files. My fears however, turned out to be totally unfounded, and Synapse surprised me very positively, completing the operation in a very similar time to BigQuery. The only major speed difference I have spotted between these two services was running the SELECT * statement at the newly created datasets. When BigQuery was repeatably completing the operation within 15–20 seconds, the process of listing rows in Synapse was going on even 10 minutes after BigQuery was done!! I found this circumstance very interesting, as contrary to my guesswork, saving results to the external tables was taking around 30 times shorter than producing the result-set.
Concluding, we do not need to worry about our increasing dataset, in either of these services. Both of them can perform complex operations on a large amount of data very smoothly. However, not both of them can handle retrieving rows to the result tables equally well, therefore the speed classification belongs to BigQuery.
Stats so far: Synapse: 0, BigQuery: 2
<h3>Simplicity</h3>
Leaving the underlying technology of the tool behind, one of the most detrimental parameters the team should be thinking of is how quickly the final solution can be implemented and delivered. That is why simplicity turns out to be a very deterministic component with a high weight.
Realistically, it does not take more than 5 minutes to set up BigQuery. What we need to do to start using BigQuery is to create a bucket in Google Cloud Storage (GCS) and then load files into it. Afterwards, we can directly head up to BigQuery to create a dataset and a table under which we can point our file located in GCS. This is the bookish way, however, BigQuery offers a more lazy option as well being able to upload the file directly there, point the file located on GDrive, or even on the solutions of competitors such as Amazon S3 or Azure Blob storage!
To run the first query in Synapse, on the other hand, we are required a prior set up of a resource group, storage account and finally a Synapse Workspace, which altogether takes considerable longer. The point goes to BigQuery.
Stats so far: Synapse: 0, BigQuery: 3
<h3>Running queries in Python:</h3>
At some stage, while building a data engineering pipeline we reach to the point where running queries needs to be automated. From the abundance of scripting languages we most likely choose Python, thanks to its simplicity and a great support of Data Engineering and Data Science tools.
Running SQL queries against BigQuery from the Python perspective is very simple and is possible through the BigQuery Python client running the query() method just after specifying the project, dataset and table names.
The same operation is much more complicated to achieve in Synapse and requires a prior set-up of ODBC drivers, which is different for every UNIX architecture. BigQuery receives another point from me.
Stats so far: Synapse: 0, BigQuery: 4
<h3>Syntax/limitations:</h3>
The immediate difference we spot right away is — SQL in BigQuery does not equal SQL in Synapse. The default query language in the latter one is Microsoft Transact-SQL (T-SQL), but if you want to write queries in Kusto Query Language (KQL), you are more than welcome. BigQuery, on the other hand bases upon Google Standard SQL dialect, which ceaselessly since 2016 is a standard.
This all involves the small changes like the way of defining BOOLEAN types (BIT (1, 0) in Synapse, BOOLEAN (TRUE, FALSE) in BigQuery), through the lack of few useful methods existing only in BigQuery (such as EXCEPT, REGEXP_REPLACE, USING, etc) up to the way of saving external tables.
From the perspective of a BigQuery user, a lack of these methods is already noticeable in an attempt of selecting every column apart from THIS ONE. Let’s consider this case: your source table has 250 columns, but within your CTE, you only want to list 230 of them. Doing this in GCP would look as simple and neat as this: SELECT * EXCEPT(column_1, column_2, column_3, …, column_20) FROM source_table.
Absence of an EXCEPT method (nor its equivalent) forces us to list all of them manually within the query. To not make it sound that dramatic, Synapse has a feature listing them down for us (right click on the source file, and choosing “Select TOP 100 rows” ). As all 250 columns were selected, we now need to manually remove the 20 we are not interested in. Sadly, such a bypass does not work within the CTE, and either we have the patience level above the average human being listing all of the columns manually, or we need to think of some smarter solution I have not found yet.
Many people will find the syntax of SQL in BigQuery more user-friendly, as in contrast to Synapse, it hides the underlying complexity and users can focus on writing the actual SQL straight away. For example — if we want to create an external table from the CSV file in BigQuery, all we need to do is create a dataset and then run the following statement:
<pre>CREATE EXTERNAL TABLE sample_dataset.sample_table </pre>
<pre>OPTIONS (</pre>
<pre>format = ‘CSV’,</pre>
<pre>uris = [‘gs://bucket/our_source_csv_file.csv’]</pre>
<pre>)</pre>
Synapse on the other hand, requires prior creation of a specific Database and File Format. On top of that, it is mandatory to define schema within the CREATE statement. And although Synapse can write them for you (when you click on the name of the .csv file within the Data tab), all these fields will require supervision and probably modification to the desired filed type and length. In the scenario of having a big number of columns, things are getting pretty exhausting again.
<pre>CREATE EXTERNAL TABLE [sample_dataset].[sample_table](</pre>
<pre>[Field1] nvarchar(256),</pre>
<pre>[Field2] nvarchar(256),</pre>
<pre>[Field3] bigint,</pre>
<pre>(...)</pre>
<pre>[Field70] float</pre>
<pre>) </pre>
<pre>WITH (</pre>
<pre>LOCATION = 'DummyData.csv',</pre>
<pre>DATA_SOURCE = [sample_datasource_dfs_core_windows_net],</pre>
<pre>FILE_FORMAT = [CSVFormat]</pre>
<pre>)</pre>
Although this kind of granularity may seem redundant, and only complicate something that could have been done easier, it also offers more customisability options, the practised user can take advantages from. If only Synapse was not lacking the Except statement, I would grant both services by one point, however, from the perspective of BigQuery user, this lack causes just too much inconvenience.
Stats so far: Synapse: 0, BigQuery: 5
<h3>Summary:</h3>
Losing the scoring competition, Synapse seems like a bad choice to start working with, which it’s not. It’s still very powerful tool with a decent speed that can be used with a great effect in various data analysis operations. With BigQuery, however, you can achieve the same thing quicker, simpler and probably cheaper. Nevertheless, unless you are an individual, the choice you make, will most likely be determined on the IT architecture your company already use..
<figure><img alt="" src="https://cdn-images-1.medium.com/max/568/1*643m0CTyNgVljHVLW2XEFg.jpeg"></figure>For more insight follow <a href="https://medium.com/datasparq-technology">datasparq-technology</a>, and find us on <a href="https://www.linkedin.com/company/datasparq">LinkedIn</a>, <a href="https://twitter.com/DatasparqAI">Twitter</a>, and our <a href="http://datasparq.ai/">website</a>!
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=7b9e3b2d19f2" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/bigquery-vs-synapse-who-is-currently-winning-the-battle-7b9e3b2d19f2">BigQuery vs Synapse — who is currently winning the battle?</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

BigQuery vs Synapse — who is currently winning the battle?

Question: When’s the last time Machine Learning (ML) directly influenced your behaviour? Answer: At least 10 times today so far.
Data science concepts are being used to affect every aspect of our lives, from the articles you get recommended on Medium, to the prices you’re shown for a taxi, and even which breakfast cereal is on offer in the super market. In 10 years time, data science will be involved in every human decision.
…but it can be difficult to understand. Sometimes it’s impossible to see how a model arrives at its result, and we have to trust it blindly. Sometimes a problem can’t be understood because it’s too complex. We wanted to fix that.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*97vWHf-3pWJLSeBBwLj45Q.gif"><figcaption><a href="https://play-ml.datasparq.ai/">play-ml.datasparq.ai</a></figcaption></figure><h4>Introducing PlayML</h4>
<a href="https://play-ml.datasparq.ai/">play-ml.datasparq.ai</a> is a website where curious people can play around and experiment with the most cutting edge data science techniques. Challenging problems are visualised, made interactive, and broken down into basic statistics.
We want tough concepts, such as delivery route optimisation, to seem intuitive, so that people from all backgrounds feel confident when deciding to use data science to solve a problem.
We’re not just trying to teach statistics, we’re explaining real world problems and and real solutions that we’ve built ourselves. The techniques shown in PlayML have real proven value and are being used by the world’s leading companies <a href="https://datasparq.ai/our-work">right now</a>!
Here are some of our best notebooks to get started:
<h4>1. Supply Chain - Queueing Model</h4>
This notebook simulates the supply chain of a pizza factory. Scenarios like these suffer from complexity and unpredictability. How do we prevent bottlenecks and ensure we’re producing as much as possible?
<figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*KZj7m4JvjcpGm-wB3M-R5Q.gif"></figure><a href="https://play-ml.datasparq.ai/article/supply_chain">Check it out here</a>.
<h4>2. Dynamic Pricing - Taxi Company</h4>
Ever been shocked to see the price of a taxi shoot up during rush hour? This notebook explores how the supply and demand of taxis can be balanced by adjusting the price in real-time and the algorithms used to optimise it.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*q4ykp4CUkcNR0WpQ301LKg.gif"></figure><a href="https://play-ml.datasparq.ai/article/pricing_algorithm_v3">View the notebook here</a>.
<h4>3. Vehicle Routing</h4>
How does a delivery company optimise their routes to minimise cost and get you your package sooner? It’s an increasingly complex problem, with an ever increasing number of variables. We’ve come up with an approach that beats commercially available software.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*iyBbx3F2iS1xJHYO0KwI3w.gif"></figure><a href="https://play-ml.datasparq.ai/article/vehicle_routing">Try it out here!</a> Or <a href="https://datasparq.ai/vehicle-routing-problem">watch the video</a>, where two of our top Data Scientists talk through the problem.
<h4>Help us Spread the Word</h4>
We want everyone to have insight into the algorithms and techniques in use all around them. If you like our content, please share it!
<h4>👉 <a href="https://play-ml.datasparq.ai/">play-ml.datasparq.ai</a>
</h4>
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=78566a82faa0" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/what-is-play-ml-78566a82faa0">What Is PlayML?</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

What Is PlayML?

Out of all the languages I’ve used, Go is the best. I find myself saying this to a lot of people, so here’s my attempt to explain why.
My perspective comes from years of data engineering with Python and front-end development with JavaScript/TypeScript. I’ve been exposed to a fair amount of C, C++, Java, C#, and R, and have formed a lot of opinions about the languages themselves, as well as their implementations and ecosystems.
<h4>It’s Clean</h4>
Here’s the ‘hello world’ program for Go. It’s like many others:
<pre>package main import "fmt" func main() { fmt.Println("Hello, world!") }</pre>
There’s no extra fluff like void , int main(), or return 0;. Instead, these are implied by the lack of arguments or return type in the function definition. Someone with no knowledge of the Go language can easily understand what’s going on.
There are no globally defined functions used; every function comes from a named package. This makes it vastly easier to follow than C++ or Java.
Public functions must start with capital letters, which avoids the need for public or private everywhere, and allows me to see which functions are public at a glance, and without seeing the definition.
Here’s a loop over a slice (a list):
<pre>for index, item := range myList { fmt.Print(item) }</pre>
Here’s a while loop. It’s still a for loop but it uses a condition rather than a range:
<pre>sum := 0 for sum &lt; 1000 { sum += sum }</pre>
There are little syntactic shortcuts like this everywhere.
<h4>It’s Fast</h4>
Surprisingly fast.
<h4>It’s Forgiving</h4>
I mentioned it’s fast, but for someone like me who can’t write C very well, it’s faster than any other language. This is because it optimises things for you. Forgot to make something a pointer? No problem!
<h4>Slices</h4>
Slices make arrays easier by abstracting away management of array length. They remain very fast but are also forgiving, providing a perfect middle ground between a Python list and a C++ array. Fixed length arrays are also available if I need them.
<pre>var s = []int{1, 2, 3}</pre>
<pre>s = append(s, 4) // slice grows in size</pre>
<pre>fmt.Println(s) // [1 2 3 4]</pre>
<h4>Goroutines
</h4>
Goroutines are lightweight threads. They make parallelising code very easy and the syntax is much cleaner than most other languages, many of which have added concurrency as an afterthought.
They are as simple as they could possibly be. Just define any function as normal and use the go keyword.
<pre>go listenForPings()</pre>
<h4>Channels</h4>
Sometimes you need to send information to a goroutine after you’ve started it, or get something to wait for a message before running. Channels do all of that and more.
I can make a channel that sends strings with ch := make(chan string). Then I can pass it to a function with go x.monitor(ch). My function (now running in a goroutine) will send messages to this channel (note the &lt;- syntax ).
<pre>func (x* HeartBeatDetector) monitor(channel chan string) { if time.Since(x.Last) &gt; x.Rate { // send name to channel to report dead process channel &lt;- x.Name } ... }</pre>
Other functions could read the messages from this channel. This for loop will keep going until the channel is closed with close(ch):
<pre>for d := range ch { fmt.Printf("Oh dear, %v died!\n", d) }</pre>
<h4>Error Handling</h4>
No more try/catch blocks. Now errors are returned by any function that could result in an error:
<pre>err, result := myFunc() if err != nil { panic(err) }</pre>
This may look ugly to a Python user, as there are now three extra lines of code for what is essentially an unhandled error, but at least now I (and my IDE) can see exactly what’s going to happen if there’s an error, so I won’t miss anything.
Here’s some more precise error handling, where I check the type of the error:
<pre>if err := Foo(); err != nil { switch e := err.(type) { case *SyntaxError: // do something interesting with e.Line and e.Col case *InternalError: // abort and file an issue default: log.Println(e) } }</pre>
<h4>Static Types</h4>
In my experience, typing ultimately makes code easier to write and understand. The downside is that it can lead to a bulky codebase. In Go, things are fairly clean.
I can define a type like this:
<pre>type item struct { id int value string }</pre>
Then define a method for my type:
<pre>func (i *item) String() string { return i.value }</pre>
Then define a function that uses my type:
<pre>func ConcatItems(a item, d item) { return a.String() + b.String() }</pre>
I also need a method to create new instances of my type. The convention is to use the following pattern, where a function New creates the type defined within that module:
<pre>func New(id int, value string) item { i := item{id, value} return i }</pre>
<h4>JSON</h4>
I can define how a type should be represented in JSON, then read a JSON string and convert it directly to my type. Validation is automatic.
<pre>type Mission struct { Name string `json:"name"` Services []Service `json:"services"` Stages []Stage `json:"stages"` Params map[string]string `json:"params"` isValid bool }</pre>
<pre>func NewFromJSON(jsonString []byte) Mission { var m Mission json.Unmarshal(jsonString, &amp;m) return m }</pre>
<h4>Easy to Compile</h4>
I mean this both in general (simple command: go build, helpful error messages, fast compile times, easy install in the first place) and in terms of all the different operating systems and architectures I can compile to, including Web Assembly, which has allowed me to <a href="https://mattsimmons1.github.io/bb/playground/">run my Go package natively in the browser</a>!
I’ve used a simple <a href="https://github.com/MattSimmons1/bb/blob/main/.github/workflows/go.yml">build pipeline on GitHub</a> to build my packages for multiple architectures. It compiles binary distributions for 5 different platforms, and I didn’t have to configure anything.
<h4>Runes</h4>
Runes represent unicode codepoints, which get stored as int32. This means I can treat a and 你 in exactly the same way, even though a has one byte and 你 has 3 bytes. Thanks to runes, I can confidently parse text containing non-ASCII characters without messing anything up.
Go’s syntax treats 's' as a rune, and "s" as a string of length 1. 'hello' would be invalid because it has multiple characters and therefore can’t be a rune. It only took a few minutes to get used to this, and it’s really nice to work with.
<h4>go get</h4>
I’ve gone through the process of turning Python code into packages and uploading to PyPi a few times now. It’s still confusing, and takes me a while.
With Go there’s no confusion. The command is 5 letters. The package location is the repo name, the install location is the repo name, the import path is the repo name. Very simple.
<pre>go get github.com/MattSimmons1/bb</pre>
It also compiles and installs the package automatically. I appreciate this as it allows people to install my program with a single line.
<h4>Built-In HTTP Server</h4>
Admittedly, I would rather use Python’s Flask or FastAPI for most use cases, but Go’s http server is very light weight. I’ve used it countless times.
<pre>http.HandleFunc("/", serveHome) if err := http.ListenAndServe(":8080", nil); err != nil { panic(err) }</pre>
<h4>Requests</h4>
The http module does what you’d expect. It’s nice that I don’t need to install any additional modules.
<pre>postBody, _ := json.Marshal(map[string]string{ "foo": "bar", })</pre>
<pre>resp := http.Post("localhost:5000/api", "application/json", bytes.NewBuffer(postBody))</pre>
<pre>defer resp.Body.Close() body := ioutil.ReadAll(resp.Body) var res []Response json.Unmarshal(body, &amp;res)</pre>
If you need to do anything over UDP, TCP, DNS, or Unix Sockets, you can use net. Here I’m sending a string over UDP in the same way I would send a string to a file.
<pre>conn, err := net.Dial(“udp”, “127.0.0.1:49161”)</pre>
<pre>fmt.Fprintf(conn, "03C;13C;24C") // send data</pre>
<h4>Cobra</h4>
<a href="https://github.com/spf13/cobra">Cobra</a> is a library that can turn a go project into an easy to use command line tool in minutes. It’s used like this:
<pre>func main() { if err := func() (rootCmd *cobra.Command) { rootCmd = &amp;cobra.Command{ Use: "my-cli", Short: "My Tool's Command Line Interface", Args: cobra.MinimumNArgs(1), Run: func(c *cobra.Command, args []string) { DoStuff(args[0]) // my function return }, }</pre>
<pre> rootCmd.AddCommand(func() (createCmd *cobra.Command) { createCmd = &amp;cobra.Command{ Use: "version", Short: "Print the version number", Run: func(c *cobra.Command, args []string) { fmt.Println("v0.1.0") }, } return }())</pre>
<pre> return }().Execute(); err != nil { log.Panicln(err) } }</pre>
I could then run my-cli hello to run the main program, my-cli version to run a subcommand, or my-cli help (a subcommand Cobra made for me).
If I were to just run my-cli with no arguments, I would see:
<pre>Error: requires at least 1 arg(s), only received 0 Usage: my-cli [flags] my-cli [command]</pre>
<pre>Available Commands: help Help about any command version Print the version number</pre>
I can easily add all of the flags and subcommands I need, and don’t need to worry about overcomplicating my code. <a href="https://github.com/spf13/cobra/blob/master/projects_using_cobra.md">Many projects use Cobra</a>, including Kubernetes, so I can be confident that the interface for my tool will be familiar to lots of developers.
<h4>time</h4>
Go comes with the two types time.Time and time.Duration. Both give you nanosecond precision and are really easy to work with.
Use time.Now() to get the current time. If you want to know the duration since that time, use d := time.Since(t1), then format that duration however you like, or just print it’s value and it will automatically be formatted in a sensible way.
Unlike Python, I don’t have to convert between datetime.date, datetime.datetime, datetime.time, pandas.Timestamp, and time.struct_time. There’s just one time type that does everything I need it to.
<h4>Mutex</h4>
The mutex library allows me to easily guarantee that my Goroutines won’t try to update the same value at the same time. This isn’t relevant without regular use of Goroutines, but it’s very powerful.
Here I’ve given my type a property called mux of type sync.Mutex for making sure that multiple go routines can read and write to last, which records the last time a heartbeat was detected.
<pre>func (x *HeartbeatDetector) detected() { defer x.mux.Unlock() fmt.Printf("❤️") x.mux.Lock() x.last = time.Now() }</pre>
I also used the defer keyword here to make sure the mutex always unlocks when the function ends.
<h4>Mascot</h4>
Go’s mascot perfectly represents the philosophy behind the language.
It’s simple. It has everything it needs and nothing extra. There’s only a few basic features shown, but you can understand what it is right away.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ztwC5SUq0Q26MBizwC4NxQ.png"></figure>I can’t think of a better mascot in the world of software.
<h4>Developer Experience</h4>
If you’re using a good IDE (I strongly recommend GoLand), it will point out every potential issue to you as you code, so you’ll practically never have any unexpected errors. It shows the types of every variable and every unhandled error.
The most helpful thing it does is automatically add every module I reference to my import statement at the top of the file. If it weren’t for this, I would be wasting time trying to remember the location of every module, or just forget to import and get errors when I try to run.
<h4>Tutorial</h4>
<a href="https://gobyexample.com/">gobyexample.com</a> provides the perfect way to quickly learn a new language. It starts simple, goes through each concept one at a time, and lets you run code in your browser.
<h4>Conclusion</h4>
In conclusion, Go is amazing. It should be part of everybody’s arsenal, along with Python (for simple scripting/data wrangling) and JS (for front-end). Go is perfect for anything that needs to be reliable and high performance, such as a Microservice, developer tool like Kubernetes or Terraform, or Data Science tool.
If you’d like any more tips you can find me on Twitter: <a href="https://twitter.com/MattSimmons01">@MattSimmons01</a> &amp; <a href="https://twitter.com/DatasparqAI">@DatasparqAI</a>.
Reach out to us at <a href="https://datasparq.ai/contact">datasparq.ai/contact</a> for advice on solving a problem with Data Science!
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=8583e73895d3" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/why-i-like-go-so-much-with-examples-8583e73895d3">Why I like Go so much, with examples</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Why I like Go so much, with examples

<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6PhIRwYf86sTsQg5"><figcaption>Photo by <a href="https://unsplash.com/@popnzebra?utm_source=medium&amp;utm_medium=referral">Pop &amp; Zebra</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure>Imagine a large web portal is reaching out to you to create an AI solution that will be recommending the most relevant articles to users. That’s exactly the task we have been tackling in Datasparq recently. We needed to determine which articles had multiple parts, and what order they should go in based on the titles alone so that the next article in the sequence can always be recommended.
But articles are not the only example where you can come across sequential data. Consider movies and TV series for instance — they would not have produced Avatar 2, 3, 4 and 5 if the first film had not been a success. In this article, we are going to replicate the solution we devised and create a BigQuery table of movie sequels so that we know which movie follows the preceding one e.g. Avatar 2 comes after Avatar and Star Wars: The Rise of Skywalker is the continuation of Star Wars: The Last Jedi.
We use “IMDb movies extensive dataset” available on Kaggle: <a href="https://www.kaggle.com/stefanoleone992/imdb-extensive-dataset">https://www.kaggle.com/stefanoleone992/imdb-extensive-dataset</a>, which contains over 85,000 movie titles. This massive dataset comprises four smaller chunks, namely IMDB movies.csv, IMDB names.csv, IMDB ratings.csv, and IMDB title_principals.csv. We are interested in the latter because this is the one that contains the titles that we will parse and from which we will extract sequences. Having said that, let’s get started!
<figure><img alt="" src="https://cdn-images-1.medium.com/max/938/1*kpWnI2NCK2K6CIKsp7WkZA.png"><figcaption>How would you know these two movies are related if there was not any text on the posters?</figcaption></figure><h3>Setting things up</h3>
To analyse this dataset, we will be using BigQuery which is one of the key GCP tools. Although this is a paid service, everybody is eligible for $300 credit to explore this massive toolset (which is way more than enough for the purposes of this project).
To load the data, we first need to create a dataset in which we insert the table. Specifying the location, choose the one which is the closest to you, so that data latency will be the lowest, and queries will be executed faster. We will be creating a table from the .csv containing all data. For convenience, it is best to upload this file into the dedicated bucket on Google Cloud Storage (GCS).
Letting it figure out the schema automatically will probably raise an error because some of the columns do not have consistent data. The (not-ideal) solution we are going to apply now is to set these columns with the type of STRING. To save your time defining everything manually, just copy and paste the following to Schema as text.
<pre>imdb_title_id:STRING, title:STRING, original_title:STRING, year:STRING, date_published:STRING, genre:STRING, duration:INTEGER, country:STRING, language:STRING, director:STRING, writer:STRING, production_company:STRING, actors:STRING, description:STRING, avg_vote:FLOAT, votes:INTEGER, budget:STRING, usa_gross_income:STRING, worlwide_gross_income:STRING, metascore:STRING, reviews_from_users:FLOAT, reviews_from_critics:FLOAT</pre>
<h3>Levenshtein distance</h3>
Now we are moving to the cornerstone of this article — Levenshtein distance. In the simplest words, this metric tells us how much one text string is different from another i.e. how many characters do we need to change in one string to make it identical to the other.
Unfortunately, there is no ready-made Levenshtein distance function to use in BigQuery so we need to create one on our own. BigQuery supports creating user-defined functions (UDFs) written as SQL expression or JavaScript code. Luckily for us, Andrei wrote the Levenshtein distance function a couple of years back and published it on GitHub under the MIT License: <a href="https://gist.github.com/andrei-m/982927">https://gist.github.com/andrei-m/982927</a>. We are going to use his bit of code. However — we will implement a few changes.
Currently the code counts the exact Levenshtein distance, regardless of how big it could be. Considering the huge amount of records present within the dataset this would be a very computationally heavy process involving lots of time to run the whole query. Moreover, this would consume a much larger amount of resource which would consequently lead to higher bills to pay for GCP (or to exhausting the free $300 credit much quicker).
The thing is, we do not really care about the exact Levenshtein distance number because as soon as it crosses the determined boundary, it is beyond our limit anyway. To address that, and significantly speed up the query execution, we are going to return the max set boundary for the given title, as soon as it is crossed. Implementation of these changes will allow us to execute the code approx 100x faster!
<a href="https://medium.com/media/da854d34ed454d83b93ec2b0b357716f/href">https://medium.com/media/da854d34ed454d83b93ec2b0b357716f/href</a><h3>Data cleansing</h3>
To speed up performance and increase readability, we don’t want to select all the columns from the dataset but rather focus on these (which can be useful in the later stages). The most appropriate ones in our case will be imdb_title_id, title, director, and date_published.
After a quick dataset exploration, we realise the sequence is expressed in several ways. Below are a few examples.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/266/1*3RAoZz9cuYSRPDa82_B_PA.png"><figcaption>Figure 1 — sequence at the end of the title</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/278/1*Y75i5c1erGCNyPGUXljhCw.png"><figcaption>Figure 2 — sequence written as a roman numeral</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/488/1*rjPwvebCFNlKFq61RIXjQQ.png"><figcaption>Figure 3 — sequence in the middle</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/1*oL2dh7YV81rLTp6yKET6GQ.png"><figcaption>Figure 4 — sequence at the beginning of the title</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/528/1*YEdKAHzrOPe5h2ZrqyIRaA.png"><figcaption>Figure 5 — sequence as the year at the end</figcaption></figure>Data engineering involves a lot of customisation of the data we work with. To extract all sequences from the various titles, we will need to adjust our query to catch all of the cases.
As was already mentioned, Levenshtein distance checks how many characters we need to change in one string to make it identical to the other one. What we strive for, is to find the golden mean — in terms of how many characters we allow to be different without having a negative impact on our final result. We do not want to set it too high, as we do not expect the next sequel to have a totally different title. It’s just the sequence that will be different. We also want to minimise the number of comparisons, to avoid extra costs associated with the query processing and to make it run faster.
And here comes the problem… If we do not set the distance high enough, we will miss plenty of movies that are actually sequential e.g. those in Figure 3, which list the subtitle as well as the title. Fortunately, there is a simple workaround we can use in that situation. We just have to REPLACE() these substrings with empty strings so taking Figure 3 as an example; “Destination Dubai” will be deleted leaving just “The Wedding Party 2”. The solution works perfectly fine, removing what is unnecessary and just keeping the string with a valuable number.
<a href="https://medium.com/media/302df79c6198e582e304122992159196/href">https://medium.com/media/302df79c6198e582e304122992159196/href</a><h4>Roman numerals to Arabic numbers:</h4>
Here comes another tricky bit. We cannot just catch the sequence written as a Roman numeral and REPLACE()it with the Arabic one because this will only pass muster in a very simple situation e.g. when the roman numeral is one of I, V, X, etc. What can we do then? Well, this is another time we can make use of UDFs and create such a method that will locate the Roman numeral, find an Arabic representation of it, and finally replace it. That is exactly what Mikhail did in this StackOverflow post:
<a href="https://stackoverflow.com/questions/69771597/what-is-the-best-way-of-converting-roman-numerals-to-arabic-ones-in-bigquery">What is the best way of converting roman numerals to arabic ones in bigquery?</a>
After a little modification of Mikhail’s code to suit our needs, this is what we are going to use:
<a href="https://medium.com/media/f43ce234a2ce501b056ca185896b3400/href">https://medium.com/media/f43ce234a2ce501b056ca185896b3400/href</a>and subsequently this…
<a href="https://medium.com/media/27da95c22f9317066d392fab9f126be6/href">https://medium.com/media/27da95c22f9317066d392fab9f126be6/href</a><h4>Sequence extraction:</h4>
Contrary to what you may have thought about extracting sequences, we do not need to define another fancy UDF :) Multiple CASE statements with a simple Regex do this job pretty well. As mentioned before, they just need to be properly adjusted to catch every way the sequence is expressed.
<a href="https://medium.com/media/92785253671fe2b8331babf3c438d74a/href">https://medium.com/media/92785253671fe2b8331babf3c438d74a/href</a><h4>Levenshtein distance in practise:</h4>
As we already have the Levenshtein distance method, it is now time to use it in practice. To be able to compare one text string to another, we need to double the data by joining a table to itself using CROSS JOIN.
This operation can be very computationally heavy and take ages to run, hence the query optimisation is key, and that is why we did not want to count the precise value of Levenshtein distance once it crosses our max_boundary.
Although by using INNER JOIN, we could have the result-set produced quicker, we would need to specify what we should join the tables on. The only options would be either on director or writer. However, there are lots of sequels where both of them are different. Joining tables based on them may not fully represent our result-set, as we would probably just obtain its fraction. We would also not join the tables based on movie ids, as we would not want to compare movies to themselves.
As was said before, there is no one-size-fits-all value for Levenshtein distance, and it should always be determined with regard to the data and the problem we are working on. For this specific case, I found allowing up to 3 characters to be different a reasonable number but I highly recommend experimenting with this value on your own, if only to see how the final output can be dramatically different!
<a href="https://medium.com/media/4995b0dda8040d2481fd85456c190dc2/href">https://medium.com/media/4995b0dda8040d2481fd85456c190dc2/href</a><h4>Determining sequentiality:</h4>
To clearly indicate whether the movie is a subsequent sequel, it would be nice to add a column and express sequentiality in the form of a simple Boolean statement; TRUE — this movie is the continuation of the previous one, FALSE — no it’s not. To do so, we will just need to form specific case statements, of how the sequentiality should be determined based on seq_1, seq_2, and next_by_date.
We know for sure, in the case where seq_2 is greater by one than seq_1, we want to mark this movie as the next part. Not having first parts explicitly marked as 1, slightly complicates this task and forces us to form a slightly more complex WHEN statement: WHEN seq_1 = 0 THEN seq_1 + 1 = seq_2.
Although this should be enough in most cases, a subset of such movies may exist where the titles are near identical, the movies have been produced by the same director, and contain a sequence in the title. This is a dangerous case, where a totally different movie may be declared as a continuation of another one.
To prevent that, we are going to use next_in_sequence parameter which tells us what the subsequent movie produced by the same director was with respect to the date. To make things clear — for sequence determination, we cannot only rely on this parameter. There may have been a director who produced a couple of other movies in the gap between the sequels of the given one. This parameter, however, works well as an additional layer of certainty where the next sequel is the correct one. After incorporating it, our statement looks like this:
<pre>WHEN seq_1 = 0 AND next_by_date = 1 THEN next_by_date + 1 = seq_2 OR seq_1 + 1 = seq_2</pre>
Finally, if neither of these two statements above returns TRUE, we want to classify that movie as non-sequential. Expression seq_1 = next_by_date will do the job.
In the end our code should look something like this:
<a href="https://medium.com/media/e2bf99b42652d749978a39c4e2f4eda4/href">https://medium.com/media/e2bf99b42652d749978a39c4e2f4eda4/href</a><h4>Final result:</h4>
Once we finally have all the bits done, we can combine the puzzles using WITH AS clause and produce the table. In the final SELECT statement, we are going to select all columns from the last subquery, determine the Levenshtein distance value and eliminate those rows that have empty strings in their titles (arising out of our previous REPLACE statements). For readability, we can also ORDER the records BY title_1.
Our table of sequential movies should look something like this and has well-over 1000 records:
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mK4_6I1XRn-ixFcczNxJlQ.png"><figcaption>Figure 6 — Final table</figcaption></figure>To find out more about Datasparq, <a href="http://info@datasparq.ai/">please get in touch</a> or go to our <a href="https://datasparq.ai/contact">website</a>. We would be delighted to hear from you!!
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=4b6653b17be6" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/levenshtein-distance-as-a-remedy-for-sequential-data-4b6653b17be6">Levenshtein distance as a remedy for sequential data</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Levenshtein distance as a remedy for sequential data

<blockquote>Search for something that works and then remember it. Search and memory is the essence of Reinforcement Learning — Andrew Barto
</blockquote>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*O45BsOLAKxpp8kfi.jpg"><figcaption>Found it! Inflatable decoy tanks were deployed on the south coast of England during WWII to fool the enemy into believing that the real D-Day landings were taking place around Calais</figcaption></figure>As a Data Science team, we were lucky enough to get into Reinforcement Learning [1] or RL a couple of years ago when we started looking at optimal pricing for car parking spaces at an international airport [2].
Airports and Dynamic Pricing
It’s an interesting problem, you have a fixed resource, the car park spaces themselves, and you have customer demand which varies over time — largely with demand for airline travel. You also give people the ability to book spaces well ahead of time. What price do you set?
<figure><img alt="An airport car park with aeroplane flying overhead" src="https://cdn-images-1.medium.com/max/1024/0*gxFrGul2iXAqrieH.jpg"><figcaption>Airport car parks presents a challenge for optimal pricing</figcaption></figure>Imagine you run such a car park and have the power to set prices. If you wait a couple of days, you might get a higher price from another customer for the same date. If you lower your price for that date because you haven’t sold the spaces and the date is approaching, your customers might quickly learn that waiting is the best policy and not book when they first check the car park website.
Furthermore, customers that book for a weekend might well be families or tourists looking for a quick break who are perhaps booking with a low cost airline. If they’ve paid £67 for a return trip to Majorca, they’re likely to think that £75 for a weekend parking spot is a little on the high side. Whereas your business traveller might think that is entirely reasonable or just not an expense they’ll be picking up at the end of the day.
The ability to learn what price works for a given demand, for a given day of the year, with a given booking lead time is a good example of dynamic pricing.
Dynamic pricing is a growing area of data science and a long-standing problem in operations research and one that is enjoying much more attention as marketplaces themselves become more dynamic.
What makes these problems amenable to reinforcement learning is vast event data sets around customer interactions. This gives an RL tool the ability to deduce the demand level (as well as, potentially, need and urgency) for a particular cohort of customers and thus find a customer specific price that will lead to a well used resource.
Why dynamic pricing?
Before we get into the reinforcement learning and how it can power dynamic pricing, we need to understand a few use-cases for why prices might need to change in more general settings.
<ul>
<li>
Increased demand/reduced supply. As in any economic model, if supply does not match demand for a product then the market may be able to sustain a higher price.</li>
<li>
Expiring products. Anything that has a limited sale window is likely to attract varying a price, whether it’s a hotel room for tonight, a loaf of bread that’s about to reach its expiry date, or a plane ticket on a flight. None of these can be sold after the window has passed.</li>
<li>
Seasonality. Some products are much more desired at certain times of day or times of year. Some may not be available at all at the wrong time. Christmas decorations typically get discounted heavily after Christmas as retailers don’t want to stock them for the whole subsequent year waiting for them to sell.</li>
<li>
Cost of production/supply. Some products cost more to produce on demand than on the normal production run. For example Brompton, the folding bike manufacturer based in London, typically has a 5 week lead time for a normal folding bike but that can be reduced to 5 days with tracked delivery if you are prepared to pay a premium for a model. The implication is that there is a reason to charge a varying price more closely linked to the cost of production (and possibly the customer’s willingness to pay for a quick delivery — see below).</li>
<li>
Price by customer. Possibly the most contentious reason to charge a dynamic price — certainly in mass retail — is the act of setting a different price for the same product to different customers. Sometimes this is veneered over by selling in different markets, e.g. electric cars in the US versus the EU market. But within a market, it may be desirable to identify customers who are time-limited and are not looking at competitor products, and may be willing to pay more for a standard product.</li>
</ul>
Varying a price to reflect super-high demand or capture a high need customer is not always the right thing to do. For instance, airlines charging thousands of pounds for seats on the last flight that makes it home before an enforced quarantine period starts or a taxi company surging prices in the face of a civil emergency — can lead to considerable long-lasting reputational damage. Important to consider— is it the ethical thing to do?
Reinforcement Learning and what to watch out for!
We’ve been working on a dynamic pricing project for the last few months. It’s a market-place environment where customers are exposed to a set of agents offering a service and we have to work out what price to set. The marketplace takes a cut of the fee paid and the service providers get the rest.
Basic features:
<ol>
<li>Customer demand is seasonal and varies dramatically across the day — it is also price sensitive.</li>
<li>The service providers are a limiting resource — if they’re working for a customer they can’t be doing other jobs for other customers.</li>
<li>The higher the price, the more the service providers want to offer the service and the quicker a match is made.</li>
<li>The lower the price, the better for the customer — of course — but also the longer they may be kept waiting for a match with a possible service provider and indeed they may get no match at all.</li>
</ol>
How do you set prices in such a setting so that you offer a fair price to the service providers and yet don’t fleece the customers. Well you can use reinforcement learning. We don’t know the price that is appropriate but we can set one and see what impact it has on the market — then we can learn and in the next epoch adjust accordingly.
<blockquote>Reinforcement learning at its root, is just memoized context-sensitive search. You do some operation and then you remember the result and then the next time you have to do that, you look it up — Rich Sutton
</blockquote>
If you read any reinforcement learning text book, you’ll find a diagram very much like this describing the basic principles. In this instance, the agent is the marketplace, the action is the ability to set a price and offer it to the customer, the state is the state of the marketplace (I know that’s self referential but we’ll revisit that) and the reward is a measure of success from having made a successful match between customer and service provider. Again we’ll come back to this because it’s not straightforward and it will drive the whole optimisation of the system. There can be a choice between many possible actions reflecting the possible prices that may be offered in the marketplace. Each action may lead to a distinct change of state and a different reward associated with that change.
<figure><img alt="A feedback loop depicting an agent taking an action, and reacting to the reward and change of state from the environment that consumed the action." src="https://cdn-images-1.medium.com/max/984/1*nQBVz20wyXh1eezFG6cVMQ.png"><figcaption>The reinforcement learning loop. CC by-SA 4.0 Jeremy Bradley</figcaption></figure>So what does the Agent do — well the marketplace learns from successful matches and unsuccessful matches and adjusts the price (or not) accordingly as each new opportunity to make a match comes round. There are maybe 20,000 transactions a day and prices are set in near real time.
I think the three most important aspects to doing this kind of activity with reinforcement learning can be summarised with:
What’s in a state?
Quick primer: Reinforcement learning comes out of a branch of mathematics called dynamic programming which solves an underlying Markov Decision Process for a value function. Deriving a value function gives you a policy which in turn tells you which actions you should take in a given state.
That was lots of jargon so let’s break that down— a value function computes the value of each state of your system under a given action policy; knowing which states are high value is important when discovering a better policy. A policy is a pre-defined strategy for choosing which action to take from a given state, ideally to maximise your accumulated lifetime reward. A Markov Decision Process or MDP is a state transition system which defines the states, actions, rewards and next states for any system.
<figure><img alt="A Markov Decision Process probabilistic transition system depicting states in green and actions in brown. Two of the actions result in distinct rewards of -1 and +5." src="https://cdn-images-1.medium.com/max/1024/0*umbiYLQFjQxM0tQ6.png"><figcaption>A simple 3-state MDP with actions in brown and rewards +5 and -1 marked. CC by-SA 4.0 Waldo Alvarez</figcaption></figure>What’s this got to do with states? If the underlying mathematical model (MDP) has too many states, then the dynamic programming algorithm will take forever to converge (or a very long time anyway). i.e. you’ll never get a solution and you won’t know what action to take — or price to set in this case — to improve your overall lifetime reward.
It is very easy to inadvertently give yourself an enormous state space. We have a marketplace that spans 850 locations across the world and we want to set prices every 15 minutes, we have 20 levels of customer demand and 20 levels of service provider supply, and 20 possible price bands. That’s a little over 4.5 billion states right there!
Surprisingly this is not necessarily a disaster as you can represent your state space approximately using a deep learning neural network of some other ML model but it definitely adds to the complexity as we’ll see when we try to learn with it.
What does success look like?
Success is defined by your reward. You might think that success is easy to specify. Why not choose revenue or profit and watch the dollars roll in. Well yes and no. Let’s imagine we used revenue in our marketplace.
If I have some very rich customers, then I can set my prices very high and I may initially even make a lot of revenue. However, I have inadvertently made my market very brittle and subject to the whims of a few well-off customers who are now dominating the market. Every other customer is priced out and will probably never come back.
So let’s lower the prices so that I open myself up to more of my customer base. The prices are still high. The revenue is improved because the customer base is larger, but there are service providers who are now turning down small jobs that are too low in value in the hope that a higher value job will come along shortly. Many customers with smaller jobs are left waiting for an unacceptable period of time before they get a price match. But that is ignored by the reward metric as it only cares about revenue in this case.
Again this is a short term win. Those customers left hanging around probably never return to your marketplace even when they have larger jobs to offer. In the end, low customer satisfaction kills your business.
What we found is that a hybrid reward metric which captures both financial success and customer satisfaction will allow you to find a sweet-spot in your problem — keep the service providers happy with a decent revenue while keeping the customers happy with a good performance metric.
Can it learn?
Reinforcement learning works best when it learns as you might expect. Successful learning comes from convergence of the algorithm and gives you a consistent result for the value of being in a particular state.
If you’re using an explicit state space model (sometimes called tabular learning, so not a neural network) you need enough training data or learning episodes to visit each state many thousands of times to get converged learning. (Converged learning refers to the model having a consistent picture of what value the most important states and actions have).
Now we know why keeping track of the state space is so crucial. If you’re using tabular learning which is simple and has many benefits you have to have a pre-training set that is 100x or 1000x the number of states in the state space or at least the number of states you are likely to visit. Alternatively, if your model is learning from scratch, your learning feedback loop needs to visit all of those states 100s or 1000s of times to get converged learning.
If you’re using an approximate state deep learning model for very large state spaces, you won’t be surprised to learn that many gigabytes of training data are needed to train that model effectively.
Certainly, if your training data only visits each state once or twice, your learning is going to be very poor and your recommended prices or actions are not going to be great!
Wrapping up
I’ve really only touched upon reinforcement learning as a mechanism for learning optimal prices. There’s lots of benefit to getting it right — an algorithm that can learn autonomously and present the right price for even new unseen scenarios; an algorithm than can even look after customer satisfaction if the correct reward structures are put in place to guide learning.
There are many ways you can get yourself in a tangle. But keeping the state space at a reasonable size that can match the available training data you have (or are likely to encounter during learning) is a great start and defining an appropriate reward metric will help you drive your learning in the right direction while not making embarrassing decisions along the way.
This introduction is still only the tip of the iceberg but it reflects some of the key decisions we had to take when taking reinforcement learning from the text book [1] (which is quite excellent by the way!) to a real-life project. Other significant considerations include:
<ul>
<li>
exploration versus exploitation — at some point you’ll need to take a suboptimal decision to see whether it leads to a better policy that was previously unknown. This can trigger some interesting conversations with stakeholders!</li>
<li>
discount factors — how much do you value reward that can’t be realised for weeks or months versus reward you can access today?</li>
<li>
learning rates — how quickly should your learning system adapt to new evidence or is your data quite noisy, thus meaning you don’t want it to adapt to a blip by mistake.</li>
<li>
algorithm — where to start; tabular or approximate representation, Expected-SARSA or Q-learning. There are many techniques and pros and cons to each of these approaches but in general, start simple and build up complexity as you need it is good advice.</li>
<li>
off-policy versus on-policy learning — is your system able to learn as it discovers new states or is it going to have to be pre-trained off-line? Is your system able to implement the decisions it takes or is it only recommending the decisions to an operator who can decide to adopt or decline them? All these factors influence whether you need to use an on- or off-policy approach.</li>
<li>
time-varying rewards and demand — probably the hardest issue of all in reinforcement learning is time-heterogeneity. Your environment may change over time and the reward you see one day may differ the next day for the same action. If this means a change in the statistical properties of your system (rather than just noise or differences in samples from the same distribution), then your RL system needs to know to adapt to that. Censored memory and rapid learning rates are the order of the day here.</li>
</ul>
All of these features need addressing at one point or other in practice. <a href="mailto:jeremy.bradley@datasparq.ai">Leave me a comment</a> if you’d like to see a Part II on some of these more technical aspects of practical implementation of reinforcement learning.
And finally…
If you are interested in joining us at <a href="http://www.datasparq.ai/">Datasparq</a> for exciting projects like this or just finding out how your company can benefit from our Reinforcement Learning, Data Science and Engineering expertise, <a href="mailto:%20info@datasparq.ai">please get in touch</a>. We’ll be delighted to hear from you.
References
[1] Richard S. Sutton and Andrew G. Barto. <a href="https://www.andrew.cmu.edu/course/10-703/textbook/BartoSutton.pdf">Reinforcement Learning, An Introduction</a>. 2nd ed. MIT Press, 2018.
[2] Andreas Papayiannis, Paul V. Johnson, Dmitry Yumashev, Peter Duck. <a href="https://doi.org/10.1093/imaman/dpy015">Revenue management of airport car parks in continuous time</a>. IMA Journal of Management Mathematics. Jan 2019.
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=5f7fa3054446" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/war-stories-from-using-reinforcement-learning-for-dynamic-pricing-5f7fa3054446">War stories from using Reinforcement Learning for Dynamic Pricing</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

War stories from using Reinforcement Learning for Dynamic Pricing

Imagine you’re building thousands of rocket engines and spending billions of dollars on a quest to make humanity a multi-planetary species. If your manufacturing process isn’t as efficient as possible, you’ll run out of money before you get there. You probably need to optimise this process like crazy right? So why don’t we see if we can optimise our software in the same way…
(please ignore the fact that nobody needs to manufacture software, that’s why it’s called software not hardware, just bear with me)
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*qXCFgcRXosxgEthX"><figcaption>Photo by <a href="https://unsplash.com/@spacex?utm_source=medium&amp;utm_medium=referral">SpaceX</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure>In a recent interview, Elon Musk talked about how manufacturing was underrated and was the most difficult problem SpaceX had to solve.
<blockquote>“10,000% more work goes into the production system than the thing itself.”</blockquote>
<blockquote>“The amount of effort that goes into the design rounds down to zero relative to the amount of effort that goes into the manufacturing system.”</blockquote>
He went on to describe 5 steps used to optimise engineering for manufacturing, which emerged from the many successes and mistakes of rocket programs and Teslas in the past:
<h4>1. Make the requirements less dumb</h4>
The requirements are definitely dumb; it does not matter who gave them to you. Therefore, requirements get challenged, which allows for out-of-the-box thinking (see Cybertruck).
<blockquote>“Everyone’s wrong. No matter who you are, everyone is wrong some of the time.”</blockquote>
<blockquote>“All designs are wrong, it’s just a matter of how wrong.”</blockquote>
He also states that specific people should own requirements, not departments, so that they can be questioned much quicker.
<h4>2. Try very hard to delete the part or process</h4>
You can’t afford to add a part ‘just in case’. Musk encourages deleting as many parts as much as possible, even if you end up adding some back later.
<blockquote>“If you’re not occasionally adding things back in, you’re not deleting enough.”</blockquote>
<h4>3. Simplify and optimise the design</h4>
This step comes third, not first, for good reason. The critical point of this whole process is to not do this step until you’re absolutely sure it’s needed before you start wasting your time.
<blockquote>“The most common error of a smart engineer is to optimize something that should not exist.”</blockquote>
Elon stressed the importance of an engineer understanding the whole system to avoid optimising the wrong thing. He revealed that SpaceX engineers had made the mistake of putting enormous amounts of effort into reducing the weight of engine components, but not the weight of unused fuel, despite them being equivalent.
<h4>4. Accelerate cycle time</h4>
Here’s where you start to find issues, and have a chance to fix them before it’s too late. It’s like learning to run before you run a marathon.
<blockquote>“You’re moving too slowly, go faster! But don’t go faster until you’ve worked on the other three things first.”</blockquote>
<h4>5. Automate</h4>
Why is this separate from the fourth step? Because this assumes you wait until any and all issues have been found before removing all humans from the process.
It would be hugely costly to try to automate something that was overly complicated or wasn’t needed.
This is also the point where you should aim to remove in-process testing for parts that can be produced reliably. Musk states that a tester could be creating a choke point, and will always have some have false negatives.
<blockquote>“If things are getting to end-of-line testing, and are passing, then you don’t need to do in-process testing.”</blockquote>
We can summarise this process as the following:
Design → Question → Simplify → Optimise → Accelerate → Automate
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*jmz58whKh3ky9qS3"><figcaption>Photo by <a href="https://unsplash.com/@spacex?utm_source=medium&amp;utm_medium=referral">SpaceX</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>How does this relate to software?</h3>
So let’s say instead of building thousands of rockets, you’re developing the next big app/website/API. It’s a huge task. If you do everything right you’ll be bought by Google within 5 years, else you’ll take 15 years, never get feature-complete, spend all your money, and get overtaken by somebody else. How does this situation compare to SpaceX’s?
In terms of the processes and ways of thinking, there are a lot of parallels: it’s easy to get carried away solving the engineering problems and ignoring the bigger picture. Engineers tend to want to add loads of functionality that isn’t essential. They like optimise for speed and efficiency without being required to. They stick to a set of best practices and don’t usually question them.
<h4>Manufacturing ≈ Going Live</h4>
In the design stage your code only needs to work once. When you go live you’re running the same code billions of times, on millions of different computers, for years or decades. There’s a lot that could go badly: will you need constant patches, or tiny changes for every new use case? Will you need to spend forever teaching people how to use it? Will you be able to keep up with demand and scale? Only by solving these issues can you hope to actually achieve anything with your software.
Like with rockets, this is an underrated part of programming. An inexperienced software developer might assume their cobbled together script can be used in production with no changes. This is like building a rocket engine in your garage from spare parts and deciding you’re going to build 100,000 more in the exact same way.
<h4>Part = Script, Service, or Program</h4>
Cutting out an entire service from your app (e.g. deleting an email server because you can just use an API) is undeniably a great way to reduce complexity and go live faster.
<h4>
Cost = Cost?</h4>
For software, material cost is much lower, personnel cost is much higher, and there are additional computing costs. People often worry about their compute/network/storage bill, but the salaries of the people fixing bugs all day long are likely to be larger.
There’s also the opportunity cost of complexity. If a low priority feature requires a day in total to develop, deploy, debug, document, etc. over the course of a month, then you’ve missed the opportunity to start a killer feature, or present your product at a conference, or have a much needed office party!
<h4>Weight = Complexity</h4>
For rockets this doesn’t just mean weight of the part, it’s also how much thrust you can get for that weight. Adding complexity to a piece of software means more complex documentation, technical debt, bugs, deployment, etc., all of which increase costs but more importantly, slow you down and kill your momentum.
Reducing complexity allows you to go further with the same amount of resource.
<h4>Requirement = Requirement</h4>
Requirements often don’t get questioned, and they rarely have an individual owner. It’s far too easy to make assumptions about how software will be used and not seek out the opinion of an actual potential customer.
<h4>Feature = Feature</h4>
We’re all familiar with dubious feature requests. The Agile process helps to prevent low priority features from being worked on but doesn’t stop us from adding unnecessary complexity.
<h4>In-process testing = Unit tests</h4>
This one has really made me think. Elon’s right, we don’t need most of these unit tests. We’ve got hundreds that pass every time, and they sometimes get false negatives which take time to investigate. We could probably replace 90% of them with a single integration test.
(I won’t suggest getting rid of them though, just running them less often)
<h4>Colony on Mars = __________</h4>
It helps to know what you’re working towards. A clear and ambitious goal has certainly helped keep SpaceX focused on what’s important. What’s the ultimate end goal of your project? What will make you say ‘Mission accomplished’?
The optimisation of the productionisation process is vital for any software company. There’s a huge impact on time but it’s also the difference between being proud of your accomplishments, and being embarrassed. We’ve all seen projects that are being carried by one developer who knows how to fix the many reoccurring issues, and has given up hope of ever working on something interesting again.
I guarantee no app ever went live without a single issue, so there’s plenty of room to improve.
<h3>What Should We Be Doing Differently?</h3>
With all the parallels described above, I think we can take some learnings from the five steps. Looking back at some of my own projects that were difficult to get live, I can now see that we skipped some steps or did them in the wrong order, just as Elon admitted Tesla had done in the past. Based on this, here’s what I think we should all be doing differently:
<ul>
<li>Challenge all requirements, no matter how small. A common example would be “We’ve got streaming data therefore we need a streaming data pipeline” but in reality there’s no requirement for data to be processed in realtime. Micro-batch processing is much simpler and cheaper to build.</li>
<li>Pause once you have a working concept to ask which processes aren’t needed, and just delete them. Don’t spend time on a feature nobody cares about just because you want everything ‘production ready’.</li>
<li>Recognise the true cost of complexity. Any ‘part’ in your app that could be replaced with something much simpler must also be deleted.</li>
<li>Don’t optimise for the wrong thing. Is speed your priority or is it lowering cost? Should you spend your time improving features or adding more? Find a real person in the business who can explain what the priority is and why.</li>
<li>
Don’t optimise too soon. Don’t invent a framework before trying something the simple way first. Don’t define a data model when the data isn’t finalised. Be flexible to change throughout.</li>
<li>
Accelerate your code before you automate. You’ll find which parts require further optimisation, and uncover problems that would be impossible to fix after going live.</li>
</ul>
Most importantly, appreciate just how challenging it is to go from a great concept to a live app for a mass audience. Your approach to productionisation could determine your success or failure. Keep complexity low and momentum high, else you’ll never get to Mars.
The full text of the interview referenced can be found here: <a href="https://everydayastronaut.com/starbase-tour-and-interview-with-elon-musk/">https://everydayastronaut.com/starbase-tour-and-interview-with-elon-musk/</a>
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=a7963fef9887" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/elon-musks-5-steps-of-engineering-applied-to-software-a7963fef9887">What Can a Software Engineer Learn From a Rocket Engineer? Quite A Lot Actually.</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

What Can a Software Engineer Learn From a Rocket Engineer? Quite A Lot Actually.

A large part of our business is making valuable data science products a reality. I’m going to share some of what we have learnt on how to do this quickly whilst maintaining the highest quality.
Pandas alone won’t save you
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*7mU7TnQZRcHy51xc"><figcaption>Photo by <a href="https://unsplash.com/@wuyyyying?utm_source=medium&amp;utm_medium=referral">Ying Wu</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure>The first mistake is to believe that every data science project is only a data science problem. Multiple different roles have an important part to play.
The best and most efficient teams I have worked in are a combination of Data Science + Product + Engineering roles. That will not come as a surprise, but what makes a difference is ensuring that these roles have a part to play throughout the project. Unless the project is heavily standardised or a repeat of another — there is virtually no chance of finishing on time or to an acceptable standard by passing (or more commonly “throwing”) from one team to another.
A common example of where early team integration really shines is during feature generation.
<ul>
<li>Data features will often need to be re-run, re-configured and re-designed (multiple times)</li>
<li>New data sources (with valuable additional signal) need to be ingested and cleaned</li>
<li>Tests on various potential models will be repeated and reviewed when features change</li>
</ul>
A role diverse team will create repeatable processes from the outset and adhere to the boundaries of company infrastructure, ensuring that no costly logic “re-writes” are required when moving from test to live datasets. The benefits are often greater than the sum of parts — we develop data models supporting current project requirements and perhaps those of others too. This is just one of the areas where we have managed to cut weeks, if not months, out of projects without effecting quality.
MLOps products help, collaboration is better
Secondly, do not let anyone tell you that an MLOps product (Kubeflow, MLflow, Cloud provided, etc)+ Data Science are going to be all the tools your team will ever need.
Elements of Data Science projects are similar, especially in operations (monitoring, testing, processing, development environments etc). Although there are great open source and cloud based tools available, parts of your projects will be unique to your business.
If your business is software based this might be second nature. If not, having a strategy to share both code and documentation between projects is a fantastic accelerator. Make sure you bring relevant teams together to discuss lightweight governance, location and features of existing reusable parts.
Here is an example of our serverless components, we use these across several projects to reduce maintenance time and improve quality: <a href="https://datasparq.ai/elements-of-engineering">https://datasparq.ai/elements-of-engineering</a>
Think about the end at the beginning
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4PFHtzQVS7nvozbt"><figcaption>Photo by <a href="https://unsplash.com/@sigmund?utm_source=medium&amp;utm_medium=referral">Sigmund</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure>Finally, those with experience will know that finishing a data science project is as difficult as starting or building one. It turns out that APIs are not designed for human consumption. And, no team is likely to be happy manually running data processes, creating graphs or having similar conversations forever.
It is also possible that no two Data Science projects you run have the same results interface. A handful of examples we have completed include: APIs, web / mobile applications, static files, databases (all different kinds), messaging service notifications and more.
Finishing a project means delivering something that can be used in operations, sustainably. It’s well worth taking the time early on in a project to make sure your team either has or can acquire the skills needed to finish the project as they might be a little different to starting and developing. This is where product-oriented awareness and design helps round a project off in a way that drives impact for the customer.
Final thoughts
Do not let any of the above put you off! Data Science projects are extremely rewarding, fantastic investments and really enjoyable to work on. The extra effort invested in early team integration, code collaboration and building tricky results interfaces is often rewarded by increased adoption and usage.
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=1b380ff13ea5" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/data-science-projects-at-pace-1b380ff13ea5">Data Science projects at pace</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Data Science projects at pace

As technology and investment market trends converge, now is the time for PE firms to learn what AI really is.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mwnMr4VIcm2l4Dxka4ohhA.jpeg"><figcaption>Photo by <a href="https://unsplash.com/@yukato?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Yu Kato</a> on <a href="https://unsplash.com/s/photos/lens?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure>One clear trend in the investment world right now is the <a href="https://www.ft.com/content/cd9571a3-726c-4995-9954-23a8dcf12b19">growth of Private Equity deals</a>. The first half of 2021 has seen PE deals worth more than $500bn; helping to propel global mergers and acquisitions activity to an all-time high.
<a href="https://www.idc.com/getdoc.jsp?containerId=prUS47482321">The other trend is AI technology growth</a> — despite being around for decades, and growing significantly since the arrival of cloud computing earlier this millennium— AI is still growing. This trend isn’t particularly new, but it means that AI capabilities are becoming more common place in more businesses; and leading businesses are taking advantage of the benefits of AI to outperform their lagging competition.
Combining these trends, it’s no surprise that there’s an increasing amount of investment activity involving companies that have AI as a core component of their proposition and enterprise value. <a href="https://www.cbinsights.com/research/report/enterprise-ai-trends-2021/">AI companies raised a record $33 billion in equity funding in 2020</a>, and what’s more, an increasing number of companies claim to be “AI-first”; using cloud technology, data science and engineering to deploy AI into different aspects of their business to operate better.
Time for PE to learn about AI
If AI is deemed to be a crucial differentiator for a company’s proposition, or a major value lever given the rich, unique dataset the company has, then it’s important this is well understood when assessing the company’s valuation and investment potential. When considering a deal with an “AI-first” company, any investor needs to be clear on the existing and potential value of AI within that company.
<ul>
<li>Do buyers understand exactly what AI capabilities they’re buying?</li>
<li>How do buyers evaluate and leverage the claimed AI capabilities?</li>
<li>For data-rich companies that aren’t even claiming to use AI, how do buyers assess the potential value that AI could add to the acquisition?</li>
</ul>
Many investment brochures will advertise the organisation’s good data governance, rich data assets, scalable data platform, and leading AI capabilities that makes it a unique investment opportunity — but in many cases the truth may be it’s not so unique. After all, despite AI maturing in the last few years, it’s still like teenage sex — more companies are talking about it than actually doing it.
PE companies will use their preferred IT due-diligence and Commercial due-diligence partners to confirm a potential investment’s true capabilities, potential and risk; but these organisations rarely have the AI practitioner experience to robustly assess AI; and the remit of a typical IT DD and CDD exercise is too broad to provide any deep analysis within the AI domain.
The answer: AI Due-diligence
Given the increasing importance of rigorously evaluating a company’s AI capabilities before investing, and the lack of detail IT DD and CDD studies provide for this, DataSparQ is finding itself developing valuable AI Due-diligence reports on potential assets for PE investors. Whilst a PPT slide deck isn’t the traditional output of a DataSparQ project (we pride ourselves on developing and running operational AI solutions in the real world; our outputs are usually more Python than PPT), our deep AI practitioner expertise means we know what good looks like, and because we understand what it takes to deliver AI successfully in the real world, we’re in a privileged position to help others.
Our AI DD service consists of both a “backward-looking” confirmatory assessment of a potential investment’s AI capability, as well as a “forward-looking” analysis of how AI could be used to unlock further hidden value in that business. Different investments often have a different focus; for businesses with established AI capabilities that give them a unique competitive advantage, the confirmatory assessment is more important. For data-rich companies early in their analytics journey, the forward-looking analysis is often more valuable.
The areas of investigation during an AI DD include:
<ul>
<li>
Data diagnostic — a review of the data collected, stored and used by the business. Is it done systematically or ad-hoc? How accurate, relevant and complete is it? How is it catalogued and governed?</li>
<li>
Algorithm diagnostic — an assessment of the algorithms and analysis used. Are they appropriate (meeting requirements for accuracy, explainability, auditability, bias, customisation)? Are they robust? Will they scale and remain competitive as the business grows and the market matures? Are they compliant with industry rules and regulations?</li>
<li>
Technology diagnostic — a review of the technology systems and architecture used to build and run these AI capabilities. Are they resilient? Will they scale to support future growth requirements? Are they cost efficient? Are they secure?</li>
<li>
Capability diagnostic — an analysis of the business’s way of designing, building and running AI solutions. Do the right skill-sets exist? Is the capability unique and sustainable? Does the way of working encourage innovation?</li>
<li>
AI Opportunity review — a process to identify, validate and quantify high-value opportunities for AI yet to be exploited. What hidden value levers can AI unlock? What incremental value does AI offer over a more low-tech solution? How feasible is each opportunity? What’s a realistic delivery timetable for value capture?</li>
</ul>
Given a modest amount of time with the management team, and other key individuals in the business who might not normally be involved in traditional DD exercises, these questions can be answered within the timelines for most DD processes. It can be efficient for the AI DD provider to work alongside the IT DD and CDD providers so the same questions don’t have to be asked twice.
Bottom line
As AI matures and becomes a differentiating value lever in organisations, it’s increasingly important for investors to put this capability under the microscope when considering deals. This means PE companies need to start asking new questions, and learning more about AI than the buzzwords and headlines in order to know what questions to ask, let alone answer them.
The innovation of AI technologies and methods is fast and shows no sign of slowing. It will be an ongoing learning journey for investors to ensure their assessments remain relevant. As a result, AI practitioners like us may find ourselves increasingly doing PPT as much as Python as we become the go-to service providers for AI DD.
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=e2da04f94b8e" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/why-private-equity-needs-to-learn-about-ai-to-make-better-investment-decisions-e2da04f94b8e">Why Private Equity needs to learn about AI to make better investment decisions</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Why Private Equity needs to learn about AI to make better investment decisions

Humans don’t like risk and, as with any technology investment, a Machine Learning solution comes with its own set of risks. Will it predict the right thing, will it be accurate, will it be useable? In this post I’m going to take you through how we at Datasparq reduce risk through Proof of Value.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*IwpfqunBgcA0693VDTVP4g.jpeg"><figcaption>A machine learning solution to demand forecasting can lead to pretty, ordered, stocked shelves… and higher revenues! But will it work?</figcaption></figure>Our machine learning solutions are designed to allow people to perform beyond their potential, whether that’s making more accurate sales forecasts, creating effective retention strategies for customers, or focusing business development effort on leads likely to convert to wins. Machine learning solutions give people the benefit of the predictive power hidden away in data.
Machine learning has proven time and again to be effective at predicting the future by learning from the past, whether that’s <a href="https://www.bbc.co.uk/news/technology-57157566">identifying potential skin cancer</a>, or <a href="https://towardsdatascience.com/deep-dive-into-netflixs-recommender-system-341806ae3b48">helping you decide what to watch next on TV</a>. That’s not entirely surprising as these solutions are fundamentally doing what humans have evolved to do — observe, learn and predict, just with the advantage of being able to process vast amounts more data.
But just because there’s evidence something has worked before doesn’t necessarily mean it’s going to work in every situation.
This is why we start all of our machine learning projects with a Prove phase. It’s a short, targeted mission to reduce ambiguity and test assumptions to minimise risk. Over the course of a few weeks, we validate or test the following:
<ol>
<li>That there is a clear target variable for the prediction - we know what we’re predicting.</li>
<li>That there is a viable means for measuring value from the prediction</li>
<li>That there is predictive power in the data (signal in the noise)</li>
</ol>
1. Clear target variable for the prediction
The target variable is the variable in a dataset whose value is to be predicted using other variables. It’s pretty fundamental to any machine learning solution, and picking the wrong target variable can lead to significant wasted effort down the line.
Our goal here is to define which fields in the data will be used to form the ‘target variable’. This is an iterative process where we identify a target variable, then validate how a prediction of this target variable will be beneficial. This in turn raises new questions which allows us to loop back and idenfity a better target variable.
It sounds like it should be easy. Let’s look at demand forecasting as an example. Imagine you sell jeans in a high street store. In order to effectively manage your stock levels you need to make an estimate in advance of how many you’re going to sell. So a machine learning solution just needs to predict your sales. Simple, right?
In reality, defining the scope of prediction tends to be a significant chunk of work that requires us to deep dive into an organisation’s processes and value chain.
<ul>
<li>What product or service are we predicting demand for? Is it one product, or hundreds, each with different demand patterns?</li>
<li>For what period are we predicting demand — next week, month, year? Or maybe combinations of these, or maybe different products would benefit from different levels of prediction?</li>
<li>What is ‘demand’? Is it a placed order? A product taken off a shelf? What if that order was later cancelled, returned, or not paid for?</li>
<li>Do we need to care about who is making the demand — do we need to predict demand from a specific customer or location, or a demand that will be fulfilled by a specific fulfilment centre?</li>
<li>How far in advance would the prediction be required in order to be valuable to the business? Predicting tomorrow’s demand today is much easier than predicting for a day next month.</li>
</ul>
Some of these questions (the known unknowns) will have been answered prior to the Prove phase as part of our <a href="https://www.datasparq.ai/products/">SparQshop™</a>, others arise from deeper exploration of the data (the unknown unknowns!). All need to be answered if we’re to precisely define the target variable.
2. Measuring value
In order to optimise a model we need to know what metrics we’re using to measure performance. Understanding the key performance metric gets to the heart of some of the difficult questions around model optimisation, for example:
<ul>
<li>In a regression problem (where we are predicting a number, like product sales), what’s the relative value in over predicting vs under predicting? Underpredicting by 10% might have a significantly bigger cost impact than overpredicting by 10% (you can’t sell what you don’t have, you may though be able to store some excess stock for selling later).</li>
<li>In a classification problem (where we are predicting a binary outcome, like customer churn), what’s the impact of a true positive vs a false positive? If we predict a customer to a subscription service is going to leave (churn), they may get offered a discount to stay. If they take up this discounted offer that’s great if they would have otherwise left. However, if it was a false positive prediction and they were actually never going to leave, it’s a cost to the business.</li>
</ul>
Then there may be other factors specific to the problem being solved. In the demand forecasting example, is it more important to accurately predict demand for some products over others (maybe some have a shorter shelf life, or are more expensive to keep in stock)?
The goal here is to come up with a metric to measure model performance which relates directly to the value being created for the business. For example, for customer churn, that metric may be:
Profit ($) = R*TP — C*FP
Where R is the revenue from a true positive, C is the cost of a false positive, and TP &amp; FP are the true positive and false positive rates.
3. Predictive power in the data
This is where we create a baseline machine learning model and test the predictive power inherent in the data. This starts with some basic evaluation of the data — specifically is there enough historical data to be able to train and test a model?
Next we need to know where to start with the data. We’ll typically run a hypothesis workshop with people in your organisation who understand the data and the target variable. In the case of demand forecasting this would involve those close to the buying/selling operations to understand, from their experience, what factors affect demand. For example, we may learn that they see sales go up in summer, or are higher at shops in certain locations. From these hypotheses, we extract <a href="https://en.wikipedia.org/wiki/Feature_(machine_learning)">features</a> from the data, for example “store location”, or “average temperature over the previous 7 days”.
<figure><img alt="" src="https://cdn-images-1.medium.com/max/459/1*Uny-ym1ashJxGKzn1Gmwhw.png"><figcaption>thanks <a href="https://xkcd.com/license.html">XKCD</a></figcaption></figure>From here we can do some univariate analysis. Are there features which show predictive power on their own? <a href="https://www.tylervigen.com/spurious-correlations">Correlation ≠ causation</a> so success here isn’t proof of signal in the noise, but can help steer focus to the most useful areas of the data.
Once we have a handful of features we believe to be predictive, we can put them into a baseline model and test it. We measure the performance of the model by testing it against data it hasn’t seen. This is both out-of-time (given complete data up to 2018, can it predict what happened in 2019) and out-of-sample (given data for all time but removing data for certain shops, or products or price points at random, can it predict the gaps?).
The Prove phase isn’t designed to deliver a fully optimised model, our goal here is to demonstrate evidence that there is predictive power in the data and to get a sense of how much value might be gained through an optimised model. It’s not an exact science, but working closely with the subject matter experts gives the model the best chance of succeeding early on and it’s usually possible to prove value in this first phase, or at least get a good sense of whether it’s worth pursuing.
We’re good to go!
…not quite. The above activities bring clarity and definition to the model and it’s potential value but even the most accurate demand forecast is of absolutely no value unless it’s used.
The final piece we look at in the prove phase is how the solution will be used in operations. Are the stakeholders bought in to the idea of having a machine help with their jobs? Is is feasible to integrate the predictions with the systems/tools they use every day? What changes might be needed to operational processes once the predictions are live?
Even if these questions aren’t fully answered at this stage, having the discussion early on is another way to reduce risk.
If you think there may be an opportunity for machine learning to deliver value in your organisation and you’d like to chat about a low risk way to get started and prove the value, please get in touch at <a href="mailto:info@datasparq.ai">info@datasparq.ai</a>.
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=e61f9700ef58" width="1" height="1" alt=""><hr>
<a href="https://medium.com/datasparq-technology/proving-the-value-of-machine-learning-e61f9700ef58">How to prove the value of machine learning and avoid worthless models</a> was originally published in <a href="https://medium.com/datasparq-technology">Datasparq Technology</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Who we are

What we do

Work we've done

Contact us

Contact us

Get in touch

Come and see us

Follow us

Message us and see how we can help

Inspired? Join our team