3 Foundational Principles for Writing Efficient SQL - Related to properties, set, xai, fraud, redis

3 Foundational Principles for Writing Efficient SQL

The tables in a database form the foundations of data-driven applications. Laboring with a schema that’s a haphazard muddle of confusing names and data flaws is a challenge. Building on tables with clear names and clean data simplifies your selects.

In this article, I’ll lay the groundwork for productive SQL writing by giving tables clear names and avoiding data errors through normalization and constraints.

The second part of this series will cover ways to structure SQL to make it easier to read and debug. So, let’s start by looking at how to get the foundations in place.

Good table names are clear and concise. The names for core tables in your application will be single-word nouns. These map to the corresponding business concepts. For example, clients , payments and invoices . Children of these tables extend the parent name with context like customer_addresses and invoice_items .

Sadly, naming your database objects is a rare luxury. Once you create a table or column, its name is fixed. While you can rename them, you have to change all code to the new name simultaneously. In large codebases, this is impractical.

So, what do you do if you’re working with a schema full of cryptic names? Are you doomed forevermore?

The good news is there are tricks you can use to bring clarity to confusing names:

A view is a stored query. You can use these to give a more understandable name to tables or columns. For example, this view makes it clear that the table cust_adrs stores customer addresses and the purpose of its columns:

create view customer_addresses as select c_id customer_id, a_id address_id, st start_date, en end_date from cust_adrs; 1 2 3 4 5 6 create view customer_addresses as select c_id customer_id, a_id address_id, st start_date, en end_date from cust_adrs;

You can then use the view like a regular table. Provided you only give new aliases in the view — [website], the only SQL clauses are select and from , and the select has no expressions — accessing the view is the same as using the table. Over time, you can shift code to use views with enhanced names.

But this approach takes time. There will be an extended period while you’re still working with the original opaque names. Adding metadata can help give context to these.

Table and column comments — free-form text describing objects — are a widely supported way to do this.

Oracle Database 23ai extended this concept with schema annotations, the key-value pairs you can use to document your tables, views, columns and indexes. For example, these statements annotate the unclear names for the table cust_adrs and its column c_id with a descriptive display value:

alter table cust_adrs modify ( c_id annotations ( display 'Customer ID' ) ); alter table cust_adrs annotations ( display 'Customer Addresses' ); 1 2 3 4 5 alter table cust_adrs modify ( c_id annotations ( display 'Customer ID' ) ); alter table cust_adrs annotations ( display 'Customer Addresses' );

You can view the annotations by querying the [dba|all|user]_annotations_usage views:

select object_name, column_name, annotation_name, annotation_value from user_annotations_usage where object_name = 'CUST_ADRS'; OBJECT_NAME COLUMN_NAME ANNOTATION_NAME ANNOTATION_VALUE CUST_ADRS DISPLAY Customer Addresses CUST_ADRS C_ID DISPLAY Customer ID 1 2 3 4 5 6 7 select object_name, column_name, annotation_name, annotation_value from user_annotations_usage where object_name = 'CUST_ADRS' ; OBJECT_NAME COLUMN_NAME ANNOTATION_NAME ANNOTATION_VALUE CUST_ADRS < null > DISPLAY Customer Addresses CUST_ADRS C_ID DISPLAY Customer ID.

Using clear names is the first step to building a good foundation. The next is to structure your tables effectively.

Database normalization is the process of removing redundant information from your tables. This avoids data duplication and makes certain types of data errors impossible.

Working with normalized data means you spend less time dealing with data quality issues, such as finding and removing duplicate rows. This frees you up for more productive tasks like building new functions.

The normalization process defines a series of normal forms. These are rules that tables must conform to in order to reach that level of normalization. The first three normal forms are:

First normal form (1NF): Each row and column stores a single value and there are no duplicate rows.

Each row and column stores a single value and there are no duplicate rows. Second normal form (2NF): There are no columns that depend on part of a primary or unique key.

There are no columns that depend on part of a primary or unique key. Third normal form (3NF): There are no columns that depend on columns that are not part of a primary or unique key.

While higher normal forms exist, these relate to overlapping keys and multiple many-to-many relationships. These are rare in practice. Ensuring your tables are in 3NF will cover most cases you work with.

A good smell check to see if a table is normalized to at least 3NF is to ask:

“If I modification one column in a table, does that imply I have to modification other columns simultaneously?”.

If the answer is yes, you’ve almost certainly violated a normal form. To fix this, split the dependent columns into a new table or remove them altogether.

For example, say you’re building a quiz-taking app. When players submit answers, you want to record the time they started, finished, and took to complete a quiz, alongside their answer. This gives a table like:

