Public Use Microdata Sample (PUMS) for data engineers

Years ago I was studying how to use Power Pivot and wanted a large dataset to test. I discovered the Public Use Microdata Sample data (Public Use Microdata Sample (PUMS)). This data provides curated results from millions of questionnaires sent out each year by the US Census bureau. These files are generated from the survey …

Public Use Microdata Sample (PUMS) for data engineers Read More »

Azure Synapse Analytics pipeline expression to create dynamic URL

A URL can be broken up into multiple parts. 1 If you break up the URL into its parts, how do you put all the pieces together to form the full URL in Azure Synapse Analytics (#AzureSynapse) pipelines or Azure Data Factory? This post gives an example of how to create a valid URL from …

Azure Synapse Analytics pipeline expression to create dynamic URL Read More »

Limiting permissions with “Execute as” when using dynamic SQL

When using dynamic SQL there is always the possibility that someone will inject SQL that does harm. They can put queries that can drop objects or can create sysadmin logins. Running the scripts as low permission users can reduce the potential harm. But that takes time to research and is easy to cut. You can …

Limiting permissions with “Execute as” when using dynamic SQL Read More »

Getting metadata from query using sp_describe_first_result_set

One of the tasks that eats a lot of my ETL development time is documenting the views and procedures I write. I need to know where exactly the data is coming from. sp_describe_first_result_set provides a flag for outputting the source for many of the columns in a query. exec sys.sp_describe_first_result_set N’exec [Integration].[GetOrderUpdates] ”20100101”, ”20170122”’, null, …

Getting metadata from query using sp_describe_first_result_set Read More »