This May Patch Tuesday release is very much a “back-to-basics” update with just 78 patches for Microsoft Windows, Office, Visual Studio, and .NET. Notably, Microsoft has not released any patches for Microsoft Exchange Server or Microsoft SQL Server. Due to the concerns of publicly reported exploits for five Windows vulnerabilities, the Application Readiness team has recommended a “Patch Now” schedule for Windows and a standard release cadence for the other platforms. To help navigate these changes, the team from Readiness has provided a helpful infographic detailing the risks of deploying updates to each platform. Known issues There are still reports of issues with devices with Citrix Session Recording Agent (SRA) version 2411 installed on Windows 10 platforms. This is an ongoing issue, with no further reported fixes or updates from Citrix or Microsoft. Otherwise (at the time of writing), Microsoft has not reported any issues with this month’s update for its Windows desktop and server platforms. Major revisions and mitigations Microsoft has not published any major revisions or mitigations to its patches and security fixes for this May. Windows lifecycle and enforcement updates Microsoft has not published any enforcement updates this month. Testing guidance Each month, the team at Readiness analyzes the latest Patch Tuesday updates from Microsoft and provides detailed, actionable testing guidance. This guidance is based on assessing a large application portfolio and a comprehensive analysis of the Microsoft patches and their potential impact on Windows platforms and application deployments. We have broken the most significant changes into feature-based groupings to help with testing prioritization. The Readiness team recommends the following areas for testing for the May Patch Tuesday patch cycle: Remote Desktop, security, and identification Test your Remote Desktop Gateway configurations. Establish sessions through the gateway and reconnect a few times to ensure stability. Validate VPN creation, connection, and deletion. Also test fast reconnection and password change flows with PEAP-MSCHAPv2. Load system level crypto libraries and validate CheckSignatureInFile behavior using legacy (2011) certificates. Test secure boot scenarios, especially if running dual-boot with Linux. Ensure all logins work after this month’s updates. Run PowerShell modules with and without AppLocker policies to confirm policy enforcement integrity. Media and codecs Check your subtitles in MKV formats for Blu-ray playback. Test audio/video recording using both internal and external devices. Validate DRM-protected content, especially in Microsoft Edge and Office apps. Testing regimes should include a cycle of playback, record, and stream — then check your system logs for crashes or errors. Storage and filesystems Perform Windows error log creation, appends, and reopen scenarios using Common Log File System APIs. Simulate SMB folder access from multiple windows. Changes in one view should reflect in the other. Validate UNC path access across apps. Run these tests with Microsoft Explorer and line-of-business apps that access network shares or log files. Installation and application infrastructure Given the focus of the Readiness team, it would be remiss to forget the changes to Microsoft’s update and application infrastructure with the following tests: Conduct basic install, repair, roll-back and uninstall tests for MSI Installer packages. This process should be (mostly) automated by now. If you’re an organization that employs App Silos, you will need create a test cycle that includes invoking the BFS driver via an isolated app context Run web, file transfer, and messaging scenarios to test network throughput under load. In addition to these specific test exercises, we highly recommend a full business logic test of your internal and line-of-business applications that have significant graphics requirements. This is required due to the changes to the Windows kernel and GDI (graphic) subsystems). Readiness recommends your testing in priority in the following order: RDP and remote access, application installations, PowerShell testing, and then storage system testing. Updates by product family Each month, we break down the update cycle into product families (as defined by Microsoft) with the following basic groupings: Browsers (Microsoft IE and Edge) Microsoft Windows (both desktop and server) Microsoft Office Microsoft Exchange and SQL Server Microsoft Developer Tools (Visual Studio and .NET) Adobe (if you get this far) Browsers Microsoft has not released any native updates for its browsers this month. However, there were five Chromium updates (CVE-2025-4050, CVE-2025-4372, CVE-2025-4096, CVE-2025-4052, and CVE-2025-405) that will update Microsoft Edge. All of these low-profile changes can be added to your standard release calendar. Microsoft Windows Microsoft has released three critical updates, plus 41 patches rated as important. The critical updates affect Microsoft’s Remote Desktop platform and the Virtual Machine bus (VMBus). Unfortunately, the following Windows desktop updates have been reported as exploited in the wild: CVE-2025-30400 CVE-2025-32701 CVE-2025-32706 CVE-2025-32709 CVE-2025-30397 As a result of these zero-days, the Readiness team recommends a “Patch Now” schedule for these Windows patches. Microsoft Office Microsoft has released two critical rated updates (CVE-2025-30377 and CVE-2025-30386) for the Microsoft Office platform this month. Both of these patches were updated mid-week for documentation reasons. Following these critical patches, Microsoft has released a further 16 patches that have been rated as important; they update Microsoft Office in general (as opposed to Word or Excel). Please add these Microsoft Office updates to your standard release calendar. Microsoft Exchange Server No updates for Microsoft Exchange or Microsoft SQL server this month. Good news for all the server teams. Microsoft development platforms A single critical update (CVE-2025-29813) to the Microsoft DevOps platform and four patches rated as important by Microsoft have been released to the developer platforms this month. All of the patches rated as important affect Visual Studio and Microsoft .NET. Add these updates to your standard release schedule. Adobe Reader (if you get this far) No Adobe updates (published by Microsoft) for this May patch cycle. Given the recent security advances implemented in Windows 11 23H2 and 24H2, I think that we will see much less of Adobe in this column.

