Database design and optimization with AI

Advanced AI-Assisted Coding for Developers

Thalia Barrera

AI Engineering Curriculum Manager, DataCamp

Plugging in real data

New folder under Atlas's /usecase directory
sqlite3.OperationalError: no such table: activity_events
❗ Table exists under a different name: schema mismatch

AI for schema mapping

🤖 Schema alignment prompt:

Align my database schema to the schema Atlas needs to run. Use the tables from the production database and the tables Atlas expects. Rename tables and columns safely and list the mapping between the original data and the new schema.

ai-table-squema.mp4

Real-world data updates

mermaid: datacamp-purple: data reconciliation pipeline

Wayfarer ingests events from partners
Partners send duplicates, corrections, and adjustments
Stale data → inaccurate KPIs
Reconciliation through CRUD operations

AI-assisted duplicate detection

Partners send new batches of booking events
Multiple providers scrape the same API → duplicates are common
Must detect duplicates before inserting into production

nanobanana: center: duplicate detection in database tables

Designing the detection query

Query performance optimization

SQL query:

🤖 Performance optimization prompt:

Here is an SQL query I run frequently, along with the table schema. Identify the main performance bottlenecks and rewrite the query to be more efficient without changing the results.

ai-efficient-query.mp4

Database design at scale

🤖 Database design prompt:

Given this schema and workload (frequent reads of KPIs by date and city, and daily batch inserts), recommend normalization improvements, partitioning strategies, and scaling options.

Prioritize changes with the best impact-to-effort ratio.

ai-sql-partitioning.mp4

Let's practice!

Advanced AI-Assisted Coding for Developers