create table quiz_answers ( quiz_id integer, user_id integer, answer clob, start_time timestamp, end_time timestamp, time_taken interval day to second, primary key ( quiz_id, user_id ) ) 1 2 3 4 5 6 7 8 9 create table quiz_answers ( quiz_id integer , user_id integer , answer clob , start_time timestamp , end_time timestamp , time_taken interval day to second, primary key ( quiz_id, user_id ) ).

But there’s a relationship between non-key values: time taken = end time – start time . Changing any of these three columns implies you have to change at least one of the other two also. Avoid this inconsistency by removing one of these columns from the answers table.

Note there is an exception to the enhancement test. This arises if you change all the columns in a table’s primary key or one of its unique constraints. In this case, you’re changing an identifier for the row, so other values will likely change as well.

As with bad names, unnormalized tables are tricky to change in existing applications. Normalizing your data from the start saves you from wading through junk data.

But normalization alone is not enough to save you. To keep your data clean, you should also create constraints.

Database constraints enforce data rules. The database ensures all data meet these rules.

Without constraints in place, data errors will creep in, which can cause consumers to lose faith in your applications. Finding and fixing these errors is time-consuming. Creating constraints at the start avoids this pain.

Primary key: Ensures values are mandatory and unique. A table can only have one primary key.

Ensures values are mandatory and unique. A table can only have one primary key. Unique constraints: Like a primary key, a unique constraint stops you from storing duplicate values. Unlike a primary key, you can store nulls in unique columns, and one table can have many unique constraints.

Like a primary key, a unique constraint stops you from storing duplicate values. Unlike a primary key, you can store nulls in unique columns, and one table can have many unique constraints. Foreign keys: Define a parent-child relationship. The foreign key points from columns in the child table to the primary key or a unique constraint in the parent. With this in place, you can’t have orphaned rows.

Define a parent-child relationship. The foreign key points from columns in the child table to the primary key or a unique constraint in the parent. With this in place, you can’t have orphaned rows. Not-null constraints: Ensure you can store only non-null values in the columns, [website], they’re mandatory.

Ensure you can store only non-null values in the columns, [website], they’re mandatory. Check constraints: Verify a condition is true or unknown for every row.

Defining these constraints helps cement the foundations laid by normalization. For example, primary keys or unique constraints are necessary to enforce the “no duplicate rows” rule in 1NF.

Constraints can also help if you find yourself working with unnormalized data. While discussing normalization, we saw how storing start times, end times and durations for quiz answers can lead to inconsistencies. While removing one of these columns is the best solution, this may be impractical in a longstanding application.

Instead, you can ensure that all data conforms to the formula by adding this check constraint:

alter table quiz_answers add constraint quan_answer_time_c check ( ( end_time – start_time ) = time_taken ) 1 2 3 alter table quiz_answers add constraint quan_answer_time_c check ( ( end_time – start_time ) = time_taken ).

Once in place, new data that violates this rule will be rejected.

Unfortunately, it’s likely there is existing data where this rule is false. If so, adding the constraint will fail, and you’ll have the time-consuming job of fixing it. Fortunately, there’s a trick you can use to stop more invalid data from arriving:

These ignore existing data and apply the rules only to new data. Do this in Oracle Database with the following:

alter table … add constraint … novalidate; 1 alter table … add constraint … novalidate;

While you should still clean the existing data, you can be sure that no new errors will creep in.

Working with poorly named tables and invalid data means spending time deciphering and correcting them; a drag on your productivity.

Choosing good names, normalizing your tables and creating constraints give you a solid structure to be productive when writing SQL. With these foundations in place, you can turn your attention to structuring your SQL effectively. Stay tuned for the second article in this series for tips and tricks to help you do this.

Around the world, 127 new devices are connected to the Internet every second. That translates to 329 million new devices hooked up to the Internet of ......

