Forensic data analyst – database administrator

Under the supervision of the chief data and information analyst, the forensic data analyst–database administrator (FDA–DBA) works with other CITA team members to provide critical support for timely and accurate data collection and analysis. The FDA–DBA is responsible for implementing ETLs, performing database maintenance, and fulfilling complex data requests. The FDA–DBA maintains policies and procedures for ensuring the security and integrity of OIG databases.

In addition to the above, the FDA–DBA routinely performs tests and modifications to ensure that data systems are performing correctly. The FDA–DBA troubleshoots database and ETL related problems when they occur.

DUTIES:

• Develop Extract, Transform and Load (ETL) processes using Pentaho Data Integration and Tableau Prep Conductor
• Troubleshoot and resolve database and ETL related problems
• Work closely with CITA staff to support all stages of data collection, cleaning, and coding for data analysis
• Manage OIG staging and production environment database instances including installation, configuration, and maintenance
• Develop, administer, and maintain ETLs and other related processes used to update Tableau dashboards
• Plan and document designs for new database elements, data flow diagrams, and data models
• Review third-party databases to develop data dictionaries and related SQL queries
• Assist OIG staff in the development and review of SQL queries
• Performs related duties as required

MINIMUM QUALIFICATIONS:Graduation from an accredited college or university with a bachelor’s degree in computer sciences, information technology/systems, business administration or a directly related field, plus two years of data analysis or data management work experience, or an equivalent combination of education, training and experience.

DESIRED KNOWLEDGE/SKILLS:

• Data management, with a strong background in relational database design with knowledge of Microsoft SQL and MySQL databases
• Creating, scheduling, and debugging extracts on Tableau Server
• Relational data structures, theories, principles, and practices
• Experience querying Oracle, MySQL, Postgres, and T-SQL
• Data warehousing, dimensional design/modeling and ETL architectures (such as Pentaho or Snowflake)
• Writing and reading complex SQL queries, stored procedures, triggers and views and program in at least one other programming language (such as Python, R, or Java)
• Data sets matching and manipulating large amounts of data from multiple sources
• Documenting with superior details and accuracy
• Translating technical issues for non-technical audiences
• Data modeling tools such as SQL Developer Data Modeler

SKILLS AND ABILITIES:

• Strong work ethic and professionalism
• Excellent written and oral communication
• Excellent interpersonal, communication, and problem-solving skills
• Collaborates with co-workers and thrives in a team-based environment
• Ability to effectively work cross functionally in a fast-paced work environment

ANNUAL SALARY:Range:$89,112 – $122,556(annual increases)

BENEFITS:For information on City of Chicago employees’ benefits, please visit our benefits website at:chicago.gov/city/en/depts/fin/benefits-office.html.

TRAVEL AND SCHEDULE:Travel outside Chicago is not required. Work schedule is 9:00 a.m. – 5:00 p.m. Monday through Friday, flexibility permitted.

RESIDENCY REQUIREMENT:Employees must be a resident of the City of Chicago and proof of residency is required at the commencement of employment.

KNOWLEDGE AND SKILLS ESSAY REQUIREMENT:A passing score on a knowledge skill test(s) and/or essay may be required.

Your application must answer the following prompts:

• In less than 500 words, describe how you would handle the following situation:
  You are assigned with monitoring ETL’s that support OIG’s public-facing dashboards ((Use the "Apply for this Job" box below).). One of those ETL’s has failed for three days in row. You have confirmed the database connection is correct, there are no unexpected null values, duplicate records, mismatched formats, or incorrect data types in the source data. What more would you do to identify the cause and resolve the failure?

WE VALUE DIVERSITY

The Office of the Inspector General (OIG) is an equal…