Press "Enter" to skip to content

Day: August 7, 2023

Learning about Data in R with str()

Steven Sanderson explains the value of the str() function:

In a nutshell, str() stands for “structure” and offers a concise summary of the structure of an R object. It presents essential details about the object, including its data type, dimensions, and the first few values. By providing an overview of your data, str() allows you to grasp the fundamentals at a glance and proceed with a clearer understanding of what you’re working with.

str() is a really useful function and people who develop objects in R thoughtfully can pack a lot of useful data into the one call.

Comments closed

Thoughts on Partitioning in Postgres

Ryan Booz splits things out:

For 20+ years of database and application development, time-series data has always been at the heart of the products I work with. Inevitably, as the quantity of data grew over time, management became more difficult and query performance suffered. Over the years, the primary method for managing this growth in data effectively would be to partition it. The problem is, until recently, partitioning wasn’t easy to setup in most OLTP databases like PostgreSQL or SQL Server.

Fortunately, PostgreSQL has significantly improved its ability to partition large data tables over the last 6 years, starting with PostgreSQL 10.

Read on for Ryan’s recommendations around partitioning and a few thoughts on sharding.

Comments closed

Cross-Database Query Folding in Power Query

Chris Webb doesn’t want query folding limits:

A few months ago a new option was added to the Sql.Database and Sql.Databases functions in Power Query in Power BI and Excel which allows Power Query queries that combine data from different SQL Server databases to fold. Here’s a simple example showing how to use it.

Read on for the setup and the demonstration. This is pretty nice for cases where you have user data in one database and reference data in another, or you’re trying to join together data from two separate systems.

Comments closed

An Overview of Partitioning and Sharding in Postgres

Michael Christofides defines terms:

It has been possible to do partitioning in PostgreSQL for quite a while — splitting what is logically one large table into smaller physical tables. Since version 10, a huge leap was made with the introduction of declarative partitioning, and more improvements have come every year since.

Sharding is a different story — splitting what is logically one large database into smaller physical databases. The primary tool for this in the PostgreSQL ecosystem is the Citus extension. But you can also handle the sharding logic at the application level, as recent posts from the likes of Notion and Figma have described. Somewhat confusingly, some forms of sharding are sometimes referred to as vertical partitioning, including by the team at Figma.

Read on for a few thoughts on when to perform each action and what the costs and benefits are.

Comments closed

Visualizing Snowflake Geospatial Data with Power BI

Rebecca O’Connor builds a map:

Power BI can leverage Geospatial data from snowflake with my favourite map visual – Iconmap – . Icon map can render points, polygons and linestrings using ‘Well Known Text’ format (WKT).

Snowflake supports converting geospatial datatypes to WKT. Not only this, Snowflake has the capabilities to perform the Engineering and analytical needs for Geospatial analysis without using any other tool. And the results can be visualised in a variety of medias such as Tableau, Hex, Carto or even a Custom built Streamlit application. I have written a Streamlit blog on this very recently.

Click through for information on how to get the data shaped in a way that Power BI likes.

Comments closed