Databricks' $1 billion acquisition of Neon highlights how serverless PostgreSQL has become essential for agentic AI development.Read More

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More The importance of databases to modern enterprise AI operations cannot be overstated. Data helps to train and ground AI, and multiple research reports show that without proper data, AI efforts tend to fail. With trends like vibe coding and agentic AI, it’s also increasingly important to have database technology that can be spun up as needed in a serverless approach to modern development efforts. In that environment, it should come as no surprise that databases are a particularly valuable commodity.  This week, that fact was on display with Databricks‘ acquisition of privately held serverless PostgreSQL startup Neon, which was founded in 2022. The deal is reportedly valued at a staggering $1 billion, which is shocking given that barely two years ago, the company raised $46 million in a series B round of funding. What is also particularly interesting is that Databricks itself is a data vendor, with its data lakehouse platform. At various points in the company’s history, it has positioned itself as an alternative to traditional databases, providing a data lake structure where users can make queries. So what was missing, and why did Databricks need to spend a billion dollars? What does it mean and say about what enterprise AI really needs? Functionally, it’s all about meeting developers’ needs to build agentic AI applications. According to Neon, over 80% of the databases created on its platform were created by AI agents. What is serverless PostgreSQL and why does it matter? While Neon is a startup, the core database technology that it’s based on is not new. PostgreSQL is one of the oldest and most established open-source database platforms, dating back to 1996. It’s a relational database technology, meaning it has tables and rows alongside extremely stable features that organizations have trusted for decades. The core open-source PostgreSQL database is now updated in a yearly release cadence. The most recent stable update was PostgreSQL 17, which debuted in Sept. 2024. As an open-source technology, PostgreSQL has enjoyed broad adoption and contributions. At one point, it was often compared to other proprietary relational database options, including Oracle as an alternative option. In 2025, though, PostgreSQL stands on its own. DB-Engines currently ranks PostgreSQL as the fourth most popular database in use today, behind Microsoft SQL Server, MySQL and Oracle. The state of PostgreSQL 2024 report from Timescale identifies the open-source database’s rising prominence as the database of choice for AI applications. The database’s well-established and understood nature and broad availability are among the numerous factors that make it attractive. PostgreSQL on its own is just the database, though. Running it as a serverless offering is an operational and deployment activity. The promise of any serverless database is ease of operations. Rather than requiring a dedicated database deployment that continually runs with dedicated resources, serverless is spun up on demand as needed. It’s a deployment option that is particularly attractive to developers as a way to build applications quickly. AI-based development is even more appealing as databases can be built and deployed programmatically. The serverless PostgreSQL landscape has a lot of vendors Every cloud hyperscaler has some form of PostgreSQL service and has for years.  Google has multiple offerings, including AlloyDB, Microsoft has Azure Database for PostgreSQL, while AWS has Amazon RDS for PostgreSQL and Amazon Aurora. Each of them also has some flavor of serverless offering, that is, a database available on demand. Numerous smaller vendors exist, including Timescale, EDB and NetApp Instaclustr. In fact, nearly two years ago, Databricks acquired serverless PostgreSQL vendor bit.io, which was also an early rival of Neon. As it turns out, the goals and capabilities of bit.io are quite different from Neon. “Together with the Neon team, we look forward to building the most developer and AI-agent-friendly database platform,” Phil Shin, senior director of corporate development and ventures at Databricks, told VentureBeat. “In contrast, the bit.io acquisition was not actually about Postgres but targeting developer experiences, especially in the trials and self-service process.” Shin added that the bit.io acquisition had a big impact on Databricks’ seamless signup experience.  How serverless PostgreSQL fits into the enterprise database landscape While Neon has only been around for a few years with its serverless PostgreSQL implementation, commercial vendor EDB has been in business since 2004. EDB has a series of its own commercially supported PostgreSQL offerings. Matt Yonkovit, VP of Product for EDB, told VentureBeat that the acquisition of Neon is a strong vote of confidence in Postgres as a foundational technology for AI and analytics.  “It reinforces what we’ve long believed: Postgres is increasingly central to modern data stacks,”  Yonkovit said. “Serverless is a great fit for dev/test environments and for quickly jumpstarting AI projects—but as those use cases scale, enterprises need the performance, compliance, and control of a sovereign platform.” Yonkovit noted that serverless is well-suited for short bursts and smaller workloads. It can scale up and down quickly or shut off entirely when idle, which significantly reduces costs associated with compute, power and storage. However, in his view, there are tradeoffs. “A significant challenge with serverless is that sovereign data management can become messy because you can’t control where the data is processed unless you have a well-restricted pool of resources,” Yonkovit said. The power of serverless PostgreSQL for agentic AI Neon’s serverless PostgreSQL approach separates storage and compute, making it developer-friendly and AI-native. It also enables automated scaling as well as branching in an approach that is similar to how the Git version control system works for code. Amalgam Insights CEO and Chief Analyst Hyoun Park noted that Databricks has been a pioneer in deploying and scaling AI projects.  “One of the big challenges in AI is managing the storage and compute associated with the data,” Park told VentureBeat. “Each of these needs will be increasingly hard to predict in an agentic world where end-user prompts and requests may quickly lead to unexpected demands in storage or compute to solve the problem. Park explained that Neon’s serverless autoscaling approach to PostgreSQL is important for AI because it allows agents and AI projects to grow as needed without artificially coupling storage and compute needs together. He added that for Databricks, this is useful both for agentic use cases and for supporting the custom models they have built over the last couple of years after its Mosaic AI acquisition.  What it means for enterprise AI leaders For enterprises looking to lead the way in AI, this acquisition signals a shift in infrastructure requirements for successful AI implementation. Data is critical for AI; that’s not a surprise. What is particularly insightful, though, is that the ability to rapidly spin up databases is essential for agentic AI success. The deal validates that even advanced data companies need specialized serverless database capabilities to support AI agents that create and manage databases programmatically.  Organizations should recognize that traditional database approaches may limit their AI initiatives, while flexible, instantly scalable serverless solutions enable the dynamic resource allocation that modern AI applications demand.  For companies still planning their AI roadmap, this acquisition signals that database infrastructure decisions should prioritize serverless capabilities that can adapt quickly to unpredictable AI workloads. This would transform database strategy from a technical consideration to a competitive advantage in delivering responsive, efficient AI solutions. Daily insights on business use cases with VB Daily If you want to impress your boss, VB Daily has you covered. We give you the inside scoop on what companies are doing with generative AI, from regulatory shifts to practical deployments, so you can share insights for maximum ROI. Read our Privacy Policy Thanks for subscribing. Check out more VB newsletters here. An error occured.