Google Cloud, AWS, and Microsoft Azure have jointly showcased a new open-source project called Kube Resource Orchestrator (kro, pronounced.

Whether you were building a web site or an application, hosting choices used to be about bandwidth, latency, security and availability (as well as cos......

XAI for Fraud Detection Models

One would question, why should I worry about what is happening behind the scenes as long as my model is able to deliver high-precision results for me?

In this article, we dive deep into the aspect of reasoning and try to answer the question above. More importantly, we will understand how it can help us to build greater insights into evolving fraud patterns.

The eXplainable AI (XAI) has been around for quite a while, but it has not really created a buzz in the industry. Now, with the arrival of the DeepSeek-R1 reasoning model, there is a buzz in the industry for models that can not only make highly accurate predictions but also provide some reasoning on how these predictions were made.

The research of XAI has demonstrated that a model that can accurately identify fraudulent transactions may not necessarily be accurate in terms of reasoning. XAI provides system customers with the insight and confidence that not only is the model working as expected, but also the reasoning for the decisions is accurate. In subsequent sections, we will use simple techniques of XAI and unsupervised learning to solidify our approach.

We would use a publicly available fraud data set with anonymized feature attributes and build a simple classifier model that provides us decent accuracy to detect fraud. The model will be used further for the calculation of feature importance that drives fraud decisions.

Next, we use SHapley Additive exPlanations (SHAP) to determine the importance of functions that drive our decisions of fraud vs non-fraud transactions. AWS Sagemaker Explain service also uses the same concept for explanation. Here is a cool paper for people who would like to understand more about it.

Finally, once we have the SHAP values for our elements, we would use an unsupervised learning technique to categorize the different types of fraud transactions in our dataset. The idea of clustering gives us the fraud patterns in our dataset, and businesses can use it to monitor and understand these patterns easily.

We start by installing libraries like scikit-learn, shap, and pandas.

We check for any missing values in our dataset and try to understand the data distribution. The fraud dataset should be unbalanced, which means that normal transactions should far exceed fraudulent transactions. Our dataset contains [website] of transactions identified as fraud, and the rest are non-fraud. In this example, 0 indicates a normal transaction, and 1 indicates a fraudulent transaction.

Below, we have a simple random forest classifier that tries to predict the fraudulent transactions with 93% precision. The accuracy is reasonable for us to start our eXplanation process and determine feature weights that are primarily used for identifying the fraud.

Python from sklearn.ensemble import RandomForestClassifier aspects = df.columns[:-1] X = df[aspects] y = df['Class'] X = [website]'Time',axis=1) aspects = X.columns model = RandomForestClassifier(n_estimators=5) X_train, X_test, y_train, y_test = train_test_split(X,y,[website],random_state=287) [website], y_train) y_pred = model.predict(X_test) cm = confusion_matrix(y_test,y_pred) print(classification_report(y_test,y_pred)).

Plain Text precision recall f1-score support 0 [website] [website] [website] 85297 1 [website] [website] [website] 146 accuracy [website] 85443 macro avg [website] [website] [website] 85443 weighted avg [website] [website] [website] 85443.

Next, we extract shap values for all the fraudulent transactions in the dataset. We will apply an unsupervised clustering algorithm on shap values to generalize different underlying reasons for fraud. Please note that the process to determine the SHAP values will be time-consuming.

Python import shap explainer = shap.TreeExplainer(model) shap_values = explainer(X).

We use dimensionality reduction techniques like T-SNE to visualize higher dimensional data. We pass on the results to clustering algorithms like k-means to identify fraud patterns in our dataset. The silhouette score and elbow technique are used to identify the optimal value of k.

Python X = fraud_shap_values from sklearn.cluster import KMeans from sklearn.manifold import TSNE tsne = TSNE(n_components=2, random_state=42) X_tsne = tsne.fit_transform(X) tsne.kl_divergence_ common_params = { "n_init": "auto", "random_state": 42, } from sklearn.metrics import silhouette_score sil = [] kmax = 10 # dissimilarity would not be defined for a single cluster, thus, minimum number of clusters should be 2 for k in range(2, kmax+1): kmeans = KMeans(n_clusters = k, **common_params).fit(X_tsne) labels = kmeans.labels_ [website], labels, metric = 'euclidean')) [website], kmax+1),sil) [website]"K") [website]"Silhouette Score") [website]"Elbow method") [website].

Python y_pred = KMeans(n_clusters=k, **common_params).fit_predict(X_tsne) plt.scatter(X_tsne[:, 0], X_tsne[:, 1], c=y_pred) [website]"Optimal Number of Clusters") [website].

Finally, in the last step of our process, we need to identify the attributes that have maximum weights for the frauds in our dataset. We plot a bar graph with the top five heavyweights for each fraud category.

Python for i in range(k): cluster_data = explanation_df[explanation_df['Class'] == i] cluster_data = [website]'Class',axis=1) cluster_data = [website]'Amount',axis=1) shap.summary_plot(cluster_data.to_numpy(),cluster_data,plot_type='bar',feature_names=aspects, max_display=5).

The SHAP summary plot highlights various attributes contributing to different types of fraud in our dataset.

Above, we have shown two types of fraud transactions in our dataset. If we observe closely, most of the top five factors contributing to the two types of fraud are different. Business individuals can easily interpret the graphs and understand the combination of aspects that are causing different types of fraud.

The clustering of SHAP values helps us to identify various patterns of fraud in the system. Without reasoning capabilities, it would be difficult for end consumers to understand any new or evolving patterns of fraud or why a certain transaction is fraudulent.

