What are the best practices for data engineering with SQL?

If the organisation is bigger and data is critical it is better to have a dedicated team which will maintain this data dictionary and reference. They should be involved when new requests are being groomed and worked on.

In SQL data engineering, adhere to consistent naming conventions for database structures and processes. Prioritize a normalized database design to ensure data integrity, use indexes judiciously for optimized performance, and regularly fine-tune queries based on execution plans. Implement robust error handling, enforce data quality through constraints, and thoroughly document your database schema to foster collaborative efficiency and easy maintenance.

Focus on Naming Standard Need to maintain the version Follow general coding rules Need to know when should we use the cluster index and non cluster Mainly focus on the performance

Naming convention is the one of the most important things that allows specialists with different roles (DA, DE, DQ, DS, D?) speak the same language. It's worth starting with field names: for example, by "username" everyone means the same thing. Then entity level: is it user, or person? Or customer? You can use suffixes for all of it to explain what exactly is it. In a huge company you will be tired to explain everyone, which field contains index. The seconds saved for many people will become hours of extra time.

In my experience, Naming Conventions simplify the data organization, promote extensibility, and prevent future rework when you wish to consolidate the business logic of different environments into a single account. It is also important to keep the naming convention of the Cloud Storage Layer with the Raw Layer, making it intuitive for anyone else to easily work in the future.

As many programming languages, you will read the SQL code more often than write. Following the code style guide will help you to understand and remember, what did the author mean. Even (Especially) if the author is you.

Though this is taken as a given The organisation or leadership should ensure this is documented and is made available to all employees as a ready reconner. This will help in ensuring standards are met. This along with code reviews to check style guidelines as well.

When you're looking into your SQL game, I would recommend to dive into the world of version control. Git (just to put an example) helps you track changes, collaborate seamlessly with your team, and undo any oops moments. Add some documentation about your database changes, either using specialized tools or just looking into your version control system. That over time will make wonders for your organization. Besides having your documentation and control version up to its A game level, also the take into consideration the importance of testing your data for accuracy. There's nothing worse that presenting bad data or inaccurate one to your customers (internal or external), that why you should have a good suite of testing every step of the way.

In the realm of technical coding, I find it intriguing that some of my peers still hesitate to employ uppercase SQL keywords. It appears as though they harbor the misconception that an uppercase keyword would consume more storage space than its lowercase counterpart! Personally, I experience a sense of frustration when examining a codebase and encountering lowercase SQL keywords. In my humble opinion, each programming language possesses its distinctive aesthetics, and SQL unquestionably harmonizes with uppercase keywords. After all, even the very name "SQL" is composed entirely of capital letters!

Use a consistent style guide. This will make your code more readable and maintainable for yourself and others. There are a number of popular SQL style guides available, such as the Google SQL Style Guide and the Airbnb SQL Style Guide.

- There might not be one way to optimize this. The architect had to be invoked to check how much data is involved and based on the load the optimization strategy needs to be adopted. - The challenge is when the technology moves to a better one how will we move old code into new methodology Hence we need a dictionary to be maintained to ensure its documented if for e.g. we are using spark or Beeline or pig to ensure we don't open code everytime to check this

I have learned with experience that while writing queries for the ETL process, it's important to understand concepts such as Common Table Expression versus temporary Tables; Join Explosions; Join Orders; and in-depth knowledge of Windows functions. Once you have a strong understanding of the above concepts, then you will be able to write better and optimized queries.

This is more important than a lot of people think. Ensuring everyone follows the same style makes a lot of the debugging and optimising much easier.

Write efficient SQL queries by using appropriate indexes, avoiding unnecessary joins, and optimizing subqueries. Use EXPLAIN or similar tools to analyze query plans.

For performing effective query optimizations, a basic understanding of Database Architecture and a good understanding of data access paths is important. This is especially true when trying to implement partitions, indexes, hints, etc. Furthermore, understanding query plans and determining cost of specific operations is a very handy skill. Data Engineers must also think about optimizations in alignment with database usage and scalability.

- Other than regular QA testing , organization should strive to see how many are regular code checks we do and try to automate them. This helps in regular regression testing - For critical online queries it would be ideal to perform a load test as well to check if it is optimised and that doesn't break.

Write modular code. Break your code down into smaller, reusable modules. This will make it easier to maintain and debug your code, and it will also make it easier to share your code with others. Test your code. It is important to test your SQL code before deploying it to production. This will help to ensure that your code is correct and that it performs as expected.

Testing is often the first victim when pressure starts to build on a project. In general programming, functional requirements can be hard to test across systems or libraries. Fortunately, this is less of an excuse in many data engineering problems, and high coverage easier to attain. Solutions often boil down to pipelines that move and transform data from source to destination, presenting a natural opportunity to test at the input and output boundaries. Input can be mocked and expected output asserted. Special cases can be added as they are discovered, often automatically (e.g., a CSV importer handling special characters). Start by automating flows end-to-end, then modularize at natural points of abstraction. Automation is key.

Always add meaningful comments to your code, this enhances readabilty. Use SQL linter for query formatting, a time saving trick.

Here are my suggestions: - Ensure your SQL queries are optimised for performance. This involves selecting appropriate indexes, using the right join types, and avoiding unnecessary operations. - Include descriptive comments alongside your SQL code. Comments help in debugging and understanding the purpose of each query. - Employ robust version control systems like Git or GitLab to manage changes to your SQL code. This helps in tracking modifications, collaborating with others, and ensuring code consistency. - Strive for simplicity in your SQL queries. For eg, Instead of complex nested subqueries, consider using Common Table Expressions (CTEs) or temporary tables. This simplifies code readability and maintenance.

One of the aspects of general programming that is usually more neglected than testing is quality documentation and proper version control. Code documentation applies to a specific version of the source code, and a specific source code revision is responsible for incidents in production. This is the inevitable reality of software engineering and applies verbatim to SQL code. It defines the difference between a 10-minute bug hunt and a four-hour binary search involving several versions and build/deploy runs of the source code. Code changes are complete when the affected documentation is updated and both are committed to source control with an unambiguous, bidirectional reference between them in the case of separate repositories.

Don't forget about the ACID property when dealing with structured SQL data—it's pretty important! 1. Atomicity: “all or none” - either the whole process will get executed or none. 2. Consistency: Guarantees that a transaction brings the database from one valid state to another, preserving data integrity and constraints. 3. Isolation: Ensures that concurrent transactions don't interfere with each other, maintaining data integrity and preventing data anomalies. 4. Durability: Guarantees that once a transaction is committed, its changes are permanently saved, even in the event of a system failure, ensuring data persistence.

- Are these codes going to be external facing touch points or not would decide on how extensive the testing and architecture has to be. If an application is external.fscing and it crashes because of not being able to handle the load then it is not a failure of application alone it might end up earning a bad reputation for an organization as well. Hence extra level of checks need to be put in place for external facing areas.

We need to ensure that databases, its architecture, queries are created keeping in mind the scale of the project. Also ensure that if needed, we can scale above mentioned points if there is a need for exponential growth without breaking the code and going back to scratch as well.