Posted on : May 15, 2025 By Tech World Times Security Testing  Rate this post SSMS SQL (SQL Server Management Studio) is a tool used to manage SQL Server databases. It helps users create, update, and manage database objects easily. However, even experienced users face some common errors while using SSMS SQL. In this guide, we will cover five common mistakes and explain how to fix them. Let’s look at each error in detail. 1. Incorrect Syntax Near the Keyword What It Means This error appears when there is a mistake in your SQL query. It usually shows as:“Incorrect syntax near the keyword.” Why It Happens A missing comma or bracket Wrong use of SQL keywords Using reserved words as column or table names Example sqlCopyEditSELECT name age FROM students Here, a comma is missing between name and age. How to Fix It Check your query line by line. Use commas to separate columns. Avoid using reserved SQL keywords. Use square brackets if you must use a reserved word.Corrected Query: sqlCopyEditSELECT name, age FROM students 2. Invalid Column Name What It Means SSMS SQL shows this error when the column name you entered doesn’t exist. It may look like:“Invalid column name ‘username’.” Why It Happens Typing errors in column names Column does not exist in the table Querying the wrong table Example sqlCopyEditSELECT username FROM users If the table has a column named user_name, not username, you get this error. How to Fix It Check the actual column names in the table. Use the Object Explorer in SSMS SQL to confirm the structure. You can also use this command to view columns: sqlCopyEditsp_help users Corrected Query: sqlCopyEditSELECT user_name FROM users 3. Cannot Insert NULL into Column What It Means You tried to insert a NULL value into a column that does not allow it. The error looks like:“Cannot insert the value NULL into column…” Why It Happens You left a NOT NULL column blank during INSERT Missing values in your form or code Example sqlCopyEditINSERT INTO employees (id, name) VALUES (1, NULL) If the name column is NOT NULL, this causes an error. How to Fix It Always check the table design. Make sure all NOT NULL columns get valid data. You can also add a default value or allow NULLs if needed. Corrected Query: sqlCopyEditINSERT INTO employees (id, name) VALUES (1, 'John') 4. Conversion Failed Error What It Means SSMS SQL gives this error when it cannot convert one data type to another. It may show as:“Conversion failed when converting the varchar value to data type int.” Why It Happens Trying to insert text into a number column Wrong format for dates or numbers Comparing incompatible data types Example sqlCopyEditSELECT * FROM orders WHERE order_id = 'ABC' If order_id is an integer column, this causes a conversion error. How to Fix It Always match data types properly. Use CAST() or CONVERT() when needed. Clean your input data before inserting or comparing. Corrected Query: sqlCopyEditSELECT * FROM orders WHERE order_id = 123 Or use conversion: sqlCopyEditSELECT * FROM orders WHERE order_id = CONVERT(int, '123') 5. Login Failed for User What It Means You can’t connect to the database because of login problems. The error says:“Login failed for user ‘username’.” Why It Happens Wrong username or password SQL Server login not enabled Authentication mode issues How to Fix It First, check your login details. Open SSMS SQL and use the right credentials. Ensure the SQL Server is set to Mixed Authentication Mode. To check this: Right-click the server name in SSMS SQL. Choose Properties > Security. Select SQL Server and Windows Authentication mode. Restart the server to apply changes. You can also create a new login with: sqlCopyEditCREATE LOGIN testuser WITH PASSWORD = 'Password123' Then give it access to a database. Bonus Tips to Avoid Errors in SSMS SQL Always test your query using the Query Designer. Use the IntelliSense feature for suggestions. Keep your database backed up before making changes. Use transactions to prevent partial updates. Break complex queries into smaller parts for easier testing. Conclusion Working with SSMS SQL can be smooth if you avoid common mistakes. Errors like wrong syntax, missing columns, or login failures are easy to fix. Just read error messages carefully and follow the tips above. Always check your table design and data types. Doing this saves time and prevents data issues. SSMS SQL is a powerful tool. Once you learn how to fix these common problems, you will become more confident. Keep practicing and refer to this guide whenever you face an error. Tech World TimesTech World Times (TWT), a global collective focusing on the latest tech news and trends in blockchain, Fintech, Development & Testing, AI and Startups. If you are looking for the guest post then contact at techworldtimes@gmail.com

