Python Scripts: Unused DB Objects Audit
Budget: ₹750 – ₹1,250 INR
I need two focused Python utilities that help me keep both of my data platforms tidy by flagging objects that haven’t been touched in X days and suggesting safe-to-drop SQL.
Program #1 – AWS Athena
• Connect to my Athena catalog (I work from the AWS Management Console, but in code I’d like you to stay with boto3).
• For every table and view in a specified database, look back over completed query executions for the chosen time window.
• Views can nest CTEs or reference other views, so the script must walk the full dependency chain to decide whether a reference is direct or indirect.
• Return one CSV with the following columns exactly:
database , objectname, objecttype, last_reference_datetime, referencetype (direct/indirect) , referencedBy, RemovalSql
– referencedBy is the QueryExecutionId when the object is called directly, otherwise the object that called it.
– RemovalSql is only populated when the object is demonstrably unused (no direct or indirect reference in the window) so I can drop it immediately.
Program #2 – Microsoft SQL Server
• Query Store is already enabled, so use it to pull execution history.
• Scan tables, views, stored procedures, triggers, queues, and functions.
• Produce the very same CSV layout described above, with last_reference_datetime generated from Query Store data and RemovalSql included only when safe.
Both scripts should accept command-line parameters for:
– target database / connection information
– look-back period in days
– output file path
I’m happy with standard libraries plus boto3 for the Athena side; choose whichever light dependencies you need for SQL Server (pyodbc, sqlalchemy, etc.). Code should be clean, well-commented, and ready to run from the terminal.
Deliverables
1. Two independent .py files.
2. A brief README explaining prerequisites, setup, and examples of running each script.
3. Sample output CSVs from a dry-run (anonymised data is fine) so I can verify column order and content.
Acceptance criteria
• Scripts run without modification once credentials are supplied.
• CSV headers and field ordering exactly match specification.
• Objects with any level of recent reference are never assigned a RemovalSql.
• Indirect references are accurately traced.
I’m looking for efficient, reliable code that I can schedule regularly, with clear logging so future troubleshooting is straightforward.
Program #1 – AWS Athena
• Connect to my Athena catalog (I work from the AWS Management Console, but in code I’d like you to stay with boto3).
• For every table and view in a specified database, look back over completed query executions for the chosen time window.
• Views can nest CTEs or reference other views, so the script must walk the full dependency chain to decide whether a reference is direct or indirect.
• Return one CSV with the following columns exactly:
database , objectname, objecttype, last_reference_datetime, referencetype (direct/indirect) , referencedBy, RemovalSql
– referencedBy is the QueryExecutionId when the object is called directly, otherwise the object that called it.
– RemovalSql is only populated when the object is demonstrably unused (no direct or indirect reference in the window) so I can drop it immediately.
Program #2 – Microsoft SQL Server
• Query Store is already enabled, so use it to pull execution history.
• Scan tables, views, stored procedures, triggers, queues, and functions.
• Produce the very same CSV layout described above, with last_reference_datetime generated from Query Store data and RemovalSql included only when safe.
Both scripts should accept command-line parameters for:
– target database / connection information
– look-back period in days
– output file path
I’m happy with standard libraries plus boto3 for the Athena side; choose whichever light dependencies you need for SQL Server (pyodbc, sqlalchemy, etc.). Code should be clean, well-commented, and ready to run from the terminal.
Deliverables
1. Two independent .py files.
2. A brief README explaining prerequisites, setup, and examples of running each script.
3. Sample output CSVs from a dry-run (anonymised data is fine) so I can verify column order and content.
Acceptance criteria
• Scripts run without modification once credentials are supplied.
• CSV headers and field ordering exactly match specification.
• Objects with any level of recent reference are never assigned a RemovalSql.
• Indirect references are accurately traced.
I’m looking for efficient, reliable code that I can schedule regularly, with clear logging so future troubleshooting is straightforward.