Hope you guys liked the article and that it helped you learn something new!

Pinecone introduced Tuesday the next generation version of its serverless architecture, which the organization says is designed to improved support a wide var......

xml-trueformat is a TypeScript library for parsing and manipulating XML documents while retaining their exact original formatting. It stores whitespac......

Redis is a high-performance NoSQL database that is usually used as an in-memory caching solution. However, it is very useful as the primary datastore ......

How to Set Up Redis Properties Programmatically

Redis is a high-performance NoSQL database that is usually used as an in-memory caching solution. However, it is very useful as the primary datastore solution.

In this article, we will see how to set up Redis properties programmatically on the example of a Spring application. In many use cases, objects stored in Redis may be valid only for a certain amount of time.

This is especially useful for persisting short-lived objects in Redis without having to remove them manually when they reach their end of life. We will look at how to configure time to live (TTL) for the app. TTL here is just an example of a Redis property. Other properties can be set up this way as well.

Let’s consider a Spring application that stores CardInfoEntity in Redis. The entity contains sensitive information that can be stored for only five minutes. Here is what CardInfoEntity looks like:

Java @Getter @Setter @ToString(exclude = "cardDetails") @NoArgsConstructor @AllArgsConstructor @Builder @RedisHash public class CardInfoEntity { @Id private String id; private String cardDetails; private String firstName; private String lastName; }.

One needs to set TTL so that the objects will be deleted automatically. This can be achieved in three ways:

Using timeToLive property of the @RedisHash annotation ([website] @RedisHash(timeToLive = 5*60) ).

property of the annotation ([website] ) Using the @TimeToLive annotation on either a numeric property or a method.

annotation on either a numeric property or a method Using KeyspaceConfiguration.KeyspaceSettings.

The first two options have their flaws. In the first case, the value is hardcoded. There is no flexibility to change the value without rebuilding and redeploying the whole application. In the second case, we have to introduce a field that doesn’t relate to business logic.

The third option doesn’t have those problems. With this approach, we can use property in [website] to set TTL and, if needed, other Redis properties. It’s also placed in a separate configuration file and doesn’t interfere with a business domain model.

We need to implement KeyspaceConfiguration and introduce custom KeyspaceSettings , which contains the Redis settings that we want to set up.

Java @Configuration @RequiredArgsConstructor @EnableRedisRepositories(enableKeyspaceEvents = RedisKeyValueAdapter.EnableKeyspaceEvents.ON_STARTUP) public class RedisConfiguration { private final RedisKeysProperties properties; @Bean public RedisMappingContext keyValueMappingContext() { return new RedisMappingContext( new MappingConfiguration(new IndexConfiguration(), new CustomKeyspaceConfiguration())); } public class CustomKeyspaceConfiguration extends KeyspaceConfiguration { @Override protected Iterable initialConfiguration() { return Collections.singleton(customKeyspaceSettings([website], CacheName.CARD_INFO)); } private KeyspaceSettings customKeyspaceSettings(Class type, String keyspace) { final KeyspaceSettings keyspaceSettings = new KeyspaceSettings(type, keyspace); keyspaceSettings.setTimeToLive(properties.getCardInfo().getTimeToLive().toSeconds()); return keyspaceSettings; } } @NoArgsConstructor(access = AccessLevel.PRIVATE) public static class CacheName { public static final String CARD_INFO = "cardInfo"; } }.

To make Redis delete entities with TTL, one has to add enableKeyspaceEvents = RedisKeyValueAdapter.EnableKeyspaceEvents.ON_STARTUP to @EnableRedisRepositories annotation. I introduced CacheName class to use constants as entity names and to reflect that there can be multiple entities that can be configured differently if needed.

Redis has good integration with Spring, which provides several options for configuration. The approach provided in this article is not the most laconic and clear for someone who doesn’t have a lot of experience working with Spring. In many cases, just using annotations on top of entities is sufficient.

However, if you have a more complex app with several different entities with different Redis properties, the option based on providing KeyspaceSettings can be preferable due to its clear structure and the above-mentioned advantages (using properties and keeping configuration outside business objects).

To view the full example application where AOP was used, as shown in this article, read my other article about creating a service for sensitive data with Spring and Redis.

The source code of the full version of this service is available on GitHub.

The modern discipline of data engineering considers ETL (extract, transform, load) one of the processes that must be done to manage and transform data......

On Wednesday morning, our coworkers at The New Stack started asking each other, in meetings and via panicked emails: Hey, are you having trouble with ......

In the first part of this series, I introduced the background of Kube Resource Orchestrator (Kro). In this installment, we will define a Resource Grap......