iLearn

 If you are someone who likes to brian storm on SQL query solutions, you would like this post.  Some time back, one of my friend reached out to me to get some help on a SQL query question that was asked to him during an interview with Microsoft (if you know what I mean :-) ). Keep reading. There are 2 tables, one Accounts and the other Transactions with entries as shown below.  Here D is for Deposit and W for Withdrawal Expected Output The accounts table reflect the balance before these transactions happened. So, essentially we have to generate an account statement that would look something like the above. At first glance, it looks pretty straightforward, especially the Credit and Debit part. However, the real challenge is that you need to calculate account balance as a running calculation.  Pseudo-Code  You first have to calculate the balance from the first transaction. Then just add or subtract the respective transaction amounts based on if it's a withdrawal or deposit.  Going in

I was trying to sync our team's database project and was getting the below error git: 'credential-manager-core' is not a git command could not read Username for 'https://github.com': terminal prompts disabled One thing to call out here is that I had not synced the changes in the database project for over a month and there were no issues before.  My guess is that somehow the OWA authentication got wiped out from my system and this might be the reason for this issue.  In terms of the Solution I tried searching online on  terminal prompts disabled and got a plethora of solutions mostly suggesting some git commands. I tried a few of the suggestions and didn't get any luck. This morning I started searching for  git: 'credential-manager-core' is not a git command   and came across the following issue reported on Microsoft forums  Warning/error message when running git fetch within Visual Studio 2019 It basically suggests downloading/updating the latest version

Hi everyone, welcome to another blog of iLearn.  First of all, it always gives a different "high" when you tackle a performance-related issue. This particular scenario was that the query in our quality assurance environment was running quicker than the query in production.  Here is how we started the investigation and arrived at the solution. First of all, started looking at the statistics.  1. The First check was to see if the auto stats property is set to active or not and it was ON both in Production and QA databases SELECT name,is_auto_create_stats_on FROM sys.databases WHERE name = 'OurDatabase';   2. The Second check was to update the stats on the table (in Production) being queried (since we found that the stats were not up to date) update STATISTICS OurTable; Didn't do any good, the query was still running slow 3. Finally decided to compare the configuration for Production and QA Database instances and found that the QA is set to 8 vCores and Production to

Hey guys, wanted to talk about delta lake in data bricks and the ADLS ecosystem. I have found an issue while changing the schema of delta tables.  Problem Statement - Let's say you have a table Table1 with columns column1 and column2 as a delta table and the requirement is to get rid of column2 from Table1, something like below Using T SQL we could easily achieve this by using ALTER Table drop column ...command However, in the spark SQL and Databricks realm, I couldn't find any such feature. There are options to re-order, add columns but not drop as per  https://docs.databricks.com/delta/delta-batch.html Solution - Seems like the only possible solution is to drop the folder/delta table in the ADLS You would have the table/folder recreated after running the above spark SQL query (screenshot) Does anyone have a proper solution to this? This looks like a legit limitation as changing schemas is very common in real-time projects

  Prerequisites  Azure SQL Server with a source and target database instance GitHub repo Visual code for SQL Server Database Objects Familiarity about dacpac Azure DevOps Account Things I didn't check  Since I was an admin on azure, DevOps, I am not sure on the permissions required. Ideally contrubutor access should be enough to implement this solution.  Problem Statement and Solution We are using dacpac for deploying our SQL artifacts from source to target server/database. The SQL server code, as well as the dacpac, is checked inside the GitHub repo. We need to develop an azure DevOps pipeline that uses the dacpac and does the deployment.  I have found if you change the dacpac location to a custom folder and sync it to the Github repo, we could achieve what we had set out to.  Logical Steps 1. Create a SSDT project  2. Link SSDT to github repo with a local repo 3. Create a folder for dacpac in local repo 4. Add settings in visual studio to point dacpac creation to this folder 5. B

  Problem Statement - We had a power shell script as part of post-deployment step in one of the release pipelines and it was erroring out with the following message "Unable to initialize device PRN" Solution - There was a print statement inside the script, replaced it with echo. Example print "Thanks for reading this post"  to  echo "You are awesome"

Although I have been aware of GitHub for quite a while but got a chance to use it as a repository in the project for storing artifacts only over the past few months.  We use GitHub as the sole cloud repo for storing SQL, Databricks, and Azure Data Factory artifacts.  Got an opportunity to set up GitHub with ADF right from scratch and all the excitement to learn more about GitHub took a back seat when I stumbled upon the following issue Problem Statement - You are an admin on a repo in GitHub and even then get a permission error while linking the ADF to GitHub Solution - After scratching my hairs and going on a searching spree on google for some days, I was able to figure out what needed to be done, however was not aware of how to implement it. Finally thanks to Microsoft for getting on a call and helping me resolve this issue. What needed to be done - Need to authorize AzureDataFactory service so that Github can communicate with it.  How it should be done -  Basically, you need to auth

  Identity Insert Without Identity Column Problem Statement -  There is a source table and a target table. The structure is almost similar. The ask is to insert data from the source table to the target table. The target table has an additional column of int type which could be used as a row identifier, however, it's not configured to have auto identity insert and you cannot redefine/recreate the target table. Solution - Used Row_Number window function by partitioning it with a constant value. Please see below Let me know if you guys can think of some other way! Fetch the schema details of a view Got a rarer requirement to list out the columns returned from a view along with its data type.  Here is the query

I have been working on Azure Databricks (DBx) for 3/4 weeks and there are loads of things that I have been fortunate enough to learn. I always wanted to explore or find out more about Azure DBx and just grateful enough to be given a chance to use it in a real project.  Let's get to the technicalities then.  The other day I stumbled upon this error while running a DBx notebook  User  username@domain.com   does not have READ permission on scope xxx_yyy to perform this action. So the problem statement was to find out the details of scope xxx_yyy.  I assume if you are reading this article you will have an idea of what secret scopes are in context to Azure Databricks.  You can read more on it here  Secret Scopes - Azure DBx Now I will list out the steps to create and manage secret scopes. Creating can be done through UI on azure portal but for managing the scope the process is little different. Please go through the following steps CREATE 1. For creating scopes using key vault open the