The saying goes that 80% of data collected, stored and maintained by governments can be associated with geographical locations. Although never empirically proven, it illustrates the importance of location within data. Ever growing data volumes put constraints on systems that handle geospatial data. Common Big Data compute engines, originally designed to scale for textual data, need adaptation to work efficiently with geospatial data — think of geographical indexes, partitioning, and operators. Here, I present and illustrate how to utilize the Microsoft Fabric Spark compute engine, with the natively integrated ESRI GeoAnalytics engine# for geospatial big data processing and analytics. The optional GeoAnalytics capabilities within Fabric enable the processing and analytics of vector-type geospatial data, where vector-type geospatial data refers to points, lines, polygons. These capabilities include more than 150 spatial functions to create geometries, test, and select spatial relationships. As it extends Spark, the GeoAnalytics functions can be called when using Python, SQL, or Scala. These spatial operations apply automatically spatial indexing, making the Spark compute engine also efficient for this data. It can handle 10 extra common spatial data formats to load and save data spatial data, on top of the Spark natively supported data source formats. This blog post focuses on the scalable geospatial compute engines as has been introduced in my post about geospatial in the age of AI. Demonstration explained Here, I demonstrate some of these spatial capabilities by showing the data manipulation and analytics steps on a large dataset. By using several tiles covering point cloud data (a bunch of x, y, z values), an enormous dataset starts to form, while it still covers a relatively small area. The open Dutch AHN dataset, which is a national digital elevation and surface model, is currently in its fifth update cycle, and spans a period of nearly 30 years. Here, the data from the second, third, and forth acquisition is used, as these hold full national coverage (the fifth just not yet), while the first version did not include a point cloud release (only the derivative gridded version). Another Dutch open dataset, namely building data, the BAG, is used to illustrate spatial selection. The building dataset contains the footprint of the buildings as polygons. Currently, this dataset holds more than 11 million buildings. To test the spatial functions, I use only 4 AHN tiles per AHN version. Thus in this case, 12 tiles, each of 5 x 6.25 km. Totalling to more than 3.5 billion points within an area of 125 square kilometers. The chosen area covers the municipality of Loppersum, an area prone to land subsidence due to gas extraction. The steps to take include the selection of buildings within the area of Loppersum, selecting the x,y,z-points from the roofs of the buildings. Then, we bring the 3 datasets into one dataframe and do an extra analysis with it. A spatial regression to predict the expected height of a building based on its height history as well as the history of the buildings in its direct surroundings. Not necessarily the best analysis to perform on this data to come to actual predictions* but it suits merely the purpose of demonstrating the spatial processing capabilities of Fabric’s ESRI GeoAnalytics. All the below code snippets are also available as notebooks on github. Step 1: Read data Spatial data can come in many different data formats; we conform to the geoparquet data format for further processing. The BAG building data, both the footprints as well as the accompanied municipality boundaries, come in geoparquet format already. The point cloud AHN data, version 2, 3 and 4, however, comes as LAZ file formats — a compressed industry standard format for point clouds. I have not found a Spark library to read LAZ (please leave a message in case there is one), and created a txt file, separately, with the LAStools+ first. # ESRI - FABRIC reference: https://developers.arcgis.com/geoanalytics-fabric/ # Import the required modules import geoanalytics_fabric from geoanalytics_fabric.sql import functions as ST from geoanalytics_fabric import extensions # Read ahn file from OneLake # AHN lidar data source: https://viewer.ahn.nl/ ahn_csv_path = "Files/AHN lidar/AHN4_csv" lidar_df = spark.read.options(delimiter=" ").csv(ahn_csv_path) lidar_df = lidar_df.selectExpr("_c0 as X", "_c1 as Y", "_c2 Z") lidar_df.printSchema() lidar_df.show(5) lidar_df.count() The above code snippet& provides the below results: Now, with the spatial functions make_point and srid the x,y,z columns are transformed to a point geometry and set it to the specific Dutch coordinate system (SRID = 28992), see the below code snippet&: # Create point geometry from x,y,z columns and set the spatial refrence system lidar_df = lidar_df.select(ST.make_point(x="X", y="Y", z="Z").alias("rd_point")) lidar_df = lidar_df.withColumn("srid", ST.srid("rd_point")) lidar_df = lidar_df.select(ST.srid("rd_point", 28992).alias("rd_point"))\ .withColumn("srid", ST.srid("rd_point")) lidar_df.printSchema() lidar_df.show(5) Building and municipality data can be read with the extended spark.read function for geoparquet, see the code snippet&: # Read building polygon data path_building = "Files/BAG NL/BAG_pand_202504.parquet" df_buildings = spark.read.format("geoparquet").load(path_building) # Read woonplaats data (=municipality) path_woonplaats = "Files/BAG NL/BAG_woonplaats_202504.parquet" df_woonplaats = spark.read.format("geoparquet").load(path_woonplaats) # Filter the DataFrame where the "woonplaats" column contains the string "Loppersum" df_loppersum = df_woonplaats.filter(col("woonplaats").contains("Loppersum")) Step 2: Make selections In the accompanying notebooks, I read and write to geoparquet. To make sure the right data is read correctly as dataframes, see the following code snippet: # Read building polygon data path_building = "Files/BAG NL/BAG_pand_202504.parquet" df_buildings = spark.read.format("geoparquet").load(path_building) # Read woonplaats data (=municipality) path_woonplaats = "Files/BAG NL/BAG_woonplaats_202504.parquet" df_woonplaats = spark.read.format("geoparquet").load(path_woonplaats) # Filter the DataFrame where the "woonplaats" column contains the string "Loppersum" df_loppersum = df_woonplaats.filter(col("woonplaats").contains("Loppersum")) With all data in dataframes it becomes a simple step to do spatial selections. The following code snippet& shows how to select the buildings within the boundaries of the Loppersum municipality, and separately makes a selection of buildings that existed throughout the period (point cloud AHN-2 data was acquired in 2009 in this region). This resulted in 1196 buildings, out of the 2492 buildings currently. # Clip the BAG buildings to the gemeente Loppersum boundary df_buildings_roi = Clip().run(input_dataframe=df_buildings, clip_dataframe=df_loppersum) # select only buildings older then AHN data (AHN2 (Groningen) = 2009) # and with a status in use (Pand in gebruik) df_buildings_roi_select = df_buildings_roi.where((df_buildings_roi.bouwjaar<2009) & (df_buildings_roi.status=='Pand in gebruik')) The three AHN versions used (2,3 and 4), further named as T1, T2 and T3 respectively, are then clipped based on the selected building data. The AggregatePoints function can be utilized to calculate, in this case from the height (z-values) some statistics, like the mean per roof, the standard deviation and the number of z-values it is based upon; see the code snippet: # Select and aggregrate lidar points from buildings within ROI df_ahn2_result = AggregatePoints() \ .setPolygons(df_buildings_roi_select) \ .addSummaryField(summary_field="T1_z", statistic="Mean", alias="T1_z_mean") \ .addSummaryField(summary_field="T1_z", statistic="stddev", alias="T1_z_stddev") \ .run(df_ahn2) df_ahn3_result = AggregatePoints() \ .setPolygons(df_buildings_roi_select) \ .addSummaryField(summary_field="T2_z", statistic="Mean", alias="T2_z_mean") \ .addSummaryField(summary_field="T2_z", statistic="stddev", alias="T2_z_stddev") \ .run(df_ahn3) df_ahn4_result = AggregatePoints() \ .setPolygons(df_buildings_roi_select) \ .addSummaryField(summary_field="T3_z", statistic="Mean", alias="T3_z_mean") \ .addSummaryField(summary_field="T3_z", statistic="stddev", alias="T3_z_stddev") \ .run(df_ahn4) Step 3: Aggregate and Regress As the GeoAnalytics function Geographically Weighted Regression (GWR) can only work on point data, from the building polygons their centroid is extracted with the centroid function. The 3 dataframes are joined to one, see also the notebook, and it is ready to perform the GWR function. In this instance, it predicts the height for T3 (AHN4) based on local regression functions. # Import the required modules from geoanalytics_fabric.tools import GWR # Run the GWR tool to predict AHN4 (T3) height values for buildings at Loppersum resultGWR = GWR() \ .setExplanatoryVariables("T1_z_mean", "T2_z_mean") \ .setDependentVariable(dependent_variable="T3_z_mean") \ .setLocalWeightingScheme(local_weighting_scheme="Bisquare") \ .setNumNeighbors(number_of_neighbors=10) \ .runIncludeDiagnostics(dataframe=df_buildingsT123_points) The model diagnostics can be consulted for the predicted z value, in this case, the following results were generated. Note, again, that these results cannot be used for real world applications as the data and methodology might not best fit the purpose of subsidence modelling — it merely shows here Fabric GeoAnalytics functionality. R20.994AdjR20.981AICc1509Sigma20.046EDoF378 Step 4: Visualize results With the spatial function plot, results can be visualized as maps within the notebook — to be used only with the Python API in Spark. First, a visualization of all buildings within the municipality of Loppersum. # visualize Loppersum buildings df_buildings.st.plot(basemap="light", geometry="geometry", edgecolor="black", alpha=0.5) Here is a visualization of the height difference between T3 (AHN4) and T3 predicted (T3 predicted minus T3). # Vizualize difference of predicted height and actual measured height Loppersum area and buildings axes = df_loppersum.st.plot(basemap="light", edgecolor="black", figsize=(7, 7), alpha=0) axes.set(xlim=(244800, 246500), ylim=(594000, 595500)) df_buildings.st.plot(ax=axes, basemap="light", alpha=0.5, edgecolor="black") #, color='xkcd:sea blue' df_with_difference.st.plot(ax=axes, basemap="light", cmap_values="subsidence_mm_per_yr", cmap="coolwarm_r", vmin=-10, vmax=10, geometry="geometry") Summary This blog post discusses the significance of geographical data. It highlights the challenges posed by increasing data volumes on Geospatial data systems and suggests that traditional big data engines must adapt to handle geospatial data efficiently. Here, an example is presented on how to use the Microsoft Fabric Spark compute engine and its integration with the ESRI GeoAnalytics engine for effective geospatial big data processing and analytics. Opinions here are mine. Footnotes # in preview * for modelling the land subsidence with much higher accuracy and temporal frequency other approaches and data can be utilized, such as with satellite InSAR methodology (see also Bodemdalingskaart) + Lastools is used here separately, it would be fun to test the usage of Fabric User data functions (preview), or to utilize an Azure Function for this purpose. & code snippets here are set up for readability, not necessarily for efficiency. Multiple data processing steps could be chained. References GitHub repo with notebooks: delange/Fabric_GeoAnalytics Microsoft Fabric: Microsoft Fabric documentation – Microsoft Fabric | Microsoft Learn ESRI GeoAnalytics for Fabric: Overview | ArcGIS GeoAnalytics for Microsoft Fabric | ArcGIS Developers AHN: Home | AHN BAG: Over BAG – Basisregistratie Adressen en Gebouwen – Kadaster.nl zakelijk Lastools: LAStools: converting, filtering, viewing, processing, and compressing LIDAR data in LAS and LAZ format Surface and Object Motion Map: Bodemdalingskaart – The post The Geospatial Capabilities of Microsoft Fabric and ESRI GeoAnalytics, Demonstrated appeared first on Towards Data Science.

