Generating files for data extracts, reporting and even data migration is a crucial part of any back-end team. A typical data team will have to generate many files to be sent to various clients or consumers inside or outside of the organisation. As with all things, the question is how can the generation of those files be made simpler, and can it be automated to such a degree that files are dynamically generated?
The short answer is yes, it can be done dynamically.
A slightly more nuanced answer is that it truly depends on the file that needs to be generated, where the data comes from and what file we need to generate. For example, we want to generate a CSV file from any table passed in. This is completely doable. In Linx, we can create a process that will:
- Receive the table name, file name and database connection string as input parameters
- Create a query that will select the headers
- Create a file with the headers
- Create a query to select the fields in the table separated by a comma
- Write these rows to a file
The below process showcases how such a process will look in Linx.
Here is an example solution. (You can copy this function into your own solution.)
Note: This solution will specifically work on Microsoft SQL Server databases as it requires the CONCAT function and that specific way to retrieve table headers. However, you can use it as a template and alter the queries.
With this logic, you can essentially now dynamically create queries to be executed. This has some great potential once you realise that you are not limited to hardcoded SQL statements. Create what you need on the fly.
This functionality can be expanded by passing in views instead of table names. Meaning you can craft highly specific data sets. That said, this process is perfect for dynamically creating files for data extracts.
You can now use this process to generate files on a schedule, generate files based off API calls (you can even return the file in the API response body if you are feeling up for it), or you can create these files and send them to a specific location.