The strengths and limitations of SQL ?

 

SQL and its many dialects                                                                 

There are many database systems that rely on SQL.

To name only the well-known ones: PostgreSQL, MySQL, MS SQL Server, Oracle, SQLite - And Cloud-specific: BigQuery, Redshift, Azure SQL, etc.

And therefore, there are many variations of SQL, which we will call “dialects” 😊.

The SQL language (and its dialects) is constantly evolving. This adaptability, this plasticity, and this simplicity are important elements of the success of SQL. This also explains its longevity (“since 1974”).

 

SQL, a language for everyone!                                                                   

Usually, programming languages are supposed to be used for programming purposes only, but SQL is proof to the contrary: it can be used by members of the marketing or sales team via a few queries to do analysis. SQL is also useful for the financial sector, as it is essential for navigating often countless data. As a result, we find SQL absolutely everywhere, including in the business layers, encapsulated, or “attached” to databases, or dataviz tools.

 

SQL is the past… But above all the future!                                             

SQL works well with old technologies, but also with those of tomorrow, the “hot technologies”! And it is the surprise that we expected a little less. Because we could have imagined that the blockchain, the Artificial Intelligence end up making the SQL obsolete. We cannot predict what will become of SQL in the coming years, but until now, SQL has shown remarkable resilience and has embraced new technologies with ease, taking advantage of its great plasticity and of its ease of use.

The main cloud databases use “enriched” SQL. But on a different note, MS Azure recently announced the launch of Azure SQL Database Ledger, a solution that combines the simplicity of SQL for centralized data storage, with blockchain for added trust. Another example is Dune Analytics, which allows free access to comprehensive crypto-analysis via simple SQL queries.

SQL is also present in solutions that rely on AI by allowing the injection of data to create and train machine learning models. The SQLFlow project leverages SQL to support AI tasks, including model training and evaluation.

Big Data platforms also use SQL as the main API to manage relational databases.

 

The limits of SQL                                                                                           

Almost all data technologies support SQL (or its dialects!) and allow the use of the SQL interface. We thus find encapsulated SQL practically everywhere, in ETLs, in dataviz tools, but also to feed spreadsheets from simple queries, etc. This language is so rich, so trivialized, that it systematically generates great complexity downstream:

- For teams in charge of Information Systems governance, reverse engineering to deconstruct information flows is often a challenge.

- For engineers in charge of application maintenance, this proliferation is an obstacle to proper control of information systems.

Automate reverse engineering in heterogeneous systems           

It is therefore essential to be able to automate the reverse engineering of SQL code within the system to fully understand where the information comes from, where it goes, and who ultimately accesses it.

Thus, it is possible to build the conditions for effective maintenance, and take full advantage of the potential of SQL. A data lineage solution that will both introspect the flows in the upper (dataviz) and lower (feeding) layers, which will identify all Adhoc accesses will have a certain interest in making SQL cohabit durably with the information systems of today and tomorrow.

ellipsys@ellipsys-bi

www.ellipsys-bi.com  

#datalineage 

#sql #plsql #tsql #redshift #bigquery