With the amount of Data growing exponentially in the last few years, one of the biggest challenges has become finding the most optimal way to store various data flavors. Unlike in the (not so far) past, when relational databases were considered the only way to go, organizations now want to perform analysis over raw data – think of social media sentiment analysis, audio/video files, and so on – which usually couldn’t be stored in a traditional (relational) way, or storing them in a traditional way would require significant effort and time, which increase the overall time-for-analysis. Another challenge was to somehow stick with a traditional approach to have data stored in a structured way, but without the necessity to design complex and time-consuming ETL workloads to move this data into the enterprise data warehouse. Additionally, what if half of the data professionals in your organization are proficient with, let’s say, Python (data scientists, data engineers), and the other half (data engineers, data analysts) with SQL? Would you insist that “Pythonists” learn SQL? Or, vice-versa? Or, would you prefer a storage option that can play to the strengths of your entire data team? I have good news for you – something like this has already existed since 2013, and it’s called Apache Parquet! Parquet file format in a nutshell Before I show you the ins and outs of the Parquet file format, there are (at least) five main reasons why Parquet is considered a de facto standard for storing data nowadays: Data compression – by applying various encoding and compression algorithms, Parquet file provides reduced memory consumption Columnar storage – this is of paramount importance in analytic workloads, where fast data read operation is the key requirement. But, more on that later in the article… Language agnostic – as already mentioned previously, developers may use different programming languages to manipulate the data in the Parquet file Open-source format – meaning, you are not locked with a specific vendor Support for complex data types Row-store vs Column-store We’ve already mentioned that Parquet is a column-based storage format. However, to understand the benefits of using the Parquet file format, we first need to draw the line between the row-based and column-based ways of storing the data. In traditional, row-based storage, the data is stored as a sequence of rows. Something like this: Image by author Now, when we are talking about OLAP scenarios, some of the common questions that your users may ask are: How many balls did we sell? How many users from the USA bought a T-shirt? What is the total amount spent by customer Maria Adams? How many sales did we have on January 2nd? To be able to answer any of these questions, the engine must scan each and every row from the beginning to the very end! So, to answer the question: how many users from the USA bought T-shirt, the engine has to do something like this: Image by author Essentially, we just need the information from two columns: Product (T-Shirts) and Country (USA), but the engine will scan all five columns! This is not the most efficient solution – I think we can agree on that… Column store Let’s now examine how the column store works. As you may assume, the approach is 180 degrees different: Image by author In this case, each column is a separate entity – meaning, each column is physically separated from other columns! Going back to our previous business question: the engine can now scan only those columns that are needed by the query (Product and country), while skipping scanning the unnecessary columns. And, in most cases, this should improve the performance of the analytical queries. Ok, that’s nice, but the column store existed before Parquet and it still exists outside of Parquet as well. So, what is so special about the Parquet format? Parquet is a columnar format that stores the data in row groups Wait, what?! Wasn’t it complicated enough even before this? Don’t worry, it’s much easier than it sounds Let’s go back to our previous example and depict how Parquet will store this same chunk of data: Image by author Let’s stop for a moment and explain the illustration above, as this is exactly the structure of the Parquet file (some additional things were intentionally omitted, but we will come soon to explain that as well). Columns are still stored as separate units, but Parquet introduces additional structures, called Row group. Why is this additional structure super important? You’ll need to wait for an answer for a bit :). In OLAP scenarios, we are mainly concerned with two concepts: projection and predicate(s). Projection refers to a SELECT statement in SQL language – which columns are needed by the query. Back to our previous example, we need only the Product and Country columns, so the engine can skip scanning the remaining ones. Predicate(s) refer to the WHERE clause in SQL language – which rows satisfy criteria defined in the query. In our case, we are interested in T-Shirts only, so the engine can completely skip scanning Row group 2, where all the values in the Product column equal socks! Image by author Let’s quickly stop here, as I want you to realize the difference between various types of storage in terms of the work that needs to be performed by the engine: Row store – the engine needs to scan all 5 columns and all 6 rows Column store – the engine needs to scan 2 columns and all 6 rows Column store with row groups – the engine needs to scan 2 columns and 4 rows Obviously, this is an oversimplified example, with only 6 rows and 5 columns, where you will definitely not see any difference in performance between these three storage options. However, in real life, when you’re dealing with much larger amounts of data, the difference becomes more evident. Now, the fair question would be: how does Parquet “know” which row group to skip/scan? Parquet file contains metadata This means that every Parquet file contains “data about data” – information such as minimum and maximum values in a specific column within a certain row group. Furthermore, every Parquet file contains a footer, which keeps the information about the format version, schema information, column metadata, and so on. You can find more details about Parquet metadata types here. Important: In order to optimize the performance and eliminate unnecessary data structures (row groups and columns), the engine first needs to “get familiar” with the data, so it first reads the metadata. It’s not a slow operation, but it still requires a certain amount of time. Therefore, if you’re querying the data from multiple small Parquet files, query performance can degrade, because the engine will have to read metadata from each file. So, you should be better off merging multiple smaller files into one bigger file (but still not too big :)… I hear you, I hear you: Nikola, what is “small” and what is “big”? Unfortunately, there is no single “golden” number here, but for example, Microsoft Azure Synapse Analytics recommends that the individual Parquet file should be at least a few hundred MBs in size. What else is in there? Here is a simplified, high-level illustration of the Parquet file format: Image by author Can it be better than this? Yes, with data compression Ok, we’ve explained how skipping the scan of the unnecessary data structures (row groups and columns) may benefit your queries and increase the overall performance. But, it’s not only about that – remember when I told you at the very beginning that one of the main advantages of the Parquet format is the reduced memory footprint of the file? This is achieved by applying various compression algorithms. I’ve already written about various data compression types in Power BI (and the Tabular model in general) here, so maybe it’s a good idea to start by reading this article. There are two main encoding types that enable Parquet to compress the data and achieve astonishing savings in space: Dictionary encoding – Parquet creates a dictionary of the distinct values in the column, and afterward replaces “real” values with index values from the dictionary. Going back to our example, this process looks something like this: Image by author You might think: why this overhead, when product names are quite short, right? Ok, but now imagine that you store the detailed description of the product, such as: “Long arm T-Shirt with application on the neck”. And, now imagine that you have this product sold million times…Yeah, instead of having million times repeating value “Long arm…bla bla”, the Parquet will store only the Index value (integer instead of text). Run-Length-Encoding with Bit-Packing – when your data contains many repeating values, Run-Length-Encoding (RLE) algorithm may bring additional memory savings. Can it be better than THIS?! Yes, with the Delta Lake file format Ok, what the heck is now a Delta Lake format?! This is the article about Parquet, right? So, to put it in plain English: Delta Lake is nothing else but the Parquet format “on steroids”. When I say “steroids”, the main one is the versioning of Parquet files. It also stores a transaction log to enable tracking all changes applied to the Parquet file. This is also known as ACID-compliant transactions. Since it supports not only ACID transactions, but also supports time travel (rollbacks, audit trails, etc.) and DML (Data Manipulation Language) statements, such as INSERT, UPDATE and DELETE, you won’t be wrong if you think of the Delta Lake as a “data warehouse on the data lake” (who said: Lakehouse). Examining the pros and cons of the Lakehouse concept is out of the scope of this article, but if you’re curious to go deeper into this, I suggest you read this article from Databricks. Conclusion We evolve! Same as we, the data is also evolving. So, new flavors of data required new ways of storing it. The Parquet file format is one of the most efficient storage options in the current data landscape, since it provides multiple benefits – both in terms of memory consumption, by leveraging various compression algorithms, and fast query processing by enabling the engine to skip scanning unnecessary data. Thanks for reading! The post Parquet File Format – Everything You Need to Know! appeared first on Towards Data Science.