Best practices for schema management

Article
09/30/2024

Applies to: ✅ Azure Data Explorer

Here are several best practices to follow. They'll help make your management commands work better, and have a lighter impact on the service resources.

Action	Use	Don't use	Notes
Create multiple tables	Use a single `.create tables` command	Don't issue many `.create table` commands
Rename multiple tables	Make a single call to `.rename tables`	Don't issue a separate call for each pair of tables
Show commands	Use the lowest-scoped `.show` command	Don't apply filters after a pipe (`\\|`)	Limit use as much as possible. When possible, cache the information they return.
Show extents	Use `.show table T extents`	Don't use `.show cluster extents \| where TableName == 'T'`
Show database schema.	Use `.show database DB schema`	Don't use `.show schema \| where DatabaseName == 'DB'`
Show large schema	Use `.show databases schema`	Don't use `.show schema`	For example, use on an environment with more than 100 databases.
Check a table's existence or get the table's schema	Use `.show table T schema as json`	Don't use `.show table T`	Only use this command to get actual statistics on a single table.
Define the schema for a table that will include `datetime` values	Set the relevant columns to the `datetime` type	Don't convert `string` or numeric columns to `datetime` at query time for filtering, if that can be done before or during ingestion time
Add extent tag to metadata	Use sparingly	Avoid `drop-by:` tags, which limit the system's ability to do performance-oriented grooming processes in the background.	See performance notes.