analytics

Date Table Generation Notebook for Databricks Unity Catalog 

In this episode of “How do I create a date table from nothing in my chosen platform”? I am covering Databricks.   No lengthy life story of what bubble tea I was drinking when creating this or childhood memories like some food bloggers. The code is linked below, look at the comments in the notebook if …

Date Table Generation Notebook for Databricks Unity Catalog  Read More »

Persist the List of Files in an External Stage in Snowflake

In Snowflake, you can access files external to the Snowflake instance using something called a STAGE. A STAGE is a named object that points to a location such as AWS S3, Azure Storage Account or Google Cloud Storage Buckets, and contains the required authentication credentials to access the files in that storage location. To programmatically …

Persist the List of Files in an External Stage in Snowflake Read More »

Get the Iteration of a Weekday in a Month on a Virtual Calendar

There are many situations where you need to identify the iteration of a weekday within a given month. Most commonly though is for the identification of public holidays e.g., the 4th Thursday in November – Thanksgiving in USA. There are no built-in methods to get this information in any SQL-based platform I have used thus …

Get the Iteration of a Weekday in a Month on a Virtual Calendar Read More »

Dynamic SQL in Databricks and SQL Server

What is dynamic SQL? Dynamic SQL is a programming technique where you write a general purpose query and store it in a string variable, then alter key words in the string at runtime to alter the type of actions it will perform, the data it will return or the objects it will perform these actions …

Dynamic SQL in Databricks and SQL Server Read More »

Calculate Easter Sunday Dynamically using SQL

In any data warehousing or analytics solution, you will eventually need to generate a calendar table in order to perform time intelligence analytics. One key aspect that most businesses will require is to view KPIs by working days only i.e. days when the business was operating normally. Each business will have their own unique rule sets …

Calculate Easter Sunday Dynamically using SQL Read More »

Parameterize Synapse Analytics Spark Notebooks Efficiently

When creating pipelines in any sort of data flow to move data from an incoming source to a target location, ideally you don’t want to create single-purpose activities. These would only perform one action or set of actions on a specific set of source and target objects. E.g. take the source.sales table, filter out where …

Parameterize Synapse Analytics Spark Notebooks Efficiently Read More »

Query JSON data in SQL Server and Synapse Analytics

When would you work with JSON Data? JSON is a popular data representation format used on the web to exchange information between remote parties.It is also used for storing unstructured data in log files or NoSQL Document Databases such as MongoDB or Azure CosmosDB.SQL also has the ability to store JSON data in a text …

Query JSON data in SQL Server and Synapse Analytics Read More »

Query Azure Data Lake via Synapse Serverless Security Credentials Setup

Overview Azure Synapse Analytics Serverless SQL Endpoint has the capability to query files that are stored in an Azure Data Lake using T-SQL code as if they were regular tables in a relational database.These files can be semi-structured or unstructured in nature. Using the Create External Table As Select (CETAS) functionality, you can even generate new …

Query Azure Data Lake via Synapse Serverless Security Credentials Setup Read More »

Dynamic External Tables in Azure Synapse Analytics On-Demand

What is an External Table? This article will focus on the Synapse Analytics implementation of External Tables.However, note that there are other flavours of external tables and they behave slightly differently depending on which product you are using to defined it. SQL Server SQL Database Azure Synapse Analytics Analytics Platform System (PDW) External Tables in …

Dynamic External Tables in Azure Synapse Analytics On-Demand Read More »