Call today 0845 643 6463

SQL

  • How to Handle Deleted Rows in an Incremental Load

    September 4th, 2026 in Big Data, Blog, Data Lakehouse, Data Warehouse, ETL, SQL, SQL Server, SSIS, Uncategorised

    Most data warehouse issues are apparent: a pipeline fails, an alert fires, you get a phone call at an unsociable hour. Deleted source data doesn’t do this. A row is removed from the source, and no one notices. It sits in the warehouse silently skewing measures and KPIs, until the finance director asks why a cancelled order is still in the revenue report. What’s causing this? Incremental loads use watermarks to avoid re-extracting…

    » Read more

  • SQL Server vs Fabric Warehouse: T-SQL gotchas nobody warns you about

    SQL Server vs Fabric Warehouse: T-SQL gotchas nobody warns you aboutJuly 23rd, 2026 in Blog, Data Warehouse, Fabric, Microsoft Fabric, SQL, SQL Server

    Fabric Warehouse looks just like SQL Server. It uses T-SQL, has schemas, stored procs and more. Yet, you migrate your first proper workload and see “that’s not supported” errors you never had to think about before. Fabric Warehouse isn’t SQL Server. Under the bonnet it’s storing everything as Delta Parquet in OneLake, and that storage format shapes what T-SQL can and can’t do. Here’s what actually trips…

    » Read more

  • My Broken Query Runs Successfully in Synapse, Not That I’m Complaining, But How?

    My Broken Query Runs Successfully in Synapse, Not That I'm Complaining, But How?May 14th, 2026 in Blog, SQL, SQL Server, Synapse

    I had one of those moments this week, I spent too long trying to figure out how something that I knew was broken, was running fine and throwing no errors. Can it be called troubleshooting if it isn’t causing trouble? I have a Synapse pipeline that’s been running just fine for months, due to some migration work, I needed to take a look at the source query and found it was broken. I had it open in SSMS on the other monitor, ran it,…

    » Read more

  • Cymru, code and community at SQLBits!

    May 9th, 2026 in Just For Fun, News, SQL

    SQLBits wasn’t just about learning for the Purple Frog team, it was a chance to share our expertise and celebrate with the wider data community. Our HR & InfoSec Director Hollie Whittles and Senior AI Engineer Lewis Prince both took to the stage. Senior AI Engineer Lewis Prince led two sessions: “Responsible AI in Practice for the Microsoft Data Platform” and “AI Beyond the Hype: Practical Solutions for Real Businesses”, focusing…

    » Read more

  • Enforcing NOT NULL in a CTAS

    December 9th, 2025 in Blog, SQL

    In this blog I will show you how to use a CTAS to create a table with non-nullable columns. I have a table with the following structure (note all columns are non-nullable): CREATE TABLE [dbo].[Date] ( [Date] DATE NOT NULL, [Day] INT NOT NULL, [Week] INT NOT NULL, [Month] INT NOT NULL, [Quarter] INT NOT NULL, [Year] INT NOT NULL ) GO If I want to create a new version of this table with an additional column called YearMonth, the easiest way to do…

    » Read more

  • Why DuckDB Is the Data Engineer’s New Secret Weapon

    August 6th, 2025 in Blog, Data Warehouse, Python, SQL, Uncategorised

          Most databases need a server. DuckDB doesn’t. It’s a zero‑config, in‑process OLAP database you can run directly using Python. It’s fast, SQL‑friendly, and speaks Parquet like a native language — no clusters, no heavyweight setup, no waiting for provisioning. Think of DuckDB as SQLite for analytics — but faster, more column‑aware, and built for modern data engineering.   What Makes DuckDB Special DuckDB…

    » Read more

  • Reset Row Number within a Window in SQL

    July 24th, 2025 in Blog, SQL, SQL Server

    In this blog I will show you how to reset the row number within a partition window when using the ROW_NUMBER() function in SQL. The following is a sample dataset showing customer subscriptions with start/end dates, the RenewedSubscriptionID represents the subscription that was renewed to start a new subscription, for example SubscriptionID 101 was renewed to create SubscriptionID 102. The goal is to identify the sequence of renewals by customer.…

    » Read more

  • How to Run T-SQL in Microsoft Fabric Python Notebooks

    June 26th, 2025 in Blog, Python, SQL

    Here at Purple Frog Systems, I’ve been experimenting with pure Python Notebooks rather than PySpark Notebooks for small to medium datasets, due to their super quick start up time and reduced CU consumption for customers with smaller F-SKUs. https://learn.microsoft.com/en-us/fabric/data-engineering/fabric-notebook-selection-guide When scrolling through LinkedIn I came across a Microsoft Blog which allows you to combine T-SQL and Python within…

    » Read more

  • Verifying Potentially Stolen Passwords 

    • Change the Power BI Partner Logo to a vector instead of an imageApril 3rd, 2025 in Blog, Purple Frog Systems, SQL, SQL Server

    You’re currently being DDoS’ed and receive a ransom note. The attackers claim they’ve hacked your system and provide a list of usernames and passwords as “proof”. But did they really breach your system, or are they bluffing? Let’s investigate by checking the list against the database of hashed passwords.  Retrieve the Stored Password Hash from the database DECLARE @StoredPasswordHash VARBINARY(64) =…

    » Read more

  • Power BI Dataset Gives Mashup Timeout Error While Refreshing

    July 26th, 2024 in Blog, Data Warehouse, Power BI, PowerBI, SQL, SQL Server, Uncategorised

    Help – my Power BI dataset is giving mashup timeout error on refresh The mashup timeout error Incremental refreshes are a great benefit to have in order to reduce refresh times – but not when they give a mashup timeout error. Although they are meant to be a performance enhancement, they aren’t always the best approach as they fire parallel queries to the source and apply date time filters. Despite the default timeout being 2 hours, or 5…

    » Read more